Pivot Table Month Over Month: How To

To calculate month-over-month (MoM) change in a pivot table, add your date field to the Rows area, group it by month, then add the value field twice to the Values area and set one instance to “Show Values As → Difference From” the previous month. This gives you the absolute change, and you can add a third instance for “% Difference From” to see the percentage change.

Formula

The underlying calculation for month-over-month change is simple: MoM Change = (Current Month Value − Previous Month Value). For the percentage version: MoM % Change = (Current Month − Previous Month) / Previous Month × 100. In a pivot table, you don’t type these formulas; the “Show Values As” feature does it for you. The key is that the pivot table must have the date field grouped by month so that “previous” is defined as the prior calendar month.

Worked Example

Suppose your pivot table shows sales for January and February. January sales are 10,000 and February sales are 12,500. To get the month-over-month change:

1. Add the Sales field to the Values area twice.
2. Right-click one instance and choose Show Values As → Difference From.
3. Set the Base Field to the Date field (grouped by month) and the Base Item to (previous).
4. The result for February is 12,500 − 10,000 = 2,500.
5. For the percentage, set the other instance to % Difference From with the same base settings. The result is (12,500 − 10,000) / 10,000 × 100 = 25%.

January shows blank (or 0) because there is no previous month in the data.

Why Month-Over-Month Matters

Month-over-month analysis is a common way to track short-term trends without the seasonal noise that year-over-year comparisons carry. It answers questions like “Did sales grow from March to April?” or “How did website traffic change from one month to the next?” Because it compares consecutive months, it’s sensitive to recent changes, making it useful for monitoring momentum, spotting sudden drops, or evaluating the immediate impact of a marketing campaign. However, it can be misleading if your business is seasonal — a dip from December to January might just be the post-holiday slump, not a real decline.

How to Group Dates by Month

Before you can calculate month-over-month, your dates must be grouped by month. In Excel, right-click any date in the pivot table’s Rows area and choose Group. In the dialog, select Months (and optionally Years if you have multiple years). In Google Sheets, the process is slightly different: you can’t group directly in the pivot editor, so you need to create a helper column with a formula like =TEXT(A2,"YYYY-MM") and use that as a row field. Once grouped, the pivot table will show one row per month, and the “previous” base item will refer to the prior month in the sorted order.

Common Problems and Fixes

• Blanks for the first month: This is expected because there is no previous month. You can leave it or hide the row.
• Dates not grouping: If your date column contains text or inconsistent formats, the grouping option may be grayed out. Convert the column to true date values first.
• Wrong “previous” month: If you don’t group by month, the pivot table treats each date as a separate item, so “previous” might be the previous day. Always group first.
• Percentage shows as decimal: Change the number format of the value field to percentage (right-click → Number Format → Percentage).
• Multiple years: If your data spans more than one year, grouping by month alone will combine January 2023 and January 2024. Group by both Years and Months to keep them separate.

Comparing MoM with Other Periods

Month-over-month is just one of several period-over-period calculations you can do with “Show Values As.” You can also compare to the same month last year (Previous Year), to a specific base month, or use Running Total to see cumulative totals. The choice depends on your question: MoM is best for short-term operational monitoring, while year-over-year is better for long-term growth because it removes seasonality. If you’re in a highly seasonal business, you might prefer % Difference From with a base item of the same month in the prior year.

Limitations and Accuracy

The month-over-month calculation in a pivot table is only as good as your data. If you have missing months (e.g., no sales in February), the “previous” month will be January, not February, which can distort the comparison. Also, the number of working days varies between months, which can affect metrics like total sales or hours worked. For those, consider using daily averages or a calendar table that includes all months, even with zero values. Finally, the percentage change can be extreme or undefined when the previous month is zero or very small — be cautious when interpreting those values.

Frequently Asked Questions

How do I show month-over-month in Google Sheets pivot table?

Google Sheets doesn’t have a built-in “Show Values As” option, so you need to create a helper column with a month-year label (e.g., =TEXT(A2,"YYYY-MM")) and use it as a row field. Then add the value field twice: one for the current month, and one with a calculated field that subtracts the previous month. Alternatively, use a formula outside the pivot table with SUMIFS to compare two months directly.

Why is my month-over-month showing 0 for all rows?

This usually happens when the base field is not set to the date field, or the date field isn’t grouped by month. If the base field is set to a different field (like product), the pivot table looks for a “previous” product, which doesn’t exist, so it returns 0. Set the base field to your grouped date field and the base item to (previous).

Can I calculate month-over-month without grouping dates?

Technically you can, but it won’t be meaningful. If you don’t group, each date is a separate row, so “previous” would be the previous day. To get monthly comparisons, you must group by month (or use a month-year helper column) so that the pivot table treats each month as a single item.

How do I handle months with no data?

If a month has no records, the pivot table won’t show it, and the “previous” month will skip it. To include missing months, ensure your source data has a row for each month (even with zero values) or use a calendar table. Alternatively, fill in zero values for those months in your source data before creating the pivot table.

What’s the difference between “Difference From” and “% Difference From”?

“Difference From” shows the absolute change (e.g., +2,500 units), while “% Difference From” shows the percentage change (e.g., +25%). Use the absolute when you care about the magnitude, and the percentage when you want to compare relative performance across different scales (e.g., comparing small and large product lines).

Can I use month-over-month in a pivot chart?

Yes. Once you have the calculated field in the pivot table, you can create a pivot chart from it. The chart will show the MoM change or percentage for each month, which can help visualize trends. Just remember that the first month will have no value.

Related

• Summerland Bank Home Contents Plus for Strata Owners
• Time Capacity Planning Software: How to Choose
• Tire Circumference Calculator: How to Calculate

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2