How to sort a pivot table

1 April 2022By Dasha

Do you work with big data and try to sort it in proper order? Use a spreadsheet editor functionality to automate your work. In this article, we will look at how to sort pivot table by values and how to create them.

How to sort a pivot table

Sorting a pivot table is useful when you work with a lot of data. It is one way to organize all data by a value or find individual items for further analysis. You can sort the data from A to Z, from Z to A, by sum values, from the maximum value to the minimum one, vice versa, etc.

Let’s see how to sort in pivot table in an Excel file using the ONLYOFFICE spreadsheet editor.

Watch also our video guide to discover how to work with pivot tables :

How to sort pivot table from largest to smallest value

We have a source table of orders for a certain period. It shows the number of items in the order, the price per item, and the total amount of the order. Based on this data, we have created a pivot table.

How to sort a pivot table

We will sort the pivot table by the total amount from the largest to the smallest to find out the best-selling product.

Click on the small drop-down arrow next to «Labels» —> More sort options.

How to sort a pivot table

Check Descending and choose the column to sort in the drop-down. In our case, we select the sum of the total amount.

How to sort a pivot table

In the same way, you can sort the values from lowest to highest.

How to sort a pivot table alphabetically

It is also possible to sort the data in pivot tables alphabetically. For example, to quickly find a product by name.

Click on the small drop-down arrow next to «Labels» as in the previous step. Then, select an option, A to Z or Z to A, and the column you want to sort.

Sorting pivot table values using a condition

Sorting within a pivot table by condition is convenient for reports because you can quickly display data greater or less than a set number. Let’s find orders whose total value exceeds $1000.

Go to the filter settings —> Value filter —> Greater than…

How to sort a pivot table

In the opened window, select Sum of Total amount and set 1000. Click OK.

How to sort a pivot table

So only orders over $1,000 will remain.

What is a pivot table?

A pivot table is one of the data analysis tools available in Excel files. It shows the same data of a usual table differently. It allows you to quickly present, calculate, summarize, and analyze the same data to form a report.

Using this functionality, you can:

  • Group items/records/rows in categories,
  • Change the rows and columns,
  • Count the number of items in each category,
  • Summarize element values,
  • Calculate the average value, find the minimum or maximum value, etc.

How to create a pivot table in Excel file

Step 1. First, you need to make a table with all the data you have and want to analyze.

How to sort a pivot table

Step 2. Go to the Insert tab —> Pivot table.

In the opened window, in the Source data range field, select the range of cells, based on which the pivot table will be created.

How to sort a pivot table

Choose where to place it: in a new worksheet or in the existing worksheet. In the second case, select the cells where the table will appear. Click OK.

Once the table is created, it will be empty.

Step 3. Now let’s proceed to editing. We need to select the source data that will be displayed in our table.

In the right-side toolbar, you’ll see the column names from the source table. Check the ones you need for data analysis.

How to sort a pivot table

Then, choose the section what a field to add: Filters, Columns, Rows, or Values. One field can be added to several sections if needed.

You can choose a section in a field menu or drag and drop a field to the necessary section.

How to sort a pivot table

That’s it. Choose a layout and format it in the Pivot Table tab to your taste. Then, you can sort pivot table by value or other option as described above.

How to sort a pivot table

As you can see, working with pivot tables is not as difficult as it may seem, and can even make working with data easier. Try all the functionality of ONLYOFFICE editors: