7 Excel Spreadsheet Problems and How to Solve Them

If you need to make a list of anything, it’s tempting to see Excel as the default repository: after all, it’s only a small list of items for yourself or a few close colleagues. Perhaps you need more sophisticated formulas for calculations or macro programming to automate data collection and processing.

Unfortunately, the ease of starting to work in Excel or a rival spreadsheet program is also one of its biggest problems. What begins as a small project in Excel grows into something massive, at which point you could also face speed and stability issues or even a development problem you can’t solve.

Furthermore, big data management tasks often present significant challenges, such as organization, implementation, classification of files, database management, user collaboration, and more. All it takes to break the structure of a database is placing data in the wrong area, typing data inconsistently, or even having two people working on the same sheet. Several things can go wrong, causing time delays and possible data loss.

This article explains the most common issues that users encounter when using Excel spreadsheets, how to tackle them, and when you’re better off taking the plunge and switching to a database instead.

Issue #1: Excel Multi-User Editing

When Excel systems grow organically, you quickly run into problems where one user opens a workbook at any particular time, and a second person gets told that it’s already open. The second user can cancel, wait, or view a read-only version. Excel’s promise to let you know when the other person exits the workbook is a gamble since it doesn’t check the status that often and may never enlighten you. Even if it does, someone else might log in and open the file before you.

To avoid the “solo user” effects, you can use Excel Online (the cut-down, web-based version of Excel) or turn on the Shared Workbooks feature. Here’s a quick guide on how to share a Spreadsheet.

1. Open your desired Spreadsheet and click on “File” at the top.
2. Next, in the menu on the left-hand side, click on “Share” to open up a new window.
   
3. Now, enter the information of the user you want to share the Spreadsheet with.

Note: You can also split the data into several workbooks so that different people work on different workbooks without treading on each other’s toes.

Issue #2: Excel Shared Workbooks

Excel Online allows multiple editors by default, but it’s missing a great deal of functionality. The service isn’t much of a contender for anything but the simplest tasks. Although the Shared Workbooks feature looks like it should do the job, it’s full of restrictions. For example, you can’t create a table or delete a block of cells if the workbook is shared.

There are workarounds for some online Excel restrictions. For others, it’s a matter of changing the workbook’s structure rather than using a workbook already set up, but this scenario often gets in the way. As a result, it is impossible to use a shared workbook the same way you might use an ordinary, single-user workbook.

Changes in shared workbooks are synchronized between users each time the workbook gets saved. This action gets placed on a timed schedule, forcing a save every five minutes, for example. However, the overhead of regular saving and the tracking of every user’s changes becomes quite significant. Workbooks can quickly balloon in size and put a strain on your network, slowing down other systems.

Issue #3: Excel Linked Workbooks

Splitting your data across multiple workbooks can provide a workaround to the problem of multi-user editing. Still, each workbook will likely need links between them so that values entered in one get used in another. Links between workbooks are also helpful for keeping separate data in separate files, rather than having individual sheets in one workbook.

Annoyingly, these links are another source of frustration and instability. They become absolute, including the full path to the source workbook, or relative, including 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 change them.

The rules are governed by several options and by whether the workbooks got saved before inserting links. The links also change when you save the workbook or open and use Save As to make a duplicate rather than copy the file using File Explorer. The upshot of all this confusion and uncertainty is that the links between workbooks break easily, and recovering from broken links is time-consuming. Nobody gets access to the files affected.

Linked data is only updated when the files get opened unless you specifically click “Data > Queries & Connections > Edit Links > Update Values.” Here’s a quick demonstration.

1. Open up your desired Spreadsheet and click on “Data” at the top.
2. Now, locate “Queries & Connections” and click on “Edit Links.”
3. Then, select “Update Values.”

If your links aren’t between two workbooks but cover three or more, you have to open all the workbooks in order, ensuring that any updated data processes occur in the correct order, from the first to the second to the third. If you changed a value in the first workbook and opened the third, it wouldn’t see any changes because the second workbook hadn’t updated its values.

This data chaining is logical, but it increases the likelihood that information 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, and with that comes the danger of typing it in slightly differently each time.

Issue #4: Excel Data Validation

Errors can creep into data within any computer system: people mistype words or transpose digits in numbers with monotonous regularity. If your data doesn’t get checked as it’s entered, you’re going to have problems.

By default, Excel accepts whatever the user types. It is possible to set up validation on look-up lists, but they are difficult to maintain, mainly if the same field gets used in more than one place. If users have to enter document ID numbers or customer reference numbers without any checks, it’s easy to tie the wrong records together without realizing it. The data integrity of the system becomes fatally compromised, and any analysis of the data is suspect.

You may already suffer the effects of data validation problems without realizing the root cause. Consider a situation where you have a list of invoices in Excel. The user types the name of the customer slightly differently on each invoice. As a result, you get invoices to “Jones Ltd,” “Jones Limited,” “Jonse Ltd,” and “joness.”

You may be aware that these are all referring to the same company, but Excel doesn’t. Any analysis of the invoice data, such as a pivot table based on customers by month, delivers multiple results when there should only be one.

