Let’s take a look. Before you create a pivot table, make sure your source data is in good shape. Rows and columns need to be consecutive. If you have any blank rows or columns in the data, remove them first. Next, make sure that each column has a unique label in the first row. These labels will appear in the field list once the pivot table has been created. Now select any cell inside the data. Then click the Pivot Table button on the Insert tab of the ribbon. Excel will open the Create PivotTable dialog box and ask you to select a table or range for the source data. Usually, Excel will guess the location of the data correctly. You should see a moving dashed line in the background around the full set of data. Next, select a location for the pivot table. In most cases, you’ll want to choose New Worksheet. Keeping the Pivot Table on its own worksheet allows it to expand in any direction without overwriting other content. When you click OK, Excel adds a new worksheet to the workbook to the left of the source data. In the new worksheet you’ll see an empty placeholder for the pivot table, and a Field List pane to the right. You’re now ready to start building the table. To add a field to the pivot table, you can just click the checkbox. By default, Excel will add numeric fields to the Value area and text fields to the Row Labels area. We’ll add one of each to make sure things are working properly. To remove a field, simply uncheck the checkbox, or drag the field out of the field list pane. To add a field to the Column Label area drag the field from the Field list to Column labels area. To set the number format of a value field, click the drop-down and choose Value Field settings. Then click the Number Format button and define the format as you like. Laying out a pivot table is an interactive process. Experiment with various layouts to find what works best for your particular application. If you want to clear a pivot table and start over, use the Clear control, on the Options tab of the PivotTable Tools Ribbon. Click, Clear, then choose “Clear All”
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
