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:


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


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Extract Numbers Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.  Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.One of the cool uses of Flash Fill is extracting the numbers from your text au...
How To Create A Custom List In Excel  A Custom List in Excel is very handy to fill a range of cells with your own personal list. It could be a list of your team members at work, countries, regions, phone numbers or customers.The main goal of a custom list is to remove repetitive work and manual errors i...
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,...
Dynamic Data Validation List What´s a dynamic data validation drop down list in Excel, you say?Well, as you add new data into your Excel Table, your drop down list automatically gets updated.That is a cool feature and it means that you do not need to update your data validation source reference each ...