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:

Of 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 –

The 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.

The 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.

## Modifying 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:

but 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.

## Conclusion

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.**

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

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?

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?

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

sent you your license ccode by email

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

Hi Charles,

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

https://sites.google.com/site/e90e50/random-topics/tool-for-parsing-formulas-in-excel

Cheers,

Kris

and The FrankensTeam

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