Yes, you can sort a pivot table by a hidden field, but the method depends on what you mean by “hidden.” If the field is hidden from the report (e.g., a column or row field that is not displayed), you can still sort by it using the field’s sort options in the PivotTable Fields pane. If the field is hidden in the sense that it is a value field that is not shown (e.g., a calculated field or a second value field that is collapsed), you can sort by it by selecting the field in the field list and applying a sort. This article explains the exact steps, common pitfalls, and workarounds.
What “Hidden Field” Means in a Pivot Table
In Excel pivot tables, a field can be hidden in several ways:
- Field not added to the layout: The field exists in the source data but is not dragged into any area (Rows, Columns, Values, or Filters). It is not visible in the pivot table at all.
- Field in the Filters area: It appears as a drop-down filter at the top of the pivot table, but its values are not shown as rows or columns.
- Value field collapsed or hidden: A value field (like Sum of Sales) might be hidden if you have multiple value fields and you collapse one, or if you use a “Show Values As” setting that hides the original.
- Field in the Rows/Columns area but with subtotals or grand totals that hide it: Sometimes the field is there but its individual items are collapsed, making it appear hidden.
The most common scenario is the first: you want to sort the visible rows (e.g., product names) by a numeric field (e.g., total profit) that is not currently displayed in the pivot table. Excel allows this, but the sort option is not always obvious.
How to Sort by a Field That Is Not in the Layout
To sort a pivot table by a field that is not currently in any area, you must first add that field temporarily to the Values area, sort by it, and then remove it (or keep it hidden). Here is the step-by-step:
- Click anywhere inside the pivot table to activate the PivotTable Fields pane.
- In the field list, find the field you want to sort by (e.g., “Profit”).
- Drag it into the Values area. It will appear as a new value field (e.g., “Sum of Profit”).
- Click the drop-down arrow next to the field name in the Values area, or right-click on any cell in that value column, and choose Sort > Sort Largest to Smallest (or Smallest to Largest).
- After sorting, you can remove the field from the Values area by dragging it back to the field list, or by unchecking it. The sort order of the row items will remain applied, even though the field is no longer displayed.
If the field is already in the Values area but you want to hide it visually (e.g., you have two value fields and you only want to show one), you can right-click on the column header of the field you want to hide, choose Remove Field? No, that removes it. Instead, use the Design tab > Report Layout > Show in Tabular Form? That doesn’t hide it. To hide a value field, you can collapse it using the +/- buttons, but that only hides the detail, not the field. The only true way to hide a value field is to remove it from the layout, but then you lose the ability to sort by it unless you use the temporary method above.
Sorting by a Field in the Filters Area
If your hidden field is in the Filters area (a report filter), you can still sort by it, but the sort will apply to the items in the row or column area based on the filter’s selected value. For example, if you have a filter for “Region” and you select “East,” the pivot table shows only East data. You can then sort the row labels by any value field that is visible. The filter field itself is not used for sorting; it only limits the data. If you want to sort by the filter field’s values (e.g., sort regions alphabetically), you would drag that field into the Rows area temporarily, sort, then move it back to Filters. Excel does not offer a direct “sort by filter field” option.
Using Custom Sort Options for Hidden Fields
Excel also allows you to sort by a hidden field using the More Sort Options dialog. This works when the field is in the Rows or Columns area but its items are collapsed or hidden. For instance, if you have a two-level row layout (e.g., Category and Product) and you collapse the Category level, you might want to sort the visible products by a value field that is not shown. Here’s how:
- Right-click on a cell in the row or column that contains the items you want to sort.
- Choose Sort > More Sort Options.
- In the dialog, select Descending (Z to A) by: or Ascending (A to Z) by: and then choose the value field from the drop-down list. This list includes all value fields in the pivot table, even if they are not currently displayed.
- Click OK.
This method works even if the value field is hidden (e.g., you have two value fields but only one is visible). The drop-down will show all value fields, including the hidden one. This is often the simplest way to sort by a hidden value field without temporarily adding it.
Common Problems and Workarounds
Users often encounter issues when trying to sort by a hidden field:
- Sort option is grayed out: This happens when the field you are trying to sort is not in the Rows or Columns area. You must first add the field to the layout, sort, then remove it.
- Sort order resets when you refresh: If you refresh the pivot table, the sort order may revert to the default. To preserve the sort, you can set a custom list or reapply the sort after refresh. Alternatively, use the PivotTable Options > Layout & Format > Sort to set a default sort for the field.
- Hidden field is not in the field list: If the field is not in the PivotTable Fields pane, it means it is not part of the source data or it has been excluded. Check the source data range and ensure the field is included.
- Sort by a calculated field: Calculated fields (like a formula that sums two columns) can be sorted, but they must be added to the Values area first. They cannot be sorted directly from the field list unless they are in the layout.
Comparing Methods: Temporary Add vs. More Sort Options
There are two main approaches to sorting by a hidden field. The temporary-add method is straightforward and works for any field, but it temporarily clutters the pivot table. The More Sort Options method is cleaner because it does not change the layout, but it only works if the field you want to sort by is already in the Values area (even if hidden). If the field is not in the Values area at all, you must add it. Here is a quick comparison:
- Temporary Add: Works for any field, even if it is not in the pivot table. Steps: drag to Values, sort, remove. Downside: extra steps, risk of forgetting to remove.
- More Sort Options: Works only for value fields that are already in the pivot table (even if hidden). Steps: right-click, More Sort Options, choose field. Downside: cannot sort by a field that is not in the Values area.
For most users, the temporary-add method is the most reliable because it works in all cases. However, if you have multiple value fields and one is hidden, the More Sort Options dialog is faster.
Frequently Asked Questions
Can I sort by a field that is not in the pivot table at all? Yes, but you must temporarily add it to the Values area, sort, then remove it. The sort order will remain.
Why does my sort disappear when I refresh the pivot table? Excel resets sort orders on refresh unless you set a default sort. To set a default, right-click the field, choose Field Settings, then Advanced, and select the sort option.
Is there a way to sort by a hidden field without changing the layout? If the hidden field is a value field that is already in the pivot table (e.g., you have two value fields but only one is visible), you can use More Sort Options. If it is not a value field, you must add it temporarily.
Can I sort by a filter field (e.g., Region) even though it’s in the Filters area? Not directly. You would need to move the field to Rows, sort, then move it back to Filters. Alternatively, you can use a slicer to filter and then sort the visible data.
Does this work in Excel for Mac? Yes, the steps are the same, but the right-click menu may look slightly different. On Mac, you can also use the PivotTable Fields pane and the Sort options in the ribbon under Data > Sort.
Alternatives: Using Slicers and Custom Lists
If sorting by a hidden field is a frequent need, consider these alternatives:
- Slicers: Slicers let you filter data visually, and the pivot table updates accordingly. You can then sort the visible rows by any visible value field. This does not help sort by a hidden field, but it can make the data more interactive.
- Custom Lists: If you want a specific sort order (e.g., by a priority ranking), you can create a custom list in Excel (File > Options > Advanced > Edit Custom Lists) and then sort the pivot table by that list using More Sort Options.
- Power Pivot / Data Model: If you use Power Pivot, you can create a measure that calculates a value and then sort by that measure even if it is not displayed, using the same methods.
In conclusion, sorting by a hidden field is entirely possible with a few extra clicks. The key is to understand that Excel’s sort options only apply to fields that are in the pivot table layout, so you either add the field temporarily or use the More Sort Options dialog to reference a hidden value field. With these techniques, you can keep your pivot table clean while still controlling the order of your data.
Related
- How to Unmerge Cells in Excel (Group of Cells)
- Excel HR Templates for Attendance: A Practical Guide
- Excel: How to Split Information in a Cell
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Update Pivot Table Data in Google SheetsGoogle Sheets pivot tables update automatically when cells in their source range change. If you add rows or…
- How to Build a Bubble Chart in Excel: A Step-by-Step GuideTo build a bubble chart in Excel, arrange each observation’s X value, Y value, and bubble-size value in…
- Progress Doughnut Chart in Excel: Build and Format OneTo make a progress doughnut chart in Excel for Microsoft 365 on Windows, enter a completed percentage and…
- Pivot Table Formula Errors in Excel: Causes and FixesA PivotTable formula error usually comes from an error in the source data, a calculated field that does…
- Excel Sparklines: Meaning, Types, and How to Use ThemExcel sparklines are miniature charts placed inside individual worksheet cells to show a small set of data as…
- How to Make Charts in Excel Dynamic as Data ChangesTo make a chart update as your data changes, turn the source range into an Excel Table, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.