• Skip to primary navigation
  • Skip to main content

EvalCentral Blog

Archiving & Amplifying Evaluation

  • Twitter (@evalcentral)
  • Show Search
Hide Search

How to Make a Not-So-Scary Starter Dashboard in Excel

This is an Eval Central archive copy, find the original at depictdatastudio.com.

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.

October 4, 2022 by Ann K. Emery

Filed Under: depictdatastudio

Eval Central Logo

Footer

Eval Central developed and managed by Chris Lysy (@clysy) of Freshspectrum.com ยท Copyright © 2025