One of the most powerful tools within Microsoft© Excel is the ability to simplify large datasets. Normally this is done with a pivot table. Basically a pivot table is a summary of the referenced data. For example in the screenshot below I have columns of information related to electronics.
You can download this Excel file here and try it for yourself.
The spreadsheet has dozens of rows and would be hard to summarize without the aid of a pivot table. Follow the instructions below to create your own pivot table.
1. Click any single cell inside the data set.
2. On the Insert tab, click PivotTable.
The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.
3. Click OK.
4. The below panel will display and allow you to choose several items such as which rows and columns will display, which filters you would like to filter by any formulas you wish to include. As you will see I have chosen to add Item, Manufacturer, Category and Cost to my pivot table. Category and Cost have been added as Columns and Manufacturer and Items are my Rows. I’ve added the SUM formula also.
5. The above choices present me with a very detailed, but easily understood pivot table which displays totals by Category and also shows the Items as they relate to each Manufacturer. While this looks busy with lots of data it can be simplified by choosing different, and fewer, options.
