The stacked chart wasn’t terrible.

She already formatted it, following all the usual dataviz and accessibility principles. (White outlines, brand colors, direct labels instead of legends, sequential colors for a sequential variable, only labeling the min and max on the scale, using sufficient color contrast, etc.)

The challenge was that her chart was going into a dynamic dashboard, so depending on which filters were selected, the numbers might be really big, really small, or even zero.

We needed to think forward and plan for all those different scenarios.

Challenge: A Single Stack is Lonely

Another challenge was that her chart looked lonely.

I don’t love stacked charts for a single stack.

I don’t have peer-reviewed articles to back this up; it’s a personal preference.

Stacks are extroverts. They thrive with their friends, like this:

Challenge: Stacked Charts are Tough for Tiny Slices

Remember how her chart was going into a dynamic dashboard?

So depending on which filter(s) were selected, the numbers might be really small.

Stacked charts are terrible for small slices. They’re hard to label, and even harder to see.

You’ve run into this, haven’t you? It’s a common dataviz headache.

Challenge: Stacked Charts are Tough for Zeros

Another common dataviz headache is that stacked charts are terrible for zeros.

Because they literally can’t show zeros. There’s nothing to visualize.

Sure, I’ve seen people try all sorts of workarounds, like adding text boxes off to the side that explain that a category isn’t shown because the number was a zero. But that’s messy and time-consuming.

Solution: Use Columns/Bars Instead of Stacks

I often recommend changing the stacked bar/column into a regular bar/column, like this.

(Should you use vertical columns or horizontal bars? I’m using vertical columns here because this particular graph is sequential.)

(Worried you’ll have to delete the chart and start from scratch? No way! It’s faster than that. You can change the chart type with just a few clicks.)

Can you see how regular bar/column charts work better for tiny numbers?

Can you see how regular bar/column charts work better for zeros?

And don’t worry — regular bar/column charts still work when comparing multiple categories, too. We’d simply make a small multiples layout, like this.

Your Turn

These principles can and should be applied to every software program.

As you can see, I’m using everyday software (Excel) in these examples.

Have any how-to Excel questions? Comment here and let me know. I’ll post a link to an existing tutorial, or I’ll make a brand new tutorial for you.

I’d go to the public library, grab a dog-eared book off the shelf, and flip through the grayscale images trying to match the author’s screenshots to my own computer.

They were always using a different version of Excel than me.

I couldn’t find the buttons from their blurry screenshots.

I had to zig-zag my eyes back and forth between the book and my screen.

That’s how I learned Excel. And it was terrible.

Introducing the Excel How-To’s Templates

That’s why I spent hours and days and weeks making the Excel How-To’s templates for you.

Rather than having a separate book open, you’ll learn directly from Excel.

All the instructions are typed directly into Excel for you – alongside the living, breathing graphs – so you can save precious time.

Or, if you’re not interested in learning all the how-to steps, you can use the completed templates for your own project. There are notes telling you exactly where to type your own numbers and percentages so that they’ll show up in the finalized charts. No need to reinvent the wheel.

What’s Inside: 31 Templates

There are 31 templates so far.

I make updates and additions a few times a year, and send the latest Zip folder to everyone to make sure they’ve always got the latest version.

Chart Chooser

In addition to the graphing instructions, you’ll see my 2-page Chart Chooser, which’ll help you narrow down which chart to try.

Theme Colors and Theme Fonts

You’ll also get step-by-step instructions for setting up your organization’s brand colors and brand fonts. Themes save time and help you look professional.

Available In

Inside each template, you’ll learn which version of Excel that chart’s available in.

Most charts can be made on most computers.

There are some exceptions here and there, which is why it’s explicitly spelled out for you.

Uses

You’ll learn what that chart type’s best for.

And, I’ve added notes about where that chart is most or least common. In practice, we don’t see every chart in every workplace. Some charts are pretty much only seen in peer-reviewed journal articles. Other charts are pretty much only used by accountants. And so on.

Real-Life Examples

