🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Building Column Charts

Quiz me Quiz Compact player layout Large player layout
Focus video player for keyboard shortcuts
Auto
  • HD
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Microsoft Excel 365 - Basic & Advanced course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

Facebook Twitter LinkedIn WhatsApp Email

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Learn how to create an effective column chart by reducing ink and 'noise' that distract from the main messages.

Exercise files

Download this lesson’s related exercise files.

Building Column Charts
28.6 KB
Building Column Charts - Completed
33.1 KB

Quick reference

Building Column Charts

How to create an effective column chart by reducing ink and noise that distract from the story.

When to use

Use to remove distracting elements from your data whenever you are charting.  Doing this will help you convey your message to your readers as quickly as possible.

Instructions

Charting Goals

  • Every chart should tell a story, quickly and effectively
  • Extra chart elements create noise and get in the way of the story
  • It is a best practice to remove as much excess ink (noise) as you can

Creating a Column chart

  • Select cells A5:B9
  • Go to Insert --> Charts --> Recommended Charts and select the Clustered Column chart

Adding context

  • Right-click the data series (columns) and choose Add Data Labels
  • Select the data labels, right click and choose Format Data Labels
  • Set the Label Position to Inside Base
  • Change the font (Home tab) to bold and white
  • Select the chart title, press the equals key, click on A3, and press Enter
  • Right-click the columns, choose Format Data Series, and set the Gap Width to 60%

Reducing Ink

  • Select the horizontal gridlines and press Delete
  • Select the vertical axis and press Delete
Login to download
  • 00:04 Let's get started with charting in Excel.
  • 00:06 But before we, do I just want to call out something that's really important to me is
  • 00:11 that every chart you build should tell a story, to convey information quickly and
  • 00:16 effectively.
  • 00:17 In order to do this,
  • 00:18 what I do is I customize every chart to get rid of additional elements and really,
  • 00:22 really dial in on the message that I'm trying to send.
  • 00:25 So we're going to start with that right now.
  • 00:26 I'm going to grab some data here for some animal sales, we're going to go up to our
  • 00:31 Insert menu, and at this point, I know which chart I want.
  • 00:35 It's this one right here 2d column, but what if you don't?
  • 00:38 Well, that's what the Recommended Charts button is all about.
  • 00:41 Microsoft provides you with some different recommendations based on your data of what
  • 00:45 it thinks might be appropriate.
  • 00:47 And you'll notice that the clustered column chart is the top chart here.
  • 00:50 So I'm going to go and say, okay, now, it's not a bad job, it looks okay.
  • 00:56 But there's a little bit of a problem with the title, it gives me count,
  • 00:59 because that's the name of the column.
  • 01:01 Well, that's great, but what I really want is the number of animals sold.
  • 01:05 Now while the data's all linked to the chart,
  • 01:08 I also want to make sure that my title is linked to the chart.
  • 01:10 So I'm going to click outside my chart just to be safe,
  • 01:14 click on the title, and say equals.
  • 01:16 If you see = show up in here we have a problem,
  • 01:19 if you see = show up in the formula bar, we're all good, okay?
  • 01:23 So, if you do see = in here, press Escape, try again.
  • 01:27 Now, I'm going to select the cell, you see it says equals example a5,
  • 01:32 when I hit Enter, this chart title is now linked into the chart.
  • 01:36 Now the next thing that I'm going to do is I'm going to take a look at these
  • 01:39 bars right here, and I want to add some precision labels to them.
  • 01:42 I don't believe you should ever release a column chart without data labels on them.
  • 01:46 So we're going to right click, and we're going to choose to Add Data Labels, and
  • 01:51 that puts a nice precision across the top.
  • 01:53 Now let's be honest, your brain is really good at picking out that beef is
  • 01:57 the biggest because the line is the tallest, our brains are awesome at that.
  • 02:00 But the second question from your accounting friends is always
  • 02:03 going to be yeah, but by how much?
  • 02:05 And this gives you the exact precise numbers without having to guess and
  • 02:09 infer based on these lines.
  • 02:11 Speaking of these lines, I don't think I really need this anymore with this kind of
  • 02:15 precision, so I'm going to go and
  • 02:17 select the vertical axis and press the Delete key and make it go away.
  • 02:21 And now with that gone, I don't really need these lines, so
  • 02:25 I'm going to select one of those and click Delete to make that go away as well.
  • 02:30 Let's fool around with a little bit of formatting on the data labels here,
  • 02:33 I'd actually prefer to see these look a little different.
  • 02:35 So I'm going to go right click on them, and
  • 02:38 I'm going to choose Format Data Labels.
  • 02:40 I'm going to choose to put this inside and which actually brings them inside
  • 02:45 the bars, and then I'm going to go to my home tab, and I'm going to choose to make
  • 02:50 them bold and set the font to white so that I can see them.
  • 02:53 And there we go, we've now got our labels inside the bars and
  • 02:57 that looks pretty nice.
  • 02:58 Now the next thing that I want to do is I want to actually make these bars a little
  • 03:02 bit wider.
  • 03:02 Now I'm going to miss this for
  • 03:04 a second, because I want you to know how to call this up.
  • 03:07 We can select our series, right click and choose to Format Data Series.
  • 03:13 And that will actually pop up a pane where we can change things like colors and
  • 03:17 fills, but we also get the ability to play with this thing called Gap width.
  • 03:21 Series Overlap for reference is only relevant if you have multiple series and
  • 03:25 we only have the one type series, so
  • 03:27 that's not going to make a difference to us.
  • 03:29 But our Gap Width, check this out.
  • 03:31 If we go and drag this really, really big and let go, those bars get narrow.
  • 03:36 But if we go the other way, let's take it down to about 50% ish,
  • 03:39 how about somewhere I can't quite get that precision 54, but
  • 03:43 of course I can always change it, so we'll make it 50, there we are.
  • 03:46 We now get nice wide bars that are very obvious to see.
  • 03:50 So that's a pretty good looking chart that's going to be nice and effective.
  • 03:54 It's all linked to the data, so the data gets updated,
  • 03:58 everything should update in the chart, I'm happy with that.

Lesson notes are only available for subscribers.

Conditional Formats with Custom Rules
05m:12s
Building Bar Charts
03m:59s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement