This guide will show you how to insert or display a named range on another sheet in Excel.
It is very challenging to remember and manage multiple cells in one sheet. Imagine how tedious and difficult it would be if you wanted to access these cells in a new spreadsheet. A quick solution is by using named ranges. However, it is essential to remember that this feature is not available for Excel Web App. But don’t worry, you can create named cells in the desktop client and then use that name to embed from the Excel Web App.
A named range is an assigned name that refers to a group of cells or data sets. They are often used to avoid hard-coded values appearing in formulas, making formulas easier to understand. They are also helpful for data validation, hyperlinks, and dynamic ranges.
Let us start by creating named ranges!
How to Create Named Ranges
- First, select a range of cells.
- Click the text box in the upper-left corner and type your desired named range. In this example, let’s input “data.” This will be the named range for your selected group of cells. Hit ‘ENTER‘.
You can also add, edit and delete your named ranges by opening the Name Manager. You can access it by either pressing CTRL+F3 or go to Formulas > Name Manager.
Rules for Creating Names
When creating named ranges, below are a few rules that we need to remember:
- The first character can be a letter, underscore (_), or backslash (\).
- The remaining characters in the name can be letters, numbers, periods, or underscore characters.
- You cannot use names that represent cell references. For instance, you can’t use A1 or DD5 as these are also cell references.
- You cannot use spaces such as “Chocolate Cookie.” If you wish to combine two words, you can remove the space or use an underscore. For example, “ChocolateCookie” or “Chocolate_Cookie.”
- You cannot use operator symbols (+, –, *, /, <, >, &).
- You cannot use names that Excel uses internally, such as Auto_Activate, Auto_Close, Database, Extract, Print_Area, etc.
- The letters ‘C’ and ‘R’ are not allowed as Excel uses them as selection shortcuts. Names are not case sensitive, so the small letters ‘c’ and ‘r’ are not allowed as well.
- You can create the named ranges up to 255 characters only.
Once you’re done naming the ranges, use those names in formulas instead of using a constant value or cell references. You can create dynamic formulas as well. Another thing you can do is type the name and quickly go to that named range of cells without having to browse through the entire workbook.
That’s it! You can now access data in the spreadsheet without thinking about the cell references.
A Real-Life Example of Inserting or Displaying Named Range on Another Sheet in Excel
Let’s look at a real-life example of inserting or displaying a named range on another sheet in Excel.
In this example, imagine you are a cookie store business owner who maintains two spreadsheets – an inventory summary report and a sheet containing all data. The content of the summary report depends on the data from another sheet. Here’s a sample of a sheet with no named range (i.e., normal cell ranges in the formulas).
It does look confusing, doesn’t it? You may need to go back and forth between the sheets to ensure that you are referring to the correct cells. By using named ranges, you can efficiently compute the total number per cookie flavor without thinking about the cell references. It is more readable and easier to understand, especially if your business partner or colleague needs to access the sheet as well.
Once you are ready and understand named ranges, we can jump into inserting or displaying named ranges on another sheet together!
You can make a copy of the spreadsheet above using the link attached below.
How to Insert or Display Named Range on Another Sheet in Excel
This section will take you through steps that can help you insert or display the named range on another sheet in Microsoft Excel. This step-by-step guide below will show you how to access the cookie inventory, as mentioned in the example earlier.
Follow these steps to insert or display a named range on another sheet:
- First, create a table of your inventory on a second sheet.
- Go back to your first sheet and highlight the active cell where you want to display your result.
- Next, simply type the equal sign ‘=‘ and an excel function. In this example, since we are counting the total number of cookies, type the word ‘SUM’. An auto-suggest box appears, displaying the syntax for SUM and a list of named ranges. Select the named range you need followed by a right parentheses ‘)‘.
- Hit ‘ENTER‘. You’ll notice that the sum for the chocolate cookies has been displayed without indicating which sheet to access.
- Finally, input the same formula for the other type of cookies, and you’ll get the final count of your inventory.
You can also create more functionalities by using the named ranges with different functions such as VLOOKUP, MIN, MAX, AVERAGE, etc.
That’s all you need to remember to start inserting and displaying named ranges on another sheet in Excel. This step-by-step guide shows how easy it is to access, maintain and develop multiple cells and formulas without having to worry about the cell references.
This feature is just one of many which help you improve your workflow using Excel. With so many other Excel functions and features, you can undoubtedly find options to enhance your Excel experience.
Are you interested in learning more about how Google Sheets can help you make more powerful spreadsheets? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.