PivotTables for Excel tests: a beginner's guide

A PivotTable summarizes a long list without any formulas. Say you have hundreds of sales rows with a region, a product and an amount. In a few clicks, you can see total sales by region, or a count of orders by product.

• Check that your data has one heading per column and no blank rows
• Click any cell inside the data
• Choose Insert, then PivotTable, and confirm the range
• In the field list, drag the category you want to group by, such as Region, into Rows
• Drag the number you want to total, such as Amount, into Values

That's a working PivotTable. You can drag a second category into Columns to create a grid, or into Filters to narrow the results.

• Sum or count: Values may default to Count if a number column contains text or blanks, so check it. You can change it in Value Field Settings
• Putting each field in the right area, since Rows and Columns change the layout
• Sorting results, such as highest region first
• Number formatting, such as showing totals as currency
• Refreshing: a PivotTable doesn't update by itself when the source data changes, so use Refresh

Practice by building one from a blank start a few times, then see if you can do it without pausing. If a test question asks for a total by group, a PivotTable is often the fastest answer, unless it specifically asks for a formula.