How to Insert Line Breaks Correctly in Excel Formulas

Microsoft Excel is the leading spreadsheet software used around the world.

And it stands out for its ability to seamlessly handle complex tasks and formulas. While Excel can handle the backend processing of formulas, it is still important to make sure the formulas remain clear and readable.

A fundamental aspect of data management and formula construction in Excel that is often overlooked is the insertion of line breaks (where needed) 📝

Understanding how and where to strategically place line breaks not only enhances formula readability but also streamlines the debugging process and enhances the readability of spreadsheets.

In this guide, I will walk you through the top three techniques of inserting line breaks correctly in Excel. Download your free practice workbook for this guide here, and come along with me to learn all about line break insertion in Excel.

Table of Contents

Insert line breaks using keyboard shortcuts

What’s better and faster than using keyboard shortcuts to optimize your Excel jobs? ⌨

For Windows

The Windows shortcut for inserting a line break in Excel is the Alt key + Enter key.

For example, if this is a cell in your spreadsheet where you want to enter a line break after each sentence ends:

Step 1) Double-click on the cell to activate it.

Step 2) Within the cell, take your cursor to the point where you want the line break inserted (after the full stop).

Step 3) Press the Alt key + Enter key.

Step 4) A line break will be inserted as follows:

If you want to add more than one line break (for presentation or spacing reasons maybe) 📚

Step 5) Press the shortcut of the Alt key + Enter as many times as many line breaks as you want inserted.

For Mac

If you’re a Mac user, your keyboard must not know what an Alt key is.

That’s alright – the line insertion keyboard shortcut for Mac users is the Control key + Option key + Return key 🗝

The rest of the steps for line break insertion remain the same.

Activate the cell, reach the point where you want to insert the line break, and press the Control key + Option key + Return key together. 

Inserting line breaks in Formulas

We all have seen long, really long, and clumsy formulas in Excel. Like the one here.

The formula has many nested IF statements and since all of these are so closely fitted together, it is hard to decode each successive step of this formula 😒

Good news: you can make this formula clearer and more readable by adding line breaks before each successive nested IF statement.

Step 1) Double-click on the cell containing the formula to activate it.

Step 2) Within the cell, take your cursor before the nested IF statement.

Step 3) Press the Control key + Alt key (or option + command + return key if you’re using Mac).

Step 4) A line break will be inserted as follows:

Step 5) Repeat the same process for all nested IF statements.

Here’s how the formula looks:

Clearer and easier than ever! 🤩

Insert line breaks using the Replace Feature

Apart from entering simple line breaks in a cell, you might be in a situation like below.

I have multiple cells each one of which has multiple sentences. And I want to add a line break after each sentence in each cell.

Doing it manually would eat hours, and who wants to waste so much time on a task as mundane as adding line breaks in Excel? Let’s automate this 🏍

For such situations where you have multiple cells to cater to, and you need to add a line break after any particular character of a cell:

Step 1) Press the Control key + H to launch the Find and Replace dialog box as below.

Each sentence ends on the period (.), then a space character, and then a new sentence starts.

Step 2) In the Find what box, add a period and then a space character (. ).

Step 3) In the Replace box, write a period (.) and then press the Ctrl key + J.

It won’t show up as a character in the Replace box, but something will appear inserted.

Step 4) Click on Replace All.

Ready to go in a “wow” state? Here it is – line breaks are inserted in all cells after every sentence.

Excel replaced each period and space character with a period and a line break.

If you want multiple line breaks instead of one, press the Control key + J inside the Replace box as many times as many line breaks are desired.

Insert line breaks using the CHAR formula

The CHAR function is a special and one-of-its-kind function of Excel that adds special characters in Excel. You give the CHAR function a character code, and it returns the special character against it.

For example, you write in Excel the following formula:

And it returns an exclamation mark.

Since the character code for an exclamation mark is 33.

Similarly, the character code for a link break is 10.

This function comes in handy to insert line breaks when you want to combine multiple cells with line breaks.

For example, I have the contact and address information of some people split across multiple cells.

To send their parcels via post, all this information needs to be compiled in a single cell (with line breaks) and then copied/pasted on their parcels.

Step 1) Merge all the cells using the ampersand operator inserted after each cell to connect a cell with a line break and then the next cell.

Here’s how you write it up in a formula:

All cells merged into one with line breaks after each:

Alternatively, you can use the TEXTJOIN function if you have a huge range of cells to combine:

• It saves time as it allows you to specify the separating character between cells for once as the first argument that we have set to a line break.
• TRUE tells it to ignore any empty cells in between the cell range.
• And then the third argument is the cell range that you want merged in a single cell.

Short formula and simplified results.

Conclusion

How does it feel after you’ve learned the top 3 methods of inserting line breaks in Excel formulas? Must be great and great things must not stop.

So, before you leave, read out the following Spreadsheeto Excel tutorials, too that explain different methods of formatting MS Excel sheets. These are some very common areas of formatting guidance that are required very often.

• Excel Line Spacing: How to Adjust Line Spacing (2024)
• How to Remove Gridlines in Excel in 1 Minute (2024 Guide)
• How to Remove Dotted Lines in Excel (And What They Are!)