How to Make Excel Faster

Excel's limits on data size and performance can make Excel very slow when working with large, complex spreadsheets. If you want to speed up Excel spreadsheets, you have two options:

1. Use a more powerful spreadsheet like Row Zero
2. Optimize your spreadsheet to make Excel faster

In this post, we'll review both options. We'll provide 10 tips to make Excel faster and we'll also show how Row Zero is 1000x more powerful, and faster than Excel for big data work.

Option 1 - Use a more powerful spreadsheet

Row Zero is an enterprise-grade spreadsheet built for big data and speed. The free plan supports millions of rows and Enterprise plans support billion row spreadsheets (1000x Excel's limit). It's super fast and connects live to your data warehouse, so you can build live pivot tables, charts, models, and dashboards. Row Zero works like Excel and Google Sheets, so it is a good Excel alternative for big data users. Things that take minutes and hours in Excel take seconds in Row Zero.

Compare Row Zero vs Excel vs Google Sheets.

Option 2 - Optimize within Excel limits

There are several strategies to speed up Excel, but it's important to first understand the fundamental limits of Excel. As you approach these limits, Excel may slow down or crash.

Excel's limits

Excel was first built in 1985. While it has received major upgrades over the last 40 years, it wasn’t designed to handle modern data sizes or cloud data infrastructure. Working with big datasets, connecting to cloud data sources, and building complex models can all push Excel to its limits. Here are some key limits to be aware of:

• Excel row limit - 1,048,576 rows
• Excel column limit - 16,384 columns
• Memory limits - 32-bit Excel has a 2GB limit. 64-bit Excel has no hard limit, but will be limited by your computer's RAM since it leverages your computer's local resources.
• Limits connecting to data sources - You'll likely face stricter limits when connecting to data sources. For example, exporting data from Power BI to Excel has a 150,000 row limit and live connections between Power BI and Excel are limited to 500,000 rows.

Here's how Excel's limits compare to other spreadsheet limits:

10 ways to speed up Excel

Here are 10 strategies to make Excel run faster:

1. Optimize your formulas
2. Use helper columns
3. Switch to manual calculations
4. Use pivot tables instead of formulas
5. Reduce or remove formatting
6. Make your data smaller
7. Upgrade to 64-bit Excel
8. Reduce or remove connectors, add-ins, etc.
9. Optimize computer resources
10. Upgrade your computer

1. Optimize your formulas

Excel's built-in formulas are powerful and easy to use, but they can quickly be a drag on resources and make Excel very slow to update. In a few minutes, you can drag formulas across thousands of cells, build nested formulas, and write formulas referencing formulas that reference yet more formulas. Excel recalculates formulas every time an update is made, so more formulas will slow Excel performance. And some formulas are particularly problematic. Here are tips to make Excel faster by optimizing formulas:

• Replace formulas with values where possible. If you have formulas that were calculated once and don't need to change, you can select the cell range, copy, and paste values to prevent Excel from having to run these calculations over and over unnecessarily.

• Use helper columns - Helper columns break down complex formulas and make them more efficient to evaluate. More on helper columns below.

• Use faster formulas where possible. Some built-in functions in Excel are faster than others. Here are some examples:

  • Use XLOOKUP vs VLOOKUP or HLOOKUP
  • Use INDEX-MATCH vs VLOOKUP, especially in large datasets.
  • Use INDEX and tables instead of volatile functions like INDIRECT and OFFSET
  • Use SUMPRODUCT instead of complex array formulas or for multiple conditions.
  • Use AGGREGATE vs array formulas for large data
  • Use CHOOSE vs nested IF statements
  • Use TEXTJOIN vs CONCATENATE
  • Use IFERROR instead of combining IF and ISERROR

• Limit or avoid volatile functions: Volatile Excel functions like NOW, TODAY, RAND, OFFSET, and INDIRECT automatically recalculate every time any change is made to the worksheet, even if the change is unrelated to the formula. This can make Excel very slow, especially in big or complex spreadsheets. Try to limit their use. For example, consider using a timestamp with a manual trigger instead of NOW or TODAY. If you need to use RAND to generate random numbers, you can copy and paste values after the random numbers are generated to avoid continuously re-calculating.

• Limit array formulas, LOOKUPs, and IF functions in big workbooks: Heavy use of array formulas, XLOOKUP, VLOOKUP, COUNTIFS, SUMIFS, etc. over large ranges will also slow Excel performance.

• Switch to manual calculations - By default, Excel automatically recalculates formulas whenever changes are made, so if you have complex formulas that are slowing down Excel, automatic calculations could constantly make Excel run slow or freeze. More on manual calculations below.

• Use pivot tables instead of formulas. A few pivot tables can replace a lot of Excel formulas. More on pivot tables below.

2. Use helper columns to speed up complex formulas

You can use helper columns to speed up Excel calculations and make Excel faster to update. Helper columns are used to assist with calculations that would be too complex, inefficient, or slow if done in a single formula. If Excel is running slow and you have complex formulas, try breaking out some of the formula logic into a new helper column to speed up your workbook.

Here's an example of how create a helper column to speed up a COUNTIF formula:

1. In a new column, write a formula that evaluates your criteria using IF or IFS and returns just 1 or 2 values.
2. Write a COUNTIF where the criteria evaluates only the helper column for this new value. The COUNITF will solve faster since it only has to evaluate 1 unique value instead of many unique values.

Note, you can also use helper columns for pivot tables when you need a column not present in the raw data. In general, pivot tables are more efficient than using many formulas.

3. Switch to manual calculations

As mentioned above, Excel automatically recalculates the sheet whenever changes are made, but you have the option to switch to manual calculations by going to Formulas > Calculation Options > Manual. This gives you control when recalculations occur. Hit F9 to recalculate manually.

4. Use Pivot tables instead of formulas

Pivot tables are a powerful way to summarize large datasets and have built-in calculations and filtering that can be used instead of formulas in many cases. A few pivot tables can replace a lot of Excel formulas and improve performance. For example, you can easily create a pivot table that counts, sums, averages, or finds the median, min, and max values and applies filtering to replace a slow COUNTIF, SUMIF, AVERAGEIF, etc. formula.

5. Reduce or remove excessive formatting

Excessive use of formatting, especially conditional formatting, can slow down Excel. Try to limit conditional formatting to smaller ranges of data and remove formatting rules that aren’t necessary. You can clear formatting by using the "Clear Formats" option under Home > Editing.

6. Make your data smaller

At some point, no matter what you do, your data may be too large for Excel. The Excel maximum row limit is 1,048,576 rows, but Excel can slow down well before that due to data size. There are several things you can try to make your data smaller for Excel.

• Delete unused rows and columns: Excel processes entire columns and rows, even if they are empty.
• Change column references to cell ranges: Full column references can slow performance. When Excel sees "C:C", it checks the entire column even if most of the column is blank.
• Delete unused sheets: Having many worksheets in one workbook can strain system resources and slow down the task you are actively working on. You can speed up your Excel workbook by deleting unused sheets and/or saving them as a separate file.
• Convert data ranges to tables: Excel tables can improve your speed and workflow efficiency. You can convert data ranges to tables in Excel by using the Ctrl+T shortcut or the "Format as Table" button.
• Split your workbook into smaller files: In general, this is a bad idea since it reduces data integrity and increases security risk, but is often the only way to get around the Excel row limit if you have to use Excel and can't use a more powerful spreadsheet like Row Zero.
• Use Excel's Power Query (or another tool) to process big data externally and then import a smaller subset into Excel that better fits within Excel limits.

Ultimately, if you're working with big data, the best solution is probably to switch to a more powerful spreadsheet like Row Zero that is designed to work with big data. Row Zero works like Excel, but can handle much bigger datasets (1000x bigger) and is much faster than Excel when working with big data.

7. Upgrade to 64-bit Excel

If you are still using the 32-bit version of Excel, you can upgrade to the 64-bit version. Upgrading to 64-bit Excel can help make Excel faster for big data sets, especially if your dataset is greater than 300MB or uses complex formulas, macros, or external tools or data sources like Power Pivot or Power Query. While 32-bit Excel is limited to ~2GB of memory usage, 64-bit can access >4GB (up to your RAM size). The 64-bit version of Microsoft 365 is now automatically installed by default unless you explicitly select the 32-bit version before installation.

If you're not sure which Excel version you have, here's how to check.

If you have 32-bit Excel installed, here's how to upgrade to 64-bit Excel:

1. Uninstall 32-bit Office:

   • Go to Control Panel > Programs > Programs and Features.
   • Uninstall your current Microsoft Office 32-bit version.

2. Download 64-bit Office:

   • Sign in at office.com using your Microsoft account.
   • Click on "Install Office".
   • Choose "Other install options" → then select 64-bit under Advanced options.
   • Download and run the installer.

Note 64-bit Excel still has the same row limits but will perform better under those limits. To compare 32-bit vs 64-bit Excel, click here.

8. Reduce or remove external connectors and add-ins

It's increasingly common to use add-ins and connectors to connect Excel to data warehouses, external data tools (e.g. Salesforce, Quickbooks), and BI tools (e.g. PowerBI, Power Pivot). While these can be very powerful, they can also introduce lag, especially when recalculating or updating data. Third-party add-ins can make Excel slower if they are poorly optimized or conflicting. By design, these connections also tend to add much bigger datasets to your Excel workbook.

As a result, you should try to limit their use when possible. For example, rather than having a connector to several different data sources, you could consolidate your data into a central data warehouse and then import from a single source.

To view the add-ins installed and disable unnecessary add-ins, go to File > Options > Add-ins.

Heavy use of VBA and macros can also slow down Excel. In general, the more you add to Excel, the slower it will get.

Ultimately if you want to build out connected spreadsheets with big data, consider using Row Zero, which has built-in connectors to common data sources (e.g. Postgres, Snowflake, Datbricks, etc.), native python functionality, and a lot more power to handle big, dynamic data.

9. Optimize computer resources

Excel relies heavily on RAM to process big datasets. If your computer has insufficient memory (RAM) available, it can make Excel unresponsive or lag. Keep in mind that Excel often is one of many applications competing for your computer's resources. If you have other applications open like a web browser or other desktop apps, those are also using RAM. If Excel is running slow, try closing out other applications, including other Excel workbooks you are not using. You can use Task Manager in Windows or Activity Monitor in macOS to check how much memory and CPU Excel is consuming and close other programs. You can also go there to force quit Excel, if Excel freezes.

Here's how to open Task Manager and/or Activity Monitor:

Windows:

• Press Ctrl + Alt + Delete and select Task Manager.
• To force quit a program, right-click on it, and select End Task.

Mac:

• Press Command + Option + Esc.
• You can similarly force quit any program that is hogging resources.

If Excel consistently maxes out your CPU or RAM, consider upgrading your machine or switching to a true cloud spreadsheet like Row Zero that is not limited by the hardware on a user's computer.

10. Upgrade your computer

As mentioned above, Excel relies on your computer's RAM and processing speed. Underpowered computers can impact Excel's performance. Upgrading your machine can make Excel run faster, but only to a point. Ultimately, if you have a powerful machine, you'll still be limited by the fundamental limits of Excel.

Here are recommended computer specs for big Excel spreadsheets:

1. Memory (RAM):

• Minimum: 8 GB of RAM.
• Recommended: 16 GB or more.
• Why: With 16 GB or more on 64-bit Excel, your system will be able to process big Excel files more efficiently with less slowdowns or memory crashes.

2. CPU (Processor):

• Minimum: Quad-core processor (e.g., Intel Core i5 or equivalent).
• Recommended: Intel Core i7 or i9 (or AMD Ryzen 7/9) with at least 6 or 8 cores.
• Why: Excel is optimized for multi-threading, so a processor with more cores will handle multi-tasking and complex calculations better. Higher clock speeds (3 GHz or more) also improve Excel performance when working with large, complex spreadsheets.

3. Storage (SSD vs HDD):

• Recommended: Solid State Drive (SSD), at least 256 GB.
• Why: Excel gets faster with faster read/write speeds. SSDs drastically reduce file load times, especially for large files, compared to traditional hard drives (HDDs).

4. Excel Version:

• Use the 64-bit version of Excel if you are working with large files (e.g., more than 100,000 rows of data). The 32-bit version is limited to 2 GB of memory, while the 64-bit version can utilize your full memory available.

5. Operating System:

• Windows 10 or 11 (64-bit): Windows tends to be the best platform for Excel, particularly for advanced tasks and features like Power Pivot and VBA macros. Bigger Excel files and advanced features can be slower or unsupported in macOS.
• Why: Excel is optimized for Windows. Try Row Zero if you need a powerful Excel alternative for mac computers since it fully runs in the cloud, independent of operating system.

Ensuring your computer has the recommended specs above will help you maximize Excel's speed and performance. However, when working with large datasets or complex formulas, you'll still be limited by Excel's core limits (e.g. 1,048,576 rows). If you don't want to spend money to upgrade your machine or you still need to make Excel work faster, try Row Zero, which is designed to make big spreadsheets fast.

Conclusion

If Excel takes minutes to update or open, then you can speed up Excel dramatically by making some changes. There are many strategies to make Excel faster. The biggest speed gains will likely come from optimizing formulas and simplifying your spreadsheet as much as possible. Ensure your machine has sufficient resources to handle big Excel workbooks. While it can take some extra work, these tips for faster Excel workbooks can save a lot of time in the long run.

After optimizing Excel for speed, if you find that your spreadsheets are still too slow, try switching to a more powerful spreadsheet that is faster than Excel for big data work. Row Zero is the world's fastest spreadsheet and is built for big data and complex models.

Try Row Zero for free

Related Content

• Dynamic pivot tables and big data
• How to open large CSV files
• 12 Reasons Google Sheets is Slow
• How to build a connected spreadsheet
• How to open parquet files