If you use Excel often to compile and analyze data, pivot tables can be a great tool to add to your repertoire. Many Excel users are intimidated by pivot tables, but with a little practice, they can be easy to use and will give you powerful reporting capabilities. At its simplest, a pivot table is simply a report that sorts and groups raw data into a more usable format. Let’s walk through the steps of creating a pivot table using a simple spreadsheet of data.
Step 1: Enter Raw Data – First, you’ll need some data to use in your pivot table. In this example, we’ll look at sales numbers for a chain of sporting goods stores. Here is the sales information that we want to summarize with a pivot table:
Step 2: Define the Problem – Before you start creating a pivot table, try to figure out the question you’re trying to answer. In this example, let’s say that management wants to compare sales between the three stores. You may be tempted to just add up the sales amounts by hand, which would be easy enough in this case, but imagine if you were wading through a larger data set. Doing the work manually could take hours, whereas a pivot table will do the calculations for you with just a few clicks of the mouse.
Step 3: Create the Pivot Table – To add a new pivot table to your Excel workbook, click the “Insert” tab and then click the first button on the left, “PivotTable”. A pop-up window will appear asking you which data to include and where you want the pivot table in your workbook. Excel will default to include all the data on your sheet, which will usually be what you want. By default, the new pivot table will be created on a new worksheet, which is a good idea in case you decide to delete it later. Click “Ok” on the pop-up window and Excel will open a new worksheet where you can specify the settings for your pivot table.
Step 4: Build your Report – The next step is to choose the fields that you wish to display in the pivot table. In this example, we want total sales by store, so we need the “Store” and “Sales” columns. Look for the “PivotTable Field List” dialog box on the right of your screen. Click the checkbox beside “Store” to add that field to the report. On the left of the screen, you should now see a list of the stores from the original data. Go back to the field list display area and click the checkbox beside “Sales” to add the totals to the report. With both checkboxes ticked, the spreadsheet area of the page will now show the information you need in your report, as shown below:
Step 5 – Format your Report – At this point, you have created a working pivot table. You may wish to do some formatting to make the data easier to read and understand. Below is the same information as above, but with more specific column names and with the sales amounts formatted as currency:
Pivot tables can seem complicated and time-consuming at first, but they are actually quite simple to use and very helpful in answering business questions.