ExcelUser Catalog

Your Worksheet Formulas Can Change Chart Settings. Here's How...

That's right. Your worksheet formulas really can change chart settings. They can assign colors, change line widths, resize markers, format labels, and more. One Excel MVP calls it magic!

It's impossible for Excel formulas to change chart settings. That's what we're told.

But the following examples show that these add-ins give Excel formulas the ability to do the "impossible" and add significant new power to your Excel charts.

I've created a new name for those "impossible" worksheet functions-that-cause-actions: worksheet factions. You use factions in worksheet formulas just like you use ordinary functions like SUM or LOOKUP. But my factions actually do change your charts.

The following exampleswhich are included with the add-inswill give you some ideas for using this new power. The examples rely on these add-in products:

• Kyd Series gets and sets the settings for chart lines, markers, and data labels. It also includes two bonus add-ins: Kyd Colors Lite and Kyd Labels Lite.
  
• Kyd Axes gets and sets the settings for chart axes.

Example 1: Show 3D Performance in Two Dimensions

These charts show three dimensions of performance in two-dimension charts. They show Year, Sales Rank, and Sales Amount.

The position of each marker shows the year and sales rank. And the size of the marker adds the third dimension by indicating the amount of sales. From an Excel perspective, each chart marker works like a simple column or bubble chart.

I set up these charts so the amount of sales determines the height of the square markers and the area of the circle markers. I explain the formulas in the documentation.

To see how the extra dimension helps to explain the data, notice that as the rank of Hats (blue) fell from first to third, its sales actually declined. But the sales of Coats (red) increased significantly, even though its rank ended where it began.

Also notice that as you read down the column of markers for any year, each column begins with a large marker and ends with a small marker...as you would expect as you move from a high rank to a low one.

A typical Excel chart of ranked data couldn't reveal that amount of information. But factions give them the power to do so.

This example relies primarily on the KydSetMarkerSize worksheet faction. It gives spreadsheet formulas the power to calculate what size the chart markers must be to properly represent the amount of sales in each instance, and then it sets the required marker size in the chart.

To illustrate, this formula sets the size of the second round blue marker in the second chart for this example:

=KydSetMarkerSize($E$6,1,2,,19.6856796462313)

Here, cell E6 identifies the chart by specifying any cell that the chart covers. The number 1 specifies the chart's first series. The number 2 specifies the second point in that series. And the final number specifies the size of the marker, which was calculated by a separate formula. (The actual formula uses cell references throughout, of course.)
 

Example 2: Diagnostic Charts for Stock Analysis

Normal charts reveal patterns of performance. Diagnostic charts help to uncover the reasons for that performance.

One way to turn normal charts into diagnostic charts is to mark points in a chart to signal conditions that might have affected performance...in this case, stock performance.

In fact, using Excel formulas to find patterns and to mark plots of stock performance could add a whole new dimension to stock analysis.

However, our ability to highlight stock plots is limited with Excel's standard stock charts, as shown in the first and third charts for this example.

On the other hand, the second and fourth charts look somewhat like the others; but I created them using scatter (XY) charts and factions from Kyd Series. Because these charts don't use Excel's stock chart types, worksheet factions can control the color and thickness of each line or bar for each day shown.

The key factions used for the second and fourth charts are:

• KydRGB3_RGB, which converts red, green, and blue values in the range 0-255 to the single RGB value required by KydSetSeriesLineColor.
  
• KydSetSeriesLineColor, which gives worksheet formulas the power to set the color of any line in any chart.
  
• KydSetSeriesLineWidth, which gives worksheet formulas the power to set the width of any line in any chart.

As you'll see in the following examples, I use both KydSetSeriesLineColor and KydSetSeriesLineWidth frequently with these cutting-edge charts.
 

Example 3: Use Lines and Columns as Traffic Lights

Charts make it very easy for managers to review a massive number of performance indicators quickly. Even so, managers tend to experience at least two challenges when you give them charts.

First, as they glance over reports, they must pause at each chart to determine whether an upward trend is a good result (with sales, for example) or a bad result (with costs, for example). They could scan results much more quickly if each chart made it obvious whether the current results show good or bad news.

Second, managers often want to concentrate on the really bad news first. Then, if they have time, they can pay attention to other measures of performance. But which charts show really bad news?

These traffic-light charts simplify both challenges:

• The current period shows good news in green, bad news in red, and indifferent news with no change in color. That leaves no question about whether the new trends are good or bad.
   
