Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Charts for Excel 3.0

 

Books at Amazon.com

 

Staggered Axis Labels.


 

Sometimes the labels of a category axis are too closely spaced, and either Excel inclines the labels, or it will not render them all. Unfortunately Excel offers no built-in method for staggering axis labels, but there is a simple trick to achieve the same result.

Consider this simple data in A1:B8, and the column chart constructed with it. Nothing unusual, the axis labels line up below the corresponding data points, all in a single row. The blank cell in A1 helps Excel figure out that Row 1 is for series names, and Column A is for category labels.

  A B
1   The Data
2 ALPHA 1
3 BRAVO 3
4 CHARLIE 2
5 DELTA 4
6 ECHO 3
7 FOXTROT 5
8 GOLF 4

That's fine, all the labels are perfectly legible. But the chart is pretty wide. When you make the chart narrower, Excel rewrites the labels at an angle to make them all fit.

These tilted labels print fine, but on screen they are not very clear. It's also better charting technique to keep as much text horizontal as possible, to make reading the chart easier. You can double click the axis, and set the alignment to 0 degrees, as opposed to Automatic. Unfortunately, Excel still tries to make the chart more legible its way, and it shows only alternate labels.

Double click on the axis again, and on the Scale tab, change the middle box (Number of categories between tick mark labels) from 2 to 1. This places all labels along the axis, but unfortunately these labels overlap.

In Excel 97, you could use a two-column range for category labels, alternating the labels with blank cells as shown below at left, and the labels would be staggered nicely. However, Microsoft "enhanced" the appearance of axis labels from multiple column ranges. This enhancement in general was an improvement, but the orientation of the upper row of labels and the introduction of lines between labels has rendered this technique ineffective (below right).

  A B C
1     The Data
2   ALPHA 1
3 BRAVO   3
4   CHARLIE 2
5 DELTA   4
6   ECHO 3
7 FOXTROT   5
8   GOLF 4

Fortunately there's another easy trick to generate staggered labels. In alternate cells (BRAVO, DELTA, and FOXTROT), insert a carriage return before the label text by clicking in the cell, moving the cursor to the front of the label, then holding the ALT key while pressing ENTER. (The corresponding numbers in column B also appear to have a carriage return inserted; by default, cell contents are vertically aligned at the bottom of the cell. The table below shows a paragraph mark, , in the cells with an inserted line feed to illustrate the technique, although these do not show in Excel.) The category labels are nicely staggered, and perfectly legible.

  A B
1   The Data
2 ALPHA 1
3
BRAVO
3
4 CHARLIE 2
5
DELTA
4
6 ECHO 3
7
FOXTROT
5
8 GOLF 4

 
  A B C
1     The Data
2 ALPHA ALPHA 1
3
BRAVO

BRAVO
3
4 CHARLIE CHARLIE 2
5
DELTA

DELTA
4
6 ECHO ECHO 3
7
FOXTROT

FOXTROT
5
8 GOLF GOLF 4

An alternative to editing the original labels is to insert a column (B) with formulas that selectively insert carriage return, and use the inserted column as the new range of category labels. Select B2:B8, with B2 as the active cell, enter the following formula, and hold CTRL while pressing ENTER, to enter the formula into the entire selected range at once. Alternatively, enter the formula into B2, and drag it down to fill B2:B8. (Cells with this formula are shown in blue.)

=IF(MOD(ROW(),2)=0,"",CHAR(10))&A2

Note: CHAR(10) denotes a carriage return.

 

Page copy protected against web site content infringement by Copyscape

Peltier Tech Charts for Excel 3.0


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile