Pivot Tables in Excel

Pivot Tables in Excel are an efficient tool for summarizing, analyzing and organizing large datasets. They enable us to group, filter and perform calculations (e.g., sums, averages) on data using a flexible, drag-and-drop interface, transforming raw data into actionable insights without complex formulas.

How to Create a Pivot Table in Excel

Follow these simple steps to build a pivot table in Excel:

Step 1: Preparing the Data

Before creating a pivot table, ensure our data is properly formatted:

• Organize in a Tabular Format: Place our data in rows and columns, with each column having a header.
• Avoid Blank Rows or Columns: Ensure there are no empty rows or columns within our dataset.
• Name our Data Range (Optional): Highlight our data and assign a name with Formulas > Define Name for easier reference.

Step 2: Selecting the Data

1. Click any cell inside our data or
2. Highlight the specific range we want to include in the pivot table.

Step 3: Inserting a Pivot table

• Go to the Insert tab on the Excel ribbon.
• Click PivotTable.
• In the Create PivotTable dialog box:
• Verify the selected data range.
• Choose the location:
• New Worksheet: Places the Pivot Table in a new sheet (recommended).
• Existing Worksheet: Specify a cell in the current sheet.

Shortcut Keys

• Windows: Press Alt + N + V to open the Create PivotTable dialog box.
• Mac: Press Command + Option + P to create a pivot table.

Step 4: Build our Pivot Table

We'll see a PivotTable Field List pane on the right side of our screen. This is where we organize our data:

a) Drag and Drop Fields:

Drag column headers from the Field List into one of the four areas:

• Rows: Sets rows for the table.
• Columns: Creates columns for our data.
• Values: Adds numerical data to be calculated like sum, count, etc.
• Filters: Adds filters to refine our analysis.

b) Customize Calculations:

Right-click on a value in the Values area and choose Value Field Settings. Then, Select the desired calculation like Sum, Average, Count, etc.

Step 5: Formatting and Customizing the Pivot Table

• Apply a PivotTable Style: Select the pivot table and go to Design > PivotTable Styles to apply a pre-designed format.
• Sort and Filter: Use the dropdown arrows on row or column headers to sort and filter data.
• Group Data: Right-click on a row or column item and select Group to organize data by date, number ranges etc.
• Add Slicers (Optional): Go to Insert > Slicer to create interactive filters for our pivot table.

Shortcut Key

• Windows/Mac: Press Alt + J + T + F (Windows) or Command + Shift + P (Mac) to toggle the Field List pane for editing fields.

Step 6: Refresh the Pivot Table

Update the pivot table when source data changes.

• Clicking anywhere in the pivot table.
• Going to PivotTable Analyze > Refresh.

Select Entire Pivot Table Shortcut Key

• Windows/Mac: Press Ctrl + A (or Command + A on Mac) to select the entire pivot table.