Excel Speedup & Optimization Tips by Experts [Speedy Spreadsheet Week]
As part of Speedy Spreadsheet Week, I have emailed a few renowned Excel experts and asked them to share their tips & ideas to speedup Excel. Today, I am glad to present a collection of the tips shared by them.
Excel Speeding up & Optimization Tips by Hui
About Hui:
Hui (Ian Huitson) has been writing & contributing to Chandoo.org for more than 2 years. Many of you know him from Formula Forensics & Data table related articles on Chandoo.org. See about Hui page for more about him.
In no particular order:
 Minimize the use of Volatile Functions
 Organize your workbook layout and data methodically
 Where possible use fixed values or Named formulas instead of lookups even if the values only change rarely, flag those for manual checking
 Don’t Start equations with a + that actually adds 0.4% calculation time
 Minimize use of the Data Table command to running summaries only at the end of a project
 Review the logic of the model and all if’s or lookup choices for necessity or alternatives
 Use negatives instead of multiple positives where appropriate in conjunction with If’s and Lookups
 Learn about Conjunctive Truth Tables, they Rock for reporting
 Array formulas can do the work of dozens of normal cells, but use cautiously
 Use Named Formulas and UDF’s instead of multiple Helper Cells/Rows or Columns
 Minimize of us Conditional Formatting
 Minimize use of linked workbooks especially if over network drives
 Take an advanced Excel course like the ExcelHero Academy
 Minimize the use of Excel 2007
Links:
Excel Speeding up & Optimization Tips by George
About George:
George runs Excel Unusual, where you can learn about using Excel for engineering, simulations & games. In his work, he builds complex spreadsheet models all the time. So I asked him to share a few tactics with us. He wrote 2 articles in response to my request.
Links:
Excel Speeding up & Optimization Tips by Gregory
About George:
Gregory runs Excel Semipro, where he shares Excel tips & ideas. I asked him to contribute to the Speedy Spreadsheet Week. This is what he says,
Tips by George:
To speed up my worksheet files, I have one primary rule: do not use the OFFSET function, which is volatile and can slow things down considerably. In newer spreadsheets I use Tables and The imposing INDEX function to keep ranges automatically updated. In Excel 2003 I use an eventbased approach, with named ranges, the worksheet deactivate module, and VBA to keep lists and ranges updated.
Links:
Excel Speeding up & Optimization Tips by Luke
About Luke:
Luke is one of the Excel Ninjas at Chandoo.org where he contributed more than 1000 posts. I asked Luke to share some optimization tips based on his vast experience of using Excel & helping others. This is what he suggests:
 In VB, whenever I see a line like Selection.something that’s usually an indicator that I’m using extra lines. Either I need to apply the method directly to the object instead of selecting it, or I need to use a With statement.
 With Event macros, don’t forget the allimportant lines of Application.EnableEvents = False and Application.EnableEvents = True so that you don’t cause multiple events to be triggered.
 See a section of code that you’re repeating? Probably need to make this a separate Sub or Function that you can then reference from the main code.
 When building your formula page, think topdown. Cells near the top of worksheet should be referenced in formulas that are below, not viceversa. XL likes to calculate left to right, top to bottom. Scattering cell references around makes it work harder.
 When using large amounts of data that you want to be charted, sometimes I’ll build a formula sheet within the workbook with data, and then just build another workbook that uses a data query (referencing the formula results) to generate the charts.
 This might be more along the lines of auditing a worksheet, but sometimes it’s hard to see how I’ve laid out my constants and formulas, and using a worksheet map helps bring things into focus (related: create a worksheet map)
