A PivotTable is an Excel tool for summarizing a list into a simple format. You create PivotTables from lists, as you define which fields should be arranged in columns, which fields should become rows, and what data you wish to summarize. You don’t have to use all of the data in a spreadsheet? Just the data and the fields you need to answer your questions. Once you’ve created the table, you can then see the answer to your question. You can later reuse the PivotTable to answer different questions by rearranging it.
Create a PivotTable
Before creating a PivotTable, consider the questions you want to answer, or which information you wish to extract from your data. This step will determine how you should construct the table.
To create the PivotTable, click somewhere in the list of data, choose the Insert tab, and click PivotTable. Excel will automatically select the area containing the data, including the headings. If it does not select the area correctly, drag over the area to select it manually. Placing the PivotTable on a new sheet is best, so click New Worksheet for the location and then click OK.
You might become confused at this point, because if you’ve never created a PivotTable, nothing you see on this screen will look familiar. In reality, it?s simpler than it looks. The PivotTable Field List panel, as its name suggests, contains the fields from your list; all you need to do is to arrange them in the boxes at the foot of the panel.
In the PivotTable Field List on the right side of the screen, click the Quarter, Category, and Feescheckboxes; this is the data you need to answer the question “How much did we earn from Development for each quarter in 2011?” When you do this, Excel creates a PivotTable for you. The data might not be arranged exactly to your liking, but that’s an easy fix.
If you want to see the categories as columns and the quarters as rows, for instance, drag Quarter to the Row Labels box and drag Category to the Column Labels box. You can now read the answer to your question in the PivotTable, because column B now contains the results for fees earned from Development for the four quarters individually, as well as the grand total for the year.
2 thoughts on “Analyze data with Pivot Tables”