Interactive Excel Dashboards & ONE CLICK Update!

Inda Tracy introduces a webinar on creating an interactive Excel dashboard, explaining the concept of a dashboard as a visual display of key information for monitoring objectives. She shares her background as an accountant and her transition to teaching Excel, highlighting her contributions to the Excel community. The webinar promises to cover the mechanics of building a dashboard using Excel's built-in tools without add-ins or VBA, focusing on interactivity with slices and dynamic labels. The data set used is a detailed sales record from two Australian fashion stores, formatted for easy updates and analysis.

The session begins with formatting the data into an Excel table, using the keyboard shortcut Ctrl+T, and naming the table for better organization. The first pivot table is created for a line chart representing sales by chain, with dates grouped by months and years. The importance of formatting the pivot table values is emphasized, as it influences the appearance of the pivot chart. The process includes inserting a pivot chart, adjusting its layout, and using slices for filtering instead of field buttons for a cleaner look.

The speaker proceeds to create various charts for different segments of the data, such as sales by category and sales by manager, using bar charts for readability and sorting data in descending order for better visualization. Each pivot table and chart is carefully named for clarity, and the process of copying sheets to maintain the same source data and pivot cache is explained. The creation of a pie chart is also covered, with a focus on dynamic labels that will update based on slicer selections.

The script describes the creation of sparklines and the use of a map chart, a feature available to Office 365 Excel 2016 users. Sparklines are added to show trends over time, and the script explains the workaround for creating a dynamic range that grows with the pivot table. The map chart requires a tabular format with repeated item labels, and the data is pasted as values to create the chart, which is then linked back to the pivot table for data updates.

The process of assembling the dashboard is detailed, including adding a new sheet for the dashboard, formatting the header, and placing charts onto the dashboard using cut and paste commands. Sparklines are manually inserted, and the script explains how to reference pivot tables for the data. Conditional formatting with data bars is applied to visualize the size of numbers, and the importance of consistent formatting is discussed.

The script covers the insertion of sparklines to show data trends and the selection of appropriate colors for different chains. It also explains the process of inserting and formatting slices for user interactivity, emphasizing the importance of aligning slices to the grid for a polished look. The removal of gridlines and the creation of a manual legend for consistent color coding across charts are also discussed.

The creation of dynamic labels for the pie chart is explained, using concatenation functions to update labels based on slicer selections. The script details the process of making slicer selections update all connected pivot tables and charts, ensuring that the dashboard is fully interactive. The importance of naming conventions for easy slicer connections is highlighted.

The final steps include applying themes for consistent colors and fonts, aligning and grouping objects for ease of movement, and hiding unnecessary Excel interface elements for a clean look. The script demonstrates how to update the dashboard with new data by refreshing all pivot tables and charts with a single click, emphasizing the efficiency of the process.

The script concludes with information on protecting the dashboard, disabling slicer edit mode, and hiding worksheets to prevent accidental changes. It also mentions the possibility of embedding the dashboard on a web page while maintaining functionality. Additional resources, such as a chart recipe eBook and an online Excel dashboard course, are provided for further learning.