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:


How to assign a Name to a single cell or a Range of Cells

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


Use Defined Names to quickly navigate the Sheet within the Workbook

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.


How to use Cell Reference with Defined Names:

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.


How to use Defined Names in Pivot Table, Data validation and Vlooup function, etc.

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.


How to Lookup a value from a database without using any lookup function like (Vlookup, Hlookup, Index, Match, etc)

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:

  1. Select the range A1:F6
  2. Press keyboard shortcut key Ctrl + Shift + F3 to open Create Name from Selection window
  3. Make sure Top row and Left column is checked
  4. Click on OK

Now that we have a name created, Let’s create a formula

  1. Select cell B8
  2. add = sign write the name Sachin
  3. add space and write weekday Fri
  4. Your formula should look like =Sachin Fri
  5. Press enter

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:

  1. Select cell C11
  2. Add formula =INDIRECT(A11) INDIRECT(B11)
  3. Press

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


How to store values and formulas in a memory

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

How we can store a table of data in a memory

Before we start let me explain to you what I mean by storing a table of data within a memory.

Table 1
Table 2

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