Microsoft excels

Intheblack, Mar 2007 by Blackwood, Neale

EXPERIENCED USERS WILL WELCOME THE EXPANDED FUNCTIONALITY OF EXCEL 2007 -AND THE LOSS OF THAT PESKY PAPERCLIP

Microsoft has finally supplied a major upgrade to Excel, its flagship spreadsheet package. The last two versions of Excel - XP and 2003 -made very few major upgrades to Excel 2000. Excel 2007 las more rows and columns, improved interaction with databases, more formatting options, increased customisation of the screen layout and has a brand new user interface callod the "Ribbon".

The Ribbon

The Ribbon interface is used throughout most of the Office 2007 suite and replaces the current menu and toolbar interface. The Ribbon is a set of tabs across tho top of the screen that contain traditional toolbar icons, via which most options are now accessed. The toolbar icons are arranged in logical groups, based on tasks performed. The philosophy behind the Ribbon is to reduce the number of mouse clicks required to access options. The Home Ribbon contains the most frequently used options ( see Figure 1).

The Tooltip feature, the description you see when you hover the mouse over a screen item, now displays more information to help the user select the correct icon ( see Figure 2).

Ribbon tabs can vary depending on the task at hand. Many of the items in the Insert tab have specific tabs that are displayed once an item is inserted or if you are working on that item.

There are three issues with the Ribbon that are worth emphasising:

1. There is no option to revert back to the old menu and toolbar system

2. You can't customise Ribbons

3. A Quick Access Toolbar is customisable with icons you use frequently, and which will be constantly visible.

That said, the Ribbon is an improvement. It is easier to do most things once you are used to where the icons are and has the added benefit of displaying options that were previously hidden away in the menu system, which meant users were unaware of them.

Any options that aren't on a Ribbon will be included on the Office icon (see the top left corner of Figure 1). This has many options that were in the File menu and also has Excel 2007's version of the Tools > Options series of tabs, now called "Excel Options".

Improved features

In terms of new features and improvements that will make an accountant's life easier, here are some of the more important changes:

* 1,048,576 rows and 16,384 columns - this allows more data to be analysed. This may seem like overkill, but remember: it took over 10 years to get any extra rows or columns in Excel, so this may have to last for a few years. Many users have used Microsoft Access to analyse large amounts of data because Excel could only handle 65,536 rows. Much of this analysis can now be done in Excel. (Note: Excel is not a relational database - Access should be used for any relational database work)

* Improved conditional formatting that is easier to use and is not limited to three criteria. You can use more symbols and colours, which is useful for KPI, exception and one-page reporting. Many conditions are now built in, and require no programming, such as formatting duplicated items. You can also preview the format before applying it (see Figure 3)

* Pivot tables and charts are easier to use. (Pivot tables are a badly named but highly powerful and under-used feature of Excel - they allow you to easily create reports based on huge amounts of data from Excel or external database sources.)

* A new name manager allows you to handle range names more easily and you can now add comments to names to improve documentation

* Sorting is no longer limited to three criteria. There are now up to 64 levels in the sort. You can sort by the cell fill or font colour

* Chart formats are more professional and have additional colours and effects. More chart options are available, but they seem like overkill and allow too much flexibility. You could spend a lot of time trying to get the chart format perfect. You can save the format as a template to allow for future use (see Figure 4)

* You can resize the name box and the formula bar so that longer range names and formulae are easier to view (see Figure 1)

* Files can be saved in PDF format, but note that you can't read PDF files in Excel 2007 and will have to download the free Adobe reader, if you don't already have it

* Improved tools to access large, multidimensional databases, with seven new functions for extracting data from data cubes.

Other general changes that are worth noting:

* Right-click menus contain more options

* Customisation of the status bar is now possible and it contains a number of shortcuts, such as zoom percentage and icons for the different page and print views

* A new print layout displays the spreadsheet on pages that have margins, similar to Word's print preview

* Display of the header and footer is improved, though the contents have changed very little, and now look like a Word header and footer

* Excel now remembers a greater number of recently opened files (up from nine to 50)

* The linking and embedding of Excel charts and tables in Word and PowerPoint has improved

* There is no annoying Office Assistant - the paperclip is gone!

Compatibility issues

Obviously, many of the changes mentioned will cause problems when you have staff working on Excel 2007 together with previous versions.