What to Do When Excel is Slowing You Down?

Using Excel has its pitfalls – like everything in life. I bet each of you has had the experience of errors (and the heart attack that was a bonus) after changing a row, such as a financial record in a shared spreadsheet. Fortunately, we have solutions for both Google Sheets and Excel’s access controls to make things easier.

In today’s business world, everyone uses Excel to manage and analyse data. But when companies are dealing with big data and high-stakes tasks, Excel’s shortcomings start to show and slow things down. If you are working with massive datasets, you know what I mean. 

Luckily, we’ve got optimization tricks and alternative options to tell you about. And don’t miss our insights on simplifying complicated business logic and functions – you don’t need programming skills to use them. 

In this article, we look at the common challenges faced when Excel starts slowing you down and provide practical solutions to tackle these issues. 

Let’s get this show on the road. 

Problem 1: Multiple People Working in Parallel

When many users attempt to modify offline files or on a shared disc, collaboration in Excel may quickly become a pain. Conflicting data and restricted access are the main worries we are talking about here.

Even with online spreadsheets, tracking history can become a tedious task. How do you find the version you are looking for? 10. August at 10:50, 10:51, or 10:55? Or maybe it was changed the day before or just a second ago?

You have to sit down, analyse it, and find the right time – where do you find the version of the file before one of four people edited it and accidentally removed the line with the customer data?

When I worked for a game localization company, I was always afraid that I would accidentally delete important information. There were many people who used the localization file for the game’s events, and we regularly provided them with translations into multiple languages from our vendors. 

One wrong move could mix up the texts of the events and cause major errors – the texts did not match in terms of meaning and number of characters, and users inundated us with complaints.

Let’s have a closer look at possible solutions to this type of problem. 

Solutions

Google Sheets

• Dedicated roles: One of the ways of reducing the chances of accidentally changing data, is turning more to sharing data options in Google Sheets. The domain-level permission settings let you limit users’ ability to edit, view, or share the spreadsheet. 

That’s the key here – restrict edit access to only those who really need to use the document, and allow others only to comment or view it. It will not only ensure security and prevent unauthorised changes, but also make the document more reliable and less prone to random edits and having to browse through edit history. 

You can customise it even more –  share your work with three types of targets: individuals (by email address), groups (for multiple users at once), or your domain if you’re part of a Google Workspace domain. 

• Hiding sheets: Another useful feature in Google Sheets is the ability to hide sheets, especially those that contain sensitive or source data. By hiding those, you can control which parts of the spreadsheet are visible to different team members.  If some information is 

How to do it?

1. Open a spreadsheet in the Google Sheets.
2. Tap the sheet you want to hide.
3. On the sheet tab, tap the Down arrow Down arrow.
4. Tap Hide. This option won’t show if your spreadsheet doesn’t contain two or more sheets.
5. Your sheet will be hidden from view.

This can be especially helpful if you want to share a spreadsheet but keep certain data or calculations hidden from certain users. It adds a layer of privacy and organization.

MS Excel

In Microsoft Excel, you can restrict access to your files or make them final using Information Rights Management (IRM). IRM is a security feature that prevents sensitive information from being printed, forwarded, or copied by unauthorized people. 

You can find these permissions in the workbook and authenticated by an IRM server. There, try to set permission levels manually by accessing the “Permissions” option under the “Review” tab. You can also assign access levels such as ‘Read,’ ‘Change,’ or ‘Full Control’ to specific employees or groups. 

Keep things transparent and simple when setting permissions by using pre-built templates that follow your company’s rules and policies. Remember that if you’ve used a template, you can’t change or remove permission levels manually. 

An interesting feature of IRM is that it also lets set expiration dates for files, control printing and copying permissions, enables scripts to run, and requires a connection to verify permissions. 

Problem 2: Handling Huge Amounts of Data

As data volumes grow, Excel’s performance can go down really hard. You need to ask yourself these questions: up to what number of data Excel starts slowing down your processes? When does it start to get in the way and possibly mess up our analysis and decision-making?

Theoretically, Google Sheets support up to 10 million cells, while the latest Excel files allow up to 1,048,576 rows.  But the truth is that 10k+ records start to clog up spreadsheets.

