Excel Slow to Respond – Avoiding Mistakes that Make Excel Slow Down to a Crawl




Everyone hates it when excel becomes slow to respond. So here are some tips to avoid doing things that can slow down excel. Excel 2007 and later versions sport a much bigger grid which tends to amplify poor spreadsheet design. An Excel spreadsheet that seemed a bit slow in Excel 2000 would likely get bogged down to a crawl in Excel 2007 if you, like me, are using the same hardware.

The key to understanding what makes excel slow lies in understanding how excel calculates and recalculates when it first opened and then when subsequent changes are made to the worksheet. At a very basic level if any cell is dependent on another cell for determining its value, the independent cell will be calculated first followed by the dependent cell. So say if cell B1 refers to cell A1, either using a formula (B1 = 10*A1) or a direct reference (B1 = A1), the cell A1 will be calculated first. Since Excel has millions of cells, it internally maintains what is called a dependency tree. So the cells that are independent of other cells would be placed at the top and the ones which are dependent on others, later down the order. The greater the degree of dependence, the later the cell will be calculated. The dependency list is dynamic and as and when new formulas are entered, rearrangement and sorting of the chain takes place. The first time a workbook is opened, the process may take longer (and will be likely slow for complex workbooks) than during initial calculations simply because the first time, Excel has to start from scratch while the second time, it has some benefit of already having created the dependency tree beforehand. Having said that, Microsoft has made (and probably will in future) changes to how these dependencies are calculated and processed in order to optimize performance. However generally, as a rule of thumb, a cell that is dependent on others will be calculated later than the one which is not. Now let’s get back to doing something about that slow excel workbook of ours !

1. Arrange cells in order of their independence from left to right and top to bottom

All else being equal, the cell towards the top and left will be calculated first before cells towards bottom right are processed. With that being the case, it may be important to have the cells that are not dependent on anything else to be placed in the first column followed by cells which have a greater degree of dependence. The last column of your spreadsheet should have the cells which are the most dependent.

order-to-calculation-of-cells-in-excel-slow

Let’s take two scenarios. The scenario towards the left will make excel slow while the one on the right will recalculate slightly faster. I test both the scenarios and found an improvement of around 7% (4.21 secs for the former vs. 3.9 secs for the later) when cells were arranged in the order of dependence (least dependent towards the left and dependent towards the right)

placement-of-cells-in-order-of-dependence-excel-slow

Here’s how you can test this out.

1
2
3
4
5
6
7
Sub dependent_cells()
t = Timer
For Each cel In ActiveSheet.Columns(5).Cells
    cel.FormulaR1C1 = "=RC[-1]"
Next cel
MsgBox Timer - t
End Sub

Depending on you hardware spec, you may want to modify the code.

2. Place everything in a single sheet

This flows from the previous – working across sheets imposes overhead. So if possible, place everything in a single sheet. Start with raw data towards the left, followed by intermediate formulas towards the center and the final out put towards the right – all in the same sheet.

Let’s test this one out. Let’s simply update a column with identical values – in the first one the first column from another sheet and in the second, the first from from the same worksheet. The first piece of code references cells across sheets.

1
2
3
4
5
6
7
Sub across_sheets()
t = Timer
For Each cel In ActiveSheet.Columns(2).Cells
    cel.FormulaR1C1 = "=Sheet1!RC[-1]"
Next cel
MsgBox Timer - t
End Sub

The second piece of code references cells only within the same sheet.

1
2
3
4
5
6
7
Sub same_sheet()
t = Timer
For Each cel In ActiveSheet.Columns(2).Cells
    cel.FormulaR1C1 = "=RC[-1]"
Next cel
MsgBox Timer - t
End Sub

So what’s the improvement. The first one was timed at 13.2 while the second one averaged at 12.4 with a performance improvement of around 6%. Modest, yes … but it keeps adding up.

3. Rename worksheets

order-of-calculation-of-sheets-in-vbaWhen there are more than one worksheet to be updated, the order of calculation is the alphabetical order of the name of worksheet (the one that is shown on the sheet tab). So in this example, all else being equal, the sheet with the label “allan” is calculated first, the one labelled “colin” second, “diana” third and the “neil” at the end. You can change this sequence by changing the name of the sheets by editing the tabs.

Want to test this out? Let’s create a number sheets in the workbook. Let the A1 cells of all sheets refer to a single cell placed in a particular sheet. (Sheet1’s A1 = LastSheet’s A1, Sheet2’s A1 = LastSheet’s A1 and so on and so forth)

1
2
3
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub

Now in the VBA editor, place this code in the ThisWorkbook code window. With this code in place, any time the workbook calculates, you know which sheet has been called. Now change the value of the cell A1 in the last sheet. You will see that the sheet which is labelled earlier in the alphabet will be calculated earlier.

Numbers take precedence over alphabets so if you change the label of the sheet labelled “neil” to “1neil”, it will be calculated earlier. So when you have multiple worksheets, some with data and other with formula and yet a few other with final graphs and dashboards, name the one with data earlier in the alphabet and the other ones later in the alphabet.

4. Place everything in a single workbook

Working across workbooks can make excel slow especially when a lot of references are involved. The order in which you place the data and formulas should be:
1. Everything in a single sheet only (starting with top left and proceeding right & downwards as the dependence of the cells increases)
2. In Sheets named alphabetically (sheets with only data earlier, sheets with formulas later)
3. Only when both the options have been exhausted, should you turn to using references from other workbooks

5. Sort Data

Another thing that can slow down an excel spreadsheet is the lack of sorting. If you are using formulas that have the ability to search across a range or multiple ranges, sort the data. Start with the column used most in a lookup followed by others less used ones. If a vlookup find a match in the first few rows rather than having to go through 30000 rows, it will work much faster. You increase the chances of the former by sorting the data.

