I’m a big fan of takeaway text: for reports, for presentations, and even for interactive dashboards.
Sometimes people think that takeaway text isn’t possible for dynamic dashboards in Excel. They worry that they’ll have to write each sentence by hand. False!
In this video, you’ll learn about two ingredients:
- Concatenation: You’ll use the ampersand (&) to join the content from a few cells together.
- Helper Cells: There are pivot tables, helper tables (with lookups), and more concatenation to write out the graph labels.
You can write takeaway sentences about:
- The most or least (the poles)
- The averages
- Anything else you think your audience might be interested in
What’s Inside
- 0:00 Why we need “takeaway text” in data visualization
- 1:26 Two elements needed: Concatenation and Helper Cells
- 1:41 Concatenation in Excel – the Primary Language example
- 2:26 Concatenation – the Months in Program example
- 3:12 Concatenation – the Household Income example with rounding
- 3:46 Concatenation – the Residence map example
- 3:56 Ideas for takeaway sentences to describe graphs: Min and max, mode, etc.
- 4:36 Helper Cells (linked to pivot tables with lookups)
- 5:38 Need more info??
Your Turn
Download this Excel dashboard and follow along: https://depictdatastudio.gumroad.com/l/Demographics-Dashboard
Transcript
Ann K. Emery: [00:00:00] I’m a big fan of takeaway text, takeaway text in reports and presentations, even in dynamic dashboards like this one.
Now, a lot of times people are like, “Ann I love takeaway text too, but it’s n/a for me. I can’t do it because I have a dynamic dashboard. I can only put…” This is what they tell me. This isn’t true though.
“I can only put the topic. I can’t also put the takeaway message because then as people explore the data, I can’t like manually write all these sentences.” Okay. But false. Look at how the takeaway sentences, let me zoom in so you can see this one a little bit better. Look at how they change as they interact with the dashboard.
It’s dynamic. Look, it says 17 people. And then I’m looking at this one right here at 17 people. And then it says 14, and then it says 11. This one changes. This one changes. This one doesn’t change because it’s always English is the most common language in this fictional data set about fictional programs.
Um, [00:01:00] but if it did change, if it was suddenly Farsi or French, that’s what the sentence would say, because look, It’s concatenated, okay? It’s not done by hand. I didn’t, by hand, just type in, like, blah, blah, blah, blah, blah, blah. That would be a ton of work. It would defeat the purpose of an interactive dashboard, right?
It, like, it wouldn’t be possible to write those by hand. So, This is what this video is about. I’m going to show you behind the scenes how you need these two things. Okay. You need to concatenation and you need helper cells, concatenation and helper cells. I’m going to show you the concatenation first, and then I’ll show you the helper cells in a moment.
Concatenation is is a formula. Okay. I’m going to just going to type this one right here so you can see it. It’s a formula. So you start with equals. You say equals the helper cell you would have already set up. That’s over here. I’ll show you this in a moment. Stay tuned. Okay. Equals the helper cell and double [00:02:00] quotes sentence structure, double quotes.
Okay. Helper cell where you already have English selected and Double quotes, sentence structure, double quotes. English is the most common language at this site. Double quotes. Press enter and your sentence structure is written for you. Let me show you this one. Uh, let me move this one down, make a little bit of space.
It would be equals helper cell. Where do I have my helper cell? It would be under the, let’s see how well I labeled this. Okay, under the months in program section with the pivot tables. Here’s a helper table and I’m going to pick this helper cell. Okay. Equals helper cell and that’s concatenation. It joins things from different cells together and quotes sentence structure quotes.
What did I want this one to say? [00:03:00] 14 people, double quotes, space people have blah, blah, blah, blah, blah, blah, period, double quotes. Enter 14 people, blah, Okay. Uh, this one is really, really similar. It’s got two helper cells. It’s helper cell, the number and double quotes people at this site. Up to their open parentheses.
When you have parentheses in your sentences, it gets a little tricky too. Cause some of your formulas are going to have. Parentheses. And here’s the other help or sell with the percentage that I had to round. And then I add to multiply to a hundred and then I had to add the percentage symbol, you know, et cetera, et cetera, double quotes at the end.
What did I have in this one? Participants live in and help or sell. So you have to think about what should the sentence say? And in your real life it’s going to be [00:04:00] different. Then these obviously ideas for you, it would be things like calling out the most or the least the polls that makes interesting data stories.
It could be, um, here’s a poll. That’s the most, it could be a big slice, a little slice, something you think your audience might be interested in. Here’s another idea of showing the most like nine people are from Montana. In this site, whichever site is selected here. Okay, that’s concatenation, concatenation, right?
You’re adding helper cells and some sentence structure. It’s joined by the ampersand. The second thing you need is you need the helper cell, right? So it’s over here and I’ll show you, I don’t know, which one should I show you behind the scenes of maybe this one, the language one. So right here, I’ve got a pivot table.
Of the languages. And then I have a helper table because I needed this helper table to [00:05:00] change it from person to people do that on a couple of the charts, right on zero people, but then this one says one person. So a lot of times I’ll do cells like this, so I can have the actual number, but then the graph label right next door.
I figured out how many languages there were. I did a ma, a Simple Max formula. You’ve seen that one before, right? What is the, what is the most, what’s the category the most? And then I did a X lookup to say if it’s 21 is the most, what language does that correspond to? It’s English. Let me know which of this, these techniques you’d like more info about.
Do you wanna hear more about helper tables about. Graph labels that are dynamic about person versus people, XLOOKUP, MAXFORM, I don’t know, you tell me, you tell me and I’ll get through as many of your questions as I possibly [00:06:00] can.
Leave a Reply