Want to become better in Excel? Join Chandoo.org courses
Excel SchoolLearn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks. 
VBA ClassesLearn VBA & Macros stepbystep. Build complex workbooks, automate boring tasks and do awesome stuff. 
Excel Speeding up & Optimization Tips by Narayan
About Narayan:
Narayan is one of the Excel Ninjas at Chandoo.org where he contributed more than 1000 posts. I asked Narayan to share some optimization tips based on his vast experience of using Excel & helping others. This is what he suggests:
PeriodtoDate and Cumulative SUMs
There are two methods of doing periodtodate or cumulative SUMs. Suppose the numbers that you want to cumulatively SUM are in column A, and you want column B to contain the cumulative sum; you can do either of the following:
You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference, it automatically increases for each row.
You can create a formula such as =$A1 in cell B1 and =$B1+$A2 in B2 and drag it down as far as you need. This calculates the cumulative cell by adding this row’s number to the previous cumulative SUM.
For 1,000 rows, the first method makes Excel do about 500,000 calculations, but the second method makes Excel do only about 2,000 calculations.
Subtotals
Use the SUBTOTAL function to SUM filtered lists. The SUBTOTAL function is useful because, unlike SUM, it ignores the following:
Hidden rows that result from filtering a list. Starting in Excel 2003, you can also make SUBTOTAL ignore all hidden rows, not just filtered rows.
Other SUBTOTAL functions.
Using SUMPRODUCT to Multiply and Add Ranges and Arrays.
In cases like weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax for SUMPRODUCT can be 20 to 25 percent faster than an arrayentered SUM.
{=SUM($D$2:$D$10301*$E$2:$E$10301)}
=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)
These three formulas all produce the same result, but the third formula, which uses the comma syntax for SUMPRODUCT, takes only about 77 percent of the time to calculate that the other two formulas need.
Dynamic Ranges
These are most often created using the OFFSET and COUNTA functions , as in the following :
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)1,1)
Sometimes , when data is stored in the form of records , so that all columns have data to the same extent , there may be several dynamic ranges ; say we have ORDER_ID in column A , CUSTOMER_ID in column B , and the AMOUNT in column C. Thus there may be several dynamic ranges as follows :
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)1,1)
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B)1,1)
=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C)1,1)
These can be simplified to :
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)1,1)
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$A:$A)1,1)
=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$A:$A)1,1)
These can then be optimized by storing the COUNTA value in a cell , and using the cell reference within the OFFSET formula :
=OFFSET(Sheet1!$A$1,0,0,Sheet1!$F$1,1)
=OFFSET(Sheet1!$B$1,0,0, Sheet1!$F$1,1)
=OFFSET(Sheet1!$C$1,0,0, Sheet1!$F$1,1)
Where Sheet1!$F$1 contains the formula : =COUNTA(Sheet1!$A:$A)1
For more, refer to MSDN.
Resetting the USED RANGE
Pressing CTRL END will take the cursor and place it on the cell which Excel thinks is the last used cell in the worksheet.
Suppose you do this , and the cursor lands on D27 ; now navigate to any cell which is as far away as you can imagine , say AA3456 ; enter any character , even a space will do ; then clear the cell contents by pressing the DEL key.
Pressing CTRL END will now take the cursor to AA3456.
To reset the USED RANGE , go to the Immediate Window of the VBA Project , and enter the following statement :
Application.ActiveSheet.UsedRange
Your used range should now be reset to its earlier value of D27 ; pressing CTRL END will now take the cursor to D27.
Refer to this Stackoverflow discussion for more.
Excel Speeding up & Optimization Tips by Jordan
About Jordan:
Jordan runs Option Explicit, an Excel VBA blog. He shared these tips with us,
 When reading and writing to ranges, use .value2 (this is noticeable for large, iterative calculations)
 Ensure that ALL spreadsheet errors are handled. The most common errors I see ignored are #Ref errors and #Div (for dividing by zero). Use Go To Special… to find these errors and either delete them or use IFERROR to handle them. In my opinion, Excel errors are one of the biggest contributing factors to slow spreadsheets.
 When using INDEX, include the row or column number even if you don’t need it. For example, if I’m pulling data from only one column, I need only write =INDEX(A1:A10, 1) to pull the first item. However, =INDEX(A1:A10, 1, 1) appears to be a hair faster. Try it.
 Cut down on Lookup functions. In many instances, the lookup table has already encoded information in the correct order. Instead of looking up, say, Stage 2, just use INDEX on the desired column and pull from row 2.
Thanks to Hui, George, Gregory, Luke, Narayan & Jordan
Many thanks to all of you for sharing these ideas & tips so that we can speed up Excel. If you found these tips useful, say thanks to the contributors.
More on Excel Optimization & Speeding up:
Read these articles too,
 Optimization & Speedingup Tips for Excel Formulas
 Charting & Formatting Tips to Optimize & Speed up Excel
 Optimization Tips & Techniques for Excel VBA & Macros
 Excel Optimization tips submitted by our readers