Imagine a document that takes 15 minutes to open (been there, done that) – it contains promotional strategies derived from customer data (10k sales records) in Excel. Because of the macros, it takes forever to open the file locally on the computers. 

You simply cannot analyse the data properly – if you want to check, for example, which promotion is running this season, errors will occur when switching between spreadsheets. It’s just too risky to use spreadsheets instead of a regularly backed up database.

Also, calculations performed on the full range of data may start to slow down, even though they were working perfectly before. This is the cycle of life, or rather of business. As a small business grows, so does its data. Excel may have worked effectively for a scale-up, but in larger companies, problems start to crop up.

Yet, very often you need to keep using Google or Excel spreadsheets for some time. There are ways to raise its performance without going mad. Have a look at our tips below and try to use them regularly.

Solutions

Google Sheets

• Keep References Close: Don’t link information from far-off worksheets in your Google Sheets. It can really slow things down as it takes forever to fetch because of the exchange delays.
• Use Filters for Efficiency: Got a ton of data? Want to work with only some of it? Filters will help you when dealing with large datasets. They make things easier to handle and take some load off the server, making Google Sheets work faster.
• Divide Large Sheets: It might be time to break up your data into smaller bits across different workbooks. Yeah, it’s a bit more work, but it can help speed things up, even if it complicates life.
• Reset Offline Access: When all else fails, give this a shot. Turn off and then on Google Sheets’ offline access. Every so often, it’s one small trick that can boost its performance.

MS Excel

• Use database tools: For extensive datasets, think about using database tools or saving data in CSV files. By offloading data storage and retrieval to these tools, you can optimize Excel exclusively for calculations, not storing. Tools like Power Pivot or Power Query facilitate seamless data import for analysis.
• Turn to manual calculations: Switching Excel to manual calculation mode (File > Options > Formulas) can come in handy, particularly with complex workbooks. With manual calculation, Excel will only recalculate when you manually prompt (e.g., by pressing F9).
• Turn off Excel add-ons: To further boost Excel’s performance, deactivate unnecessary add-ins by navigating to File > Options > Add-ins. Unchecking extensions you don’t require can free up system resources and super-charge the calculations.
• Export as Excel Binary File: If your Excel file doesn’t need to interact with external tools, consider saving it as an Excel binary file (.xlsb). This format cuts out any outside interactions, making calculations faster. 
• Delete unnecessary pivot tables: When using pivot tables for temporary analysis, it’s common sense to delete them once you’ve found the insights you need. For dynamic data, opt for formulas instead of retaining pivot tables.

Problem 3: Complicated Business Logic 

Let’s set the record straight. Spreadsheets are not ideal for implementing difficult business logic. For example, if a certain amount is to be calculated when 3 fields are filled with customer financial data.

It is super important to know how many fields and sheets there are where this should happen – the user needs to know that such conditions have been set. And when people on the team change or someone gets sick, the road ahead gets bumpy.

In a web application, this can be skipped and fully automated, but for the mathematical functions for accounting, banking it is not so easy.

A good example for Excel would be the option to send an email after registering an account with this web application. Here, it is only possible to send these emails manually after uploading the list to a file.

Other possible workarounds include complex functions or writing code to send an email. However, this is worse than automating a custom application because there is no control over the email. We have no way to verify that it was delivered or actually sent. There is also the option to integrate with an external tool like Zappier and link to an email sending tool like Mailchimp, but there is an additional cost and 2 additional tools to use.

What to do if you need to keep using those spreadsheets?

Solutions

As a workaround, people use the built-in functions, formulas, and macros. This is not a bad approach at all. To extend the variety of solutions, you might incorporate into your Excel, we would like to introduce Regexes.

Regex functions might be helpful in extracting certain data patterns or conditions from spreadsheets. Regex can be applied, for instance, to find and calculate the needed financial data depending on having particular fields, making it clear where and how these requirements are defined.

On top of that, using regex may speed up processes and guarantee data integrity without the need for user intervention by automating specific jobs inside a Web site, such as email validation and processing. You may streamline the process and lessen reliance on complicated spreadsheet functions or outside applications by utilising regex to extract and edit data. 

