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+