Inside each of the templates, you’ll see links real-life examples. Then, you can see how that chart has been used in reports, slideshows, dashboards, infographics, and websites by other agencies like yours.

Before-After Editing Example

Then, you’ll get a preview of the before-after transformation that you’re about to create.

For example, in the bump chart tutorial, you’ll transform a regular ol’ line chart into a ranking chart.

Below, you’ll get ideas for how you’d adapt that busy bump chart for a presentation (by graying everything out and highlighting one category at a time).

Step 1: Set Up the Table

You’ll walk through step-by-step instructions to make and edit all the 31 charts.

Step 1 is setting up your data table that’ll feed into the chart.

For native charts – bar charts, pie charts, line charts, etc. – the table set-up is extremely straightforward.

But for non-native charts, we have to get creative.

For example, in the population pyramid tutorial, you’ll see:

• the original table, and
• the magic table.

I’ve added notes to help you figure out what goes where.

The gray area gets pulled directly into the chart, so those are the values you’ll tweak when you’re making population pyramids for your own projects.

Step 2: Insert a ___ Chart

As we’re building non-native charts, we have to fool Excel.

We often insert one chart type… and disguise it to look like a different chart.

This took me nights and weekends, over several years, to figure out on my own. Let’s get you up to speed immediately! No need to waste your time fighting with Excel on your nights and weekends.

You’ll see explicit instructions about which chart type to add.

For example, to create a waffle chart (a.k.a. square pie), you’ll need to insert a 100% stacked bar chart first.

Step 3 (and onward): Format Format Format

This is where you’ll learn about Excel’s lesser-known features.

For example, you’ll learn how to adjust the primary and secondary axes’ Series Overlap and Gap Width, which makes our bars overlap.

I don’t find these edits to be intuitive at all. Quite the opposite! That’s why I’ve added screenshots for you – so you don’t have to memorize which buttons to click.

Variations

Finally, you’ll see variations of each chart.

For example, in the dot plot tutorial, you’ll see how to set up:

• 2 sets of circles with a line connecting them
• 2 sets of circles without a line connecting them
• 1 set of circles (a lollipop)
• Smaller dots with labels above or beside the dots
• Arrows
• Arrows, sorted (my personal fav)

Get the Excel How-To’s Templates

They’re included with your Data Storytelling in Excel tuition.

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.

In this article, you’ll learn about 20 stress-free charts that we can make inside spreadsheet software like Microsoft Excel.

These visuals can be added to static or interactive dashboards.

They’re fast for us to make… which means a quick turnaround time for your audiences. No need to wait weeks or months until a dashboard is ready! These visuals are made within minutes, so your dashboard is ready same-day.

This is a preview of everything that’s included inside Dashboard Design. This hybrid course includes 9 modules of self-paced lessons along with live Office Hours.

Module 1 is all about planning, deciding what to include, and iterating.

Module 2 involves hands-on practice to create these 20 stress-free charts.

Visualizing 1 Point in Time

In the first lesson of Dashboard Design, you’ll make stress-free charts for visualizing 1 point in time inside spreadsheet software like Excel.

You’ll create:

• (1) Tallies
• (2) Circles
• (3) Filled Squares
• (4) Outlined Squares
• (5) Bars
• (6) Stacked Bars
• (7) Heat Tables

In the Dashboard Design course, you’ll download the file, practice along with me, and pause and re-watch segments as needed. 

You’ll see my Answer Key with the formulas on the left, and you’ll practice in the Your Turn section on the right.

You’ll learn which techniques are best for small n’s vs. big n’s.

​You’ll see case studies of these techniques being used in real dashboards, slides, reports and even as appendices to technical reports.

You’ll hear me talking about “Big A” Accessibility (508/ADA guidelines) as we go, ensuring that your stress-free charts match your existing branding while also being colorblind-friendly and grayscale-printing friendly.

This is a 26-minute lesson; there are timestamps below the video so you can easily rewind or fast-forward. The timestamps making re-watching segments easier for Future You. 

