Microsoft Excel: Advanced Dashboard Design (Project-Based!)

Explore key data visualization best practices for clear communication, including three key questions, selecting effective charts, eliminating noise, and telling stories with data through line, bar, scatter plots, and maps.

Discover why data visualization matters by turning raw data into life-like charts that reveal patterns instantly. Learn how cognition and visual perception combine to speed understanding of complex information.

Learn how data visualization reveals patterns faster, using the 10/2 rule to compare four products’ sales across 12 months, and understand how a Hanscom Quartet illustrates different visual stories.

Ask three key questions to pick a chart: data type, what to communicate, and end user needs. For time-series data, a line chart illustrates sales trends.

Master essential visuals in Excel for dashboards, including KPI cards, line and stacked area charts, pie charts, bar charts, tables, scatterplots, and maps with best practices.

Apply chart formatting to reduce noise using the 10/2 rule: remove borders, format axis labels, add a title, and highlight top series to show internet overtaking tv in ad spend.

Turn charts into stories by using descriptive titles, data labels, bold text, color, and shapes. Highlight key points with markers and text boxes.

Choose the chart for your data to avoid misleading trends, using bar charts for categorical comparisons instead of line charts. Don't prioritize variety over clarity; keep labels clear, colors consistent.

Learn best practices for data visualization in Excel: choose visuals by data type and audience, favor effectiveness over variety, remove clutter, and tell a clear, data-driven story with descriptive text.

Visualize RIAA data on US music revenues by format from 1973 to 2019 in Excel, highlighting physical vs digital formats, as the data visualization specialist at Maven Music.

Create a stacked area chart in Excel to show how digital and physical music revenues evolve over time, with data selection and chart formatting for clarity.

Apply chart formatting in Excel to clarify music revenue by format, remove borders and gridlines, adjust axis labels to millions/billions, and label physical and digital series directly.

Visualize how the music market shifted to digital formats over physical since the 2003 iTunes launch, using color, labels, and text boxes to tell the story.

Explore dashboard design principles, select visual elements like charts and metrics, and add context to create engaging, decision-driving dashboards with effective layouts.

Learn that a dashboard is a group of visuals that helps you understand data quickly and make smart, accurate decisions. Explore the different types of dashboards.

Explore the types of dashboards—exploratory and explanatory—and design for both with interactive elements that empower end users to slice and dice data, tell a clear story, and support decisions.

Define a clearly defined purpose for every dashboard and tailor it to a specific audience. Ask who the end user is, what they need, and how data helps them act.

Align every metric and KPI with the dashboard’s purpose, identify the right metrics, then choose visuals and define filters and interactivity for the audience and outcomes.

Learn to add context to dashboards by pairing top line KPIs with comparisons to targets and comparable periods, showing trends and seasonality to tell a clear, meaningful story.

Learn how to design effective dashboard layouts by arranging visuals to guide the viewer through a clear story, using top-left emphasis, size and color, and gestalt principles.

Define a clear dashboard purpose by identifying end users and their needs, then select metrics that matter, add meaningful context, and use thoughtful layouts to tell a story.

Develop a project-wide Excel dashboard to explore wage and employment trends by industry, with a state map, using statistical and lookup formulas, custom charts, data types, form controls, and protections.

Create a data prep tab to build a unique industry list for the industry filter, apply data validation, and set a year filter to drive interactive, formula based Excel dashboards.

Compute a conditional average by industry for 2020 using the averageifs function, and prepare data for a dashboard.

Visualize average annual wages by industry with a bar chart to compare categorical data, then adjust bar width, format axis numbers as thousands (K), and clarify labels.

master sorting bar chart data in Excel using formulas, fix cross-worksheet reference quirks, and adjust axis to properly display average annual wages by industry.

Learn to create a donut chart in Excel to show the share of employees by industry, using sumifs to total employees for the current year and formatting for clarity.

Group the donut slices into selected and others to clarify industry shares, and use vlookup to pull the chosen industry's employee count for a dynamic share.

Visualize wage and employee trends by year using a combo chart, with average wages and total employees plotted on dual axes for a chosen industry.

Learn to split a combo chart into two stacked visuals with separate y axes, add data labels using custom formats, and apply gestalt design for a clear, cohesive data story.

Learn to build a filled map in Excel 2019+ for state comparisons of employees by industry and year, using data setup, ifs formulas, and a field map in a dashboard.

Learn how to use Excel's linked data types to fetch state populations for normalizing an employees map by population, producing employees per 1000 capita for clearer visualization.

Switch between employees per 1000 capita and average wages by state on a map using a dynamic column, data validation, and a diverging color scale for dashboard comparisons.

Design the dashboard layout in excel with a tab and data validation; align bar, donut, combo, and map visuals to show wage and employment by industry for high school students.

