What To Do When Excel Keeps Crashing

Love using Microsoft Excel? So do more than half a billion people globally. After all, it’s one of the most popular spreadsheet software available on Windows as well as Android, iOS, macOS. And let’s not forget it’s packed with gazillions of computation or calculation capabilities, pivot tables, graphing tools, and more.

Sure – you can use databases as well as BI solutions to analyze CSV files. And honestly, power BI solutions like Power BI as well as databases can process your data a lot faster than Excel. In fact, Power BI dashboards are more interactive, customizable, and visually appealing.

Instantly Open Big Spreadsheets. Free.

But when it comes to the simplicity of spreadsheets – there’s no comparison. The reason why most people love using Microsoft Excel is that it’s simple and easy to use. And while it’s incredibly popular, the thing is – Microsoft Excel still has its limits.

If your spreadsheet file has exceeded the maximum Excel row and column limit, then Microsoft Excel may not be able to launch your file at all, or will open it and simply drop data. In addition, when you launch a large spreadsheet on Excel, sometimes you’ll see your screen turning grey along with the “Excel (Not Responding)” error message at the top.

And while there may be numerous reasons behind your Excel file being too large in size, what’s the solution to open it without Excel freezing or causing your computer to crash?

To help you understand how to open spreadsheet files too large for Excel, we have put together this blog post, where we’ll be covering:

• The Limitations of Microsoft Excel
• Why Did My Excel File Size Get So Big?
• The Easiest Way to Open Spreadsheets too Big for Excel
• How to Reduce Your Excel File Size

We’re so excited to share this with you. So, let’s dive in.
‍

The Limitations of Microsoft Excel

Even though Microsoft Excel is one of the most popular spreadsheet software, it still has its limits which can cause the application to either not launch your spreadsheet at all or freeze. Some of the most common ones include:

• The Maximum Row and Column Limit – Your Spreadsheet File Will Not Launch at All, or with Incomplete Data
• Spreadsheet Files Too Big for Excel in Size – Too Much Data Causes Microsoft Excel to Freeze or Computer to Crash

First, let’s talk about the maximum row and column limit.

The Maximum Row and Column Limit – Your Spreadsheet File Will Not Launch at All, or with Incomplete Data

No matter where you’re fetching your data from, first it’s important to note that the maximum row limit in Microsoft Excel is 1,048,576 and the maximum column limit stands at 16,384. If you try to launch a spreadsheet on Microsoft Excel that exceeds this limit, you’ll get the following error message:

Now, you can either split this large file with more than 1,048,576 rows and 16,384 columns into multiple smaller files or look for an Excel add-in that supports additional rows and columns. But – it’s just too much work.

To dive deep into the Limitations of Microsoft Excel, we’d recommend reading Microsoft’s “Excel Specifications and Limits” guide.

Another big problem, apart from Excel’s specifications and limits reported by Microsoft, is Microsoft Excel struggling to launch and process a spreadsheet file that’s large in size.
‍

Spreadsheet File Too Big for Excel in Size – Too Much Data Causes Microsoft Excel to Freeze or Computer to Crash

Let’s say you have a low-end PC with 4 GB RAM and an Intel i5 Processor. With this computer, if you try to launch a 50MB Excel file with numerous rows and columns, then you’ll see Excel crashing within seconds or minutes.

Here, we’re not saying that Microsoft Excel will not open your spreadsheet file at all. You’ll obviously be able to open it.

But the thing is – you’ll face a hard time analyzing your data and tapping into Excel features – as it’ll be so annoyingly slow that you’ll end up wanting to shut down your computer due to frustration.  And even if you manage to load it, there will be chances of data loss between actions you take on the file. Your cursor will freeze, calculations will be delayed, and it will be a generally frustrating experience.  

Worse – what if you’re making changes to the file and it freezes all-of-a-sudden? In that case, maybe you’ll lose some data. Auto-save is a great feature, but when dealing with a huge file, it's advisable to turn this feature off. The reason is that a save may take several minutes. Thus, Excel gets into a loop of save after save, and you can never get any work done between saves.

So, if your Excel is freezing or crashing, turn off auto-save and get into the habit of manually saving your Excel File for every small change you make. Otherwise, if it crashes, you may have to start from scratch.

Low-end computers struggle to process a spreadsheet file that’s 20 or 30 MB in size. Whereas high-end computers can easily process it. But what if your spreadsheet file is 200MB in size? While it heavily depends on your computer memory and resources,  the thing is - large spreadsheet files are always a problem when you’re trying to launch them on Microsoft Excel.

This brings us to our BIG question:

Why Did My Excel File Size Get So Big?

Wondering why your spreadsheet file is so big? Allow me to share a few common reasons behind Excel files getting so big in size and causing Microsoft Excel to freeze:

• Data Generated from a Third-Party Source like a Database or CRM.
• Hidden Worksheets.
• Excessive Formatting, Styles & Shapes.
• Too Many Formulas & Calculations.
• Unused Pivot Tables & Charts.
• Macros - Custom or 3rd Party.
• References to Other Excel Files.

If your Excel file is too large in size and you can’t figure out the reason behind it, then allow us to share the best way to open such a file.

The Easiest Way to Open Spreadsheets Too Big for Excel

