Spreadsheets can help you make sense of numbers and other information

We run workshops covering the basics of spreadsheets.  During one of these we covered the question “why spreadsheets?”

Spreadsheets are an incredibly powerful tool for manipulating and reporting numbers as well as other types of data. This makes them a key tool for every manager and business owner. Indeed, many businesses run on spreadsheets. While this may start as a sound decision; as a business grows it may be time to move on.

What are spreadsheets good for?

Spreadsheets were invented for manipulating numbers, primarily for accountants to replace the paper or mainframe ledgers they used to have. These can be budgets, accounts, financial forecasts, orders or anything else you may want to analyse.  This data can be typed in or loaded from a third party source: most software exports use CSV format which can readily be imported into a spreadsheet. Once in, the data can be manipulated at will.

Formulae can be employed to do complex calculations as well as simple sums but if all they produce are arrays of numbers, then it can be difficult to interpret so the software also provides the ability to produce charts. You can create a simple chart with a couple of clicks. With a bit more effort, you can customise it for use in your management presentation. Even if you have corporate systems, this facility to create bespoke charts is very valuable.

As well as numbers, they are good for holding and analysing other types of data, such as mailing or product lists. You can assign categories to each item and then filter and sort using these. It’s quick and easy to do and makes light of the data analysis you might need to do in order to make a decision.

Once you understand how to use a tool like Excel, you will be producing spreadsheets for all sorts of reasons.

When the alarm bells should sound

One of my concerns with spreadsheets lies in the total accessibility of the information. Unless you have gone to the trouble of locking down the sheets or cells within it, it is very easy to overwrite important data or formulae but much more difficult to spot and correct the error. As the complexity of the spreadsheet increases, so does the potential of basing an important business decision on flawed data. My real life example is the quotation for some work that a project manager sent out which did not include any travel or accommodation costs. Although they were visible on the spreadsheet, they were not included in the total!

To my mind the alarm bells sound sound when the calculations get so complex that you cannot do a “common sense” check on the bottom line.  This is pertinent when you have inherited a spreadsheet from someone else and do not understand how it works.

Other pointers are a workbook using lots of sheets to add a third dimension to the data.

What do I do then?

When you decide that you need to replace a spreadsheet, the answer depends on what you were using it for.  If you are using spreadsheets to manage your business, it may be time to move onto a finance, order or customer management system.

If your spreadsheet is used for analysis, then it may be time to review what it does. Most of these have evolved as they developed. By standing back and working out what you now use it for, you may decide to move onto something else. Alternatively, you may decide to develop a replacement.

In the meantime, make the most of one of the most powerful pieces of software you will come across!