Issue #5: Excel Navigation

Large workbooks are challenging to navigate. The sheet tabs across the bottom of the window are a terrible mechanism for finding your way around when there are numerous amounts of them. With more displayable tabs across the screen, it becomes difficult to find what you need. Here’s a quick way to navigate through sheets.

1. Near the bottom, left-side of the screen, right-click on the “arrows buttons,” to the left of the sheet names, to bring up the “Activate Sheet” dialog.
   
2. Only the first 20 sheets are listed before you have to scroll through the list. There’s no way to sort, group, or search for the sheet you want. The window should look something like the one shown below.

Issue #6: Excel Security

You can add security to Excel workbooks, but it’s rife with problems. Protection is geared much more towards protecting the structure of the workbook rather than the data. You can try to lock some sheets and cells to stop users from changing the structure and formula, but if they can see the data, they can usually change any or all of it (unless you do some creative macro programming).

Issue #7: Excel Speed Problems

Excel isn’t the fastest application, and its programming language, VBA, is sluggish compared to more professional programming languages such as C#. This scenario stems from the intended use and flexible nature of Excel. It is, after all, a spreadsheet engine. Yes, Excel VBA can get pressed into becoming a service that manages lists of data, but that doesn’t mean it’s the best choice for that kind of work. Other applications are better suited to such tasks—mainly because they get explicitly designed to do them.

Using a Database for Structured Data

If you’re hitting any of the problems outlined in this article, don’t ignore them. There’s a professional answer to storing structured data, known as a database. It doesn’t have to be scary or expensive, and it should allow you to think logically about your data, how it links together, and how you interact with it.

Note: If you’re moving from a spreadsheet solution to a database, don’t slavishly duplicate the spreadsheet design. Take the opportunity to make it better.

There are general-purpose database applications available, with which you can construct a bespoke solution. Alternatively, you may find that a specialist database application—one already designed for the purpose you require—is cheaper, faster to implement, and a better fit.

For instance, if you have a list of customers and the details of all your interactions with them, that is considered a customer relationship management (CRM) system. Despite its fancy name, a CRM system is a specialized database. Similarly, account packages such as QuickBooks and Sage are specialist databases. If you can’t find a prebuilt application that suits your particular needs, you can build it yourself or get one made for you by your IT department or consultant.

The most common database type is a relational database, which stores its data in tables and consists of rows and columns. Each row holds the data for a separate item. For example, each column describes a different subject attribute, such as the customer’s name or credit limit.

The tables have relationships defined between them so that, say, an invoice carries the customer ID. This process means you can easily find all the invoices for a particular customer or retrieve the customer’s phone number from a specific invoice. You only need to enter the customer’s data once to create the customer record, and then you can then use it on as many invoices as you need without having to type it in again. To create a database, you have to define these tables and relationships and then define the screen layouts you want to use to list and edit the data.

There are dozens of database applications out there. Some are easy to use and do the entire job, allowing you to customize the tables, data-entry screens, and reports. Others are more full-featured in specific areas but require other tools to do a complete job.

For instance, a program may be reliable when defining the tables and relationships and having robust analysis and reporting features. Still, the application ultimately lacks any tools for determining data-entry screens. Microsoft SQL Server is the obvious example here. As with other large database systems, SQL Server takes care of the back-end and expects you to use another tool, such as Visual Studio, to develop the front-end.

Which Database Options are Right for You?

Database Option #1: Microsoft Access

Access is one of the granddaddies of desktop databases. It’s easy to use yet easy to abuse. You can design tables, screens, and reports from scratch or start from a template.

Some of the templates are overtly American and don’t always teach good practice, but they get you started quickly. Screens and programming features can be pretty sophisticated. You can deploy your finished application to other users through your intranet (NOT internet) rather than rely on file shares.

Database Option #2: Microsoft SharePoint

SharePoint is a database, as well as a document-storage mechanism. You can use it to compile simple lists and link them together. The Form Designer is slightly sophisticated, but customization is still possible. SharePoint’s ability to “grab” a list of data accumulated in Excel and place it into a custom list is helpful.

The program makes the custom list available to everyone on your network and enables you to add security to restrict who can do what with that data. You can ask SharePoint to alert you by email whenever someone adds, edits, or deletes records. If you’re storing data concerning people, calendar items, or tasks, you can synchronize that data with Outlook.

Database Option #3: Zoho Creator

Zoho Office is a web application that includes a database that uses drag-and-drop functionality to spread its forms in a simple, intuitive manner. The drag-and-drop process also gets used to program the interactions and workflows. Your data and applications are available from anywhere, with simple security to keep your data private. Zoho charges on a per-user, per-month basis, but it limits the number of records you can store for that established price. The program costs extra when storing more data or for other features such as email integration.

Excelling at Excel

As you can see, Excel offers many features but lacks in some areas within each one. Sometimes, another application does the job better, especially if it’s designed specifically for the task. Other times, Excel works just fine, such as for smaller databases, as long as you know how to prevent problems from occurring in the first place.