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 DB.

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 minutes.

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.
 

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 Dashboard Reporting 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 below.)

I'll write more about Excel 2010 in the months ahead.
 

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 control.

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 certain days.
   
• Etc.

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 workbook here.

(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.)

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 dashboard reports.

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 2010.
 

Soon after Excel 2007 was first introduced I posted a poll that 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 results.

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.

More later,

Charley

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.