How to simplify Excel formulas with “Cell Naming”…

This week’s post is about a little-known-feature in Microsoft Excel called “Cell Naming.”  If you ever use Excel, like I do, then this tip is for you!  This is one of those features that I always knew existed but I never actually took the time to try, until now.

Simply put, “Cell Naming” allows you to assign a name to a cell, or a range of cells, for easy reference.  

You can get to the ” Name Manager” from the ‘Formulas’ tab then by clicking on the “Name Manager” button. 

For example:  

Click on the image to get a clear view…


In the above example, you see an Excel worksheet which is 3 columns wide x 14 rows tall that lists sale prices of 10 items at 3 different stores (“Store1″,”Store2″,”Store3″).  Each column has a “SubTotal” at the bottom that adds up the prices of that store’s 10 items sale prices, respectively.  Finally, there is a cell that adds up the 3 different stores “SubTotals” to equal the “GrandTotal.”

Next to the spreadsheet is the “Defined Names” which show you all of your cells/ranges with their associated names.

The four “Defined Names” that I created are:

Store1ItemTotals = $E$4:$E$13
Store2ItemTotals = $F$4:$F$13
Store3ItemTotals = $G$4:$G$13
GrandTotalSales = Store1ItemTotals+Store2ItemTotals+Store3ItemTotals

This was just a simple example, but imagine if you have 1000′s of cells with complex formulas that you need to manage!  And instead of having to manually cross-reference which cells that each formula is calling (i.e “E14″), you can know exactly which is being called by the descriptive “Cell Names” that you’ve pre-applied (i.e. “Store1ItemTotals”).  

I hope this tip will help you save a great amount of time and energy when working with Excel in the future!

2017-07-27T00:01:03+00:00 October 31st, 2013|Uncategorized|

About the Author: