Category: Sort and Filter

If you have a report filter at the top of your pivot table, do the items in the drop down list ever appear out of order? That happens in my pivot tables occasionally, and there isn’t a quick and easy way to fix the problem!

Video: Sort Pivot Table Report Filters

In the short video below, I show the report filter problem, where the drop down list is not sorted correctly.

After that, I show a workaround that you can use, to get the sorting problem fixed.

There are written steps, and more pivot table sorting tips, on the Pivot Table Sorting Fixes and Tips page, on my Contextures site.

Report Filter Sorting Problem

If you notice that items are out of order in a pivot table report field, you might go to the Data tab on the Excel Ribbon, where the Sort & Filter commands are located.

However, the Sort commands are dimmed out, because you can’t use those in a Report filter field.

Why not? I have no idea!

Report Filter Sort Solution

Fortunately, there’s a workaround that you can use, to sort the report filter items.

It’s not complicated, but it’s a bit annoying that this is the only way to sort things!

To sort the report filter field, follow these steps:

• First, drag the Report field down to the Row area of the pivot table body.
• Next, right-click one of the pivot items in the Report field that you moved to Rows.
• Because the field is in the Rows area, the right-click menu now shows the Sort command
  • The Sort commands on the Data tab are available too!
• Click the Sort command, then click one of the sort options – Sort A to Z, or Sort Z to A.
• When the sort is finished, drag the field bad up into the Report Filter area.

NOTE: If you have several Report Filter fields to sort, use the Report Filter sorting macro, on my Contextures site.

Get the Sample File

You can get the Excel sample file, and more pivot table sorting tips, on the Pivot Table Sorting Fixes and Tips page, on my Contextures site.

Also, if you have lots of Report Filter fields to sort, you can save time with the Report Filter sorting macro, on my Contextures site.

__________________________

How to Sort Pivot Table Report Filters

__________________________

After you create a pivot table in Excel, you can filter the data, to focus on specific things. Maybe you want a top product report, or a regional summary, or see the sales to a couple of new customers. You can also use filters on date fields, and there are 3 different types you can use.

Continue reading “Pivot Table Date Field Filters – 3 Types to Try”

Do you create weekly or monthly reports based on pivot tables, and share those reports with clients or co-workers? If there are multiple pivot tables in your report, use this Excel dashboard technique to check all the filters, before you hit Send!

Continue reading “Excel Dashboard Check Pivot Table Filters Before Printing”

The Pivot Table Top 10 Filter feature lets you focus on what’s working well. Create a report that shows sales from the top regions, or show the best-selling products. You can use this feature to show the opposite too. Which products are selling poorly? Which cities or regions aren’t doing well? These two short videos show quick ways to analyse sales data with pivot table top 10 filters.

Continue reading “How to Use Pivot Table Top 10 Filters to Analyse Sales Data”

Usually, it’s easy to sort an Excel pivot table – just select one of the sort options – A to Z or Z to A – in the heading drop downs. However, you might run into a pivot table sorting problem, where the wrong item stays at the top. See why that happens, and how you can fix the sorting problem.

Continue reading “Pivot Table Sorting Problem Wrong Item at Top”

If you create two or more pivot tables from the same source data (pivot cache), you can connect them to the same Excel Slicers. That will make it easy to filter all the connected pivot tables at the same time.

Continue reading “Connect Excel Slicers to Multiple Pivot Tables”

For a quick way to filter an Excel Pivot Table, you can insert a Slicer, for one or more of the pivot table fields. You can also connect Slicer to multiple pivot tables, if all the pivot tables use the same pivot cache.

Continue reading “Connect Slicer to Multiple Pivot Tables”

When you add fields to the pivot table row area, you can sort the items alphabetically, or by the numbers in the Values area. The sorting can be done manually or with a macro. See below for an Excel pivot table sorting macro. Data Model pivot tables can be sorted with it too.

Continue reading “Excel Pivot Table Sorting Macro Data Model”

When you add a date field to your Pivot Table, Excel automatically groups the dates into a hierarchy, such as years and months.

See how to stop pivot table date grouping in the latest versions of Excel, and a couple of workarounds for Excel 2016. You can also read about why this feature was added, and watch a date grouping video.
Continue reading “Stop Pivot Table Date Grouping”