PIVOT TABLE

Group Pivot Table Items

This example teaches you how to group pivot table items. Learn how to group products and how to group dates by months.

Below you can find a pivot table. Go back to Pivot Tables to learn how to create this pivot table.

Group Products

The Product field contains 7 items. Apple, Banana, Beans, Broccoli, Carrots, Mango and Orange.

To create two groups, execute the following steps.

1. In the pivot table, select Apple and Banana.

2. Right click and click on Group.

3. In the pivot table, select Beans, Broccoli, Carrots, Mango and Orange.

4. Right click and click on Group.

Result:

Note: to change the name of a group (Group1 or Group2), select the name, and edit the name in the formula bar. To change the name of the newly created field (Product2), double click it. To ungroup, select the group, right click and click on Ungroup.

5. To collapse the groups, click the minus signs.

Conclusion: Apple and Banana (Group1) have a higher total than all the other products (Group2) together.

Group Dates

The Date field contains many items. 1/6/2012, 1/7/2012, 1/8/2012, 1/10/2012, 1/11/2012, etc.

To group these dates by months, execute the following steps.

1. Click any cell inside the Date column.

2. Right click and click on Group.

3. Select Months and click OK.

Note: also see the option to group by seconds, minutes, hours, etc.

Result:

Multi-level Pivot Table

It's perfectly ok to drag more than one field to an area in a pivot table. We will look at an example of multiple row fields, multiple value fields and multiple report filter fields.

Remember, our data set consists of 214 rows and 6 fields. Order ID, Product, Category, Amount, Date and Country.

Multiple Row Fields

First, insert a pivot table. Next, drag the following fields to the different areas.

1. Category Field and Country Field to the Row Labels area.

2. Amount Field to the Values area.

Below you can find the multi-level pivot table.

Multiple Value Fields

First, insert a pivot table. Next, drag the following fields to the different areas.

1. Country Field to the Row Labels area.

2. Amount Field to the Values area (2x).

Note: if you drag the Amount field to the Values area for the second time, Excel also populates the Column Labels area.

Pivot table:

3. Next, click any cell inside the Sum of Amount2 column.

4. Right click and click on Value Field Settings...

5. Enter Percentage for Custom Name.

6. On the Show Values As tab, select % of Grand Total.

7. Click OK.

Result:

Multiple Report Filter Fields

First, insert a pivot table. Next, drag the following fields to the different areas.

1. Order ID to the Row Labels area.

2. Amount Field to the Values area.

3. Country Field and Product Field to the Report Filter area.

4. Next, select United Kingdom from the first filter drop-down and Broccoli from the second filter drop-down.

The pivot table shows all the 'Broccoli' orders to the United Kingdom.

Frequency Distribution

Did you know that you can use pivot tables to easily create a frequency distribution in Excel? You can also use the Analysis Toolpak to create a histogram.

Remember, our data set consists of 214 rows and 6 fields. Order ID, Product, Category, Amount, Date and Country.

First, insert a pivot table. Next, drag the following fields to the different areas.

1. Amount Field to the Row Labels area.

2. Amount Field (or any other field) to the Values area.

3. Next, click any cell inside the Amount column.

4. Right click and click on Group.

5. Enter 0 for Starting at, 10000 for Ending at, and 1000 for By.

6. Click OK.

Result:

To easily compare these numbers, create a pivot chart.

7. Click any cell inside the pivot table.

8. On the Insert tab, in the Charts group, click Column and select one of the subtypes. For example, Clustered Column.

Result:

Note: pie charts always use one data series (in this case, Apple). To get a pivot chart of a country, swap the data over the axis. Select the chart. The PivotChart tools contextual tab activates. On the Design tab, click Switch Row/Column.

Slicers

In Excel 2010 or later, you can insert slicers to quickly and easily filter pivot tables. However, using the report filter gives the exact same result.

Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this pivot table.

To insert a slicer, execute the following steps.

1. Click any cell inside the pivot table. The PivotTable Tools contextual tab activates.

2. On the Options tab, insert a slicer.

3. Check Category and click OK.

4. For example, Click Fruit to only show the fruit exported to each country.

Note: notice how the report filter changed to Fruit. Hold down CTRL to include fruit and vegetables.

pdate Pivot Table

Refresh | Change Data Source

Any changes you make to the data set are not automatically picked up by the pivot table. Refresh the pivot tableor change the data source to update the pivot table with the applied changes.

Refresh

If you change any of the text or numbers in your data set, you need to refresh the pivot table.

1. Click any cell inside the pivot table.

2. Right click and click on Refresh.

Change Data Source

If you change the size of your data set by adding or deleting rows/columns, you need to update the source data for the pivot table.

1. Click any cell inside the pivot table.

2. The PivotTable Tools contextual tab activates. On the Options tab, click Change Data Source.

Tip: change your data set to a table before you insert a pivot table. This way your data source will be updated automatically when you add or delete rows/columns. This can save time. You still have to refresh though.

Calculated Field/Item

This example teaches you how to insert a calculated field or calculated item in a pivot table.

Below you can find a pivot table. Go back to Pivot Tables to learn how to create this pivot table.

Calculated Field

A calculated field uses the values from another field. To insert a calculated field, execute the following steps.

1. Click any cell inside the pivot table.

2. The PivotTable Tools contextual tab activates. On the Options tab, click Calculated Field.

3. Enter Tax for Name.

4. Type the formula =IF(Amount>100000, 3%*Amount, 0)

5. Click Add.

Note: use the Insert Field button to quickly insert fields when you type a formula. To delete a calculated field, select the field and click Delete (under Add).

6. Click OK.

7. Drag the Tax field to the Values area.

Result:

Calculated Item

A calculated item uses the values from other items. To insert a calculated item, execute the following steps.

1. Click any Country in the pivot table.

2. The PivotTable Tools contextual tab activates. On the Options tab, click Calculated Item.

3. Enter Oceania for Name.

4. Type the formula =3%*(Australia+'New Zealand')

5. Click Add.

Note: use the Insert Item button to quickly insert items when you type a formula. To delete a calculated item, select the item and click Delete (under Add).

6. Repeat steps 3 to 5 for North America (Canada and United States) and Europe (France, Germany and United Kingdom) with a 4% and 5% tax rate respectively.

7. Click OK.

Result: