All You Need to Know About

How To Make a Column Chart in Excel

Whether it be creating a column chart or clustered column chart in Excel, learn all about it here!

Here are the top things on what you can do with Column Charts in Excel:

Excel Charts helps you visualize your data to quickly assess information. If you wish to compare a part-to-whole data over time or over different categories, then the Stacked Column Chart is the ultimate tool for you!

Stacked Column Chart

In Stacked Column Chart, data series of various categories are stacked one upon another in vertical columns. It is used to compare the contribution of a value to a Total. These charts are useful when you want to:

– Compare part to whole data over time

– Compare part to whole data over category

Let’s look at an example to understand how data is displayed in a Stacked Column Chart Excel.

Excel Data Table

This data table represents the sales amount for the North & South region from the year 2010 to 2014. Using this data, you can create a Stacked Column Chart to compare the sales amount for both the regions over the time period.

Stacked column chart

Years used of the analysis are plotted along the X-Axis and the regional sales amount are series and each region’s sales amount are shown on the Y-Axis.

To learn how to create this Chart, go through our blog on Stacked Column Chart.

This representation of data not only helps you to understand the growth of individual segments and also the comparison between the two. You can spot the sales amount for both the North Region (Blue Bar) & South Region (Red Bar) and also see how the sales amounts increase/decrease over time.

In Excel Stacked Bar Chart, data series of various categories are stacked one upon another in horizontal columns.

100% Stacked Column Chart

The 100% Stacked Column chart is an extension of the Stacked Column chart in that it compares the percentage that each value contributes to a Total.

This 100% Stacked column chart is different from the Stacked Column Chart only in terms of representation of the column bars:

  • In a Stacked Column chart, the height of each bar is the total value of a category
  • In 100% Stacked Column Chart, the height of each bar is the same (100%) and the segments are shown as a percentage of the total value.

So the value of each bar in 100% Stacked Column Chart Excel will always be 100 irrespective of the total value. The vertical axis of this chart contains percentage figures and not absolute value!

For example, if you want to show the percentage of contributions for the North & South sales over the last 5 years then this is the chart for you.

Make sure to download the exercise workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select the table on where we want to create the chart.  

Excel Stacked Column chart

STEP 2: Go to Insert > Column > 100% Stacked Column

Excel Stacked Column chart

Your chart is now ready:

100% stacked column chart

As you can see, it is easy to compare the sales amount for each region with the Excel 100% Stacked Column Chart.

Even though the Y-axis is plotted as a percentage. If you wish to view the sales amount of any region at any year, you can simply hover upon the column and watching the tooltip.

For example, you want to know the sales amount for the North Region in the year 2012. You can hover upon that column and see that the sales amount is $50,492.

100% Stacked Column Chart


Convert Year in Data Table to Text

One thing you need to make sure when creating a chart using this data table is that the first column containing years should be converted to text. If this is not done, then by default Excel will treat them as numerical values and will be included in the calculation of Northern and Southern sales.

To convert a number to text, simply add an apostrophe in front of the number.convert number to text

Thermometer Excel Chart

Thermometer Charts are one of the many Charts you can create in Excel.  It does not really exist in Excel, however we can get creative and create our own.

A Thermometer Chart gives you a good view of the percentage of progress made on your project, budget etc.

We are going to use a Column Chart and customize it to make it look like a thermometer.

In this example I show you how easy it is to insert a Thermometer Chart using Excel.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Given your data, ensure that you have the Percentage computed.

Thermometer Chart 01

We have the Percentage computed as the Total divided by the Goal Amount.

Thermometer Chart 02

STEP 2: Select the Percentage value, and select Insert > Column Charts > 2-D Clustered Column

Thermometer Chart 04

STEP 3: Right-click on the Header and Click Delete.

Thermometer Chart 05

STEP 4: Right-click on the bottom column name  and Click Delete

Thermometer Chart 06

STEP 5: Right click on the Column itself, and Select Format Data Series.

Thermometer Chart 07

Set the Gap Width to 0%. This will ensure the column fills our entire chart.

Thermometer Chart 08

STEP 6: Right click on the Column percentages, and Select Format Axis.

Thermometer Chart 09

Set the Maximum to 1. This will ensure our range is from 0% to 100%.

Thermometer Chart 10

STEP 7: Resize the chart to a smaller size. You now have your Thermometer Chart ready.

Update your data values and see the chart go up and down!

Thermometer Chart 11

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]