These Unique Excel Add-Ins Do the
To Deliver Extraordinary Power
to Excel Charts
These Excel add-ins allow worksheet formulas to change chart settings.
For the first time ever, your formulas can create traffic-light charts, highlight chart elements,
number formats, and much more.
by Charley Kyd
Microsoft Excel MVP
It's impossible for
Excel formulas to change chart settings. That's what we're told.
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
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
Display 3D Performance in Two
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
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
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
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.
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
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.
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
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
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
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
Chart Your Non-Periodic Data
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
2. Partial gridlines connect the largest
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
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
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
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
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
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.
|Comments from another Excel MVP...
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,
..."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
..."I'm going to keep working with
this... it's very cool!"
short, Ken liked the add-ins so much that he signed up
as an affiliate. Thanks, Ken!
| The Kyd Series Add-In
— for Excel 2007 & 2010
add-in gives your worksheet formulas the power to change chart
series in Excel 2007 and 2010.
With this add-in, your worksheet formulas can change
chart types, set colors, change widths, adjust
transparencies, assign marker types, change the size of
markers, hide or reveal data points or markers, and so
A bonus add-in allows your formulas to write numbers
or text to data labels, and assign the label's number
- 11 worksheet functions (called "factions") that
change settings for Excel chart series.
- 12 worksheet functions that return information about
series in Excel charts.
- 2 worksheet functions provided by Kyd Colors Lite,
a bonus add-in. The functions translate between a single RGB value and the red, green, and blue component values.
- 2 worksheet functions (called "factions") provided by Kyd Labels Lite,
add-in. One faction writes data labels to points in a series
and the other faction assigns a number format to those labels.
- 24 sample workbooks with more than 100 charts showing
examples of how to use the Kyd Series, Kyd Colors Lite,
and Kyd Labels Lite add-ins. 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.
Only: $67.00 US
| The Kyd Axes Add-In
— for Excel 2007 & 2010
add-in gives your worksheet formulas the power to
control chart axes in Excel 2007 and 2010.
add-in, your worksheet formulas can set the maximum and
minimum values for your chart axes, change tick-label
spacing, set the number format of tick labels, set the
number of units between tick marks, and more.
This add-in also gives your worksheet formulas the
power to automatically change number formats so your
chart always displays the correct number of decimal
This add-in includes:
- 10 worksheet functions that return information about
axes in Excel charts.
- 8 worksheet functions (called "factions")
that change settings for Excel chart axes.
- 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.
Only: $37.00 US
I'm so sure of your success that I guarantee all purchases for one full year. If you're not completely satisfied with
one of my products, just send me a personal email any time within a year of purchase, and
I'll issue a full refund.
Free Product Updates
||Updates to the add-ins are free for
one full year.
||You get two licenses for the price of
one. Our license gives you permission to use
one copy of each purchase for your company and
one copy for personal use.
||My shopping cart sends a receipt
automatically. If your company needs a different format
to reimburse you for your purchase, I'll work to get you
what you need.
Site licenses are available. Please
contact me for details.
You can choose from
these methods of payment: Visa, MasterCard, American
Express, Discover, and PayPal.
After you purchase
the add-in, you'll receive a download link for a zip
file. To open the file, You can access it directly using
Windows Vista or Windows 7. Or you can use a program
download a free copy here.
The documentation and license files use a PDF file format. If
you don't already have Adobe Acrobat PDF Reader, you can
download a FREE copy here. You'll probably use it a lot.
|Version of Excel
||These add-ins work only with Excel 2007
and 2010. I plan to offer a version for Excel 2003 and
earlier by the Spring of 2011.
Because Excel 2008 doesn't use VBA, these add-ins
don't work on the Mac.
||I respect your email privacy. I'll never
sell, lend, or give away your email address to any other
|Secure Purchase Process
I have partnered with Authorize.net, a leading payment gateway, to accept credit cards and electronic check payments safely and securely for our customers.
The company adheres to strict industry standards for
payment processing, including 128-bit Secure Sockets Layer (SSL)
technology for secure Internet Protocol (IP)
transactions, industry-leading encryption hardware and
software methods, and security protocols to protect
customer information, and compliance with the Payment Card Industry Data Security Standard (PCI DSS).
Copyright © 2004 - 2012 by Charles W. Kyd, all rights
reserved. Content, graphics, and HTML code are protected by
US and International Copyright Laws, and may not be copied,
reprinted, published, translated, hosted,
or otherwise distributed by any means without explicit permission.