Mastering Pivot Tables and Dashboards in Microsoft Excel Online: A Step‑by‑Step Guide

Introduction

Favo Frances welcomes learners to a hands‑on session on data analysis with Microsoft Excel Online. This lesson builds on a previous video and focuses on creating clean tables, applying filters and sorts, building multiple pivot tables, visualising the results with pivot charts, and assembling a professional dashboard ready for export.

1. Preparing the Data Set

• Download the data from the link in the video description.
• Convert to editable mode: click the Viewing button near the Share icon, switch to Editing, and confirm the conversion popup.
• Remove blank rows: locate any empty rows (e.g., row 2), right‑click the row header and choose Delete.
• Select the whole range with Ctrl +A.
• Format as a table: click More options ► Format as Table, pick a style (orange shown), ensure My table has headers is checked, then press OK. The data now behaves like a structured table, making filtering and sorting reliable.

2. Filtering and Sorting

• Filtering hides unwanted records. Use the dropdown arrows on column headers, uncheck Select All, then tick the values you need (e.g., only Group A). Click Apply.
• Sorting re‑orders rows. Click a column header (e.g., Race/Ethnicity), choose A → Z or Z → A to arrange alphabetically. The same can be done for Parental Level of Education.
• To reset filters or sorts, select Select All again or use Clear Filter from the sheet menu.

3. Building Pivot Tables

a) First Pivot Table – Total Math Score by Parental Education

1. Click any cell in the table.
2. Go to Insert ► PivotTable.
3. Accept the auto‑selected range and choose New Worksheet.
4. In the PivotField pane, check Parental Level of Education (rows) and Math Score (values). The table now shows total math scores for each education level.

b) Second Pivot Table – Total Reading Score by Race/Ethnicity

1. Copy the first pivot table, paste it a few rows below to create a fresh pivot area.
2. In the PivotField pane, uncheck the previous fields.
3. Check Race/Ethnicity (rows) and Reading Score (values). The result highlights which race/ethnicity group scores highest in reading.

c) Third Pivot Table – Overall Total Score by Parental Education

1. Duplicate the second pivot table, paste it elsewhere.
2. Replace the fields with Parental Level of Education (rows) and Total Score (values). This gives a complete performance view per education level.

4. Creating Pivot Charts

• Column Chart for the first pivot table (Math Score vs. Education).
• Pie Chart for the second pivot table (Reading Score distribution across race/ethnicity).
• Bar Chart for the third pivot table (Overall Score by Education). Each chart is inserted via Insert ► Chart, selecting the appropriate chart type, then resizing using the transform handles.

5. Assembling a Dashboard

1. Click the + icon at the bottom to add a new sheet (named Dashboard).
2. Set a background colour (e.g., dark/black) using the Fill Color tool on the whole sheet selection.
3. Insert a Text Box (Insert ► Text Box) for the dashboard title, centre‑align, increase font size, and optionally add a coloured fill.
4. Move each chart to the dashboard sheet:
5. Right‑click a chart ► Move Chart ► select Dashboard sheet ► OK.
6. Drag the chart into place, ensuring it stays within column I to keep the layout on a single page.
7. Rename sheets for clarity (e.g., Data Set, Pivot Tables, Dashboard) by double‑clicking the sheet tab or right‑click ► Rename.

6. Exporting the Dashboard

• Choose File ► Export ► Download as PDF.
• Adjust settings: paper size A4, orientation Portrait (or Landscape if preferred).
• Click Download to obtain a polished PDF ready for submission or presentation.

7. Final Thoughts

Favo emphasizes patience—watch the video repeatedly if needed, follow each step carefully, and treat the dashboard as a professional deliverable for managers or clients.

Recap of Key Actions

• Clean and format data as a table.
• Apply filters and sorts to explore the data.
• Build three distinct pivot tables for different analytical questions.
• Visualise each pivot table with an appropriate chart type.
• Consolidate charts on a dedicated dashboard sheet, style it, and export as PDF.

This workflow equips beginners with a repeatable process for turning raw data into insightful, presentation‑ready reports using only Excel Online.

By mastering tables, filters, pivot tables, and pivot charts, you can transform raw Excel data into clear, interactive dashboards that communicate insights instantly—no additional software required.