Every video has captions that can be turned on/off. Every video has a full transcript​, too (so you can skim the transcript and see which lessons will be most helpful to you). Dashboard Design includes ~20 case studies in Modules 8 and 9, and nobody needs to watch them all. You can skip around and see which case studies from fellow participants are most applicable to your own industry and workplace.​

Visualizing Exactly 2 Points in Time

In the next lesson of Dashboard Design, you’ll create stress-free charts for visualizing exactly 2 points in time.

These are the charts you’ll need for before/after, pre/post, baseline/endline, etc. dashboards.

You’ll create:

• (8) Slope charts
• (9) Column charts
• (10) Win/loss columns
• (11) Deviation bars
• (12) Checkboxes
• (13) Sort-of checkboxes with squares
• (14) Sort-of checkboxes with circles

3+ Points in Time

Do you collect and share data every week, month, quarter, or year?

These are the stress-free charts that can showcase your time series patterns.

​Inside Dashboard Design, you’ll create:

• (15) Trendlines
• (16) Column charts
• (17) Win/loss columns

Progress Towards a Goal

​Then, you’ll create:

• (18) Checkboxes
• (19) Deviation bars
• (20) Spillover bars

Putting It All Together

​In the final lesson of this module of Dashboard Design, you’ll practice applying all the skills you just learned.

You’ll take this boring, black and white table…

…and transform it into a professional, skimmable dashboard. 

This is the first of many before/after transformations you’ll make alongside me.

Later in the Dashboard Design course, in Module 3, you’ll practice making more of these static dashboards from start to finish. Static dashboards are great for busy, non-technical audiences who just need a 1-pager of key findings. 

In Modules 4, 5, 6, and 7, you’ll make interactive dashboards in Excel.​

Your Turn

Comment here: Which of these stress-free charts are you already familiar with? Which ones are new?

Text boxes are clunky and time-consuming.

Instead, we’ll create Helper Cells with concatenated values.

What’s Inside

• 0:00 Intro
• 0:33 Dataviz On The Go
• 0:42 Why not text boxes??
• 1:42 Pie example
• 5:42 Donut example
• 7:42 Bar example
• 12:28 Advanced: Nested IF with left, mid, right
• 14:42 Your turn

Materials

Download the Excel file: https://depictdatastudio.ck.page/numbersandpercentagesingraphlabels

Transcript

