Is there a way to pretty print Excel formulas?

I've got a few worksheets of semi-complex formulas to slog through, so this would make my life a bit easier.

I'm just looking to turn something like this

AC6+AD6+(IF(H6="Yes",1,IF(J6="Yes",1,0)))+IF(X6="Yes",1,0)

into something more readable without manually doing it in Vim or the like. Excel does do color-matching on the parentheses, but it's still all mashed together on one line.

5 Answers

Try Excel Formula Beautifier . It pretty prints (aka beautifies) Excel formulas.

(I help maintain this, always looking for feedback to make it better.)

1

This VBA code will win no awards, but it's O.K. for quickly looking at typical formulas. It just does what you'd do with parens or separators manually. Stick it in a code module and call it from the VBA immediate window command line. (EDIT: I had to look at some formulas recently, and I improved on what was here from my original answer, so I came back and changed it.)

Public Function ppf(f) As String Dim formulaStr As String If IsObject(f) Then Debug.Assert TypeOf f Is Range Dim rng As Range Set rng = f formulaStr = rng.Formula Else Debug.Assert VarType(f) = vbString formulaStr = f End If Dim tabs(0 To 99) As Long Dim tabNum As Long tabNum = 1 Dim tabOffset As Long Dim i As Long Dim c As String For i = 1 To Len(formulaStr) c = Mid$(formulaStr, i, 1) If InStr("({", c) > 0 Then ppf = ppf & c tabNum = tabNum + 1 tabs(tabNum) = tabs(tabNum - 1) + tabOffset + 1 tabOffset = 0 ppf = ppf & vbCrLf & Space(tabs(tabNum)) ElseIf InStr(")}", c) > 0 Then tabNum = tabNum - 1 tabOffset = 0 ppf = ppf & c & vbCrLf & Space(tabs(tabNum)) ElseIf InStr("+-*/^,;", c) > 0 Then tabOffset = 0 ppf = ppf & c & vbCrLf & Space(tabs(tabNum)) Else ppf = ppf & c tabOffset = tabOffset + 1 End If Next i End Function

If you call it like so:

?ppf([q42])

you don't have to worry about escaping your double quotes and so on. You'll get output that looks like this:

AC6+ AD6+ ( IF( H6="Yes", 1, IF( J6="Yes", 1, 0) ) ) + IF( X6="Yes", 1, 0)

You can also call it with a plain string.

4

This version of the above code snippet now also handels quoted characters differently, which means, it leaves them alone and doesn't let them effect the indenting if they are inside a string like:

"This ({)},;+*-/ won't lead to a linebreak"

It is controlled by the boolean variable bInsideQuotes. It also uses 

.FormulaLocal

to make it possible for Endusers to see something they know.

Public Function prettyPrintFormula(f As Variant) As String Dim formulaStr As String Dim ppf As String If IsObject(f) Then Debug.Assert TypeOf f Is Range Dim rng As Range Set rng = f formulaStr = rng.FormulaLocal Else Debug.Assert VarType(f) = vbString formulaStr = f End If Dim tabs(0 To 99) As Long Dim tabNum As Long tabNum = 1 Dim tabOffset As Long Dim i As Long Dim c As String Dim bInsideQuotes As Boolean bInsideQuotes = False For i = 1 To Len(formulaStr) c = Mid$(formulaStr, i, 1) If InStr("""", c) > 0 Then bInsideQuotes = Not bInsideQuotes End If If InStr("({", c) > 0 And Not bInsideQuotes Then ppf = ppf & c tabNum = tabNum + 1 tabs(tabNum) = tabs(tabNum - 1) + tabOffset + 1 tabOffset = 0 ppf = ppf & vbCrLf & Space(tabs(tabNum)) ElseIf InStr(")}", c) > 0 And Not bInsideQuotes Then tabNum = tabNum - 1 tabOffset = 0 ppf = ppf & c & vbCrLf & Space(tabs(tabNum)) ElseIf InStr("+-*/^,;", c) > 0 And Not bInsideQuotes Then tabOffset = 0 ppf = ppf & c & vbCrLf & Space(tabs(tabNum)) Else ppf = ppf & c tabOffset = tabOffset + 1 End If Next i prettyPrintFormula = ppf End Function

I just solved the issue by using the VS Code sql-formatter extension. I simply paste my formular to a sql file and format with the sql-formatter.

When I put this to file

{=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),"")}

It comes out like that:

{ = IFERROR( INDEX( NAMES, SMALL( IF(groups = $ E5, ROW(NAMES) - MIN(ROW(NAMES)) + 1), COLUMNS($ E $ 5 :E5) ) ), "" ) }

Its not perfect but acceptable for my taste. And this can be copied and used back into Excel / google sheets.

In 2023, by far the easiest way to do this is to install Microsoft's own Advanced Formula Environment. As well as pretty printing formulas (which can then be copy-pasted into the regular formula bar), it has a host of other features. Go to 'Insert' tab, 'Get Add-ins', search 'Excel Labs'.

I'll also heartily recommend the 'Formula Forge' add-in as a replacement for Evaluate Formula. It makes difficult formulae much, much easier to evaluate.