Published Now and Again for Business Users of Microsoft Excel.
Excel 2010 + Free Mini-Dashboard
Wednesday, July 22, 2009
If you like this newsletter, please forward it to
other Excel users.
Last year, when I sent my previous newsletter, I had great plans for some new
stuff. But then I was pulled into a project, and then another, and so on. And way too much time went by.
So let's see if I can get this newsletter started up again...
I didn't spend all my time spinning my wheels. I also
introduced a new plug-n-play product. Initially, I called it PNP#2, but I've
rebranded it as IncSight
Unlike my first plug-n-play dashboard product, this one links its reports to an
Excel database. This design offers several great advantages:
- It should take less than an hour to set up a report.
- Reports can be updated in seconds each period.
- The Excel database can contain formulas that return data from
PivotTables, text files, OLAP databases, and other sources.
You can see a video of
IncSight DB here, which shows how I set up a new dashboard in about 20
To thank you for your patience about the newsletter, I'm offering a $10
coupon for all orders of $50 or more. But the coupon will be good only for the
rest of July. So if you're thinking about ordering, now's the time.
Coupon code: July 2009
When you order IncSight
DB, enter this code in the payment form and choose Apply. After you do so,
you'll see the discount amount subtracted from your order.
Excel 2010 Is On the Way
Microsoft released the Technical Preview of Office 2010 last week. Normally, the
details about this product would be carefully protected at this point in the
testing cycle. This time, however, Microsoft has told those of us who are using
the product that it's okay to talk about it. So here goes...
Here are some key changes to Excel 2010:
1. Sparklines. The current version of the product generates
the three types
of sparklines shown here.
2. Slicer. We're told that this tool helps us to better visualize Pivot
Table views and cube functions. When Gemini is available (see below), I'll be
able to tell you more about Slicer.
3. Excel Web App. There's a new Excel Web App, which we can't talk about
yet. That's just as well, because I haven't had an opportunity to test it.
4. Conditional Formatting gives us more control over formatting. For
example, data bars now can display negative values.
5. New Spreadsheet Functions. I counted 57 new spreadsheet functions in
Excel 2010. Nearly all of these are improved versions of existing statistics
functions. However, Excel 2010 does offer international versions of NETWORKDAYS
and WORKDAY, which allow for custom weekend parameters.
6. Project Gemini is a free add-in that brings many of the features of
Analysis Services to the desktop, allowing Excel users to analyze millions of
rows of data. It will work only with Excel 2010 and above. There's a lot of
excitement about this tool, but it's not available for testing yet.
7. A 64-Bit Version of Office 2010 will be available for the first time.
8. Improved Macro Recorder. The macro recorder in Excel 2007 doesn't
record macros that work with charts. That feature has now been restored.
9. Ribbon Modifications. You'll easily be able add tabs and groups to the
Ribbon, and add commands to the groups, much as you can add commands to the QAT
in Excel 2007.
10. Camera Tool Improvements. If you've read
With Excel, you know that Excel's Camera tool probably is Excel's
most-useful hidden tool. Excel 2010 has added many features to it, improved
performance, and fixed several ugly bugs. (See more about the new Camera tool
I'll write more about Excel 2010 in the months ahead.
Free Self-Updating Chart
A while back, I got curious about how the US economy has performed under various
administrations. So I created this mini-dashboard that shows the performance of
two key economic indicators in the context of which political party was in
This is a cutting-edge workbook, for at least two reasons.
First, it's self-updating. If you open this workbook around the
10th of each month, and press F9 to recalculate Excel, the two charts will
update using data over the Web.
Second, you could use a similar technique to improve your performance reporting. This is
because performance often has meaning only in the context of 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
That is, rather than showing your performance in the context of political
parties, as I do in the figure above, you can show your company's performance in
the context of
activities that affect your business.
I recently started to offer this workbook to people who subscribe to this newsletter.
But if you're already
a subscriber and want a copy of these self-updating charts, you can download the
(A lot of people will be trying to download this workbook at the same time, so
if you have problems doing so, please wait a while and then try again.)
Camera Tools and Adaptive Reporting
The Camera tool -- also known as a "linked picture" in Excel -- returns a live
image of any range. I use the Camera tool to display tables in Excel
Unfortunately, in any version of Excel through 2007, you never should point a Camera tool at a
range that contains a chart. If you do so, and then print or print-preview your
worksheet, an Excel bug probably will reduce your chart's plot area to about the
size of the word: "Ugh!"
That bug appears to have been fixed in Excel 2010. So now, with the help of the
Camera tool, we're able to create a report figure that's been in the back of my
mind for a long time.
Suppose we name some empty cell SwitchA. I've wanted to be able to set up a
figure so that if I enter 1 in the SwitchA cell, I see a table in my report; if
I enter 2 in the cell I see a large chart; if I enter 3 in the cell I see two
small charts; and so on. That is, changing the value in the SwitchA cell
replaces one figure with another in a report. Similarly, cells named SwitchB and
SwitchC could control other figures on the same page of the report.
Further, I want to let formulas in the Switch cells determine which figure
should be displayed and then return 1, 2, 3, etc. as needed. This would let my
report automatically reconfigure itself as my report changes from Australia to the
Europe Region, or from August to Quarter 3, or from Marketing to
Operations, and so on.
The formulas in the Switch cells also could determine which of several figures
would be the most interesting for readers. For example, if the measure for
Chart1 is unchanged from the previous month but the measure for Chart2 shows
significant change, then Chart2 could be displayed automatically.
Finally, I want to make this all work without using any VBA.
I'll explain how to set this up when we get closer to the launch date for Excel
Past and Future Surveys
Soon after Excel 2007 was first introduced I posted a
asked what version(s) of Excel my visitors are using. I followed that up
with a survey that asked what my
visitors think about Excel 2007's Ribbon. It's time to end both series of
questions. Within a few days after the end of the month I'll post the final
However, I plan to conduct other surveys later this year. I hope you'll
participate in the surveys and I think you'll be interested in the results.
PS Don't forget: If you want to
save $10 on IncSight DB,
be sure to use the coupon code July 2009
before the end of this month. The coupon automatically expires on August 1.
PPS Follow me on twitter.