Professor Excel

Comments 20

  1. John

    Hi, could you fix up the wording of the fifth section so it makes sense please? (5. Same cell ranges in formulas)
    It looks like some words have accidentally been deleted from the paragraph.

  2. Anbuselvam K


    Can you please explain more about the section 13. Please send some example file for better understanding to split the one file to several.

  3. Mae Chacon

    Thank you very much and its beautiful page,we liked it.

  4. Mayhem


    More often than not volatile formulas, conditional formats and other useful excel features are needed to analyse large data sets. If you stop using the features of excel that make it useful there is no point in using excel. Hence the solution cannot be to stop use the features of excel that makes it useful.

    • Henrik Schiffner

      Hi Mayhem,
      you are right, you should consider well if you want to remove useful and essential features from your Excel workbook. But to my mind, the speeding-up process has several steps: First you need to find out what feature/worksheet/table slows down the calculation. Especially formulas (e.g. lookups) within conditional formatting rules should be evaluated. As the next step you have to decide, if you want to keep that feature and try to optimize less important parts of your workbook. Also you could just temporarily remove or also optimize them. For most features there are alternatives (maybe less beautiful).
      In order to cut a long story short: I still believe you should at least investigate those features you mentioned. Only that way you can make a well considered decision of how to speed up your workbook.
      Best regards,

  5. Miha

    Hello Henrik,

    I have problem also with very slow excel workbook. Can you please be so kind and check what is wrong with this file if I send it to you? I have try almost all your methods. The file is my own product and nobody can explane why is hapening or processing everything so long. Copying of one cell can take to 5 minutes.

    Thank you in advance

    • Henrik Schiffner

      Thanks for the comment. VLOOKUP is not volatile, though, so that it shouldn’t be added to number 11.

  6. Christopher Robinson-Keys

    Thanks for this. After upgrading to Windows 10, I was experiencing a issue with a very large spreadsheet, that was spending minutes recalculating every time I filtered a column. Changing my calculation options to manual has resolved the issue. Cheers CRK

  7. random user

    within item 7 also point out: “that VLOOKUP and the INDEX-MATCH combination are much faster than SUMIFS.”
    You might also note that INDEX(,MATCH()) tends to be slower than HLOOKUP or VLOOKUP,
    while HLOOKUP and VLOOKUP tend to be slower then helper cell=MATCH() followed by =INDEX(,helper cell).

    within item 7 also you point out: “Another example: =MAX(A1,0) is app. 6% faster than =IF(A1>0,A1,0).”
    A further speedy example would be =(A1)*(A1>0) which drops the function all together.

    within item 8 it is worthwhile to remind users that a row of running sums across 60 columns does
    60 function calls with
    1770 [=+(60)*(60-1)/2] additions across the row.
    This is much slower than, say:
    J2: =J1
    K2: = K1+J2
    L2: = L1+K2
    M2: =M1+L2
    which does just 59 additions, with zero function calls, to generate the same running sum.

  8. nader shah haqmal

    how to calculate internet download speed in Excel sheet total time of downloading of some file of movies if we enter the exact speed (ex : 170 kb per sec)

  9. Karl

    Thank you so much for this. #8 was incredibly helpful. I was struggling with unmerging a worksheet with 53k rows and about 30 columns so I could sort as needed. When selecting the entire worksheet to unmerge this took forever. However, #8 gave me the idea to select each column individually to unmerge. This cut the time down to about 5 seconds.

  10. Rowan


    you should change “17. Move data into one sheet”

    I personally have used this technique and found it reduced my programs runtime from an hour and a half to 2minutes 45 seconds. All by merging data from one worksheet to another, and then running my macro.

    I can personally vouch for its effectiveness. And would implore all users who have macros that process a bit of data on one sheet, automatically paste the evaluated data onto another worksheet, then return to the original worksheet for more data processing etc etc to merge all the data. It works really well!


  11. Mr Nick

    re Tip #8 – Does this apply across a sheet as well as a column? For example, if using a vlookup in two columns is there a benefit to using the same range and changing just the reference column or does this make little difference? eg vlookup(A2,’sheet2’$A$1:$F$200,3,false) and vlookup(A2,’sheet2’$A$1:$F$200,6,false) vs vlookup(A2,’sheet2’$A$1:$C$200,3,false) and vlookup(A2,’sheet2’$A$1:$F$200,6,false)

    re Tip #5 – Once manual calculation is on, is there a benefit in calculating prior to saving?

Leave a comment

%d bloggers like this: