Add-ins:
Excel
PowerPoint

The x-axis of a Column Chart can contains either category
values (the x values are equidistant) or dates. Neither of these allows for a
truly numeric x axis. This tip shows how to **simulate** a column chart that
has cardinal numbers on the x axis (cardinal numbers contain a sense of ‘distance’
– e.g., 97 is 2 more than 95, 7 more than 90, and 3 less than 100). Of course,
once the axis supports cardinal numbers it also becomes possible to convert it
to a logarithm (or log) scale.

Starting with Excel 2007, Microsoft enhanced the charting module to provide greater formatting control over the chart elements. This makes it possible to make a XY Scatter chart look like a column chart!

Suppose we want to plot the data in x in a column chart.

Figure 1

What we would like to see is as in Figure 2 and maybe even a logarithm scale on the x axis as in Figure 3.

Figure 2

Figure 3

The idea behind simulating this effect is to create a XY Scatter chart, hide the default markers and add appropriately formatted error bars.

Start with a XY Scatter chart for the data in Figure 1. Remove the title and legend, if any.

Figure 4

Add error bars: Select the plotted series, then **Chart Tools context ribbon | Layout tab | Error Bars
dropdown | More Error Bars Options… button**.

Figure 5

In the resulting dialog box, select 100% Negative error bars, change the Line Style width to 10pt, and format the line color as desired.

Figure 6

Figure 7

Figure 8

The chart should now look like

Figure 9

Format the series to have no marker: In Excel 2010, double-click
the plotted series (in 2007 right-click and select Format Data Series…) and
from the **Marker Options tab**, change the
**Marker Type to None**.

Figure 10

The result is in Figure 11.

Figure 11

It may not be obvious, but the 1^{st} column
overlaps the y axis. It’s more apparent if the x-axis scale is a log scale.
To make the change, double-click the x-axis, and in resulting **Format Axis dialog box**, in the **Axis Options tab**, **check
the Logarithmic scale checkbox**. The result is in Figure 12.

Figure 12

If the appearance of the 1^{st} column is not
acceptable adjust the minimum value of the x axis so that the no part of the column
overlaps the y-axis. It will also be necessary to adjust the x-axis parameter ‘Vertical
axis crosses’.

Figure 13

The final result is in Figure 14.

Figure 14