Pivot Table Issues

Sort Year-over-Year Donation Changes in an Excel PivotTable

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Calculate and sort donors by their year-over-year percentage change in donations in an Excel PivotTable.

Sort Year-over-Year Donation Changes in an Excel PivotTable
Product
Microsoft Excel PivotTable and Power Pivot
Device & OS
not provided
Scenario
A user wants to compare each donor's donations with the prior year and rank donors by the percentage change.
Observed behavior
A PivotTable value displayed with Show Values As > % Difference From may not sort in the expected order.
Before you start

Check that the source data identifies each donor and includes a valid donation date and numeric amount. If using Power Pivot, confirm that the Data Model has a continuous calendar table related to the donation date.

Solution 1Recommended

Create a Power Pivot measure and sort its results

Use a Data Model measure when you want the year-over-year calculation to respond to PivotTable filters.

The example assumes a Donations table with an Amount column and a Calendar table with a Date column. The calendar must cover the relevant years and be related to the donation date.

1
Check the calendar relationship

Open Power Pivot > Manage and check Diagram View for a relationship from Calendar[Date] to the donation date column. Mark the continuous calendar as a date table using Design > Mark as Date Table.

2
Create the donation measures

In Power Pivot, create these measures: Current Donations := SUM(Donations[Amount]) and Prior Donations := CALCULATE([Current Donations], SAMEPERIODLASTYEAR(Calendar[Date])).

3
Create the percentage measure

Create YoY % := DIVIDE([Current Donations] - [Prior Donations], [Prior Donations]) in Power Pivot, then format the measure as a percentage.

4
Build and sort the PivotTable

Insert a PivotTable from the Data Model. Put donor in Rows, calendar year in Columns or Filters, and YoY % in Values. Select a YoY % cell for the year you want to rank, then right-click and choose Sort > Sort Largest to Smallest.

Create a Power Pivot measure and sort its results
Missing or zero prior-year donations: DIVIDE returns a blank when the prior-year amount is blank or zero, so those donors will not have a meaningful percentage change to rank.
Free Microsoft Office alternative

Work with donation spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with donation data while making a straightforward transition from Microsoft Office.

Review and organize donor-year donation data in WPS SpreadsheetOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a familiar interface for everyday spreadsheet workAccess a free, lightweight alternative to Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my % Difference From column sort as expected?

A value displayed through Show Values As > % Difference From may not sort as expected. Calculate the percentage in a Data Model measure or donor-year source summary, then sort the resulting value column.

How do I sort donors by the change for a particular year?

Place the calendar year in Columns or Filters, select a YoY % value for the target year, and right-click it. Choose Sort > Sort Largest to Smallest to rank donors by that year's change.

Why is a donor's year-over-year percentage blank?

The prior-year donation amount may be missing or zero. DIVIDE returns a blank for a blank or zero denominator; check the donor's prior-year data and the calendar relationship in Power Pivot > Manage.

Can I calculate the percentage without Power Pivot?

Yes. Create a worksheet summary with one row per donor and year, look up the prior-year total, and calculate the percentage change. Use that summary as the source for a new PivotTable.