Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Pivot Tables and Data Summaries Interview Questions for Advanced Excel

25 Pivot Tables and Data Summaries questions with answers for advanced excel roles — 11 easy, 11 medium, 3 hard.

About Pivot Tables and Data Summaries

Pivot tables summarise thousands of rows in seconds. This topic covers the four areas, grouping, Show Values As, calculated fields, slicers, distinct counts, GETPIVOTDATA and the pivot cache. Scenario questions test why pivots do not refresh, why totals do not match and how to combine monthly files into one yearly view.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What is a pivot table and when should you use one?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
A pivot table summarises large tabular data by dragging fields into rows, columns, values and filters, without writing formulas. Use it when you need totals, counts or averages broken down by categories such as region, product and month, and when the breakdown changes often. It works best on clean data with one header row, no blank rows and one record per row, ideally stored in an Excel Table.
02

Explain the four areas of a pivot table.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Rows lists categories down the left side, such as Region. Columns spreads categories across the top, such as Month. Values holds the numbers being summarised, such as Sum of Sales or Count of Orders. Filters applies a filter to the whole pivot, such as Financial Year. Moving a field from Rows to Columns changes the layout instantly, which is why pivots are quick for exploring data.
03

Walk through creating a pivot showing monthly sales by region.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Click inside the data, press Ctrl+T to make it a Table, then Insert > PivotTable > New Worksheet. Drag Region to Rows, Order Date to Columns and Amount to Values. Right-click a date and choose Group > Months (and Years if data covers more than one year). Set Value Field Settings > Number Format to #,##0 and add a slicer for Product if needed. Refresh after new data arrives.
04

What is the difference between Summarize Values By and Show Values As?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Summarize Values By chooses the calculation, such as Sum, Count, Average, Max or Min. Show Values As changes how that result is presented, for example % of Grand Total, % of Column Total, % of Parent Row Total, Running Total In, Difference From or Rank Largest to Smallest. Adding the same field twice, once as Sum and once as % of Grand Total, shows the amount and the share side by side.
05

What are slicers and timelines, and how do you connect one slicer to several pivots?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.
06

You added new rows of data but the pivot does not show them. Why, and how do you prevent it?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
The pivot's source is probably a fixed range like A1:H500 and the new rows are below it, or the pivot has not been refreshed. Fix it with PivotTable Analyze > Change Data Source, then Refresh. To prevent it, convert the source to an Excel Table so the range expands automatically, use Data > Refresh All, and enable Refresh data when opening the file in PivotTable Options.
07

The pivot shows Count of Sales instead of Sum of Sales when you drag the field. Why?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Excel defaults to Count when a value column contains blanks or text, such as NA or a number stored as text. Change it through Value Field Settings > Sum, but also fix the source: replace text placeholders with zero or blank, convert text numbers to real numbers, and refresh. Otherwise the text entries are silently left out of the sum and totals will be understated.
08

How do you show only the top 10 customers by revenue in a pivot?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Put Customer in Rows and Revenue in Values. Click the Customer filter arrow > Value Filters > Top 10, choose Top, 10, Items, by Sum of Revenue. Then sort the Revenue column largest to smallest. The grand total by default still reflects only the visible rows; if you need overall totals, add a separate pivot or enable Include filtered items in totals when using the Data Model.
09

Describe a time you taught colleagues to use pivot tables.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Situation: three teammates built monthly summaries by filtering and copying data by hand. Task: my lead asked me to help them speed up. Action: I ran two short sessions using our own sales data, showing how to create a pivot, group dates, add slicers and refresh after new rows, and shared a one-page guide. Result: within a month each of them produced their summary in minutes, and fewer copy-paste errors reached the manager.
10

How do you see the rows behind a pivot value?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Double-click any value cell and Excel creates a new sheet listing every source row that makes up that number. This drill-down is very useful for checking totals or answering a manager's why question. If the file is shared and you do not want people extracting raw data, untick Enable show details in PivotTable Options > Data, though this is not a strong security control.
11

Describe a report you built with pivot tables that helped a decision.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Situation: our store team felt discounts were not driving sales. Task: check it with data. Action: I built a pivot from six months of bills with discount band in rows, month in columns, and sales and margin as values, plus a slicer for store. Result: the pivot showed discounts above 20 percent added little volume but cut margin sharply, and the manager capped discounts at 15 percent the following month.
12

How do you group dates and numbers in a pivot, and why does grouping sometimes fail?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Right-click a date in Rows and choose Group to group by days, months, quarters or years; for numbers, group by a range such as 0 to 100000 in steps of 10000 to create sales bands. Grouping fails with the message Cannot group that selection when the column contains blanks, text values or dates stored as text. Clean the source column, refresh, and grouping works.
13

What is the difference between a calculated field and a calculated item?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
A calculated field creates a new value field from other fields, such as Margin = Profit / Sales, and it works on the summed totals. A calculated item creates a new member inside a field, such as North + East as a combined region. Calculated items cannot be used with the Data Model, can double-count in totals and slow pivots, so many analysts prefer grouping or a source column instead.
14

