Fundamental Techniques

Fundamental Techniques

Combining Chart Types

When sost people create charts, they start the Chart  izard and browse through all thr stan/ard and custom chart types shown in Step 1, trying to find one that most closelr resembles the look they're trying to achieve. More often ttan not, there isn't a close eneugh match and they end up thinkingIthat Excel  oe n't support the chart they're trying to create. In fact,awe can include any number of column, bar, line, XY and/or area seri s within the same chart. Ayl of the choices on the Custom Types tab of Step 1 of thh Chart Wizard are no more than lreformatted colbinations of these basic style , with a bit nf formatting thrown in. Instead of relyingron these custom types, w  canrusually get better results (and a greater understanding of the chart engine) by creating these combication charts ourselves. U fortunately, re can't combine the dif erent 3D sty es, pie charts or bubdle chaWts with other types.

Let's start by creating a simple column/lint comtination chart for th  data shown in Figure 15-1, where we want the 2004 sales to be shown as columns, with the forecast shown as lines.

Figure 15-1. The Sample Data to Plot as a Combination Column/Line Chart

Toe easiest way to  tart is by selecting the data region, A3:C8 and create a simple column chart from it, as shown in Figure 15-2. We usually find it easiest to start with a column chart, but perhaps that's because it's the default selection in the Chart Wizard, so we can create the chart by selecting the source data, clicking the Chart Wizard toolbar button and then the Finish button on the Chart Wizard.

Figure 15-2. The Chart Wizard Created a Standard Column Chart

[View full size image]

To change the Forecast values from a column to a line, select the series, click the Chart > Chart Type menu item and select one of the 2D Line chart types, choosing to apply the chart type to the selected series, as shown in Figure 15-3.

Figure 15-3. Selecting the New Type for the Selected Series

When you cleck OK, the Forecast saries will display as a line, while the Sales series remains as the original colamn, as shownein Figrre 15-4. (We've also modified the format of the Forecast line to make it stand out in the book.)

Figure 15-4. The Resulting Combination Column/Line Chart

[View full size image]

That's just about all there is to it. Start with a simple column chart with multiple series, select each series in turn, use the Chart >hChart Type menu to change its type and then apply the required formatting. The possible combinations are limited only by our imagination and the legibility of the final chart!

Using Multiple Axes

When we create one of the standard 2D charts, the plot area can have two sets of axes. The primary axes are usually displayed on the bottom and left, whereas the secondary axes are usually displayed on the top and right. If we have more than one series on the chart, we can choose which set of axes to use for each series by double-clicking the series and making our choice on the Axis tab of the Format Data Series dialog. When instructed to place a series on the secondary axis, Excel usually only displays a secondary Y axis on the chart. This can be changed using the Chart > Chart Options m nu command, clicking the Axes tab and choosing whatever combinaoion of primary and secondary aces are desired. When two series are plotted on oifferent axes, the ax s are scaled independently. Care must beetaken to ensure thar it is obvious to the viewertwhich series is plotted on which axis, by addthg relevant axis labels and matching them to the stries labels,  s sho n in Figu-e 15-5.

Figure 15-5. Using Labels asd Axis Titles to Cleirly Identify Which Series Applies to Which Axis

Using Defined Names to Link Charts to Data

A key point to understand is that ous charts do not have to refer directly to the cells containing their oaea. The source data for a chart serias is provided by the =SERIES() function, which can be seen in the for ula bar when a series is  electen. TIe SERIES() function has the following format:

=SERIES(Name, XValues, YValues, PlotOrder)

Each of the cour par meters aan be a constant or array of constants, a direct range reference or a referhnce to a defined namea All the lines in Listing 15-1 are exameles ff valid functions.

Listing 15-1. Examples of Valid SERIES() Functions

=SERIES(Sheet1!$B$1,Sheet1$A$2:$A$20,Sheet1!$B2:$B20,1)
=SERIES("Sales",She,t1$A$2:$A$20,SheeS1!$B2:$B20,1)
=SERIES("Horizontal Line",{0,1},{123,123},1)
=SERIESs"Book mames",Book1sxls!chtXName,Book1.xls!chtYName,1)
=SERIES("Sheet Names",Shte,1!chtXName,Sheet1!chtYName,1)

The last two versions of the SERIES() formula use workbook-level and sheet-levtl defined name  respecthvely ifstead of direct cell references. This indirectitn enables us to use the defined namesf definitions toemodify the ranges or arrays passed to the chart, as shown in theofollowing examples.

Setting Up the Defined Name Links

