WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to use a pivot table in excel (Step-by-Step)

July 26, 2022

You can dynamically manage, sort, organize, and analyse massive data sets with a pivot table. Financial experts all across the world frequently employ pivot tables, one of Excel's most potent data analysis tools. Excel essentially operates a database in the background of a pivot table, enabling you to quickly manipulate massive volumes of data.

How to use a pivot table in excel online, 2016 and 2019

A step-by-step tutorial for inserting a pivot table in Excel is provided below:

1.Organize the data.Making sure your data is well-organized and can be quickly transformed into a dynamic table is the first step. To do this, check that all of the data is arranged in the correct rows and columns. The table won't function properly if the data is not properly arranged. As seen in the example below, make sure the categories (category names) are in the dataset's top row.

2.Insert the pivot table.Step two entails choosing the information you wish to include in the table, after which you locate the tables Group on the Insert Tab of the Excel ribbon and choose Pivot Table, as seen in the screenshot below.

3.Make sure the appropriate data are selected in the dialogue box when it appears, and then choose whether you want the table to be added as a new worksheet or placed anywhere on the current worksheet. It is totally up to you to decide what suits your preferences.

4.Configure the pivot table's fields.The Pivot Table Fields box will show up when you finish step two. By dragging and dropping the items that are presented as accessible fields, you can set the fields here. To choose the things you want to appear in the table, use the tick boxes next to the fields.Sort the table.

Once the fundamental pivot table is in place, you may sort the data using a variety of criteria, including name, value, count, and other options. The auto sort button is highlighted in the image below; click it to sort the date. Then, select additional sort options to choose one of the available sorting criteria.Right-clicking anywhere in the table and choosing Sort, more sort options, and other alternatives are more options.

5.Filter the data.A great approach to quickly sort the data is by adding a filter. With the filter function, we can now see the data for particular sub-sections with a single button click. In the example above, we shown how to sort.As shown in the example below, adding a filter is indicated by an additional box that says channel appearing at the top of the pivot table after dragging the channel category from the list of possibilities down to the Filters section.

6.7Next, we may select the filters we want to use by clicking the filter button (as shown below).

7.After completing this step, we can view the revenue, shipping costs, and marketing expenses for all items that were purchased via the Instagram channel, for instance.The data values, edit (calculations).\Excel pivot tables show all data by default as the sum of the data that is currently being displayed in the table. 

For instance, the total revenues by category, total shipping costs by category, and total marketing costs by category are all shown in this table.We can figure out how many products were sold so that we may switch from reporting the total of all revenues to the count of all revenues. For the purpose of reporting, this might be helpful. By selecting Value field settings from the context menu when you right-click on the data you want to edit, the box seen in the screenshot below will appear.

Note: This above written article is an attempt to show you how to use pivot table in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                  

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.