Custom Axis Labels and Gridlines in an Excel Chart

The Problem

I came across a post on the Super User forum whose author wanted to Get Excel to base tick marks on 0 instead of axis ends (with fixed maximum or minimum). Essentially, the user wanted to scale the axes of his plot to ±35, but have axis labels every 10 units centered on zero, not starting at -35. The desired chart is shown here:

Chart With Axis Labels and Gridlines Where You Want Them

It seems like this should be easy in any charting software. It was a built-in feature of the first charting software I ever used, but then, I wrote that software myself in the mid-1980s. However, it’s not part of Excel’s charting engine, nor of many other charting packages.

Positioning your own custom axis labels and gridlines is not too difficult to do, by dispensing with the default labels and gridlines, and using dummy series along the X and Y axes, with data labels and error bars on these series.

Here is the data used in this tutorial. The First and Second blocks are used for the chart’s main series; the Axis block is used for both dummy series; and the Err Bar block is used if you don’t want the error bars to obscure the default axis lines.

Data for Custom Axis Labels and Gridlines

The Original Chart

Here is the XY Scatter chart of the First (blue) and Second (orange) data sets. I guess it’s a question mark symbolizing the confusion expressed by the original questioner.

Default Excel 2013 Chart

Here is the chart, squared-up so the gridlines outline approximately square regions. At this scale, the default axis limits are ±40.

Squared-Up Chart with Default Axis Scales

The margin around the plotted points is wider than desired, but any attempt to place the axis labels on multiples of 10 result in something like the chart above (±40) with too wide a margin, or the chart below (±30), with no margin.

Squared-Up Chart Imposing ±30 Axis Scales

Setting the axis scales at ±35 also locks the axis labels at half-multiples of 10 (at the 5’s: -35, -25, etc.).

Squared-Up Chart With ±35 Axis Scales

Adding Custom Axis Labels

We will add two series, whose data labels will replace the built-in axis labels. The horizontal axis dummy series (gray line and circle markers) uses the column of numbers (E2:E8) as X values and the column of zeros (F2:F8) as Y values. The vertical axis dummy series (yellow line and circle markers) uses the same column of numbers (F2:F8) as X values and the column of numbers (E2:E8) as Y values.

Chart With Dummy Series Along X and Y Axes

Select the vertical dummy series and add data labels, as follows. In Excel 2007-2010, go to the Chart Tools > Layout tab > Data Labels > More Data label Options. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Data Labels, and choose More Options…. Then in all versions, choose the Label Contains option for Y Values and the Label Position option for Left. The labels are (temporarily) shaded yellow to distinguish them from the built-in axis labels.

Data Labels Added to Dummy Vertical Axis Series

Select the horizontal dummy series and add data labels. In Excel 2007-2010, go to the Chart Tools > Layout tab > Data Labels > More Data Label Options. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Data Labels, and choose More Options…. Then in either case, choose the Label Contains option for X Values and the Label Position option for Below. The new labels are shaded gray to set them apart from the built-in axis labels.

Data Labels Added to Dummy Horizontal Axis Series

Select each axis in turn, and select the None option for Axis Label.

Default Axis Labels Have Been Removed

Adding Custom Gridlines

We will use error bars to the dummy axis series to replace the built-in gridlines. Select the horizontal dummy series and add error bars as follows. In Excel 2007-2010, go to the Chart Tools > Layout tab > Error Bars > more Error Bar Options…. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Error Bars, and choose More Options…. In all versions, default horizontal and vertical error bars of length 1 will appear on the data series.

Default Error Bars Added to Dummy Horizontal Axis Series

Select the new horizontal error bars. You may have to use the Current Selection dropdown on the Chart Tools > Layout or Format tabs or on the right click formatting popup to select them. Click Delete.

Select the vertical error bars. Format them to have an Error Amount (length) of 35 and no end caps. I’ve kept the default error bar line color of black, to distinguish them from the built-in gridlines.

Horizontal Error Bars Removed, Vertical Error Bars Scaled to Chart Height

Select the vertical dummy series and add error bars in the same way. In Excel 2007-2010, go to the Chart Tools > Layout tab > Error Bars > more Error Bar Options…. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Error Bars, and choose More Options…. In all versions, default horizontal and vertical error bars of length 1 will appear on the data series.

Default Error Bars Added to Dummy Vertical Axis Series