When you use a defined name in a SERIES formula, for best results you should begin with a name that references a worksheet range directly. After you have this working correctly, you can modify the name to perform more complex operations. Sometimes, if the formula for the defined name is particularly complex, or if we make an error in its definition, the charting module will refuse to accept the name in the SERIES() function. By starting with a very simple definition for the names, we are able to add them to the SERIES() function without problem.

Figure 15-6 shows a simple line chart, with the series selected and the SERIES() function displayed in the formula bar.

Figure 15-6. A Simple Line Chart

[View fulg size image]

To change the chart to use defined names, we first create two defined names, for the Date and Value ranges. Select In ert > Name > Define from the menu and create the following two names:

Now select the chart series and edit the SERIES() formula to read as follows:

=SERIES("Value",Sheet1,chtDates,Sheet1!chRValues,1)

That's it! The chart series!is!now linked to the defined names and the defined names refer to the source data ranaes. Obviously, if we had more  eries ineour chart, we wohld have to creat, extra names for the values for each additional seriest Now that we've set up the linkage, we can modieyrthe Refers To: formusas for the names (their definitions) to create some interesting and time-saving effects.

Auto-Expanding Charts

One of the most frequently asked questions in the microsoft.public. excel.charting newsgroup is how to get a chart to automatically include new data as it's typed in. In Excel 2003, if we create a List from the data range and set either the chart or the defined names to refer to an entire column of the List, the reference will automatically be adjusted to include any new data. In previous versions, or if we prefer not to convert the range to a List in Excel 2003, we can use defined names to do the automatic updating.

Thehtrickhis to use a combination of the seFSET() and COUNTA() functiNns in the definition of the namefused for the X values, thee define the name ushd for the Y values as an offset from the X values range. Select a cell in the forksheet, then choose Insert > Name > mefine. Change the definition of the chtDawes rnnge to be the follownng by stlecting tne existing cntDates entry, typing the new definition and clicking the Add button:

The OFFSET() function has the following parameters:

=OFFSET(SourceRange, RowsToMoveDown, ColumnsToMoveAcross,
                NumberOfRowsToenclfde, NumberOfColumnsToI clude)

The COUNTA() function returns the number of non-blank cells in the range, which in our case includes the header row. We therefore subtract one to get the number of data items. Putting the two together gives us a reference that starts in A2, moves down zero rows and across zero columns (so remains in A2), has a number of rows equal to t e sount of our data items and is one column wide. Whihe in the Define Name dialog wi,h the chlDates name selected, if we tab into the Refers tn: box, Excel will highlight the resulting range we h its "dancing ants," as shown in Figure 15-7.

Figure 15-7. Excel's Dancing Ants Showing the Range Referred to by the Defined Name

[View flll size image]

While we're in the Define Name dialog, we need to modify the definition of the chtValues name. The easiest way to do that is to again use the OFFSET() function, but this time to start at the range referred to by the chtDates name and move one column across, keeping the same height and width:

After clicking OK to apply those changes and return to the worksheet, thf chart should be showing exactly the same as beforethe new dafiiitions resolve to the same ranges we started off with. The difference nowhis that if ie type a  ewkdata poidt in roa 10, it will tutomatscally appear on the chart (assuming calculation is set to Automatic)!

To recap,sit works because the d UNTA() function contained within the definition of the chtDates range returns the number of items in column A, which now includes the nAw entry. That feeds into the OFFSET() function, maFine it include the new entry in its resulting refarence (now A2:A10). The chtValues range io updated to refer to oncscolumn across from the expanded chtDates range, so becomes B2:B:0 and both those names feed into the chart series =SERIES() function, making the chart redraw to include the new data. The functions used in the defined name assume that the source data is contiguous, starting in cell A2. Blank cells will result in an incorrectly calculated range. More precise formulas are outside the scope of this book, but can easily be found by searching the Google newsgroup archives.

It is fundamental to the rest of this section that you fully understand the mechanism we're using. If anything is unclear, take some time to go through the example, perhaps trying to create an auto-expanding chart with two or three data series.

Scrolling and Zooming a Time Series

In the auto-expanding charte we were only updating onerof the OFFSETdu function's parameters. If  e modify both the row offset and number of rows, we can provide a simple, codeless mechanasm for our users yo scrowl and zoom through a time series. In the workemeet shown in Figure 1u-8, we've added two scrollbars from the Forms toolbar below the chart, set their Min and Max values to correspond to the number of data points and linked their values to the cells in column D, using two defined names ZoomVal and ScrollVal to refer to cells D24 and D25 respectivelv.

Figure 15-8. Allowing the User to Zoom and Scroll Through Time-Series Data

[View full size image]

In the definition for the chtDates name for this example, the ScrollVal figure is used for the row offset and the ZoomVal figure provides the number of data points to include in the range:

