Want to watch me work?

Here’s a collection of livestreams:

Which one’s your favorite?

What’s Inside

• 0:00 Welcome
• 0:30 1. The before version
• 1:25 2. Table
• 1:36 3. Heat table
• 1:42 4. Donuts
• 2:05 5. Donuts with top icons
• 2:43 6. Donuts with side icons
• 2:58 7. Icon array 
• 3:05 8. Icon array with gray
• 3:20 9. Stacked bars in portrait
• 3:35 10. Stacked bars in landscape
• 3:45 11. Dots with dumbbells
• 4:02 12. Dots with arrows. 
• 4:06 Vote!
• 4:18 How-to tips (Spreadsheet to download; 2 YouTube lives)

Download the Excel File

It’s here: Download

Sure, Excel can handle bar charts and line charts. But it can also make population pyramids, dot plots, and maps!

In this video, you’ll see more than a dozen different charts that are possible inside Microsoft Excel.

What’s Inside

• 0:00 Intro
• 0:17 Dataviz On The Go
• 0:36 3 Levels of Excel Skills
• 1:05 Level 1: Overused Native Charts (Bars, Lines, Pies, etc.)
• 2:35 Level 2: Underused Native Charts (Combo, Tree, Sunburst, Maps, etc.)
• 4:01 Level 3: Non-Native Charts (Population Pyramids, Dumbbell Dots, Lollipops, etc.)
• 5:02 Disguises Needed for Non-Native Charts
• 5:54 Interactive Dashboards in Excel (Excel Tables, Pivot Tables, Pivot Charts, and Slicers)
• 6:16 Static Dashboards in Excel (Made in Excel, but Saved/Shared as PDFs)
• 6:41 Your Turn
• 6:55 A Personal Note

3 Levels of Excel Vizardry

I’ve taught data visualization in Excel a dozen different ways over the years.

Nowadays, I teach Excel dataviz based on the degree of behind-the-scenes hacking needed to produce that chart.

We start easy. Then, we work up to harder battles.

Here are the three Levels of Excel Vizardry:

• Level 1: Overused Native Charts
• Level 2: Underused Native Charts
• Level 3: Non-Native

Let’s go through some of the Excel secrets in more detail.

Level 1: Overused Native Charts

These are the familiar faces:

• Pies
• Donuts
• Bars and columns
• Clustered bars and clustered columns
• Stacked bars and stacked columns
• Line graphs

What are Native Charts?

“Native” charts mean they’re available from our menu with just a few clicks:

What’s Wrong with Overused Charts?

There’s nothing wrong with a bar chart here or there… but any chart gets boring when we show it over and over and over and over and over and over.

There’s also the issue of analytical depth — or lack of depth.. If we’re only using bar charts… then we’re only showing totals and averages. There are dozens more statistical approaches!

Beware! Formatting Needed

Stacked bar charts, for example.

They’re easy to make.

But we still have to:

• enlarge the font;
• darken the font (to pass official Accessibility rules for color contrast);
• directly label the data (so viewers aren’t relying on the colored legend alone — another Accessibility rule);
• outline the touching shapes in white (which helps with colorblindness and grayscale printing);
• show fewer increments in the scale (so it’s not so busy);
• decide whether to apply a dark-light contrast — or not (learn about data storytelling here); and
• adjust the gap width (if you want) to nudge the bars closer together.

Level 2: Underused Native Charts

This is where it starts getting fun!!

Excel can make:

• Combo charts (e.g., a column chart with a target line, as shown below)
• Overlapping Bars
• Area charts (where you shade the area underneath the line for better oomph and high color contrast)
• Slopes (a line chart with exactly 2 points in time, like pre and post)
• Small Multiples Lines (to combat the spaghetti line graph)
• Bumps (for rankings)
• Scatter plots (x and y)
• Bubble charts (x, y, and z)
• Tree maps (for nested categories)
• Heat Maps
• Sunbursts (nesting)
• Box and Whisker (to go beyond averages and show the min, quartile 1, median, quartile 3, and max)
• Waterfall (how pieces add to a net number)
• Radar (to compare several ordinal categories at once)
• Icons & Symbols (to make our graphs easier to navigate — and more memorable!)