Select the new vertical error bars. Again, you may have to use the Current Selection dropdown on the Chart Tools > Layout or Format tabs or on the right click formatting popup to select them. Click Delete.

Select the horizontal error bars. Format them to have an Error Amount (length) of 35 and no end caps. The error bars are shown below in their default black line color, to distinguish them from the built-in gridlines.

Vertical Error Bars Removed, Horizontal Error Bars Scaled to Chart Width

Select each set of built-in gridlines (not the error bars we’ve just added so painstakingly) and press Delete.

Default Gridlines Have Been Removed

Format the error bars to have a light gray line color.

Custom Gridlines (Error Bars) Have Been Lightened

Finally, hide the two dummy axis series, by formatting them to have no markers and no lines.

Dummy Axis Series Have Been Hidden (No Lines, No Markers)

Note that the error bars on the (0,0) points of the dummy series block the axis lines, which are slightly darker gray. You may wish to keep the darker axis lines to help clarify the chart. This means applying custom error bar values, with 35 for all points except zero for the point at the origin.

Custom Error Bars that Don’t Obscure the Axis Lines

Select the vertical error bars. On the format dialog, under Error Amount, select Custom (they will suddenly transform to very short error bars with length 1), then click Specify Value. Delete the “={1}” from the Positive Error Value box, and select the range with custom error bar values (G2:G8). Then delete the “={1}” from the Negative Error Value box, and select the range with custom error bar values (G2:G8). Press Okay.

Select the horizontal error bars and repeat the steps above to assign the range G2:G8 as Custom Error Amount for the positive and negative error bars.

The result is shown here, with light gray error bars and slightly darker axis lines.

Custom Error Bar Values Added to Reveal Default Axes

This tutorial is just another example showing how to enhance your chart in ways that were not built into Excel’s extensive charting capabilities. Using other capabilities, there are numerous ways to add lines, labels, and other features to an Excel chart exactly where you want them. Just remember that you are controlling Excel, and not vice versa.

 

Peltier Tech Charts for Excel

Column Chart Gridlines Cutting Through Bars

An astute reader asked how to achieve the gridline effect in the following chart. The gridlines are not gray lines running across the whole chart, behind the bars. Instead, the bars themselves appear to be cut into sections where the gridlines would have gone. There are in fact gridlines in the chart, with a line color to match the background, in front of the bars.

Column Chart with gridlines cutting through bars

Gridlines

The thing is, gridlines are always in back of the bars, as shown in this chart. I’ve lightened the bars, and darkened and widened the gridlines, to show their normal relative positions. Don’t let me EVER catch you making gridlines like this.

Normal gridlines lie behind bars. Don't make gridlines like this!

If gridlines are used at all, they should be thin light gray lines, as unobtrusive as possible.

Use thin light gray lines for unobtrusive gridlines

But I digress.

The Data and Chart

Here is the population data for this exercise. Column B has numerical years. Column A has formulas as follows. The even decades (1800, 1820, etc.) simply link to the year; A3 has the formula =B3. The odd decades (1790, 1810, etc.) insert a line feed before the year; A2 has the formula =CHAR(10)&B2. The reason for this will become apparent when we get to it.

US Population Data for our chart

Here is the default chart created from the data in columns B and C.

Default column chart

Let’s apply some formatting. That default title is way too large, perhaps the only concession to my over-50 eyes in all of the Office suite. The gap width is set to 50% so the bars are more prominent. And I’ve changed the source data for the chart to column A. The line feed in alternating cells staggers the X axis labels so the years are easier to read. The vertical axis is removed, because we’ll add our own later.

Apply a little tasteful formatting

Adding Custom Gridlines

And now to add the gridlines. And how do we get gridlines in front of the data? Well, there are several ways to make horizontal lines in Excel charts. The crudest is simply to draw lines from the Shapes command on the Insert tab, but they’re hard to line up, and they don’t stay where you put them. What’s the trick?

Error bars. Error bars are positioned in front of bars in a chart. We’ll apply error bars, and color them white, so they appear to cut through the bars.

We’ll add a series of points along the right vertical edge of the chart, draw error bars to the left of these points, and add data labels to the right. Here is the data.

Data for our gridlines

To get the millions to show without all of those extra zeros, apply a number format of “0,,” (without the quotes, duh). The zero by itself means use the number format with no decimal digits, and each comma after the zero means slice off one group of three zeros, one for thousands, another for millions.

