Saturday, September 30, 2006

Excel Sparkcharts

Charley Kyd from ExcelUser.com does some pretty amazing stuff with charts and dashboard reporting.

Charley writes books and newsletters and these are great resources.

Here is an excerpt fropm his latest newsletter about Sparkcharts

I've just posted an article about a new Excel add-in that every business user of Excel should take a close look at. It's an amazing product, and I want to tell you more about it here.

The product is SparkMaker Basic. It provides a selection of Excel spreadsheet functions that return sparkcharts rather than numbers.

So what's a sparkchart, and why are the functions so amazing?

The answer begins with Edward Tufte. He's Professor Emeritus of Political Science, Statistics, and Computer Science at Yale University. He's spent much of his professional life finding and explaining ways to present data more clearly.

Several years ago, Tufte invented the idea of 'sparklines', which are word-sized line charts. Then, less than two years ago, Bissantz introduced SparkMaker Pro for Microsoft Office. This product included many features and priced it at $200.

Last week, the company introduced the $60 SparkMaker Basic, which includes only Excel functions. From an Excel user's point of view, SparkMaker Basic has the best of Pro's features, for one-third of the price.

Because Bissantz includes a variety of chart types in addition to lines, their product generates sparkcharts, not merely sparklines.

For many people in business, however, a sparkchart is an idea that makes no sense at all. Many people in business create charts that fill a computer screen, or a printed page. What possible use is a chart about the size of one of the words in this sentence?

When you think about it, however, you probably could use word-sized charts in most of your Excel reports:

In addition to showing Month and YTD results, you could include tiny charts that show how each line item has trended over the past year.

When you have any data in rows or columns, you could use sparkcharts to quickly display patterns in those numbers.

You could show the relative measures of performance for each item in a table in a report.

You could set up a traffic light or an exception indicator that helps to explain why the exception has appeared.

Etc.
Considering the little amount of spreadsheet real estate they take up, the little effort they require to learn and use, and their low cost, sparkcharts offer an impressive return on investment.


Right now, the primary product is the Excel dashboard book. But he has several more books in the works. You can learn more about the dashboard book on his site.


Excel and Sharepoint

I have been working a lot with Sharepoint lately. Sharepoint is Microsoft's intranet application. It is a content management system, though compared with some of the open source ones (like Drupal) there is a lot to be desired from that perspective. However it does integrate well with most of the Microsoft products as a team collaboration tool. OneNote, Visio, Word and Powerpoint all work well with it.
I have attached a Word doc - from Microsoft - which is a Sharepoint guide.SharePointGuide.doc

However I have been trying to help some customers shift their network file structures into Sharepoint for better team collaboration, searchability and content management. What I have discovered is that Excel files that have been linked cannot be used in Sharepoint. Here is what some others have to say about that.

Joel Oleson
Google Excel Group discussion


I have found some limited use for reports and other display / basic editing of files. And certainly from a repository perspective Sharepoint is fine with Excel files.

But dont try and link files and use SharePoint!


Monday, September 11, 2006

XML and Office 12

There's a lot starting to be written about the new Office 12 or Office 2007 and the Open XML Formats for file structures.
Basically there is a completely different approach to file types.
(For the technical minded they have changed from a binary to an XML format.)

For everybody else think of a Zip File - now think of that as a container and inside that container are a number of other files - each one is an element of the main file. There is formatting, header, authoring information etc. There is the text and data, there are images and embedded objects. Each of these can be manipulated and edited from outside the application it is normally viewed in.

So think of opening the text element from a word doc in a text pad, editing it, and then reopening the word doc with all your formatting applied and the text edits are all there. Very handy if you need to use a computer without Office 2007 on it.

Think about editing the header information and changing your company details or logo. Then think of applying these changes to all the word docs on your system - without having to open one of them! Thats the sort of benefits you can expect to see from this new format.

Of course the application can create and edit backward compatabile versions, and can even be set to default to those versions.
Viewers will be available from Microsoft for free that will enable those without Office 2007 to open and view documents.

For a primer in starting to read about how the Open XML format works start with these web resources
Brian Jones Blog
MSDN Articles on Open XML and Related Topics
MSDN - Introducing the Office (2007) Open XML File Formats