When someone asks, “How do I add a line break in a formula?”, it can mean one of two things:
- How do I make a long formula easier to read by splitting it across multiple lines in the formula bar?
- How do I insert an actual line break (like pressing Enter) inside a formula to control how the output is displayed?
In this post, I’ll walk you through both!
Line Breaks in the Formula Bar
Let’s start with making formulas easier to read. Imagine you’ve entered this formula:
=IF(B2=50,”Full”,IF(B2>50,”Too Many Bookings”,”Spaces”))
This formula works fine, but what if you had more conditions? Nesting multiple IF’s quickly becomes messy and hard to read. To solve this problem you can insert line breaks in the formula bar to split your formula into multiple lines:
=IF(B2=50,”Full”,
IF(B2>50,”Too Many Bookings”,
“Spaces”))
To do this, click into the formula bar and wherever you want a line break, press ALT + ENTER (Windows) or OPTION + ENTER (Mac)
This doesn’t change how the formula works, it just makes it easier for you (or someone else) to read and understand. If you need more space to see your formula, click the little down arrow at the right edge of the formula bar to expand it.
Adding a Line Break in the Formula Result
Now let’s move on to inserting a line break into the result of a formula so Excel actually displays a new line in a cell.
Let’s say you’re building a dashboard for a company that offers Excel Training. E1:E20 contains the number of bookings on each course. In C1 you want to display:
Total Bookings to Date
[The number of bookings]
In C1, enter this formula:
=”Total Bookings to Date” & CHAR(10) & SUM(E1:E20)
CHAR(10) is the character code for a line break (new line). This formula joins the text Total Bookings to Date to the sum of E1:E20 but inserts a line break between them.
You will need to turn on Wrap Text for C1 so the new line actually shows in the cell.
Want to see this in action? Check out the video…
Download a copy of the file(s) used in this video: https://share.zight.com/OAu127B1