7 Excel Spreadsheet Problems and How to Fix Them

If you need to list anything, it's tempting to see Excel as the default repository: after all, it's just a small list of items for yourself or a few close colleagues.

Maybe you need something more sophisticated, like calculating formulas or programming macros to automate data collection and processing.

Unfortunately, the ease with which you can start working in Excel or a competing spreadsheet is also one of its biggest issues. What starts as a small project in Excel grows into something huge, in which case you might also face speed and stability issues or even a development problem that you cannot solve.

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

This article explains the most common issues that arise when using Excel spreadsheets, how to fix them, and when it's best to take the plunge and switch to a database instead.

Problem #1: Multi-user Excel editing

When Excel systems grow organically, you quickly run into problems when one user opens a workbook at one point and a second person is told it's already open. The second user can cancel, wait, or view a read-only version. Excel's promise to notify you when the other person leaves the workbook is a gamble, as it doesn't check status often and may never enlighten you. Even if it is, someone else might log in and open the file before you.

To avoid "solo user" effects, you can use Excel Online (the simplified web version of Excel) or activate the Shared workbooks characteristic. Here's a quick guide on how to share a spreadsheet.

1. Open the spreadsheet of your choice and click deposit at the top.
2. Then, in the left menu, click on Share to open a new window.
3. Now enter the information of the user you want to share the spreadsheet with.

You can also divide the data into multiple workbooks so that different people work on different workbooks without stepping on each other's toes.

Problem #2: Excel shared workbooks

Excel Online allows several editors by default, but it lacks a lot of features. The service isn't much of a competitor for anything other than the simplest of tasks. Although the shared workbooks feature seems like it should get the job done, it is full of restrictions. You cannot create a table or delete a block of cells if the workbook is shared, for example.

When Excel systems grow organically, you come across the problem that only one user can open a workbook at a time.

There are workarounds for some Excel Online restrictions. For others, it's about changing the structure of the workbook rather than using an already configured workbook, but this scenario often gets in the way. Therefore, it is not possible to use a shared workbook in the same way that you might use an ordinary single-user workbook.

Changes to shared workbooks are synchronized between users each time the workbook is saved. This action is scheduled on a schedule, forcing a backup every five minutes, for example. However, the overhead of regularly logging and tracking each user's changes is quite significant. Filing cabinets can quickly grow and strain your network, slowing down other systems.

Problem #3: Workbooks linked to Excel

Splitting your data across multiple workbooks can provide a solution to the multi-user editing problem. However, they will probably need to have links between them so that the values ​​entered in one are used in the other. Links between workbooks are also useful for keeping separate data in separate files, rather than having individual sheets in a single workbook.

Unfortunately, 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 source and destination paths. While that sounds reasonable, Excel uses arcane rules for deciding when to use each type of link and changing them.

The rules are governed by several options and whether the workbooks have been 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 copying the file using File Explorer. The result of all this confusion and uncertainty is that links between workbooks break easily and recovering broken links is a time-consuming process. No one has access to the affected files.

Linked data is only updated when files are opened, unless you specifically click Data > Queries and Connections > Edit Links > Update Values. Here is a quick demonstration.

1. Open the spreadsheet of your choice and click Data at the top.
2. Now locate Queries & Connections And click Edit links.
3. Then select Update values.

If your links are not between two workbooks but span three or more, you must open all workbooks in the correct order to ensure that the data processes update in the correct order, from first to second to third. If you change a value in the first workbook and then open the third, it won't see any changes because the second workbook hasn't updated its values.

This data chaining makes sense, but it increases the likelihood that the information is incorrect or that you're trying to open a workbook that someone else is already editing.

Of course, you can try to avoid linked workbooks altogether, but it's possible that you end up entering the same data in multiple workbooks, leading to the danger of entering it slightly differently each time.

Problem #4: Excel Data Validation

Errors can creep into the data of any computer system: people mistype words or transpose digits into numbers with monotonous regularity. If your data is not verified as you enter it, you are going to have problems.

By default, Excel accepts all types of users. It is possible to implement validation on lookup lists, but these are difficult to maintain, especially if the same field is used in multiple places. If users have to enter document IDs or customer reference numbers without any verification, it's easy to link the wrong records without realizing it. The data integrity of the system is severely compromised and any analysis of the data is suspicious.

You may already be experiencing the effects of data validation issues without realizing the root cause. Consider a situation where you have a list of invoices in Excel. The user types the customer's name slightly differently on each invoice. As a result, you receive invoices for 'Jones Ltd', 'Jones Limited', 'Jonse Ltd' and 'joness'. You may know that they all refer to the same company, but not Excel. Any analysis of billing data, such as a pivot table based on customers by month, provides multiple results when there should only be one.

Problem #5: Excel Navigation

Large binders are difficult to navigate. The sheet tabs at the bottom of the window are a terrible mechanism for orienting yourself when there are a lot of them. With more tabs displayable on the screen, it becomes difficult to find what you need. Here's a quick way to navigate through sheets.

1. Near the bottom left of the screen, right-click the arrow buttons, left of the sheet names, to bring up the Activate sheet dialogue

Only the first 20 sheets are listed before you have to scroll down the list. There is no way to sort, group or find the sheet you want. The window should look like the one shown below.

Issue #6: Excel Security

You can add security to Excel workbooks, but there are a lot of problems with that. Protection is much more about protecting the structure of the workbook than the data. You can try locking certain sheets and cells to prevent users from changing the structure and formula, but if they can see the data they can usually change some or all of it (unless you're doing some creative macro programming).

Problem #7: Excel speed issues

Excel is not the fastest application and its programming language, VBA, is slow 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. Yes, Excel VBA can be commissioned to handle lists of data, but that doesn't mean it's the best choice for this type of work. Other apps are better suited for such tasks, mostly because they're explicitly designed to do them.

If you're having any of the issues described in this article, don't ignore them. There is a professional answer to storing structured data, called a database. It doesn't have to be scary or expensive, and it should get you thinking logically about your data, how it's related, and how you interact with it.

Notes: If you're moving from a spreadsheet solution to a database, don't slavishly duplicate the spreadsheet design, take the opportunity to improve it.

There are general-purpose database applications, with which you can build a tailor-made solution. Alternatively, you may find that a specialized database application, already designed for the purpose you need, is cheaper, faster to implement, and better suited.

For example, if you have a list of customers and the details of all your interactions with them, this 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 specialized databases. If you can't find a pre-built application that meets your particular needs, you can create it yourself or have your IT department or consultant create one for you.

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

You only need to enter customer data once to create a record, and then you can use it on as many invoices as you want.

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

There are dozens of database applications. Some are easy to use and do all the work, letting you define tables, data entry screens and reports. Others are more comprehensive in specific areas but require other tools to do a full job.

For example, a program can be reliable in defining tables and relationships and have robust analysis and reporting capabilities. Yet the app ultimately lacks the tools to determine…