Use color strategically to differentiate wages and employment across charts, maps, and donuts, applying blue for wages and orange for employment, with a dynamic percentage display.

Highlight a selected industry in the bar chart by adding a conditional data series and applying dynamic formatting and custom number formats for clean, blue emphasis.

Discover how to enhance an Excel dashboard with form controls, linking a spin button and option buttons to cells, and using index and if functions to drive industry selection.

Apply finishing touches to the dashboard by inserting the Maven Careers logo and customizing the theme colors to teal and red, then save the palette for a consistent look.

Hide unused rows and columns to confine the dashboard area. Protect the workbook structure and worksheets, and ensure form controls work by adjusting locked cells.

Build a monthly dashboard in Excel to filter by region, track revenue trends and year-over-year performance, compare stores, and identify top gainers and losers, with data from Jan 2020–Jul 2021.

Convert the sales data into an Excel table, build a data prep tab with a region filter and dynamic year-month logic using structured table references for auto-updating dashboards.

Define named values in Excel's Name Manager for region, current year, and current month to replace hard cell references, simplifying dashboard filter formulas and readability.

Calculate kpis for a monthly revenue dashboard by summing total revenue for the selected region, month, and year, then compute year-over-year and month-over-month changes and format currency.

Learn to create performance indicator cards in a dashboard by linking text to cell values and applying conditional formatting to color-code year-over-year and month-over-month changes with green and red indicators.

Learn to display KPI cards by using linked pictures to preserve conditional formatting in Excel, enabling dynamic updates for year-over-year and month-over-month values.

Set up KPI cards for current-month revenue with comparisons to the previous year and month, then build a revenue trend using absolute structured references and IFS formulas with date filters.

Create and customize a line chart to compare monthly revenue trends by adding series, a month-label axis, axis titles, and a legend, with a thousand-format y axis.

Discover a practical approach to hiding zero values in a line chart by using conditional formulas that return blanks or errors, based on month comparisons.

Create complementary bar charts to compare store revenue and month-over-month performance across stores, using current period totals and previous month changes, with color-coded bars and clear labels.

Learn how to automatically sort bar charts by store revenue in Excel using rank, index, and match, and keep dashboards up to date with dynamic data tables.

Learn to identify top and bottom performing products driving regional revenue by calculating month-over-month changes, ranking by performance, and building dynamic top/bottom product tables using index and match.

Design a regional revenue dashboard in Excel by setting up a dashboard tab, applying a region filter with data validation, and arranging KPI cards, revenue trend, and bar visuals.

Learn how to use color strategically in Excel dashboards to highlight the current month, emphasize the current year, and de-emphasize past data, while leveraging conditional formatting and regional highlighting.

Learn to tell a data story in dashboards by turning titles into guiding questions, adding a current period label, and using color to highlight revenue trends and regional comparisons.

Share the dashboard online via Excel Online for real-time editing; save to OneDrive, unlock the dropdown cell, protect the sheet, and hide the data prep tab and formula bar.

Learn formula-based and conditional formatting to drive a KPI dashboard in Excel. Replace linked pictures with text, and use arrows and color rules to show month-over-month and year-over-year trends.

Visualize insights from Maven hotel group booking data (July 2015 to August 2017) by building a dashboard using pivot charts, conditional number formats, and a layout to communicate business recommendations.

Analyze the FG booking data to reveal how cancellation rates and average daily rates follow seasonality, then design a dashboard to communicate revenue loss and overbooking opportunities to management.

Visualize seasonality in Excel using a pivot chart combo chart to compare average daily rate and cancellation rates, with color, markers, and data labels for a clear dashboard story.

Explore a pivot chart comparing revenue and revenue loss by month, with emphasis on july and august 2016, using a column chart, data labels, and thousands formatting.

Explore conditional custom number formats in Excel to display data labels as millions or thousands based on value, using an if expression and semicolon syntax.

Discover how to replace pie charts with 100% stacked bar charts to reveal the composition of cancellations by lead time, with clean formatting and clear data labels.

Create two bar charts linked to the pivot table to compare cancellation rate and average daily rate; update automatically when filters change.

Design a 1920 by 1080 Excel dashboard by defining a shape boundary, removing grid lines, and organizing visuals into problem and opportunity sections with a final recommendation.

Finalize the dashboard narrative by using text to present the overbooking of same month reservations for the resort hotel in July and August, highlighting cancellations and the 30-day lead-time opportunity.

Save the dashboard as an image by selecting all visuals, copying as a picture, and pasting onto a canvas with a white fill and white outline for easy sharing.

Define the dashboard purpose and audience in Excel to guide design, choose relevant metrics, use effective visuals, reduce clutter, leverage layouts, and tell a compelling data story.

Access Maven Analytics for curated learning paths and instructor support, and practice data visualization and dashboard design with Makeover Monday and the data playground.