I’ve been working so extensively on Excel these days, that I cannot pardon myself if I didn’t take the time out ‘now’ to document some of my experiences! And believe me, Excel still surprises how much I can learn again everyday!
Something that I thought I’ll document here today is something tested my very basic logic skills in general – and got me very irritated till I could fix it!
I’m sure a lot more people would have faced this too, it’s just too trivial, but too irritating as well! Here it is –
“I try entering a formula to calculate something adjacent to one of the cells, and what would you expect – the formula to just stare at you even after an ‘enter’?? And that’s exactly what happened! It failed my understanding as to what could be causing this issue – my first instincts was to check if the auto calculate was ‘on’; and it was!”
Thank God for Google and the Internet, for otherwise, I don’t know how long it would take for someone who’s right in the middle of something to unwind and sort out a problem like this!
The problem was a ‘Text” formatting of the cell. There’s another way this problem can persist, it’s if the formatting is ‘CUSTOM’ – and this, not many people have encountered as far as I saw it. In all cases, just re-formatting the cell should work, but that’s where Excel stops being predictable!
Just reformatting does nothing to solve your problem – you have to re-enter/atleast activate the cell using ‘F2 and Enter’ to get the formula to be working! And this would then be needed to be done across all the cells that contain such formatting… Quite a pain I guess!
And it was even more of a pain when I get data in Excel in many tabs, with a lot of data, and I end up wanting to use that information using some reference formulae! You just can’t figure out what went wrong where, when Excel stops being predictable – that’s when you figure out a solution to keep Excel from reaching its limits!
The solution, as I see it, is pretty simple – just replicate every piece of data into a new workbook, ‘but as values’ only! Without any formatting… and now you’re good to go around pushing Excel back to its limits!!!