Pretty Print Excel Formulas?
Asked Answered
I

5

9

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.

Irons answered 18/9, 2009 at 21:41 Comment(0)
T
4

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.

Turtle answered 19/9, 2009 at 0:34 Comment(4)
This is great, thanks! One question, am I supposed to call this from a cell? It doesn't like the [cell].formula part for some reason. If I copy the literal formula though, it works great!Irons
You'd have to tweak it for calling from a cell. As is, you'll just get back the string argument plus some unprintable characters because Excel doesn't treat the CR and LF the way VBA does. The ?ppf([q42].formula) would be what you type in the immediate window in the VBA development environment. '?' is just short for 'Debug.Print', and the brackets are short for 'Application.Evaluate(<string>)', so [q42] evaluates to the range $Q$42. Obviously, you will find that this routine fails to print the way you want in all kinds of cases, but it's adequate for quick inspection of your routine formulas.Turtle
To call it from a cell, change the vbCrLf to vbLf, and set the cell you call it from to display wrapped text. You could also make the function take a range argument and then grab it's formula or formulaArray property.Turtle
Oh, you mentioned calling it from the immediate window at the top of the answer...sorry, missed that. Thanks again for the help!Irons
A
11

Try Excel Formula Beautifier http://excelformulabeautifier.com/. It pretty prints (aka beautifies) Excel formulas.

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

Arciniega answered 29/5, 2012 at 13:59 Comment(3)
This is just for readability, right? You can't paste the formulas with line breaks back into Excel? Anyway, thanks very much for your great tool!Hardshell
With Alt+Return you can add line breaks in formulasCampanile
The Excel Formulat Beautifier is based on a javascript library which contains errors. You have to double check the results.Campanile
T
4

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.

Turtle answered 19/9, 2009 at 0:34 Comment(4)
This is great, thanks! One question, am I supposed to call this from a cell? It doesn't like the [cell].formula part for some reason. If I copy the literal formula though, it works great!Irons
You'd have to tweak it for calling from a cell. As is, you'll just get back the string argument plus some unprintable characters because Excel doesn't treat the CR and LF the way VBA does. The ?ppf([q42].formula) would be what you type in the immediate window in the VBA development environment. '?' is just short for 'Debug.Print', and the brackets are short for 'Application.Evaluate(<string>)', so [q42] evaluates to the range $Q$42. Obviously, you will find that this routine fails to print the way you want in all kinds of cases, but it's adequate for quick inspection of your routine formulas.Turtle
To call it from a cell, change the vbCrLf to vbLf, and set the cell you call it from to display wrapped text. You could also make the function take a range argument and then grab it's formula or formulaArray property.Turtle
Oh, you mentioned calling it from the immediate window at the top of the answer...sorry, missed that. Thanks again for the help!Irons
P
1

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.

Percolate answered 18/4, 2023 at 16:36 Comment(0)
M
0

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
Maples answered 29/4, 2015 at 12:33 Comment(0)
D
0

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.

Derek answered 1/10, 2020 at 14:5 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.