Pivot Table Interview Questions and Answers

Pivot table interview questions come up in most interviews for roles that handle data, from admin and sales support to finance and analysis. Interviewers want to know that you can build a pivot, read it correctly and fix the common problems, like a field that counts instead of sums or a pivot that ignores new rows. This guide answers twelve common questions using one small sales table.

The sample data#

All answers use this sales table in A1:C6:

A pivot with Region in Rows and Sales in Values shows North 2600 and South 1650, with a grand total of 4250. Add Product to Columns and North splits into Laptop 2000 and Phone 600.

Basic pivot table questions (1–4)#

These check that you've built a pivot yourself.

1. What is a pivot table, and why use one? A pivot table summarises a table by group, such as total sales by region, without writing formulas. It's fast to build and easy to rearrange: drag a field somewhere else and the summary changes.

2. How do you create one? Click any cell in the data, choose Insert > PivotTable, confirm the range and choose a new worksheet. Then drag fields from the field list into the areas. The beginner's guide to creating a pivot table goes step by step.

3. What are the four areas?

4. What does the data need to look like? One header row, one record per row, no blank rows or columns in the middle, and no merged cells. Each column should hold one type of data, so the Sales column contains only numbers. If the data arrives as a report with subtotals and blank rows, clean it into a plain list first; a pivot built on a messy report double-counts the subtotals.

Calculation questions (5–8)#

These check that you can make a pivot say what you need.

5. Why does a field show Count instead of Sum? Excel uses Count when a value column contains blanks or text. Fix the data, or right-click a value, choose Summarize Values By > Sum. You can also pick Average, Max or Min there.

6. How do you show each region's share of the total? Right-click a value, choose Show Values As > % of Grand Total. North shows 61.2% (2600 ÷ 4250) and South 38.8%. The same menu offers % of column total, % of row total and running totals.

7. How do you group dates by month or quarter? Put the date field in Rows, right-click a date, choose Group, and select Months, Quarters or Years. Recent versions of Excel often group dates automatically when you add them.

8. What is a calculated field? A calculated field adds a new value made from other fields, such as Sales × 10% for commission. Find it under PivotTable Analyze > Fields, Items & Sets > Calculated Field. It works on the totals of each field, not row by row, which matters for things like averages of ratios.

Maintenance questions (9–10)#

These are where many candidates slip.

9. Why doesn't the pivot show new data? Pivots don't update automatically. Right-click and choose Refresh, or use Data > Refresh All (Windows: Ctrl+Alt+F5). If the new rows are outside the original source range, refresh won't find them. The fix is to turn the source into an Excel table (Ctrl+T; Mac: ⌘T) before building the pivot, so the range grows with the data.

10. What is GETPIVOTDATA? When you click a pivot cell while writing a formula, Excel may insert a GETPIVOTDATA formula instead of a plain cell reference. It keeps pointing at the same item even if the pivot changes shape. If you prefer simple references, you can turn this off under PivotTable Analyze > Options.

Comparison questions (11–12)#

These test judgment.

11. When would you use SUMIFS instead of a pivot? When the report must update the moment the data changes, follow a fixed layout, or feed other formulas. The formula that matches the North Laptop cell above is:

=SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Laptop")

It returns 2000, the same as the pivot, and it never needs refreshing. The comparison of pivot tables vs SUMIFS covers the trade-offs, and the SUMIFS function guide explains the syntax.

12. Can you build a pivot-style summary without a pivot? Yes. In Excel 2021 or Microsoft 365, =SORT(UNIQUE(A2:A6)) lists each region once, and a SUMIFS beside it totals each one. The result updates instantly. See how to summarise data without a pivot table.

How to answer pivot table questions well#

Many interviews ask you to build a pivot on the spot, so practise until you can do it in under a minute. As you work, say what you're doing: "Region to Rows, Sales to Values, now switching to % of Grand Total". Mention refreshing and table sources without being asked; it shows real experience. For lookups and dynamic arrays, the other big topics, see the advanced Excel interview questions and the general list of Excel interview questions.

Practise it#

Build your first pivot in Pivot tables 1, then add subtotals, filters and % of grand total in Pivot tables 2. To practise the formula version, work through the pivot-style summary with formulas: you type the formula, and it checks it instantly. For a timed check, try the data analyst Excel mock test.