Pivot tables, charts and dashboards

Pivot tables

The fastest way to summarise thousands of rows: drag fields into Rows, Columns, Values and Filters.

Typical questions they answer in seconds:

• Total sales by region and month.
• Number of applications by status for each job.
• Average resolution time by support agent.

Tips:

• Build pivots from a Table so new rows are included on refresh (right-click → Refresh).
• Change value calculations: sum, count, average; Show Values As % of total, % of row, difference from the previous month.
• Group dates by month, quarter or year; group numbers into ranges.
• Use slicers and timelines for interactive filtering.
• Calculated fields for ratios (with care: they sum before dividing).

Charts

• Line for trends over time; bar/column for comparisons; stacked bars for composition; scatter for relationships; avoid 3D and crowded pies.
• Title with the takeaway ("Pune drives 40% of Q3 growth"); label axes; remove clutter; highlight the key series.
• Combo charts (columns + a line on a secondary axis) for values with percentages.

Conditional formatting

Highlight what needs attention: overdue dates in red, top 10% values, duplicates, data bars and colour scales, or formula-based rules (=$E2<TODAY() for overdue rows).

Building a simple dashboard (MIS report)

1. Raw data on one sheet (a Table).
2. Calculations or pivots on another.
3. A dashboard sheet with KPI cards at the top (total, vs target, vs last period), 2–4 charts, and slicers for date and region.
4. Consistent colours and alignment; no gridlines; a "last updated" note.
5. Refresh routine: paste new data → refresh all → check totals.

Interview tasks often give you a raw dataset and 30–60 minutes to produce exactly this. Practise with public datasets.

Check yourself

• Build a pivot showing monthly sales by product category with % of grand total.
• Which chart would you use for 12 months of revenue with a target line?
• Write a conditional formatting rule to highlight rows where status is "Delayed".