Working out the percentage change in Microsoft Excel 2019

Formulas are very important when working as a financialanalyst, as you need to make sure you are using the correct formulaconstruction. There are many methods to construct a formula for aparticular outcome, so understanding is very important when calculatingto ensure you arrive at the correct answer. In addition, the simplestmethod is always the best, rather than typing a long, complicatedformula to arrive at the same result. Choose to use the most efficientmethod.

Let’s have a look at how we would calculate the percentage change. Wehave ACTUAL SALES figures for ourproducts in cells C16:C20 of the worksheet, and BUDGET SALES PROJECTION in cells D16:D20. Wewould like to work out the percentage change in cells E16:E20. You canwork this out in two ways, as follows:

1. Actual sales minus budget divided by budget
2. Actual sales divided by budget minus budget divided by budget

So, to elaborate, you would use this formula in this particularexample: =C16/D16-D16/D16, which can be simplified toactual sales divided by budget – 1, =C16/D16-1, as shown in the followingscreenshot:

We will continue using the ConvertingValues.xlsx workbookfor this example. Proceed as follows:

1. Click on cell E16.
2. Type the following formula, clicking on the relevant cell referencesas you formulate it: =C16/D16-1. 
3. Place the mouse pointer at the bottom right-hand corner of thecell.
4. The AutoFill handle (the +crosshair pointer) will appear; then, double-click to fill the formuladown to the rest of the cells. The result can be seen in the followingscreenshot:

5. You are now able to view the percentage change in cells E16:E20 andcan adjust actual and budget projections where necessary, and the changewill be reflected in the % CHANGEcolumn. If we adjust cell C17 to read 65,000.00 as the ACTUAL SALES figure, then % CHANGE will update to a positive4.839%.

In the next topic, we will concentrate on working out the percentageincrease or decrease.