A much needed upgrade to Excel

What is a spreadsheet?

In the old days when accountants used to do most work by hand, they used cash books and ledgers to list items in a ‘sheet’ format, where columns tended to have headings, and rows tended to have data.

Back in the old days, Lotus introduced a new fangled computer program that took a lot of the work out of producing ‘books’ by allowing the user to use some formulas and functions to replicate the good old ‘paper’ books.

As the years have advanced, and Microsoft took over from Lotus as the purveyor of spreadsheets, the good old spreadsheet has adapted and changed.

Microsoft revolutionized the spreadsheet, adding far more functionality.
Put simply a spreadsheet is a sheet divided into rows and columns. Each cell within the spreadsheet can have many purposed. It can be a ‘header’ with some text in; a data cell with sum ‘data’ in it, or a formula cell with a ‘formula’ in it.

The formulas are what give the spreadsheet its power. Ranging form simple arithmetic formulas (including Sum – which adds a specified range of cells together), to more advanced formulas such as ‘financial’ formulas. (The range of formulas is split into Groups – ‘Sum’ formulas, Logical formulas, Lookup and Reference formulas, Text formulas, Math & Trig formulas, Financial formulas, Date and Time formulas.).

The variety and complexity of the formulas is vast and allow for users to design a multitude of different worksheets (Some great example: Amortization Schedules; Budget Forecasts; Profit and Loss accounts; weight loss table; golf score average etc.). The uses a spreadsheet has are immense, generally if you can think of it, then you can probably do it. I even designed a rudimentary space invaders game, and my own personal cricket batting average spreadsheet – this had errors because it gave me a very low average *grin*

Excel also gives you many ways in which you can format and manipulate the data. There are some pretty advanced database functions (although I advise you use MS Access for true database functions), some neat Pivot Tabels that allow for some powerful summarization of the data, and a whole bunch of formatting options.

In addition there is a versatile graphing program that allows you to visually summarize your data – this allows for many different types of graphs, ranging from pie charts and histograms, to some advanced stock charts.

The beauty of Excel is the ability to link it to other worksheets, but also other Microsoft applications – so you can write a nice ‘document’ in word and then import a graph from Excel (or vice versa).

I could go on and on about the functionality of a spreadsheet – but to be honest you’d be better off buying a book!

Excel is probably the best of the bunch at the moment – earlier versions were not as intuitive as some of its competitors (see my note below on the 2007 version) but overall, the functionality and power of Excel outweighs the lack of intuitiveness.

The look and feel of Excel 2007

For those used to the old style of Excel (note that there is not a ‘classic’ style available in Excel 2007), the format changes will be quite a shock. Excel is now formatted as follows:

Home button – this has most of the ‘start-up’ functions ‘New’, ‘Open’, ‘Save-as’, ‘Print’ are all available in this button. The button also has a section where you can change all the settings within excel (such as default options, default file locations etc) – the are many available options (all of which were scattered around in the old versions) - but it will take a while for you to get your own personal preferences setup. I note that I was able to ‘turn off’ some of these options using VBA – now that the Excel workbook is an XML file, it isn’t so easy to stop users tweaking the options.

Quick Access tool bar – this is perhaps the most useful item – it allows you to add the most useful items that you use – this seems far more elegant than the ‘chunky’ user toolbars in prior versions of Excel, but essentially does the same. I tend to have Undo buttons, print buttons, spell check and a few other minor buttons on this.

The ‘ribbon’ – this is the new XML ‘tool bar’ that replaces all those menu items. It is divided into several ‘groups’ that are far more intuitive than the menus. The main items are:
-Home – this has general items that are used for formatting the worksheet. You can change fonts, apply styles, format text etc.
-Insert – this combines all the different types of ‘objects’ that can be inserted into the worksheet – PivotTables, Pictures etc, Charts, Hyperlinks, text boxes are all controlled from this group.
-Page Layout – this controls how the final ‘print’ will look and allows for changes in orientation, margin, page break etc.
-Formulas – this is a useful group with a ton of resources for all the formulas available in Excel – in addition, there are some additional auditing, calculation and ‘name’ functions.
-Data – this section allows for manipulation, control and connection of data (and datasources). While not as advanced as a real database, these functions are quite powerful.)
-Review – perhaps one of the weaker parts of excel – this group allows for Spellcheck, Thesaurus, Translation as well as controls that allow commenting and sharing of documents. The sharing allows for tracking of changes etc – I always felt that this was a poor part of Excel, Excel 2007 doesn’t do much to improve this.
-View – this controls the look and feel of Excel worksheets – controlling the zoom, the view mode, whether gridlines are shown etc.
-Developer – when you first use Excel 2007 this isn’t available. As a developer I began to panic immediately – How do you amend VBA, how do you change Macros? Well you first have to go into the Home button and click on Excel Options – somewhere buried in these options is a check box to make development tools available. This group allows you to amend and record Macros, view and amend code. There is a section for XML – as many users are not familiar with this (as it wasn’t that transparent in previous version) they’ll probably ignore this section. It really is for Advanced users.

