While working in Excel, you might need to calculate the average percentage change in a series of data. For example, if you have an Excel worksheet with the number of sales of every month of the year, then you might want to calculate the percentage change in sales between the successive two months to find out if the business is growing or declining. Or you can also use a specific month as the reference and calculate the average percentage change in sales between that month and other months of the year. In this tutorial, I will show you how to calculate the average percentage change in Excel.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**3 Suitable Ways to Calculate Average Percentage Change in Excel**

Letâ€™s assume a scenario where we are working with the sales volumes of the first 6 months of the year of a shop. We have the **Month**, **Sales Volume**, and **Monthly Average Percentage Change** in the worksheet. Now, we will calculate the **monthly average percentage change **in sales between 2 consecutive months and also calculate the change in sales between **January **and every** other month**. The image below shows the worksheet that we are going to work with.

**1. Calculate Average Percentage Change Using Formula**

We can use the formula for average percent change to calculate the change in sales between two consecutive months. The formula for percent change is like below.

And formula below is for **average percentage change**.

**Step 1:**

- First, we will write the below formula in cell
**D6**to calculate the**percentage changes**in**Sales Volume**between the month of**January**and**February**.

`=(C6-C5)/C5 * 100`

**Formula Breakdown:**

Here,

**C6** = **Sales Volume in February** = Final Value

**C5** = **Sales Volume in January** = Initial Value

**Note: We can not calculate the percentage change in sales for the month of January as we do not have Sales Volume for the preceding month to consider it as an Initial Value.**

- Upon pressing
**ENTER**, we will now see the**percentage change**in**Sales Volume**between the month of**January**and**February**.

**Step 2:**

- Now, we will drag the fill handle of cell
**D6**to apply the formula to the rest of the cells.

- Finally, we will see the
**percentage change**in**Sales Volume**between**each month**and its**preceding month**.

- While all the
**cells with values**under the**Monthly Percentage Change**column are selected, we will click on the**Decrease Decimal**button several times from the**Number**under the**Home****Each time**we**click**on this**button**, it will**decrease**the**decimal**by**one place**.

- Now, we will see that each cell in the
**Monthly Average Percentage Change**is now**decreased**to**2 decimal points**.

**Step 3:**

- Now, we will calculate the
**average percentage change**using all the**monthly percentage changes**. Write down the below formula in cell**D12**.

`=SUM(D6:D10)/COUNT(D6:D10)`

**Formula Breakdown:**

- The
**SUM**formula will**sum up**all the cell values in the range**D6:D10**. - And the
**COUNT**formula will count all the cells in the range**D6:D10**.

- Upon pressing
**ENTER**, we will now see the**average percentage change**in**Sales Volume**for all the sales volumes for the 6 months.

**Read More:** **How to Calculate Average in Excel Excluding 0 (2 Methods)**

### 2. Apply Percent Format to Calculate Average Percentage Change

We can also use the Percent Format to calculate the **average percentage change**. Just follow the below steps.

**Step 1:**

- First, we will click on the
**Percent Style**button several times from the**Number**under the**Home**It will change our**Number Format**to**Percentage**.

**Step 2:**

- Then, we will write the below formula in cell
**D6**.

`=(C6-C5)/C5`

- Upon pressing
**ENTER**, we will now see the**percentage change in Sales Volume**between the month of**January**and**February**.

**Step 3:**

- Now, we will drag the fill handle of cell
**D6**to apply the formula to the rest of the cells.

- Finally, we will see the
**percentage change**in**Sales Volume**between**each month**and its**preceding month**.

**Step 4:**

- Now, we will calculate the
**average percentage change**using all the**monthly percentage changes**. Write down the below formula in cell**D12**.

`=SUM(D6:D10)/COUNT(D6:D10)`

**Formula Breakdown:**

- The
**SUM**formula will**sum up**all the cell values in the range**D6:D10**. - The
**COUNT**formula will count all the cells in the range**D6:D10**.

- Upon pressing
**ENTER**, we will now see the**average percentage change**in**Sales Volume**for all the sales volumes for the 6 months.

**Step 5:**