6. Remove Unwanted Formulas

This may sound trivial but in many cases, we leave hundreds if not thousands of sumifs, countifs and vlookups loitering around long after they have served their purpose. So convert them to static values and avoid giving your excel workbook a chance to go slow on you.

7. Do not repeat formulas

As simple as it may sound, most of use (including me) have the habit of using the same formula across all the cells in a column. Say we wanted to know the today’s date and then mark cells which are in the past and the ones in the future. More often than not, we end up writing =IF(cell_value < NOW(), "past", "Future") in the first cell and summarily copying it to the entire column. That saves time but if this is repeated across multiple columns in a really large spreadsheet, this can make excel slow to respond.

8. Avoid Using Functions that need to be re-calculated at every step

If you’ve used the RAND() or the RANDBETWEEN() functions, you would know this. Anytime even a single cell value is changed in an excel spreadsheet, all cells within the spreadsheet which contain these formulas recalculate themselves, even when the cell being changed is not referenced by any of them at all. Conditional formatting also has the same characteristic. Throw in OFFSET, ROWS, COLUMNS, INDIRECT into the set and now you know why your favorite excel spreadsheet with thousands to these functions has suddenly become so slow! These functions have to be processed at every re-calculation, becoming a potential resource hog when used generously across thousands of cells.

9. Do not select cells when working with VBA – Assign them to objects

When you record a macro, vba replicates your actions. Oftentimes it results in sub-optimal code generation. For example you recorded a macro to update the values of all cells in a column. If the macro were recorded, you could would have statements like cell.select followed by ActiveCel.value = X. Half of this code is redundant – cells can be directly update without having to be selected. So a statement like cells(1,10).value = 10 would be equivalent to cells(1,10).select followed by ActiveCell.value = 10 and execute much faster. In his post, Dany Hoter, a Product Planner on the Excel team, highlights ways to read and write to a range without selecting the cells. The first approach simply assigns the range to a range object. Any changes made to the range object are reflected in the actual range in the excel workbook. Here is the first example (slightly modified)

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub using_range()
t = Timer
Dim DataRange As Range
Set DataRange = Columns(1).Cells
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
  For Icol = 1 To MaxCols
    DataRange(Irow, Icol) = DataRange(Irow, Icol) + 1
  Next Icol
Next Irow
MsgBox Timer - t
End Sub

Now this is way faster than taking the cell.select followed by ActiveCell.value = X method. However, this still is not the fastest way to read and write to a range using VBA. Here’s Dany’s approach using the variant object type.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub using_variant()
t = Timer
Dim DataRange As Variant
DataRange = Columns(1).Cells
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
  For Icol = 1 To MaxCols
    DataRange(Irow, Icol) = DataRange(Irow, Icol) + 1
  Next Icol
Next Irow
Columns(1).Cells = DataRange
MsgBox Timer - t
End Sub

This one is fast – infact the fastest I have seen so far. Remember to assign the variant object back to the range after your done.

Highly recommended readings

http://msdn.microsoft.com/en-us/library/aa730921.aspx
http://msdn.microsoft.com/en-us/library/aa140058(office.10).aspx
http://blogs.msdn.com/…/more-on-performance-improvements-in-excel-2010.aspx
http://blogs.msdn.com/excel/archive/2009/09/03/performance-improvements-in-excel-2010.aspx


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Peter Petit wrote:

    Using Windows 7 and Excel 2010, I struggled with a very slow Excel, with long delays after each mouse click or keystroke, until one day I noticed that some little purple triangles were slowly populating the upper left corner of rows having formulas. The light went on. I went to File|Options|Formulas, and unchecked “Enable Background Error Checking”. That fixed it.

  2. Rob Heart wrote:

    response to Peter. Brilliant! I like to have multiple spreadsheets open on multiple screens and was tearing my hair out with the slow excel refresh. Killing the background error checking fixed it for me too.

  3. Ted wrote:

    Renaming the sheets was helpful. Thank you

  4. Karen wrote:

    Thank You!!! This has been driving me insane.

  5. Janus wrote:

    I would like to stress the importance of point 1!
    I was working on a VB program and at some point the computation time increased from 1 min to 30 min! After a long debugging session it turned out that a column (not even included in the VB calculations) was calculated from the four neighboring columns. Changing the order in the formula got the computation time back to 1 minut! (eg. =A3+B3-D3-C3 to =A3+B3-C3-D3).
    Another curious thing was that with the old formula my CPU usage was around 12% running the code. With the new formula CPU usage climbed to around 35%…

  6. Eric wrote:

    For slow Excel workbooks try to remove or limit conditional formatting

  7. Paul wrote:

    I ran the procedures “using_variant” and “using_range” on about 20 values (in Excel 2010) to see what the time difference was between them. With using_variant it took about 0.48 seconds. With using_range it took about 0.016 seconds. using_range was about 30 times as fast as using_variant! This is opposite to what the author tells us to expect. Am I missing something?

  1. Excel Bogs down wrote:

    […] Re: Excel Bogs down How many rows are there in each sheet and how man sheets are there ? Are you running any code as well, as if you have a huge chunk of code that had to calculate something before it moves on to the next section than that might also be slowing it down. And i5 should be sufficient for almost most things on excel, unless you are crunching some serious data. Try a few of these and see if they help in any shape or form: Excel Slow to Respond – Avoiding Mistakes that Make Excel Slow Down to a Crawl | Excel & V… […]

  2. Optimize Formula Calculation Speed: Part 10 - Summary - Excel off the grid wrote:

    […] Avoiding Mistakes that Make Excel Slow Down to a Crawl (Data Bison) […]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES