Excel Optimize

Problem: As shown in Fig. 206, your worksheet contains several different formulas. It would be easier to understand the results if each component of every formula were named for what it represented and not just for the cell it came from.

Strategy: Use named ranges to make formulas easier to understand.
1) Give cell B3 a name of Revenue. Select cell B3. In the Name box (the area to the left of the formula bar), type Revenue and press Enter, as shown in Fig. 207.

2) Give cell B4 a name of COGS. Select cell B4. Click in the name box, type COGS and hit Enter.
3) Clear the formula in B6. Re-enter the formula and use the mouse to select the cells. Type an Equal sign. Using the mouse, touch B3. Type a Minus sign. Using the mouse, touch B4. This will enter the formula as =Revenue–COGS, as shown in Fig. 208. This is easier to understand than a typical formula.

Gotcha: You need a lot of foresight to use this technique. In order to have this work automatically, you are supposed to be smart enough to create the range names before you enter the formula. However, most
people get the formula fi rst and then decide to make the worksheet easier to understand.
1) If you want to assign names after the formulas are created, use Insert – Name – Apply to apply names to existing formulas, as shown in Fig. 209.

2) As shown in Fig. 210, select all of the names that you want to apply.

Result: A formula like =B6–B11 will be updated to =GrossProfit–Expenses, as shown in Fig. 211.

Summary: To create plain language formulas, first assign a range name to each cell in your formula. Use the mouse when entering the formula. To assign range names to a formula after the fact, use Insert – Name – Apply.