{"id":2893,"date":"2021-01-05T10:18:45","date_gmt":"2021-01-05T18:18:45","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=2893"},"modified":"2021-01-05T10:18:46","modified_gmt":"2021-01-05T18:18:46","slug":"how-to-use-pivot-tables","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=2893","title":{"rendered":"How to Use Pivot Tables"},"content":{"rendered":"\n

Pivot tables\u00a0are one of\u00a0Excel’s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.<\/p>\n\n\n\n

Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.<\/p>\n\n\n\n

\"Pivot<\/figure>\n\n\n\n

Insert a Pivot Table<\/h3>\n\n\n\n

To insert a\u00a0pivot table, execute the following steps.<\/p>\n\n\n\n

1. Click any single cell inside the data set.<\/p>\n\n\n\n

2. On the Insert tab, in the Tables group, click PivotTable.<\/p>\n\n\n\n

\"Insert<\/figure>\n\n\n\n

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.<\/p>\n\n\n\n

3. Click OK.<\/p>\n\n\n\n

\"Create<\/figure>\n\n\n\n

Drag fields<\/h3>\n\n\n\n

The PivotTable Fields pane<\/strong> appears. To get the total amount exported of each product, drag the following fields to the different areas.<\/p>\n\n\n\n

1. Product field to the Rows area.<\/p>\n\n\n\n

2. Amount field to the Values area.<\/p>\n\n\n\n

3. Country field to the Filters area.<\/p>\n\n\n\n

\"Drag<\/figure>\n\n\n\n

Below you can find the pivot table. Bananas are our main export product. That’s how easy pivot tables can be!<\/p>\n\n\n\n

\"Pivot<\/figure>\n\n\n\n

Sort<\/h3>\n\n\n\n

To get Banana at the top of the list, sort the pivot table.<\/p>\n\n\n\n

1. Click any cell inside the Sum of Amount column.<\/p>\n\n\n\n

2. Right click and click on Sort, Sort Largest to Smallest.<\/p>\n\n\n\n

\"Sort<\/figure>\n\n\n\n

Result.<\/p>\n\n\n\n

\"Sorted<\/figure>\n\n\n\n

Filter<\/h3>\n\n\n\n

Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France?<\/p>\n\n\n\n

1. Click the filter drop-down and select France.<\/p>\n\n\n\n

Result. Apples are our main export product to France.<\/p>\n\n\n\n

\"Filtered<\/figure>\n\n\n\n

Change Summary Calculation<\/h3>\n\n\n\n

By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.<\/p>\n\n\n\n

1. Click any cell inside the Sum of Amount column.<\/p>\n\n\n\n

2. Right click and click on Value Field Settings.<\/p>\n\n\n\n

\"Value<\/figure>\n\n\n\n

3. Choose the type of calculation you want to use. For example, click Count.<\/p>\n\n\n\n

\"Summarize<\/figure>\n\n\n\n

4. Click OK.<\/p>\n\n\n\n

Result. 16 out of the 28 orders to France were ‘Apple’ orders.<\/p>\n\n\n\n

\"Count\"<\/figure>\n\n\n\n

Two-dimensional Pivot Table<\/h3>\n\n\n\n

If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. First,\u00a0insert a pivot table. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.<\/p>\n\n\n\n

1. Country field to the Rows area.<\/p>\n\n\n\n

2. Product field to the Columns area.<\/p>\n\n\n\n

3. Amount field to the Values area.<\/p>\n\n\n\n

4. Category field to the Filters area.<\/p>\n\n\n\n

\"Create<\/figure>\n\n\n\n

Below you can find the two-dimensional pivot table.<\/p>\n\n\n\n

\"Two-dimensional<\/figure>\n\n\n\n

To easily compare these numbers, create a\u00a0pivot chart\u00a0and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot tables features Excel has to offer.<\/p>\n\n\n\n

\"Pivot<\/figure>\n\n\n\n

Below, please see a wondaful tutorial – by Kevin Stratvert<\/strong><\/p>\n\n\n\n

\n