The second day of the 2009 Excel User Conference was intended for hardcore users and tackled topics such as spreadsheet optimisation, functions and VBA. Simon Hurst reports.

Day one report: introductory tips
The 2009 Excel User Conference was structured so that talks on the first day covered relatively simple topics. These are covered in Simon Hurst's first article [1] from last month's event.

Data exchange techniques explored by Nick Hodge
The first session of the second day concentrated on Excel's data exchange capabilities. Nick Hodge [2] started off by showing how to import and export text files in Excel. Excel's text import wizard had trouble importing text fields that contained numbers - particularly if they included leading zeros, he pointed out. Excel's natural inclination is to assume that any field with numbers in should be treated as a number (not unreasonably). If your raw data is in a CSV, file then Excel just takes it upon itself not to run the text import wizard when you open the file, but to assume it understands the format and guess all the data types - converting your numeric product references to numbers and, in so doing, stripping out all leading zeros. Nick's answer is to rename the file so it no longer has a CSV extension. This switch forces Excel to run the text import wizard which includes the ability to define each column's data type manually. So you can tell it that your product code is a text field and thereby preserve its format and leading zeros.

Another good, practical tip related to using Excel as the data source for a Word mailmerge. Unfortunately the data transfers neatly into Word, but any cell formatting doesn't, so your numbers and dates may not appear as you would wish to see them. The solution is to add columns to your data that use the Excel Text() function to convert your value or date fields to text in your chosen format.

Applause very nearly broke out spontaneously as Nick went on to show how to use the Edit in Microsoft Query option towards the end of the Microsoft Query Wizard steps to add one or more parameters as part of a query criteri, for example: between [Start date] and [End date]. The parameters within the square brackets can then be linked to cells in a worksheet so that, as new values are entered into those worksheet cells, the data returned by the query to the Excel worksheet changes accordingly.

Smurf's introduction to VBA
Simon Murphy [5] then returned for his second presentation of the conference – this time, a beginner's guide to VBA. Although only three members of the 100-strong audience admitted to being VBA beginners, Simon's long experience of developing in Excel VBA meant he was able to provide authoritative guidance on many programming issues. For example:

• Use the Excel form controls, not the far less reliable ActiveX controls




• Always ensure you declare your variables; and




• Be very careful not to allocate the Ctrl+C keyboard shortcut to a particularly destructive macro.

He also demonstrated how to use VBA to create a user-defined function which could be used in a workbook in the same way as a normal built-in Excel function.

More data visualisation tips from Andy Pope and Bob Phillips
Next Andy Pope [6] and Bob Phillips looked at "Visualising data in Excel" and, probably because we had all read books by Edward Tufte and Stephen Few on the subject, came to broadly the same conclusions as the AccountingWEB series on presenting financial reports [7]. Bob covered some of the same excellent techniques for presenting data without using charts, as in his presentation to the previous conference [8].

Andy Pope set about creating charts that, until I'd seen him produce them, I would have thought to be completely impossible in Excel. My favourite example was a set of 'bullet' charts. These are recommended by Few as a much more space-efficient alternative to speedometer gauges. Instead of a circular or semi-circular dial face with coloured areas to signify good, OK and danger, a bullet chart uses a narrow line to represent the data within a wider bar that has coloured bars to represent the different areas. Andy created this using dummy series for the bands, overlapped the bars and then increased the gap width of the 'real' data bar to reduce its width - this technique is explained in more detail in a separate article, Charting tips and tricks from Excel MVP Andy Pope [9].

Then for me came the best bit of the entire conference, Pope used the Excel Camera to create a dynamic link to a picture of the column chart, then rotated the resulting picture through 90 degrees to turn it into a bar chart. I couldn't resist trying this out on one of my own (entirely unsuitable!) charts:

The trouble with array formulae - Bob Phillips
As the day became steadily more technical, Bob Phillips took us through the capabilities and drawbacks of array formulae, concluding that most of the time the Sumproduct() function could do everything an array formula could do, but without the need to remember to use Ctrl+Shift+Enter to save it each time, and often with less apparent complexity. For example, given a list of invoices with analysis codes, dates and values, Sumproduct() could calculate the total of sales for a particular day for a particular code:
=SUMPRODUCT((D2:D200=AnalysisCode)*(E2:E200=CurrentDate),F2:F200)

Last, but not least - spreadsheet acceleration tips from Charles Williams
If you are struggling with one or more Excel workbooks that take an inordinately long period of time to copy then the conference may well have saved its best until last. Charles Williams had a lot to live up to as far as I was concerned following the 7 hours to 1.5 minutes [10] revelation at the end of the first day.

He more than lived up to the challenge as he explained the detailed steps how to achieve a 200-fold speed increase in his example spreadsheet. The technique was pretty simple. First you have to turn off automatic calculation and then gradually drill-down through your project identifying the bottlenecks by recalculating the whole workbook, each worksheet and then selected ranges of cells within any problem worksheet. In order to calculate just a range and to achieve the appropriate level of accuracy some VBA code is necessary and Charles has many useful utilities – both chargeable and free – on his Decisionmodels.com [11] website. In the conference example, he was able to reduce a 12-second recalculation time down to a few milliseconds by removing volatile functions and replacing formulae that had to perform millions of calculations with alternatives that used far fewer. The general guidance was to highlight the problem formulae using the drill-down approach, then examine those formulae to see how many actual calculations each one performed and see how using a different formula could achieve this. Sometimes the answer is just to break the formula into separate parts so you end up with more formula cells, but each one carries out far fewer calculations.

With slight exaggeration Charles ended his talk, and the formal part of the conference with the thought that: "there are often 26 million ways of doing the same thing in Excel, but some are 26 million times slower than others."

This report is just a brief overview of the second day's proceedings. For news of the planned follow up event, keep an eye on the conference website [12].

Subscribe to the ExcelZone newswire
[13]To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function [14] will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.