Note – you may be a great VBA programmer – but to get the best out of Excel 2007 you’re going to have to combine your VBA skills with your XML skills. To be able to customize the ribbon requires knowledge of XML and VBA – and is not as easy as previous versions!


What’s change in Excel 2007



With Google taking a stab at online spreadsheets, and the ever improving Apple introducing versatile and user friendly spreadsheets, the old stagnated Excel is looking…well….old and stagnated.

As Excel 2007 is introduced, it is obvious that Microsoft are beginning to listen to the users and not only update the technology, but also enhance the user interface. From a purists point of view it is a monumental change, with far better and more logical usability, and a more standardized file format that will definitely enhance the final product.

On loading Excel you immediately see a vast difference. Straight away you see the new ‘ribbon’ which replaces the old Menu style from Excel 2003 I note that this ribbon is based in XML so users used to manipulating the menus using simple VBA will have a shock as they have to delve into the realms of XML to amend the ribbon.. Furthermore, when you scroll down to the bottom, or to the right you notice that the worksheet just got a whole lot bigger – there’s over a million rows and 16,000 columns – this is already a huge improvement to the 2003 format – which was very limited for most serious users. Note – that like most versions of Excel, there is backward compatibility – you can load your 2003 files in 2003 mode – but you are then limited to the 2003 number of rows and columns. You can convert files, but if you have extensive macros then this may actually cause a lot of problems.

The new ribbon does take a while to get used to, and in fact seems to be an interface that allows you to essentially pull up a lot of the old ‘forms’ that you will be familiar with. There are however, far more controls and adjustments available I particularly like the advanced sorting options – you can now sort by color – cool or wot. I note that a lot of the functions you used to have to delve into menus, require only a click.

The ribbon itself is neat and well organized. The ribbon is divided into subsets – for example you have a ‘formulas’ tab that has all your formula related functions and definitions. It is far more intuitive than the old menus style as everything is well grouped.

A lot of the features from Excel 2003 (and before) are still available – in fact the basic functionality really remains mostly unchanged A spreadsheet is a spreadsheet is a spreadsheet - however a lot of the tools that allow you to manipulate the data, or format the data have been vastly improved. As an example, the use of themes is very prevalent in Excel 2007 and is a very useful feature.

The conditional formatting is perhaps the most enhanced (for me anyway) – you now have many new options from coloring ‘scales’ based on the cell value, data bars based on the cell value – you can highlight a top 10 (or 20 or 30), or bottom 10 etc. It really is versatile and is much easier to use than the old conditional formatting.

A really cool feature prevalent throughout Excel 2007 is the ‘preview’ mode – as you highlight different formats (conditional or otherwise) or look at different fonts, the workbook changes – as you slide your mouse cursor over different ones it will change – this allows you to quickly see what changes will do to your data, without having to apply the changes.

One of the more powerful features, PivotTables has been improved. This generally just makes it more intuitive – making a PivotTable in Excel 2003 could be very cumbersome, but it’s not quite as bad in Excel 2007 – it still is a little awkward. Excel does a nice job of auto formatting – far better than in Excel 2003.

Charts are much the same as Excel 2003 – again the way to produce and format them is far more intuitive, and there are more varieties available – so overall you can produce some far snazzier graphs than ever before.

It’s impossible to cover all of the improvements in Excel 2007 – simple things like adding a ‘background’ picture are very easy, whereas it used to be difficult in Excel 2003. I find that a lot of the things that I used to say ‘I wish Excel would do…..’ is now done in Excel 2007.

Having said that, the functionality of the spreadsheet is as good as ever – one of the strengths of Excel is the variety of formulas that allow you to manipulate the data – Excel 2007 continues with this and adds some of the ‘add-ins’ as standard.

Perhaps the biggest downside is the learning curve needed for using advanced macros. Having designed many applications using extensive VBA, I was a little dismayed by the introduction of the ribbon. The control of this ribbon is very tricky (if you are not used to XML) and I am finding that a lot of macros will need to be rewritten. I currently use Excel 2007 on Windows XP, but have been warned that once Excel 2007 is installed on Vista, a lot of the DLLs that I have used to create my VBA disappear – this will then add another layer of testing and redesign.

Overall my impressions are good – the User Interface is very intuitive, and although most people have been using the old style menus for 10 years…they’ll soon adapt to the ribbon and be using it with ease.

The ability to enhance your worksheet in many different ways, and to manipulate the data is very powerful – the ability to sort by color has been on my personal wish list for years!

Do you really need to update to Excel 2007? Not really – the basic functionality is the same – BUT there is so much ‘fun’ and useful stuff in Excel 2007 that I suggest that it’s not a bad upgrade!