Inserting A New Line In An Excel Cell
Inserting a new line in an Excel cell is something we often come across. In most Office applications, you must press Enter to start a new line. However, with Excel, pressing Enter takes you to the next cell. Inserting a line break is necessary when writing text within a cell in different lines, for example, when entering an email label or importing data from a database.
Below is a text string in cell A1 that we entered without any line breaks. Cell A3 contains the exact text string with two inserted line breaks using Alt + Enter. The example shows that the text looks much better with the inserted line breaks. Let us look up how we can insert line breaks to get text strings with better readability in an Excel cell.
Table of contents
- Top 3 Ways to Insert a New Line in a Cell of Excel
- Insert Line Break in Cell after Certain Character
- Important Things to Note
- Frequently Asked Questions (FAQs)
- Download Template
- Recommended Articles
- The easiest way to insert a new line in an Excel cell is through the keyboard shortcut Alt + Enter.
- Besides, you can also use the & operator and the CHAR(10) formula in Windows to dynamically add a new Line in an Excel cell wherever required in the cell. You can also use the Find + Replace option to add a carriage break and insert lines at particular characters in Excel.
- Check if the Wrap Text option is enabled in Excel for the formulas to work.
Top 3 Ways To Insert A New Line In A Cell Of Excel
There are a few ways to insert a new line in an Excel cell. Some of the most used ways include:
- Using keyboard shortcuts to insert a line break wherever necessary
- Using formulas to enforce a line break wherever needed
- Using a named formula [CHAR(10)]
#1 – Using the Shortcut Keys “Alt+Enter”
The easiest way to create a line break in an Excel cell is using the Alt + Enter keys in Windows. Below is a table containing some text in a single line.
- Step 1: Double-click on cell A1, place the cursor where you want to enter a line break, and press Alt + Enter. It is done by holding down the Alt key and then pressing Enter.
- Step 2: This inserts a line break at the point where we place the cursor.
- Step 3: Repeat this process wherever you are starting a new line in an Excel cell in the text.
You can also place the cursor in the formula bar at the location where you need the line break and press Alt + Enter.
Note: In macOS, you may press CONTROL + OPTION + RETURN for the same results.
Note that Wrap Text in Excel is automatically enabled for the cell when you press the Enter key. If you click on Wrap Text again to disable it, the text again appears in a single line.
#2 – Using the “CHAR(10)” Formula of Excel
Pressing the Alt + Enter key is easy when you do it a few times in a cell. However, for larger datasets, it becomes tedious to use Alt + Enter each time to insert a new line in an Excel cell. Instead, you can opt for Excel’s CHAR(10) formula to dynamically enter a line break.
Below is a table that contains the details of some employees of an organization. Here, Column A has their name, Column B their designation, Column C their location, and Column D is their email ID. You must append all the column data and give appropriate line breaks to get their email signature.
- Step 1: Let us introduce a new column E to print each person’s email signature. Here, you can use the “&” symbol separated by a space “ “.Let us first try to concatenate the data in Columns A and B.
As observed above, the formula =A2 & “ “ & B2 appends the data in columns A and B with space as a separator.
- Step 2: Apply the formula to concatenate the rest of the columns. We write the formula in cell E2 as
=A2 & ” ” & B2 & ” ” & C2 & ” ” & D2
Press Enter and check the result.
- Step 3: Now, you have all the data of the different columns concatenated into a single column. However, we expect a different result. Therefore, we must use CHAR(10) in the formula to add line breaks at appropriate places, enabling the addition of a new Line in an Excel Cell.
We can modify the above formula as
=A2 & CHAR(10) & B2 & CHAR(10) & C2 & CHAR(10) & D2
Please note that the spaces separator “ “ has been removed since we will print each column in a new line. Enter the above formula in cell E2 and press Enter.
- Step 4: You can now drag the Autofill handle from E2 to E7 to apply it to the other employees.
It is a New Line in an Excel Cell shortcut method. Remember that CHAR(10) works only when the Wrap Text feature is enabled. Else you get all the data in a single line.
Wrap Text can be enabled by clicking on it in the Alignment Group of the Home tab, as shown below.
Line Feed And Carriage Return
To understand what CHAR(10) means, read on. Excel uses Line Feed and Carriage Return to enter a new line. Here, CHAR(10) and CHAR(13) return a Line Break and Carriage Return, respectively. A carriage return was made for earlier versions, such as typewriters, to take the cursor to the beginning of the same line, unlike Line Break, which takes the cursor to the next line.
Different operating systems consider line breaks differently. For example, only Carriage Return does not provide a new Line in an Excel cell. However, combined with the Line Feed, it gives a line break.
#3 – Using the Named Formula [CHAR(10)]
Naming your formulas makes it easier to maintain them. Let us look at an example where we can use a name for the CHAR(10) formula.
Below is a table with some famous comic characters(Column A), the comics in which they appear(Column B), and the year of their first appearance(Column C). We must combine the three columns’ data and insert a New Line in an Excel cell after each column’s data. Add a fourth column, namely Details (Column D). Normally, the formula to add a line break is:
=A2 & CHAR(10) & C2 & CHAR(10) & B2
We are printing the character name followed by the year of their first appearance and then the comic’s title. Hence, in the above formula, A2 is followed by C2, then B2.
Now, we can make CHAR(10) a named formula to make it easy to use. Assign any name, LB in this case, to CHAR(10). Thus, it becomes a named formula that you can easily use anywhere in your sheet.
- Step 1: To make CHAR(10) a named formula LB, go to the Defined Names group in the Formulas tab.
- Step 2: Click on Define Name, and a dialog window New Name appears. Enter the function’s name in the Name box in excel, a comment on the named formula in the Comment box, and the formula in the ‘Refers to’ box. Press Ok.
- Step 3: Now, you can use the formula name to print the data in a new line within a cell in the following way.
- Place the cursor in cell D2 and start entering the formula =A2 & CHAR(10) & C2 & CHAR(10) & B2.
- Instead of CHAR(10),go to the Use in Formula option in the Defined Names group in the Formulas tab.
- Select the formula name LB and continue entering the formula as seen below.
- Step 4: Press Enter. You get the output as required with each column data appearing in a new line.
- Step 5: Drag the Autofill handle to apply it to all cells from D2 to D5.
Insert Line Break In Cell After Certain Character
Let us look at a simple method for inserting a line break after a specific character. There will be instances where you must add a line break for a group of cells at particular points.
In the table below, you have some data separated by commas (,). You can use the Find and Replace option in Excel to create line breaks at the commas.
- Select the cells where you must add the line break for a particular character, comma in this case.
- Press Ctrl + H to open the Find and Replace dialog window.
- Add a comma followed by a space (, ) in the Find What box because we have a space after the comma in the selected cells.
- In the Replace With box, place the cursor and press Ctrl + J(inserts line breaks). Press Replace All.
You get the result as follows.
Thus, you can insert a line break after any character in a group of cells using the Find and Replace option.
Important Things To Note
- You can use a keyboard shortcut or Char(10) as a formula to insert a new line in an Excel cell.
- When the Wrap Text option is enabled, you can insert a new line using the Keyboard shortcut method or the CHAR(10) formula.
- Using a named formula for CHAR(10) enables you to use it easily anywhere when starting a new line in an Excel cell for large amounts of data.
- You can insert a new line in an Excel cell in Windows using CHAR(10), while it is CHAR(13) in Mac.
Frequently Asked Questions (FAQs)
To insert a new line in an Excel cell, you can use the Alt + Enter key combination. Double-click the cell where you want to insert multiple lines. Press Alt + Enter numerous times to enter multiple lines in one cell. You can also use the CHAR(10) formula to get many lines.
Place the cursor at the position you want to enter a new line/break a line and press Alt, holding it down, followed by the Enter key. Thus, a new line in an Excel cell is inserted, thereby changing the line.
To insert a new line in an Excel cell, you can use the keyboard shortcut Alt + Enter, the CHAR(10) formula, or the Find and Replace option. But, if any of these do not work, you need to enable the Wrap Text option. Therefore, a new Line in an Excel Cell not working.
This article must help understand the New Line in an Excel Cell ways and examples. You can download the template here to use it instantly.
This has been a guide to New Line In An Excel Cell. Here, we explain the 3 top methods to do it along with step by step examples. You can learn more from the following articles –