USE AN INVISIBLE SERIES TO FLOAT COLUMNS

Figure 1126 Have your bars or columns float.

Strategy: This is a common trick. You will have the bars sitting on top of a rogue series and then make the series invisible. Follow these steps:

1. Next to your original data, build a new data range that will be used for the chart. You will have two series. You can call them anything, but here I’ve used “Hide” and “Show”.

2. Use formulas to calculate the height of the visible column and the height at which the column should float. Below, Step 3 is 175 tall and is floating 275 in the air. The 275 is the height of the previous two bars.

3. Create a stacked column chart.

4. Click on one of the bottom columns to select the Hide series.

5. On the Format tab, open the Shape Fill drop- down and choose None.

Figure 1127 The bottom columns will go away

6.If the lower columns have an outline, click the Shape Outline dropdown and choose None. The bot- tom columns will now disappear.

7.Click on the legend and press Delete.

The original figure in this topic used arrows instead of columns. This is easy to do. 1. Use Insert, Shapes, Arrow and draw an upward facing arrow near the chart.

2. Click on the arrow shape to select it.

3. Ctrl+C to copy the arrow.

4. Click on one of the visible columns in the chart. This will select all of the column in the series. 5. Press Ctrl+V to paste. This will replace the columns with the arrows.

A similar trick is used to make waterfall charts. The black columns are series 1. The white columns are series 3. An invisible series 2 makes the white columns float.

The waterfall chart here uses a hidden series 2 to achieve the basic effect. The data labels are the most difficult part of the chart.

1. .

Figure 1128 This is a stacked column chart with a hidden series 2.

Width.

Figure 1129 The 2450 is =3500-1050. The 1470 is =2450-980.

5.Select series 1 and add data labels to the center.

6.Select series 3 and add data labels to the center.

It is frustrating that you can not choose Outside End for the data labels. You must manually move each label into position. Follow these steps.

7. Click one label in series 1 to select all labels in the series. 8. Click one label again to select the individual label.

9. Drag the label up to the correct position.

10.Choose the next individual label and drag it.

11.Repeat step 10 for each label.

12.Repeat steps 7-11 for series 3.

USE ROGUE SERIES FOR SHADING

Problem: I want to shade the areas between the gridlines in this chart.

4

Figure 1130 Add alternating shading between the gridlines.

Strategy: Use four series as stacked area charts.

To use this method, you need to take control of the vertical axis. Format the vertical axis. Figure out the minimum, maximum, and major unit that you will be using.

Figure 1131 Make sure the scale won’t change.