Want to become better in Excel? Join Chandoo.org courses
Excel SchoolLearn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks. 
VBA ClassesLearn VBA & Macros stepbystep. Build complex workbooks, automate boring tasks and do awesome stuff. 
Introducing our Online Power BI Class:
Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.
Click here to know more and join us.
 

Leave a Reply
Happy Ugadi  75 Excel Speeding up Tips Shared by YOU! [Speedy Spreadsheet Week] 
15 Responses to “Excel Speedup & Optimization Tips by Experts [Speedy Spreadsheet Week]”
I'm interested  how come noone has mentioned PowerPivot yet?
Speed up Excel by minimizing the use of Excel 2007???
@Jay
Excel is a lot slower in most areas than 2003, especially charting and graphics useage areas.
If you don't believe me, maybe read http://help.lockergnome.com/office/Excel2003versusExcel2007calculationspeedftopict950956.html
If you have to upgrade go straight to 2010
This is very interesting Hui. Excel 2007 slow!
That explains the pain I suffered a couple of years back when I worked on huge data files with Excel 2007 ðŸ™‚
Thanks for the link. I'll let my peers (using 2007) know about this.
Ravi.
@Hui,
Use Named Formulas and UDFâ€™s instead of multiple Helper Cells/Rows or Columns
Can you extend on this?
Thanks
@ Narayan  thanks for the comma sumproduct trick, I had no idea that the comma version is 77% faster
@ Jordan  like above, had no idea that specifying the column number has the result of speeding things up...
ðŸ™‚
Thanks Chandoo and all the contributors, these kinds of perspectives are pretty rare to get so how awesome it came at us all in one hit!
John
It seems to me that in order to make Excel 2007/2010 calculation effective is to disable or "send away" all the charts and work with large chunks of formulas. Beyond 1 million cells these new versions are fast but unfortunately most of the Excel worksheets have less than that and almost always there are some charts involved. In this case Excel 2003 and earler versions are faster. The charting in 2007 is hopelessly slow especially when there are labels and objects attached to the curve markers. Avoid combining two colors in background of the charting area (use some plain color instead)  this effect is bothersome in 2003 too.
I should have added these, my top five development tips: http://optionexplicitvba.blogspot.com/2011/10/bevisualbasicforapplications.html
For brevity:
1. Change the default font for the editor. I use Consolas.
2. Change the default comment formatting. I use clashing colors that really stick out.
3. Make good use of the Immediate window.
4. Disable automatic syntax check (get rid of those annoying popups!)
5. Always use Option Explicit
Thanks for the excellent topic and tips shared.I want to share a basic tip for beginners for optimizing spreadsheet.
TIP: Check if your excel default setting opens three sheets when a workbook is opened.Change it to one sheet and add sheet if required. This will not only optimize size and speed of excel operations but also speedup other operations like attaching it to email or opening /saving etc.
Keep it simple for best optimization!
This is very interesting Hui. Excel 2007 slow!
@Life Line
Maybe you want to read some commentary by Excel experts about Excel 2007 speed issues here:
http://answers.microsoft.com/enus/office/forum/office_2007excel/excel2007xlsmfilesspeedissues/b8f0ad5a1341430fbedb933212675418
or
http://help.lockergnome.com/office/Excel2003versusExcel2007calculationspeedftopict950956.html
or
http://www.excelbanter.com/showthread.php?t=143818
2 questions for you gurus:
SUMPRODUCT: what's the best way of writing this sort of formula?
=sumproduct(sheet1!D2:O50000,(sheet1!A2:A500000<>sheet2!a1)*(Sheet1!D1:O1="jan"))
EXTERNAL LINKS: what's better?
//folder1/folder2/folder 3/folder 4 or
//folder1/folder2 or simply
f:\
Hi,
I am working with excel 2013, with a new computer (8 CPU / 16Go RAM). It is faster than what I had before, But as I am working on Big sheets, I would like to optimize the all program..
I am sure there are some options that I can switch off, but where, and which ones?
If you have any tips... ðŸ™‚
[…] great question. And there’s some great answers over at Chandoo’s post Excel Speedup & Optimization Tips by Experts from last […]