Start a New Line Inside a Spreadsheet Cell in Excel | Excel Semi-Pro

Start a New Line Inside a Spreadsheet Cell in Excel

by Gregory on October 2, 2010

Line Break Inside a Worksheet Cell

Sometimes it’s necessary to have more than one line inside a worksheet cell, which is easily done with a line break.

Add a new line by holding down the Alt key while you press enter. It’s the keyboard shortcut Alt+Enter. In Excel 2008 and 2011 for Mac use Cmd+Option+Enter.

Use a Line Break in a Cell

Fig. 1 - Use a Line Break in a Cell with Alt+Enter

A Potential Problem with Using Ctrl+Enter

The line break is the character code 10. While invisible to the eye in Excel, this character may create problems when exported into a database system by prematurely starting a new record every time it’s encountered.

Using Extra Spaces to Create a Line Break (Not Recommended)

I’ve seen Excel users who don’t know about this shortcut so they try and make the lines wrap inside the cell by providing extra spaces between words. Adding spaces may provide a worksheet cell that looks nice and correct, but an export will also include those extra spaces. (See the formula bar below)

Use Spaces to Wrap a Line in a Cell

Fig. 2 - Use Spaces to Wrap a Line in a Cell

Another problem with adding extra spaces is that, when the column width changes it can provide an unpredictable alignment. By looking in the formula bar you can see if there’s a proper line break (fig 1) OR extra spaces that might be problematic. (fig 2)

Expanding the Formula Bar in Excel 2007 and 2010

The formula bar typically doesn’t show more than one line. In the two figures above the formula bar has been expanded. You can do this with the keyboard shortcut Ctrl+Shift+U, which you’ll probably never remember, or you can select the drop-down arrow at the right side of the formula bar.

Expand Formula Bar

Formula Bar for 2003 and 2008

This keyboard shortcut doesn’t work in Excel 2008 or Excel 2008 for Mac, and there’s no drop-down arrow at the right side of the formula bar. When you encounter a cell with more than one line Excel automatically expands the formula bar. No manual intervention required.

This is why some users are stumped after upgrading to 2007 or 2010 and finding they can’t see the entire cell contents in the formula bar. I know I was. 🙂

Related Posts Plugin for WordPress, Blogger...
steve November 21, 2010 at 8:03 am

alt-enter is not giving me a line break in excel 2011, nor is Alt-return, or any combination of ctrl, shift, command keys.

Gregory November 21, 2010 at 8:36 am

Your right. In Excel versions for Mac, 2008 and 2011, you have to hold the Control and Option keys, then press Enter. It took me awhile to figure that one out.

Thanks for the comment.

Thang November 30, 2010 at 7:55 pm

I just want to correct Gregory: in excel 2011 for mac, if you want to enter a new line in a cell, you need to hold both “Control” & “Command” keys & press “Return” (not “Option” key).

Gregory November 30, 2010 at 8:26 pm

Strangely enough both of those keyboard combinations work for me in Excel 2011: Ctrl+Option+Enter or Ctrl+Cmd+Enter

Gregory November 30, 2010 at 8:36 pm

In Excel 2011 Help, there’s a Keyboard Shortcut list in Help, under the category for Entering Data on a Sheet, you can find that to “Start a new line in the same cell” the keyboard shortcut is CONTROL+OPTION+RETURN which is where I got my information.

Tommy September 20, 2011 at 4:47 am

Seems like Cmd+Alt+Enter works fine.

Gregory September 21, 2011 at 8:30 pm

Alt and Option are the same key on a Mac, so yeah.

Jess October 2, 2012 at 8:42 am

Thanks! Not being able to do this was drving me crazy!

Carol November 2, 2012 at 1:47 pm

Which key is the one that is being referred to as “command”?

Gregory November 2, 2012 at 2:01 pm

It’s next to, and on both sides of the space bar on a Mac keyboard. It also has the word command printed on the key.

Dennis December 20, 2012 at 12:16 pm

HELP. I have a list of abbreviation & Acryonyms and the word(s) for that abbreviation.
Can anyone help me with trying to get a new line into a formula?
my currently formula is:
=UPPER(B2)&”: “&B2&” is a data modeling abbreviation/acronym for “& PROPER(A2)
“AI: ai is a data modeling abbreviation for Ab Initio”

Examples (in 2 different columns):
accept = acpt
accounts payable = ar

I need to create a title then a sentence.
acpt is a data modeling abbreviation/acronym for Accept.
——new cell—————–
ar is a data modeling abbreviation/acronym for Accounts Payable.
——new cell—————–

Gregory December 28, 2012 at 1:39 pm

The character code for the line feed is CHAR(13) but that won’t force a line feed in a formula. The best I can tell you now is to use your existing formula, then copy and paste as values, then go into each cell and manually use ALT+Enter to force a line feed within the cell.

A.W. Marczewski March 8, 2013 at 3:13 pm

Use vbNewLine (constant in VBA code – check Miscellaneous constants in VBA Help) – it is system dependent (different in Mac and in PC). However, it won’t work unless you set word wrapping in cell format (the same is true for line break inserted by using keyboard, e.g. Alt-Enter) – you’ll see this non-printable character as a small box (Win), with test wrapping on you get new line and no strange characters.
Alternatively use various combinations of vbCr and vbLf to test/see what works for your system (of course text wrapping must be on), e.g. char(13) & char(10):
function test_CrLf()
test_CrLf = vbCr & vbLf
end function

Takioso May 7, 2013 at 5:20 am

I could not figure out, yet, how to produce cvs files that are suppose to contain several lines with in one value (cell).
Actualy i usse java to export my csv-file, tat works fine, but without he ablility of giving excel 2010 the correct hint to have that certain values with new lines. Always excel thinks it would be a new row.
I already tried using ‘\r’ instead of ‘\n’ wih in he value.
I am workig on a Windows 7 System
can you give a woking excample record how it is supossed to look like?

Thanks a lot in advance and best regards


Gregory May 14, 2013 at 7:21 pm

I’m afraid I don’t know how to create a new line within a cell in a CSV file.

Comments on this entry are closed.

Previous post:

Next post: