These Unique Excel Add-Ins Do the 'Impossible'
To Deliver Extraordinary Power to Excel Charts

But the following examples show that my new add-ins do the "impossible" to deliver significant new power for Excel charts.

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

Let me show you what I mean...

The first set of figures below shows how worksheet factions turn chart markers into simple column charts. And the second set shows how factions support traffic-light charts.

These examples rely on two new add-in products: Kyd Series and Kyd Axes. The Kyd Series product also includes two bonus add-ins: Kyd Colors Lite and Kyd Labels Lite.

You can see how the factions work in this video and in the information below...

Display 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 showing 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. A typical chart of ranked data wouldn't reveal that sales information.

My Excel formulas used a worksheet faction from Kyd Series to set the size of these markers. As the data changes, so do the markers.

Here's an idea...After you set up this chart, show a copy of it to your friends who use Excel. Tell them that you didn't use any macros to create this chart and that you didn't size the markers manually. Then ask them if they could create a similar chart.

But after you ask the question, don't wait too long before you admit that you "cheated" by using a Kyd add-in.
 

Use Traffic-Light Charts to Delight Managers

Your managers will love traffic-light charts. Here's why...

Managers tend to experience at least two challenges with 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 is 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 look at other measures of performance. But which charts show really bad news?

These traffic-light charts simplify both challenges. 

First, the current period shows good news in green, bad news in red, and indifferent news with no change in color.

Second, when the current period shows really bad news, the chart automatically displays it using a larger segment. This causes the 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 thicker lines and different colors -- orange and blue, for example, rather than red and green.) 

Using traffic-light charts requires only three worksheet factions from the Kyd Series add-in: One worksheet faction controls the line color, another controls the line width, and for column or bar charts, another controls the area color.

Also, you easily can specify any color value you want using a function from the Kyd Colors Lite add-in, which is included as a bonus with the Kyd Series product.
 

Use Flexible Bullet Charts 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, average, and good performance.

This chart relies on functions and factions from both the Kyd Series and Kyd Axes add-ins.

Diagnose Performance by Marking Chart Points

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

The way to turn normal charts into diagnostic charts is to mark points in a chart to signal conditions that might have affected performance.

Here, for example, worksheet factions from Kyd Series mark the holidays in a chart of daily retail sales. At least three of the marks correspond with noticeable peaks in sales. But this diagnosis shows that one peak is unexplained, and needs further research.

Also, two holidays show no increase in sales. This could signal missed opportunities.

Notice that the horizontal 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.

By the way, you can use several markers per point to signal performance. As this enlarged example shows, you can overlap several markers for each point. (Each marker is attached to an overlapping line.) For each marker, your factions can control the size, color, and visibility of both lines and fills.
 

Diagnostic Charts Enhance Technical Stock Analysis

Diagnostic charts also can help to diagnose stock performance.

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

However, our ability to mark stock plots is more limited with Excel's standard stock charts. This is because Excel doesn't allow us to change the size or color of individual chart elements. So we can only use markers.

For example, the two charts on the left use Excel's standard stock chart types. The red dots are markers that are controlled by worksheet formulas that use a faction found in Kyd Series.

The two charts on the right look somewhat like the ones on the left; but I created them using scatter charts and factions from Kyd Series. But because these charts don't use Excel's standard stock chart types, worksheet factions can control the color and thickness of each line or bar for each day shown. Factions also could display markers, of course.
 

Create New Chart Types to Explain Data Clearly

One of the great benefits of owning 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 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 Federal Reserve Board's web site.

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 bar shows the maximum and minimum performance over the previous 30 years. The black bar shows average monthly performance during that time. The right bar shows similar results for the past two years. And the blue line shows the two-year trend.

Worksheet factions found in the Kyd Series add-in control the chart elements.

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 impossible if this add-in hadn't given me the power to label the orange bars properly. 
 

Chart Your Non-Periodic Data Clearly

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 could also have used full gridlines.) How is it possible to display  non-periodic 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.
 

Compare Performance Over Two Time Periods

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.

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.

Here's another way you can stump your friends who use Excel. What worksheet technique could they use to create a dual-axis chart like this?

The answer, of course, is to use the Kyd Labels Lite add-in, which is included with the Kyd Series product.
 

Set Chart Number Formatting Automatically

If you want your charts to look professional, this cluttered 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 Figure 1 uses the General number format. That format is ugly and unprofessional, so you apply the "#,##0.0" number format. But when your data changes you occasionally have a result like Figure 2. So you must manually change the format to "#,##0.00", as in Figure 3.

But then your data changes again, as in Figure 4. So now you have extra zeros that clutter up your chart. So you set "#,##0". But when your data changes again, you could have a problem as in Figure 5. So now you must set the format back to "#,##0.0", as in Figure 6.

The real problem is that standard Excel doesn't offer automatic number formatting. But that's what a Kyd Axes worksheet faction offers.

Figures 7, 8, and 9 show the results that the faction can produce. Figure 7 started with "#,##0" and automatically changed the format to "#,##0.0000" as demanded by the data. Figure 8 started and ended with "$#,##0". And Figure 9 started with "0%" and ended with the number format "0.00%", because that's what the data required.

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

What New Charting Solutions Can You Create?

These examples offer only a hint of the power that Kyd Series and Kyd Axes offer. You're limited only by your imagination.

As you find new ways to explain your data more clearly with these add-ins, please send me examples that I can share with other Excel users.
 

?

I sent both add-ins to fellow Excel MVP Ken Puls and asked for his opinion. Here are some of his comments over several messages:

..."This is great work, Charley, seriously." 

..."I was playing with your add-ins at work today, and making use of the Kyd Series add-in.  Again, some really nice work" 

..."I demoed this to one of my employees, who was dumbfounded.  Actually, it was pretty funny.  He asked me "how'd you do that?".  Naturally I told him it was magic.  His response was "well I KNOW that.  What KIND of magic?"  He was really impressed too." 

..."I'm going to keep working with this... it's very cool!"

In short, Ken liked the add-ins so much that he signed up as an affiliate. Thanks, Ken!