Pretty Print Excel Formulas?

Question

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.

Accepted Answer

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

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

Accepted Answer

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Pretty Print Excel Formulas?

Must Read

5 Answers

Sophia Al-Mansoor