Building an Interactive Excel Dashboard in Less Than 15 Minutes

Introduction

• Goal: Build an interactive Excel dashboard in under 15 minutes.
• Suggestion: Watch once, then re-watch pausing/rewinding as you replicate.
• Highlight: Demonstrates how structured data enables fast dashboard creation.

Initial Setup

• Dashboard sheet: Regular sheet, no add-ins needed. Contains formatted header and column widths.
• Data: Sales data for 2017-2019; adding 2020 data to show update process.
• Key: Data structured in tabular format suitable for pivot tables and formulas.

Formatting Data

• Convert to Excel table using Ctrl + T.
• Ensure table has headers.
• Example table named Table1.
• Use dynamic structured references for automated updates with new data.

Inserting Pivot Chart

• Insert a pivot chart on a new worksheet.
• Build pivot table via field list adjustments (legend series, axis categories).
• Add slicer for the year.
• Change chart type to bar chart for longer category labels.
• Remove unnecessary buttons and grid lines on the chart.
• Apply data labels and adjust formatting (e.g., comma separators).

Chart Customizations

• Categories and Products sorted in reverse by default; reverse order in axis settings.
• Adjust column width (gap width ~50%).
• Reformat and position chart title; place slicer above chart.
• Move slicer within chart area for cleaner look; cut and paste entire setup to dashboard sheet.
• Ensure slicer only filters assigned chart via report connections.

Adding Additional Charts

• Copy and paste existing pivot table to create new one; retains formatting.
• Adjust pivot table fields and insert appropriate chart type (line chart for time-series data).
• Customizations: Hide fill buttons, add title, remove grid lines/legend, adjust axis position.
• Additional slicer for categories; similar format and placement adjustments as previous slicer.

Calculating Year-on-Year Changes

• Calculate year-on-year percentage difference via Show Values As setting for pivot table.
• Format number as a percentage with no decimal places.
• Insert chart for year-on-year change; customize similarly (remove buttons, add labels/title, position axis).

Legend and Final Adjustments

• Create a dummy chart for a unified legend to avoid repetition in all charts.
• Resize and position legend chart appropriately on dashboard, layering other elements if needed.
• Update connections ensuring all charts are filtered as necessary.

Updating Dashboard with New Data

• Add new data to the bottom of Excel table; table formatting auto-applies.
• Refresh all pivot tables from data tab (Refresh All button) to include new data.
• All charts in dashboard automatically update to reflect new data.

Conclusion

• Demonstrated the creation of an interactive Excel dashboard in under 15 minutes.
• Key takeaway: Structured data formatting in an Excel table facilitates quick and easy dashboard updates.
• Encouragement to download the Excel file for practice.
• Call to action: Like, subscribe, and share the video.