If you need to make a list of anything, the easy solution is probably using Excel as the default solution. After all, it’s only a small list of items for yourself or a few close colleagues. Right? In the event when you need something more sophisticated, all you need is to key in some formulae for some calculations, or maybe some macro programming to automate the collection and processing of data. Just type “=” to start writing a formula and Excel will guide you along the way. Unfortunately, solving spreadsheet problems is much easier said than done in reality. The ease with which you can start work in Excel or any spreadsheet program is also one of its problems. What starts as a small project in Excel grows over time. One fine day, you will then start to realise that you are stuck with a behemoth. At this point you could be facing speed and stability issues, or even a development problem you just barely get your head around. Sound familiar? Here, we will examine a few of the spreadsheet problems you might come across on a daily basis. We will also talk about some the ways you could possibly tackle them in Excel, as well as defining the point where you’d be better off switching to a database instead. After all, efficiency is the key to successful business. Here we go looking at the common spreadsheet problems.
7 Common Spreadsheet Problems
1. Multi-user editingWhen Excel systems grow organically, you quickly run into the problem that only one user can open a workbook at any one time. The second person to try to open the file is told it’s already open and that they can cancel, wait or view a read-only version. Excel’s promise to let you know when the other person closes the workbook is rather hollow, since it doesn’t check the status very often, and indeed it might never enlighten you. Even if it does, someone else might nip in and open the file before you. There are three ways around this spreadsheet problem: 1) Use Excel Online, the cut-down, web-based version of Excel; OR 2) Turn on the Shared Workbooks feature; OR 3) Split the data into several workbooks so that a different person can use each workbook without you all treading on each other’s toes.
2. Shared workbooksExcel Online allows multiple editors by default, but it’s missing so much functionality that it isn’t really a contender for anything but the simplest tasks. Although its Shared Workbooks feature looks like it should do the job, it’s loaded with restrictions. For example, you can’t create a table or delete a block of cells if the workbook is shared. There are currently some workarounds for some of these spreadsheet problems. For some users, they prefer to change the structure of the workbook, rather than using a workbook that’s already been set up. However, these workaround are not permanent solution as the underlying issues can still get in the way. As a result, it can be impossible to use a shared workbook in the same way you might an ordinary, single-user workbook. Changes in shared workbooks are synchronised between users each time the workbook is saved. This can be on a timed schedule, forcing a save every five minutes, for example. However, the overhead of regular saving and tracking every user’s changes is quite large. Workbooks can easily balloon in size and put a strain on your network, slowing down other systems. Shared workbooks are also fragile and prone to corruption. Microsoft is aware of the problem, but doesn’t seem to be doing much about the issue. It looks like it’s hoping Excel Online’s multi-authoring method will take over from the older shared workbook technology. However, this would not be a realistic proposition until the company removes all the restrictions, and extends the multi-authoring technology to the full Excel desktop application. (as it has with Word, PowerPoint and OneNote)
3. Linked workbooksSplitting your data across multiple workbooks can provide a workaround to the spreadsheet problem of multi-user editing. But it’s likely these workbooks will need to have links between them so that values entered in one can be used in another. Links between workbooks are also useful for keeping logically separate data in separate files, rather than just separate sheets in one workbook. Annoyingly, these links are another source of frustration and instability. They can be absolute, including the full path to the source workbook, or relative, including only the difference between the source and destination paths. Although this sounds sensible, Excel employs arcane rules to decide when to use each type of link and when to change them. The rules are governed by a variety of options, some of which aren’t at all obvious, and by whether the workbooks were saved, and where they were saved before the links were inserted. The links can also change when you Save the workbook or Open and use “Save As” to make a copy, rather than copying the file using the File Explorer. The upshot of all this confusion and uncertainty is that the links between workbooks can break easily, and recovering from broken links can be a time-consuming process, during which no-one can actually use the files affected. Linked data is only updated when the files are opened. Unless you specifically Click:
Data | Connections | Edit Links | Update ValuesBecause of this, if your links aren’t between two workbooks, but cover three or more, you have to open all the workbooks in the correct order to ensure the updated data flows in order. (from the first to the second to the third) If you change a value in the first workbook and then opened the third, it wouldn’t see any changes because the second workbook hadn’t updated its values. This chaining of data is logical, but it increases the likelihood that data is either incorrect or that you’ll try to open a workbook that someone else is already editing. Of course, you can try to avoid linked workbooks altogether, but there’s a chance you’ll end up entering the same data into more than one workbook. With this, comes the danger of typing it in slightly differently each time.
4. Data validationSpreadsheet problems and errors can creep into data in any computer system:
- people mistype words; OR
- transpose digits in numbers with monotonous regularity