Sometimes, we have those days
We get to the office in the morning, coffee in hand, but we’re sluggish. We feel like the world is moving 10 times slower!
Excel has those sluggish days too (too often if you ask me), and when it happens your day can get derailed. However, there are things you can do to help minimize these performance issues, and I’ll show you how!
Phantom Data
First thing you need to do to optimize your sheet’s performance is check whether you have data within cells that you think are blank. Here’s the thing, sometimes while you’re busy grinding away at that super important analysis you inadvertently format cells you didn’t intend to (highlighting the entire row, for example) or Excel reads data in cells that used to contain data. I call this ‘phantom data”…
This phantom data has the ability to grow the size of your file to larger than it should be. You must get rid of this data by:
- Find the last row of data
- Highlight the row below the last row of data
- Press CTRL + SHIFT + DownArrow
- Press CTRL + – (minus)
- Find the last column of data
- Highlight the column after the last column of data
- Press CTRL + SHIFT + RightArrow
- Press CTRL + – (minus)
Stop Daisy Chaining Your Formulas
We all know what happens when you stand domino tiles in front of one another and tip the first one over. A daisy-chain reaction occurs where, one after another, the other dominoes fall over.
More dominoes mean it takes longer for the daisy chain to complete.
When you daisy chain Excel formulas together it can have the same slow progression affect on a Excel spreadsheet. Daisy chaining formulas is where the result of one affects the calculation in another cell, which affects the calculation of another, and another, and so on. The more data you’re working with, the larger the impact is on your spreadsheet and the slower it becomes.
Next time you’re setting up your spreadsheet keep this in mind. If you feel like it’s necessary to daisy chain your formulas, consider using a pivot table to calculate the values you need and use the GETPIVOT function to access those values.
Avoid using volatile functions
The word “volatile functions” conjures up mental images of hourglass icons spinning into oblivion.
Excel has two types of functions, and most fall under the non-volatile category. These functions only get recalculated when a cell that affects, or is affected by, that function changes. Efficiency down to the core…
However, when you use a volatile function, like TODAY(), Excel will recalculate and update the result of this function no matter which cells you modify in your sheet. As you can imagine, this could potentially cause a huge problems for you. What’s worse, many actions trigger Excel to update the calculation of these functions:
- Deleting/inserting rows or columns.
- Filtering/unfiltering.
- Adding, editing, or deleting a defined name.
- Renaming a worksheet.
- Changing the position of a worksheet in relation to other worksheets.
- Hiding or unhiding rows
There is almost nothing you can do to avoid this (except turning off automatic calculations).
So, what are examples of the volatile Excel functions? Here is a list:
- RAND()
- NOW()
- TODAY()
- OFFSET()
- CELL()
- INDIRECT()
- INFO()
- RANDBETWEEN()
If you are working with a relatively small set of data then this isn’t as big of a problem. If you’re working with a historically slow sheet and you’re using one of the functions in the list above, consider using alternative functions to get the job done.
For example: instead of using TODAY(), consider using the Excel shortcut for entering a static date and time:
Ctrl + ;
Consider upgrading Excel to 64-Bit
Excel currently (post 2010 versions) comes in two different versions; 32-Bit and 64-Bit. One of the major differences between the two is that the 64-Bit version is twice as capable of processing data as the 32-Bit version. Typically this means your spreadsheets will load and run faster, especially if you’re used to working with large data files. From my experience, the 32-Bit version of Excel usually begins to experience performance issues once file sizes exceed about 20MB. Upgrading may provide a huge boost in performance.
There’s one catch though…
I’d like to point out that before you make any modifications to your system you should first figure out if doing so is the right move for you. You must consider what your hardware configurations are, your operating system, and whether your machine is even capable of running a 64-Bit software application (some aren’t). This all seems more complicated than it is, but I want to make sure you understand that if your system can’t handle the upgrade you could end up doing more damage than you thought.
How do you know which version of Excel you’re running?
- Open Excel
- Choose File in the top left corner, and then select either Account or Help from the list on the left.
- Click on About Excel
- Here, you should be able to see which version of Excel you’re running.
If you’re running the 32-Bit version and you’re interested in upgrading, make sure you do your research. I would start by checking out this article, which fully discusses the advantages and drawbacks to upgrading.
Try avoiding array formulas
Array formulas can be extremely powerful, but using them excessively has it’s drawbacks. Excel has a tough time calculating large array formulas, or too many array formulas at once.
In a nutshell, array formulas can evaluate and apply a formula to each individual cell in a range and produce multiple results. Since an array formula must sometimes calculate the formula on each cell individually, using these powerful functions too often can slow down your sheet.
Consider using a few helper columns or a separate sheet to replicate the steps taken with the array formula by using standard Excel formulas to obtain the same result. Whether this works will depend on your unique case, so you’ll need to really leverage your creativity and problem solving skills.
Use Excel Tables
Have large lists of data in your workbooks? Convert them to Excel tables and watch your sheet perform calculations much more easily. Converting your data to a table makes referencing that data in a formula much easier for you and Excel (and much more intuitive).
The best method to ensure your sheet is optimized fully is to set it up properly from the beginning. Carefully planning how your data is going to be fed into the sheet and how it’s going to generate a result is pivotal to making sure your sheet is fully optimized for performance.
So, next time you’re sitting there staring at this screen…
And you feel like laying a smack-down on your computer, just remember these tactics and you should have a better performing spreadsheet.