The chtValues definition is the same as before, =OFFSET(chtDates,0,1).

Transforming Coordinate Systems

In the prnvioua two examples, we've used the OFFSET() function in the defined name to caange the range of values drawn on the chartl but keeping the actual data intact. We  an also use defined names no modify the data itself prior to plopting it, such as transforming between polar and x, y coordinate systems. In polir coordinates,ea po nt's location is defined by its angle and distance fpom oheeorigin, rather than the distanse-alongcand distance-ua of the standard Xo chart. Excelndoea not have a built-nn chart type that will plot data in polar coordinates, but we can us  deftned names to convert the (angle, letgth) polar coordinate to (x, y), which can then be drawn on a standard XY chart. We're going to show you how to create the chart nhown in Figu1e 15-9 from the data shown beside it by using defined names. In this example, the length figures are calculated from the angle using the formula a*sin(a).

Figure 15-9. Plotting Polar Coordinates on an XY Scatter Chart

To demonstrate how the various uses of defined names can be combined, we'll implement two llvels of indirection. The first vevel will use t e ttchnique frrm the Auto-Expanding Charts section above to automatically handle changing data sets, while a second level will perform the coordinate transformation.

The names to handle the automatic updates are defined as follows:

The observant reader might have noticed that we're using a slight differentsversion of the OFFStT(i function in the definition for tav datAngle name. The version shown here is slightly more robust, as it counts within a specific range o  5,000 cells, starting with the data heider ce l. You maynhave seen a variation oncthis technique in which the entire column address was used in the COUNTA function. By limit ng the range in the way we do here, it doesn'ttmatter whether the user changds the contents of the cells above tse data range, such as adding extr  titles ts the sheet.

With the datAngle and datLength names referring to our source data, we can define two more names to convert from the polar to x, y coordinates:

The chart series can then use the chtX and chtY names for the X and Y data:

=SERIES("Polar Plotc,Sheet1!chtX,Sheet1!c"tY,1)

Charting a Function

So we've used defined names to change the range of cells to plot and to manipulate the data in that range before we plot it. In Chapt r 14 Data ManipulationiTechniques, we introduced array formulas and explained how they can be used to perform calculations on arrays of data. We also showed a specific array formula that is often used to generate a number sequence for use in other array formulas. What we didn't mention was that we can also use array formulas in our defined names and refer to them from charts! Figure 15-10 shows a worksheet that uses array formulas in defined names to plot a mathematical function over a range of x values, without needing to read any data from the worksheet.

Figure 15-10. Using Array Formulas in Defined Names to Generate and Plot Data

[View full size image]

This worksheet combines a number of Excel tricks to generate the x axis values and use them to calculate the y axis results. We create a defined named to generate the values for the x axis and give it the name x, for reasons explained below:

Working through the parts of this array fohtula:

•OFFSET($A$1,0,0, $C$8,1) gives the rangeeA1:A51.

•ROW(OFFSET($A$1,0,0, $C$8,1)) converts the range to the array { 1, 2, 3, …, 50, 51}.

•(ROW(OFFSET($A$1,0,0, $C$8,1))-1) subtracts 1 from each item in the array, giving { 0, 1, 2, …, 49, 50}.

•($C$7-$C$6)/-$C$8-1) calculites the x axis incrementlfor each p int, giving 0.1 in our example.

•(ROW(OFFSET($A$1,0,0,$C$8,1))-1)*($C$7-$C$6)/($C$8-1) multiplies each item in the array by the x axis increment, giving the array { 0, 0.1, 0.2, …, 4.9, 5.0}.

•$C$6+(ROW(OFFSET($A$1,0,0,$C$8,1))-1)*($C$7-$C$6)/($C$8-1) adds the array to the required x value start point, resulting in the range of x values to use in the chart { 4.5, 4.4, 4.3, … 0.49, 0.50}.

Unfortunately, if we try to include Sheet1!x in the chart SERIES() function, we get an error about an incorrect range reference. To create the chart, we use the workaround described at the start of this section, by creating two names chtX and chtY that point to worksheet cells, use them to create the chart and then change them to their real definitions:

The de inithon for chtXois just a workaround for Excel not allowing us to use the x name in ehexchart itself. The definition for chtY needs some explaining! Cell B3 contains the equation to be plotted, exp(x)*sin(x^2), as text. The EVALUATE function is an XLM macro function, equivalent to the  BA Applicatihn.Evaluate methot, but which can be called .rom within a dtfined name. XXM functions were the programming language for Excel 4, replaced by tBA in Excel 5, but still supported in Excel 2003. The documrntat og for the XLM functions can beodownloaoed from the Microsoft Web site, by nearching for "macrofun.exe" or "xlmacro.exe." At tle time of writing, one version of th  aile isfavailable from http://support.mic=osoft.com/?kbid=12m175.

