Sort Year-over-Year Donation Changes in an Excel PivotTable
Question details
Calculate and sort donors by their year-over-year percentage change in donations 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.
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.
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.
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.
In Power Pivot, create these measures: Current Donations := SUM(Donations[Amount]) and Prior Donations := CALCULATE([Current Donations], SAMEPERIODLASTYEAR(Calendar[Date])).
Create YoY % := DIVIDE([Current Donations] - [Prior Donations], [Prior Donations]) in Power Pivot, then format the measure as a percentage.
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.

Sort a donor-year summary built from source data
Use a calculated summary table if Power Pivot is unavailable or you prefer to calculate the change before creating the PivotTable.
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.

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.




