I had a call last week from someone who was struggling with a slightly unusual problem with a spreadsheet and needed a bit of Excel Detective work.

In this case the data wasn’t moving; nothing that he did would change the view he was seeing on the main worksheet. He had data in front of him when the file opened but as he moved the cursors around the screen the data remained still and didn’t scroll over to other rows or columns. He could use the cursors, or the go to command to move to different cells and could see data change in the name box and formula bar but on the main worksheet it was as if the screen was frozen.

Whilst on the call discussing possible causes he mentioned that the text seemed quite big and we worked out that the spreadsheet was displayed at zoomed up by 130%

From there it was quite straightforward to realise what had happened: the screen had been frozen so that the first few rows and first few columns remained visible. This is a simple, but very useful trick if you have large worksheets you wish to manoeuvre around. For example take a spreadsheet where the columns have heading labels in them, you may wish to freeze those so that as you scroll down the rows you can still which column contains what value.

In order to activate the freeze command then (using Excel 2010) click the View tab on the ribbon then click the freeze panes option:

You just then choose the one that you require – whether to freeze just the top row (you would likely use this if row 1 included your headers) or just the first column (or column A, for example if this was a list of names that you wanted to remain in view whilst you scrolled to the right)

If you choose the first option – freeze panes, this will freeze around whichever cell you have selected. For example if you’ve cell C3 selected it will keep frozen rows 1, 2 and 3 and columns A, B and C. All other cells you’ll be able to scroll through using your mouse, the cursor keys or whichever method you’d prefer.

If you’ve not used freeze panes it’s a really useful option, have a go on some of your own data.

Back to the ‘phone call. In this instance the freeze panes option had been used, afterwards someone had increased the scale of the zoom so rather than looking at the screen at 100% it was around 130% larger. I’m not sure if this was accidental or to make the text larger however the effect was that the frozen rows and columns were the only thing now visible on the screen, and the unfrozen rows and columns became hidden.

Excel was still working correctly – the problem was that there were no visible cells on the worksheet which weren’t frozen.

Excel Detective solution: return the zoom to 100% and / or unfreeze the panes,

A slightly unusual problem but a fun one to solve, have you come across anything similar? Use the comments below to let me know, I’d be interested to hear.