Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 56 Create a Column Chart with Variable Widths and Heights

Wouldn't it be nice to create
a column chart whose columns can vary in width and height? Then, as
you plot your data into the columns, the columns'
width and height cleverly adjust themselves
simultaneously.

Excel doesn't provide
this feature directly, but by hacking an XY scatter chart you can
create a very effective variable width column chart. XY scatter
charts are used to compare values; therefore, they provide a perfect
base on which to start creating a variable width column chart.

Figure 5-24 shows a variable width column chart that
charts the percent share versus cost for the following expenses: gas,
electricity, water, food, travel, and other. The X axis (the axis
along the bottom of the chart) shows the percentages (%), while the Y
axis (the axis on the lefthand side) shows the cost ($).

Figure 5-24. XY scatter chart set up from range D2:E8

To create this chart, set up some data
such as that in Figure 5-24 and, using the Chart
Wizard, highlight the range D2:E8. Then, in Step 1 of the Chart
Wizard, select the XY Scatter Chart option. Accept the default
scatter chart, which shows only points, and click Next to move to
Step 2 of the Wizard. Ensure that Columns is selected. Click the Next
button to move to Step 3 of the Wizard, and under Value (Y) Axis,
type Cost ($). Click the Next button and
ensure that the chart will be produced as an object, not on a new
sheet. Click Finish to see the scatter chart.

You can use Ctrl-~ (which is the same on the Mac) to show you the correct formulas to place in the cells. You also could select Tools Options... View (Views under Excel Preferences on Mac OS X) and check Formulas under Window options.

Now it's time to play
around with the chart to create columns. First remove the legend and
gridlines (highlight them, then click Delete) and format the plot
area to no fill by clicking the gray background, right-clicking, and
selecting Format Plot Area. Under Area, select None.

Highlight the X axis, then double-click it
to get to the Format Axis dialog. Click Scale. Under Value Axis
Scale, set the Minimum to 0 and the Maximum to 1. Click the Patterns
tab and set the Major Tick Mark type to None and the Tick Mark Labels
to None, then click OK. The scatter chart will look something like
that shown in Figure 5-25.

Figure 5-25. Modified scatter chart

The next step is to create the lines
for the columns, so double-click the data points to bring up the
Format Data Series dialog. Click the X Error Bars tab. In the Display
section, select Minus, then select Custom - Range and highlight the
range F2:F8. This produces the horizontal line at the top of the
column.

Now click the Y Error Bars tab and
select Both under Display. Set the Custom + Range to H2:H7 and the
Custom - Range to G2:G8, then click OK. This will give you the
vertical sides of the columns.

Now that all the hard work
is done, it's time to tidy up a bit and add some
labels. First, under the Format Data Series dialog, select Patterns
and then select None under Marker. The results are shown in Figure 5-26.

Figure 5-26. XY scatter chart manipulated to produce variable width column chart

If you want to use labels, you need to
download John Walkenbach's Chart Tools, available
from http://j-walk.com/ss/excel//image/library/english/10052_charttools.
Part of this add-in is designed specifically for data labels. It
enables you to specify a worksheet range for the data labels for a
chart series. (Unfortunately, it doesn't seem to
work on Excel for the Macintosh, even after extraction from its EXE
distribution.)

Before you use Chart Tools, you must add a
new data series to provide X-axis labels for the chart. So, highlight
the chart, right-click, and select Source Data and then Series.
Select Add to add a new series. Under X Values, highlight the range
I3:I8, and under Y Values, highlight the range J3:J8. Format the new
data series so that it has no marker by going to the Format Data
Series dialog, selecting Patterns, and then, under Marker, selecting
None.

Now it's time to use an
add-in. Make sure you have Series 2 selected, and then select Chart
JWalk Chart Tools. When the dialog box pops up, make the
data label range K3:K8.

To add yet another new data series to provide column labels for the
chart, highlight the chart, right-click, select Source Data, and then
select Series. Select Add to add a new series. Under X Values,
highlight the range I3:I8, and under Y Values, highlight the range
C3:C8. Again, format the new data series to have no marker by
selecting Pattern, then Marker, then None in the Format Data Series
dialog.

Again, use your add-in. This time highlight Series 3 and link the
data labels to A3:A8. The result will look like that shown in Figure 5-27.

Figure 5-27. Completed variable width column chart

The fantastic thing about this type of chart is that the bars will
either expand or contract up the Y axis and along the X axis when the
entries in the % Share or the Cost ($) columns change. Pretty
nifty.

Andy Pope