Yes, These are Native Charts

Well… if you’re using the latest version of Excel.

If you’re on outdated software, (most of) these charts are still possible. They just get harder to make, i.e., they’re in Level 4 territory.

Yes, Underused Native Charts Add Variety (and Analytical Depth)

We’re not just adding variety for variety’s sake.

(Although common sense — and hundreds of consulting projects — has shown me that dataviz novelty is one of the best ways to increase engagement.)

Most importantly, we’re adding analytical depth. For example, a regular ol’ bar chart just compares the average or total of several categories. What if we compare them by location, too? Now we’ve got a heat map! We can spot geographical patterns, which would’ve been impossible in a bar chart.

Beware! Formatting Needed

Scatter plots are easy to make.

But we still have to:

• enlarge the font;
• darken the font (to pass official Accessibility rules for color contrast);
• add a key (that each dot represents one student);
• label the scales (with everyday language, like More skills gains, because scatter plots are notoriously difficult to read for people who don’t stare at graphs all the time); and
• decide whether to add a dark-light contrast.

Level 3: Non-Native Charts

Are you already using a variety of charts? Have you actually analyzed your data (beyond averages, and beyond totals)? Can you adjust the gap width, annotate the data, and apply colors strategically in your sleep?

Then you’re ready for Level 3!

With behind-the-scenes elbow grease, you can make:

• Stream Graphs
• Waffles
• B’Arcs
• Small Multiples Bars
• Population Pyramids
• Diverging Stacked Bars
• Lollipops
• Dots
• Swarm Plots
• Tile Grid Maps
• Sankey Diagrams

What are Non-Native Charts?

You won’t find any buttons that automatically make these charts.

Instead, we have to insert one chart type… and disguise it as something else.

For example, we have to insert a stacked bar chart… and disguise it as a waffle chart. You’ll need a Magic Table behind the scenes, too.

A stacked bar chart gets disguised as a population pyramid. Yes, you’ll need a Magic Table with placeholder values.

A scatter plot gets disguised as a dot plot, and so on. Each value gets assigned a x-y placeholder location inside the Magic Table.

Do these maneuvers turn your brain inside out and upside down? You’re not alone.

Learn More

If you’re consistently making, editing, and applying graphs from Level 3, you’re already a vizard. Get in touch so I can send work your way!

If you’re in Level 1 or 2, you’ll love Data Storytelling in Excel. You’ll go slow and steady so you don’t feel overwhelmed. You’ll dip your toe in… and then you’ll be swimming in the deep end in no time.

I recently saw two (!) of these tables within the same week:

In this tutorial, you’ll see a few ideas for transforming tables with check marks.

These tiny tweaks can be accomplished in everyday software like Excel.

What’s Inside

• 0:00 Intro
• 0:18 The “before” version
• 1:05 Decluttered
• 1:38 Left-aligned text for speed-reading
• 1:57 X’s into checks with built-in checkmarks
• 3:05 Filled vs. empty squares (with Webdings g and c)
• 4:48 Dark vs. light circles (with Webdings n)
• 5:47 Showing totals with tallies at the bottom
• 6:10 Showing totals with tallies at the right with countif, concatenation, and rept
• 6:48 Bars or stacked bars
• 6:59 The before-after transformation

Related Resources

How to Add Checkboxes to Excel

Download the Spreadsheet

It’s here.

I ignored my inbox and turned a boring table into a 2-pager.

What’s Inside

• 0:00 The 2-pager about NIH grant terminations
• 2:20 Lookup table with Stateface icons
• 2:38 Original table from the policy brief
• 2:47 The dataset
• 2:58 Idea 1: A better table that’s Accessible and accessible
• 3:22 Idea 2: A static graph or map
• 3:40 Idea 3: An interactive visualization
• 3:55 Idea 4: A 2-page infographic
• 4:06 The call-out boxes with icons at the top
• 4:12 The finished map
• 4:30 The finished table
• 4:42 Color-coding by category
• 4:51 Big-picture first, then more detailed, then more detailed
• 5:15 The finished product again
• 5:29 More ideas for the original policy brief
• 6:04 Let’s connect

Download the Spreadsheet

It’s here.