Site icon Best Excel Tutorial

How to Sort a Pivot Table

You learn how to sort data in a pivot table. Sorting a PivotTable in Excel can help you visualize data better, whether it’s identifying top buyers or analyzing other trends.

Pivot table data preparation

Mark the data, click insert, and then click Pivot Table. It will create a pivot table.

Check the label, then click on it, and drag it to the field of your need in your pivot table.

How to sort pivot table by values?

Click the arrow on Row Labels or Column Labels, and then click the sort option you want. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.

Manual Sorting in a Pivot Table

For more sorting options, click More Options.

Note: Manual sorting allows rearranging items by dragging, but values cannot be rearranged.

Ascending (A to Z) and Descending (Z to A) sort alphabetically or by the sum of quantities.

Automatic Sorting in a Pivot Table

In More Options, enable Sort automatically every time the report is updated. This feature automatically sorts data when your pivot table is refreshed.

After this step the data in your pivot table will be sorted.

Note: If the pivot table is based on data in an Excel table, you can also sort the underlying data by clicking on the column header and selecting “Sort A to Z” or “Sort Z to A”. The pivot table will automatically reflect the change in sorting.

Advanced Sorting in Pivot Table

For advanced Excel users there are various sorting possibilities available.

Multi-level Sorting

To sort your data on multiple levels, begin by clicking within your PivotTable.

Navigate to the “Data” tab located on the Excel ribbon and select “Sort.”

Inside the “Sort” dialog box, you have the ability to define both primary and secondary sorting criteria. For instance, you can first sort your data by product category and then, within each category, sort it by sales amount.

Custom Sorting

For situations requiring a specific custom order, you can create a custom list. Access the “File” tab, then “Options,” and finally, select “Advanced.”

Scroll down to the “General” section and click on “Edit Custom Lists.” This allows you to establish a custom list with your desired sorting order, such as sorting regions in a predefined sequence.

By delving into these advanced sorting options within your PivotTable, you gain the ability to fine-tune your data presentation and analysis to meet specific needs, making Excel an even more potent tool for interpreting and analyzing data.

Exit mobile version