But that 23.5? People always ask me where that came from. The answer is easy once you know it. Let’s look at the chart without the year labels, and just count the bars. They are numbered 1 to 23 from left to right. The tickmarks on the axis between the bars are at the halves: the chart starts at 0.5, the next tickmark is at 1.5, all the way up to the tick at 23.5 at the right edge of the chart. We’ll add an XY series with the points directly above this tickmark, at X=23.5.

Where does '23.5' come from?

Copy the gridline data (two columns and all rows including the “million” label). Select the chart and use Paste Special to add the data to the chart. Make sure the Paste Special dialog has these options selected: New Series, Values in Columns, Series Names in First Row, Categories (X Labels) in First Column, and DON’T Replace Existing Categories.

Paste Special

And here is the chart with the added data. It’s in column chart form, but we’ll fix that.

Add gridline data to chart

Right click on the added bars, choose Change Series Chart Type from the pop-up menu, and choose the XY Scatter type with markers only.

Convert gridline series to XY Scatter

Excel moved the series to the Secondary axis, and dumped an extra set of axes into the chart. No problem, Format the XY series, and on the first tab of the Format Series dialog, choose Primary Axis.

Move XY series to primary axis

Now it’s time to add the error bars. Select the XY Series, and on the Chart Tools > Layout tab, click on Error Bars, More Error Bar Options.

Add error bars

The horizontal error bars are easily visible: note how they lie in front of the bars. You can’t see the vertical error bars, because they are only one unit (one person) tall, but they are selected when you add the error bars. Close the Format Error Bars dialog and press delete to remove the vertical error bars.

Delete vertical error bars (can't see them, because they are 1 person tall)

Format the horizontal error bars. Choose the Minus direction, No Cap as the End Style, and a Fixed Value of 23 (see the explanatory chart above: the error bars reach from X=23.5 to X=0.5, so they are 23 long). Here’s how the Format Error Bars dialog should look:

Format Horizontal Error Bars

And here’s the chart with the appropriately sized error bars.

Format horizontal error bars

Select the XY series, and on the Chart Tools > Layout tab, choose Data Labels > Right. This adds the default data labels, which show the value. The data labels have the same number format as the cells containing the values. You’ll probably have to select the plot area and drag the right edge inward to make room for the labels.

Add data labels to XY series

Format the XY series, and choose None for the Marker Option.

Hide markers of XY series

Finally, format the error bars, so the line color matches the chart background, in this case, white.

Format error bar line color to match chart background

Lessons Learned

In this tutorial I’ve shown how to use gridlines that seem to cut the bars in a bar or column chart. Among the tricks I’ve used are:

  • Line feeds in the category axis labels to stagger labels and prevent overlap,
  • Using error bars to draw horizontal lines in a chart (error bars are great for vertical lines, too),
  • Using data labels for custom axis labels.

You can accomplish many things with these tricks in your charting arsenal.

For an Excel workbook that contains this step-by-step tutorial, click on this link:  Gridlines_Splitting_Bar_Chart.xlsx

 

Peltier Tech Charts for Excel

Draw an Axis With Tick Marks But No Line

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.

Excel's Default Column Chart

The Excel 2007 default column chart is a little better, without the dreary background.

Excel 2007's Default Column Chart

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.

Improved Column Chart with Dark Borders

You can lighten the gridlines, borders, and axes a bit more to help emphasize the data.

Improved Column Chart with Medium Borders

You can remove the border, leaving axes only, and you can remove the gridlines.

Improved Column Chart with Medium Axes and No Borders

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

Improved Column Chart with Medium Gridlines and No Y Axis

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.

Tick Mark Data

Start by hiding the axis ticks and lines, but leaving the tick labels.

Improved Column Chart with No Y Axis

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.

Adding Tick Marks Step 1

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.

Adding Tick Marks Step 1

Format the XY series. and place it on the primary axes. Now it is properly aligned in the chart.

Adding Tick Marks Step 1

Add positive X error bars to the XY series, using the blue-shaded cell for the custom value.

Adding Tick Marks Step 1

Format the series to hide it: no lines and no markers.

Adding Tick Marks Step 1

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.

Improved Column Chart with Medium Tick Marks

This is also a very clean style for a column chart. The same technique can be used on most other chart types.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0