Percent Change

 

The percent change formula is frequently used in Excel. First, let's understand the formula. Next, let's calculate the monthly and total percent change over a period of time.

Percent Change Formula

Let's say you went for a run and covered 5 miles today, compared to 3 miles yesterday. What is the percent change in your distance?

1. Input 3 in cell B2 and 5 in cell B3.

2. In cell B5, type the percent change formula =(B3-B2)/B2 and press Enter.

Percent Change Formula in Excel

Tip: when using the percent change formula, think of this: (New-Old)/Old

3. Select cell B5. On the Home tab, in the Number group, apply a Percentage format.

Apply Percentage Format

4. The percent change is 67%, showcasing an impressive boost in your running distance!

Percentage Change Formula

5. Change the new value to 6. This results in a percent change of 100% (increase).

Percent Increase

6. Change the new value to 1.5. This results in a percent change of -50% (decrease).

Percent Decrease

Monthly Percent Change

Let's say you're tracking the monthly sales of your business, and you want to find out how much the sales are changing each month.

1. Select cell C3 and enter the percent change formula, (New-Old)/Old, you learned earlier.

Calculate Monthly Percent Change

2. Select cell C3. On the Home tab, in the Number group, apply a Percentage format.

Apply Percentage Format

3. Select cell C3, click on the lower right corner of cell C3 and drag it down to cell C13.

Drag Formula Down

4. Check if everything went alright.

Relative Reference

5. You can use conditional formatting to visually highlight positive and negative changes.

Monthly Percent Change Analysis

Explanation: we created two conditional formatting rules. If the percentage change is positive, cells get a green fill color. If the percentage change is negative, cells get a red fill color.

Total Change

In a similar way, we can calculate the Total Change over a period of time.

1. This time we lock the reference to cell B2. Select cell D3 and enter the formula for total percent change shown below.

Total Percent Change

2. Select cell D3. On the Home tab, in the Number group, apply a Percentage format.

Apply Percentage Format

3. Select cell D3, click on the lower right corner of cell D3 and drag it down to cell D13.

4. Check if everything went alright.

Absolute Reference

Explanation: when we drag the formula down, the absolute reference ($B$2) stays the same, while the relative reference (B3) changes to B4, B5, B6, etc.