Building an Interactive Excel Dashboard

Overview

• Purpose: Create an interactive Excel dashboard in less than 15 minutes.
• Approach: Watch the process, then download the Excel file and follow along.

Data Preparation

• Data Structure:
  • Sales data from 2017 to 2020.
  • Data structured in a tabular format suitable for Pivot Tables and formulas (SUMIFS, COUNTIFS).
• Excel Table Format:
  • Use Ctrl + T to format data as a table with headers.
  • Dynamic structured references for automatic updates when new data is added.

Dashboard Creation Steps

1. Insert Pivot Chart

• New Worksheet: Insert a Pivot Chart on a new worksheet.
• Field List Adjustments:
  • Use fields as Legend Series and Axis Categories instead of traditional columns and rows.
• Add a Slicer:
  • Right-click to add a slicer for the year.

2. Chart Customizations

• Change Chart Type:
  • Change from a long category label to a Bar Chart.
• Remove Unnecessary Elements:
  • Remove value field buttons, axis fill buttons, grid lines, and legends.

3. Formatting Adjustments

• Data Labels: Add data labels to the chart.
• Axis Formatting:
  • Change the axis to reverse order.
  • Adjust gap width for better visualization.
• Chart Title: Add and position the chart title appropriately.

4. Additional Charts

• Duplicate and Modify Pivot Tables:
  • Copy existing pivot table for efficiency.
  • Change layout for categories across columns and years in rows.
• Line Chart for Time Series:
  • Suitable for showing data over time.
• Add Slicer for Category:
  • Format slicer to remove headers and adjust style.

5. Year-on-Year Change Charts

• Show Values as Percentage Difference:
  • Format for year-on-year changes for relevant metrics.
• Final Chart Adjustments:
  • Similar formatting as previous charts (remove grid lines, legends, etc.).

Creating a Legend

• Dummy Chart for Legend:
  • Duplicate a chart, remove all but the legend.
• Positioning:
  • Format to fit in the header, ensuring slicers are on top.

Updating the Dashboard

• Adding New Data:
  • Add new data to the next available row in the table.
• Refresh Pivot Tables:
  • Use the Refresh All option to update charts automatically.

Conclusion

• Outcome: Created an interactive dashboard in under 15 minutes.
• Key Takeaway: Utilizing Excel tables allows for efficient data management and dashboard interactivity.
• Resources: Excel file available for download.

Call to Action

• Like the video, subscribe for more content, and share with others who might find it useful.