Excel…the love/hate relationship users have with it is undeniable. Just ask any Advanced Spreadsheet Modeler or Analyst. But, as dataset sizes grow, programs such as Excel and Google Sheets start to slow down. The result? Many headaches and sleepless nights as you struggle to complete your work (and keep your boss off your back). In this article, we’re going to give you the Advil to this constant headache with our top tips on how to make Excel run faster with lots of data.
Let’s get into it…
How to make excel run faster with lots of data? Optimization methods!
Use all available processors
Excel is set by default to use all available cores. But sometimes, it only uses one processor.
Here’s how to check if Excel is using all of the available processors:
- Go to “File” > “Options” > “Advanced”
- Scroll down to “Formulas”
- Make sure “enable multi-threaded calculation” is turned on
- Select “use all processors on the computer”
Disable add-ins
Excel add-ins allow you to extend the application’s functionality. Whilst this can be useful – if you’re not using them, they’re just going to slow you down! Deactivating the ones you’re not using will save a lot of system recourse and increase formula calculation speed.
How to disable Excel add-ins
- Go to “File” > “Options” > “Add-Ins”
- Untick all add-ins you don’t need (you can always go back in and re-add them if you need to)
- Click ‘OK’
Remove any unnecessary formulas
If you have formulas in your spreadsheet that are not being used, remove them. This will help your spreadsheet run faster because Excel doesn’t need to calculate those formulas. So, if you don’t need them, convert them to static values (copy and paste values).
To check which formulas are being used and which ones aren’t, go to the “Formulas” tab and click on “Show Formulas”.
Avoid referencing the entire row/column (A:A)
This one might sound simple, but we see it all the time. Referencing the whole row/ column is a bad practice and should always be avoided.
Whilst you might only see a few cells of data, Excel will check the whole lot anyway! Meaning, that it will take much longer to calculate.
Turn on manual calculation
Using manual calculation doesn’t mean you have to calculate everything yourself (ain’t no one got time for that!). It just means that it will only update when you tell it to, rather than automatically doing it itself.
This isn’t going to make Excel run faster, but if you’re working on a large dataset, it saves time by not having to constantly recalculate over and over again.
How to switch to manual calculations:
- Go to the “Formula” tab
- Click “calculation options” then select “manual”
- Use the F9 key every time you want to recalculate
Steer clear of problematic formulas and functions to make Excel run faster with lots of data
Avoid array formulas
Array formulas can be very useful, but they can also be quite tricky to work with. Plus, Excel has a bit of a tough time calculating large or numerous array formulas as it has to calculate each cell individually.
Instead, consider utilizing the helper columns or separate sheets using standard formulas to replicate the steps taken with the array formula.
Get rid of unused PIVOTS
Using pivot tables is a great way to summarize a large amount of data quickly. Nevertheless, if you only need the pivot table once, use it, and delete it (or copy/paste values). Use formulas instead if you need dynamic data.
Use simple formulas
The simpler your formulas, the faster Excel will calculate them. So, instead of using large complex formulas in a single cell, divide them up into smaller parts across multiple cells.
This will not only make Excel run faster, but it will also simplify your model and make it easier for you (and others) to understand the calculation steps.
Minimize volatile functions
There are two types of functions in Excel.
- Non-volatile – recalculates when a change is made to a particular cell (or connecting cell).
- Volatile – recalculates EVERY time a change is made in the file (even if you don’t want it to)
Some common volatile functions include:
- OFFSET
- NOW
- TODAY
- RAND
- RANDBETWEEN
- INDIRECT
- CELL
For smaller datasets, volatile functions won’t cause you much trouble. But, when working on large sluggish files it can be a bit of a headache waiting for it to update, or worse… crash completely!
Unfortunately, there isn’t really a way around this issue. You either need to turn on ‘manual calculation’ as mentioned earlier. Or, keep your volatile functions to a minimum.
Alternatives to volatile functions
- Instead of using OFFSET try INDEX and MATCH combinations to achieve the same result
- Instead of using TODAY try using the shortcut for entering a static data/ time “CTRL +”
Stop wasting your time trying to force a square peg into a round hole
Ok, let’s be real now. The fact you’re having to read up on tips for “how to make Excel run faster with lots of data” speaks volumes! You’re just trying to do your job. You shouldn’t need to be looking up quick fixes or solutions to a problem that just shouldn’t be happening in the first place!
Excel just isn’t cut out for the job – FACT!
But, there is a way you can solve all of these headaches without needing to implement any of these tips!
How?SheetRocks SaaS is 100x faster than any legacy spreadsheet! It allows you to perform complex analyses of large datasets – without needing to code a thing (or use any of the tips mentioned in this article)!
SheetRocks is still in BETA. But, with an increasing number of people requesting access (because they’re fed up with slow, clunky spreadsheets too), we’ve decided to launch a founding members program to welcome people into the rocketship we’ve been building behind the scenes.
As a founding member, you’ll not only get access to SheetRocks’ high-performance SaaS spreadsheet solution, but you’ll also get access to;
- 2x custom done-for-you SheetRocks spreadsheets per month. If you’ve got a problem spreadsheet, let us fix it!
- 3x custom functions or integrations per month. As we’re still in BETA, if you need us to add some functions or integrations that we don’t have just yet – we’ll build them for you.
- 4x live onboarding calls per month to answer any of your questions.
- 2 x 20-minute 1:1 calls per month to help you with any unruly spreadsheets.
- Access to the Odyssey Crew Membership – a place to network with fellow spreadsheet rockstars.
- And, more!
This is a limited-time offer; once we hit 2,000 members, we will be closing our founding member’s white-glove onboarding program. So, act quickly if you want to take advantage!
For the full deets, and to join us on the rocketship click here!
