How to Calculate Sales Growth over 3 Years in Excel (2 Methods)

Measuring sales growth is one of the key points to measuring the overall company growth. Because more sales mean more potential for higher profit. The sales growth can be calculated over 1 year, 3 years, 5 years, etc. But in this article, you will only learn to calculate the sales growth over 3 years in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Extract Sales Amount from Dates to Years

In the following picture, I have taken some sales amounts against some dates over 3 years.

First, I will show you to group all the sales amounts for each corresponding year. For this purpose, I will use a formula having the SUMPRODUCT and the YEAR functions.

Now follow the steps below:

❶ Insert the formula in cell F5 first.

=SUMPRODUCT((YEAR($B$5:$B$20)=E5)*($C$5:$C$20))

❷ Then press ENTER.

Formula Breakdown

  • YEAR($B$5:$B$20) returns only the year out the dates in the range $B$5:$B$20. Output: {2019;2019;2020;2020;2019;2021;2020;2020;2020;2019;2020;2021;2020;2020;2019;2020}
  • YEAR($B$5:$B$20)=E5 returns either TRUE or FALSE. If any year in the range $B$5:$B$20 matches with the year stored in E5, it returns a TRUE; otherwise, it returns FALSE. Output: {TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
  • (YEAR($B$5:$B$20)=E5)*($C$5:$C$20): Here, E5 represents the year 2019. Thus, YEAR($B$5:$B$20)=E5 returns an array of 1 and 0; 1 for the year 2019 and 0 for otherwise. So, the array of 1 and 0 is multiplied parallel with the sales amount in the range $C$5:$C$20. Output: {63516;56804;0;0;51786;0;0;0;0;66558;0;0;0;0;64819;0}
  • SUMPRODUCT((YEAR($B$5:$B$20)=E5)*($C$5:$C$20)): Here, the SUMPRODUCT function sums up all the sales amount in the array {63516;56804;0;0;51786;0;0;0;0;66558;0;0;0;0;64819;0}. Output: $303,483.

Extract Sales Amount from Dates to Years

❸ Now drag down the Fill Handle from cell F5 to F7.

Now you will have Net Sales amounts against the years 2019, 2020, and 2021.

Extract Sales Amount from Dates to Years

So, we have got the Net Sales amounts against each corresponding year. Now I will use this Year vs Net Sales data table to show you calculate sales growth over 3 years in Excel.


2 Methods to Calculate Sales Growth over 3 Years in Excel

1. Calculate Simple Sales Growth Rate over 3 Years

Here, I will show you to calculate the simple sales growth rate over 3 years in Excel.

The generic formula of the simple sales growth rate is:

=(Third Year Sales Amount - First Year Sales Amount)/First Year Sales Amount

Now follow the steps below:

❶ Insert the following formula in cell C9.

=(C7-C5)/C5

Formula Breakdown

  • C7 is the Third Year Sales Amount.
  • C5 is the First Year Sales Amount.

Calculate Simple Sales Growth Rate Over 3 Years in Excel

❷ Then press the ENTER button.

Now you will get the simple sales growth rate over the last 3 years in cell C9.

Calculate Simple Sales Growth Rate Over 3 Years in Excel

Read More: How to Calculate Annual Sales in Excel (4 Useful Methods)


2. Calculate Compound Sales Growth Rate over 3 Years

Now I will show you to calculate the compound sales growth rate over the last 3 years.

The generic formula to calculate the compound sales growth rate is:

=((Third Year Sales Amount/First Year Sales Amount)^(1/(Year Span -1)))-100%

Now follow the steps below:

❶ Insert the following formula in cell C9.

=((C7/C5)^(1/2))-100%

Formula Breakdown

  • C7 is the Third Year Sales Amount.
  • C5 is the First Year Sales Amount.
  • (1/2) is the (1/(Year Span -1)). Here, the Year Span is 3. So 3 – 1 = 2.

Calculate Compound Sales Growth Rate Over 3 Years in Excel

❷ Now hit the ENTER button.

Now you will get the compound sales growth rate in cell C9 which is 57%.

Calculate Compound Sales Growth Rate Over 3 Years in Excel

Read More: Calculate Sales Growth Percentage in Excel (2 Easy Approaches)


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.


Conclusion

To sum up, we have discussed 2 ways to calculate sales growth over 3 years in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo