17. Interview Questions and Answers
17.5 Tables, PivotTables and Charts
Q37. What is an Excel Table and why use it?
A structured range (Ctrl + T) that expands automatically, copies formulas down, has built-in filters, banded rows, a Total Row and structured references like tblOrders[Amount]. Pivots and charts based on a Table include new rows automatically.
Q38. What is a PivotTable and when do you use it?
An interactive summary tool that groups and aggregates large data by dragging fields into Rows, Columns, Values and Filters – for example sales by city and platform. I use it for fast exploratory summaries, month/quarter grouping, % of total and top-N analysis without writing formulas.
Q39. PivotTable vs formulas (SUMIFS) – which do you prefer?
Pivots are faster to build and explore, handle grouping and % calculations easily, and refresh in one click. Formulas give fixed layouts, cell-level control and live updates without refresh, which suits formatted reports and dashboards. In practice I explore with pivots and use SUMIFS (or GETPIVOTDATA) for final report layouts.
Q40. What is a calculated field in a PivotTable?
A formula on pivot fields, e.g. Commission = Amount*0.05, added via PivotTable Analyze › Calculations › Fields, Items & Sets › Calculated Field. Note that it operates on summed values, so ratios like AOV should be calculated as =Amount/Orders using summed fields, and Distinct Count needs the Data Model.
Q41. Why is my pivot not showing new data?
The source is a fixed range, not a Table, or it hasn't been refreshed. Convert the source to a Table and use Refresh All; if needed, update it via Change Data Source.
Q42. What are slicers and timelines?
Visual filter buttons for PivotTables, PivotCharts and Tables. A timeline is a date slicer that filters by days, months, quarters or years. With Report Connections one slicer can control several pivots built on the same source.
Q43. What is GETPIVOTDATA?
A function that returns a specific value from a PivotTable, e.g. =GETPIVOTDATA("Sum of Amount",$A$3,"City","Pune"). It keeps returning the right value even if the pivot layout changes, which is useful for KPI cards.
Q44. How do you show the top 5 regions or products?
In a pivot: Value Filters › Top 10 › 5 Items; with formulas: LARGE with INDEX-MATCH, or in Microsoft 365 =TAKE(SORTBY(Products,Sales,-1),5).
Q45. Which chart would you use for which purpose?
Line for trends over time; column/bar for comparing categories; pie/doughnut for part-of-whole with few categories; scatter for relationships; histogram for distributions; combo with a secondary axis for two measures on different scales; waterfall for contributions to a change.
Q46. How do you make a dynamic chart?
Base it on a Table so it grows with the data, or on a helper range driven by a drop-down (Data Validation + XLOOKUP/INDEX), or on dynamic named ranges (OFFSET/INDEX). PivotCharts with slicers are the quickest interactive option.
Ravindra Bagale's Tip
Many students give a one-sided answer to "Pivot vs formulas" ("pivot is always better"). Give a balanced answer – pivot for exploring, SUMIFS/GETPIVOTDATA for a fixed report – and say what you used in your project.
Ravindra Bagale's Tip – मराठी
बरेच students "Pivot vs formulas" ला एकाच बाजूने उत्तर देतात ("pivot is always better"). Balanced (संतुलित) उत्तर द्या – explore साठी pivot, fixed report साठी SUMIFS/GETPIVOTDATA – आणि तुमच्या project मध्ये काय वापरलं ते सांगा.
Ravindra Bagale's Tip – हिंदी
बहुत से students "Pivot vs formulas" का एकतरफ़ा जवाब देते हैं ("pivot is always better"). संतुलित (balanced) जवाब दो – explore करने के लिए pivot, fixed report के लिए SUMIFS/GETPIVOTDATA – और बताओ कि अपने project में तुमने क्या इस्तेमाल किया.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of tables pivottables and charts on a blank sheet for BrightLedger Finance.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Vikram Rao and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Oliver Bennett.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply tables pivottables and charts to a 5-row made-up BrightLedger Finance table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with tables pivottables and charts.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as BrightLedger_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
