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