Analytical Charts
This Excel Analytical Charting Strategy Reveals an Economic
Warning
You can uncover new insights about your company by transforming
your data before you chart it. This method of data
transformation has revealed a longterm weakness in the U.S.
economy.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

With normal charts, you find data and then you plot it. But with
analytical charts, you transform your data in various ways to
discover hidden insights when you plot it.
For example, plotting the
Rate of Change (ROC) of your data can reveal underlying
strength or weakness in your company, information that your original data hides. This chart
is a case in point:
This unusual transformation of the data reveals a weakness in the US
economy, a weakness that I've never seen discussed before...and I read articles
about business and the economy every day.
Unlike typical ROC charts, this chart plots the tenyear,
annualized ROC. Its underlying data is the real (that is,
inflationadjusted) GDP in the U.S. As you can see, the tenyear
growth rate of the U.S. GDP has fallen in two stairsteps over
the past 50 years. And particularly, that growth rate has plummeted since the Great Recession.
So that I could be as productive as possible when I created
this chart, I use the
ExcelFriendly Database Strategy, which consists of four
steps:
 You stow your data in an ExcelFriendly
Database, This is a database to which you can link your
worksheet formulas. In this case, I used Excel Tables as my
database.
 You flow your data to an Excel template
that's set up as a springboard for quick and easy
development.
 You show your data in your reports and
analyses.
 You grow your Excel system by
continually improving it.
Several months ago, I set up an Excel template that uses
Power Query to download data from the Federal Reserve Economic
Database (FRED). Therefore, to create this chart, I first
entered the two series IDs I needed into a Table and downloaded the data in less than five seconds
to my data Table. And because the template was linkedenabled,
my key formulas already were set up to return data to my report,
which, in this case, was a single chart.
The note at the bottom of the chart tells you the two FRED
data series I used, which you can see at these URLs:
https://fred.stlouisfed.org/series/USARECM
https://fred.stlouisfed.org/series/GDPC1
Notice the pattern of the URLs here. Links to all of FRED's
500,000+ data series add the series IDs as the last section of
the URL.
The first series contains 1 for each quarter that was in
recession, and zero otherwise. The second series shows real GDP
in billions of 2009 Dollars.
Once this data was in my Table, I could work with it easily.
The Staging Table
With my data easily available, I set up a staging Table to
contain just the data I wanted to chart, which was from 1965
forward. The first column contained the dates for each quarter
beginning in Jan1965.
Another column in the staging Table used a SUMIFS formula to
return the GDP value for each date. Another column used SUMIFS
and EDATE to return the GDP value ten years earlier than each
date in the Table.
A third column calculated the annualized 10year growth rate
for each date. That is, it divided the current value in its row
by its tenyear value to find one plus the growth percentage over
ten years. Then it took that tenyear growth number to the onetenth
power, and subtracted one, to find the annualized growth rate
for that period over then years.
To illustrate, the last point in the chart is for
OctoberDecember, 2017, and has the value 17,271.702. The
equivalent value from 2007 is 14,991.784.
Dividing the first number by the second gives us
approximately 1.152...which equals one plus the 15.2% growth
amount over that tenyear period. To find the annual growth rate
over those ten years, I took 1.152 to the onetenth power, and
then subtracted one. And that gave me the value 1.4%, which is
plotted in the chart.
As a formula, this calculation is:
(17271.702/14991.784)^(1/10)1 = 1.4%
In contrast, the first point on the chart represents a growth
amount of 44.3% over ten years. Taking 1.443 to the onetenth
power, then subtracting one, gives us an annualized growth rate
of 3.7% over those 10 years.
Digging Into the Chart
Here's the chart again:
The blue line shows series 1, which is a plot of the
annualized growth rates. To add the dotted trendline, I
first selected series 1. Then, in the Chart Tools,
Design, Chart Layouts group, I chose Add Chart
Element, Trendline.
Series 2 indicates recessions, using the technique I
described in
How to
Show Recessions in Excel Charts.
Series 3 uses Excel's new charting feature that I described
in
Two Business Uses for Excel's New Chart Feature.
If you don't have that feature in your version of Excel, you
could set up a Table that plots each of the three lines in a
different series. But doing so requires clever formulas and
dynamic range names, which I won't go into here.
Finally, the three red labels are returned by data labels
attached to specific points in the red line. The faint red lines
above each label are leader lines, which you have the ability to
include when you set up your data labels.
Later this month, I'll introduce a training series that I've
been working on for four years. It will teach all the elements
that went into this chart, and more. The training will
include...
 How to set up the foundation of the ExcelFriendly
Database Strategy, from raw data to an interactive standard
report.
 How to add dashboard features—primarily charts—to a
standard report.
 How to use Excel Tables as an ExcelFriendly Database.
 Why and how to use dynamic range names with Excel
Tables.
 Why and how to use the halfamillion free data series
that FRED offers.
 How to use Power Query to for a variety of purposes,
including downloading web data.
 Several classes about Excel charts, include a
deepdive into the GDP chart.
 And a LOT more.
If you're an ExcelUser subscriber, watch for announcements in my Weekly
Strategies newsletter. If you're not a subscriber, you can
subscribe here.
