Maybe you’ve experienced this in the past, Excel is sometimes not the most stable application on your computer. Though the reasons behind Excel crashing is complicated; it’s a mix of your usage, computational power, and data size/type.

To understand why Excel crashes, you’ll first need to understand what it does well and what it does not.

Things Excel isn’t good at

1. Big data analysis

Excel is a spreadsheet program meaning that it’s made of a grid of cells. A spreadsheet stores data values in cells, with all cells represented in a system of rows and columns. Cells can refer to other cells, and the spreadsheet can include cells that carry out processing on other cell values.

A database however stores data values in tables. Each table has a name and one or more columns and rows. A row in a table is called a record. A single record includes a value for each column in a table. Databases can enforce relationships between records in different tables.

Due to Excel being a spreadsheet program, it’s great for data entry and modeling but falls short when it comes to storing a larger number of records. This difference becomes particularly distinctive when your data is larger.

Excel has a hard limit on rows and columns. In 2020, you can store 1,048,576 rows and 16,384 columns in an Excel spreadsheet. This is obviously insufficient for big data analysis. This is where a database has to come to for rescue. With a database, you can store millions of rows of data and read it rather quickly.

2. Integration

Excel is a desktop application that can connect to web apps if you use the “add-ins”. However, the connection can interfere or conflict with Excel. Since you're receiving data from the web to your local computer, many things can go wrong such as malicious connection (virus), and connection disruption, and compatibility issues.

On the web however, integrations can be built in a more of a network friendly environment. This is how Google Sheet took some market shares from Excel.

Where Excel “excels” at

1. Data modeling for small quantity of data (less than 1 million rows)

You can ask a financial analyst how much they use Excel on daily basis. I used to be one of them. I spent probably 80% of my time working on a spreadsheet working with individual company’s financial data. To build a quick Pro Forma or DCF model, there’s really not a replacement to my knowledge. This however changes when you’re dealing with a larger quantify of data. If you are extracting information from more than 1 million rows of data, a spreadsheet becomes much less capable.

2. Manual Data Entry

Accountants and people in operations enter data in spreadsheets for centuries. It is perhaps still the most flexible way of building a database till this day. Again, this changes when you have a thousand of sales people all entering sales numbers in one database. This is where a lot of proprietary software comes in and transform the manual data entry process.

3. Quick charting and data exploration

This is maybe the weakest spot to a spreadsheet’s prowess. There are thousands of charting tools on the market; some cater to a particular type of data feed, some are even designed for a certain type of data (time-series, text and even image). A typical spreadsheet program does not handle all types of data equally when it comes to data exploration and visualization. This is why you have hundreds of Business Intelligence software that charts your spreadsheet data in like a thousand ways. Nevertheless, they’re all built on tops of a spreadsheet!

How to avoid crashing?

Now you understand the limits and strengths of Excel, you can now try to avoid crashing it. Microsoft has explained it pretty well in this page.

To sum up,

1. You should check your integrations/add-ins to make sure the connection is “healthy

2. You should check your data to make sure the data isn’t too “big”

3. Check your computer to make sure there aren’t multiple processes running concurrently since Excel consumes a lot of RAM

Move to Cloud?

What if you do have a lot data and want to connect it with third-party apps? You should try Acho Studio. It’s a cloud-based data management software that can help you access, process, and analyze all data easily and powerfully.

It’s not a spreadsheet program, but you can transform your data in there without coding. Try it out, it’s pretty fun