Thursday, March 26, 2020

How to use DAVERAGE function in EXCEL


Today we are going to learn an important function of MS-Excel which is DAVERAGE function. DAVERAGE function calculates the average of selected database entries that meet with specific criteria that you already set.

Without furthermore delay. Let’s go and have a closer look at how to use a DAVERAGE function in your excel worksheet.

General Syntax

= DAVERAGE (Database, Field, Criteria)

Database: The entire Database table where your data values are stored.

Field: Select the column from where you want to calculate the average of data values.

Criteria: Select the criteria range based on the condition you already set up.

If you don’t know, how to use this DAVERAGE function in Excel. Then just follow the below screenshots and please have a closer look at these images.

First, open the excel worksheet which contains the database table and criteria table. For example, create an excel worksheet as like ours.

Select a blank cell where you can display the result of DAVERAGE function and you can also give a name of that blank cell as your wish.  

Then, from that selected blank cell just follow the below steps according to the screenshots and you will get the result.





















Final words, this tutorial is written for beginner level audiences so we choose to write this tutorial in a very simple way with simple words. 

If you think you are an Excel expert then please skip this tutorial. Just comment us or contact us about your requirements and we will certainly create your required tutorial and content for you.

Stay safe & healthy. Stay with tutorialabc.com for easy learning!

Finally, we add a GIF file of the whole process here, incase you missed any part of it. So, please carefully look at the GIF for better understanding.





















Sunday, March 22, 2020

How to protect your Excel Book Using a Password


There might be some situations come when you need to lock your Excel file. Think of a situation, suppose someone has control over your PC and you have some important data in an Excel file on that PC.

Well, in that situation, there might be some potential risk of losing your important data or risk of alteration and modification of data values.

But, you need not to worry at all, MS-Excel has a built-in option to lock your Excel file which can secure your data.

For that, Go to File menu then select Save As option and from there click on Tools just beside the Save button. Select General Options and type the password for Read-only mode and also put a password for permission to modify the data of that file. 

Finally, reenter both passwords and click OK. And, finally, click the Save button and see the magic! 


Your Excel WorkBook is locked now and only you can get access to this file by entering the passwords. 


Isn’t it easy! So, for securing your important data in Excel file always lock your file by following the above-mentioned steps. 


Here, I am providing some screenshots for better understanding. Have a look at the screenshots sequence and it will clear all your confusions.

(DEMO EXCEL BOOK)
































(PASSWORD PROTECTED EXCEL BOOK)

That’s all for today. Thanks for staying with tutorialabc

Sunday, March 1, 2020

How to use HLOOKUP function in MS-EXCEL (beginner)


In MS-Excel HLOOKUP is a built-in function which is categorized as a Lookup/Reference Function where ‘H’ stands for Horizontal. According to techonthenet, HLOOKUP function performs a horizontal lookup by searching for a value in the top row of the table and returning the values in the same column based on the index number.

HLOOKUP can be used as a worksheet function (WS) in Excel. As a worksheet function, the HLOOKUP function can be entered as part of a formula in a cell of a worksheet.


Anyway, HLOOKUP function can be used for a large set of data in a single or multiple tables to find or lookup a specific value in a faster way.


But in this demonstration, we will use a single table contains a small amount of data and find out some data through HLOOKUP function.

Basic Syntax


=HLOOKUP( lookup_value, table_array,row_index_number, [range_lookup])

This function contains some arguments and each has a specific meaning. So, let’s take a closer look at what these arguments actually pass in the function.

lookup_value: This is the value you are looking for in the first row of the table.

table_array: Two or more rows of data which are sorted in ascending order or the range you would like to define from the table to lookup the value.

row_index_number: The row number in the table from which the matching value can be retrieved. For first row you can write 1 and for other row just write in a numeric value of that row.

range_lookup: this argument contains logical value. If, True that means the approximate match of that value and if, False which means it will look for the exact match of the value from the table. It is an optional field and by default, it is True in nature.

Well, working with example is the easiest way to understand how HLOOKUP function work. 

Now, take a closer look at the below screenshots and data values passed here for better understanding.  

Here is a demo table with some demo data to perform HLOOKUP function.


Then, we will chose a blank cell where we can look up our required value through HLOOKUP function. We will use K2 cell for the operation.

Suppose, we have a scenario that there is a table of some products for an online grocery store and customers ordered these products in the above manner. Each order has a unique OrderID with the UnitRate of the product and the number of unit (Quantity) they want.

Let’s think, we need to look up the UnitRate for a specific OrderID, e.g. we need to look up the UnitRate of OrderID=19881. From the table we can easily say that, it is $9.75 but how can we know using a HLOOKUP function. Let’s have a look on the procedure,

Chose a blank cell, for our case we chose K2,




Then, we can start typing ‘=HLOOKUP and press tab of the keyboard in that cell and manually input the arguments according to our needs. Like the following one, 



But, for the beginners, there’s a second option to pass arguments in HLOOKUP function. Go to the Formulas tab and look for Lookup & reference menu, click on it a Drop Down list will show you a screen like this,


Then just click on it and a new box will pop up. Like this one,


Based on the field, click and type in the box. Like for Lookup_value we want to look for the OrderID = 19881 which is in cell E1, so we will click the cell.


For Table_array, we will select the entire table by dragging the cell from A1 to G3. 


For Row_index_number, we will select the row as the index to view the lookup table. 


We chose 2 because we want to view the UnitRate of the Lookup_value E1.

And, finally, there is another field named Range_lookup and here we wrote FALSE because we want an exact match. 



Then, just click OK and get the result of your HLOOKUP function.


Well, this tutorial is written for very beginner level audiences so we use a very tiny amount of data to demonstrate it and use very simple words to instruct them. 

If you want an advance level of tutorial on How to use HLOOKUP function in MS-EXEL for advance user please do some comments for it and we will provide you a fully functional tutorial with a wide range of data on this topic.

That’s all for today. We will post a new tutorial ASAP! Till then be happy and keep learning. Stay with  tutorialabc & for any inquiry please contact us through our Contact page.