Hey Excellers, thanks for joining me for another #formulafriday. Today I want to share my top 3 reasons to use named ranges in your formulas. There are many benefits to using named ranges in your Excel formulas. This blog post will discuss three of the most important reasons why you should start using named ranges today. Named ranges can help you save time and improve accuracy in your data analysis, and they are straightforward to use. Let’s take a closer look at each of these benefits. Starting with the basics how to create named ranges in Excel.
What Is A Named Range?
A named range is a name that refers to either a cell, a range of cells, a constant value or a formula. Named ranges can then be used in a formula to replace a constant value or a cell reference. So, for example, if you have a constant value in a cell such as Exchange Rate, you can name that cell that contains your exchange rate value as ‘Exchange Rate’ instead of using A2 in your formulas.
What Are the Rules?
Yes, there are a few rules about naming conventions, but they are not too confusing!!
- What characters? The first character of a name must be a letter; an underscore character (_), or a backslash (\). The remaining characters in the name can be letters, numbers, periods, and underscore characters. Tip: You cannot use the uppercase and lowercase characters “C”, “c”, “R”, or “r” as a defined name because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
- Cell references are disallowed. Names cannot be the same as a cell reference, such as A2
- No Spaces, please. Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, for example Sales_Tax or Second.Quarter.
- Name length. A name can contain up to 255 characters.
- Case sensitivity. Names can contain uppercase and lowercase letters. So if you create a name called SALES_TAX and then try to create another sales_tax you will be prompted to create another unique name.
How To Create A Named Range
There are a few ways to create a named range in Excel. Here are three ways as a lightening fast guide on how to generate those names.
Use the Name Box.
- Select the range of cell or cell you want to name.
- Click in the Name Box in the top left of the workbook.
- Name your cell or range of cells.
- Hit Enter.
Create Names From Worksheet Labels.
- Select the labels and the cells you want to name.
- On the Formula Ribbon – Defined Names – Create From Selection.
- Select the location of the label you want to use.
- Hit Ok
- Your selected name will appear in the Name Box.
Use The Name Manager.
- Formula Tab – Defined Names – Define Name
- Give your range a name
- Type in your value for this named range or use the selector to select a cell that contains your named data.
- Hit Ok
Ok, so here are my top three reasons for using those named ranges in a formula.
Your Formulas Will Make More Sense.
Names make your formulas more understandable. Let’s look at my monthly sales summary in Euros. The total figure at the end of the summary is required to be converted to GBP to send to Head Office. We can click on the cell that contains the Total Sales In GBP calcualation and can easily see that the Named Range of Exchange_Rate is multipled by the value in cell B16 which is the Total Sales Value. We could make the formula even more strightforward by naming cell B16 as Total Sales Value……… so readers would not have to look to see what number cell B16 is refering to….
You Can Easily Edit Your Formulas.
Let’s say that you need to change the Exchange Rate that the company is using this month. By using the Name Box to select the Exchange_Rate named range we can quickly navigate to the correct cell, and change the rate, which then populates anywhere we are using the Exchange_Rate reference in a formula. Nice and easy.
Lightening Fast Simple Formula Creation.
Once your ranges are named or defined then you can use them over and over again in formulas. Let’s return to the Sales Summary that we need to send over to Head Office. I have gone ahead and named some more ranges in the worksheet for Total Sales Summary and Total Sales Volumes. You can see in the Name Manager which range of cells they refer to.
I can now write super fast formulas using the names. Let’s look at the average unit price our Sales people are getting for our products.
So that’s my top 3 reasons for using named ranges with formulas. Hope you enjoyed today’s Formula Friday Excel Tip.
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.
