Excel Tip #17 - Shortcut to Naming Cells or Ranges and Why You Should Name Excel Cell Ranges

Suppose you have a 5x5 Microsoft Excel table. On the top row you have the months of the year beginning with January through May. Then on the left hand side you have stock ticker symbols including but not limited to AAPL, GOOGL, and WFC. In the heart of all of this you have the stock prices of the respective month and ticker.


Your goal is to find the average price for GOOGL in the five months beginning in January.

The simple answer would be to type in the =average formula and drag your mouse over the row beginning with GOOGL. Maybe you are somewhat more Excel savvy and directly type in =average(D5:H5) instead.


Let me introduce to you a third, more efficient and flexible method. Suppose you name that range from D5:H5 "GOOGL". Going forward all you have to do is type in =average(GOOGL). If you are adding more months just make sure you include the additional months in the cell name.


Now, it is quite easy to highlight five cells and type in "GOOGL" in the box to the left of your formula toolbar.

If you have a table that is 100x100, that can become tedious. What is an easy and quick shortcut to naming all of the columns and rows with their headers?

The answer is to highlight the table and hold CTRL+SHIFT+F3 to name the columns and rows based on headers. You will be prompted with the 'Create Names from Selection' box. The default is the 'Top Row' and 'Left Column'. In our example, this means the columns with header 'Jan' will be named 'Jan' and the row 'AAPL' will be named 'AAPL'.


Let's say that you forgot what cells ranges in Excel you named and don't want to duplicate names. Hit CTRL+F3 and you will see all the cells and cell ranges you've named already along with which cells are being selected as part of that name tag.


Quick Summary

1) Quickly sum cell ranges using the cell name in replacement of the cell range
2) Highlight table and hold CTRL+SHIFT+F3 to name the columns/rows based on headers
3) Use shortcut CTRL+F3 to bring up the 'Name Manager' to view named cells ranges

For more excel tips check out the #1 best selling e-commerce book below.



Labels: