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
Do you ever wish to master the excel shortcut keys?
If yes, then you are not alone.
This guide will show you everything you need to know about Excel shortcuts keys
The skill that will allow you to use the keyboard more than the mouse
Primarily the best strategies and core techniques
Also by the end of this post, I will show you the 3 vital tips that will help you to remember these shortcut keys in the
updated on:
Most of the shortcut keys are the combination of one or more modifier keys.
For e.g.:
The most common shortcut key:
Are in combination with the modifier key Ctrl
Similarly, more than 90% of the shortcut keys are in combination with three major modifier keys.
Ctrl, Alt and Shift keys
However, there are some key’s that are stand-alone keys for e.g.:
Function f, Tab ⇄ , Insert Insert key, etc
So let’s go through each of these key’s step by step
F1 Open Excel Help Window
For Instant support, use this key to open Excel help window
F2 Put The active cell in edit mode
This key can be used if you wish to edit anything within a cell. Use Esc key to come out of edit mode
F3 Open Paste Name Window
If a name is defined, then you can use this key to paste the defined name within a formula
F4 Repeat last action
Fill cell A1 with red color, then click on cell B1 and hit function F4 key to get exact same behavior OR Delete column A1, then select any other column and hit F4 to get exact same behavior
F4 Switch between Absolute, Row, Column, and Relative references
Within a formula, press function key F4 to make both column and row reference absolute $A$1, repeat F4 to make row reference absolute A$1, again repeat F4 to make column reference absolute $A1, repeat F4 to make it relative A1
F5 Open Go To window
Move the courser to the desired location by placing cell reference under References
F6 Switch between Split Window, Ribbon bar
Move the course within the split panes and ribbon bar clockwise
F7 Spell check
Use this key to check spelling in the active worksheet or selected range
F8 Extended selection mode
To select multiple cells press function key F8 and use the arrow key’s to select the desired range. Press F8 again to come out of the selection mode
F9 Recalculate the entire workbooks
Function key F9 will help you calculate the entire workbook. If the automatic calculation is turned off
F10 View menu bar key tips
Function key F10 will help you view menu bar key tips
F11 Create New Chart Sheet
From the selected data create a new chart sheet
F12 Shortcut key to open save as dialog box
Function key F12 will help you save the file as any ‘xyz’ name
Shift + F2 Add or edit a comment
You can add a comment or edit an existing comment within a cell
Shift + F3 Open Insert Function window
For a quick selection of function’s hit Shift + F3 to open Insert Function window
Shift + F5 Open Find and replace window
This key will display a search box that will help you find anything within a sheet or a workbook
Shift + F6 Switch between Split Window, Ribbon bar
Move the course within the split panes and ribbon bar
Shift + F7 Select multiple ranges within a sheet
This key will help you select multiple ranges of cells by using arrow keys or mouse
Shift + F9 Display additional option for a selected item
This key will show an additional short menu for the selected item within a workbook
Shift + F11 Shortcut key to add a new worksheet
If you press Shift + F11 a new sheet will get added to the left of the active sheet
Shift + → right arrow key
Extend the selection to the right
Shift + ← left arrow key
Extend the selection to left
Shift + ↑ up arrow key
Extend the selection to up
Shift + ↓ down arrow key
Extend the selection to down
Shift + Enter select a cell above after completing a cell entry
On pressing Enter key If you want to move the selection above the current cell use Shift + Enter key
Shift + Spacebar key to select the entire row
From active cell press Shift + Spacebar to select the entire row. To select multiple rows select multiple columns and hit Shift + Spacebar
Shift + Tab move the courser towards left
Use this key if you want to select the previous cell or previous option
Shift + Alt + → Right Arrow Group row or column
Use this key to group a range of Rows or Column
Shift + Alt + ← Left Arrow Ungroup row or column
Use this key to ungroup a range of Rows or Column
Alt key Help you view menu bar key tips
Use the Alt key to highlights all the respective shortcut on the ribbon bar
Alt + F1 Shortcut key to creating a chart
This key will help you create a chart from the selected data
Alt + F2 Shortcut key to open save as dialog box
This key will help you save the file as any ‘xyz’ name
Alt + F4 Shotcut key to close excel sheet
This key will help you close the active sheet
Alt + F10 Shortcut key to open selection pane
View the list of all objects, change their orders or visibility
Alt + A Shortcut key to open DATA Tab
This key will open the Data Tab and will display all the shortcut key within it
Alt + F Shortcut key to open FILE Tab
This key will open the File Tab and will display all the shortcut key within it
Alt + H Shortcut key to open HOME Tab
This key will open the Home Tab and will display all the shortcut key within it
Alt + M Shortcut key to open FORMAT Tab
This key will open the Format Tab and will display all the shortcut key within it
Alt + N Shortcut key to open INSERT Tab
This key will open the Insert Tab and will display all the shortcut key within it
Alt + P Shortcut key to open PAGE LAYOUT Tab
This key will open the Page Layout Tab and will display all the shortcut key within it
Alt + R Shortcut key to open REVIEW Tab
This key will open the Review Tab and will display all the shortcut key within it
Alt + W Shortcut key to open VIEW Tab
This key will open the View Tab and will display all the shortcut key within it
Alt + Enter Add new line within a cell
This key will allow you to add multiple lines of text within the same cell
Alt + = Add SUM function to the selected cell
Get a sum of a range using Alt + = sign
Alt + Spacebar Display control menu
This key will allow you to use all control options such as Restore, Move, Size, Minimize, Maximize and Close
Alt + Numeric keys Add Symbols that are not on your keyboard
Allow you to add symbols that are not on your keyboard, you may also use multiple numbers to get a variety of options
Alt + Tab Switch between all open applications towards the right
This key will allow you to switch between all open applications towards the right
Alt + Shift + Tab Switch between all open applications towards left
This key will allow you to switch between all open applications towards left
Alt + Shift + F1 Add new worksheet
This key will allow you to add a new worksheet to the left of the active sheet
Ctrl + F2 Display Print Preview
This key will display print preview under the print tab
Ctrl + F3 Open Name Manager
This key will display a Name Manager window
Ctrl + F4 Close Workbook
This key will help us close the active workbook
Ctrl + F5 Restore the window size
This key will help you restore down the selected workbook
Ctrl + F6 Switch between open workbooks
If you have multiple workbooks open; then use this key to switch between each them
Ctrl + F7 Move the workbook
If the workbook is not in maximized mode; then Ctrl + F7 key will help us move the worksheet
Ctrl + F8 Activate resize mode when a workbook is not maximized.
From non maximized mode press Ctrl + F8 to activate the Size mode, and then use the arrow key to resize
Ctrl + F9 Minimize the active workbook
This key will help you minimize the active workbook
Ctrl + F10 Maximize the active workbook
This key will help you maximize the active workbook
Ctrl + F11 Add new Macro Sheets
A new sheet will get added as Macro1 to the left of the active sheet
Ctrl + F12 Open any saved workbook
This key will help you open already saved workbook
Ctrl + 0 Hide Selected Columns
Select the columns you want to hide and hit Ctrl + 0
Ctrl + 1 Open Format Cells window
Use this key to Format the selected Cell
Ctrl + 2 Apply Bold effect to the selected cell
Use this key to remove or apply a bold effect to the selected cell
Ctrl + 3 Apply Italic effect to the selected cell
Use this key to remove or apply Italic effect to the selected cell
Ctrl + 4 Apply Underline effect to the selected cell
Use this key to remove or apply Underline effect to the selected cell
Ctrl + 5 Apply Strikethrough effect to the selected cell
Use this key to remove or apply a strikethrough effect to the selected cell
Ctrl + 9 Hide Selected Rows
Select the Rows you want to hide and press ctrl + 9
Ctrl + A Select All Cells
This key will help you select all the cell within a worksheet
Ctrl + B Apply Bold effect to the selected cell
Use this key to remove or apply a bold effect to the selected cell
Ctrl + C Copy the selected cells
Use this key to the copy the selection in the clipboard, so that it can be pasted at any desired location
Ctrl + D Create a duplicate copy
This key will help you create a duplicate copy of the topmost cell
Ctrl + E Activicate Flash Fill
Enter a few examples and then use this key to automatically fill in the value for the rest of the cells
Ctrl + F Open Find and replace window
This key will display a search box that will help you find anything within a sheet or a workbook
Ctrl + G Open Go To window
Move the courser to the desired location by placing cell reference under references
Ctrl + H Open Find and replace window
A Replace tab will be active, that will help you find and replace anything within a sheet or workbook
Ctrl + I Apply Italic effect to the selected cell
This key will help you find text within a workbook
Ctrl + K Open Insert hyperlink window
Use this key to link any documents for quick access
Ctrl + L Open Create Table window
This key will help you create a Table from the selection, that will help you organize as analyze data effectively
Ctrl + N Create a new workbook
From the active sheet use this key to create a new workbook
Ctrl + O Open a spreadsheet
From Active sheet use this key to open already saved workbook
Ctrl + P Display Print option under File Tab
Use this key to set a print area and print the document
Ctrl + Q Display Quick Analysis option from the selected data
Use this key to quickly analyze the data; using excel’s most useful tools, such as charts, color-coding, tables, formulas, etc.
Ctrl + R Fill Right
Use this key to take the data from the left and fill it to the right
Ctrl + S Save Workbook
Use this key to save the unsaved data within a workbook
Ctrl + T Open Create Table window
This key will help you create a Table from the selection, that will help you organize as analyze data effectively
Ctrl + U Apply Underline effect to the selected cell
Use this key to remove or apply Underline effect to the selected cell
Ctrl + V Paste the Copied data
Use this key to Paste the copied data from selection to the desired location
Ctrl + W Close active workbook
If you have multiple workbooks open, you may use this key to close the active workbook
Ctrl + X Remove the selected cells
Use this key to the remove the selection and put it to the clipboard, so that it can be pasted at any other location
Ctrl + Y Repeat the last action
Use this key if you wish to redo or repeat the last action
Ctrl + Z Undo the last action
Use this key if you want to undo the last action performed
Ctrl + + Insert Cell, Row, or Column
From the active cell use this key to insert a cell down, also you can use this key to insert row or column by selecting either of them
Ctrl + - Delete Cell, Row, or Column
From the active cell use this key to delete a cell, also you can use this key to delete row or column by selecting either of them
Ctrl + ' Copy the value from a cell above
Use this key to copy the value from a cell above with no formatting effects
Ctrl + ` View all the formulas within a sheet
Use this key to view all the formulas used within an active sheet
Ctrl + ; Insert current date.
Use this key to insert current date within a selected cell
Ctrl + / Select the array range
From the active array, cell use this key to select the array range for the selected cell
Ctrl + \ Select all the cell that doesn’t match the active cell value
From the selected range of rows use this key to select all the cell that doesn’t match the active cell value
Ctrl + [ Select all the values based on active formula cell
Use this key to know all the values that the active formula cell is dependent on
Ctrl + ] Select all the formulas based on active cell value
Use this key to know all the formulas within a sheet that is dependent on the active cell value
Ctrl + Home Move the cursor to top left cell Or cell A1
From any cell, if you want to quickly move the cursor to top-left cell Or cell A1 use this key
Ctrl + End Move the cursor to the end of the lower-right most used cell
Use this key to select the last occupied cell within a sheet
Ctrl + Arrow Select last occupied cell in the same column or row as the active cell is
Use this key to select the last occupied cell in the same column or row as the active cell is
Ctrl + Enter Keep the active cell selected
Use this key, if you wish to keep the active cell selected after entering any value
Ctrl + PageDown From the active sheet move to next sheet
Use this key to move to the next sheet from the active sheet
Ctrl + PageUp From the active sheet moves to the previous sheet
Use this key to move to the previous sheet from the active sheet
Ctrl + Spacebar Select the entire column
From the active cell use this key to select the entire column. Select multiple cells to select multiple columns
Ctrl + Tab Switch between workbooks
If you have multiple workbook open; use this key to switch between each of them
Ctrl + Shift + A Display function arguments
Use this key inside a function to display a function argument within a cell
Ctrl + Shift + F Open Format Cells window with Font tab active
Use this key to Format the selected Cell
Ctrl + Shift + L Add Filter
Use this key to apply a filter to the selected data or a cell
Ctrl + Shift + O Select all Comments
Use this key to select all the comments within an active sheet
Ctrl + Shift + P Open Format Cells window
Use this key to Format the selected Cell
Ctrl + Shift + U Expand the formula bar
Use this key to expand or collapse the formula bar
Ctrl + Shift + : Insert current time
Use this key to insert current time within a selected cell
Ctrl + Shift + ~ Apply General Number Format
Use this key to apply a general number format to the selected cell
Ctrl + Shift + ! Apply Number Format
Use this key to apply a number format to the selected cell
Ctrl + Shift + @ Apply Time Format
Use this key to apply time format to the selected cell
Ctrl + Shift + # Apply Date Format
Use this key to apply a date format to the selected cell
Ctrl + Shift + $ Apply Currency Format
Use this key to apply currency format to the selected cell
Ctrl + Shift + % Apply Percentage Format
Use this key to apply the percentage format to the selected cell
Ctrl + Shift + ^ Apply Scientific Number Format
Use this key to apply Scientific number format to the selected cell
Ctrl + Shift + & Apply outline border
Use this key to apply the outline border from the selected cell
Ctrl + Shift + * Select all the data around the active cell
Use this key to select all the data around the selected cell; you may also use this key to select an entire PivotTable report
Ctrl + Shift + ( Unhide the hidden row
Use this key to unhide any hidden row within the selection
Ctrl + Shift + ) Unhide the hidden columns
Use this key to unhide any hidden columns within the selection
Ctrl + Shift + - Remove outline border
Use this key to remove the outline border from the selected cell
Ctrl + Shift + + Insert Cell, Row, or Column
From the active cell use this key to insert a cell down, also you can use this key to insert row or column by selecting either of them
Ctrl + Shift + " Copy the value from a cell above
Use this key to copy the value from a cell above with no formatting effects
Ctrl + Shift + Home Extend the selection to the top left cell Or till cell A1
From the active cell use this key to extend the selection to the top-left cell or till cell A1
Ctrl + Shift + End Extend the selection to the last used lower-right cell
This key will help you select all the cells from the active selected cell to the last used lower-right cell
Ctrl + Shift + Arrow Extend the selection to the last occupied cell in the same column or row as the active cell is
This key will help you select all the cells from the last occupied cell in the same column or row as the active cell is
Ctrl + Shift + Spacebar Select All Cells
Use this key to select all the cell that contains data, repeat the process the second time to select the data with headers (if it’s a table), repeat the process the third time to select an entire cell within a worksheet
Ctrl + Shift + Tab Switch between workbooks
If you have multiple workbook open; use this key to switch between each of them
Ctrl + Shift + PgDn Select multiple sheets towards right
Use this key to make multiple selections towards right from the active sheet
Ctrl + Shift + PgUp Select multiple sheets towards left
Use this key to make multiple selection towards left from the active sheet
Ctrl + Alt + F2 Open any saved workbook
This key will help you open already saved workbook
Ctrl + Alt + F3 Open Define name window
Use this key to define and apply new name range
Ctrl + Alt + F4 Close the active sheet
This key will help you close the active sheet
Ctrl + Alt + F9 Calculate all the worksheet within a workbook
Use this key to recalculate the formula, in all sheet within an active workbook
Ctrl + Alt + V Open Paste Special Window
Copy any cell and use this key to open Paste Special Window
So now that you have the complete list…
Let me share the 3 vital tips that will help you remember these excel shortcut keys in the long run?
Look for an underline ( _ ) sign below words
For e.g: If you look at the Paste Special Window
You will see an underlined letter below each word. These letters denote the shortcut key.
If you will press “F” key formulas will get selected
Or
If you press “V” values will get selected
You got the point
In some cases pressing key directly may not work. Try using it with the modifier keys like Ctrl , Alt or Shift
Use Alt key
From an excel sheet if you press Alt key a shortcut key assigned to each of the tabs will be displayed.
If you press any of these key all the shortcut keys within the tab will be displayed
Mouse hover
There are some direct shortcut keys assigned to most of the commands.
If you hover the mouse over any of the commands a shortcut key assigned for that command will be displayed.
For e.g: If you hover a mouse on an AutoSum command under Home tab a shortcut key associated to it i.e Alt + = will be displayed
That’s it.
So now it’s your turn
Do you like the 3 tips that I shared?
Which modifier key would you like to use first?
Or
Do you have anything that could be an addon to this list?
Let me know by leaving a comment below
Hello to every one, it’s in fact a good for me to go to see this site, it
consists of valuable Information.
I’m glad you found it useful!
Hello Shailesh,
Can you add search bar in this
It will help to search by keyword which I want to refer, now I have to scroll whole document to search a particular function.
Thank you
Sure Chitesh
Leave a Reply