Want to put both the numbers and the percentages inside your Excel graph’s labels, like this?
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.
Leave a Reply