Create a manual PivotTable if you prefer to design the PivotTable yourself. Here's how.
Want more?
Create a PivotTable to analyze external data
Create a PivotTable to analyze data in multiple tables
This table contains data about book sales.
It has a column for the Genre of the books, the Date they were sold, the Sales Amount, and the Store where they were sold.
Before you create a PivotTable, the data you are going to use (referred to as Source Data) should be arranged correctly.
All the columns should have headings. The headings are used to name the fields in the PivotTable.
Each column contains the same type of data, for example, text in one column and currency in another, and there should be no blank rows or columns.
For this PivotTable, we’ll use source data that is in a table.
It doesn’t have to be in a table, a range of cells can be used as well.
For information about using external data, see the article Create a PivotTable to analyze external data.
For information about using multiple database tables, see Create a PivotTable to analyze data in multiple tables.
There’s a link to both articles in the course summary at the end of the course.
Click any cell in the data. Click INSERT and PivotTable. All of the source data is automatically selected. In this example, the entire SourceData table.
We recommend using a table because, if the table grows, the PivotTable will automatically include the new data when you refresh the PivotTable.
For information about creating a table, see the link Create or delete an Excel table in a worksheet in the course summary.
By default, the PivotTable will be created on a New Worksheet.
If you want, you can create it on an existing worksheet instead, by clicking Existing Worksheet and providing the Location. Click OK. In our example, a new worksheet with an empty PivotTable is created.
In the PivotTable Fields List, at the top, are the fields we’ll use to create the PivotTable.
They are the same as the column headings in the source data.
At the bottom are the four areas of a PivotTable; the fields can be added to: ROWS, COLUMNS, VALUES, and FILTERS.
I check the Genre field and it’s added to the PivotTable as rows of text labels.
By default, text fields are added as rows and numbers as values.
Check the Sales Amount field and it’s added as a column of values that are added using the SUM function.
Right-click a cell in the Sum of Sales Amount column, click Number Format, and click Currency.
I don’t want any digits after the decimal place, so I set the Decimal places to 0, click OK, and we can see the total sales for the different genres of books.
I click and drag the Store field to COLUMNS, and we can see the sales of genres for each store along with grand totals.
An important factor we don’t have in the PivotTable yet are dates. How do sales differ over time?
I check the Date field and it’s added to the ROWS area. But this many rows of dates makes the PivotTable hard to use.
Let’s group the list of dates. Right-click any date, and click Group.
I'll use the default, Months, but you can select one or more options, such as Quarters and Years. Click OK.
The dates are grouped under the genres by month, much easier to work with. And we can see the sales of the genres over time for each store.
When you click any cell in a PivotTable, the PivotTable Fields List and the PIVOTTABLE TOOLS tab appear.
When you click outside of the table, they go away.
Under PIVOTTABLE TOOLS, click the DESIGN tab to change how the PivotTable looks.
For example, click Report Layout, click Show in Outline Form, and now Genre and Date are in separate columns.
Check Banded Rows to make it easier to read across the rows of the PivotTable.
Click the down-arrow next to PivotTable Styles; there are many options.
When you mouse over them, you get a preview of what the PivotTable will look like.
Click the style you want to change the look of the PivotTable.
We’ll cover the ANALYZE tab in subsequent videos.
Up next: Sort, filter, summarize, and calculate your PivotTable data.
