Excel Defined Names: The Definitive Guide
Updated:
This is a complete guide on Excel Defined Names
Today, I will show you the exact benefit of using Defined Names
If you open a new Excel Sheet it will have a default name (Book1 – Excel) associated with it
Even it has a default Sheet as “Sheet1”
And you know that you can customize the same as required.
Similarly, each cell and a range of cells in a workbook has a name to it and we can customize it as required.
In this guide I will show you:
In the above example, we have used the formula “=SUM(B2:B5)*E1” to find out the Total Sales value
Now, let’s use Defined Names:
Select the cell E1 and hover the mouse to the top left corner
You will see a small popup that says Name box
In the Name box, you could see the cell reference E1
From Name box select E1 and rename it as Fees
Press Enter
Congratulations you just named a single cell
Now, let’s name a range of cells
Select the range “B2:B5“
Go back to the Name box and rename it as Enrolment
Press Enter
On the right of Name box, you can see a small dropdown arrow
If you click on it, a list of all the Names we created will be displayed.
Click on any of the Name
You will notice that the cursor points to that cell reference.
That’s it
Not let’s use the Name we just created to find the Total Sales value
Go back to the cell E2
Replace the existing formula to “=SUM(Enrolment)*Fees“
You will get the same result.
But this time using Name we created
In the above Workbook, we have a daily attendance record for each month
We need to click on the sheet to switch between each month
So, instead of clicking on sheet name, we can use the Name box to switch between each month
Here’s the step:
You will be redirected to the Sheet name you just selected
You can also use function key F5 to open the “GO TO” window with a list of all the names we created.
Choose the name you wish to access and you will be redirected to the respective sheet
That’s it.
In the above data sets, we have an hourly rate, Employee name, and Paid hours.
Based on the data we need to find out the total pay using the defined names technique
So now let’s first name the cell B1 as Hourly_Rate`
This time we will use the other option instead of using the name box to define the name
Select the Range A1 : B1
From the Formulas tab under Defined Names click on Create from Selection
Create Names from Selection window will popup
Click on Left Column.
Click ok
boom
Excel has created a name based on the value in the left column
Now
Select the range B3:B8
From the Formulas tab under Defined Names click on Create from Selection
Create Names from Selection window will popup
This time choose Top Row and click ok
That’s it.
Name Paid_Hours is created
Click on Name Manage or press function key F5 to view all the names you created.
Now
Select the cell C4
Write the formula = press function key F3 to open the paste name window
Select the name Hourly_Rate and click ok
Add * sign and press function key F3 again
This time choose Paid_Hours and click ok
Your formula should look like
=Hourly_Rate * Paid_Hours
Drag the formula till C8
Note: The formula will only work if the Paid hours and Total pay are in the same Row.
Use Defined Names in data validation:
From the above data select the range A2:A11 and name it as Country
Now select any the cell D1 and go to data validation
Under data, validation add the source as =Country and click ok
That it.
Now let’s understand how to use Defined Names in Vlookup function
Select the range A2:B11 and name it as CC
Select the cell D3 and write the formula
” =VLOOKUP(D1,CC,2,0) “
Change the value from the data validation list to see the effect
Use Defined Names in Pivot table
We will use the same name (CC) for creating Pivot Table
Select cell D5
From Inset tab click on Pivot Table
Under Create PivotTable window
Select a table or range add =CC and click on ok
That’s it
You have used the defined names to create a Pivot.
In the above table, we have Names, Weekdays, and Paid hours
We need to create a formula that will pull the Paid_hours from the above table based on the Name and Weekday
Yes, we will use Excel Defined Names do that
Here’s how:
First, we need to name each column and row in the table. To do that:
Now that we have a name created, Let’s create a formula
You will get 18 as an output
Change the Name and weekday to get the appropriate result
Now let’s make it even more dynamic
Let’s keep the Name Sachin in cell A11 and Weekday Fri in cell B11
Now let’s add a formula =A11 B11
In cell C11 to find the paid hours based on the value in cell A11 and B11
Press Enter
You will get an error #NULL! output
This is because Excel is not able to recognize the cell value as a Name
To help excel recognize the cell value as a name we need to make use of Excel Indirect function
Here’s how:
You will get 18 as output.
Now, you can change the value in A11 and B11 to get the appropriate result
Now let’s understand
Store Values:
In the above table has two columns, Name, and Total pay
We need to add a % column to find the percent for each of them
So the formula to find the percent is
Total Pay for Sanjay / Sum of Totalpay * 100
i.e: =B2/SUM($B$2:$B$6)*100
Drag the formula to get the result for all of them
In this formula value, 100 is repeated
So let’s store the value 100 in a memory
Click on Define Name
A New Name window will open
Name it as “percent“
Under Refers to 100
Click on OK to store the value 100 with name percent
Now in place of 100, you can use the name “percent “
=B2/SUM($B$2:$B$6) * percent
You can also store the entire formula
Here’s how:
Open the Define Name window
Under Refers add the formula : ” =B2/SUM($B$2:$B$6) * 100 “
and name it as “ Find_total_pay_percent “
Click on ok
That’s it
Now in cell E2, you can place the name we created to get the same result
Till now we learned :
How to name a cell
How to name a range of cells
How to store a single value as the name
How to store formula as a name
Now let’s understand
Before we start let me explain to you what I mean by storing a table of data within a memory.
Looking at Table 1 we need to find the values for the employees in Table 2
Yes, you can use the simple VLOOKUP function
Now let’s say I am deleting Table 1 but I still need the output in Table 2
In this case, you need to store the values of Table 1 somewhere in a memory
Now let’s understand how:
Press the keyboard shortcut : Ctrl + Alt + F3
To open the Define name window
Let’s name it as Table1
Under Refers we need to add the value of Table 1 in an Array format
To add a group of data in an Array format we need to you curly brackets
So will say ={
Now let’s add the first value Sanjay
={“Sanjay”
Next, we need to add the Paid hours and Total pay for Sanjay
To move to the next column we need to use a comma
={“Sanjay”,26,260
Now we need to add the record for Sachin
To move to the next Row we need to use Semicolon
So your final output should look like :
={“Sanjay”,26,260; “Sachin”,18,180; “Asif”,22,220; “Asifa”,16,160; “Suraj”,24,240}
That’s It
You have stored the table in the memory and named it as Table 1
Now go back to Table 2 replace the formula
That’s it
Over to you
What do you think
Are the Excel Defined Names useful?
Or
If you have something to add to the list
Or
Do you have any questions?
Let me know by leaving a comment below
Leave a Reply