Four steps to an Excel budget shortcut

Excel Yourself

Learn a nifty technique that makes applying factors to multiple formulas a breeze.

Question

Is there an easy way to apply a factor to many different formulas in Excel?

Answer

There is a technique that makes this process easy using Excel’s Paste Special features.

Figure 1Let’s assume you have created a new budget model. You have used many different formulas across different sheets to allocate the costs across the year. Your manager wants to see the results of reducing all the costs by 2 per cent.

If you have already included a cost factor, it becomes easy. You change the factor from 100% to 98% and it’s done. If you haven’t included a cost factor, you start thinking about all the formulas you have created that calculate the different costs and how long it will take to apply a factor to all of them.

Figure 2You can add a factor to thousands of formulas in four steps. This technique will work for formulas throughout the file. Figure 1 demonstrates this technique. This is a small and simple example. The technique can be applied to complex formulas and thousands of cells. The yellow cells are for input. The formulas in the range C5:H8 divide the values in column B by 12 to allocate the costs across the months.

Cell B2 has the factor percentage.

The four steps to apply a Factor are:

  1. Click cell B2 and click in the Name Box on the left of the Formula Bar and above the column letters – see Figure 2. Type the word Factor in the Name Box and press Enter – see bottom of Figure 2. The name you use isn’t important to the technique; it should be descriptive. Once a cell has been named, that name can be used throughout the file in formulas to refer to that cell without having to use $ signs to fix the cell reference (see section on Range Name Advice).
  2.  Enter the following formula into a blank cell, using cell D2: =Factor You can type =and click cell B2. Excel will enter Factor into the formula so no other typing is required.
  3.  Copy cell D2.
  4. The last step is to select the range (C5:H8) where you want to insert the factor. Right click the range and select Paste Special.

    The dialog in Figure 3 will display. You need to select Formulas (Paste section) and Multiply (Operation section) as shown in Figure 3, then click OK.
This will amend all formulas in the range. An example of how the formulas are changed is shown on the following page.

Cell C5 before Paste Special:

=$B5/12

Cell C5 after Paste Special:

=($B5/12)*(Factor)

Figure 3If you want to insert the Factor into other formulas, simply select the other ranges and press the F4 function key. F4 repeats the previous command. This assumes you haven’t done anything else after the Paste Special operation described above.

You can then delete the cell D2 that contains =Factor because it is no longer needed. You can now enter a different percentage in cell B2 (90%) to adjust all the formulas in one step – see Figure 4.

Professional development: Microsoft Excel 2013 Power user

Range name advice

Figure 4

Figure 4

I include at least one capital letter in all of my range names. If Excel recognises a range name, it will include the capitalisation in the range name in the formula. Hence, if I type a range name in lower case and make an error, it will remain in lower case.This makes it much easier to identify the error if I use multiple range names in a formula. The latest versions of Excel suggest range names as you type in the formula.

After you create the range name, it is listed in the drop-down list in the Name Box. This enables you to go directly to that named range, via the Name Box, from anywhere in the file.

I use range names in most of my spreadsheets as they are flexible, easy to use and provide self-documenting formulas. This example is useful, but only scratches the surface of what range names can do.

The companion video and an Excel file may assist your understanding.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.
April 2016
April 2016

Read the April issue

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

TABLE OF CONTENTS