The Problem With Excel
You’ve imported your data, built your analysis, and are ready to calculate an answer. You hit ‘Enter’ and it happens, Excel locks up and you are stuck waiting and wondering if the file will update or if the program will crash. It is a terrible feeling. There are a number of reasons why Microsoft Excel might be slowing down or freezing and they are not all entirely intuitive. Excel performance issues may manifest in 3 main ways:
- Excel is processing slow
- Excel is calculating slow
- Excel is slow to open files
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. Below is a round-up the most common reasons why an Excel file might be slow and solutions to each.
-> Row Zero is a blazingly fast, connected, programmable spreadsheet Try it here
The top 10 reasons why Excel is slow and how to fix them
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 may 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 be able show all rows when opening the file.
There are ways to work around the row limit with additional applications, like data model 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: Use a smaller data set or work with a big dataset in another program and paste the final analysis in Excel. (See Row Zero, the spreadsheet for big datasets).
2. Computer does not have enough memory
Excel runs on a user’s local computer. Unlike hosted spreadsheets, like Row Zero, that have access to the compute powers of the cloud, local spreadsheet applications, like Microsoft Excel, use a personal computer’s hardware. 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 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 within Excel’s max row count but still feels slow try running it on a computer with more RAM.
3. Memory intensive computations
The construction of certain spreadsheet formulas can cause an Excel spreadsheet to lock up due to the nature of the calculation. The most common types of slow computations are row by row calculations, like interest rate calculations in which the value in each subsequent row depends on the value of the previous row or calculations in which the value calculated in one cell depends on an entire column of data that must be referenced for every cell. These calculations executed on one cell are not a problem but when executed as part of a doubleclick drag across thousands of cells, will be problematic.
Solution: If a spreadsheet uses these types of functions, consider another way to perform the calculation or try a different application that has more power or is better suited to that type of analysis. (See Row Zero)
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.
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 derimental 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 Number 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.
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 data ranges to make it easier to refer to them when writing additional formulas. Unfortunately, using lots of named ranges can make Excel slow.
Solution: If a spreadsheet makes use of many named ranges, try rebuilding the spreadsheet without the named ranges.
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 can cause a spreadsheet to slow down.
Solution: Remove conditional formatting where possible.
9. Add-Ins Causing Problems
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. Issues with 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
The above issues are not an exhaustive list but should cover the major causes of slow Excel spreadsheets. If you are looking for a faster spreadsheet that can handle large datasets and still feel snappy and responsive, give Row Zero is your answer. Row Zero was designed from the ground up for performance. It runs in the cloud and can easily connect to cloud data sources, like databases, APIs, and enterprise storage services (S3, Snowflake), enabling users to easily import data and build live models and dashboards. Workbooks can be shared and advanced users can write Python to build more complicated analysis.