When creating a PivotTable, the data used to create the table remainsas it is in the workbook—unchanged. A separate table is produced aftercreation where the data is manipulated to make it more understandable tothe reader. There are a few things you should know before creating aPivotTable to get the workbook data ready to get the most out ofPivotTable reports.
The first point is that data needs to be organized vertically andcontain column headings. The second point is to ensure that no blankrows are present in the data and that there are no additionaldescriptive notes or text in any of the cells or any additional formulato the side or underneath the data. Another recommendation is that youformat the data as a table before creating the table. The only reasonfor this is that any new data rows added to the table are includedautomatically in the range, adding them to the dataset.
Let’s get our data ready and create the PivotTable:
- Open the ChoklatoFlakSales.xlsx workbook.
- On the first sheet, SSGProducts, you will see datarelating to product sales itemized by Salesperson. Before wecreate a PivotTable from this data, we will format the data as atable.
- Click on the data on the worksheet, then go to
Home | Format asa Table from the Stylesgroup.
- Select a table style from the list provided.
- The Format as a Table dialog boxpops up, asking you to confirm the range that was automaticallyselected. Although this is normally accurate, check the range to be surethat all the data is included in the selection. Ensure that you selectthe My table has headers option,too:
- Click on the OK button to formatthe selection as a table.
- To create the PivotTable, go to Insert |PivotTable:
- The Create PivotTable dialog boxpops up, where you can specify the data range and where you would liketo place the PivotTable in the workbook. Excel automatically assumes youare using the table and refers to it in the
Select a table or range option at the top ofthe dialog box. Note that you can also use an external connection.
- We will choose to place the PivotTable in the existing worksheet forthis example and then define which cell to use as the location for thePivotTable. For this example, we will use cell H2:
- The last step is to decide whether to add this data to the datamodel so that you can analyze more than one table. We will add it to thedata model for this example.
- Click on OK to confirm.
- Let’s continue on to the next section by adding fields to build ourPivotTable.