PivotTables, Charts and Dashboards in Excel

A PivotTable summarizes thousands of rows in seconds, without a single formula, and lets you change the analysis by dragging fields with the mouse. It's the tool that impresses most in a job interview and saves the most hours in an office. In this lesson, which opens the Advanced level, you'll learn to create PivotTables, turn them into charts and build a small dashboard.

1. Clean data
2. Create the PivotTable
3. Values and percentages
4. Group and filter
5. Charts and dashboard

Before you start: clean data

A PivotTable only works well if the source data follows lesson 1's rules: headers in a single row, one record per row, no blank rows or merged cells. Ideally your data is an Excel table (lesson 4): when you add records, the PivotTable includes them the next time you refresh.

To practice, use the tblSales table on the practice file's Sales sheet: 300 orders with the columns Date, Region, Salesperson, Code, Product, Category, Units, Price and Amount. Everything you'll see works the same with 2,000 or 200,000 rows.

Create your first PivotTable

1. Click any cell in the data.
2. Go to Insert → PivotTable. Excel detects the range (or the table name).
3. Choose "New Worksheet" and click OK.
4. The PivotTable Fields pane opens on the right: the list of columns at the top and four areas at the bottom.

Drag Region to Rows and Amount to Values. You now have sales by region. Drag Category to Columns and you get a region × category grid. All in under ten seconds.

Set up the values: sum, count, average and percentages

Click the field inside Values → Value Field Settings:

• Summarize Values By: Sum, Count, Average, Max, Min. If a number shows up as "Count" instead of "Sum," the column has blank cells or text.
• Show Values As: "% of Grand Total," "% of Column Total," "Difference From" or "% Difference From" (for example, change versus the previous month). This is the analysis managers usually ask for.
• Number Format: set it from this dialog, not from the ribbon, so it survives every refresh.

Pro tip: you can drag the same field to Values twice, once as "Sum of Amount" and once as "% of Grand Total." That way you see each region's dollars and its share side by side.

Group dates by month, quarter and year

When you drag Date to Rows, Excel usually groups it by years, quarters and months automatically. If it doesn't, right-click a date in the PivotTable → Group → select Months and Years. Always keep "Years" alongside "Months," or January 2025 and January 2026 will be added together.

Slicers and timelines: filters with buttons

With the PivotTable selected, go to PivotTable Analyze → Insert Slicer and choose, say, Region and Salesperson. Buttons appear that filter the table with one click. A Timeline (PivotTable Analyze → Insert Timeline) does the same for dates, with a slider by month or quarter.

One slicer can control several PivotTables at once: right-click the slicer → Report Connections. That's the backbone of a dashboard.

Refresh: the step everyone forgets

A PivotTable doesn't recalculate on its own when the data changes. Click PivotTable Analyze → Refresh (or Alt + F5), or Data → Refresh All (Ctrl + Alt + F5) if you have several. You can also check "Refresh data when opening the file" under PivotTable Options → Data.

Worked example: the Portland coffee roaster, no formulas needed

In the SUMIFS lesson you built a sales-by-region grid by hand. With a PivotTable on tblSales you get it in seconds: Region to Rows and Amount to Values twice, the second with Show Values As → % of Grand Total.

Now swap Region for Product and apply a value filter (Value Filters → Top 10 → 5): the French press brings in $44,135, 36% of all revenue, followed by whole-bean coffee ($21,780) and the 70% dark chocolate ($15,210). Put Date in Rows grouped by month and you get the trend for the year:

Charts: choose the right one

To create a PivotChart, select the PivotTable and go to PivotTable Analyze → PivotChart. The chart responds to the same filters and slicers.

Five rules for professional-looking charts

1. Use a title that states the takeaway: "West sales up 18% in March," not "Sales by region."
2. Delete what adds nothing: heavy gridlines, borders, shadows and 3-D effects.
3. Sort bars from largest to smallest.
4. Use one accent color for what you want to highlight and gray for the rest.
5. Show data labels only when there are few bars.

Your first dashboard in 15 minutes

1. Minute 1Set up two sheetsOne called Dashboard (what people see) and one called Calc (what does the work).
2. Minutes 2–5Three PivotTablesOn Calc: sales by month, sales by region and the top 5 products (Value Filters → Top 10 → 5).
3. Minutes 6–8A PivotChart for eachMove them to the Dashboard sheet (right-click → Move Chart).
4. Minutes 9–10Connected slicersInsert Region and Category slicers and connect them to all three PivotTables with Report Connections.
5. Minutes 11–13Three big KPIsTotal sales, average order and number of orders, with formulas on the data table or with GETPIVOTDATA.
6. Minutes 14–15Final cleanupHide gridlines (View → Gridlines) and line everything up.

With the practice data, your three KPIs should read $122,759.50 in total sales, 300 orders and an average order of $409.20 ($122,759.50 / 300). For a deeper look at the questions analysts answer with pivots, see the PivotTables for analysis lesson in our free data analytics course.

Summary

• A PivotTable summarizes thousands of rows by dragging fields into Rows, Columns, Values and Filters.
• "Show Values As" gives you percentages and changes without formulas.
• Group dates by month and year, and filter with slicers and timelines.
• PivotTables don't recalculate on their own: click Refresh.
• Pick the chart for what you want to show, and title it with the takeaway.

Next lesson: FILTER, SORT, UNIQUE and LET: Excel Dynamic Array Functions. You'll meet formulas that return entire tables and update themselves.