While this can be a powerful tool, remember that it can also make your formulas more complex and potentially harder for others to understand and maintain.  If you’re not sure if your regex is correct, we have a useful training and testing area in Regexr. There you can test and verify your regex before implementing it and avoid further errors in your sheets.

Here’s a bunch of our examples of how you can use regex on both platforms. 

MS Excel

Unfortunately, Excel doesn’t have built-in regex functions, but you can create custom VBA functions or use third-party tools that support regex. Here’s a basic overview:

• RegexMatch Function: This searches for text matching a regex pattern and returns TRUE if a match is found, or FALSE if not. It can be used to test if a cell value matches a particular pattern.

Example: =REGEXMATCH(A1, “\d{4}-\d{2}-\d{2}”)

If you are afraid of the formula above, get back to regexr.com – this can help to understand the basics. After playing around with the tool, it will be much more understandable.

• RegexExtract Function: This searches for substrings matching a regex pattern and extracts the first match or all matches, depending on the parameters.

Example: =REGEXEXTRACT(A1, “\b[A-Z]{2}-\d{4}\b”)

• RegexReplace Function: This replaces substrings matching a regex pattern with specified text.

Example: =REGEXREPLACE(A1, “\b\d{4}-\d{2}-\d{2}\b”, “YYYY-MM-DD”)

• Regex Tools Add-ins: You can also use third-party add-ins like the Ablebits Regex Tools for Excel to simplify working with regular expressions without needing to write VBA code.

Google Sheets:

Regular expressions, often abbreviated as regex or regexp, are great tools for text pattern matching and manipulation. Google Sheets has built-in support for regular expressions through various functions, making it easier for users to work with data.

Regex patterns are particularly useful when you need to:

• Extract Specific Information: You can use regex to extract specific pieces of data from a text string. For example, if you have a cell containing a complex string that includes a date in the format “YYYY–MM–DD,” you can turn to regex to extract just the date portion.

Example: =REGEXEXTRACT(A1, “\d{4}-\d{2}-\d{2}”)

• Replace or Clean Data: Regex can help you clean and standardize data. If your spreadsheet contains inconsistent formatting for phone numbers, addresses, or dates, regex can help you order them. 

Our example: ==REGEXREPLACE(A1, “\b\d{4}-\d{2}-\d{2}\b”, “YYYY-MM-DD”)

• Check for Patterns: You can use regex to check if data complies with specific patterns or rules. For instance, you can use it to validate email addresses, phone numbers, or other structured data.

Example: =REGEXMATCH(A1, “^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$”)

• Split Text: When you have text that needs to be divided into separate parts, regex can find delimiters and split the text.

Example: =SPLIT(A1, ” “)

• Search and Filter Data: Regex can help you search for specific text patterns within a larger dataset and filter rows that meet certain criteria.

Example: =FILTER(A1:A10, REGEXMATCH(A1:A10, “keyword”))

• When working with regex in Google Sheets, you’ll use functions like REGEXEXTRACT, REGEXREPLACE, REGEXMATCH, and more. These functions take a text string as input and apply a regex pattern to it. If there’s a match, they return the result as specified by the function.

Keep in mind that regex should be used with care. You need to document your formulas well, especially if they have complex regex patterns. What’s more, sharing regex-based formulas with others may require some training or explanation, as regex can be cryptic to those unfamiliar with it.

App development 

For businesses dealing with sophisticated business logic, using app development tools instead of traditional Excel or spreadsheets may be a more practical choice. Excel is a strong tool for managing and analysing data, but it may struggle to manage complex business processes, particularly ones that need a lot of data processing, automation, and scalability.

1. It can handle regexes like a pro, you can even create an automatic test for them instead of manually checking their accuracy.
2. Any kind of notifications can be easily implemented, such as sending emails, SMS or push notifications.
3. Any kind of integration can be added, and your action can trigger the integration with another platform or service, or send the data to wherever you want it to go.
4. With app development, you can make special tools to extract, analyze, and show data in ways exactly how you want it for your business. This is much more flexible than using Excel.
5. Web apps can show changes right away, so when you do something or change data, you see the results instantly. This quick feedback helps you make better decisions and reduces mistakes, which is better than waiting for updates like in Excel.

