Excel Bar Graphs

These days, everyone and their mother has “Microsoft Excel” listed as one of their skills on LinkedIn. So you probably already know that Excel is a powerful tool for data analysis, manipulation, and visualization. Working in Marketing Analytics, I literally use Excel every single day. (In fact, you might have noticed that I’ve been AWOL on the blog recently — I’ve been so busy with work that all I can even think about is Excel.) Some people are “Excel Wizards”, using tons of shortcuts and fancy formulae. If that’s you, more power to you! But in the end, it’s the output that matters: how will you communicate your work to an audience of colleagues or clients? Often you’ll present your findings in a series of charts or graphs. And when I see a professional slide deck full of ugly, unpolished, or even default-style Excel bar graphs (or any graphs), it drives me up the wall.

I want to help you.

This post serves as a quick tutorial on how to format your Excel bar graphs to make them awesome. Here’s where we’re headed in this post:

Excel Bar Graphs - Final Product

Why bar graphs, when there are so many other graphs out there? First, because they’re the most common type of graph I’ve seen in my short career so far. I’ll be focusing on stacked bar graphs in particular. If great bar graphs come in handy for me, hopefully they will for you! And second, because “How should I format my bar graph?” is one of my FAQs from colleagues at work. Now I can just send them to this link for a quick tutorial. (Hello, work friends!)

I’m also going to assume a basic knowledge of Excel, meaning I won’t necessarily provide specific paths to every command. (For the record, I’m using Excel 2013.) If you need clarification, please comment below! I’d love to help you out!

Getting Started

Let’s say you have some data.

Excel Bar Graph 1 - Data

On one axis of your graph, you’ll want to plot the time dimension. Traditionally, this is on the horizontal axis. And on the other, you’ll plot your values — in this case, number of baked goods. (Waddup Tim Hortons reference!)

If you plot this data as is, your stacked bar graph will look something like this:

Excel Bar Graphs 2 - Default with Colours

There’s one thing you may notice already: the colours aren’t Excel’s default palette! That’s because I’ve created my own custom colour palette. Plus, my standard font colour is black, rather than the default grey. I would highly recommend deciding on a single, cohesive colour palette to represent your company or team, and use it consistently for all of your work moving forward. Check out Microsoft’s tutorial on creating “themes” here.

I’ve also deleted the horizontal lines that appear as a default option. I rarely keep them, as they just add clutter to an otherwise polished slide.

You may be wondering why I would include the “Total” bar in yellow — doesn’t that make the bar double the size it should be? Yes, it does. But as you’ll see, this is one of the tricks of the trade. Make sure you include a total in your data for these kinds of Excel bar graphs!

Adding Your Labels

Next, we add data labels. Click the + sign just outside the top right-hand corner of the graph and select the data labels option. Once they’ve appeared, you can play around with their placement. For example, I’ve moved the data labels for the “Total” bar to the “Inside Base” option. I also made those labels bold for emphasis.

Excel Bar Graphs 3 - Data Labels

Right now, the labels look pretty cluttered, and that yellow “Total” bar just looks weird. But hang tight — all will be revealed.

Making the “Total”, a Total

Let’s deal with those pesky yellow bars. Right-click on any one of them, then choose “Format Data Series”.

Excel Bar Graphs 4 - Format Data Series

The panel on the right-hand side will allow you to make these yellow bars invisible.  Excel Bar Graphs 5 - No FillExcel Bar Graphs 6 - No FillWe’ll also use the series options to adjust the space between the bars. I usually set this to 50% or 75%, depending on the number of bars across the axis.
Excel Bar Graphs 7 - Gap WidthExcel Bar Graphs 8 - Gap Width

To complete the illusion of a built-in total label, we’ll manually adjust the maximum value of the y-axis:

Excel Bar Graphs 9 - Height

Looking good! We’ve got labels on all of our bars, so we don’t really need the vertical axis labels anymore. But we don’t want to delete the axis so it doesn’t mess up our formatting! Rather than deleting those unnecessary y-axis labels, we’ll simply hide them in the Format Axis panel:

Excel Bar Graphs 10 - Hide Axis Labels

Finally, we have to remove the legend entry referring to the “Total” bar. Just select it and tap Delete.

Excel Bar Graphs 11 - Legend

The Finishing Touch

There are lots of little formatting tweaks you can make at this point, so that your graph matches the aesthetic you’re seeking. For example, you could increase the font sizes, move the legend to another side, add a title, and lots more.

My final tweak will be changing the font colour of some of the labels so that they stand out against the background in my custom colour palette. I’ll also remove the border around my chart area.

Excel Bar Graphs - Final Product

And there you have it!

Feel free to adjust to your liking, but this is a great starting point for any beginner working on their first awesome Excel bar graphs. Friends and coworkers, if you’d like more tips and tricks to take this graph to the next level, let me know! I literally already have screenshots for Part 2 of this post, haha!