Ann K. Emery: [00:00:00] How do you put numbers and percentages inside your graph labels? In this video, you’re going to learn how to do this.
We’re going to go through two different workarounds, three different chart types, so you’ll know how to do this for pies, for donuts, for bar charts.
And you can also download the Excel file that I’m using. Just look below the video, you’ll see a link to download it for free. Obviously, you’re going to learn more if you follow along with me and you do it yourself, that’s going to be way better than just watching me do it, which would be really boring too.
I’m Ann Emery. You’re watching Dataviz On The Go, the series where you learn dataviz time savers inside everyday software like Excel.
And speaking of being on the go, I was in South Africa just a few days ago teaching dataviz and one of the participants there raised his hand and he was like, Ann, How do I add the numbers and the percentages inside my chart labels? And I walked over to his laptop and he showed me a [00:01:00] column chart that he had.
It had about 10 different columns going across and he had numbers and percentages and I was like, you already did it. What do I need to help you with? And he was like, well, but then it was text boxes manually put on each of those 10 columns, which must’ve taken him like 10 minutes to do, right? That’s way too much time.
Um, he was explaining how. When he updated the data each month, the text box location didn’t change with the height of the column. So he had to manually adjust the placement and that’s way too much work. So I’m not going to show you how to do this with text boxes. I’m going to show you the better way behind the scenes.
It involves a little bit of a learning curve, but I guarantee you in the long run, it’s going to be way faster. All right. Open up your Excel file if you haven’t already, and let’s go through each of these chart types together. So what you’re going to do along with me is you’re going to highlight the gray section of the table.
It’s a little counterintuitive. You might be tempted to highlight the numbers and the percents. [00:02:00] We’re just going to do the numbers. You’re going to go to Insert, and we’re going to add a 2D pie chart. Okay. These are the default settings that Microsoft gives us, and we’re going to make it look like this one over here.
If you have questions about big A accessibility or little A accessibility, just comment below the video and I will certainly point you in the right direction. I have hundreds of blog posts and YouTube videos and conference presentations all about accessibility in dataviz. This video is just about the labels, so I’m going to try to hold back from talking too much about the formatting and just show you the number percentage combo here.
Okay, so how you do this is you click on any one of the pie slices here, do a right click, add data labels. Then you click on the labels, any one of them, right click, [00:03:00] format data labels. Labels are kind of annoying because you have to add them and then you have to format them. It’s not my choice. That’s just how it is.
It feels like a little more work though, doesn’t it? Right? Add format. When you click format, it’s going to pull up the sidebar and you’re just going to click the percentage box. That’s basically it. The value, a number in this case, and the percentage. There are a few more formatting things that you’re probably going to want to do while we’re in here.
For example, you can add the category name. Okay. So it looks like We’re not going to do the iZigZag, which is also not colorblind friendly or grayscale friendly. We don’t do legends in dataviz. I’m going to delete this built in chart title for now. Okay, it’s getting a little closer to the model right here.
And let me go back to that format menu. We did category, we did value, we did percentage. I’m going to uncheck leader lines. Those [00:04:00] are little gray connecting lines that show up sometimes if you manually drag the label too far away, they just add clutter. I’m going to delete those. The separator. Right now, it’s category, comma, number, comma, percentage.
I’m going to do new line. And the label position, I’m going to put outside end. You might have to manually drag things just a little bit, like maybe you’d have to shrink down your pie chart. Here I am showing you all the edits when I promised myself I wouldn’t, but I didn’t want this one to be cut off, right?
Okay, and then the final thing you might want to do is maybe you make the word Colored and bold. So how you do that, I’ll zoom in to make sure you can see exactly where I’m clicking, cause it’s really small, right? When you click on the label, it looks like nothing happened. You kind of have to like click a couple of times and then depending on where your mouse is, a different section is going to be dark gray.
Can you see that? It’s just, it’s such a slight difference, like a medium gray [00:05:00] versus a light gray. Do you see that? You like, right? The one that’s a little bit darker. So the one that’s a little bit darker, the category label, you can, you have to like, Oh, it’s so hard to click on the right spot. You have to highlight it, highlight the word you have, and then you can make just the word colored.
To match the slice it corresponds to, right? To remove guesswork. And colored font should be bold to make sure it passes accessibility standards. You go through it again, right? Double click on just that first one. Highlight it. That one’s gonna be that second theme color. And bold, etc, etc. Same thing on donut charts.
Okay. Scroll down on your spreadsheet. Let’s practice this again so that it feels more fluent for you. You highlight just the number, you go to insert, you pick out your chart type. Let’s do a 2D donut. If you highlight percentage, if you’re like, duh, [00:06:00] I’ll just do it this way. I’m just going to do it better or something or different.
Um, it’s not going to work. It’s going to show you this nested donut where it has the numbers and then the percentages. And that’s weird. Why would you want your chart to look like that? Okay. We don’t want that. We want just a regular old donut chart. That’s how you highlight just the numbers. You’re going to add the labels and then format them.
So you click on the slices or wedges of the donut, right click, add, click on the labels, right click, format, very similar menu as before. Click category. You’ve got value already. Click percentage, uncheck leader lines, change the separator. We’ll do new line and then Can you place them outside? No, you can’t.
Isn’t that annoying? So you have to manually Right. There’s not the position or location section down here. I just manually drag them, you know, just like a little bit [00:07:00] kind of over here. And then sometimes I get frustrated that this takes a while, but it takes about this long. Delete this part. You’d make it colored, probably delete this, et cetera.
Right. There’s a little bit more formatting to do. You might have to like, I just can’t help but format this for you. Anyway, that’s why I have the completed version. So you can see up to Ann Emery standards, what it should look like. So, pies and donuts. Pretty easy, right? You add the labels, you format them, and you look for the little square checkbox that says percentage.
Pretty darn easy, behind the scenes. Bar charts. They’re a little bit different. We need the second workaround for these. So, for bar charts, we are going to highlight the gray area Just like before, go to insert. This should look familiar, right? Let’s add a 2D bar chart like this, but we want it to look like the number and the [00:08:00] percent.
We can try, we can try the same thing. It’s not going to work, right? Click on the bars, right click, add data labels. Click on the labels. Right click. Format. Where’s the percentage checkbox? It’s not there. That percentage checkbox is only there for pies and donuts. It’s not there for bar charts and a bunch of other chart types.
So we have to use that second workaround. And the workaround is gonna be Helper cells.
We are going to type in exactly what we want here and then whatever is sitting in these cells is going to feed in here. Okay.
Just for kicks, I’ll show you what, uh, what these do. Click on your labels, right click, format. Pulls up the window. Value from cells. This is one of my [00:09:00] favorite boxes. Click that. Ignore this little pop up for a second. All you do is you highlight your helper cells, you click okay, and it shows you the helper cells, comma, the value.
So here is hi at the top of the table, that is hi down there, right? Here is I’m and there’s I’m down there and the value. But you wouldn’t keep this, you would do a little more formatting of course, like you would just remove the value and the leader lines. You can type in whatever there. And it feeds into the label.
Isn’t that nice? So if you want number percent, should I show you the long way? Uh, I’ll show you the long way. And then the short way you could manually, this is a long way. You could manually type in. Uh, this, and then it shows up right there. Whatever’s in the helper cell [00:10:00] goes over there. Now, am I going to tell you to manually type stuff in?
Absolutely not. Absolutely not. You’re going to do a concatenation. You’re going to say equals this and. This ampersand, it is really smushed. It’s the number smush the percent altogether. It’s two, two, four, two, and then it’s 0. 384. It’s all these digits from this division, right? From the numerator and denominator living here behind the scenes.
So you go back in and edit this. If you’ve seen concatenation before, you know how to do this, right? If you’re new to concatenation. Welcome. Hopefully this opens up a whole new world for you of formatting control that you have for your dataviz behind the scenes. Formatting for this would be like, um, what would we have to do?
The number and, uh, quotes. Open parentheses, whoops, quotes, space, open [00:11:00] parentheses, quotes, and the B61 and C61, those are specific locations. Those do not get quotes, but everything else does get quotes. So if I want space, open parentheses, it has to have double quotes around it. And then everything’s just separated by the ampersand, okay?
Number, space, parentheses, percent. It’s not going to be perfect, but we’re getting closer. Number space, open parentheses percent. And you just go back through and you edit it again and again. So let’s add this, uh, let’s do a times a hundred and then we’re going to round this to a decimal place and we’re going to add what else?
Percentage symbol, closing parentheses. Okay, we’re getting closer and closer. Because I do this for a living and I’m very, very picky about my graph labels, I would also go through and do [00:12:00] one more layer of editing where I add the comma, the thousands separator comma, and I would do that using a nested if and left mid and right.
But, That really deserves another video. If you want though, you can look in this answer key and the formula that you’re going to see is going to be much longer because I have nested if and left mid right. Okay. What if, speaking of if, uh, another variation would be what if you have like a Big digits, right?
Like these are little digits, three and four digit numbers. If you have big, big, big digits, like currency, I work on a lot of grant making projects with foundations. They have things like project amounts and they’re often in the millions or tens of millions, sometimes. Same idea where you’d add some helper cells.
This is actually, here’s a really long, there’s, this [00:13:00] nested, if left, mid, right. It gets a lot longer. And the same idea, you could just highlight, insert your bar chart. Does this look familiar now? That’s why I want to show it to you twice. Add the labels, right click, whoops, got to click on them, right click, format, value from cells, select the helper cells you would have made.
Okay, uncheck, uncheck value, uncheck leader lines, and then you do a little bit more formatting so it would look something like this. One downside to using concatenated helper cells is the labels are all the same format. So you can’t make just the number bold and purple or just the percentage. It’s all or nothing.
Um, so I, I don’t love that, but you know, it’s like that, of course there are variations of this, right? Like if you have like really, really, really big numbers, things [00:14:00] like in the millions, you could also write longer formulas, if statements with left, mid and right, so that something like 123 just says 123 K or millions might say 1.
27 M for million. And then your finished version could look, you know, something like this, right? It’s really, really hard to do that, uh, with text boxes. It’s really clunky. You might make typos. You have to manually adjust it all the time. And you can’t just right click and check, check the percentage box.
So I hope that the concatenation, even though there’s a little bit of a learning curve, I think it’s going to save you a lot of time in the end. All right, it’s your turn. Let me know what types of questions you run into. Let me know if you apply it and how this works in your data set.

