A few months ago, I had a user call in complaining that Excel would freeze and become completely unresponsive when she was working in a specific workbook. It was fairly large (10MB), but well within what the computer should have been able to handle. I opened a remote session to the computer, and started troubleshooting. My first step, as always in these cases, was to look at the event logs, which showed no relevant entries. Next, I inspected one of the files in question.
The user mentioned that she had a second file, with a lot more information, which was a lot smaller in file size. Comparing the two workbooks, I saw that this seemed indeed to be the case. The smaller file had a number of tabs, all of which had more information contained in them. In the smaller file, she had redone the formatting manually, which led me to google “Excel slows down due to formatting”. A number of results indicated possible issues with data validation, and both files had data validation enabled. We were certainly on to something.
I took another, closer, look at the large file; why was it so large? It hit me that the scroll bar seemed to allow for a lot more scrolling than in the other file. As it would turn out “a lot more scrolling” was somewhat of an understatement. Scrolling to the bottom of the worksheet, I found that the workbook had in excess of one million rows, the vast majority of them empty. Once we’d cleared out those, and resaved under another name, the file was reduced to a far more reasonable 79kb.
In the end, finding the solution was the result of observation, troubleshooting, and checking what issues people have discussed online; all three are bread and butter to support techs.