PART 4: MAKING THINGS LOOK GOOD	435

You should never use cone or pyramid charts. The categories at the top of each cone get far fewer pixels than the categories at the bottom. In the next figure, the 34% spent on administration seems practically nonexistent.

Figure 1090 Never use cone or pyramid charts.

PREVENT THE DROP TO ZERO

Problem: I’ve built formulas to create a chartable range. I want to show how the fund balances have been increasing for three years. All of a sudden, the points for future months look like someone spent all the money, dropping to zero.

4

Figure 1091 Prevent the zero points for the future.

Strategy: Zero cells will be plotted. Empty cells or #N/A! cells will not be plotted. Change your formu- las to put an NA() instead of 0. Say that you are building the chart range with =SUMIF(). Start to use =IF(SUMIF()=0,NA(),SUMIF()). The chart will look much better.

Figure 1092 Use #N/A instead of zero.

It is ironic that 99% of the time, you are trying to avoid the #N/A. In this case, however it solves the prob- lem.

436	POWER EXCEL WITH MR EXCEL
	EXPLODE ONE SLICE OF THE PIE

Problem: What is the point of an exploded pie? It doesn’t look any better than a pie.

Figure 1093 Adding white spaces between wedges doesn’t add anything.

Strategy: It is better to explode one slice of a pie. If you want to draw the reader’s attention to one slice of the pie, bring that slice to the front of the chart and explode that slice of the pie.

1. Identify the pie slice that you want to highlight. 2. Right-click the pie and choose Format Data Series.

3. Use the Rotation slider to bring that slice of the pie to the 4 o’clock position. There is more room for the pie slice at that point.

Figure 1094 Rotate the chart.

4. In the chart, do a single click twice on the wedge to be exploded. 5. Click and drag that wedge out from the center of the pie.

Figure 1095 Explode one slice of the pie to call attention to that slice..

PART 4: MAKING THINGS LOOK GOOD	437

MOVE SMALL PIE SLICES TO SECOND CHART

Problem: All of the tiny pie slices really make the chart hard to comprehend.

		Figure 1097 The secondary chart shows detail of
Figure 1096	Everything under 10% is noise.	all the small slices.

Strategy: Excel offers two chart types that will take the small wedges to a secondary chart. To make these look right, you should always plan on tweaking the settings.

Choose the pie chart. Select Design, Change Chart Type. Choose the Bar of Pie icon.

Figure 1098 There must be a better name.

Double-click the secondary chart and choose Format. You have choices on how Excel will split points to the secondary axis. The first dropdown offers Position, Value, Percentage Value, and Custom. With custom, 4 you can choose each data point and specify if it is in the first or second plot. An easier choice is to choose

Percentage Value. Move everything that is below 10% to the secondary plot.

Figure 1099 Adjust which items are in secondary plot.

You can control the size of the secondary plot. It starts at 75% of the size of the pie. If those small slices are unimportant, make it smaller. However, if you need to see the detail of those small items, you can go up to 200% of the size of the pie chart.

Figure 1100 These percentages are relative to the first pie.

Gotcha: I had to choose individual labels near the bottom of the column and resize just those labels to prevent overlapping. To select individual labels, you can click one label to select all labels, then a second click to select one label. Then, use the right arrow key to move from label to label.

438	POWER EXCEL WITH MR EXCEL
	ADD A TRENDLINE TO A CHART

Problem: In his book Success Made Easy, retail guru Ron Martin suggests using a daily chart to track your progress toward a goal. His typical chart shows your progress toward the goal as well as where you need to be to remain on track.

Here, the thin line is the track. This is where I would need to be in order to finish by the set goal.

The thick, wavy line is my actual work toward the goal. I can see from the chart that I am currently slightly ahead of the track. However, what would happen if I continued to work at my current average pace? Would I meet the goal?

Excel makes it easy to add a trendline to charted data.

1.Right-click the graphed line for actual results. From the menu that appears, choose Add Trendline.

Figure 1101 Actuals are barely ahead of the On

Track line.

Figure 1102 Add a trendline to the Actuals line.

2.Excel displays the Format Trendline dialog. The result is a dotted line that shows the predicted There are a few settings to change in this results if you continue at your current pace. dialog. Go to the Trendline Name section.

Change the name to Custom. Type a name such as Forecast. Keep the dialog open.

3.Choose Line Style from the left navigation.

Open the Dash Type dropdown and choose a dashed or dotted style. Since this line is only a forecast, you want to differentiate it from the other lines.

Figure 1103 Excel projects your final results based on past actuals.

As you continue to plug in actual data, the trendline will redraw. Seeing the forecast line predict a sizable miss usually causes me to really put it into hyperdrive for the next few days. A couple of days of aboveaverage activity causes the actual line to go above the track line. Nevertheless, the dotted trendline is still predicting that I will miss the goal. That is because the trendline sees all those days early in the month when I did practically nothing. It predicts that those days might happen again.

In other data sets, the chart might only show actuals, with the last actual appearing at the right edge of the chart. In that case, you can use the Format Trendline dialog to specify that the trendline should predict forward a certain number of periods.