Overview
This lesson demonstrates how to build an interactive Excel sales dashboard using tables, pivot charts, and slicers in under 15 minutes.
Preparing and Formatting Data
- Structure data in a table format with columns for each data type (e.g., year, category, product, sales).
- Format the data as an Excel Table using Ctrl+T and confirm headers are included.
- Naming the table helps with clarity but is optional.
Creating Pivot Charts
- Insert a Pivot Chart on a new worksheet based on the Excel table.
- Use the Field List to organize data into Legend Series (columns) and Axis Categories (rows).
- Add a slicer for selecting the year to filter the chart interactively.
- Change chart type to bar chart for long category labels and enhance readability.
- Clean up chart elements by removing unnecessary buttons, grid lines, legends, and axes.
- Format data labels using Value Field Settings, applying number formatting with comma separators.
Customizing Chart Layouts
- Adjust category order and gap width for better visual alignment.
- Position chart titles and slicers appropriately to utilize space.
- Remove slicer headers for a cleaner look and format slicers with no border.
Building Additional Charts
- Copy existing pivot tables to reuse formatting and structure.
- Create line charts for trends over time and add appropriate slicers (e.g., by category).
- Disconnect slicers from charts where filtering is not required.
- For ratings, set Value Field to “Average” and format values as percentages.
Calculating Year-on-Year Changes
- Use “Show Values As” > “Percentage Difference From” to calculate year-on-year change.
- Base comparison on the previous year and format as a percentage.
- Insert column charts for change visualization, removing redundant elements and setting axis labels position to “low”.
Managing Legends and Dashboard Layout
- Avoid repeating chart legends; create a separate “dummy” chart with just a legend for the dashboard.
- Place and resize slicers and charts to fit the dashboard, using Alt to snap to grid.
Updating the Dashboard with New Data
- Add new data to the next empty row in the Excel table.
- Refresh all pivot tables via Data > Refresh All to instantly update all dashboard charts.
Key Terms & Definitions
- Excel Table — A structured range with special formatting and dynamic referencing.
- Pivot Table — A tool to summarize and analyze data quickly.
- Pivot Chart — A chart linked to a pivot table for interactive visualization.
- Slicer — A visual filter for pivot tables and charts.
- Value Field Settings — Options to change data aggregation and formatting in a pivot table/chart.
Action Items / Next Steps
- Download and review the example Excel file.
- Rewatch the video, pausing to build each dashboard component step by step.
- Practice updating the dashboard with new data and refreshing charts.