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.