Five Great Tips to Speed Up Excel When Working on Large Spreadsheets
You may have noticed that the larger your Excel spreadsheet becomes, the slower its response times tend to get. From formatting cells to calculating formulas, as well simply waiting for the file to open or save, a slow-moving spreadsheet can be frustrating to use.
Aside from the annoyances, when it comes to business, time is money. The longer you spend waiting for your unwieldy spreadsheet to respond, or needlessly perform repetitive tasks, the more your productivity suffers. That’s why The Excel Experts have put together our five best tips and solutions to help with this problem, and help get you back on track.
Create Multiple Spreadsheets in One Workbook
This may sound obvious, but you’d be surprised how many times some Excel users needlessly push the capabilities of Excel by loading all their data into just one spreadsheet. One of the main ways to keep Excel working quickly is to create multiple spreadsheets in one workbook, and then link between them using three-dimensional formulas.
Use Format Painter
This is a great way to make formatting and reformatting cells quick and painless. With the Format Painter feature, you can do it automatically without taking ages to transfer each one manually. The Format Painter icon is the button in the top left that looks like a painter’s brush. Simply select the cell with the format you wish to use, then click the Format Painter icon, and proceed the cursor to the cells/column/row that you want to replicate the format in. You’ll notice the cursor becomes a bold plus and paintbrush, which you can then drag and drop using your mouse. Your highlighted cells will then replicate the original format, without the need to navigate and select via the menu all over again.
Split Your Screen
By using the Split Screen feature in Excel, you can view and work on multiple sections of your spreadsheet at one time, allowing you to jump between tasks for a more efficient approach. To split your spreadsheet out into up to four views, just position your cursor where you’d like to split the screen then select View > Split from the top menu. You can then adjust the positioning by placing your cursor on the divide line and dragging it across. Once you’ve finished using the feature, just click the same section in the menu to return to normal.
Turn off Automatic Workbook Calculations
Make your large spreadsheet more responsive by turning off the Automatic Workbook Calculations. This means that instead of Excel working out calculations and formulas instantly, you can instead get your spreadsheet to withhold calculations until you decide to do them. This means the document is performing less processes, and should be quicker as you use it. To toggle this setting, just navigate through File > Options > Formulas, then in the first section – Calculate Options Under Workbook Calculation – change the setting to Manual. Finally, uncheck the Recalculate Workbook Before Saving box, and click okay. All you’ll need to do to make spreadsheet calculations now is hit the F9 key once you’re ready to do so.
Upgrade to Excel 64 Bit
If all else fails, a larger solution could be to switch to the 64-bit version of Excel (if you’re still on the 32). One feature of the 64-bit version is to enforce spreadsheet limits which are only based on available memory and system resources, which means a more streamlined operation. Just remember that 64-bit Office programmes only work with 64-bit Windows, and if you want to change one Office programme to 64-bit, you’ll have to upgrade the entire Office suite with it, or the programme won’t run. For more information on upgrading or super charging your Excel, speak to one of our Excel specialists who can give you a full consultation.