Calculate Week-over-Week Bounce Rate Change in an Excel PivotTable
Question details
Calculate the week-over-week percentage change in bounce rate from separate bounce and visit measures.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A PivotTable reports bounces and visits by week and marketing channel, and the user needs to compare each week's bounce rate with the previous week's rate.
- Observed behavior
- A calculated item does not produce the correct week-over-week change when bounces and visits are separate measures.
Check that your source data includes dates, marketing channels, bounces, and visits. Define a consistent week start and confirm that previous-week data is available for each comparison.
Calculate the change with Power Pivot measures
Use measures when your Excel edition supports Power Pivot and you want the calculation to respond to PivotTable filters.
Calculate bounce rate as total bounces divided by total visits in the current filter context. A simple average of row-level rates can be misleading when rows have different visit counts.
The seven-day shift below compares the same days in the preceding week. Use a complete date table and a full-week filter for a full week-over-week comparison.
Select the source table, choose Insert > PivotTable, and select Add this data to the Data Model. Add a complete date table to the model and create a relationship from its date column to the source date column.
On the Power Pivot tab, choose Measures > New Measure. Create a measure such as Bounce Rate := DIVIDE(SUM(Data[Bounces]), SUM(Data[Visits])). Replace the table and column names with those in your model.
Choose Power Pivot > Measures > New Measure again and create Previous Week Bounce Rate := CALCULATE([Bounce Rate], DATEADD('Date'[Date], -7, DAY)). Replace 'Date'[Date] with your date table's date column.
Create WoW Change := DIVIDE([Bounce Rate] - [Previous Week Bounce Rate], [Previous Week Bounce Rate]). Add the week and marketing channel to the PivotTable rows and add the measures to Values. Format WoW Change as a percentage.

Calculate the change from a weekly PivotTable summary
Use a worksheet summary when Power Pivot is unavailable and a manually updated comparison is sufficient.
Compare weekly bounce rates in WPS Spreadsheets
Use a weekly summary and worksheet formulas in WPS Spreadsheets to calculate bounce rates and their week-over-week change without a Power Pivot measure.
- 1. Open the weekly data: Open your workbook in WPS Spreadsheets. On a worksheet, arrange one summary row per week and channel with week-start dates in column A, channels in B, summed bounces in C, and summed visits in D.
- 2. Calculate the current rate: In E2, enter =IFERROR(C2/D2,"") and fill it down. Make sure the week-start dates in column A are consistent and seven days apart.
- 3. Calculate the previous rate: In F2, enter =IFERROR(SUMIFS($C:$C,$A:$A,A2-7,$B:$B,B2)/SUMIFS($D:$D,$A:$A,A2-7,$B:$B,B2),"") and fill it down.
- 4. Calculate and format the change: In G2, enter =IFERROR((E2-F2)/F2,"") and fill it down. Select columns E through G and choose percentage formatting on the Home tab.

Frequently Asked Questions
Why is averaging bounce rates sometimes incorrect?
A simple average gives each rate equal weight, even when visit counts differ. For a combined rate, divide the sum of bounces by the sum of visits in the desired week and channel.
What happens if the previous week's bounce rate is zero?
Relative percentage change cannot be calculated with a zero denominator. The DIVIDE and IFERROR formulas shown return a blank rather than a divide-by-zero error.
Can I calculate the change without Power Pivot?
Yes. Summarize bounces and visits by week and channel in a PivotTable, copy the results to a worksheet, and use the rate, SUMIFS, and change formulas in the weekly summary solution.
Why is my previous-week result blank?
Check that a matching channel and the prior week exist, that week-start dates are exactly seven days apart, and that the prior week has visits. For the Power Pivot measure, also check the date table relationship and date filter.