• When the current period shows really bad news, the chart automatically displays it using a larger segment or column. This causes the really bad result to "pop out" on a page. This pop-out effect allows your managers to instantly see every chart on a page that shows really bad news. Your spreadsheet formulas determine what "bad news" means.

(For color-blind managers, you can use different colorsorange and blue, for example, rather than red and green.) 

These traffic-light charts use several factions and functions from Kyd Series:

• KydGetSeriesPointsCount counts the number of points in the chart, which automatically tells the factions which segment (the last segment) should be modified
• KydSetSeriesFillColor controls the fill colors of the column chart.
• KydSetSeriesLineColor controls the line colors.
• KydSetSeriesLineWidth controls the widths of both the line plot and the border of the column plot.
• KydRGB3_RGB makes it easy to specify the required RGB color.

Example 4: Use Bullet Plots to Replace Gauges

Bullet charts show what most gauges do, but more clearly and in a smaller space.

The orange bars show actual performance; the black lines show targeted performance; and the sets of three blue bars show bad (dark blue), average, and good performance.

This chart relies on these functions and factions from Kyd Series and Kyd Axes:

• KydRGB3_RGB makes it easy to specify the required RGB color.
• KydSetDataLabelText manages the X-axis labels, which, in this example, actually are data labels associated with a hidden line.
• KydSetSeriesLineColor controls the line colors.
• KydSetSeriesLineWidth controls the width of the blue and orange columns and the thickness of the black horizontal line.

Example 5: Diagnostic Chart for Sales

Diagnostic charts aren't limited to stock analysis. Here, for example, worksheet factions from Kyd Series mark the holidays in a chart of daily retail sales. Two of the marks correspond with noticeable peaks in sales. But the peak in late July is unexplained. If research shows that a special promotion was held at that time, Kyd Series could mark that day with a dot of a different color, to indicate a special promotion.

Notice that one holiday showed no increase in sales. This could signal a missed opportunity to increase sales in the following year.

Notice that the X axis uses a number format that causes the day, month, and year to appear in three separate rows. A faction from Kyd Axes performs this magic. I've found no way to assign this number format using standard Excel.

This chart relies on these functions and factions...

• KydRGB3_RGB to specify the RGB value for the marker color.
• KydSetMarkerFillColor, which allows spreadsheet formulas to set the fill color of the round chart markers.
• KydSetMarkerSize, which sets the size of the red markers.
• KydSetAxisLabelNumberFormat, which gives your formujlas the ability to set the number format of labels for the chart's axes.

Example 6: New Chart Type...H Plot

One of the great benefits of using the Kyd Series and Kyd Axes add-ins is that it gives you many more options for displaying data clearly. In fact, if you need to create a new type of chart, you probably can do so.

For example, a recent news report said that people in the US are finding jobs more quickly. So I got curious and downloaded data for the Mean Duration of Unemployment from the web site of the Federal Reserve Board of St. Louis.

I could have charted the entire data series from 1967, but that would have compressed recent trends too much. I could have started the chart in recent years, but that would have hidden relevant historical data. So I invented this "H" chart.

The left orange column shows the maximum and minimum performance over the previous 30 years. The black bar shows average monthly unemployment duration during that time. The right bar and column show similar results for the past two years. And the blue line shows the two-year trend.

Notice how the first and last labels in the horizontal axis are completely different from the other labels. Factions from the Kyd Labels Lite add-in manage these labels. In fact, creating this chart would have been difficult if this add-in hadn't given me the power to label the orange bars properly.

This chart relies on two spreadsheet factions:

• KydSetSeriesLineColor, which sets the with the orange, blue, and black lines.
  
• KydSetDataLabelText, which writes text in cells as text in data labels attached to a hidden chart series set up to display those labels.

Example 7: Irregular Dates

This chart shows actual results from five surveys for a US Senate race in Washington State. Results for the Republican candidate are in red and for the Democrat candidate are in blue.

Do you see anything unusual about this chart? I see three things.

1. The dates aren't periodic. That is, they're not evenly spaced. How is it possible to label non-periodic data in Excel charts?

2. Partial gridlines connect the largest data point to each label. (I also could have used full gridlines, however.) How is it possible to display non-periodic, partial-height gridlines like these?

3. As in a previous chart, the day, month, and year labels are in three rows. How is that possible to do in Excel?

You can use worksheet factions found in Kyd Series and Kyd Axes to accomplish all three effects:

