Making sense of complex Formulas: an Indenting Viewer-Editer

Some time ago I was working with a client to speed up one of their workbooks.
I was using the FastExcel V3 formula profiler and it showed that one formula was taking a significant proprtion of the calculation time.

And the formula was too complicated to easily understand. So I decided that FastExcel V3 really needed a better way of understanding, creating and modifying formulas and started developing one. It currently looks like this:

Indent1Of course the trouble with creating a formula indenter is:

  • Nobody agrees what the “correct” indentation style is!
  • And anyway what works well for one formula does not neccessarily work well for another formula.

So I added the ability to dynamically switch indentation styles: for me splitting the OR(…) section by commas makes it easier to read –

Indent2The Viewer-Editer also helps you debug the formula by showing you the result of the selected portion of the formula and by making it easy and fast to jump to and select different parts of the formula.

Indent3The Select options work in conjunction with the navigate arrows (Next left, This, Next right, Expand selection, Contract selection).
So if you click the right arrow with Functions selected the selection jumps to the next function on the right and shows you the result in the evaluate box.

Indent4Modifying the Formula

You can modify the formula by directly editing the formula text and there are also many of the familiar Excel tools built-in:

  • Function Wizard
  • Insert a Reference or a Defined Name
  • Change a reference from Relative to Absolute (F4)
  • Build up a Mega-Formula by inserting a copy of another formula


Clicking the Function Wizard button when a function is selected brings up the function wizard for that function so that its easy to change:

Indent5but if nothing is selected then the Function Wizard is called allowing you to choose a function, enter its parameters, and have it inserted at the current insertion point.


I have added quite a lot of function to the viewer-editer since the original concept, but I am sure it can be improved further.

So please download FastExcel V3 Beta 3, try it out and let me have your comments.



This entry was posted in Uncategorized. Bookmark the permalink.

7 Responses to Making sense of complex Formulas: an Indenting Viewer-Editer

  1. ruve1k says:

    It would be much easier to read if formula arguments would align to the first argument — not the beginning of the function. Thanks!

    • fastexcel says:

      Try using the Each Open and close option, and then check after each comma: that lines up the function arguments, and increases the indent for each nesting level. The downside is it expands quite a lot.
      Maybe I should allow the user to set their own default settings?

  2. Jeff Weir says:

    Very cool, Charles. Can’t wait to upgrade. By the way, what is the upgrade path?

    Also, I purchased a previous version in the past, but for some reason don’t have it installed any more, and can’t even find the email from you with installation info from when I initially purchased it. Any chance you can resend me the product key email?

  3. fastexcel says:

    There cwill be an upgarde path but I have not worked it out yet!

    sent you your license ccode by email

  4. ross says:

    Good job Charles knows what he’s doing Jeff!!! ;-)))

  5. Kris says:

    Hi Charles,
    Maybe you would be interested in an earlier work by our FrankensTeam: we developed a formula parser tool using regular expressions:
    and The FrankensTeam

  6. R Sandlin says:

    Does this work on Excel 2010 Professional 64-Bit? I’m seeing other editors are only 32-bit.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s