Why is Excel so slow?
Desktop Excel is slow because it is limited to the computing and memory resources on a local computer and because the application itself must manage both data storage and analysis, which increases the memory requirements. When spreadsheets contain big data sets, complicated formulas, and numerous advanced features, like named ranges, conditional formatting, or add-ins, they require more memory (RAM) to operate. The average personal computer has 8GB of RAM and Excel files with large datasets or heavy computational loads consume that memory quickly. Computers with faster processors and 16GB or 32GB of RAM will perform better when running big Excel files but even those machines have their limits. In addition to hardware limitations, Excel was developed before memory efficient columnar data stores were invented, which means the ability to support large data sets just isn't as good as it could be.
Due to the hardware and software constraints, Excel added Power Pivot, Data Model, and Power Query to Excel in 2010. Those features are found in the 'Data' tab in Excel and are designed to handle bigger data sets with memory efficient data storage. The downside to Excel's business intelligence features is they don't provide the same simple and flexible spreadsheet experience as normal Excel. For this reason, many Excel users are unaware of the BI features and unable to use them. The Excel BI features are described here:
- Data Model - Connects data sources (databases, APIs, CSVs, spreadsheets and other file types) to Excel in the form of data tables.
- Power Query - A graphical user interface to view large data sets as data tables and automate transformation steps to produce clean data sets.
- Power Pivot - A graphical interface to join data sets on matching fields make column level transformations, and create pivot tables and filters.
The following post covers the most common causes of slow Excel spreadsheets, reasons why, and solutions. If you are looking for a quick solution, try Row Zero, a blazingly fast spreadsheet designed for big data. Otherwise, continue reading for common solutions in Excel or skip to specific sections using the table of contents.
- Data set is too big for Excel
- Computer does not have enough memory
- Memory intensive computations
- Volatile or problematic Excel functions
- Full-column references
- Large numbers of formulas
- Use of named ranges
- Use of conditional formatting
- Problematic add-ins
- Graphics acceleration
Why is Excel 365 slow?
Excel 365 is even slower than desktop Excel because, like Excel it makes use of a local computer's memory (RAM) and processor but it also runs in the browser, which is an extra layer of indirection, complicating processing efficiency. The browser is essentially a virtual machine. Excel 365 communicates with the browser, which then communicates with the processor on the local computer. The extra layer of transmission is the piece that further slows Excel 365. In fact, Microsoft limits the size of an Excel 365 file to 10MB.
Symptoms of a slow Excel file
You've imported your data, built an analysis, and are ready to calculate an answer. You hit 'Enter' and it happens, Excel freezes and you are stuck waiting and wondering if the file will update or if the program will crash and you'll lose all your latest changes. If you've ever felt like Excel is lagging, running slow, slow to open, or crashing, you have experienced the performance limitations of Excel. Read on for ways to improve a poor performing spreadsheet.
10 reasons why Excel is slow and how to fix it
As mentioned above, the fundamental challenge with Excel is it is limited to the compute power on a local computer and was developed before modern techniques for memory efficient data storage was developed. For these reasons, there are several fundamental Excel limitations. We address ways to work around Excel's 10 most common performance issues below.
1. Dataset is too big for Excel
Microsoft Excel is very fast for small to medium sized data sets but as those datasets start to grow, the application will slow down. Excel has a row limit of 1,048,576 rows and 16,384 columns. If a data set is bigger than the limit, Excel will not show all rows or columns when opening the file. Additionally, Excel will be very slow with 1,000,000 rows of data loaded into the spreadsheet. 
There are ways to work around the row limit using Excel's BI tools, data model, Power Query, and Power Pivot, but no easy way with the standard application. If a dataset is under the row limit but still large (>500,000 rows), Excel may start to slow down when executing basic functions and computations simply due to the amount of data it needs to process. Frustratingly, Excel's user interface will lock up and not allow additional clicks until the task is complete. 
Solution: The first and most obvious solution is to use a smaller data set. You can also try using different programs to shrink your data set, which you can read about here: The Excel Row Limit: 1,048,576 rows. You can also make use of Excel's BI features found in the 'Data' menu:
- Data model - Connects data sources (databases, APIs, CSVs, spreadsheets and other file types) to Excel in the form of data tables.
- Power Query - A graphical user interface to view large data sets as data tables and automate transformation steps to produce clean data sets.
- Power Pivot - A graphical interface to join data sets on matching fields make column level transformations, and create pivot tables and filters.
2. Computer does not have enough memory
When working with large data sets or doing complicated analysis in Excel it is possible insufficient memory (RAM) or processor speed may make Excel feel slow. Other applications, like your internet browser and word documents are also using RAM, which leaves less to allocate to Excel. Additionally, Excel has it's own memory limits. The 32-bit version has a memory limit of 2GB of RAM while the 64-bit version does not have the same limit.
Solution: If a dataset is under Excel's max row count but still feels slow an easy solution would be to try running it on a computer with more memory (RAM). You can also try some of the other options below that will further reduce the memory requirements.
3. Memory intensive computations
The design of certain spreadsheet formulas can cause an Excel spreadsheets to lock up due to the nature of the calculation. There are 2 common types of slow computations: 1) row-by-row calculations in which the value in each subsequent row depends on the value of the previous row (e.g interest rate calculations) and 2) Calculations that include full column looks-ups, like a lookup or comparison function (e.g. VLOOKUPS, XLOOKUPS, COUNTIFS, etc...) These calculations executed on one cell are not a problem but when executed as part of a doubleclick drag across thousands or a million of cell, will require a huge amount of memory and time to execute.
Solution: If a spreadsheet uses these types of functions it is difficult to get around them. With interest rate calculations in particular, there's no easy way to replace them and it may be better to try a bigger computer or another spreadsheet application.
4. Volatile or problematic excel functions
Volatile functions are functions that recalculate whenever any change is made to the spreadsheet, even if the change has no direct impact on the function's result. These functions are a drain on memory because they are constantly recalculating. They are especially problematic when opening a saved Excel file and may cause it to open slowly.
Examples of volatile functions include NOW(), TODAY(), RAND(), and RANDBETWEEN(). When used excessively, these functions can cause a spreadsheet to recalculate constantly, which can make it slow and unresponsive.
Lookup functions: Lookup functions such as VLOOKUP(), HLOOKUP(), and INDEX()/MATCH() can be slow, especially when used with large data sets. In addition, using volatile functions in combination with lookup functions can make a spreadsheet even slower.
Array formulas: Array formulas are formulas that perform calculations on arrays of data instead of individual cells. While array formulas can be powerful and efficient, they can also be resource-intensive and slow down a spreadsheet, especially if they are used extensively.
User-defined functions: User-defined functions (UDFs) are custom functions created by the user. While UDFs can be useful for performing complex calculations, they can also be resource-intensive and slow down a spreadsheet, especially if they are used extensively.
Solution: To optimize the performance of an Excel spreadsheet, it is important to minimize the use of volatile functions and user defined functions (UDFs), especially when applied across large datasets.
5. Full-column references
When writing formulas, it can be convenient to select an entire column of data (e.g. A:A) rather than a range of cells (e.g. A1:A1200). Unfortunately, selecting an entire column of data can be detrimental to the performance of your workbook. When Excel sees "A:A" it will check the entire column, which is additional work if most of the column is blank.
Solution: Instead of full column references, select distinct ranges, like A1:A1200, where possible.
6. Large numbers of formulas
Large numbers of formulas in a spreadsheet can hinder performance. Excel will try to recalculate each formula every time an update is made, causing slow performance. Even if the spreadsheet formulas are simple, compiling thousands of them in one spreadsheet means every time a cell is changes, all the formulas re-execute.
Solution: There are 3 possible solutions.
- If some of the formulas can be replaced by values, for example large columns that were calculated once and won't need to change. Select the cell range, copy, and paste values.
- Use faster formulas like iferror() instead of combining if() and iserror().
- If all formulas are needed, change a setting in excel to only rerun formulas on command rather than with every update. Go to File > Options > Formulas. Under Calculation Options, select 'Manual' for the Workbook Calculation option. Un-check 'Recalculate workbook before saving.' Now press F9 to get Excel to recalculate all formulas.
7. Use of named ranges
Named ranges are a nice Excel feature that gives a name to cell ranges to make it easier to refer to them when writing additional formulas. Instead of "C2:C72380," the range can be named "Sales" and thereafter referred to as "Sales" in any formula. Unfortunately, using lots of named ranges can make Excel run slowly. In small numbers, named ranges are a very convenient feature.
Solution: If a spreadsheet makes use of many named ranges, try rebuilding the spreadsheet without the named ranges and simply refer to sections of data with the standard range reference (e.g. C2:C72380).
8. Use of conditional Formatting
Conditional formatting is an Excel feature that formats cells based on their value. (e.g. scores over 90 are green, scores between 80-90 are yellow, and scores below 80 are red). Prolific use of conditional formatting over very large data sets can cause a spreadsheet to slow down. 
Solution: Remove conditional formatting where possible.
9. Problematic add-ins
Excel Add-ins are pieces of software that run inside Excel but are not part of the baseline program. Add-ins are offered by Microsoft and 3rd parties. Occasionally add-ins cause Excel to run slowly either because the add-in is not optimized for performance or it is adding additional load to Excel.
Solution: Disable unused add-ins. Click through File > Options > Add-Ins > Manage > Excel Add-ins > Go. Next unselect all unused add-ins and click 'Ok.'
10. Graphics acceleration
Some users of Excel 2013 and 2016 found that disabling graphics hardware acceleration improved performance of the application. Note: This is only an issue with older versions of Excel and unlikely to be relevant for most users, who are on Excel 2019 and newer.
Solution to disable hardware graphics acceleration, in Excel go to File > Options > Advanced > Display > Disable Graphics Hardware Acceleration. Then click 'Ok.'
Conclusion
Excel is estimated to be used by 2 billion people worldwide and is the go-to analytics tool for the majority of the world. Over the years Microsoft has built an enormous range of features into Excel to handle every use case encountered by business professionals. Unfortunately, despite the breadth of work Excel supports, there are scenarios in which its performance suffers and the product becomes unusable. This post covered the 10 most common issues causing slow Excel files and their solutions to make Excel faster. However, with Excel's architecture and data storage requirements there will always be performance limitations. If you are looking for a faster spreadsheet, Row Zero is a spreadsheet designed to handle 100M+ row data sets without crashing or freezing because it makes use of bigger computers in the cloud and modern software techniques for faster processing.
Another solution to a slow spreadsheet would be the use of a BI tool, like Microsoft's PowerBI or Tableau business intelligence tools. Both tools are feature rich and powerful visualization and BI tools. These tools are very different from the spreadsheets most people are familiar with and for that reason, were not reviewed in this post.