Pivot Tables - 2

Hope many of you are now comfortable with creating pivot tables? The first article ended with creation of a simple pivot table. To summarise, while we create a pivot table we have to select characteristic fields for row, column and page (each can have more than one field) and a numeric field for data.

How to change a pivot table?
Once a pivot table is created it can be changed by going back to the wizard. Just right click any cell in the pivot table and select ‘pivot table wizard’. This will take you to Step 3 of the wizard. Select layout option and do the necessary changes needed.
In case you do not want any change in the structure of pivot table but would only like to update the pivot table with fresh data, than right click any cell in the pivot table and select ‘refresh data’.

Hiding a field – row, column and page
To remove data from a single row or column in a pivot table, hide the associated item. Double-click the field containing the item you want to hide. In the Hide Item box in the Pivot Table Field dialog box, select the items you want to hide. Repeat the above process to unhide and hidden item.

Working with Totals in a Pivot Table
Unless specified otherwise, Excel automatically adds subtotals and grand total lines to pivot table.

Grand Totals provide total values for all cells in rows and columns. Subtotal provide total values for outer items when the pivot table contains more than one row/column field.

To hide the grand total in the pivot table, right click on the pivot table and go to ‘Table Options’. Remove the ticks for ‘Grand Totals for rows’ and ‘Grand Totals for columns’, as shown below.



To hide the subtotals in the pivot table, right click the characteristic field for which you do not need pivot table and select ‘field setting’. Select ‘none’ as shown below.


Other mathematical functions
The default mathematical function would be sum. You can use other mathematical functions like average, counts, maximum, minimum, etc. To use any other mathematical function right click the pivot table and select the pivot table wizard, go to the layout designer wizard. Double click the data field and choose the functions required as shown below.

Use the ‘Options’ button (shown above) to display the data in other forms like % total/row/column, etc. This will be more helpful for analysis.

Sorting of data or Looking at Top N
To sort the data in ascending/descending order of a particular characteristic field right click the field and go to ‘field settings’. Select ‘advanced’ (shown in pic.2 above). A pop-up box as shown below will appear.


Do the needful setting and select Ok.

Pivot Charts
These are as powerful as a pivot table. They present the pivot table in a graphical format and it is very easy to prepare the same. After preparing the pivot table, right click any of the cells on a pivot table and select ‘Pivot Chart’. A pivot chart will be created automatically in a different sheet. The row fields of pivot table would be available on X axis, column field on Y axis and the page field on top left corner of the chart. By default the chart would be a bar chart. To use any other chart like line, pie, etc. select the ‘Chart Wizard’ and select the type.