How To Change Series Names In Excel
What'southward in the name?
If you are working with Excel spreadsheets, information technology could mean a lot of time saving and efficiency.
In this tutorial, you'll learn how to create Named Ranges in Excel and how to use it to save fourth dimension.
Named Ranges in Excel – An Introduction
If someone has to call me or refer to me, they will use my name (instead of saying a male is staying in and so and and so place with then and so superlative and weight).
Right?
Similarly, in Excel, you can requite a name to a cell or a range of cells.
Now, instead of using the prison cell reference (such equally A1 or A1:A10), you can simply use the name that you assigned to it.
For case, suppose you have a data gear up as shown below:
In this data set, if you have to refer to the range that has the Date, you will take to use A2:A11 in formulas. Similarly, for Sales Rep and Sales, you will take to use B2:B11 and C2:C11.
While it's alright when yous only take a couple of data points, but in example you huge complex data sets, using jail cell references to refer to data could be time-consuming.
Excel Named Ranges makes it piece of cake to refer to data sets in Excel.
You tin create a named range in Excel for each data category, so use that name instead of the cell references. For example, dates can exist named 'Engagement', Sales Rep information can exist named 'SalesRep' and sales data can be named 'Sales'.
You lot can also create a proper name for a unmarried cell. For example, if you lot have the sales commission percentage in a cell, you can proper name that cell as 'Committee'.
Benefits of Creating Named Ranges in Excel
Here are the benefits of using named ranges in Excel.
Employ Names instead of Jail cell References
When yous create Named Ranges in Excel, you can employ these names instead of the prison cell references.
For example, you can use =SUM(SALES) instead of =SUM(C2:C11) for the above data set.
Accept a look at ṭhe formulas listed below. Instead of using cell references, I have used the Named Ranges.
- Number of sales with value more than 500: =COUNTIF(Sales,">500″)
- Sum of all the sales done by Tom: =SUMIF(SalesRep,"Tom",Sales)
- Committee earned past Joe (sales past Joe multiplied past commission percentage):
=SUMIF(SalesRep,"Joe",Sales)*Commission
You would agree that these formulas are like shooting fish in a barrel to create and easy to understand (specially when you share it with someone else or revisit it yourself.
No Need to Become Back to the Dataset to Select Cells
Some other significant benefit of using Named Ranges in Excel is that y'all don't need to go back and select the prison cell ranges.
You can just type a couple of alphabets of that named range and Excel volition show the matching named ranges (as shown below):
Named Ranges Make Formulas Dynamic
Past using Named Ranges in Excel, yous can make Excel formulas dynamic.
For example, in the instance of sales commission, instead of using the value two.5%, you can employ the Named Range.
Now, if your company later decides to increase the commission to iii%, you can just update the Named Range, and all the calculation would automatically update to reverberate the new commission.
How to Create Named Ranges in Excel
Here are three ways to create Named Ranges in Excel:
Method #1 – Using Define Name
Here are the steps to create Named Ranges in Excel using Ascertain Name:
This will create a Named Range SALESREP.
Method #2: Using the Name Box
- Select the range for which you want to create a name (exercise not select headers).
- Go to the Proper noun Box on the left of Formula bar and Blazon the name of the with which you lot want to create the Named Range.
- Note that the Proper noun created here volition be available for the entire Workbook. If you wish to restrict it to a worksheet, use Method 1.
Method #3: Using Create From Option Option
This is the recommended mode when you have data in tabular form, and y'all want to create named range for each column/row.
For instance, in the dataset below, if you want to quickly create three named ranges (Appointment, Sales_Rep, and Sales), then you can use the method shown beneath.
Here are the steps to rapidly create named ranges from a dataset:
This will create iii Named Ranges – Engagement, Sales_Rep, and Sales.
Annotation that it automatically picks upward names from the headers. If there are whatsoever space between words, it inserts an underscore (as you tin't accept spaces in named ranges).
Naming Convention for Named Ranges in Excel
There are sure naming rules you need to know while creating Named Ranges in Excel:
- The kickoff character of a Named Range should be a letter and underscore character(_), or a backslash(\). If it's anything else, it will prove an error. The remaining characters tin can exist letters, numbers, special characters, menstruation, or underscore.
- You tin non utilize names that as well represent jail cell references in Excel. For example, you can't use AB1 as it is besides a prison cell reference.
- You can't employ spaces while creating named ranges. For instance, you can't accept Sales Rep equally a named range. If you want to combine two words and create a Named Range, utilise an underscore, menstruation or uppercase characters to create it. For example, you can have Sales_Rep, SalesRep, or SalesRep.
- While creating named ranges, Excel treats uppercase and lowercase the same way. For example, if you create a named range SALES, and then you will not be able to create another named range such equally 'sales' or 'Sales'.
- A Named Range can be up to 255 characters long.
Too Many Named Ranges in Excel? Don't Worry
Sometimes in large data sets and complex models, you may end up creating a lot of Named Ranges in Excel.
What if you don't remember the proper name of the Named Range you created?
Don't worry – here are some useful tips.
Getting the Names of All the Named Ranges
Here are the steps to get a list of all the named ranges you created:
This will give you a listing of all the Named Ranges in that workbook. To use a named range (in formulas or a cell), double click on it.
Displaying the Matching Named Ranges
- If you accept some thought about the Proper name, type a few initial characters, and Excel will show a drop down of the matching names.
How to Edit Named Ranges in Excel
If you take already created a Named Range, you can edit it using the post-obit steps:
Useful Named Range Shortcuts (the Power of F3)
Here are some useful keyboard shortcuts that volition come up handy when yous are working with Named Ranges in Excel:
- To get a listing of all the Named Ranges and pasting it in Formula:F3
- To create new name using Name Managing director Dialogue Box:Control + F3
- To create Named Ranges from Selection: Control + Shift + F3
Creating Dynamic Named Ranges in Excel
And then far in this tutorial, we have created static Named Ranges.
This means that these Named Ranges would always refer to the same dataset.
For example, if A1:A10 has been named as 'Sales', it would ever refer to A1:A10.
If yous add more sales data, then you would have to manually go and update the reference in the named range.
In the world of ever-expanding data sets, this may end upward taking up a lot of your fourth dimension. Every time you lot get new information, you may take to update the Named Ranges in Excel.
To tackle this issue, we can create Dynamic Named Ranges in Excel that would automatically business relationship for additional data and include information technology in the existing Named Range.
For example, For instance, if I add together two additional sales information points, a dynamic named range would automatically refer to A1:A12.
This kind of Dynamic Named Range can be created by using Excel INDEX function. Instead of specifying the cell references while creating the Named Range, nosotros specify the formula. The formula automatically updated when the information is added or deleted.
Permit's see how to create Dynamic Named Ranges in Excel.
Suppose we have the sales data in jail cell A2:A11.
Here are the steps to create Dynamic Named Ranges in Excel:
-
- Become to the Formula tab and click on Define Name.
- In the New Name dialogue box blazon the following:
- Name: Sales
- Scope: Workbook
- Refers to: =$A$2:Alphabetize($A$2:$A$100,COUNTIF($A$two:$A$100,"<>"&""))
- Click OK.
- Become to the Formula tab and click on Define Name.
Done!
Yous now have a dynamic named range with the name 'Sales'. This would automatically update whenever you add together data to it or remove data from information technology.
How does Dynamic Named Ranges Work?
To explain how this work, yous need to know a bit more about Excel INDEX function.
Most people employ INDEX to return a value from a list based on the row and column number.
But the Index function likewise has another side to it.
It can exist used to return a prison cell reference when it is used as a part of a cell reference.
For example, here is the formula that nosotros have used to create a dynamic named range:
=$A$two:Index($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&"")) INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&"") –> This function of the formula is expected to return a value (which would be the tenth value from the list, considering in that location are 10 items).
Nonetheless, when used in forepart of a reference (= $A$2: Index($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&""))) it returns the reference to the cell instead of the value.
Hence, hither it returns =$A$two:$A$11
If we add 2 additional values to the sales cavalcade, information technology would then return =$A$2:$A$13
When you add together new information to the list, Excel COUNTIF function returns the number of not-blank cells in the data. This number is used by the Alphabetize part to fetch the jail cell reference of the final detail in the list.
Annotation:
- This would merely work if there are no blank cells in the information.
- In the case taken above, I have assigned a big number of cells (A2:A100) for the Named Range formula. You tin adjust this based on your data set.
You lot tin can also use OFFSET part to create a Dynamic Named Ranges in Excel, however, since OFFSET function is volatile, information technology may atomic number 82 a slow Excel workbook. INDEX, on the other hand, is semi-volatile, which makes information technology a meliorate choice to create Dynamic Named Ranges in Excel.
You may likewise like the following Excel resources:
- Costless Excel Templates.
- Free Online Excel Preparation (7-Office Online Video Grade).
- Useful Excel Macro Code Examples.
- 10 Advanced Excel VLOOKUP Examples.
- Creating a Drib Downwardly List in Excel.
- Creating a Named Range in Google Sheets.
- How to Reference Another Sheet or Workbook in Excel
- How to Delete Named Range in Excel?
Source: https://trumpexcel.com/named-ranges-in-excel/
Posted by: thomasprel1989.blogspot.com
0 Response to "How To Change Series Names In Excel"
Post a Comment