Here is a neat feature in Excel that many people do not know about. Excel offers a alternate way to create formulas, by letting you use the column and row labels, rather than cell references or named ranges.
The exercise is in the Formula Labels file. The result is in the Formula Labels Result file.
First you need to set up Excel to accept labels in the formulas. Go to the Tools menu, then select Options.
Check the Accept labels checkbox near the bottom right. While your dialog box might look different based on your version of Excel, this feature should be available for all users with the 97 version and beyond.
Here is the spreadsheet we will work with.
Notice the first sum, which uses a traditional cell reference- =SUM(B3:D3).
Now lets change it to use the label for the row.
The formula is now =SUM(Units).
Think of the advantages:
You don't have to highlight cells to create a cell reference
You don't have to set up named ranges. These have their advantages, but for a simple workbook that is not being linked to other workbooks, you might not need all the features of named ranges.
The formula can be easy for someone else to follow
A trick with using this technique. Just like with using named ranges in formulas, you don't need to worry about case. Excel will automatically correct it. If your row label says Sales and you type in sales in the formula, Excel will change it to sales. You can give your pinky and the shift key a rest.
The example above uses the row labels. You can also use column labels. We could have created the sum instead as:
+Line A+ Line B+ Line C
An advantage of this formula is that you could then copy it to sum up the Sales, Cost of Sales and Gross Margins. The disadvantage is that it is not as intuitive to your reader as using Units.
Where the column labels could particularly be useful is where you have subtotals. Suppose for example, we had more product lines and grouped them by three product groups. We could then create the sum as:
+Group 1+ Group 2 + Group 3
Another place column references can come in handy is working with text. Suppose you had columns for the city, state and zip. You could use the column labels to put together a formula to create a combined cell with the city, state and zip combined.
You can also use row and column references together. This is great for just pulling out pieces of information from the worksheet. Suppose in our example, we wanted to find the sales for Line A. Our formula could be:
= Sales Line A
or
= Line A Sales
This could save you from having to use more complicated lookup formulas.
In closing, named ranges are very powerful, but if you have a simple worksheet, you might find it easier to work with the row and column labels instead.