Creating Pivot Tables in Excel: A Step-by-Step Guide

Unlock the power of your data with Excel Pivot Tables. This guide provides clear instructions, visual aids, and expert tips to help you summarize and analyze your data with ease, regardless of your Excel version or operating system.

This section details the core steps to create and manage your pivot tables.

1. Prepare Your Data

Ensure your data is organized in a tabular format with unique column headers. Avoid blank rows or columns within your data range.

Screenshot showing well-organized tabular data in an Excel spreadsheet with clear headers and no blank rows or columns.

2. Insert a Pivot Table

Click anywhere within your data range. Go to the 'Insert' tab on the Excel ribbon and click 'PivotTable'.

Screenshot showing the 'Insert' tab in Excel ribbon with the 'PivotTable' button highlighted.

In the 'Create PivotTable' dialog box, Excel will usually pre-select your data range. Choose 'New Worksheet' for the PivotTable to be placed on a new sheet, then click 'OK'.

Screenshot of the 'Create PivotTable' dialog box, showing options for data range and placement (New Worksheet selected).

3. Arrange PivotTable Fields

The 'PivotTable Fields' pane will appear on the right. Drag and drop fields into the four areas: Filters, Columns, Rows, and Values.

Screenshot of the Excel 'PivotTable Fields' pane, with sections for Filters, Columns, Rows, and Values highlighted.
  • Rows: Fields dragged here will appear as rows in your PivotTable.
  • Columns: Fields dragged here will appear as columns.
  • Values: Fields dragged here will be summarized (e.g., Sum, Count, Average).
  • Filters: Use these to filter the entire PivotTable data.

4. Customizing Your PivotTable

To change how values are summarized (e.g., from Sum to Average), click the dropdown arrow next to the field in the 'Values' area, then select 'Value Field Settings...'.

Screenshot of the 'Value Field Settings' dialog box in Excel, showing options to change summary calculation.

Use the 'Design' tab on the Excel ribbon (appears when PivotTable is selected) to change layout, styles, and add/remove subtotals/grand totals.

Screenshot of the 'Design' tab in Excel ribbon, showing various PivotTable layout and style options.

5. Refreshing PivotTable Data

If your source data changes, your PivotTable will not update automatically. To refresh, right-click anywhere on the PivotTable and select 'Refresh', or go to 'Analyze' tab > 'Refresh'.

Screenshot of the context menu in Excel, showing the 'Refresh' option for a PivotTable.

Frequently Asked Questions