Pivot tables is one of the most powerful tools in Microsoft Excel. With PivotTables, you can turn thousands of rows of data into a compact report in seconds. In this lesson, we will briefly review the general information, and also follow an example of creating a pivot table in Excel.
If there is a large amount of data in an Excel sheet, it can be difficult to analyze it. PivotTables can make worksheets easy to manage by bringing data together and processing it in a variety of ways.
Using pivot tables to answer questions
Suppose we want to answer the question: “What is the total amount of sales of each of the sellers?” in relation to the data in the example below. Finding the answer to this question can be difficult and time-consuming, as the data for each salesperson is scattered over many rows. Of course, you can use the command Subtotal, to sum it up on a per-seller basis, however, we still have a lot of information to work with.
Luckily, a PivotTable is able to instantly calculate and summarize the data so that it can be easily read and manipulated. When completed, the pivot table will look something like this:
Once you have created a PivotTable, you can use it to answer various questions by rearranging or pivoting the data. For example, if we wanted to answer the question, “What is the total amount of sales by month?”, we can make our pivot table look like this:
Create a PivotTable
- Select the table or cells (including column headings) that contain the data you want to use.
- On the Advanced tab Insert click command summary table.
- In the dialog box that appears Create a PivotTable enter the required settings, and then click OK. In our example, we will use Table1 as the data source and place the pivot table on a new sheet.
- An empty pivot table and a list of fields will appear on the new sheet.
- After creating a PivotTable, you will need to decide which fields to add to it. Each field is a data source column header. In the PivotTable Fields list, select the check box for each field you want to add. In our example, we want to calculate the total amount of sales of each of the sellers, so we check the boxes in the fields Seller и Order price.
- The selected fields will be added to one of the four areas below the field list. In our example, the field Seller was added to the area Rows, While the Order price – to the region The values. In addition, you can click on the desired field and, holding down the left mouse button, drag it to the required area.
- The PivotTable will calculate and summarize the selected fields. In our example, the pivot table has calculated the total amount of sales for each of the sellers.
As with regular lists in Excel, you can sort the data in a pivot table using the command Sort and filter, which is on the tab Home. In addition, you can apply any type of number formatting, for example, change the number format to Monetary. True, some types of formatting may disappear when you change the pivot table.
If you change any data in the data source worksheet, the PivotTable will not update automatically. To update it manually, select the PivotTable, click the tab Analysis and press Update & Save.