If you use Microsoft Excel to organize data (say, a timeline or a list of documents being produced), you may have run into the problem of having more text than will fit into a normal cell. You need to either wrap text like a paragraph or insert line breaks in the middle of the Excel cell, because otherwise the text just breaks out of the borders of the cell and keeps on going:
And if that's not annoying enough, if you have to type something into the cell to the right, then you've just cut off the last part of that other cell:
What you want to be able to do is either (a) have the information in the first cell wrap text in Excel like a paragraph so it appears on multiple lines within that cell or (b) insert a line break like you would in a word processor.
Here's how you do each:
If it really doesn't matter where the line wraps (as long as everything stays within the same cell), then the thing to do is format the cell so that the text wraps automatically. Although the different versions of Excel (from 2002 through 2019) have various buttons and commands to do this, the one foolproof method that works in all versions is this:
- Right-click your mouse inside the cell.
- You'll see a menu that looks like this:
- Once you have the Format Cells dialog box open, go to the Alignment tab and check the box next to Wrap Text:
- The text in that cell will now wrap automatically.
Wrap text in Excel 2007 & up
In the Ribbon-based versions of Excel , this is a one-click operation. Just go to the Home tab and click on Wrap Text:
Good news: You can apply this formatting to more than one cell at a time. You can select multiple cells by holding down the CNTRL key while clicking on them, select entire columns or rows by clicking on their headers (the "A, B, C" on top of columns or the "1, 2, 3" to the left of rows), or even select the entire spreadsheet by clicking on the upper-left-hand corner (where the A and 1 meet). Once you've selected all your cells, then just follow the steps above.
Inserting a line break within a cell
But what if you want to control exactly where the line breaks (say, you want an address to appear like this):
Even easier! After you type each line, just press ALT-ENTER on your keyboard to insert a hard return. (If you're using the Mac version of Excel, it's CTRL-OPTION-ENTER or CTRL-COMMAND-ENTER.)
Controlling vertical alignment
By default, data in cells is aligned at the bottom. This can create some readability problems if some of your cells have multiple lines:
If you have a particular preference as to whether the cell text aligns from the top, the bottom, or in the center, select all the cells you want to re-align, then right-click to get the menu (as we saw above), choose Format Cells, and go back to the Alignment tab.
See that drop-down menu that says Vertical (above)? That allows you to change the vertical alignment within the cells you have selected. If you want everything to line up across the top, then choose Top. If you want everything centered, choose Center. You get the idea, right?
Update: Copying cells that have hard returns
Down in the comments, there's a bit of a controversy about whether you can successfully copy a cell into which you've embedded hard returns to another cell. Some readers are finding that, when they copy and paste, each line ends up in a different cell. There's a reason for that.
Here's a quick guide to both "how to" and "how NOT to" copy-and-paste cells that contain line breaks:
I have 20 lines in a cell spaced using alt-enter. This looks fine if the row height is set to display all lines. If I reduce the row height to display only three lines, the three lines that are displayed are the top three lines. How do I change this to display the last three lines?
This is MS Excel 2010 on Windows XP.
How do I do the “Wrap text”?
@Fred – Frankly, I don’t see any way to do that — none of the alignment options work that way.
FINALLY! I was JUST trying to figure this out the other day!
How did u do that? You mean u managed to display the last 3 lines of the cell rite?
I’ve been trying to figure out how add a hard line break (Though I did not know this was the correct term) within a cell for a while now. Thanks for sharing!
It was driving me crazy that I didn’t know how to insert a line break within a cell. (and I knew the space bar was NOT the answer!). Thanks so much.
How about a way to split cells with hard line breaks into multiple columns? I have a customer who gave me addresses this way and I would like to break them into columns to import into my shipping program.
Pressing alt + enter is not working for me. It’s the way I’ve always done it but for some reason it’s not working on this new computer. Am I missing something?
Nevermind…apparently the alts on each side of the keyboard have different functions. Alt + enter works with the alt on the left side of the keyboard.
I was hoping to do a text-to-columns or global replace. I ended up editing each line, adding a , then deleting the extra line feed. Then I used text-to-columns to break the single cell into multiple ones. It was tedious but it worked.
I don’t understand why people can’t decide on the correct way to represent address data in excel. I have seen it being done the wrong way for over 30 years!
Thanks for your help Gracie. By the way, Gracie is a beautiful name! it is my mother’s middle name.
Anyone know how to take hidden comments and move them to a new cell. Tried a copy, paste special and it just moved comments. also tried a copy paste and it put each line of comment into multiple cells. Any way to copy entire comment into one new cell?
I’m not sure I understand what you mean by “hidden comments.” How did you attach your comments to the cell?
hey when i try to do the ALT enter it just goes to the next cell, am i missing something here?
(a) Did you see Gracie’s comment above? It may be your Alt keys don’t work the same way on both sides.
(b) Not to be insulting, but are you *sure* you’re actually going to the next cell? When you do Alt-Enter, it *looks* like you’re in the next row until you actually exit that cell by hitting Enter. The visual on that is a little confusing until you get used to it. When you say “next cell,” are you seeing the cursor go down or to the right (or left or up)?
How do I paste text with line breaks in one cell? Excel will break the text in multiple rows automatically but that is not the way I want it to behave. A bonus question: can I merge cells so that all the content from all cells would go to the merged cell?
If you paste the information (with the line breaks) into the Formula Bar (up top — the field that has the “fx” to the left), it will be formatted with line breaks.
Merging cells would be done with the CONCATENATE function (http://office.microsoft.com/en-us/videos/video-concatenate-function-in-excel-VA102526057.aspx?CTT=1)
My question if the same as Jay’s (june 18 2012). i have entered multiple lines of data in a single cell using alt enter. When i copy the contents of this cell to another it appears in several rows. your answer confirms this happens. are you also saying there is no shortcut and in every instance i would have the tedious task of merging the copied cells. I have to do this often and its time consuming. Please help.
i find all your other tips most helpful. some have cut my work time by half. thank you thank you.
Jay’s question was how to paste multi-line information from another source into a single Excel cell. I cannot replicate the problem you seem to be having with copying the multi-line contents of one Excel cell to another — on my end, it works fine. What version of Excel are you using?
I am using the Microsoft Office Excel 2010. When i type in multiple lines of data in a cell using alt enter i am unable to copy any selected part of the data content only to another cell. it appears in several row. I am copying the whole cell and making changes to the content after pasting it.
The difference may be in the way you’re copying the cell. If you’re copying the contents of the cell — if you’re going into the cell itself (either with F2 or within the formula bar or by double-clicking) and selecting the individual lines/words/characters, then that’s why you’re getting the result you’re seeing. See the video above (under “Update”) for a demonstration/explanation.
If you want to copy just part of the cell into another cell, then do use double-click; just make sure you double-click both just before you copy and just before you paste. If you double-click and copy part of the contents, then single-click a cell to copy to, you’re mixing apples and oranges vis-a-vis copy methods. Use one method or the other as appropriate.
just wondering is it possible to put multiple lines of data into one cell, but give each row of data a new line within the cell?
Concatenate works perfectly if you want multiple rows to appear in one cell
I don’t really understand the difference between “put[ting] multiple lines of data into one cell” and “giv[ing] each row of data a new line within the cell.”
I think he means giving each one a border. So that the column nest to it doesn’t have to line up perfectly. I am wondering the same.
If that’s the case, then merging the cells adjacent to the cell with multiple lines would be the ticket. That function is found on the Home tab in the Alignment section — the drop-down is labeled “Merge & Center,” but the function that’s needed here is simply “Merge Cells.”
I am wanting to add addresses to an invoice I have designed on excel. I also want all the clients to be in a drop down list so I dont have to type them everytime. I have used the Alt – Enter button to use multiple lines for ab address in a cell but when I then add the list by going to data/valiadation etc the address appeard as one line with little squares where the line has been out. Any ideas what I can do?
@Sophie: I’m not sure I know the answer to this one — that’s a pretty advanced function with the lookups and all. You might try asking the question at answers.microsoft.com or consulting one of the experts at http://mvps.org/links.html#Excel.
What I can’t figure out is how to decrease the line spacing within a cell.
For example, if you look above at your hard break example with the 2 lines of text, I want to shrink the spacing between the lines, anyone know how to do that? I’ve tried setting it as a single spaced, smaller font, etc, but not working for me.
Life saver! Thanks
I’m trying to control exactly where the lines break within a cell by using ALT-ENTER after I type my copy, but rather than getting a hard break for a second line in that cell, I’m immediately moved to an adjoining cell. What am I doing wrong? Im working on Mac Version 12.1.
@Mark — See the reply to Scot above. Also, the tutorials on this blog are for the Windows version; if you’re looking for anything to do with Mac, see http://blog.officeformac.com/
I create a multi-column weekly report in Excel where I have one column of cells with multiple line breaks in each cell. I have to email this to my supervisor in Outlook each week to “edit” and when it comes back and I try and copy it BACK into Excel, I lose the line breaks in the cell. In other words, the separate lines recorded in a single cell in the original output are put back into separate cells when I copy over from Outlook. I have tried copying the Outlook text into a Word table and then copying that back to Excel, but in all cases I lose the line breaks within the cell. Any ideas?
This may sound a little harsh, but … can your supervisor not simply edit the file in Excel? Does s/he not have Excel? Or has it but doesn’t know how to use it? What’s the barrier to simply attaching the Excel sheet to the outgoing email, rather than dumping the text into the body of the email? I don’t really get how this copy-and-paste process you describe evolved. How did you two come up with this way of doing things?
It sounds to me like your company needs the services of a good IT professional to set up some sort of environment in which your supervisor has direct access to the data that needs editing. Or your supervisor should get/learn Excel and “edit” your sheet when you turn it in. Or you could let your supervisor write on a hard copy and you type the changes. But the system you describe sounds maddeningly over-engineered to me. Flipping the data back and forth among various file types (copying to Word, over to Outlook, then re-copying back to Excel) is bound to cause formatting issues.
Yes, I do attach the Excel spreadsheet, sometimes ONLY the spreadsheet, but the pertinent columns are always copied to Outlook and revised there and then returned to me. The text that comes back appears to have the line breaks in the cells, but when I copy back to Excel, the line breaks don’t carry over. No worries, we are revising the process anyway, so this was just a shot at a short term fix, but I was just curious if it was even possible. The “over-engineering” is not part of the current process, just my attempt to convert the Outlook text I receive BACK into the format that I sent over with the line breaks intact. Thanks for your help!
Okay, I understand. The copy-and-paste thing can do nasty things to text – stripping out codes, etc. – so that’s why that is happening.
You might try asking around at answers.microsoft.com. Who knows, maybe one of the Microsoft MVPs has a suggestion that will work better than my [non] answer!
OK, I’ll give that a shot. Thanks!
I placed my queries to you in July 2012. You have since given me several answers and the video (which was perfect BTW). They have all been been useful. I have been hard at work using all your tips. Thanks from me and all my staff.
Great – I’m glad that helped!
I have a list of items (about 200) I need to copy from one document (Word) into a tabled list in Excel 2003. How do I do this without copy-pasting each individual line into each individual cell one at a time?
If you want each item to go into its own cell, then just copy the item from Word, click into the first cell in Excel, and hit CTRL-V. If there are hard returns (a.k.a. paragraph breaks) between each item, each item will appear in its own cell.
If you want all 200 items to appear in the same cell with the hard returns intact, click into the cell in Excel, then click into the Formula Bar up top (that little white area to the right of the “fx” symbol) and paste it (CTRL-V there). All 200 items will go into the same cell.
Sorry for the duplicity – but I asked this question back in August 2012 (it’s seen above) but never have had a reply. I’m hoping for an answer as I run into this often and it is making crazy that I can’t figure it out.
What I can’t figure out is how to decrease the line spacing within a cell.
For example, if you look above at your hard break example with the 2 lines of text, I want to reduce the spacing between the lines (to close up the gap between the lines more), anyone know how to do that? I’ve tried setting it as a single spaced, smaller font, etc, but not working for me. The only thing that comes close it seems it to change the font.
Thank you!! Libby
@Libby — To my knowledge, there is very limited paragraph formatting within a cell (ex: justification, line wrap, etc.), and none which would enable you to control line spacing. That’s really more of a word processing function than a spreadsheet function.
Wow… Thank u so much was looking for this option… It was mind boggling each time i keep hitting the space bar for a line break… at last found it….
Thank u!!!! Diann
I’ve some problem. In a excell sheet, I’ve 400 row with words like: ,”[email protected]”,””,””,””,””,””,””,””,””,””,””,””,””,”+919268498360″,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””
So, I want to replace “[email protected]” and the phone number “+919268498360” in everyline from the A1 and B1 cell in C1.
Please help me.
“Find and Replace” is accessible by clicking CTRL-H.
To place a “hard return” on a Mac platform use Control Command Enter to move text to the next line.
THANK YOU SO MUCH!!!! I have been working on this FOREVER!
I was having the same problem other users were having in that it would just move to the cell below. The problem was I had a Mac!!!!!!
Hi, I’m wondering if it is at all possible to merge several rows or data into one cell? Whenever I’ve tried this excel says “The selection contains multiple data values. Merging into one cell will keep the upper-left most data only”. I’m working on a mail merge doc and need several rows of data to merge as well as the corresponding columns. For example, each customer has 8 rows of data but excel thinks each row is a different customer. Is there a way to do this??
I don’t really think this is possible without some programming on the Excel side. You might try posting your question at answers.microsoft.com to see if one of their MVPs has a solution.
Thanks so much anyway! I ended up having to create a report in Access in order to use two different data sources.
ALT + Enter to add a line in cell, is not working in MS Office 2010. How to fix it?
See my answer to @scot’s comment above. The tutorial above was done in Excel 2010, so I know that feature works the way I describe.
For MAC Office, it is different.
Maybe that is why some are having problems?
Place cursor at desired location in text, press: ctrl + alt (option) + enter (return)
HOme menu wrap text option will give you multiplines in a single cell
I can able to add tabs in single cell itself and Alt Enter is also working fine.
But i have a couple of columns to merge and my merged cells required to come one under one in a single cell.
Ex1: Did manually Copied and pasted directly from excel sheet.
Ex2: Tried with merging formula
Result is: Example Checking Testing (It not comes one under one in a same cell)
How to do this for all cells ? Pls help us.
We want to do this for all cells automatically, because data is huge.
You might try the Concatenate function.
Comments are closed.