← 30 Days Excel Series
Day 9 of 30IntermediatePivot Tables

Day 9: Pivot Tables — Advanced

5 questions · Excel Interview Preparation

Q1

What is a Calculated Field in a Pivot Table and how do you create one?

A Calculated Field adds a formula-based column to the Pivot Table using the existing fields as variables — without modifying source data. Go to PivotTable Analyze → Fields, Items & Sets → Calculated Field. Name the field (e.g. "Profit Margin") and enter a formula using field names: = Revenue - Cost, or = (Revenue - Cost) / Revenue. The field appears in the Values area and is calculated correctly at all aggregation levels.

💡 Interview tip: Calculated Fields use aggregated values (totals, not row-level), which can cause unexpected results for ratios. For row-level calculations, add a column to the source data instead.
Q2

What is a Pivot Table Slicer and how does it improve reporting?

Slicers are visual filter buttons that sit on top of your Pivot Table. Insert → Slicer → select fields. Clicking a slicer button instantly filters the Pivot Table to show only that value. One Slicer can control multiple Pivot Tables simultaneously — right-click the Slicer → Report Connections → select all Pivot Tables to connect. This makes interactive dashboards where a single click updates all related charts and tables.

💡 Interview tip: Connect one slicer to multiple Pivot Tables for dashboard-style reporting — this is a senior-level Excel skill interviewers look for.
Q3

How do you show values as % of grand total in a Pivot Table?

Click any cell in the Values area → Value Field Settings → Show Values As → % of Grand Total. This shows each cell as a percentage of the overall total. Other useful "Show Values As" options: % of Row Total (row-level percentages), % of Column Total (column-level), Difference From (shows change from a reference item — useful for month-over-month comparison), Running Total (cumulative sum by date or category).

💡 Interview tip: "Show Values As" → "Difference From" for YoY or MoM comparison is an advanced skill — if you know this, mention it.
Q4

What is Power Pivot and how does it relate to Pivot Tables?

Power Pivot is Excel's in-memory data modelling engine (available in Excel 2016+ Professional). It allows you to: load much larger datasets than a worksheet can hold, create relationships between multiple tables (like a database), write DAX formulas for complex calculations, and build Pivot Tables on the combined data model. A regular Pivot Table works from one flat table; a Power Pivot-backed Pivot Table can join multiple tables without VLOOKUP.

💡 Interview tip: Power Pivot is asked at senior analyst level. Knowing the difference between a flat Pivot Table and a Data Model-backed one shows depth.
Q5

How do you prevent a Pivot Table from collapsing when the source data changes?

A Pivot Table may collapse or lose its layout when refreshed if items disappear from the data. Fix: PivotTable Options → Data tab → check "Retain items deleted from the data source". Also, if you want the Pivot Table to always expand to the latest data range, ensure your source is an Excel Table (Ctrl+T) — Tables auto-expand when new rows are added, and the Pivot Table's source updates automatically.

💡 Interview tip: Using a Table as the Pivot Table source is the most important habit for production reporting — it eliminates the "missed new rows" problem.
← Day 8All DaysDay 10 →

Want 1:1 Excel coaching?

Join EVIKA Academy for live Excel training, real projects, and placement support in Delhi NCR.

Book Free Demo →
Best Data Analytics Course in Noida Delhi NCR | EVIKA ACADEMY