To sort a pivot table by absolute value in Excel, you cannot use the built-in sort options directly—they only sort by the displayed numeric value, not its magnitude. Instead, add a helper column to your source data that calculates the absolute value (e.g., =ABS(B2)), then add that helper as a value field and sort by it, or use a custom sort order based on the helper column.
Why the Built-In Sort Fails
Excel’s pivot table sort buttons (right-click > Sort > Sort Largest to Smallest) work on the raw values in the selected field. If your data contains negative numbers, sorting by the value column will place negatives below positives, regardless of magnitude. For example, if you have sales returns of -500 and -50, a descending sort puts -50 above -500 because -50 is numerically larger, even though -500 has a larger absolute value. The same issue occurs with ascending sorts. This is a common frustration when working with variances, differences, or error margins where the sign is irrelevant and you care only about the size.
How to Add an Absolute Value Helper Column
The cleanest solution is to add a column to your source data that computes the absolute value. This works whether your source is a worksheet range, a table, or a Power Pivot data model.
- Open your source data sheet. Assume values are in column B, starting at B2 (with headers in row 1).
- In cell C2, enter the formula:
=ABS(B2) - Copy the formula down to cover all data rows. If your data is an Excel Table, the column will auto-fill.
- Refresh your pivot table (right-click > Refresh).
- Add the new column (e.g., “Abs Value”) to the Values area of your pivot table.
- Right-click any cell in that new value field and choose Sort > Sort Largest to Smallest (or Smallest to Largest).
This sorts by the absolute value because the helper column contains only non-negative numbers. The original negative/positive values remain in the row labels or other fields, but the sort order is now based on magnitude.
Sorting by a Helper Field Without Adding It to the Report
If you don’t want the helper column to appear in the pivot table, you can still use it for sorting. Add the helper field to the Values area, sort by it, then remove it from the Values area. The sort order persists because Excel remembers the sort state based on the underlying data. However, if you refresh the pivot table, the sort may revert unless you reapply it. To make it stick, you can set a custom sort order.
Using a Custom Sort Order for More Control
For scenarios where you need a specific order (e.g., sort by absolute value but keep ties in a certain way), you can create a custom list. This is more advanced but gives you full control.
- First, sort your source data by the absolute value column (using Excel’s normal sort on the worksheet).
- Copy the list of row labels in that sorted order.
- Go to File > Options > Advanced > Edit Custom Lists.
- Paste the list into the “List entries” box and click Add.
- In the pivot table, right-click the row label field > Sort > More Sort Options > Manual (you can drag items) or use the custom list as the sort order.
This method is overkill for most users, but it helps when you need a permanent, repeatable order that survives refreshes.
Alternative: Using a Calculated Field
You can also create a calculated field inside the pivot table that computes the absolute value of the sum or average. However, calculated fields work on the aggregated values, not the individual rows. For example, if you have positive and negative values that net out, the absolute value of the sum is not the same as the sum of absolute values. To get the true sum of absolute values, you must use the helper column approach. A calculated field with =ABS(FieldName) only works if you want the absolute value of the aggregate, which is rarely what you need for sorting by magnitude.
Common Problems and Solutions
- Refresh resets the sort: After adding the helper column, refresh the pivot table. If the sort disappears, reapply it. To avoid this, consider converting your source data to an Excel Table (Ctrl+T) so the helper column expands automatically, and always sort after refreshing.
- Helper column shows in the pivot: If you don’t want it visible, you can hide the column in the source data, but the pivot will still use it. Alternatively, you can rename it to something like “SortKey” and then remove it from the Values area after sorting, as described above.
- Negative numbers in row labels: If your row labels themselves are negative numbers (e.g., account codes), sorting by absolute value of the label is different. You would need to create a helper column for the labels as well, but that’s less common.
- Mac differences: The steps are identical on Mac Excel. The ribbon paths are the same, but the sort options are under PivotTable Analyze > Sort & Filter on both platforms.
Comparing Methods: Helper Column vs. Calculated Field
The helper column is the most reliable and straightforward method. It works in all versions of Excel, including Excel 2010 and later. The calculated field approach is limited because it applies the ABS function to the aggregate, not the individual data points. For example, if you have values 100 and -150, the sum is -50, and ABS of that is 50. The sum of absolute values is 250. If you want to sort by the sum of absolute values, you must use a helper column. If you only care about the absolute value of the net total, then a calculated field works, but that’s a different question.
Frequently Asked Questions
Can I sort by absolute value without adding a column to my source data?
No, not directly. Excel’s sort feature always uses the displayed value. You could use Power Query to add a custom column, but that still requires modifying the data model. The helper column is the simplest way.
Will the sort stay after I refresh the pivot table?
Sometimes. If you sort by a value field that remains in the pivot, the sort usually persists across refreshes. If you remove the helper field after sorting, the sort may revert. To make it permanent, keep the helper field in the Values area (even if you hide the column in the source) or use a custom list.
What if my pivot table uses multiple value fields?
You can sort by any value field. Just right-click on that field’s cells and choose Sort. The helper column approach works the same way—add the absolute value field and sort by it.
Does this work in Google Sheets?
Google Sheets pivot tables have the same limitation. You can add a helper column with =ABS() and sort by that. The steps are similar: add the helper column to the source range, then in the pivot table editor, add it as a value and sort by it.
Can I use VBA to sort by absolute value automatically?
Yes, you can write a macro that adds a helper column and sorts the pivot table. However, it’s usually easier to do it manually. If you use VBA, remember to save a copy of your workbook before running the macro, as macros cannot be undone with Ctrl+Z.
Why does Excel not have an option to sort by absolute value natively?
Excel’s sort is designed to work on the raw data as it appears. Since absolute value is a transformation, it’s not built into the sort dialog. Microsoft has not added this feature to pivot tables as of Excel 365, so the helper column remains the standard workaround.
Related
- Excel: How to Split Information in a Cell
- How to Rotate a Table in Excel: 3 Easy Ways
- Excel How to Select Certain Cells: 6 Quick Ways
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table SUMPRODUCT: When and How to Use ItYou generally cannot enter SUMPRODUCT directly as a PivotTable value calculation: a standard PivotTable summarizes source fields, while…
- Excel Stacked Funnel Chart: How to Build OneExcel does not offer a built-in stacked funnel chart: its native Funnel chart displays one value series, not…
- How to Add a Formula to an Excel GraphTo add a formula’s results to an Excel graph, enter the formula in a worksheet column, fill it…
- Excel Graph With Drop-Down Selection: Build a Dynamic ChartYes. In Excel for Microsoft 365 on Windows, create a drop-down with Data Validation, then connect a chart…
- Line 3 of a Graph in Excel: How to Find and Format ItIn an Excel chart, “line 3” usually means the third data series—the third set of values drawn as…
- Excel Power Pivot: How to Create a DAX Measure in ExcelTo create a measure in Excel Power Pivot for Microsoft 365 on Windows, open the Power Pivot tab,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.