Problem 4: Complicated Functions & Macros

Time and again, functions and macros are usually created not by Excel experts, but by average users who need to solve a specific problem. Then the usual story unfolds: someone copies the solution and adapts it to their needs.

When the author leaves the company, there are parts of the spreadsheet that no one knows how to use, but that are critical to the business.

That’s why one of the main problems with user-generated functions and macros is the lack of documentation. Their authors may not take the time to thoroughly document how they work, and that’s causing countless issues later.

People also waste a lot of time and make complex macros or calculations without knowing that they could use custom functions to make their work easier. However, in such cases, basic programming know-how is required. Let’s examine these solutions in detail below.

Solution

Custom Functions

Imagine you’re dealing with large data sets, such as scientific research results or financial modelling, and you need to do complex calculations or data manipulations. Custom functions can be written to automate these tasks.

You can create a function in Excel or Google Sheets that calculates loan amortisation schedules to help you understand how payments are applied to principal and interest over time. A custom function can also help with investment analysis, such as calculating the net present value (NPV) or internal rate of return (IRR) of potential projects. 

Example: Suppose you have a dataset with temperatures in Celsius, and you need them in Fahrenheit. You can create a custom function that takes Celsius as input and returns Fahrenheit values, simplifying the conversion process.

Macros

Macros, on the other hand, allow you to record a series of actions and play them back whenever required. Let’s say you often format data tables in a specific way or apply certain calculations to datasets. By recording these actions as a macro, you can perform them consistently and swiftly with just a few clicks.

Example: If you frequently apply the same formatting (e.g., bold text, colour-coding) to specific cells in your spreadsheet, recording a macro to do this saves you a lot of time.

Lesser-Known Excel and Spreadsheet Functions

Excel and Spreadsheet Functions: Both Microsoft Excel and Google Sheets offer a treasure trove of built-in functions. Many users are unaware of the full range of functions available. These functions can typically replace the need for external tools or complex combinations of formulas.

Example: Instead of relying on external currency conversion tools, you can use built-in add-ins. These add-ins can be incredibly convenient, especially if you frequently work with international transactions or travel abroad.  For example, Google Finance, provides real-time currency exchange rates and can handle conversions in a few simple steps. 

Custom Functions and Macros for Functionality Beyond Defaults

While the default functions in Excel and Google Sheets are powerful, there may be scenarios where you require specialized functions adapted to your needs. This is where custom functions and macros shine.

Example: Consider a scenario where you’re managing inventory in a retail business. You might create a custom function or macro that not only tracks stock levels but also provides alerts when inventory runs low, helping you optimize stock management efficiently.

The Role of Basic Programming Knowledge and Documentation

• Basic Programming Knowledge: It’s important to know that to fully leverage custom functions and macros, at least a basic understanding of programming concepts is beneficial. You don’t need to be a coding expert, but knowing how to write simple scripts or functions can significantly enhance your ability to create and customize these solutions.
• Importance of Documentation: While creating custom functions and macros, it’s crucial to provide documentation. This documentation explains the purpose and functionality of these custom elements. Having someone on your team who understands these solutions and can document them is priceless.
• Knowledge Transfer: Documentation serves as a bridge for knowledge transfer. If the original creator leaves the company, the documentation ensures that others can take over the maintenance and usage of custom functions and macros without disruptions.

Don’t Let Excel Slow You Down

This is just the tip of the iceberg of potential spreadsheet problems and solutions. Although many of them can be solved, focusing on improving an already slow solution may not be the most reasonable or effective task.

Excel is a great solution to get started, build the process, and get things moving. But as you grow your business, your customers, and especially your data, you have to choose.

Do you want to stay with the tool that is slowing you down, or do you want to move to the next level of automation or even a custom solution?

If it is the latter – feel free to get in touch with TH-EY. Most of the problems we talk about come from our experience, and we already have many more solutions in our portfolio.