Excel case sharing: Calculation methods of year-on-year growth rate and month-on-month growth in each year

How to calculate the year-on-year growth rate of Excel? How to calculate the month-on-month growth of Excel? The following article will introduce the Excel calculation method of year-on-year growth rate for each year. This is a method used by every company for data analysis.

It’s the middle of the year again. The boss asked Xiao Wang to make a year-on-year and month-on-month analysis table as soon as possible based on the details of all sales data in the past three years. After Xiao Wang got it, he saw more than 5,000 pieces of data! I started working on it in a hurry, using a lot of function formats, and it took me a day to complete it. The boss was very dissatisfied and asked him to study hard with his colleague Sister Yan.

Xiao Wang found Sister Yan. When Sister Yan took a look, the mouse operation was completed in ten seconds, and no functions were used. Xiao Wang saw Sister Yan's method and immediately made up his mind to learn EXCEL well. Do you want to know how Sister Yan did it?

As shown in the figure, the following table is the source data. The daily sales of each sales manager from 2016 to 2018 are listed. Now we need to make a year-on-year and month-on-month analysis report on a quarterly basis.

1. Excel year-on-year difference and year-on-year percentage

1. Select the table area and click "Insert" ” tab, place the PivotTable in the “Table” group on the new worksheet, and click OK.

2. Put the order date under the row field of the pivot table, the sales manager under the column field, and the sales amount under the value field.

#3. Right-click under the PivotTable row field---Group---Quarter and Year, and click OK.

Tips: If the year or quarter appears when you first add the date, you can also right-click under the row field to cancel the combination, so that it returns to the date. Format.

4. Under the value field, right-click---Value display mode---Difference

##5. Select year, basic item for the basic field Select the previous one and click OK.

As shown in the figure above, we get the year-on-year difference in sales data on a quarterly basis and on an annual basis.

Analysis:

Since there is no data from the previous year in 2016, it is displayed as empty.

Year-on-year difference = number of this quarter - number of this quarter of the previous year

Then what do we want to get the year-on-year difference percentage in this table at the same time?

First, we add another sales volume to the value field. At the same time, in order to distinguish, we changed the first sales volume to the year-on-year difference, and the second one to the year-on-year percentage. You can modify it by directly selecting the cell where the label is located.

Click the year-on-year percentage under the value field, right-click---Value display mode---Difference percentage

Select the year for the basic field, select the previous one for the basic item, and click OK.

#In order to reflect the data more intuitively, we can change the order of the sales manager and the value in the column field.

Such an analysis report is ready, and it also includes the year-on-year difference and percentage of sales data.

Excel year-on-year growth rate analysis:

Year-on-year percentage = (number of this quarter - number of this quarter of the previous year) / number of this quarter of the previous year ×100%

2. Excel month-on-month difference and month-on-month percentage

So how do you get the month-on-month difference?

We need to add another pivot table. We also put the order date in the row area of ​​the pivot table, the sales manager in the column area, and the sales volume in the value area. Here we see that the pivot table automatically puts the date Combined into quarters and years.

Tips:

I don’t know if you have noticed that different PivotTables created in the same workbook and the same data source, they The date combinations are all consistent. If the date combination of one pivot table changes, the others will also change accordingly. So if you want to create a PivotTable that combines new dates without affecting the previous PivotTable, what should you do? The method is as follows:

Hold down Alt D P, select "Microsoft Excel List or Database and Pivot Table" in the first step, select the table area in the second step, and select "Whether the new report is created in the same Select No on "Have the same data in the report" and confirm.

Similarly, under the value field, right-click---Value display mode---Difference, select the order date as the basic field, select the previous one as the basic item, and click OK.

As shown in the figure above, we get the quarter-on-quarter difference in sales data on a quarterly basis.

Analysis:

Since the pivot table cannot identify that the fourth quarter of the previous year is the previous benchmark for the first quarter of this year, each The first quarter quarter-over-quarter differences of the year were all empty.

The order date here represents the quarter

The month-on-month difference = the number of this quarter - the number of the previous quarter

How about getting the month-on-month difference percentage, the same Add the sales amount to the value field a second time. The first sales volume is modified to the month-on-month difference, and the second is modified to the month-on-month percentage.

Click the month-on-month percentage under the value field, right-click---value display mode---difference percentage, select the order date for the basic field, select the previous one for the basic item, and click Sure.

Replace the order of sales manager and value in the column field.

The final result is as follows:

Excel chain formula analysis:

Chain percentage = (Number of this quarter - Number of last quarter)/Number of last quarter × 100%

Tips:

After setting the year-on-year and month-on-month periods, think What should I do if I change it back? Right-click under the value field---value display mode---no calculation.

In addition to the above-mentioned usage of pivot tables, there are many useful techniques. If you are interested, please like and leave a message below to share more good techniques with you!

Related learning recommendations: excel tutorial

The above is the detailed content of Excel case sharing: Calculation methods of year-on-year growth rate and month-on-month growth in each year. For more information, please follow other related articles on the PHP Chinese website!