• KydSetSeriesLineColor sets the colors of both lines. (Controlling the line colors with a faction wasn't really necessary here, but it allowed me to change the colors easily.)
  
• KydRGB3_RGB calculates the RGB value for the lines.
  
• KydSetAxisScaleMinimum and KydSetAxisScaleMaximum, set the minimum and maximum values of the specified value axis, here, the Y axis.
  
  (Note: If I hadn't used these factions, Excel would have set a maximum value that was too large, and a minimum value of zero. This would have made it difficult to see the gaps between the two lines. Of course, I could have manually set the max and min values, but I probably would have needed to adjust those settings each time I added new data. On the other hand, these two functions let my spreadsheet set the Y-axis values automatically.)
  
• KydSetDataLabelText assigns the date serial numbers for the dates shown.
  
• KydSetDataLabelNumberFormat assigns the number format for the dates.

Example 8: Dual X Axes

A chart like this in the Wall Street Journal compared stock performance during the 1930s with that of today.

The WSJ chart used shades of gray. But here, the blue dates label the blue line, and the red dates label the red line.

The two sets of X-axis labels actually are data labels associated with two invisible lines plotted at the bottom of the chart. Then two KydAxis functions manage those labels:

• KydSetDataLabelText writes either the date or a null string to the label, as determined by a formula.
  
• KydSetDataLabelNumberFormat formats the dates as shown, and ignores the null strings.

This type of chart could be very useful for comparing performance after two different events. For example, you could compare the change in sales during a prior recession to the current one, or changes in certain expenses for prior and current managers, or web statistics after a major change in your web site, or whatever.

Example 9: Auto-Format the Y Axis

If you want your charts to look professional, this example illustrates a feature that will save a lot of time. The feature solves a problem that occurs when you update existing reports and analyses that use charts.

The value axis (the Y axis) in the top chart uses the General number format. That format is ugly and unprofessional, so you apply the "#,##0" number format. But when your data changes, you occasionally get a result like the middle chart. So you must manually change the format to "#,##0.0", as in as in the third chart.

But since maintaining the Y axis manually takes time and effort, most users just add an extra zero or two to the Y axis. This works, but clutters the axis with unneeded zeros.

The real problem is that standard Excel doesn't offer automatic number formatting. But that's what a Kyd Axes worksheet faction offers, as shown in the third chart.

The formula for the third chart is:

=KydSetAxisTickLabelAutoFormat(B30,2,,"#,##0")

Here, cell B30 is any cell covered by the chart. The number 2 refers to the Y axis. And "#,##0" is the seed format. The faction adds as many decimal positions to this format as needed to display the specified axis correctly. Here, for example, it needed to add only one decimal position.

If you often update existing charts with new data, and if you care about the appearance of your charts, you'll use this faction a lot.

In Summary...

Kyd Series includes:

• 13 worksheet factions that change settings for Excel chart series, fill color, line color, line transparency, and line width.
   
• 14 worksheet functions that return information about series or colors in Excel  charts, including .
    
• 24 sample workbooks with more than 100 charts showing examples of how to use this add-in. All figures on this page are included in the examples.
   
• A 110-page manual that documents all 27 functions and factions, and that explains how to install the add-in.

Kyd Axes gives your worksheet formulas the power to control chart axes in Excel 2007 and 2010. It includes:

• 8 worksheet factions that change settings for Excel chart axes including value-axis number formatting, maximum and minimum scale settings, tick-mark and tick-label spacing, and more.
  
• 10 worksheet functions that return information about axes in Excel charts.
   
• 9 sample workbooks with more than 60 charts showing examples of how to use the Kyd Axes add-in.
   
• A 60-page manual that documents all 18 functions and factions, and that explains how to install the add-in.

Order Details
Here's What You Get with Kyd Series: The KydSeries Add-In, providing worksheet functions that read and update Excel chart-series settings. 27 Sample Workbooks that show KydSeries in action. 110-Pages of Documentation that explain how to use KydSeries in detail. Kyd Series: Only $47		Here's What You Get with Kyd Axes: The KydAxes Add-In, providing worksheet functions that read and write Excel chart-axes settings. Nine Sample Workbooks that show KydAxes in action. 58-Pages of Documentation that explain how to use KydAxes in detail. Kyd Axes: Only $37
Get Both and Save! Only $69
Excel Versions: Works with Excel 2007 and after, Windows or Mac Availability: Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time. Guarantee: One full year, unconditional. Currency: All prices are in US Dollar currency.		Licensing: Two copies: One for work plus one for home. Will Your Company Reimburse You? If you need a receipt so your company can reimburse you, no problem. You'll receive an emailed receipt within minutes of purchase. If its format doesn't meet your needs, I'll work to get you what you need. Credit Cards: