Excel Formulas Halfwit to Hero

The Tutorials
Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 
Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10

Chapter 8

Naming Cells and Ranges

You can clear up complex formulas and keep track of important cells using names. In this chapter, you learn how to apply them.

Exercise #1: Adding (and using) names

In this exercise you'll take a spreadsheet you've seen before and upgrade it by adding a few names.

  1. Begin by opening the Buy a new phone.xlsx spreadsheet. This is the same spreadsheet you used in the first exercise of Chapter 3, except now you're starting with the completed worksheet.

  2. Look at the formula in cell B8. Your first goal is to create a name for each cell reference in this formula. What cells do you need to name?

    The formula is =B6*B7 so you need to name cells B6 and B7.

  3. Start with cell B6. Look at what's inside it. Can you think of a good name for this cell?

    There are several good choices here. Usually, it's a good idea to use part of a caption or heading for the cell, if you have one. (This way, you're referring to your data with a consistent name, both on your worksheet and in your formula.) In this example, the caption next to the cell says "List price" so an obvious name choice is ListPrice or List_Price. Either one is equally as good, but if you use underscores you should be consistent and use them for all the names in your workbook.

  4. Now it's time to name the cell. Move to cell B6 and type your name in the Name Box at the top-left corner of the worksheet grid. When you're finished, press Enter.

  5. You can use the same process to name cell B7. A good name for this cell is TaxRate or Tax_Rate.

  6. Try moving back and forth between cell B6 and B7. Look in the Name Box, and you'll see that Excel always shows the name of the current cell (unless it doesn't have a name, in which case Excel shows an ordinary cell address).

  7. Now move to cell B8. You can see that the formula hasn't changed. It still uses ordinary cell references. Edit the formula to use your new names instead.

    The exact formula depends on the names you picked, but it should be something like this:

    =ListPrice*TaxRate

  8. Now check out the formula in cell B9. It uses two references, to cells B6 and B8. You've already named B6, but you should also name cell B8, which shows the total tax that's charged for the phone. (You can name any cell, no matter what it contains, so it's just as easy to name a cell with a formula in it as a cell with a number.) A good name for B8 is Tax.

  9. It's time to modify the formula in cell B9 to use your cell names. You could do this by editing the formula, as you did previously. But this time let's try the Apply Names feature. First, move to cell B9.

  10. Next, look at the Formulas►Defined Names►Define Names button in the ribbon. On the right side is a small down-pointing triangle. Click that to pop up a menu with more choices, then choose Apply Names.

  11. Select all the names in the Define Names window, to make sure you don't miss anything. (Click each one so it's highlighted blue.)

  12. Click OK. Excel will automatically replace the cell references in your formula with the cell names. You'll know this process has worked if you get a formula like =ListPrice+Tax.

  13. If you'd like a bonus challenge, try adding names to the Imaginary investment.xlsx spreadsheet that you saw in Chapter 6. The investment calculation formula is =FV(B5/12,B4*12,0,-B3). That looks pretty ugly, but a few good names can make it much clearer. Try creating all the names you need on your own, and revising the formula accordingly.

    Check the Solution worksheet to see a version of this example that makes full use of Excel's cell naming feature.

Exercise #2: Your wedding budget

