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.
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)
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.
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...
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: