This post may contain affiliate links. Please read my disclosure for more info:

Variable width column charts and histograms in Excel

Variable width column charts and histograms Thumb

Variable width column charts and histograms

In Excel, the standard column chart will display all columns with the same width at regular intervals.  However, in some circumstances, it would be better for the width of each column to be different.  For example, where each column represents different ranges of data.  This same principle of column width also applies to histograms.

Excel does not have any settings to change the width of individual columns when using a column chart.  However, it is possible to get creative with a stacked area chart and the correct data layout.  With a bit of trickery, it is possible to create this chart:

Variable width chart complete v4

 

Basic principles

To create a variable width column chart we will be using a stacked area chart.  Normally, this chart type uses fixed intervals for the x-axis (the bottom axis).  By changing the x-axis to display a data axis we can plot a point anywhere along the x-axis.  It is even possible to plot points in the same place along the x-axis, which results in a vertical line.  This provides us with the ability to create the illusion of columns.

The data labels in the chart above are also an illusion.  They are created by a line chart which has been set to have no color.

 

The chart

The chart we will be creating in this example uses fictional data to show customer satisfaction for a product based on various age ranges.  The main data is shown below,

Variable width chart initial data
Initial Data

This data will be used to create this chart.

Variable width chart complete v3
Chart to create

 

 

Setting up the data

The first step is to set-up the data in the right way.

Variable width chart source data

Our data starts at 18, but the chart axis starts from zero, therefore the values in Cells A13 – A57 (shown above) are the number of years above 18.

Each category has 4 main points.  Using the 18-25 group as an illustration, the first point is zero (blank), the next point is the value 7.6.  Both of these points happen at zero years over 18.  The next two points happen at 7 years over 18 (i.e. 25 years), the 7.6 is repeated, then reduces to zero.  The remainder of the points in Cells B17 – B24 are zero (blank).  Each subsequent category follows a similar pattern, but has more or less zeros before or after the data.

 

Creating the variable width chart

This tutorial is based on Excel 2016.  The chart can be created with other versions of Excel, however, whilst the options may be in a different place, the principles are the same.

Select the cells without the age range column (Cells B12 – F24 in our example).

Variable width chart select data

From the Ribbon click Insert -> Charts -> See All Charts.

Variable width chart - Insert chart

From the Insert Charts window click All Charts -> Area -> Stacked Area -> OK.

Variable width chart - Insert chart window

A new area chart will appear

Variable width chart first view

Right-click on the X-axis and select Format Axis…

Variable width chart format axis

From the Format Axis window select Date axis.

Variable width chart - Format Axis Date

Right-click on one of the chart series, click Select Data…

Variable chart width select data right click

From the Select Data Source window click Edit from the Horizontal (Category) Axis Labels box.

Variable chart width edit horizontal axis labels

Set the Ages column as the Axis Labels.  Then click OK.

Variable width chart - select data labels

The chart will now start to take shape as a variable width column chart or histogram.

Variable width colum chart basic complete

 

Adding data labels to the variable width Chart

The chart above is OK.  But it would look better if the data labels were directly above or below the columns.  Once we have this, we can delete the unsightly numbering on the X-axis.  Unfortunately adding labels requires changing the source data, there needs to be additional columns and rows.

Variable width data for data labels

Notice that there are now 3 rows for each value, a mid-point has been inserted into the middle of each age range.   Create the chart in the same manner as above, also including the Label Height in the chart data source.  The chart should look like this:

Variable width chart with data lables inital view

Right-click on the Label Height chart series and select Change Series Chart Type…

Variable width chart with data labels select chart type

Change the chart type to a line chart – as there is only one line, the type of line chart does not matter too much.

Variable width chart with data labels add line chart

Right-click on the line chart and select Add Data Labels…

Variable width chart line chart add data labels

Next, right click on the data labels and select Format Data Labels…

Variable width chart format data labels

From the Format Data Labels window, set the Label Position to Above.  If you are using Excel 2013 or later, click Value From Cells and select range containing the data labels (Cells H13 – H28 in our example).

Variable width chart data labels

For those using Excel 2010 and before, Value From Cells will not be an option.  If so, double-click on each data label and type the label description manually.  Delete any labels you do not wish to keep.

Right-click on the Line Chart and select Format Data Series

Variable width chart line chart format series

Set the Line to No Line and the Marker Options to None.

Variable width chart line chart no color

Variable width chart marker no color

Delete any unnecessary chart items, such as Chart Title, Legends etc, then apply any additional formatting you do want.

Variable width chart complete v3

If you would prefer the data labels below, that’s possible too.

Variable width chart complete v4

 

What Next?

Do you want to create beautiful, easy to read and insightful charts all the time?  If so, I high recommend you get Effective Data Visualization: The Right Chart for the Right Data(affiliate link).  This is one of my favorite Excel books.

Not sure if this book is for you?  Read my review.

Save

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *