Whenever I work with spreadsheets, there’s no escaping the fact that I have to work with a lot of ranges.

Some of these ranges I have to reuse time and time again to create different calculations!

Good thing there are Named Ranges in Excel!

By using a Named Range, you can make your formulas much easier to understand and maintain.

You can define a name for a cell range, function, constant, or table.

Let us work with the following table below.  We want to populate the Euro Amounts and the Sum of the USD columns:

Create a Named Range in Excel

Let us go over how to do this in the tutorial below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Let us create our first Named Range. 

Highlight the USD Amounts and type in the Name Box a name: Amounts

Make sure that there are no spaces.  Press Enter.

Create a Named Range in Excel

 

Let us do the same to our Euro Exchange Rate. Highlight the exchange rate, and type in the Name Box a name: EuroRate

Press Enter.

Create a Named Range in Excel

 

STEP 2: Let us see our Named Ranges in action!

To calculate the Euro Amount column, type in: =A9 * EuroRate.

This will use our EuroRate Named Range!

Create a Named Range in Excel

Drag your formula below to the rest of the column to get the amounts calculated:

Create a Named Range in Excel

 

STEP 3: To calculate the sum of the Sum of USD column, type in: =SUM(Amounts).

This will use our Amounts Named Range!

Create a Named Range in Excel

And now you have your total!

Create a Named Range in Excel

 

STEP 4: If you want to check all the Named Ranges you created, go to Formulas > Defined Names > Name Manager

Create a Named Range in Excel

You can edit your Named Ranges in here or delete them as well.

Create a Named Range in Excel

 

How to Create a Named Range in Excel

Helpful Resource:

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Remove Duplicates in an Excel Table When you have duplicates values within your Excel Table there is a quick and easy way to remove those values. The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry,...
Excel’s Subtotal Feature Excel´s Subtotal feature is a great way to automatically insert a Sum/Count/Average/Max/Min subtotal to your data set with a press of a button. This feature is located under the Excel Data tools menu: Data > Subtotal.  To insert this feature you need to follow these quick s...
Add Custom Symbols With Your Numbers in Excel A cool feature within Excel is the ability to format a cell's value by pressing CTRL + 1 on any cell. This brings up the Format Cells dialogue box and under the Custom category you can customize the Type to whatever you like and even insert symbols! There are four formattin...
Unpivot Data Using Excel Power Query Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013: Click to see tutorial on how to install Power Query in Excel 2013 Click to see tutorial on how to install Power Query in Excel 2010 In...