In this exercise you turn an ordinary budget list into an official Excel table, you try out some of Excel's table features, and put table names in your formulas.

  1. Begin by opening the Your wedding budget.xlsx spreadsheet.

  2. Right now you have a bunch of cells in a tabular arrangement, but it isn't an official table. To turn it into a table, move to one of the cells in the table and choose Insert►Tables►Table in the ribbon.

  3. The Create Table window appears, showing you the cells that Excel has determined are part of your table. Click OK to create the table.

  4. Your new table starts out blue, with banded row shading. Before you go on, choose a new style (of your choice) from the Design►Table Styles gallery. (Quick tip: the Design tab only appears when you're on one of the cells inside your table.)

  5. Let's try adding a column with a calculation. Start by going to cell D1. This is in the heading row, right next to the last column.

  6. Type the name for this new column: Cost Overrun. What happens next?

    Excel automatically expands the table to include your new column. (But you may want to widen the column so you can see the whole heading.)

  7. Move down to cell D2. This is where you'll type the first formula.

  8. Your formula is going to calculate the difference between the estimated cost and the actual cost, using simple subtraction. The trick is that you want to use the table column names instead of ordinary cell references. Begin by typing an equal sign (=) to start the formula.

  9. There's no need to know the column names, because Excel fills them in automatically when you use the point-and-click formula feature. To see it in action, click cell C2. Excel puts the column name [@[Actual Cost]] in the formula.

  10. Type the minus sign and then click cell B2 to get the estimated cost. Excel puts the column name [@[Estimated Cost]] in the formula.

  11. Press Enter. What happens now?

    Excel automatically adds the formula you've just made to every row in the table. The full formula is this:

    =[@[Actual Cost]]-[@[Estimated Cost]]

    This formula works for every cell because it always gets the value from the current row. The @ sign performs this bit of table name magic.

  12. Excel won't think to format the cost overrun field as a currency value. Do that now, before you continue.

    The quickest way is to click the D column header to select the whole column, and then choose Currency or Accounting (that's what the other columns use) from the format list in the Home►Number section of the ribbon.

  13. For your next trick, you'll write a formula somewhere outside the table that works on the cells in the table. Go to cell D21.

  14. You're going to use the SUM function to calculate the grand total of the cost overrun. Once again, Excel's point-and-click formula feature can help you out. Begin by typing =SUM(   Then select all the cells in the Cost Overrun column (that's D2 to D15). If you select the right cells (and nothing else), Excel automatically puts the column name in your formula.

    The final formula should look like this:

    =SUM(Table3[Cost Overrun])

  15. If you're not happy with having a boring, automatically generated table name like Table3, you can change it easily. Click a cell inside your table. Then, click the Design tab that has all the table features.

  16. In the Design►Properties section, you'll see a box with the table name (for example, "Table3"). Change it to something else, like WeddingCosts.

    This is the section you're looking for:

  17. Now check the cost overrun formula in cell D21. Has it changed?

    When you rename your table, Excel automatically updates your formulas to match. Now your formula should look like this:

    =SUM(WeddingCosts[Cost Overrun])

  18. Before you finish up, why not try out a few more table features? For example, you can sort the table so that largest costs come first. Move to the table and use the drop-down menu for the Cost Overrun column.

    Click the drop-down arrow next to the Cost Overrun heading and choose Sort Largest to Smallest. You'll see that the reception catering was the prime budget-busting culprit. On the opposite end of the list, the bridal dress has the biggest cost savings.

  19. For a bonus exercise, why not try some filtering? You can choose to hide rows that don't interest you in all sorts of different ways. For this challenge, let's hide the expense items that are less than $500, so you can focus on the biggies. Start by clicking the drop-down in the Actual Cost column to show your options.

  20. You could manually uncheck every cost that's too low. But a better approach is to choose Number Filters►Greater Than.

    Here's how you click through the menu to find the Number Filters command.

  21. Excel shows the Custom AutoFilter window where you will finish setting your filter condition. In the box next to "is greater than" type the number 500. Finally, click OK. The table will shrink down to seven rows with the high-priced cost items.

  22. When you use filtering, your SUM formula doesn't change. It still uses all the rows (including the ones that are temporarily hidden) to make its calculation. To tell Excel to use only the currently visible rows, you can replace the SUM calculation with a SUBTOTAL calculation. Begin by going to cell D21.

  23. Now edit your formula. When using SUBTOTAL, you need to supply two parameters. The first parameter tells Excel the kind of calculation you're performing (in this case, you use the number 9 to tell Excel you want to perform a sum). The second parameter has the range of cells you're using (which is the same as in the original formula). See if you can get it working.

    The final formula you need is this:

    =SUBTOTAL(9, WeddingCosts[Cost Overrun])

    To see the complete worksheet, check out the Bonus spreadsheet. (Click the Bonus tab at the bottom of the Excel window.)

PREFER PAPER? If you'd rather do your work offline, you can print this page. Use this button to expand all the hidden sections and then print the exercises for this chapter: