For business users of Microsoft Excel Free guides and templates

Chart Formatting

How to Help Readers Interpret Chart Trends Correctly...Three Tips in One

You can use vertical text in Excel to tell readers whether an upward-sloping line is good or bad news. But vertical text brings jaggies. Here's the solution.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

In a chart of business performance, an upward-sloping line could indicate good news or bad.

If the chart shows profits or sales, an upward-sloping line is good news. But if the chart shows expenses or measures about problem areas, an upward-sloping line, or a rising series of column plots, shows us bad news.

Good-news, bad-news chart indicatorsSo when a report includes a mix of such charts, how can we help readers to interpret each chart trend quickly and correctly?

The Wall Street Journal uses indicators like the ones shown here. And you can add similar indicators to your own chart figures…if you know three tips.

At first glance, the solution looks obvious. This obvious solution uses two of my three tips. Both of them also work in Excel 2003. But because the third tip only works with New Excel (Excel 2007 and after), I’ll discuss only that program.

Tip One: Make the Text Box Dynamic

Tip One is a trick I’ve used for years to make the text in text boxes dynamic. To see how it works, suppose you have a value or formula in a cell, and you want your text box to display the cell’s information automatically. Here’s how to do it:

Click on the border of your text box. Type an equal sign in your formula bar. Then click on the cell that contains the information you want to display.

After you take these steps, your text box will display the cell’s content…but only up to the first 255 characters. Similarly, you could type in a range name after the equal sign. But Excel won’t let you enter a formula.

After you take this step, your text boxes will update as readily as formulas in your cells do. In effect, text boxes act as cells that float above your spreadsheet grid.

Tip Two: Create the Arrow Shapes

Tip Two is to create the colored arrows by combining rectangles, triangles, and your text boxes.

If you’ve not worked with drawing tools before, they’re pretty easy to figure out. In Excel 2007 and above, you insert the shapes using Insert, Illustrations, Shapes. Then, with one or more Shapes selected, you use the controls in the Drawing Tools tab. You can make your task easier by zooming your Excel display to 300% or so as you construct these displays.

After you join a rectangle and triangle, position a dynamic text box on top of the arrow-like shape. Set each text box to show no borders or fills, and to use a white font.

Alternatively, you could fill the text boxes with the colors you want, and then attach the triangle objects to them, skipping the rectangles entirely. Try both approaches to see which works best for you.

At any point, you can group several shapes together. To do so, select the shapes you want to group and then choose Drawing Tools, Format, Arrange, Group, Group.

Rotated Text Boxes Introduce Jaggies…And Worse

So far, everything seems easy. You create the shapes and then add text boxes linked to cells that contain the text data you need. Finally, all you need to do is to group the arrows and text into one shape, and then rotate the grouped shape by 270 degrees.

To rotate the selected shape, press Ctrl+1 to launch the Format Shape dialog. Then adjust the rotation in the Size tab. Alternatively, select the shape then click and drag the green handle to rotate it manually.

But this is where the main problem arises! As soon as you rotate the text boxes, the text can look REALLY bad…depending on the font and formatting you choose.

before and after indicatorsThis figure illustrates the problem. The red horizontal text box looks just fine. But after I rotate a copy of it as shown, the “y” now has jaggies and the “M” becomes unrecognizable.

On the other hand, after I applied the trick I’m going to explain, Excel’s version of the green vertical text box seems to be about as good as the horizontal text.

Tip Three: How to Display Great Vertical Text in Text Boxes

The reason for the display problem is that standard anti-aliasing in text boxes is designed for horizontal text. So we need to force Excel to use a special anti-aliasing method. To do so, take the following steps:

1. Temporarily change the linked text box to standard text. So, starting with the horizontal version, click on the edge of the text box and delete the formula in the formula bar.

2. You now can right-click on the text displayed in the text box. When you do so, you’ll now see “Format Text Effects” in the context menu. Click on this item.

3.  In the Text Outline tab of the Format Text Effects dialog, choose Solid Line.

4. Ignore the Color setting, but set the Transparency value to 100%.

5. Re-enter the formula that links your text box to the cell with the text to display. In the figure above, for example, you would enter: =$C$2With

6. Re-assign your text formats as needed.

Use Tip Three Headline Text

Tip Three also works well with rotated headline text. To illustrate, this text originally was vertical, as in the previous example. But I rotated it after I copied it, which lets you examine it more easily.

Headline text in Excel

As you can see, the red box has “jaggies” in its text. That is, it’s not anti-aliased. But in the green box, Tip Three produces anti-aliased text.

This technique also can bring a slight change to standard horizontal text in text boxes. Here, the text has always been horizontal. But the edges of the text in the green version are slightly fuzzy, which can hide occasional display problems with large headlines in text boxes.

Anti-aliased horizontal text in Excel text boxes

Using the Tip Three technique does require more processing power than otherwise. But I haven’t noticed a practical difference in actual use.

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.