- Instead of calculating the
**percentage change in sales between two consecutive months**, we can also calculate the change in sales between**January**and every**other month**. - First, we will write the below formula in cell
**D6**.

`=(C6-$C$5)/$C$5`

**Formula Breakdown: **

We have inserted **Dollar signs ($)** in cell C5 that represents the** Sales Volume** in the month of **January**. Inserting the **Dollar signs ($)** will make the cell reference absolute (**$C$5****). **So, if we now drag the fill handle downward, the **cell reference $C$5** will **not change** and the formula will always calculate the **change in sales** between **January** and **every other month**.

- Upon pressing
**ENTER**, we will now see the**average percentage change in Sales Volume**between the month of**January**and**February**.

**Step 6:**

- Now, we will drag the fill handle of cell
**D6**to apply the formula to the rest of the cells.

- Finally, we will see the
**percentage change**in**Sales Volume**between**January**and every other**preceding month**. - We will also see the
**average percentage change**in**Sales Volume**for all the sales volumes of the 6 months.

**Related Content:** **How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)**

**Similar Readings:Â **

**How to Calculate Exponential Moving Average in Excel**- Â
**Determine Triple Exponential Moving Average in Excel** **Average Attendance Formula in Excel (5 Ways)****Excel Formula to Find Displaced Moving Average (With Easy Steps)****How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)**

### 3. Run a VBA Code to Calculate Average Percentage Change

If you are familiar with the **VBA** macro, then you can also use the **VBA macro** to calculate the **average percentage change** more efficiently. Just follow the below steps.

**Step 1:**

- First, we will select
**Visual Basic**from the**Developer**. We can also press**ALT+F11**to open it.

- Now, click on the
**Insert**button and select**Module**.

**Step 2:**

- Then, write down the following code in the window that appears.

```
Sub Average_Percentage_Change()
init_cell = InputBox("Insert the First Cell Reference (Ex. B5)")
num_cells = InputBox("How many values are there in the data range? (Ex. 10)")
result_cell = InputBox("Which cell will show the result? (Ex. D12)")
Â init_cell_num = Int(Right(init_cell, 1))
init_cell_letter = Left(init_cell, 1)
final_cell_num = Int(init_cell_num) + Int(num_cells) - 2
Sum = 0
For i = init_cell_num To final_cell_num
Â Â Â Range("D" & i + 1) = (Range(init_cell_letter & i + 1) - Range(init_cell_letter & i))
Â Â Â Range("D" & i + 1) = Range("D" & i + 1) / Range(init_cell_letter & i)
Â Â Â Â Range("D" & i + 1) = Round((Range("D" & i + 1) * 100), 2)Â Â
Â Â Â Â Â Â Â Next
For i = init_cell_num To final_cell_num
Sum = Sum + Range("D" & i + 1)
Â Â Next
Range(result_cell) = Round(Sum / (num_cells - 1), 2)
Â End Sub
```

**Â ****Step 3:**

- Now, click on the
**Run**.

- Now, if a window named
**Macro**appears, just click on Run from that window. - Then, a window with the prompt
**â€ś**â€ť will appear. We will insert*Insert the First Cell Reference (Ex. B5)***C5**in the input box as it has the first**Sales Volume**.

- Then, another window with the prompt
**â€ś**â€ť will appear. We will insert*How many values are there in the data range? (Ex. 10)***C5**in the input box as have the**Sales Volumes**for**6 months**.

- Finally, the last prompt will appear which will ask you
**â€ś**. We will insert*Which cell will show the result? (Ex. D12)*â€ť**D12**in the input box.

- Then, we will see the
**percentage change**in**Sales Volume**between the consecutive months. - We will also see the
**average percentage change**in**Sales Volume**for all the sales volumes of the 6 months in cell**D12**.

**Read More: Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)**

**Things to Remember**

- If you do not have a Developer tab, you can make it visible in
**File > Option > Customize Ribbon**. - To open the
**VBA**editor Press**ALT + F11.** - You can press
**ALT + F8**to bring up the Macro window.

**Conclusion**

In this article, we have learned how to calculate the average percentage change in Excel. I hope from now on you can **calculate the average percentage change in Excel** very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!