Kieran Cooper celebrates the versatility of the spreadsheet.
Can I get a graph of that?
I first discovered spreadsheets about 12 years ago, and it would be no exaggeration to say that this changed my life! Processes which had previously been tedious and error-prone were made miraculously simple and it is now impossible to imagine life without them.
The concept of a spreadsheet is blindingly simple. A document is divided into separate cells into which can be placed text or numbers. Formulae can be applied to these cells to enable calculations to be performed on their contents. But what makes spreadsheet programs (and particularly Microsoft Excel which represents the majority of the market) even more useful nowadays is the way in which sophisticated links can be made between different sets of data. For example, in preparing an organisation?s production budget you might have three sections ? one dealing with scenery costs, one with performers? expenses and another concerned with income predictions given a range of capacity scenarios. These can be magically summarised on a sheet which shows the overall deficit (more likely than not) and at a stroke it is then possible to calculate the effect that changing the ticket prices will have on the finances of the production. This can be taken a stage further. If the marketing manager maintains a series of spreadsheets recording marketing spend for each incoming show, then these can be linked to calculate overall marketing cost per ticket sold throughout the year. Spreadsheets become even more useful when files are shared on a network and colleagues can access each others? work (assuming they?re allowed to!).
Modern spreadsheets such as Excel can also incorporate external data from other databases, through a piece of magic called ODBC. This enables data to be imported from sources such as ticketing, fundraising, accounting or venue management systems. Data from these systems can then be sorted, manipulated and analysed on the spreadsheet. For example, the ?Auto-filter? function on a spreadsheet enables cross-tabulations between different data fields. ?Show me all the people who don?t have overseas addresses but whose postcode is blank? would be one use for this ? one which I have frequently used to check for errors in a file of names and addresses before a mailing. It?s a quick way to spot someone from Texas who has slipped through into a last-minute panto mailing. As a database, Excel also works well in conjunction with Microsoft Word for creating mail-merge letters or labels. The data can be checked in Excel, and that file can be used as the data source for a merge in Word.
Another invaluable function of spreadsheets is their ability to create graphs. If you have even the slightest memory of GCSE Maths then you should be able to construct a line graph of sales over time or a pie chart of an organisation?s income. Because these graphs will be automatically updated as data changes, they are ideal for demonstrating effects quickly and easily to people who might be put off by lots of numbers (and there?s nothing like a graph showing sales running 10% ahead of target to cheer up your colleagues on a Monday morning!) These graphs can then be pasted directly into a Word document in order to liven up dull reports. Again, by creating a link to the Excel file you can ensure that the data or graph will be updated whenever the information changes.
The best way to learn about spreadsheets is through trial and error. If you have a task in mind that you?d like to undertake, set aside a bit more time to work your way through the system and find the optimum way of getting it done. The help files of Microsoft programs can help (though you need to know what it is that you?re asking for first) but there are also a large number of self-help books if you?re short of a bit of bedtime reading, and a host of organisations offer training courses of varying degrees of complexity. Perhaps the best way is to keep your eyes open to what colleagues and friends have managed to do with the software, and don?t be afraid to ask them for their hints and tips.