Excel freeze when I change the data of only one cell

freezemicrosoft-excel-2013

Windows 7 Pro, Office Excel 2013, i7 last generation processor, 8 GB RAM.

I'm working on a project of about 25 MB, a lot of index formulas, tables (not range) and everything is local(the info I use is in the same document).

But I have this only sheet that is slower than the rest (the calculation options are in manual mode always), and in these sheet when I change the number of an specific cell, it freezes for about 40 secs (only if I change the data, not with the calculation), also this cell data is used in only 4 cells of the document in different sheets.

I don't know what is happening because I'm in manual mode with formulas. Also in that same sheet when I try to add new rows, it get stuck too.

I have recently had the same problem and I solved it by copy/paste all my work into a new excel workbook. But after 2 weeks It appeared again.

I also don't have any macro or conditional formatting, the document is on my computer (not a network).

In particular:
When I change the data 1 time it freeze, after this if I dont calculate the sheet again, I can change the data freely with no problem. But when I calculate again and then change again the data of the cell it freeze.

Best Answer

Having lots of index/matching can really slow the worksheet down, especially if it's tons of data. I'm assuming your data is in a table-like format, and not just random formulas everywhere.

Say you have an index/match that is in range A1:A1000. Keep A1 as a formula, but take A2:A1000 and copy/paste special as Values. This will keep a formula, in case you need to run it again, but will greatly speed up the worksheet.

Also, check if you have any Volatile functions which could slow it down as well.

Finally, do you have any Worksheet_Change type events in the sheets? That may also contribute to the size/slow down.

Related Solutions

Excel – Data Validation arrow hidden from freeze panes

Add a column to the right of column C, col width=2 seems to work well. Then move the pane to be next to that new column. It's hokey, but I can't find any other solution.

Excel: How to search multiple workbooks for Cell information

This code will work in the following way:

Open a new workbook wherever you want.
Paste the VBA code on Macro
On Sheet 1 cell A1 put the path to the folder of workbooks, for example: C:\users\yourname\folder\
On Cell A2 the first range for example: J8:Y8 and on Cell B2 the Sheet name: Sheet3
On Cell A3 the second range for example: G8:AC8 and on Cell B3 the Sheet name: Sheet4

The best of this code is that if you have more ranges/sheets to search you can add on the next rows.

It will look like this:

enter image description here

Now, execute the macro and after its execution it will show the results on Sheet2, showing the name of the file and the number of empty cells on each range.

Sub foldersearch()
    Dim wbk As Workbook
    Dim wbk1 As Workbook
    Dim wks As Worksheet
    Dim wks2 As Worksheet
    Dim totaltime As Long
    Dim dtDuration As Date
    Set wbk = ThisWorkbook
    Set wks = wbk.Sheets(1)
    Set wks2 = wbk.Sheets(2)
    starttime = Now()
    wks2.Cells.ClearContents
    dirPath = wks.Cells(1, 1)
    file = Dir(dirPath)
    rowscounter = 0
    Application.ScreenUpdating = False
    While (file <> "")
        If InStr(file, "xls") > 0 Then
            rowscounter = rowscounter + 1
            totalpath = dirPath & file
            Set wbk1 = Workbooks.Open(totalpath, , True)
            rangelist = True
            i = 2
            columnscounter = 2
            While rangelist = True
                thenewrango = wks.Cells(i, 1)
                thenewsheet = wks.Cells(i, 2)
                emptycount = workbooksearch(wbk1, thenewsheet, thenewrango)
                wks2.Cells(rowscounter, 1) = file
                wks2.Cells(rowscounter, columnscounter) = emptycount
                i = i + 1
                columnscounter = columnscounter + 1
                If wks.Cells(i, 1) = "" Then
                    rangelist = False
                End If
            Wend
            wbk1.Close (False)
        End If
        file = Dir
    Wend
    Application.ScreenUpdating = True
    endtime = Now()
    totaltime = DateDiff("s", starttime, endtime)
    a = MsgBox("Finished in" & vbCrLf & totaltime & " seconds", vbOKOnly)
End Sub
Function workbooksearch(wbk1 As Workbook, wksname As Variant, rango As Variant)
    Dim wks1 As Worksheet
    Dim obj As Object
    On Error GoTo HandleError
    Set obj = wbk1.Sheets(wksname)
    Set wks1 = wbk1.Worksheets(wksname)
    emptycount = 0
    For Each c In wks1.Range(rango)
        If c.Value = "" Then
            emptycount = emptycount + 1
        End If
    Next c
    workbooksearch = emptycount
    Exit Function
HandleError:
    workbooksearch = "N/A"
End Function

Related Question

Excel 2016 Freezing when entering data into a spreadsheet with multiple formulas