Hello Excellers, Friday again and I have a really quick, easy and really useful way to use named ranges you have already created into your formulas. If you need more details on creating named ranges I suggest you take a look at my blog posts below.
3 Reasons To Use Named Ranges In Your Excel Formulas
If you want to download the example Excel File used in this blog post then you can do so here
If you are happy with named ranges then let’s keep going…..
- Hit F3
- The name Manager Box will de displayed
- All of your Named Ranges will appear
- Select the name f the range you want included in your formula
- Hit Ok
Let’s take a look an example. I have two named ranges set up in my data set. These are Maximum_Discount and tax_rate.
Here is my data set up. I have my Sales Value in cells B5 to B9. I need to calculate Sales Tax based on the tax rate which I have in my worksheet as a named range of tax_rate.
- I have started my Formula in cell B5 = C5* ( notice that as my data is formated as an Excel Table, Excel has taken the column name and used it automatically in the formula)
- After I have type the first part of the formula – Hit F3 to bring up all of your Named Ranges
- You can quickly select any of your already created named ranges and hit Ok.
- A soon as I select tax_rate, Excel will populate all of the rows of my table automatically with the results of my formula.
- Let’s insert our Sales Plus tax Formula to complete the data set
If I want to calculate the maximum discount based on the on Sales Value, let’s insert a new column in our data table and call it Maximim Discount and begin building the formula.
- I have started my Formula in cell E5 = C5* (again as my data is formated as an Excel Table, Excel has taken the column name and used it automatically in the formula)
- After I have type the first part of the formula – Hit F3 to bring up all of your Named Ranges
- You can quickly select any of your already created named ranges and hit Ok.
- A soon as I select Maximum_Discount, Excel will populate all of the rows of my table automatically with the results of my formula.
This method is particularly useful if you have a lot of named ranges in your Excel worksheet which can be sometimes difficult to keep track of, in this way they are all brought up in front of you to choose from. Easy.
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
- Create A Micro Chart In Excel
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
- Use TRIM for a speedy data clean up