Colors direct our eye movements, and therefore our brains and attention.

It’s up to you: will you help or hinder your reader’s understanding?

Step 1: Start with Your Brand Colors

Otherwise, your graphs, slides, and dashboards will be Frankensteined.

I’ve written about brand colors and brand presents in other posts.

Some of those resources include:

• Examples of organizations’ brand colors used in graphs
• How to read color codes in style guides
• How to enter your custom color codes in Excel

Step 2: Do Your Accessibility Testing

I’ve written about colorblindness, color contrast, grayscale printing in other posts.

Some of those resources include:

• An official color contrast test
• An official colorblindness and grayscale printing test

Then, your accessibility testing “results” should go inside your organization’s Dataviz Style Guide.

Step 3: Apply Those Brand Colors According to the Data & Variables

Now, it’s time to apply those branding colors to ensure that your graph is intuitive.

Look at your graph: Is your variable binary, sequential, diverging, or categorical?

Or, do you want to tell a story with a dark-light contrast?

Binary Variables Get Binary Color Schemes

Binary variables include yes/no data, such as:

• yes/no survey questions
• people who speak Portuguese as their primary language vs. people who don’t
• people who own a home vs. people who don’t
• people who graduated from program on time vs. people who didn’t
• people diagnosed with an illness vs. people who don’t have it