What are your tips for making beautiful, polished, awesome Excel bar graphs?

Course Selection Tips for First-Timers

For all of you high-schoolers out there, summer is rapidly approaching! Many of you will have already accepted offers to colleges or universities for this coming September. Congratulations! But there is still work to be done before you can head off to school in the fall. Yes, I am referring to the dreaded “course selection”. As I helped my sister work through the process of choosing her classes this past week, in preparation for her first-ever course selection appointment, I realized that a lot of the information available to incoming freshmen is either super vague or too complicated.

Know Your Appointment Time.

You should have been assigned an enrollment time. Make sure you are available to be at a computer with good Internet at that time. It’s also important to know how early or late your appointment is, relative to the rest of your school. If your appointment is late, you may find that some of your courses or class times are already full. Keep in mind that you may need backups!

Get a Piece of Paper.

You don’t want to have the extra stress of memorizing your class schedule and course names, so write it down! This is the chart I’ve used:
Course Selection — Planning Chart

I’ve got ten slots here for courses, so that means five for Fall and five for Winter. If you’re planning on completing your degree in more than four years, just add columns and delete rows.

Use Full Course Codes.

In my experience, Canadian universities use course codes that usually consist of four letters for the department, and three or four numbers to specify the course. For example, my first-year physics class was titled “PHYS 104”. If the course code has a letter following it, such as BIOL 101A and BIOL 101B, that usually means there are two sections of the course, often offered by two different professors or at two different times. Keep track of the course codes for each course. That’s the only way you’ll be able to search for and find them when you go to sign up.

The first digit of the number in a course code usually refers to the year of study the school expects you to be in when you take that course. However, if you have the pre-requisites fulfilled, it often doesn’t matter what year you’re in if you want to take a higher-level course. (More on pre-requisites later.)

Find Your Program Requirements.

You will find your program’s requirements to graduate in your faculty’s “Academic Calendar”. For example, here’s the program I completed:

 Course Selection — Program Requirements for Queen's Physics

My university used “units” to quantify courses. 3.0 units meant one semester-long course. If you take 5 classes per semester, and two semesters a year, that equals 120 units over four years, which is a full degree. My plan had 72 units (or 24 semester-long classes) specified, meaning I had 16 semester-long classes left to pick for myself.

Fill in your chart with all of the required courses in each year. Record full-year courses twice: once for each semester. How many slots do you still have open for more courses?

Pay Attention to Pre-Requisites.

One of the first things I recommend doing when considering your university career is to look for any third- or fourth-year courses that really interest you, whether they’re in your program or not. Look at the pre-requisites for those classes, and see if they fit into your plan. But do those pre-requisites have pre-requisites of their own? Work backwards to figure out what you need to do to take those courses.

If you’re not sure what courses will interest you in the coming years, that’s okay! Focus on your program’s requirements first.

Be Well-Rounded: Check Your Breadth Requirements.

Many universities have breadth requirements, which are categories of courses in which you have to take a specified number of classes to make you more well-rounded. They might require you to take one course for a full year in each of science, arts, and humanities, for example. Choose whatever interests you in those categories, or whatever will help you prepare for your future career!

Tutorial or Not Tutorial? That is the Question.

Some courses have mandatory tutorials/discussions/seminars to go along with a lecture. Each lecture may have multiple tutorials taught by the same or different Professors or Teaching Assistants (TAs). The rule of thumb I’ve discovered is that if a course has tutorials available in the course selector, they’re probably mandatory. Also, if a lecture takes up less than three hours a week, it probably has a tutorial as well. That way you’re still getting 3 hours per week of instruction. You can always check in the course description — it will tell you whether a course has a mandatory tutorial component.

Some classes may also have laboratory components. Those usually work the same way.

If the course code for the lecture has a section (e.g. BIOL 101A or BIOL 101B), make sure you sign up for a lab or tutorial in the same section (A or B).

Course Selection for Electives!

Electives are the fun part! Electives are courses taken in any department, especially those that don’t fall under the umbrella of your program. You can take extra courses in your department if you want to, though, as long as your fulfill any breadth requirements you may have.

If you’re in a specialized program, such as engineering or commerce, your schedule may be filled up with required courses in the first year or two. That’s okay — you may still have space for electives in third or fourth year.

If you’re in a more general program, you may only have a couple of required courses in first year. That means first year is the perfect time to take some electives! Just make sure you prioritize the electives that satisfy your breadth requirements.

Be Prepared.

 

When your course selection appointment arrives, you will be able to submit your schedule for the semester or the year (depending on your school). But the online portal will allow you to pre-build a schedule before your appointment. Make sure you do that!

In addition, if your electives are super popular and your appointment is relatively late, do some searching to find backup electives that will fit your schedule, so that you can swap them out at a moment’s notice.


That’s all I have for you today, future frosh! Good luck with your course selection, and comment if you have any questions!

For those of you who have gone through this process before, what course selection tips do you have for the Class of 2020?