Awhile back we mentioned about the power of using named ranges for your calculations. Back then we showed how to create those names manually.
You also can use a short cut method to automatically create named ranges.
The exercise file is Names Automatic. The result file is Names Automatic Result.
Here is the spreadsheet we start with. Formulas have been created the traditional way, with cell references. Let's change it to use named ranges.
To save time creating the named ranges, we will let Excel create them for us. First, highlight the block of cells.
Then go to the Insert menu, select Names, then Create.
In the popup dialog box, select the Top Row and Left Column.
We can check the names by clicking the arrow at the right of the names box.
Alternatively, we could look at names by going to the Insert menu, select Names, then select Define.
No matter which way we view the named ranges, note that named ranges cannot have blank spaces, so Excel has automatically substituted an underscore for a blank.
One difference versus creating the names manually, besides being faster to create- in the automatic creation, the cell with the row or column heading is not included. That's OK- it should not matter.
We can then change all the formula cells, highlighted in blue below.
Here is what the formula view would look like:
Named ranges are a powerful feature and a good practice to use for larger spreadsheets in particular (we used a small example for demonstration purposes). Using Excel to create the names automatically can speed your work.
One other point. We also had showed you how to create formulas using labels. That is ok for simple spreadsheets like this one, but if there are multiple sheets or workbooks that will be linked together, we recommend using the structure of named ranges.