PART 4: MAKING THINGS LOOK GOOD		431
	DISPLAY AN AXIS IN MILLIONS

Problem: My numbers are in millions. I am wasting a lot of space showing all of those zeros along the vertical axis.

Figure 1083 The zeroes along the vertical axis take a lot of space.

Strategy: Double-click the numbers in the axis labels. In the resulting settings area, find the Display Units dropdown and choose Millions.

4

Figure 1084 Change the axis Display Units.

Results: Excel removes the zeros and adds a label indicating that the numbers are in millions.

Figure 1085 The zeroes are replaced with “Millions”.

432	POWER EXCEL WITH MR EXCEL
	SELECT ANYTHING ON A CHART TO FORMAT

Problem: I need to further customize a chart. How do I format a series or a single data point?

Strategy: I certainly understand the frustration. The Excel 2010 Layout tab and the Excel 2013 Plus icon offer lots of chart elements to format, but they missed the most important dropdown; the one for series.

You can choose each series from the Current Selection dropdown on the left side of the Format tabs. But the dropdown does not offer each individual point or data label.

The answer is to format anything in the chart by double-clicking.

To select a series, click on the series. If the series is too small to click, then use the Current Selection drop- down on the Layout tab.

To select an individual data point, you have to first select the series then do a second single-click on the data point. You can never choose data points using the Current Selection dropdown.

To add data labels to a chart, make sure that no series is selected. Open the Data Labels dropdown on the

Layout tab and choose any item. You can even choose More Options and immediately click OK. The alter- native method is to right-click one series and choose Add Data Labels, but this will not add data labels to the other series.

Once you have data labels, click on one label to select all of the labels for that series. Once data labels are selected, click again on one data label to select the label for an individual point.

Additional Details: If you click in a chart and start pressing the right arrow, you will cycle through every element that can be formatted. Watch the Current Selection dropdown to see what you are selecting.

In the chart below, Excel cycles through 46 separate items. In contrast, the Current Selection dropdown only offers 14 items. What is the difference between 46 and 14? The arrow keys will select each individual entry in the legend, each data point, each data label, even the “Millions” tag next to the axis.

Figure 1086 Using arrow keys to cycle will include each legend entry.

Once you have selected something, use Ctrl+1, or the Format Selection button to access the Format dialog box.

Everything on a chart can be customized. You can use any of these methods: ●● Right-click the chart element and choose Format

●● Double-click the chart element

●● Click the chart element to select it and then press Ctrl+1

●● Use the arrow keys to select an element and press Ctrl+1

●● Choose the chart element from the Current Selection dropdown on Format tab and then click the Format Selection button in the same group

There is no one “right” method of these five. There are times when the only way to format something is by clicking it and other times when something is nearly invisible and the only way you can format it is from the Current Selection dropdown. So you need to be ready to use whichever method will get you to the cor- rect element.

Gotcha: None of the methods described above will let you access an element that is not yet on the chart.

Use the dropdowns on the Layout tab to add elements such as Chart Title, Axis Title, Data Table, Error Bars, Drop Lines, Up/Down Bars, Trendlines.

PART 4: MAKING THINGS LOOK GOOD	433

Say that you want to change the color of just the May central region column. The first click on the central region column will select all of the central region columns. A second single-click will select just the one column. You right-click to access Format Data Point.

THE FORMAT DIALOG BOX OFFERS A NEW TRICK

Problem: I was formatting the chart axis, using the Format Axis dialog box. I was working in the Axis Options category. I accidentally clicked outside the dialog and clicked one of the columns in the chart. All of a sudden, I was transported to the Format Data Series dialog. What is going on?

Strategy: There is a single Format dialog box for every drawing object. While the dialog is displayed, you can click on any new object on the worksheet, and the Format dialog box will change to offer settings for that object.

In a chart, you can display the Format dialog once and keep changing the formatting for other elements.

For example, you might start formatting the axis. You can then choose Series 1 from the Current Selection dropdown and format that series. You can then choose Chart Title from the dropdown and format the title. When you are finished, you close the Format dialog box.

Additional Details: You can even access ribbon commands while the Format dialog is displayed. For ex- ample, you might need to select Layout, Chart Title, Centered Overlay Title to add a title to a chart. You can do this without closing the Format dialog box.

				USE MEANINGFUL CHART TITLES
	Problem: Excel tends to add boring chart titles. A chart title such as Sales or Profit merely labels the data
	in the chart. The title is nothing more than a legend in a large font. How can I make my chart titles more
	meaningful?
	Strategy: It’s a good idea to add a meaningful title that guides the reader. As an analyst, you can spot
	trends in the data, and you can point out something interesting in the chart by using the title.
	One annoying problem is that you seemingly don’t have a lot of control over the chart title formatting. Fol-
	low these steps to create a long title:
	1.		Select Layout, Chart Title, Above Chart. Excel adds the title Chart Title in a large font above the
			chart.
						4
	Gotcha: To edit the chart title, try triple-clicking the chart title. This should select all of the characters
	in the chart title. Alternatively, drag to select the characters in the chart title. If you just single-click the
	chart title and start typing, you will only see the title in the formula bar until you press enter.
	2.		Select the characters in the chart title using the mouse.
	3.		On the Home tab of the ribbon, choose a 14-point font size. Choose the Left Align icon.
	4.		Type a title such as Revenue Doubled in December.
	5.		Press Enter. Excel will move to a second line in the title.
	6.		Before typing the second line, change the font to 12-point on the Home tab.
	7.		Type the subtitle Post-holiday sales dropped GP% to 42%.
	8.		Click on the border of the title to exit Edit mode.
	9.		Drag the border of the title to the left in order to align the title with the left edge of the chart.

Results: You’ve added a title to guide the reader’s understanding of the chart.

Figure 1087 Guide the reader with a title and subtitle.

434	POWER EXCEL WITH MR EXCEL

Gotcha: The border around the title has only four handles. This means you can move the title, but you cannot resize it. In step 5, you were able to force the title box to add a second line. However, Excel can have a mind of its own and may decide to add a third line. It would seem that you could correct this if you had the ability to resize the title box. Instead, you would have to select characters within the title and choose a smaller font in order to coax the title back to the correct number of lines. Alternatively, delete the title and add a text box from Insert, Shapes while the chart is selected. You have more control with a text box.

AVOID 3-D CHART TYPES

Problem: I like the look of 3-D chart types, but they don’t seem to be accurate.

Strategy: 3-D chart types are not accurate, so you should try to avoid them. The 3-D effect usually ends up introducing errors into the chart.

Have you ever taken a photography class? The problem with a wide-angle lens is that anything in the foreground appears unusually large. 3-D pie charts have the same problem. The wedges at the front of the chart get more pixels than the wedges at the back of the chart. For example, both charts below are plotting the same data. This organization is spending 34% of its budget on adminis- tration. If you are the scientific review board, trying to argue that the administration slice is too large, rotate it around to the front, as in the bottom chart. In the bottom chart, 155% more pixels appear in the administration slice than in the research slice.

3-D column charts are not accurate, either. In the top chart below, you can see that each column is above a nearby gridline. Turn that chart into a 3-D column chart, and none of the columns actually extend to the neighboring gridline. People wonder if they should look at the front or the back of the column. I say it doesn’t matter because neither the front nor the back reach to the gridline.

Figure 1088 Wedges at the front of a 3-D pie appears unrealistically large.

Figure 1089 The 3-D columns don’t reach the gridlines.