Calculated Change in Excel

Excel 2007, the second application in our ongoing series reviewing the major components in Microsoft’s new Office 2007 suite, is in some ways, a radical re-making of the office productivity application used by millions around the world.

As with Word 2007, which we reviewed last month, Excel features the new Office user interface, which replaces menu and tool bars and vertical text menus with a “ribbon” and tabbed panels that stretch across the screen and graphically depict groups of functions. Also like Word, the new Excel saves data in a new space-saving XML-based file format – which, however, earlier versions of the program cannot open. And like Word, Excel 2007 isn’t quite as customizable as past versions.

New InterfaceBecause the new Office user interface is more graphical, making more features and functions visible and accessible at a glance, it may very well be, as Microsoft claims, easier to use for people who are new to the application or who use it only occasionally. But it is different enough from all past versions that devotees will need time to learn new ways of doing things, and many will be frustrated by the seemingly gratuitous changes.

Excel screen shotTable formatting in Microsoft Office Excel 2007 (Click for larger image).

Worse, advanced Excel customers will find that customizations they’ve made, especially to tool bars and menus in earlier versions, have disappeared and, in many cases, cannot be recovered. The same goes for macros, the little short-cut programs you write to perform repetitive or complex actions within an application. Any Excel macros involving aspects of the user interface that have changed will have to be rewritten or abandoned.

The new program is also generally not as customizable as past versions. You can no longer change context-sensitive (right-click) menus, for example, or add new, or remove existing, top-line ribbon items, as you could top-line menu items in past versions. One Microsoft engineer told me that some customization features may be reinstated in future releases, but there has been no official word on this.

However, Excel 2007 does offer more reasons to switch than Word 2007. Note, however, that some of the best of the new capabilities are not available to people who buy the low-end Office Small Business 2007 package. You also need Microsoft SharePoint 2007 with Excel Services for some of the advanced features to work.

Here are some of the areas where Microsoft has made improvements:

  • New easier-to-use charting module (which extends across the Office suite)
  • Increased spreadsheet size
  • Improved pivot table functionality
  • New conditional formatting features
  • Better integration with Microsoft Office SharePoint Server 2007 through Excel Services
  • New cube functions and support for Microsoft SQL Server 2005 Analysis Services

Bigger and PrettierThe increase in the size of spreadsheets you can build in Excel 2007 – up to one million rows by 16,000 columns – is likely not one that will excite a lot of small businesses. What small company needs spreadsheets that big?

The claimed improvements in charting mainly have to do with the way the new user interface presents options, but there are also some new options, mainly color schemes. In Excel 2003, you first select the data in the spreadsheet that you want to turn into a chart and then click an icon on the main tool bar to launch a sometimes confusing and overly-detailed multi-step wizard. At each of the small tabbed panels in the wizard, you select options from pull-down lists or click check boxes or radio buttons.

With Excel 2007, you select the data in the spreadsheet and click the Insert tab on the ribbon. One section of the Insert panel shows the types of charts available, illustrated with large colorful icons – column, line, bar, pie, area, scatter. Clicking an icon – for bar charts, say – drops down a panel with icons illustrating visual design options: 2D, 3D, cones, pyramids and so on. Click the one you like and Excel creates the chart. A new Design tab now appears on the ribbon showing color options. Select one and you’re done.

Fewer steps, less complexity and more design options. That said, the more detailed options from the old chart wizard are still accessible.

You can also make the work sheets attractive in ways you couldn’t with Excel 2003. Click the Formatting tab and choose a theme, which is visually displayed. It determines shading of columns and headings and the font and text color used. As you mouse over icons for different themes, you’ll see in real time how they would look in the work sheet.

In keeping with the increased emphasis on sharing and publishing Excel data, Microsoft has also added a Page Layout view (selectable in the View panel) that shows you how the sheet will look when printed. It alos lets you add headers and footers and make layout changes that you can see in real time. Switching page orientation, for example, or increasing or decreasing margins to fit data on a page are immediately reflected in the main display.

Easier SharingAs with Word 2007, you can now save Excel files in XML Paper Specification (XPS) or Portable Document File (PDF) format right within the program, something that used to require a plugin. But the most important way that Excel 2007 makes it easier to share spreadsheet information is through new integration with Office SharePoint 2007.

You can now render an Excel spreadsheet as HTML, retaining all the Excel formatting, and publish it to an Office SharePoint server URL. It will be accessible to anyone with a browser and the authority to view it. The best part: the published page links to the original Excel spreadsheet. If you change the spreadsheet, Excel Services automatically updates the page.

Office SharePoint 2007 is Microsoft’s content management, collaboration and intranet server product. You’ll also need the Enterprise CAL (Client Access License), which bundles licenses for a variety of interrelated Microsoft software products.

The bad news: this set of capabilities is only available with the Office Professional Plus, Ultimate and Enterprise packages or the stand-alone version of Excel, and not with the Standard, Home & Student or Small Business versions of Office 2007.

Excel screen shotExcel Business Bar Chart (Click for larger image).

The mechanisms within Excel for publishing spreadsheet information are fairly simple. From the big Office button in the top left corner of the screen, you choose the Publish option. But the process cannot be initiated single-handedly by a workbook author. It requires planning to ensure data security and access rules and coordination with a SharePoint administrator – which, of course, could be the same person as the workbook author in a small business.

It’s also possible – but, again, only if you have SharePoint and an advanced version of Office and have configured Excel Services within SharePoint – to publish a reporting dashboard with a summary of multiple reports. Dashboards, often a feature of corporate business intelligence programs, can include charts and pivot tables and are designed to show the status of your business, or an area of your business at a glance.

Better AnalysisSome of the graphical enhancements already touched on go further than just aesthetics. New conditional formatting, for example, lets you set filters in a spreadsheet that will add special highlighting to a data item if it meets criteria given in the condition. You could set a condition to highlight regional sales figures that fall a certain percentage below national averages in pink, for example. Conditional formatting lets you see trends at a glance and alerts you to exceptions in the data.

It’s also now easier to create pivot tables – the summary tables you can build from a worksheet by selecting and combining fields in new ways – and pivot charts. Pivot tables are a powerful analytical tool which some surveys suggest fewer than 50 percent of Excel customers use – possibly because the functionality is not particularly well documented. Excel 2007 makes it easier to create them by letting you reorient data quickly. You can now drag the fields to where you want them to display.

Bottom LineExcel 2007 shares the same arguably improved user interface as Word 2007 and the other major Office components. Long-time customers may not see much improvement, though, and will be annoyed by having to learn new routines. They’ll also be disappointed by the loss of customizability.

The new version offers some useful new improvements, though some of the best are only available when you integrate Excel with other Microsoft products. Before you migrate, consider carefully whether the improvements available to you will actually help your company.

Be sure to read our reviews on the other major applications in Microsoft Office 2007: Word, PowerPoint, and Outlook.

Based in London, Canada, Gerry Blackwell has been writing about information technology and telecommunications for a variety of print and online publications since the 1980s.

Do you have a comment or question about this article or other small business topics in general? Speak out in the Forums. Join the discussion today!

Must Read

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends, and analysis.