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.

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

Answer the questions presented as follows:

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.

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

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

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.

Next, add the Total field to the Data area.

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.

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

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.

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! [ : )