30 EXCEL POWER TIPS
Discover the shortcuts and tricks used by the pros
Enter your details for immediate access
Did you know that each Excel worksheet has 1, 048, 576 rows?
So imagine my surprise when one day I met someone who ran out of rows!
And not only that, this person ran out of rows every 10 -11 days!
Here's what was happening.
Live energy prices were fed into the system every second, which were eventually fed into his spreadsheet. Custom software analysed the data and automatically purchased or sold power to wholesalers many times each day (a bit like like stocks and shares) in order to make a profit for the company.
And every few days the rows ran out!
By no means is this normal. In fact, hardly anyone reaches that point.
It's not just the sheer volume of data, but when a spreadsheet gets beyond a certain size it becomes frustratingly S-L-O-W. Depending on the complexity of the spreadsheet, this may occur with as few as 10,000 rows.
Excel has a default setting that refreshed the worksheet (i.e. recalculates every formula) whenever any change is made.
Even when you make a small change, you may have to wait a few seconds, or even a few minutes for Excel to complete its processing.
That's when you should turn AUTOMATIC CALCULATIONS off.
You have the option to switch off automatic calculations and control things manually. This speeds things up considerably and gives you more control.
To switch to a manual calculation:
Alternatively, click the Formulas tab and you’ll find Calculation Options on the right side.
Now, the spreadsheet will only update when you say so.
The Update/Refresh key is F9, or you can click Calculate Now on the right side of the Formulas tab.
Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.
Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.