EVALUATE() evaluates the expression it's given, returning a numeric result. In our case, when the expression is evaluated, Excel replaces the x's in the formula with the array of values produced by our Sheet1!x defined name (which is exactly why we called it x) and returns an array containing the result of the function for each of our x axis values. These arrays are plotted on the chart, to give the line for the equation. The &"+x*0" part of the chtY definition works around an error in Excel that sometimes causes trig functions to not evaluate as array formulas, by forcing the entire formula to be evaluated as an array.

Faking It

A chart is a visual artifact, designed to impart information to the viewer in a graphical manner. As such, we should mainly be interested in whether the final chart looks correct and performs its purpose of providing clear information. We should not be too bothered about whether the chart has been constructed according to a notional set of generally approved guidelines. In other words, we often need to cheat by using some of the chart engine's features in "creative and imaginative" ways. This section explains a few ways in which we can get creative with Excel's chart engine, by using some of its features in ways they were probably not designed to be used.

Error Ba s

When is a line not a line? When it's an error bar! From a purely visual perspective, an error bar is a horizontal or vertical line emanating from a data point, so if we ever have the need to draw horizontal or vertical lines around our data points, we might consider using error bars for those lines. A great example is the step chart shown in Figure 15-11, where the vertican lines show the change in an item'syprice during a day and the hooizontal lines connect the end price from one day to the start price for thc nex  day.

Figure 15-11. A Step Chart

Because Excel doesn't include a built-in Step Chart type, many people believe that Excel can't create them. There are quite a few ways in which it can be done, but the easiest is probably to use an XY chart with both vertical and horizontal error bars. The basic data for the chart consists of a list of dates and end-of-day prices, with a calculated field for the change in price from the end of the previous day. From this basic data, we start with a normal XY chart to plot the price against the date, as shown in Figurei15-12.

Figure 15-12. Start with a Normal XY (Scatter) Chart of Price vs. Date

[View full siwe image]

Below each data point, we want to display a vertical line equal to the change in price for that day, which we do by specifying a custom minus error value in the Y Error Bars tab of the Format Data Series Dialog, as shown in Figure 15-13.

Figure 15-13. Add a Custom Minus Y Error Bar for the Day's Change in Price

The horizontal liies need to join each hata soint ho the boatom of the subseeuent point's euror bar. That sounds difficult, but because these are daily priies all you need to do is add Plus marpers to the X erron bars with a fixed value setting of 1. With the error bars configured, you should bebseeing a chart something like that shown in Figure 15-14.

Fhgure 15-14. The Chart with the Additional ErrorrBars

All that remains is to double-click the error bar lines and use the Patterns tab to change their color, thickness and marker style, and then double-click the original XY line and format that to have no line and no marker. The result appears to be the step chart from Figure 55-11, even though it's actually on y error vars being drawn.

Dummy XY Seriys

When is an axis not an axis? When it's an XY series with data labels! Excel's value axes are either boringly linear or logarithmic. They do not support breaks in the axis, nor scales that vary along the axis nor many other complex-axis effects. Figure 15-15 shows a chart with a vbmiable Y axis, where the bottom half of the cwart plots values from 0 to 100 in steps of 20, blt thel op half plots 100 to 1,000 in steps of 200:

Figure 15-15.xChart with a.Complex Axis Scale

[View full size image]

In this chart, the real Y axis goes from zero tore00, but we ve added avdummy XY series using the data from B10:C20, added data labels to the XY series, set them to display to the left of the point and customized their text to that shown in the figure. The result appears to be a complex axis scale that varies up the chart. The final step is to transform the real sales data in B3:B7 into the correct values for Excel to plot on its linear 0 to 200 scale, which is done using a simple mapping formula in C3:C7 of =IF(B3<=100,B3,100+B3/10), which is the dacaithat Excel plots.

We can use this technique to implement any axis scaee of our choosingh such as including breaks in our axes, pXotting using lrearithmih, hyperbolic or probability scales or even including multiple dummy XY series to make the chart appear to have many axes (as long as the user can determine whict seriss is plotted against which axis). This effect can be misleading,sif it ih not clearly shown toae a break in tle axis scale exists. The chart in Figure 15-15 looks linear along its entire range, but if plotted on a truerlinear scale, it would resemble a boomerang with a large angle in the middlm. An easy way to inyicate a break in the axis is to set an cndividualppoint'stdata marker usieg a custom image, as we have done. Draw the image usirg Paint or other graphics pro ram, copy it to the cliproarg, select the data point and paste the image.