We don’t really like to brag about our platform. However, we’d like to highlight that no matter what your spreadsheet file size is or how many rows or columns your spreadsheet file comprises, Gigasheet can load and process your spreadsheet file in seconds. The reason Gigasheet can do this is that it is a database disguised as a spreadsheet!

All you need to do is simply upload your Excel file to Gigasheet. And the best part is – you can run powerful queries (no coding experience required) to analyze and filter your data. When you’re finally done filtering your data (depending on your needs), you can save the final results in the form of a separate spreadsheet and launch that spreadsheet in Microsoft Excel. Wondering how?

Allow us to share the steps to start using Gigasheet:

• Sign up for a free Gigasheet account – it’s free forever.
• After signing up and logging in, you’ll be redirected to the Gigasheet dashboard.

• In the top-right corner, select the “+ NEW” option. From here, you’ll be able to upload a file, connect a data source, or create a folder. Since we want to upload a spreadsheet file, we’ll select “File Upload.”

• Either you can drop your file or browse your computer. Or you can import from Google Drive, Dropbox, OneDrive, Box, or via a link.

• Once you upload the file, Gigasheet will process it (usually takes a few seconds or a minute) – depending on the file size. Once it’s processed, you can click on the file name to launch the file on Gigasheet. From there, you can analyze and filter the data depending on your needs.
• You can even remove columns by unchecking them. Also, you can use filters or group by column to get filter the data.

• When you’re done removing columns and getting rid of redundant data by applying filters or via grouping, click on “File -> Save As” to save it.

Now, you can easily launch this file on Microsoft Excel. With Gigasheet, you can get rid of bad data and generate a spreadsheet file that you’ll easily be able to launch on Excel.

Easy-peasy!

And the best part is – your data stays private. We don’t sell or use data.

While you can use BI tools or Python to launch large spreadsheet files, it’s just too complicated for anyone and everyone. Not everyone in your team may know how to code or use BI tools – however, using Gigasheet is easy and simple.

Other than using Gigasheet, if you’re wondering how to reduce your Excel file size, we’ve covered it in the next section.

How to Reduce Your Excel File Size

Using Gigasheet is one of the best ways to launch large spreadsheet files. And you can use Gigasheet’s “Save As” option to save the modified version of the Excel file – with filters. But if you want to do it the old-fashioned way, you can apply three tactics to reduce your Excel file size:

• Delete Hidden Worksheets
• Remove Non-Essential Calculations/Formulas
• Remove Unnecessary Formatting, Styles & Shapes
• Split a large Excel file into multiple files

Delete Hidden Worksheets

Did you know that you can hide and unhide worksheets in Excel? There may be hidden worksheets in your workbook – which you may not be in front of you. And that specific worksheet may be the reason behind your large Excel file size.

Let’s say you see a workbook with three worksheets in plain view. But what if there’s a hidden one you can’t see in front of you?

To check whether there’s a hidden worksheet or not:

• Click on “Review.”
• Under “Review,” select “Workbook Statistics.”
• From here, you’ll be able to see the number of sheets in the workbook.

To unhide a worksheet:

• Right-click (Windows) or double-tap (MacOS) on any sheet’s name.
• Select “Unhide.”
• From here, you can select which sheet you’d like to unhide.

Now, if you think this hidden sheet isn’t required, you can just delete it and reduce the Excel file size.

Remove Non-Essential Calculations/Formulas

Formulas and calculations take up more space than numeric entries or normal text. And while there may be some instances that formulas are unavoidable – some non-essential formulas or calculations may be taking up too much space that you may not even know of. It’s not advisable to check every formula you’ve applied or the calculation you’ve made. However, allow us to share a few that can consume too much of your computer’s memory and cause Excel to freeze:

• Array formulas
• Volatile formulas like TODAY, NOW, RAND, CELL, OFFSET, INFO & INDIRECT.
• COUNTIF, SUMIF & SUMPRODUCT
• Formulas referencing whole rows or column:For example, VLOOKUP(A2,B2:C200,2,FALSE) is more performant than VLOOKUP(A2,B:C,2,FALSE) because in the latter example Excel has to search the whole column versus just rows 2 to 200.

It’s advisable to use Pivot or Excel tables – if formulas can be avoided. Also, if there are any unused Pivot tables as well as charts, then you should get rid of them too.

Remove Unnecessary Formatting & Styles

Formatting, styles, and shapes can consume your computer’s resources and increase the file size of your spreadsheet. So it’s important to get rid of the non-essential ones.

To remove unnecessary formatting:

• Select the cells you’d like to remove formatting from.
• Click on “Home.”
• Under “Home,” click on the clear icon as visible in the screenshot.
• Select “Clear Formats.”

You should not use multiple styles in the same workbook. To remove unnecessary styles, it’s advisable to use third-party tools that are suggested by Microsoft, namely Remove Styles Add-in or XLStyles Tool.

These Are Our Easy Fixes

Large spreadsheet files are always a problem. But they don’t have to be - as we want you to dive deep into your data without having to compromise on any front.

Despite getting rid of excess formatting, styles, and calculations or deleting hidden worksheets, what if your Excel file is still freezing or crashing? With Gigasheet’s ability to process billions of rows, you don’t have to worry about facing a hard time analyzing your large datasets.

Want to analyze spreadsheets too big for Excel? Try Gigasheet! Sign up today for free!