TIPS for CLICKS : Summarizing large data sets with Pivot Tables

Are you working with large tables in Excel and preparing summary reports? If so, you need to use just some mouse clicks to make interactive summary of your records efficiently. This is it: Pivot Tables tool.

This is mainly for starters. Let's assume, you're creating a large table of data, several columns wide, thousands of rows. It's a good practice to organize the table  in such a way that it

• has unique and relevant headers,
• doesn't contain any blank cells and subtotals, and
• is named (by typing the name in the Table Name box, located under the Table Design tab in the ribbon, at the upper left corner of your worksheet)

Now your table is ready for converting (if not converted yet) into the Excel Table. It might look like in this small sample:

This will make it automatically expanding or contracting as you add new records or remove some old records from it.

At this point you're ready to use the PivotTable tool.

1. Click anywhere in the Excel table, and then
   
2. Click in the ribbon on Insert tab>PivotChart & PivotTable
3. In the displayed Create PivotTable window select your options (preferably a New worksheet for location) and click OK.
4. PivotTable and PivotChart place holders are created for you (at the same time), and the Fields with names of your Excel Table headers are listed in the Fields section at the right-hand part of your worksheet. 

If the PivotChart is initially selected, the PivotChart Fields and the Layout sections are displayed there.

In the Fields section, select those check boxes for columns (fields) you want to add to the Layout section. By default, Excel adds dates and non-numeric fields (dates, text, Boolean) to the Categories axis. The numeric columns (fields) are added to the Values section.

You'll note that both the chart and table are automatically filled with relevant summaries of data and a plot. By default, again, Excel uses the Sum function for numeric data fields, and the Count function for non-numeric data fields. You can choose a different function by right-clicking on selected value field in the table and selecting one of the Summarize Values By option.

The rest belongs to your specific needs and creativity. Your choices. You can e.g.:

• rearrange the fields between the areas of the Layout sections
• choose different calculations (e.g. % or values) in Value fields
• experiment with layouts in order to get the best layout for your summary.

But the following is really good for starters: you can use these special clicks for your Pivot table and Pivot chart:

With your selection anywhere in the Excel Table:

• Click on Insert>Recommended PivotTables, to see and select, if you like, any of several suggestions presented by Excel.

With your Chart selected:

• Click on Insert>Recommended Charts, to see and select, if you like, any of available options.

Here are just examples of my PivotTable and PivotChart I've created for my Excel Table shown above (containing about 2000 records).