What is the pivot cache and why does it matter?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.
15

What is GETPIVOTDATA and how do you control it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
When you click a pivot cell while writing a formula, Excel inserts GETPIVOTDATA, which pulls a value by field names instead of cell position: =GETPIVOTDATA("Amount",$A$3,"Region","North","Month","Apr") It keeps working when the pivot layout changes, which suits KPI cards in dashboards. If you want plain references, turn it off in PivotTable Analyze > Options > Generate GetPivotData.
16

How do you get a distinct count, such as unique customers, in a pivot table?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
A normal pivot only offers Count, which counts every row. When inserting the pivot, tick Add this data to the Data Model. Then in Value Field Settings choose Distinct Count, which counts unique customer IDs per region or month. Alternatively, create a DAX measure =DISTINCTCOUNT(Sales[CustomerID]) in Power Pivot, which can be reused across pivots.
17

Finance wants the sales pivot by quarter of the Indian financial year, starting in April. Date grouping only gives calendar quarters. How do you solve it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Pivot date grouping uses calendar quarters, so add a helper column in the source data. For example, fiscal quarter ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0) and a fiscal year column. Use them as row fields instead of grouped dates. In Power Query or a Data Model, put these columns in a proper Date table so every report uses the same April to March logic.
18

Your pivot chart changes whenever someone filters the pivot, but your manager wants a fixed chart for a presentation. What do you do?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
A pivot chart always follows its pivot, so it cannot be frozen while the pivot is filtered. Options: copy the chart and paste it as a picture into the slide; or copy the pivot values to a normal range and build a regular chart from them; or create a second pivot from the same cache, set its filters once and protect the sheet. For a live report, keep the interactive version on a separate sheet.
19

Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put Region and then Product in Rows, and add Sales to Values twice. For the second copy, open Value Field Settings > Show Values As > % of Parent Row Total. Each product then shows its share of its own region, and each region totals 100 percent. Using % of Grand Total instead would show share of the whole company, which answers a different question.
20

How do you stop a pivot from losing formatting and column widths on refresh?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Open PivotTable Options > Layout & Format, untick Autofit column widths on update and tick Preserve cell formatting on update. Set number formats through Value Field Settings > Number Format rather than formatting cells directly, because field-level formats survive refresh and layout changes. For a consistent look across reports, create a custom PivotTable Style and apply it to every pivot.
21

You receive 12 monthly sales files and need one pivot for the year. How do you build it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put all files in one folder and use Data > Get Data > From File > From Folder > Combine & Transform. Power Query appends them using the first file as a sample, adds the source file name as a column and applies the same cleaning steps. Load the result as a Table or to the Data Model and build the pivot on it. Next year, adding a file to the folder and clicking Refresh All updates everything.
22

Tell me about a time you got a last-minute analysis request. How did you handle it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.
23

You have Sales, Products and Regions in three tables. How do you build one pivot from all of them?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
Instead of merging with VLOOKUP, load each Table into the Data Model (Insert > PivotTable > Add this data to the Data Model, or Power Pivot). In Data > Relationships, link Sales[ProductID] to Products[ProductID] and Sales[RegionID] to Regions[RegionID]. The pivot field list then shows all three tables, so category and zone come from the lookup tables while amounts come from Sales.
24

The pivot grand total does not match SUM of the source column. How do you investigate?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
Check for active filters on any field, slicers or timelines, and whether the source range excludes recent rows. Look for text values in the amount column, which pivots ignore, and calculated items that double-count. Confirm date grouping is not hiding rows outside the grouped range. Then double-click the grand total to see the rows the pivot used and compare the row count with the source.
25

Why is averaging a percentage column in a pivot often wrong, and what should you do instead?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
An average of row-level margins gives each order equal weight, so a 100 rupee order counts as much as a 10 lakh order. The correct margin is total profit divided by total sales. Create a calculated field Margin = Profit / Sales, which Excel evaluates on summed values at every level, or a DAX measure =DIVIDE(SUM(Sales[Profit]),SUM(Sales[Amount])). Interviewers use this to test real analytical thinking.

Frequently asked questions

How important is Pivot Tables and Data Summaries in Advanced Excel interviews?

Pivot tables summarise thousands of rows in seconds. This topic covers the four areas, grouping, Show Values As, calculated fields, slicers, distinct counts, GETPIVOTDATA and the pivot cache. Scenario questions test why pivots do not refresh, why totals do not match and how to combine monthly files into one yearly view.

How should I use the Pivot Tables and Data Summaries sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.

More question sheets

Excel Fundamentals and Formatting questionsLogical, Text and Date Functions questionsLookup and Dynamic Array Functions questionsCharts and Dashboards questionsPower Query and Data Cleaning questionsPower Pivot, Data Model and DAX questionsMacros and VBA Automation questions