How to use Excel range names efficiently in calculations

 Aug 29, 2016

A Microsoft Excel user can use range names in calculations which will create meaningful reports to management.

While names make calculations easier to understand, they also make the ranges absolute references. This means that you no longer can create a calculation and then copy it to the adjacent cells either in columns and/or rows.


All cells in the range A9:F9 have the same calculation because of the Quarter1 range name.

Rather than calculating each individual total on its own, we can use the tricks listed below in order to use range names efficiently in calculations.

Step 1:

Create the calculations in columns and rows first

  1. Select B9 cell
  2. Go to Formulas tab
  3. Double-click the AutoSum button
  4. Copy the formula to the range C9:F9
  5. Select F5 cell
  6. Go to Formulas tab
  7. Double-click the AutoSum button
  8. Copy the formula to the range F5:F8

Step 2:

Create the ranges names

  1. Select the range A4:E8
  2. Go to Formulas tab
  3. Go to Defined Names group
  4. Click the Create from Selection button


  5. Click OK
  6. Select the F4:F8 range
  7. Go to Formulas tab
  8. Go to Defined Names group
  9. Click the Create from Selection button
  10. Click OK
  11. All the ranges names are created and listed in the Name Manager dialog box.


Step 3:

Apply the ranges names to existing calculations

  1. Select all the calculations. In our case, select the B9:F9 and F5:F8 ranges


  2. Go to Formulas tab
  3. Go to Defined Names group
  4. Click the Define Name – Apply Names button
  5. Select all the names in the list EXCEPT Sales_Group
  6. Click OK


Now all the calculations will show the ranges names instead of cell references. See below.


If you would like some more information on Microsoft Excel training, take a look at New Horizons' Excel courses.

How do your Excel skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

How do
your Excel skills
stack up?

Grade your skills now

About the Author:

Magdalena Todor  

With over 20 years experience as a facilitator and university lecturer, Magda is one of our most senior and experienced trainers. With previous practical on the job experience as a project manager she embodies a brilliant balance of training experience and business knowledge. With every event she delivers, Magda takes a wholehearted approach to ensure every course is impactful, relevant and a genuinely positive learning experience for all.

Read full bio
top