How to Create Formula in Text Box in Microsoft Excel?

Sharing is caring!

Microsoft Excel users often need to display formulas, results, or dynamic calculations inside shapes or text boxes for better reports, dashboards, and presentations. While a normal text box does not accept formulas directly, there are several proven methods to show live calculations inside a text box.

In this guide, we will explain step-by-step how to create a formula in text box in Excel using simple and reliable techniques.

Why Put a Formula Inside a Text Box?

Adding a formula-driven text box helps you:

• Create clean dashboard titles that update automatically
• Show key metrics (total sales, growth percentage, current date, etc.)
• Build professional reports without extra cells on the sheet
• Make interactive and self-updating summaries

Method 1: Link Text Box to a Cell That Contains the Formula (Easiest & Recommended)

This is the fastest and most widely used method.

1. Click on the worksheet where you want the text box.
2. Go to Insert tab → Text Box (in the Text group) and draw a text box.
3. In any empty cell (for example, cell Z1), type your desired Excel formula.
   Example: ="Total Sales: $"&SUM(B2:B100)
   or ="Report Date: "&TEXT(TODAY(),"dddd, mmmm dd, yyyy")
4. Press Enter. The cell now shows the result.
5. Click inside the text box you created.
6. In the formula bar at the top, type =Z1 (or click the cell Z1).
7. Press Enter.

The text box now displays the live result of the formula. Whenever the source data changes, the text box updates automatically.

Tip: Hide the helper cell (Z1) by changing its font color to white or moving it to a hidden sheet.

Method 2: Use VBA to Assign Formula Result to Text Box (Fully Automatic)

If you want the text box to update without any helper cell, use a simple VBA code.

How to Add the Code

1. Press Alt + F11 to open the VBA editor.
2. Double-click the sheet name in the Project window (e.g., Sheet1).
3. Paste the code below:

Private Sub Worksheet_Calculate()
    Dim tb As Shape
    For Each tb In ActiveSheet.Shapes
        If tb.Name = "MyTextBox" Then
            tb.TextFrame.Characters.Text = "Total Revenue: $" & Format(Range("B100"), "#,##0")
            'Change B100 and the text as needed
        End If
    Next tb
End Sub

4. Go back to Excel, draw a text box, and name it MyTextBox (select the box → type the name in the Name Box next to the formula bar).
5. The text box now updates every time the sheet recalculates.

You can modify the formula inside the code (SUM, AVERAGE, VLOOKUP, etc.) as required.

Method 3: Camera Tool for a Live Snapshot (Visual Method)

The Camera tool takes a live picture of cells and places it inside a text box or shape.

1. Right-click any toolbar → Customize Quick Access Toolbar.
2. Choose All Commands → find Camera → Add → OK.
3. Select the range that contains your formula and result.
4. Click the Camera button.
5. Click on the sheet to paste the live picture.
6. Resize and place it wherever needed.

The picture updates automatically when values change.

Common Formulas People Use in Text Boxes

Best Practices for Formula-Driven Text Boxes

• Always use a helper cell if you are not comfortable with VBA – it is the most stable method.
• Format the helper cell exactly the way you want (currency, percentage, date format) before linking.
• Use named ranges for cleaner formulas (e.g., name B100 as “TotalSales”).
• Keep helper cells in a hidden column (e.g., column ZZ) or on a separate “Settings” sheet.
• Avoid volatile functions like NOW(), TODAY(), RAND() in text boxes if you don’t want constant recalculation.

How to Edit or Remove the Formula Link

• To edit: Click the text box → look at the formula bar → change the cell reference.
• To remove the link and keep static text: Click the text box → press Ctrl + C → Ctrl + Alt + V → Values → OK (or right-click → Reset Text).

Compatibility Note

All methods shown above work in Excel 2010, 2013, 2016, 2019, 2021, Microsoft 365, and Excel for Mac (except some older VBA restrictions on Mac).

By using any of these three methods – cell linking, VBA, or the Camera tool – you can easily create a formula in text box in Microsoft Excel that updates in real time. The cell-linking method is perfect for most users because it requires no coding and works instantly.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.