Microsoft Excel 2007 for Performance and Capacity Management

This article reviews Microsoft Excel 2007 Beta 2, explaining how the key changes will impact performance and capacity management professionals.

Excel 2007 is part of the 2007 Microsoft Office system, formerly known as Office 12. Office 2007 is planned for consumer release in early 2007.

For general information, or to download Office 2007 Beta 2, go to the 2007 Microsoft Office system preview site.

Key Changes

The following key changes are likely to be of most interest to performance and capacity management professionals.

User interface redesign, with menus and toolbars being replaced by Ribbons
Number of columns per worksheet increased
Number of rows per worksheet increased
Pivot Tables
Improved scalabililty
New file format

Ribbons

Office 2007 has a new graphical user interface, incorporating Ribbons, which replace the former menus, toolbars and many task panes.

Any custom menu items that you may have written using VBA will not appear on the Command Bar, but will still be available under the 'Add-Ins' ribbon, as shown below:

Figure 1. Office 2003 - custom menu items appear under the command bar

Figure 2. Office 2007 - custom menu items appear under the 'Add-In' ribbon

In addition, users with a knowledge of XML may write custom ribbons as shown below.

Figure 3. Office 2007 - custom ribbon

Columns per worksheet

One common problem when using is the limit on the number of columns per worksheet. This can present problems when using Excel to manipulate performance monitoring data. For example, when importing Windows System Monitor data in .csv format into Excel, a maximum of 255 counters can be imported.

In Excel 2007, the number of columns per worksheet has been increased 64-fold from 256 to 16,384, effectively solving this problem.

Rows per worksheet

Another limitation of Excel is the constraint on the number of rows per worksheet. This effectively limits the amount of data that may be easily imported and analysed. For example, imagine Excel is being used to analyse a log file that contains a record for each second of the day. Excel is unable to import more that 19 hours' data. Previously it has been necessary to split large log files into seperate files prior to importing.

In Excel 2007, the number of rows per worksheet has been increased 16-fold from 65,536 to 1,048,576. Hence in our example, Excel 2007 can now be used to import more than 12 days' data.

Pivot Tables

Pivot tables are now able to summarise and aggregate data from these larger worksheets. The maximum size of a pivot table remains limited by the size of a worksheet.

Improved scalabililty

Given that Excel 2007 can support larger worksheets, it is important to note that Excel 2007 contains support for dual-processors and multi-threaded chipsets, and can make use of all of the memory available to windows (rather than being limited to 1 GB).

New file format

Microsoft has added new zipped XML file formats to reduce file sizes, improve the recovery of corrupt or damaged files, and improve integration with external sources:

xlsb - Microsoft Office Excel 2007 binary workbook
xlsx - Microsoft Office Excel 2007 workbook (default)
xlsm - Microsoft Office Excel 2007 macro-enabled workbook
xltx - Microsoft Office Excel 2007 template
xltm - Microsoft Office Excel 2007 macro-enabled template
xlam - Microsoft Office Excel 2007 add-in

When opening .xls files created using Excel, Excel 2007 runs in 'Compatability Mode'. Note that complex models produced by Capacitas using Excel 2003 required recoding of the VBA before they would work under Excel 2007.

Capacitas offers two training courses describing how to use Excel to support effective performance and capacity management:

Access to Capacitas articles is unrestricted although research is restricted to registered users of this website; registration is free and available to all. Click here to sign up now. Subscribers will be informed via email when new research is published.