arrow47 Comments
  1. pavithraravindar
    Mar 03 - 8:53 am

    Very useful Tips, Chandoo Tips will Always fantastic…

  2. Desire
    Mar 03 - 12:02 pm

    What can replace DIRECT function

    • Michael (Micky) Avidan
      Mar 03 - 2:57 pm

      Assuming you mean (I)NDIRECT and you are using Excel 2007/2010/2013 – have a look at the link:
      Michael (Micky) Avidan

    • Jeff Weir
      Mar 03 - 9:58 pm

      Depending on what you want to do, you may like to consider the reference version of INDEX(), or the CHOOSE() function. See my response below in reply to Perri.

  3. Oz du Soleil
    Mar 03 - 1:03 pm

    Thanks for this. I’d never heard of the dependency tree and smart recalculation. This helps me understand a lot.

    Also, thanks for the warning against setting calc to manual.

    One request that I have is for an explanation of nonvolatile functions that cause Excel to drag. I’ve never used any of the volatile functions in such volume that they hurt performance. But I’ve seen thousands of SUMIFS and VLOOKUP slow and even crash Excel. How are they different from other functions like OR, INDEX, CHOOSE and IFERROR?

  4. Chris Macro
    Mar 03 - 3:15 pm

    Great article Jeff! I am a habitual user of OFFSET (I’m trying to overcome my addiction) with named ranges to make them automatically resize. As you continue your series, I’d be interested in hearing how you handle those situations efficiently.

    • Jeff Weir
      Mar 03 - 9:44 pm

      Chris: if you’re using Excel 2007 or later, use Excel Tables. You may never need create dynamic named ranges again, because Excel Tables are dynamic named ranges. Bullet-proof ones at that!

      If a table can’t handle it, use INDEX. See my sample file. Or google “INDEX Dynamic Range” for great articles like these:

      • Chris Macro
        Mar 05 - 1:58 am

        Thanks for taking the time to provide me with those excellent links Jeff!

      • Kersijus
        Aug 26 - 11:55 am

        Thanks for the article also Tables are a great tool for formatting as well as calculating. However, I have the feeling that these are unfortunately somehow volatile? I am working on a small Excel file (14 tabs, one table on each). And despite avoiding the use of OFFSET/INDIRECT, and working on manual calculation (sorry for this T_T) every time I add a row and use a filter Excel got frozen for 10 to 15 seconds. Am I doing something wrong?

        • Kersijus
          Aug 26 - 12:13 pm

          (Maybe a bit of exaggeration there, it’s more like 5 seconds at worst but I still wonder why)

        • Jeff Weir
          Aug 26 - 9:01 pm

          Tables aren’t volatile. Sounds like something else is going wrong. Any chance you can email me the file? If you can, address is

          • Kersijus
            Aug 28 - 8:17 am

            Sorry but the file contains private data. I will look forward with the line management to see if I can send one with random data instead. However I deleted external links and it didn’t change much about it. It also seems quite random: once I waited 30 seconds to see all data from a filtered table, when I did it again right after it was instantaneous, adding rows in a table seems fine but deleting them takes 10 seconds.

  5. Perri
    Mar 03 - 5:42 pm

    Thanks for this. I use Indirect quite often when I build dashboards. It enables me to look up value on different tabs based on the selection. What will be an alternative to indirect?


    • Jeff Weir
      Mar 03 - 9:56 pm

      I’ll be covering this in a future (as yet unwritten) article.

      If the different ranges are on the same sheet, you could use the ‘reference’ version of INDEX to say dynamically sum a particular named range based on a number stored in A2, like this:
      =SUM(INDEX((Range1,Range2,Range3),,,A2) )
      …where Range1 etc are named ranges (consisting of one or more cells in each range) and in A2 is a number telling Excel which of those three range names you want the INDEX function to return

      For instance, say we have three named ranges: Sales, Forecast, and Variance. And lay we have a picklist in cell A1 where a user can choose either ‘Sales’ or ‘Forecast’ or ‘Variance’. And in A2 we have an IF or VLOOKUP or even CHOOSE function that returns 1 if the user selects Sales, 2 for Forecast, and 3 for Variance.

      Then we have a formula like this:
      =SUM(INDEX((Sales, Forecast,Variance),,,A2))

      …which dynamically returns the sum of the range that the user selects with the picklist.

      If the ranges are on different sheets, choose can do the same thing with this formula:
      =SUM(CHOOSE(A2,Sales, Forecast,Variance)) as the CHOOSE function also accepts ranges.

      So the CHOOSE function can be better at this than INDEX, because it doesn’t care if your data ranges are on different sheets or even in the same workbook. Whereas try to point index to multiple ranges on different sheets and you will get an error.

  6. Ron
    Mar 03 - 8:04 pm

    Thanks for this interesting intro to optimizing. You give us a list of functions to not use, or use carefully. It would help if in this article you would also include alternatives to the functions. I saw a couple, like hard code dates instead of =Today(), but I would appreciate it if you provided more emphasis on these alternatives. I understand that you can’t go into detail about everything, but at least knowing immediately what the alternative is allows us to do our own research until you can write the rest of the articles in this series.


    • Jeff Weir
      Mar 03 - 9:32 pm

      Ron: as per my article above, if you’re struggling to find a non-volatile replacement for an existing volatile formula, then you can always post a question on the Chandoo Forum asking for some advice on non-volatile alternatives, and you can check out the Alternative Functions tab of the example download file to see some examples of common use of volatile functions, as well as some non-volatile alternatives.

      And you can always google “Non-volatile alternative to [some function].

      Your line I understand that you can’t go into detail about everything, but at least knowing immediately what the alternative is allows us to do our own research until you can write the rest of the articles in this series. made me laugh out loud…you’re asking me to let you know now about what’s in the other articles. The other articles aren’t written yet. So I can’t let you know now, because my time machine broke.

      • Darren Chapman
        Mar 04 - 3:07 pm

        Need help fixing your time machine Jeff!!! I can’t stop laughing out loud in the office!!! that’s hilarious!!

        • Fernando Fernandes
          Apr 03 - 1:16 pm

          that’s funny… he mentioned couple of volatile and the non volatile alternatives…. and he did detail them quite a bit…
          What I would like to eventually see, is a list with all volatile functions…
          This is a great awesome article about volatility of functions …

          Luv’d it, shar’d it, memoriz’d it.

  7. Henk Huiting
    Mar 03 - 10:06 pm

    I didn’t know this, so thank you for the explanation.
    You wrote: “And this dependency tree is saved along with the file itself”. Is there a way with VBA to make this tree visible in a sheet? I know i could use the “blue arrows” to see the dependency but is there a way to show it as a tree or in a table?

  8. Tim
    Mar 03 - 10:44 pm

    “CHOOSE….Whereas try to point index to multiple ranges on different sheets and you will get an error.”

    Oh, this tangent may open up a whole new world of possibilities.

    Thanks Jeff!

  9. Steffan
    Mar 04 - 5:18 pm

    A *lot* of good stuff on this thread.
    I am always using an INDIRECT nested in an INDEX, with the indirect pointing to a named range to summarize elements from several larger tables. (Like INDEX(INDIRECT(“Region_”&A1&”_Office_”&B1),2,1)) I imagine I can sub out CHOOSE for the INDIRECT, (I can even write a macro to do it for me,) but the resulting formula will be very large (at a minimum, 30 named ranges are being used.) Is there any way to get the speed of the CHOOSE with the dynamic features of INDIRECT?

    • Jeff Weir
      Mar 04 - 6:05 pm

      Steffan – nope. That’s the down side of using CHOOSE…you’ve got to specifically identify all the ranges. But that’s what makes it non-Volatile…because Excel can then extract those cell references and use them to amend the calculation dependency tree.

      Or you can use PowerPivot if installed to bring all those separate tables into one PivotTable, and then users can filter that pivottable on the region and office of choice.

      But then you might as well just go with CHOOSE…it will handle up to 254 ranges. If you’re worried about the length of the individual range reference, then you could always assign a short name to each range like “P_1” and reference that instead of the longer Sheetx!A2:Z50

      • Steffan
        Mar 10 - 5:32 pm

        I went the macro route. My workbook has a dashboard driven by offset which relied upon a large table which in turn summarized the results of 30 calculators using named ranges. Each calculator used indirect() to construct the name of a named range which holds the calculator’s drivers. (about 20 drivers are used in their own tables which come from SQL in one big lump, and often there would be the need to add or multiply drivers together, so there would be multiple INDIRECTs() in the calculators.) I wrote a macro to fish out the INDIRECT() clause from the formula, and then used activesheet.evaluate() to convert that clause to its static named range. I ran that against the calculators and got so much speed back that I found that i could leave the dashboard’s offset formulas in place.

        • Jeff Weir
          Mar 10 - 7:10 pm

          Awesome! Another approach is that you could always use a bit of SQL to mash things up, including ranges inside the workbook itself and parameters from the workbook. Although it requires quite tricky VBA, unless you have PowerPivot installed, in which case you can do this natively.

          • Steffan
            Mar 11 - 4:11 pm

            OK, that IS pretty awesome. The macro works to convert the workbooks that I already have in place, but the idea of using SQL and a “remote” connection to the same workbook to do my aggregation is pretty dang awesome and I’ll try it when building new models. Thanks for putting me on that trail!


  10. Matheus
    Mar 05 - 5:45 pm

    Great post!!

    1 question: Does formulas inside arrays and vectors are also volatile? I mean, if a change 1 cell inside a array, does the others are recalculated?



    • Jeff Weir
      Mar 14 - 10:45 am

      Thanks Matheus. Can you describe a little more a hypothetical scenario?

  11. Mark duchesne
    Mar 13 - 4:39 am

    Thanks for the heads up Jeff, this is all new information to me. I often use indirect and offset to create dynamic references/ranges based on user selection. Your article has certainly opened up my mind to explore the alternatives and how I might leverage index/choose a little differently to achieve the same result.

    • Jeff Weir
      Mar 14 - 10:38 am

      Awesome, Mark. People with open minds make the best analysts. Index/Choose are fun functions to get to know. Enjoy.

  12. […] folk. Jeff Weir here. You might remember me from shows such as Handle volatile functions like they are dynamite, Did Jeff just Chart, and Robust Dynamic (Cascading) Dropdowns Without […]

  13. Brian
    Mar 24 - 7:59 pm

    Very little detailed explanation out there on the subject of how Excel goes about calculation via dependency trees etc.

    Thanks very much Jeff for your clear explanation.

  14. Lee
    May 21 - 2:57 am

    Thanks, it is very usful post. I’ve learnt a lot from your websit.

  15. Roger
    Sep 18 - 8:28 pm

    Very useful info on volatile functions, thanks.
    I frequently use offset() for dynamic named ranges in a table. This then allows me to selectively sum one column, depending on the values in another column.
    _col1 = dynamic range of values in column1 (using offset())
    _col2 = dynamic range of values in column2 (using offset())
    sum = sumproduct(_col1*(_col2=”some value”))

    I don’t want to use DSUM , since the selection criteria can’t be encoded into a single cell.

    Is there any better way of achieving this, without the volatile offset()?

    Many thanks

  16. Charlie
    Nov 13 - 6:55 pm

    Hi Jeff,

    Thanks for this. Really useful. I’m not really at the level where I usually worry about these things but am getting there. I’m just dipping my toe into the VBA world. What is the text that I would have to copy for the VBA to hard code the date into cell A1? Tried to grab it from the download but too amateur to be able to work it out.

    Really appreciate your article, explained a lot!


  17. Maarten de Jong
    Dec 21 - 6:30 pm

    Hi Jeff,

    My compliments on article and website.

    Well I have a somewhat different wish: to deliberately include a volatile function that triggers excel to calculate. Why? To mark the active row. I can do it with a macro but the undo option becomes unavailable.

    So I used NOW() in my conditional formatting. Doesn’t work. Then I used it in a separate cell, as I thought perhaps the conditional formatting is unaffected by volatility. Also this doesn’t work; I had to force it by clicking the ‘Calculate now’ option.

    How could I make this work? Or how would you go about it?

    Looking forward to hearing your thoughts, thanks in advance.


  18. Maja
    Feb 04 - 6:43 pm

    Good written article – includes all information I was looking for.
    I’m just wondering, how to rewrite vlookup using non-violate functions without naming the range (not sure if I overlooked it here) – is it possible?

    my sample:

    Any hint would be appreciated.


    • Fernando
      Feb 05 - 11:47 am

      Why don’t you use index/match?

  19. Maja
    Feb 05 - 4:21 pm

    Hi Fernando,
    I rewrote my original formula, because the combination of INDEX/MATCH uses INDIRECT function twice:


    What I want is to get rid of INDIRECT function from my formula, but don’t name the range….

  20. MOJO
    Apr 09 - 6:08 pm

    Is it possible that INDIRECT will just stop working at some point or could OS or other local environmental issues affect it? I’m using INDIRECT in a summary spreadsheet and it was find last month, but not now. I do plan to rewrite, but would like to know why the thing just stopped working.

    • Hui...
      Apr 10 - 2:51 pm


      Can you post a sample of the formula that isn’t working ?

  21. Chip
    Apr 18 - 9:37 pm

    What about data validation using tables?

    If I have a table called tabColors with a column called Color and I want to use it for data validation, I can’t select List and use the formula:


    I have to use the formula:


    Will this cause the cell itself to be marked as volatile? Is there an alternative to INDIRECT?

    Thanks for a great, informative article.

  22. staelens kurt
    Sep 10 - 8:09 pm

    for my job i need to make a visiter list and i want to put in cell H7 current date and in I7 the hour of arival using the following commands
    the problem is that it keeps changing how can i freeze this so that when i need to look it up i will find the date and hour off arrival of that person

Leave a Reply

Mobile Theme