Excel 2020: Create Your First Pivot Table
April 22, 2020 - by Bill Jelen
Pivot tables let you summarize tabular data to a one-page summary in a few clicks. Start with a data set that has headings in row 1. It should have no blank rows, blank columns, blank headings or merged cells.
Select a single cell in your data and choose Insert, Pivot Table.
Excel will detect the edges of your data and offer to create the pivot table on a new worksheet. Click OK to accept the defaults.
Excel inserts a new blank worksheet to the left of the current worksheet. On the right side of the screen is the Pivot Table Fields pane. At the top, a list of your fields with checkboxes.
At the bottom are four drop zones with horrible names and confusing icons. Any fields that you drag to the Columns area will appear as headings across the top of your report. Any fields that you drag to the Rows area appear as headings along the left side of your report. Drag numeric fields to the Values area.
You can build some reports without dragging the fields. If you checkmark a text field, it will automatically appear in the Rows area. Checkmark a numeric field and it will appear in the Values area. By choosing Region and Revenue, you will create this pivot table:
To get products across the top of the report, drag the Product field and drop it in the Columns area:
Note
Your first pivot table might have the words "Column Labels" and "Row Labels" instead of headings like Product and Region. If so, choose Design, Report Layout, Show in Tabular Form. See Specify Defaults for All Future Pivot Tables.
Title Photo: Roman Bozhko at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.