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.
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.
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.
Check Descending and choose the column to sort in the drop-down. In our case, we select the sum of the total amount.
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…
In the opened window, select Sum of Total amount and set 1000. Click OK.
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.
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.
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.
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.
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.
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: