TUTORIAL

How to Do Pivot Tables in Excel

by Danielle Antosz

Project Overview

EXPERIENCE LEVEL: Entry-level

TIME TO COMPLETE: 20 - 30 minutes

ESTIMATED COST: Free with Microsoft Excel

SKILLS NEEDED: Basic Excel navigation

Step 1: Open Your Data in Excel

Open your dataset in Excel. This can be a CSV export, a report, or an existing workbook.

For this training, I've added a download of fake data to use if you don't have enough data to create a PivotTable. All you need to do is:

• Download it

• Copy it into the first column of Excel

• Highlight the column and go to Data → Split Text to Columns

• Choose Comma as Delimited.

It should look like this:

Step 2: Select the Data Range for Your PivotTable

You can manually highlight the entire dataset, or simply click any cell inside the dataset and let Excel detect the range.

Optional: Press Ctrl + T to convert the data into an official Excel Table.

Step 3: Insert a PivotTable from the Ribbon Menu

Go to Insert → PivotTable.

Step 4: Choose Where to Place the PivotTable

Choose New Worksheet (recommended) or Existing Worksheet.

Step 5: Add Fields to Rows, Columns, Values, and Filters

When the empty PivotTable appears, you’ll see the PivotTable Fields panel on the right. This is your control center.

Drag your fields into the four areas:

• Rows → What you want grouped

• Columns → Second-level grouping

• Values → What you want calculated

• Filters → High-level filters

Drag ‘Sales’ to Values.

If Excel defaults to Count instead of Sum, change it in Value Field Settings → Sum.

Step 6: Sort and Filter Your PivotTable Data

Sorting and filtering help drill into your results.

Example: Right-click a numeric column → Sort Largest to Smallest.

Step 7: Apply Number Formatting for Readability

Right-click any number in the PivotTable → Format Cells

Choose the formatting that matches your data (Currency, Number, Percentage, etc.).

Step 8: Use PivotTable Design Options to Customize Appearance

Go to PivotTable Analyze → Design to change layout and styling.

Recommended for readability:

• Report Layout → Tabular Form

• Banded Rows

• Subtotals → Off

Step 9: Refresh the PivotTable When Source Data Changes

PivotTables don’t auto-update. To refresh you'll need to: 

• Right-click anywhere in the PivotTable → Refresh.

• Or use: PivotTable Analyze → Refresh

If your PivotTable isn’t capturing new rows, ensure your dataset is formatted as a Table (Ctrl + T) or update the source range.

Step 10: Save or Export Your Results

Save your workbook, or export the summary for reports and presentations.

Ways to export:

• Copy PivotTable → paste as Values Only

• File → Export → Create PDF/XPS

• Take a screenshot for slide decks

Conclusion

Once you learn the workflow of insert, drag fields, adjust layout, refresh, then PivotTables become one of the fastest ways to understand your data. They’re ideal for IT reporting, finance reviews, operations dashboards, and anywhere you need insights without building formulas from scratch.

Want to level up your Excel skills? Check out CBT Nuggets' Excel training for deeper walkthroughs and hands-on labs. 

Explore more tutorials on the CBT Nuggets Tutorials Page.