How To Create A Clustered Column Chart

How To Make A Column Chart In Excel + Clustered And Stacked Charts!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Column charts are by nature very simple. They represent a small amount of data in a clear, concise manner.

But they can be eminently useful. No matter what kind of data you have, there's a good chance that a well-crafted column chart will share that information effectively with your audience.

Remember: when it comes to charts in Excel, simpler is almost always better!

Kasper Langmann, Co-founder of Spreadsheeto

Let's take a look at everything you need to know about columns charts. We'll start at the beginning.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Free video on column charts

Watch my video and learn how to insert and edit a column chart in less than 4 minutes.

Prefer text over video? Then continue below!

Why use a column chart?

We've mentioned that column charts are great for presenting information. But what sets them apart from other choices?

Here are a few reasons you should always consider this type of chart:

1. They're easy to make.

With minimal requirements for how you format your data, you can create column charts quickly and on the fly. You don't need to go through a complicated process.

The less time you spend creating a chart, the more time you can spend perfecting it.

2. They're easy to understand.

Most people have been reading column charts since grade school. They're intuitively easy to read, especially if they're well-labeled.

Because charts exist to share information quickly, that's a big plus for column charts. It doesn't get much easier.

3. Up is intuitively connected with "more."

We tend to think of things that are better being higher on a scale, and you can use that to your advantage with a column chart.

The more information you can share using these types of implications, the better.

4. They make comparisons simple.

Because most people are so familiar with column charts, they're very comfortable making comparisons between values. Even in a bar chart, this might feel a bit strange to some people.

But column charts make it easy to compare different values. And because that's what charts are for, that's a big bonus!

Like bar charts, column charts are useful in almost every situation.

Kasper Langmann, Co-founder of Spreadsheeto

It's worth noting that there are a few cases in which other graphs will serve you better than a column graph. For example, a stacked column chart is good for showing proportion, but many people find pie charts easier to understand.

If you want to show change over time, a line graph is probably best. And while a column graph might seem like a good idea for charting project completion, a Gantt chart is likely better.

Get your FREE exercise file

If you'd like to follow along, you can use the same data we've used throughout this article.

Just download the free exercise file and create the charts along with us!

Download the FREE Exercise File

Formatting data for column charts

Getting your data into the necessary format for a column chart is easy.

All you need to do is put each category and the associated value on a single line (or in a single column).

Here's an example:

Keep in mind that column charts can easily accept data organized in rows, too.

Kasper Langmann, Co-founder of Spreadsheeto

To tell Excel what the rows represent, you can add a label (Excel will import this as an axis label when we create the chart):

You can also add more than one piece of data for clustered and stacked column charts:

How to make a column chart

Once your data has been formatted (which should only take a moment), you're a couple clicks away from a column chart.

First, highlight your data:

Then, in theCharts portion of theInsert tab, click theInsert Column or Bar Chartbutton:

From the subsequent dropdown menu, selectClustered Column Chart:

Don't worry if you don't want a clustered chart. The Clustered Bar Chart button works for both clustered and non-clustered charts.

Kasper Langmann, Co-founder of Spreadsheeto

Click that button, and your chart will appear:

Note that Excel knew what to label the vertical axis—it took this information from our label in the spreadsheet.

Kasper Langmann, Co-founder of Spreadsheeto

The same method works for clustered and stacked column charts, but we'll go over those below.

Editing column charts

When you first create a column chart, Excel uses the default values for color, font, and other design elements.

But you have a lot of power in customizing exactly how you want your column chart to look.

The easiest way to make adjustments to your chart is by usingchart styles. Click on your chart, and you'll see a number of choices in the Ribbon.

Just mouse over each option to see a preview:

Click on any one of the options to instantly change the formatting of your chart.

You can also change the color scheme by using theChange Colors menu to the left of the styles:

To change the elements displayed in your chart, use theQuick Layout menu. This includes things like error bars, value labels, data tables, and legends.

You can also add and remove individual elements from your chart for a custom look.

Click theAdd Chart Element button to bring up a menu:

Keep in mind that although the menu says "Add," you can also use it to remove chart elements.

Kasper Langmann, Co-founder of Spreadsheeto

Here, we've selectedData Table (With Legend Keys):

If you didn't highlight all of the data that you wanted in the chart, or you added data to your spreadsheet, you can change the data selection.

Click theSelect Data button:

From here, you can type in your new data source, or click-and-drag to select it with your mouse.

You can also switch the rows and columns from this window. But there's a button for that, too: clickSwitch Row/Column in the Ribbon.

To change things like borders, effects, WordArt styles, and other design elements, click theFormat tab underChart Tools in the Ribbon:

There are a lot of options in the Format tab, so we won't be covering them here. They're pretty self-explanatory, though, so you should be able to find what you need.

Kasper Langmann, Co-founder of Spreadsheeto

Finally, you can use theMove Chart button to open a new window. That window lets you move the chart to an existing sheet or create a new one for it:

Clustered and stacked column charts

In addition to the standard column chart, Excel can also provide you withclustered and stacked column charts.

Let's take a look at how to make each of these. We'll start with the clustered chart.

For this chart, you'll need to have multiple values assigned to each category, like so:

Then, do the same thing you did for the regular column chart. Highlight the data, then click2D Clustered Bar Chart.

This is an effective way to present lots of data concisely.

Kasper Langmann, Co-founder of Spreadsheeto

If your clustered column chart didn't come out quite the way you wanted it to, tryswitching the rows and columns. This can make the chart easier to read.

A stacked bar chart shows both the total of a different categories and the proportion of each constituent value. It's hard to understand when it's written out, so let's take a look at an example.

Using the same data, we'll click the2D Stacked Column Chart button:

The stacked format lets viewers seetwo dimensions of data at once.

This can be helpful when both the total of a sequence of numbers and the relative proportion are important.

When the total is less important than the relative proportion, you can use a100% stacked column chart. This sets the totals to 100% and shows the proportion of each constituent value:

Remember that you can use the design and format tools on these types of charts, too!

Kasper Langmann, Co-founder of Spreadsheeto

A concise way to view data

The column chart is one of the simplest charts that Excel has. But that doesn't mean you can't present a lot of data with it.

In fact, if you can get your data into the proper format, this is one of the most efficient ways to display information.

And because most people are very familiar with this type of chart, it's one of the easiest to read.

Kasper Langmann, Co-founder of Spreadsheeto

Don't underestimate how useful a simple column chart can be!

Kasper Langmann 2021-11-18T23:20:09+00:00

Page load link