A reader asked how to make an axis that had tick marks but no line. This gives a nice clean appearance to the chart, but unfortunately cannot be done with Excel’s native axis formatting. Of course, anyone who has read my blog or my web site knows that this does not bean it can’t be done. You just have to know how to fake out Excel.
I’ll use a column chart to illustrate this technique, and I’ll also start with a little dissertation on column chart styling.
Behold the default Excel 97-2003 column chart. Functional yet oh so ugly. Muddy gray background, black border and line elements, and a nondistinct fill color.
The Excel 2007 default column chart is a little better, without the dreary background.
A few minor changes to the Excel 2003 chart bring a great improvement. The gray is gone, the bar fill color is a bit nicer and the bars are a bit wider, the font isn’t so huge, the gridlines are lightened a bit, the plot area has been expanded to fill the chart area, and the extraneous tick marks along the category axis are removed.
You can lighten the gridlines, borders, and axes a bit more to help emphasize the data.
You can remove the border, leaving axes only, and you can remove the gridlines.
Some people like the gridlines, and if you keep them you can remove the Y axis line and tickmarks. This is a pretty nice style for a column chart
But how can you remove the axis line and the gridlines, and leave only tick marks? Excel doesn’t provide for tickmarks without the axis line too. But you can build your own set of tick marks.
One way to do this is to add a helper XY series with hidden markers but visible error bars. Here is the data range for this example. The yellow range contains the column chart data. The green range contains the data needed for the XY series; The X values place the points along the left hand axis, and the Y values position a point at each axis tick location. The blue range contains the custom value for the error bars: adjustments can be made to the size of the error bars by editing this value, instead of using the Format Error Bars dialog.
Start by hiding the axis ticks and lines, but leaving the tick labels.
Copy the XY series data (green shaded range), select the chart, and use Paste Special to add the data as a new series. The series is initially formatted as another column series.
Right click on the new series, choose (Change) Chart Type, and select an XY chart type. Excel initially places it on the secondary axes, and adds nice dark axes to the chart.
Format the XY series. and place it on the primary axes. Now it is properly aligned in the chart.
Add positive X error bars to the XY series, using the blue-shaded cell for the custom value.
Format the series to hide it: no lines and no markers.
Finally format the error bars: use a medium gray instead of black, and use the stule with a line only, no end cap. If you need to change the length of the error bars, simply fiddle with the value in the blue-shaded cell.
This is also a very clean style for a column chart. The same technique can be used on most other chart types.
