Leave a Reply

Your email address will not be published. Required fields are marked *

Dashboards aren’t scary!

In this video, let’s make a starter dashboard in Microsoft Excel.

You’ll learn how to make four quick visuals:

  1. Sparklines
  2. Data bars
  3. Symbol fonts
  4. Color scales

I use these visuals over and over in my real-life consulting projects.

Watch the Tutorial

Sparklines

Sparklines are helpful for visualizing patterns over time, like daily, weekly, monthly, quarterly, or annual data.

To create sparklines:

  1. Highlight the first row of your table.
  2. Go to the Insert tab.
  3. Go to the Sparklines section.
  4. Click on the first one (a Line sparkline).
  5. Choose where we want to put the sparklines (off to the right of the table).
  6. Click insert and enjoy the sparklines!

We can also edit our sparklines!

We might adjust the data source, type (from line to column), or color. I typically gray everything out and highlight a high point or low point in a dark brand color.

We can also group and ungroup our sparklines (e.g., if we want each category in our dashboard to have its own color).

And if we change our mind, we can clear them out.

Ann K. Emery teaches you how to make a starter dashboard in Excel with sparklines, data bars, symbol fonts, and heat tables.

Data Bars

Data Bars give us horizontal bars (as opposed to sparklines’ vertical columns).

They’re helpful for visualizing summary statistics like totals or averages.

To create data bars:

  1. Highlight the cells you want to visualize (e.g., the total column).
  2. Go to the Home tab.
  3. Click on the Conditional Formatting button.
  4. Select solid-filled data bars.
Ann K. Emery teaches you how to make a starter dashboard in Excel with sparklines, data bars, symbol fonts, and heat tables.

Symbol Fonts

I use checkboxes to visualize whether I met a goal or target.

We can get quick checkboxes through symbol fonts!

In the video, you’ll see me write an =if() statement to transform g’s and c’s into Webdings checkboxes.

Audiences love the checkboxes. They’re intuitive, colorblind-friendly, and grayscale printing-friendly.

Ann K. Emery teaches you how to make a starter dashboard in Excel with sparklines, data bars, symbol fonts, and heat tables.

Color Scales

a.k.a. heat maps or heat tables.

I love color scales for visualizing the interior of my table—when I want to compare lots of rows and columns to each other.

To create color scales:

  1. Highlight the cells you want to visualize (i.e., the interior of the table).
  2. Go to the Home tab.
  3. Click on the Conditional Formatting button.
  4. Select Color Scales. Most of the time, we’ll use a Green-White Color Scale. That’ll make the big numbers dark (and the small numbers will be light).
Ann K. Emery teaches you how to make a starter dashboard in Excel with sparklines, data bars, symbol fonts, and heat tables.

Combos

In real life, we might combine several of these techniques.

We might add color scales to the interior of the table…

We might compare the totals with data bars…

We might add Webdings checkboxes to see whether we met a goal…

And we might add more data bars to see how far we were over or under our goal.

Ann K. Emery teaches you how to make a starter dashboard in Excel with sparklines, data bars, symbol fonts, and heat tables.

Formatting

In real life, we’d edit these quick visuals.

I suggest:

  • Using brand colors and brand fonts.
  • Outlining the color scales in white (so the cells can be differentiated against each other).
  • Placing the data bars in a separate column than their numeric labels.
  • Coloring the checkboxes (rather than boring black).
  • Adjusting the colors in the over/under bars (to avoid scary red).
  • Moving the labels to the over/under bars to their own column (via an =if() statement to save time).
Ann K. Emery teaches you how to make a starter dashboard in Excel with sparklines, data bars, symbol fonts, and heat tables.

Download this Spreadsheet

Try it yourself!

Download this spreadsheet.

Explore the completed version with the =if() statements.

Use the empty version to practice alongside me as you replay the video.

Get in Touch

If you get stuck, reach out o­n LinkedIn.

More about Ann K. Emery
Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

Leave a Reply

Your email address will not be published. Required fields are marked *

You Might Like

Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

Enroll

How to Make Interactive Dashboards in Excel

Interactive (a.k.a. dynamic) dashboards are a great option for technical audiences that have the time and interest to explore the data for themselves. In this blog post, you’ll learn about the four pieces needed: Datasets, linked to pivot tables, linked to pivot charts, linked to slicers.

More »

Want to wow your boss with a dynamic dashboard? Or, how about a one-pager of key findings? Our 4-course bundle provides all the how-to’s.

Enroll

Subscribe

Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.