Excel Performance Optimization for Large Spreadsheets

A 20,000-row spreadsheet shouldn’t hurt Excel performance or optimization. If Excel can’t open the file, barely scrolls, or crashes, the problem is usually not the amount of data. The slowdown often comes from what the workbook is doing with that data.

Common symptoms look like this:

• The file won’t open, or it takes forever to load.
• Scrolling and clicking feels laggy.
• Excel freezes or crashes during simple edits.

The real reason big spreadsheets get slow

Large sheets that are not optimized get heavy when Excel has to recalculate or repaint too much and that hurts performance. A few common culprits show up again and again: volatile formulas, overloaded conditional formatting, formulas applied to full columns, too many pivot tables, ghost used ranges, and hidden objects.

The good news is you can often fix performance without deleting rows.

Volatile formulas that recalculate nonstop

Some formulas recalculate every time anything changes in the workbook. We callsthese volatile formulas. In bigger workbooks, they can slow everything down.

Common examples:

• =TODAY() for the current date
• =NOW() for the current date and time

Why =TODAY() slows down every row

If =TODAY() sits in a column and fills down thousands of rows, it recalculates constantly. Change one cell anywhere, Excel recalculates it again. That extra work adds up fast.

First, decide if you truly need today’s date on every row.

Fix 1: Fill the formula only to the last row you use

If you need the date in each record, keep it, but limit it to your actual data range.

1. Click the first cell that contains =TODAY().
2. Select only the cells down to your last data row (not the entire column).
3. Fill down to that last row.

This keeps Excel from trying to calculate the formula across a million-plus rows.

Fix 2: Put today’s date in one place instead

If you don’t need the date on every row, move it to a single cell.

1. Insert a couple of rows at the top of the sheet.
2. In column A, enter =TODAY() in one cell.
3. Select the date column you no longer need (for example, column B).
4. Right-click, select Delete.
5. Choose Shift cells left, then select OK.

That removes a lot of repeated calculation, and it can help with Excel performance optimization.

Conditional formatting that adds hidden workload

Conditional formatting is useful, but it can create a lot of background work. The fastest way to see what’s happening is to review all rules for the sheet.

1. Go to Home.
2. Select Conditional Formatting.
3. Select Manage Rules.
4. Set the dropdown to This Worksheet.

Look at each rule and ask, “Do I really need this?”

Delete icon sets you don’t use

Icon sets can be expensive, especially when they cover big ranges.

1. In Manage Rules, find the rule that uses an Icon Set.
2. Select the rule.
3. Select Delete Rule.
4. Select Apply, then OK.

Once the icons are gone, Excel has less to calculate and repaint.

Remove extra color scales that don’t help decisions

If you’re tracking KPIs, Net often matters more than Sales. Net revenue is a clearer signal of overall health than raw sales totals.

1. In Manage Rules, find the color scale applied to Sales (for example, a green scale).
2. Select it.
3. Select Delete Rule.
4. Select Apply, then OK.

Now the sheet highlights what matters, without doing extra work.

Keep the rules that catch real problems

Some rules earn their place. A highlight for values less than zero is worth keeping, because negatives should stand out right away. A net-focused color scale can also stay if it helps you scan results quickly. This will help with Excel performance optimization

Use formulas only where they’re needed

Formulas are the point of Excel, but they can slow a workbook when they run across huge ranges.

A common example is a Net column where:

• Net = Sales minus Cost (often in column G)

If that formula is applied only to 20,000 rows, that’s fine. If it’s applied to the entire column, Excel may calculate it more than a million times.

Check that formulas stop at the last real row

Scroll to the bottom of your dataset and confirm the formulas end where the data ends. If they run far past your last record, trim them back to the true used range.

Paste Values when the data won’t change

If your data is static and you don’t need the formula anymore, convert it to values.

1. Select the formula cells (for example, the Net column range).
2. Right-click, select Copy.
3. Right-click again, choose Paste Values (in the same place or a new column).

This removes the calculations and can speed up future edits and increases Excel performance optimization.

Pivot tables: powerful, but they add weight

Pivot tables can grow file size and slow performance. They also force Excel to scan and update when you open the file, especially if you have many of them.

If you have several pivots doing similar work, keep the one you actually use and remove the rest. Reducing the count can take a noticeable load off the workbook.

Ghost rows and used ranges that make Excel act weird

Excel tracks a “used range.” Sometimes that range expands because of a stray entry, and it doesn’t shrink right away.

To check, use Ctrl + End. It jumps to the last row Excel thinks is used.

In a clean sheet, Ctrl + End might land on row 20,004. If you type something in a lower row (even one character), it might jump to 20,268. The surprise is what happens next: deleting that character doesn’t always reset the used range.

To reset it:

1. Clear the contents of the stray cell(s) you added.
2. Save the file.
3. Press Ctrl + End again and confirm it returns to the true last row.

A quick routine that helps:

• Go to the top of the sheet.
• Press Ctrl + End.
• Confirm the last cell matches your real last row.

Hidden objects that slow down repainting

A sheet can contain hidden images, icons, shapes, or text boxes. Even when you don’t see them, Excel still manages them, and repaints them as you work.

To find them:

1. Go to the Home tab.
2. Select Find & Select.
3. Select Selection Pane.

If you see hidden items, unhide them, then delete what you don’t need.

1. In the Selection Pane, unhide the objects.
2. Select an object (for example, notes, an airplane image, or shapes).
3. Press Delete.
4. Repeat until the list is clear.

Keep your workbook fast over time

Excel performance optimization comes down to one habit: only ask Excel to do work you truly need. Limit volatile formulas, keep conditional formatting tight, stop formulas at your last row, reduce pivot tables, clear ghost used ranges, and remove hidden objects.

If the workbook starts feeling heavy again, revisit those same areas first, they cause most slowdowns. Keep the sheet focused, and give you good Excel performance optimization.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.