Excel · Week 4 · Data Analyst Program

Learning objectives

By the end of this week you will be able to:

Build a PivotTable and use the Rows, Columns, Values and Filters areas.
Group dates by month and show values as a percent of total.
Create PivotCharts that update with the PivotTable.
Combine several PivotCharts and slicers into a one-page dashboard.

Lessons

Pivot Tables

A PivotTable summarizes a big table without any formulas. You drag fields into four areas:

Rows: the categories down the side (for example Region).
Columns: categories across the top (for example Product).
Values: the numbers to calculate (for example Sum of Sales).
Filters: limit the whole table to one slice (for example Year = 2026).

To build one: click anywhere in your data (ideally a Table), choose Insert > PivotTable, pick New Worksheet, then drag fields from the field list.

Useful settings:

Change the calculation: click the field in Values > Value Field Settings to switch Sum to Count or Average.
Show percentages: Show Values As > % of Grand Total.
Group dates: right-click a date in the PivotTable > Group > Months and Years.
Refresh: a PivotTable does not update automatically. After the source data changes, right-click > Refresh.

Think of a PivotTable as the answer to a question. "What were sales by region by month?" becomes Region in Rows, Month in Columns, Sum of Sales in Values.

Pivot Charts

A PivotChart is a chart linked to a PivotTable. Click inside a PivotTable and choose PivotTable Analyze > PivotChart. When you filter or rearrange the PivotTable, the chart changes too.

Choosing the chart type:

Column or bar: compare categories (sales by region). Use bars when labels are long.
Line: show change over time (sales by month).
Pie or doughnut: share of a whole, only with a few slices (five or fewer).

Make it readable: give it a title that states the point ("North leads Q3 sales"), remove unnecessary gridlines and legends, sort bars from largest to smallest, and right-click > Hide All Field Buttons for a cleaner look.

Excel Dashboard

A dashboard is a single screen that answers the most important questions at a glance. A simple Excel dashboard has three layers:

Data sheet: your clean Table.
Analysis sheet: several PivotTables, each answering one question.
Dashboard sheet: the PivotCharts and a few key numbers (KPIs), arranged neatly, with gridlines turned off (View > Gridlines).

Make it interactive with slicers. A slicer is a set of filter buttons. Click a PivotTable > Insert Slicer > choose Region. Then right-click the slicer > Report Connections and tick every PivotTable so one click filters all the charts at once. A Timeline does the same for dates.

Design rules: put the most important number at top left; keep to two or three colors; line charts up on an invisible grid; label every number with what it is and its unit. Ask yourself, "Could a manager understand this in ten seconds?"

Watch

VideoExcel Pivot Table Tutorial - 5 Easy Steps for Beginners
Build your first PivotTable.
VideoHow to Create Excel Pivot Tables and Pivot Charts - Beginner's Guide
Moves from PivotTables to linked charts.
VideoCreating a Pivot Table Dashboard with Slicers (15 minutes)
Connects slicers to several charts.
VideoExcel Dashboard Tutorial for Beginners
A full beginner dashboard from start to finish.

Practice

Expand your sales data to at least 100 rows (copy and change values, or download a free sample sales dataset). Then build three PivotTables: Sales by Region, Sales by Month, and Profit by Category. Create a PivotChart for each, add Region and Category slicers connected to all three, and arrange everything on a sheet named Dashboard with two KPI numbers at the top (Total Sales and Total Profit).

Check your understanding

Try each question first, then open it to see the answer.

Your source data changed but the PivotTable did not. What do you do?

Right-click the PivotTable and choose Refresh (or Data > Refresh All).

Which chart type best shows sales over 12 months?

A line chart.

How do you make one slicer filter three PivotCharts?

Right-click the slicer, choose Report Connections, and tick all three PivotTables.

Assignment

Course project: submit a one-page Excel dashboard (data, analysis and dashboard sheets) with at least three PivotCharts, two KPI numbers and working slicers. Include a text box with three insights you found.