A pivot table summarises data. Add a pivot chart and a few slicers, and the same summary becomes an interactive mini dashboard that a manager can click through without touching a formula. It is a skill that shows up in many data analyst interviews.
Step 1: start from a clean table
Convert your data to an Excel Table (Ctrl + T). Tables expand automatically when new rows are added, so your pivot always includes the latest data after a refresh.
Step 2: build the pivot table
Insert a pivot table from the Table. A typical sales example: Region in Rows, Month in Columns, Sales in Values. Check that values use Sum, not Count, which happens when a column contains text or blanks.
Step 3: add a pivot chart
With the pivot selected, choose Insert, then PivotChart. Pick a clustered column chart for comparing regions, or a line chart for trends over months. The chart is linked to the pivot, so any change to the pivot updates the chart.
Step 4: add slicers
Choose Insert, then Slicer, and pick fields such as Product Category or Year. Slicers are buttons that filter the pivot and chart with one click. Add a Timeline for date fields to filter by month or quarter with a slider.
Step 5: connect one slicer to several pivots
Right-click a slicer and open Report Connections. Tick every pivot table that should respond. Now one click filters the whole sheet, which is what makes it feel like a dashboard.
Tips that make it look professional
- Remove chart clutter: field buttons, gridlines and unnecessary legends.
- Give each chart a title that states the insight, not just the field names.
- Format numbers in the pivot (thousands, currency) rather than in the chart.
- Refresh data with Data, then Refresh All, before sharing.
Next, read common pivot table mistakes and dashboard design mistakes beginners make.
Excel, SQL and Power BI are the core of our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
