Creating MS Excel Spreadsheet PivotTables and PivotChart Reports

MS Excel Spreadsheet Pivot Tables are interactive tables that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. One advantage of this feature in Excel is that it allows you to rearrange, hide, and display different category fields within the PivotTable to provide alternate views of the data.

Create MS Excel Spreadsheet or Download Example PivotTableData.xls:

Step 1: Create MS Excel Spreadsheet and determine Need for Pivot Tables and Reports analysis.

Start Microsoft Excel. Pivot Table Functions available since Excel 1997 -- Options Are Same / Graphics are Different up to latest release.

Download the data source file used for this exercise to follow along - Howes-IT-Going MS Excel Spreadsheet Pivot Table Data (PivotTableData.xls)

Create MS Excel Spreadsheet Data PivotTable and PivotChart Report:

Step 2: Create MS Excel Spreadsheet Data PivotTable and PivotChart Report.

pivot01.jpg

Click on the Data menu and choose PivotTable and PivotChart Report.

pivot02.jpg

Answer the questions presented as follows:

pivot02a.jpg

Specify the location of the data you are going to summarize. If you have your data in an Excel list that is currently open, Excel will automatically select the cell range. For instance... Range Sheet1!$A$1:$L$179 from the example PivotTableData.xls should be auto selected when opened.

Click Next.

pivot03.jpg

Ensure the New Worksheet option is selected after you click Next from the previous step, and then click Finish.

pivot04.jpg

Assemble the PivotTable. The PivotTable field list can be a lifesaver for new and advanced users alike.

pivot05.jpg

Looking at the data, let's say you want to see how my sales representatives did. Add SalesRep field to the Row area. To do that, click on SalesRep, change the dropdown to Row Area and click Add.

pivot06.jpg

Next, add the Total field to the Data area.

pivot07.jpg

That just tells you how much of everything they sold, but what if you want to see how much in each category? Add the Category field to the Column area.

pivot08.jpg

Supposing you don't want the Condiments category as part of the analysis, click on the dropdown arrow beside Category and deselect Condiments.

pivot09.jpg

Click OK. Notice how the Grand Totals have been recalculated to not include results from the Condiments category...

AutoFormat MS Excel Spreadsheet Data PivotTable and PivotChart Report:

Step 3: AutoFormat MS Excel Spreadsheet Data PivotTable and PivotChart Report.

pivot10.jpg

To make a PivotTable look good, there are a multitude of AutoFormats available. Click on the Format menu and choose AutoFormat.

You're now done, unless you want to do further analysis and reporting! As always -- Have Fun Coding! [ : )

Go Back to:
http://Howes-IT-Going.com

Steven C. Howes and Howes-IT-Going are NOT Responsible for Usage or Reproduction of ANY Copyrighted Microsoft Titles.
This Webpage, Microsoft Excel Spreadsheet and Pivot Table were for Educational and Discussion Purposes ONLY. Terms of use.

This Webpage and Contents Created by Steven C. Howes © Howes-IT-Going 2003-2012 All rights reserved.