Do you have a spreadsheet where the calculation time is so long that you routinely use Manual Calculation mode? If so, John sends in a great tip for how to calculate only cells in a selected range. Episode 557 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we have a tip sent in by John Cassetti, John sent in a great idea if you have a question or a tip that you'd like to send in you can either call and leave us a voicemail or just drop me an email Bill at MrExcel.com, and we'll get your tip.
John has a spreadsheet that has 20,000 rows of data all using some IF formula and as you can imagine the recap times for that is just horrendous, so John generally turns off automatic calculation we can do that with tools, options, and then on the calculation tab will set it back to manual now in manual mode basically what happens is you will enter a bunch of numbers than when you want to recalculate the whole worksheet you'll hit the F9 key, but even that if you can imagine that the spreadsheet might take 40,50, 60 seconds to recalculate, John may not want to constantly recalculate the entire spreadsheet, so just to give you an idea here if we change the number just put in a number you'll see that the formula that relies on that number doesn't automatically change we can actually go through and enter a whole bunch of data and then hit the F9 key to recalculate ever thing.
Well my spreadsheet calculation time takes only a second but if your spreadsheets take minutes to calculate Johnson and a great idea basically enter your data that you need to enter and then select the range of cells that you want to calculate so we want to do just these particular rose will go to Edit, replace, and ask Excel to replace every equal sign with an equal sign it's important that you go into the options and make sure that match case and match entire cell contents are turned off, click replace all, and Excel in the process of replacing equal signs with equal signs will calculate just the cells in the selected region, a great time-saving tip if you regularly use manual calculation.
Hey thanks for stopping by we'll see you next time for another netcast from MrExcel.
Today we have a tip sent in by John Cassetti, John sent in a great idea if you have a question or a tip that you'd like to send in you can either call and leave us a voicemail or just drop me an email Bill at MrExcel.com, and we'll get your tip.
John has a spreadsheet that has 20,000 rows of data all using some IF formula and as you can imagine the recap times for that is just horrendous, so John generally turns off automatic calculation we can do that with tools, options, and then on the calculation tab will set it back to manual now in manual mode basically what happens is you will enter a bunch of numbers than when you want to recalculate the whole worksheet you'll hit the F9 key, but even that if you can imagine that the spreadsheet might take 40,50, 60 seconds to recalculate, John may not want to constantly recalculate the entire spreadsheet, so just to give you an idea here if we change the number just put in a number you'll see that the formula that relies on that number doesn't automatically change we can actually go through and enter a whole bunch of data and then hit the F9 key to recalculate ever thing.
Well my spreadsheet calculation time takes only a second but if your spreadsheets take minutes to calculate Johnson and a great idea basically enter your data that you need to enter and then select the range of cells that you want to calculate so we want to do just these particular rose will go to Edit, replace, and ask Excel to replace every equal sign with an equal sign it's important that you go into the options and make sure that match case and match entire cell contents are turned off, click replace all, and Excel in the process of replacing equal signs with equal signs will calculate just the cells in the selected region, a great time-saving tip if you regularly use manual calculation.
Hey thanks for stopping by we'll see you next time for another netcast from MrExcel.
