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:
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!