For binary variables, choose one brand color. The “presence” of the attribute gets the darker color, and the “absence” of the attribute gets the lighter color.

Here’s an example:

Sequential Variables Get Sequential Color Schemes

a.k.a. ordinal

Sequential variables have a natural order.

Examples include:

• age ranges (5-9 year olds, 10-14 year olds, and 15-19 year olds)
• income levels
• highest educational level completed (some high school, high school diploma, some college, etc.)
• years (Year 1, Year 2, and Year 3 of a project)
• semesters (fall, spring, fall, spring…)
• cohorts (first cohort of participants, second cohort, etc.)

For sequential variables, choose one brand color, and use a light-dark gradation of that color.

Here’s an example:

Categorical Variables Get Categorical Color Schemes

a.k.a. nominal

Categorical variables include:

• race/ethnicity (African American, Asian, Hispanic/Latin@, White, etc.)
• gender (male, female, nonbinary, genderfluid, etc.)
• chapters of a report
• sections of a presentation
• categories of a dashboard

For categorical variables, use a different brand color for each category.

Here’s an example:

Diverging Variables Get Diverging Color Schemes

Diverging variables are opposites.

Examples include:

• agree/disagree scales on surveys
• changes over time (e.g., “50 percent decrease” or “70 percent increase”)

For diverging variables, choose two brand colors, and place the darkest shades on the poles.

Here’s an example:

Combining these Techniques

In most real-life projects, we need to combine these color techniques.

In this map makeover, for example, we needed to:

• use brand colors, not software defaults;
• use two brand colors, one for each category; and
• apply a dark-light gradation to each map, because these are ordinal variables.

In this population pyramid makeover, we needed to:

• use two brand colors, one for each timeframe, and
• apply a dark-light storytelling emphasis to each pyramid.

Your Turn

What types of color questions do you have? Comment below..