Show
Pivot Tables > Get Started People say, "it's easy to make a pivot table" in Microsoft Excel, but you're having trouble getting started. In the steps below, I'll help you make a quick plan, then build a pivot table from sample data. Download my Excel workbook, and follow along. Soon, you can say, "Pivot tables are easy!" Author: Debra Dalgleish What is a Pivot Table?A pivot table is a quick and flexible way to show a summary for thousands of rows of data in an Excel workbook. With a few mouse clicks, Excel builds the report for you, using your dataset, and it's easy to change the layout, at any time later. You can use pivot tables instead of old-style Excel reports, that take a long time to build, because they have typed headings and complicated formulas to calculate the totals. For example, the pivot table in the screen shot below shows a summary of 500 sales orders. Excel did all the construction work for me, and it only took a minute or so! Before You Build a Pivot TableThere are a few important things for you to do, before you build a pivot table. Being prepared can save you lots of time and help you avoid problems later! Here are the 3 preparation steps that I recommend you follow, before building your first few pivot tables.
The details for each step are shown below. Note: After you build a few pivot tables, you might be comfortable doing the preparation steps in your head, instead of on paper. Step 1. Check Source DataBefore you build, check the source data that you plan to use for the pivot table, and see if it is set up correctly. First, be sure that the data is:
For the pivot table example that I'm building, the source data is a named Excel table, with sales records for a food company.
Here are a few ways that I could summarize the data in my pivot table:
Step 2. Set a GoalThe next step is to think about what you want to show in the pivot table. Why are you building it? What do you want to count or sum? (You can use other calculation functions too, but count and sum are used most often.) For example, you might have these goals, if you're creating a pivot table to share with a client, or your boss, or co-workers:
For my example pivot table, I'd like to get a simple report that shows the total number of orders for each City. That could help me set an advertising budget in each city. Step 3. Think About LayoutThe final preparation step is to make a rough sketch of what the completed pivot table should look like.
Don't worry about this step too much though! Try making a simple outline on an Excel sheet, like I often do.
Or, if you think better with a pencil in your hand, sketch out the pivot table layout on a sheet of scrap paper. Either of these methods - Excel spreadsheet or pencil & paper, will help you picture what should go where in the pivot table that you're going to build. The screen shot below shows my layout plan, sketched out on an Excel sheet.
Create a Quick Pivot TableNow that you’ve done the preparation steps, you’re ready to create the pivot table.
There are two pivot table commands in the Tables group, at the left side of the Insert tab:
Both options are explained in the sections below. Recommended PivotTablesIf you’re using Excel 365, or Excel 2013 (or later version), the Recommended PivotTables feature is a great way to get started. With this feature, Excel suggests different pivot table layouts that you could start with, based on the source data that you selected. Use Recommended PivotTablesTo try the Recommended PivotTables feature, follow these steps:
Choose a Recommended PivotTables layout:After you've looked through the list of recommended pivot tables, click on the one that you want to use. Here's how you can choose the most appropriate layout, from all of Excel's suggestions:
For this example, the Sum of Quantity by City is similar to what we need, so click on that layout, then click OK. See the Pivot TableWhen the Recommended PivotTables dialog box closes, Excel inserts a new worksheet in the workbook, with the pivot table that you selected. At least that step is quick and easy! In the screen shot below, you can see the completed pivot table for Sum of Quantity by City. Next, go to the PivotTable Field List section below, to see how to make changes to the pivot table. (You can skip over the Blank Pivot Table section below) Blank Pivot TableIf you’re using an earlier version of Excel, or if you don’t want to use the Recommended PivotTables feature, you can start with a blank pivot table, and create your own layout. To create a blank Pivot Table:
Choose the Data
NOTE: If you want to use a different table or range, you can type an Excel table name or select a different range address in the Table/Range box Choose Where
NOTE: Instead of adding a new sheet, you can click the Existing Worksheet option, and select the sheet where you want the pivot table to be created. Data Model
Click OK
When the Create PivotTable dialog box closes, Excel inserts a new worksheet in the workbook, using the next available sheet number. The outline of an empty pivot table starts in cell A3. Now, go to the PivotTable Field List section below, to see how to add fields to the pivot table. Open PivotTable Field ListNow that you have a pivot table started, you can add or remove fields, or move the fields to a different area. There is a built-in PivotTable Field List, to help with that task. To see the PivotTable Field List:
What's in the PivotTable Fields List?In the next section, you’ll use the PivotTable Fields List to add or remove fields. First, take a look at the 3 main sections in the PivotTable Fields pane.
Each section is described below the screen shot. FieldsNear the top of the PivotTable Fields List pane, there is Field List - this shows a list of the column headings from your Excel table. If a field has been added to the pivot table layout on the worksheet, that field has a check mark.
Note: If you used a Recommended PivotTable layout, you will see a check mark beside the fields that are in the pivot table. In the field list shown above:
Your field list might have different fields checked, or no fields checked. SearchIn Excel 2016 and later versions, there is a Search box above the list of fields. This can help you quickly find a field name in a long list. To use the Search box:
As you type, the field list will be filtered automatically, to only show the field names that contain the string of letters that you typed. Layout AreasAt the bottom of the PivotTable Field List pane are the four different areas of the pivot table. These areas have slightly different names in some versions of Excel:
Note: If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas. In the next section, you'll see how you can drag the pivot table fields into these areas, to add them to the pivot table layout on the worksheet. After you add a field to a layout area, that field will appear in the matching area of the pivot table layout on the worksheet Add Field to Pivot Table LayoutThe quickest way to add a field to the pivot table layout is to use the check boxes in the field list.
Recommended Pivot TableIf you started with a recommended pivot table, add a check mark to the OrderCount field.
Blank Pivot TableIf you started with a blank pivot table, add a check mark to the City and OrderCount fields.
Remove Field From LayoutThe quickest way to remove a field from the pivot table layout is to clear its check boxes in the field list.
Excel removes the field from the pivot table layout, so only the City and OrderCount fields are showing. Add More FieldsAfter you create your pivot table, you can add more fields, to show additional details about the data. Currently, the pivot table shows the total number of orders for each city. In this example, you can add another field, to see which product categories were sold.
Category is added to the Rows area of the pivot table layout, below the City field. Now you can see the total number of orders for each city, and the number of orders for each product category, in each city. You could also add a field to show when the orders were sold. The source data has a Year field, so add a check mark to Year in the field list. Because the years are numbers, Excel adds the field to the Values area, as Sum of Year. We don't want a Sum of the Years -- we want to use that as a heading in the pivot table. Let's fix that! Move the FieldsInstead of using the default location for a pivot table field, you can move it. In this example, the Year field should not show a Sum. Even though the field contains numbers, we don't want to use is in the Values area. To change the Year field to a heading, you can move it to the Rows area or to the Columns area. First, follow these steps to move the Year field to the Rows area:
Now the pivot table shows the number of orders for each year, and the city and category subtotals. That makes a tall narrow pivot table, that is a bit hard to read. To make it easier to compare the totals for each year, you can move the Year field to the Columns area. That will create year heading across the top of the pivot table. Now you can see the data for the years side-by-side, so it's easier to compare the data for each city and the products sold there. Add a Filter FieldPivot tables make it easy to summarize a large amount of data, and sometimes you'd rather focus on part of the data, instead of seeing everything. To focus on data for one of the regions, you can add the Region field as a filter.
The Region filter appears above the Row labels on the worksheet.
The pivot table changes -- only the cities in the West region are visible -- Los Angeles and San Diego. Keep ExperimentingThose are the basic steps for planning, creating, and changing a pivot table. Get the sample workbook at the link in the next section, and experiment with that data. Plan a different type of pivot table, and try to build that. For example:
Then, make a copy of one of your own workbooks, and plan a simple pivot table using your data. Based on the fields in your data, what other types of pivot table reports could you create, to show the where, when, what and how much of that data? The more you experiment, the better you'll get at showing the reports that you need. And no matter how long you've been making pivot tables, it's worthwhile to try a different layout, to see if it makes the information easier to understand. Video: Pivot Table Month CountsHere's another pivot table example, built from a list that just has one column! In this example, 100 people were asked to name their birth month, and the list was entered on an Excel worksheet. This short video below how to build a pivot table that gets a count of the number of times each month was mentioned in the survey results. There are step by step instructions on the Count Duplicates in Months List page, and you can get the sample file there too. Download the Sample FileFood Sales: Download the sample Food Sales workbook for this tutorial, to follow along with the instructions on this page. The zipped file is in xlsx format, and does not contain any macros. What is required to begin creating a PivotTable?Create a PivotTable in Excel for Windows. Select the cells you want to create a PivotTable from. ... . Select Insert > PivotTable.. This will create a PivotTable based on an existing table or range. ... . Choose where you want the PivotTable report to be placed. ... . Click OK.. What is not required in order to begin creating a PivotTable?Which is NOT required? Select an answer: Source data must not have empty rows or columns.
What is one of the requirements before creating a PivotTable?Requirements for Pivot Tables. The most important criteria: Each column must have a title. The title is always the top row of your data. ... . In earlier versions of Excel, each column heading could only appear once. ... . Your data should have a 'database' structure: Each column should have one criteria or value.. What is one of the three different forms that you can arrange the PivotTable into?Change a PivotTable to compact, outline, or tabular form. Click anywhere in the PivotTable. This displays the PivotTable Tools, tab on the ribbon.. On the Design tab, in the Layout group, click Report Layout, and then do one of the following:. |