Master Excel Pivot Table sorting and filtering. Learn advanced techniques and best practices to analyze data and make informed decisions.
by Mihir Kamdar / Last Updated:
In this comprehensive guide on mastering pivot table filter and sort in Microsoft Excel. By the end of this article, you’ll be able to:
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently!
In today’s data-driven world, quickly analyzing and interpreting large volumes of information is crucial for making informed business decisions. Microsoft Excel’s pivot tables have become indispensable for professionals looking to streamline their data analysis process. By mastering the art of sorting and filtering pivot tables, you can unlock the full potential of your data and gain valuable insights that drive success.
Before we dive into the intricacies of sorting and filtering, let’s ensure you have a solid foundation in setting up and customizing pivot tables for efficient data analysis.
To create a pivot table from scratch, follow these simple steps:
1. Organize your data in a structured table format, with each column representing a field and each row representing a record. Ensure that your data has no blank rows or columns and that each column has a unique, descriptive header.
2. Select any cell within your data range, then navigate to the “Insert” tab in the Excel ribbon.
3. Click on the “PivotTable” button → From Table/Range, which will open the “Create PivotTable” dialog box.
4. Verify that the selected data range is correct and choose output tab to place your pivot table.
5. Click “OK” to create your pivot table.
To create an effective pivot table, it’s essential to have a well-structured source and pivot table field. Here are some best practices for organizing and formatting your source data:
Each column should represent a unique field or attribute, such as “Date,” “Product,” “Sales,” or “Region.”
Each row should represent a single record or observation.
Ensure that there are no blank rows or columns within your data range.
Use consistent formatting for dates, numbers, and text across all records.
By following these guidelines, you’ll lay the foundation for a robust and reliable pivot table that can be easily sorted and then filtered data.
Once you’ve created your pivot table, it’s time to add all the data-relevant fields and values for the previous step of your analysis:
1. In the PivotTable Fields pane, locate the fields you want to analyze.
2. Drag and drop the fields into the “Rows,” “Columns,” and “Values” areas, depending on how you want to structure your pivot table.
3. Excel will automatically populate the pivot table with the selected fields and calculate the corresponding values.
Drag the Region in the Rows area, Sales Channel in Column area and Total Revenue in the Values area.
Sorting is a fundamental feature of pivot tables that allows you to organize your data in a meaningful way. Excel offers several sorting options, including:
Sorting by column labels: Arrange your data based on the values in the column headers.
Sorting by row labels: Organize your data according to the values in the row headers.
Custom sorting: Create your own sorting order based on specific criteria or preferences.
In the next section, we’ll explore each of these sorting options in detail, providing step-by-step instructions and practical examples.
Sorting data in a Pivot Table allows you to quickly organize your results, making it easier to analyze trends and patterns at a glance.
To sort your pivot table by a single column of labels:
1. Click on any cell within the column you want to sort.
2. In the Data tab, locate the “Sort” dropdown menu in the “Sort & Filter” group.
3. Choose “Sort A to Z” for ascending order or “Sort Z to A” for descending order.
Excel will instantly rearrange your pivot table based on the selected column’s values.
Sorting by row labels follows a similar process:
1. Click on any cell within the row you want to sort.
2. In the Data tab, locate the “Sort” dropdown menu in the “Sort & Filter” group.
3. Choose “Sort A to Z” for ascending order or “Sort Z to A” for descending order.
Your pivot table will now be sorted according to the values in the first column box selected row.
Sometimes, you may want to create a custom sorting order based on specific criteria or business rules. To apply a custom sort:
1. Right-click on any cell within the row or column you want to sort.
2. Select “Sort” from the context menu, then choose “More Sort Options.”
3. In the “More Sort Options” dialog box, select “Manual” under “AutoSort options.”
4. Sort is by Ascending or Descending.
5. Click “OK” to apply the custom sort.
Custom sorting allows you to organize your data to align with your unique analysis requirements and business logic.
To enable drop-down lists for sorting field items in your pivot table:
1. Click on the drop-down menu.
2. Select Sort by Ascending or Descending.
With the drop down arrow top-down lists enabled, you can now easily sort the rows area your pivot table by clicking on the dropdown arrows next to each row or column label and selecting the desired sorting option.
In addition to sorting, filtering excel pivot table is a powerful tool that allows you to focus on specific items or subsets of your data for more targeted analysis. Pivot tables offer several filtering options, including label filters, value filters, slicers, and timelines.
Label filters allow you to apply multiple filters to filter your pivot table based on the text, numbers, or dates in the row or column labels. To apply a label filter:
1. Click on the dropdown arrow next to the row or column label you want to filter.
2. In the dropdown menu, uncheck the boxes next to the items you want to exclude from your analysis.
3. Click “OK” to apply the filter.
You can also create custom label filters by selecting “Label Filters” from the filter drop-down menu and choosing from options like “Equals,” “Contains,” “Begins With,” or “Ends With.”
Value filters enable you to filter your pivot table based on the numerical values in the value fields. To apply the filter criteria to a values field using a value filter:
1. Click on the dropdown arrow next to the value field you want to filter.
2. Select “Value Filters” from the dropdown menu.
3. Choose from options like “Greater Than,” “Less Than,” “Between,” or “Top 10.”
4. Enter the desired criteria for your value filter.
5. Click “OK” to apply the filter.
Value filters are particularly useful when you need to apply filters that focus on specific thresholds, such as identifying the top-performing products or analyzing costs within a certain range.
Slicers and timelines are interactive filtering tools that allow users to quickly and easily filter data from pivot tables by selecting items from a visual interface.
To add a slicer to your pivot table:
1. Select any cell within your pivot table.
2. Go to the “PivotTable Analyze” tab in the Excel ribbon.
3. Click on the “Insert Slicer” button.
4. In the “Insert Slicers” dialog box, select the fields you want to use as slicers.
5. Click “OK” to add the slicers to your worksheet.
Users can then quickly filter out items in the pivot table by clicking on the desired items in the slicer.
To add a timeline to your pivot table:
1. Select any cell within your pivot table.
2. Go to the “PivotTable Analyze” tab in the Excel ribbon.
3. Click on the “Insert Timeline” button.
4. In the “Insert Timelines” dialog box, select the date field you want to use for the timeline.
5. Click “OK” to add the timeline to your worksheet.
Users can filter the pivot table with multiple filters by adjusting the date range of multiple selections in the timeline using the scroll bars or date selectors.
To illustrate the power and versatility of pivot table sorting and filtering, let’s explore some real-world applications and case studies across various industries.
Identify top-performing products and customer segments by sorting and filtering sales data by product categories, regions, and segments.
Analyze financial data for budgeting and forecasting by sorting and filtering expenses, revenues, and profits by departments and projects.
Evaluate employee performance and retention rates by sorting and filtering HR data by job titles, performance ratings, and count of employees.
By applying the sorting and filtering techniques covered in this guide, you can uncover valuable insights, make data-driven decisions, and drive business success in your specific field.
Yes, you can sort and filter a PivotTable in Excel. Sorting allows you to arrange your data in ascending or descending order based on one or more fields, while filtering enables you to focus on specific subsets of your data by displaying only the rows that meet certain criteria.
If you are unable to sort values in a PivotTable, it could be due to several reasons:
1. The field you are trying to sort is not in the “Values” area of the PivotTable.
2. The field in the “Values” area is calculated using a custom formula that is not sortable.
3. The PivotTable is connected to an external data source that does not support sorting.
4. The PivotTable layout or structure is preventing sorting. Try moving the fields to different areas or changing the layout.
To filter in descending order in a PivotTable:
1. Click on the dropdown arrow next to the field you want to filter.
2. Select “Sort Descending” from the dropdown menu.
3. The PivotTable will update to show the filtered data in descending order based on the selected field.
To filter data in a PivotTable:
1. Click on the dropdown arrow next to the field you want to filter.
2. Uncheck the boxes next to the items you want to exclude from your PivotTable.
3. Click “OK” to apply the filter.
4. The PivotTable will update to show only the data that meets your filter criteria.
To customize a filter in a PivotTable:
1. Click on the dropdown arrow next to the field you want to filter.
2. Select “Label Filters” or “Value Filters” from the dropdown menu, depending on the type of filter you want to create.
3. Choose the appropriate filter option, such as “Equals,” “Greater Than,” or “Between.”
4. Enter the desired criteria for your custom filter.
5. Click “OK” to apply the filter.
To create a dynamic PivotTable filter:
1. Create a separate worksheet with a list of the items you want to use as filter criteria.
2. In your PivotTable, click on the dropdown arrow next to the field you want to filter.
3. Select “Label Filters” or “Value Filters” from the dropdown menu.
4. Choose “Equals” as the filter option.
5. Instead of entering a specific value, type an equal sign (=) followed by the cell reference of your filter criteria list.
6. Click “OK” to apply the dynamic filter.
7. The PivotTable will now update automatically based on the values in your filter criteria list.
To sort a PivotTable:
1. Click on any cell within the field you want to sort.
2. In the “PivotTable Analyze” tab, locate the “Sort” dropdown menu.
3. Choose “Sort A to Z” for ascending order or “Sort Z to A” for descending order.
4. The PivotTable will rearrange based on the selected field’s values.
Yes, you can apply filters to the data after creating the PivotTable. Simply click on the dropdown arrow next to the field you want to filter and select the desired filter options. The PivotTable will update to reflect the new filter settings without affecting the original data source.
A dynamic filter in a PivotTable is a filter that automatically updates based on a separate list of criteria. Instead of manually selecting filter options, you create a reference to a cell range containing the desired filter values. When the values in the referenced range change, the PivotTable filter updates accordingly, providing a more flexible and automated filtering solution.
You now have the knowledge and skills to unlock your data’s full potential and make informed business decisions. By mastering these techniques, you’ll be well-equipped to tackle even the most complex data challenges and drive meaningful results in your organization.
You’ve learned how to:
By applying these pivot table sorting and filtering techniques consistently, you’ll develop a data-driven mindset that will serve you well throughout your career.
