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 for Windows. Got a different version? No problem, you can still follow the exact same steps.

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

Download exercise file
Download 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:

column-chart-data

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):

column-row-labeled

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

clustered-column-data

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:

highlighted-column-chart-data

Then, in the Charts portion of the Insert tab, click the Insert Column or Bar Chart button:

bar-chart-button

From the subsequent dropdown menu, select Clustered Column Chart:

clustered-column-button

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:

column-chart

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 using chart 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:

column-styles-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 the Change Colors menu to the left of the styles:

change-colors-menu

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

quick-layout-menu

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

Click the Add Chart Element button to bring up a menu:

column-chart-elements

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 selected Data Table (With Legend Keys):

column-chart-table

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 the Select Data button:

select-data-button

You’ll see a new window:

select-data-source

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: click Switch Row/Column in the Ribbon.

switch-column-row

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

format-chart-tools

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 the Move 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:

move-chart-button-window

Clustered and stacked column charts

In addition to the standard column chart, Excel can also provide you with clustered 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:

clustered-column-data

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

clustered-column-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, try switching 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 the 2D Stacked Column Chart button:

stacked-column-chart

The stacked format lets viewers see two 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 a 100% stacked column chart. This sets the totals to 100% and shows the proportion of each constituent value:

100-stacked-column-chart

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!

2019-10-15T13:40:24+00:00