Search Not Just Numbers

Tuesday 2 July 2013

Excel Tip: Multiple rows of text in one cell

Just a quick post this week.

Excel is great at handling numbers and text data fields, but what about when you need to wax lyrical and enter and hold larger sections of text.

In the data behind an invoice spreadsheet, for example, we might want to be able to enter significantly more text in the description column than anywhere else.

There are a couple of little tips worth knowing when entering larger chunks of text like this, and I thought I'd share these with you.

First of all, it's worth knowing how much text you can enter in one cell. Excel is restricted to 32,767 in a cell, which should be more than you need for most practical purposes. So how do we make the most of this?

Wrap text
The first feature worth knowing about is "Wrap text". This is a tick box accessed on the Alignment Tab when you select Format Cells (accessible (among other ways) by right-clicking on the cell and choosing Format Cells).

When this box is ticked, the text in the cell "wraps" similar to a word processor, i.e. when a word will not fit onto a line, it moves onto the next line.

The row height also flexes to fit the multiple rows of text.

Alt + Enter
Less people seem to know about this one (as is usually the case with features that are not selected from a menu). While typing within a cell, pressing Enter will finish editing that cell and move onto the next one. However, pressing Alt+Enter stays in the same cell but moves onto the next line (again like a word processor). This is great for typing a list in one cell, or entering data in paragraphs.

Once again, the row height flexes to fit the multiple rows of text.

That's it for this week, now you can go off and write a  novel in Excel!

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

13 comments:

  1. Never knew about the Alt + Enter - very useful. We use spreadsheets for project and case control so often have to add notes. Not being able to split a note into paragraphs is frustrating and makes reading the note back very difficult - not any more.

    ReplyDelete
    Replies
    1. I know about Alt + Enter but tell me one thing sir, how can sum If i insert two numbers in a cell by using Alt + Enter.

      Delete
  2. The other thing to note about entering lots of text in a cell is that Excel limits the height of a cell to 409. So, even if you Wrap the text and you have less than 32,767 characters, Excel might not show or print all the text if the column is too narrow.

    ReplyDelete
  3. Carl

    Thanks, that's a useful piece of additional information. I almost mentioned that in the main post, but was a bit concerned in case it distracted from the main point of the article, so just mentioned the character limit. Thanks for ensuring it is still there for completeness! :o)

    ReplyDelete
  4. I would very much like to use this shortcut but how do I do it on a MAC keyboard.

    ReplyDelete
  5. Thank you publisher, I have learnt new thing from You. I had this a challenged but i have now overcome the challenge GOD BLESS YOU. THE ALT ENTER WAS PERFECT.

    ReplyDelete
  6. These are excellent tips. As a beginner I can use this very well. Sometimes I also get instructions and tips from the following site. That's an interesting workshop with videos. look at it: http://www.excel-aid.com/excel-enter-entering-data-2.html

    ReplyDelete
  7. Alt + Enter has helped loads. Thank you! :-)

    ReplyDelete
  8. Alt + Enter does not work for me, just moves to next cell, please help. I've known about it for years and it used to work for me but has just suddenly stoppped, it's driving me mad!!

    ReplyDelete
    Replies
    1. Are you on a Mac? I believe it is Ctrl-Cmd-Return on a Mac.

      Delete
  9. Great Tip.
    What if I do not want to row height to expand and I just want the last or first line visible and only see the rest of the entry when the cell is highlighted?

    ReplyDelete
    Replies
    1. If you enter the row height (right-click the row number and select Row height. this will remain fixed and the cell will show the first row, or rows if you set the height large enough. You can see the full contents when you press F2.

      Delete