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
- Select B9 cell
- Go to Formulas tab
- Double-click the AutoSum button
- Copy the formula to the range C9:F9
- Select F5 cell
- Go to Formulas tab
- Double-click the AutoSum button
- Copy the formula to the range F5:F8
Step 2:
Create the ranges names
- Select the range A4:E8
- Go to Formulas tab
- Go to Defined Names group
- Click the Create from Selection button
- Click OK
- Select the F4:F8 range
- Go to Formulas tab
- Go to Defined Names group
- Click the Create from Selection button
- Click OK
- All the ranges names are created and listed in the Name Manager dialog box.
Step 3:
Apply the ranges names to existing calculations
- Select all the calculations. In our case, select the B9:F9 and F5:F8 ranges
- Go to Formulas tab
- Go to Defined Names group
- Click the Define Name – Apply Names button
- Select all the names in the list EXCEPT Sales_Group
- 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 NowNext up:
- Beginner in design? Here is what you need to know
- It is not about 'Traditional' OR 'Social' selling … It is about 'Traditional' AND 'Social' selling
- ITIL® RACI Matrix
- Our most popular Excel blog posts
- Is Emotional Intelligence B.S.*?
- Are you using these features in Microsoft Word?
- SQL Server 2016 – Always Encrypted
- Great managers provide great feedback
- What makes you healthy can also make you a good Social Seller (Infographic)
- How-to: Validate data based on the value of a different cell in Excel
Previously
- Beginner in design? Here is what you need to know
- Designer’s guide on the Golden Ratio – Why it matters
- Happy Anniversary
- PowerShell Basics Series – PowerShell Pipelining
- Win the next productivity award with Microsoft Project
- 10 steps to becoming a terrific facilitator
- Managing performance – The good and the bad
- How-to: Import Appointments from Excel to SharePoint 2013 Calendar
- Master the art of masking in Photoshop
- Learn to lead and inspire people with these TED Talks