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.

Pivot Tables - 1

I have been receiving numerous feedbacks to write about pivot tables. It was only when I started writing I realised that writing about pivot tables in a manner that can be easily understood by layman is not an easy task. In this article I have put down an introduction to pivot tables and method of creating a simple pivot tables. We will continue this with more enhanced features on pivot table in articles to follow.

Pivot Table – An introduction
A pivot table is an interactive worksheet table that quickly summarises large amounts of data using a format and calculation methods you choose. It provides an easy way for you to display and analyse summary information about data already created in Microsoft Excel.
For example, with a Microsoft Excel database that contains sales records for several sales managers, a pivot table can help analyse sales data by name, region, month, year, etc that can be chosen from source data.
The table below is the source data:
Month
Year
Type
Sales Managers
Sales Rs.
Sales Units
Region
Jan
2005
Beverages
Mr. A
2354
50
East
Jan
2005
Hair Oil
Mr. B
3312
100
Nort
Jan
2005
Edible Oil
Mr. C
5456
50
South
Feb
2005
Beverages
Mr. B
5419
300
West
Feb
2005
Hair Oil
Mr. C
8943
200
East
Feb
2005
Edible Oil
Mr. A
9866
500
Nort
Mar
2005
Beverages
Mr. C
1425
100
South
Mar
2005
Hair Oil
Mr. A
3218
55
West
Mar
2005
Edible Oil
Mr. B
6589
30
East
Apr
2005
Beverages
Mr. C
6456
110
Nort
Apr
2005
Hair Oil
Mr. A
1211
250
South
Apr
2005
Edible Oil
Mr. C
9553
355
West

A sample pivot table based on above table is shown below:
Sum of Sales Rs.
Sales Managers



Month
Mr. A
Mr. B
Mr. C
Grand Total
Jan
2354
3312
5456
11122
Feb
9866
5419
8943
24228
Mar
3218
6589
1425
11232
Apr
1211

16009
17220
Grand Total
16649
15320
31833
63802

‘Month’ and ‘Sales Managers’ field is used from the source table to organise data in pivot table.

The data of ‘Sales Rs.’ is used from the source table and is summarised and totaled.

Steps to create a simple pivot table
You create a pivot table using the Pivot Table wizard. The Pivot Table wizard is an interactive set of dialog boxes that guide you through the steps of choosing the source data and layout you want to use for the pivot table.
From Data menu, choose the Pivot Table command. Step 1 of the wizard will pop-up as shown:


Where is the data that you want to analyse?
Select Microsoft Office Excel list or database (Option 1)

What kind of report do you want to create?
Select Pivot Table (Option 1)

Click Next

Step 2 of the wizard will pop-up as shown:

Select the entire source data as the range and click Next

Step 3 of the wizard will pop-up as shown:

If you want your pivot table in the same sheet as the source table select Existing worksheet, else select New worksheet.

Click on ‘Layout’, the layout designer wizard will appear as follows:

Drag-drop the ‘Month’, ‘Sales Manager’ and ‘Sales Rs.’ field as shown above. The layout should look as shown below:


Click Ok and then click Finish. Your Pivot table as required is ready.

Try creating similar pivot tables with other fields. Also use Page (as highlighted above) in the layout designer and see the powerful analysis that it helps you with. The above article will help you create simple pivot tables, we would see some more features of pivot tables in articles to follow.