|
Home > Excel Dashboards >
Free Self-Updating Excel
Report About US Economy
This Context-Chart Report is a mini-dashboard that illustrates
cutting-edge
Excel
charting techniques. You get it for free when
you subscribe to
my
free "Excel For Business" newsletter.
by Charley Kyd
Excel MVP
 A
while back, I watched some US politicians blaming each other for our
bad economy. As with politicians everywhere, it was the other guy's
fault.
So I got curious.
First I charted monthly data about the Dow Jones Industrial Average.
Then I showed that performance in context. In other words, I found a
way to color-code the chart's background to show
which party controlled Congress and the presidency. Then I added a
similar chart to show the US unemployment rate.
Finally, I made this two-chart mini-dashboard report self-updating. That
is, after I add new data each month, anyone connected to the Web can
update their dashboard report merely by refreshing its links, and
then recalculating.
This is cutting-edge Excel stuff! Here's why:
1. Performance in Context
Forget about US politics. And forget about the specific data that
the charts display. Instead, think in general terms about performance reporting.
Performance often has meaning only in the context of other
non-numeric information. For example:
- The trend in sales can be better understood in the context of
when specific advertising campaigns were run.
- The trend in post-surgical infections can be better understood
in the context of when a new hospital policy became effective.
- The trend in gas mileage can be better understood in the context
of which gasoline blend was available at the time.
- The trend in the number of customers in a restaurant can be
better understood in the context of the type of local events that
were held on certain days.
- Etc.
So when you look at the two charts in the figure below, think about how you would use similar
charts to put the performance of your own key measures into a clearer
context.
2.
Self-Updating Reports
You can create a report that gets its data from a workbook with data. Even
when the data workbook is closed, most worksheet functions in the
report will
continue to work without error. (The worksheet functions INDIRECT and OFFSET
are two exceptions to this rule.)
But here's the interesting thing: Even if the closed workbook is on a web server, your report
workbook can use the closed workbook's data.
That's why this report is self-updating. Each
month I'll update the data workbook and save it to Excel's web server. Then
you can update this
Context Report workbook easily, just as you could update one
workbook linked to another on your hard drive.
To receive this cutting-edge Excel report
just sign up for my free newsletter at this
link.
You can unsubscribe from the newsletter at any time, and I'll never
give your contact information to others. I promise.
Charley Kyd
|
