This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
Hello and welcome back to the Excel Tip of the Week! This week, we have a Basic User level post in which we're going to look at a few options for how to include text in your Excel spreadsheets.
Excel is not a word processor
It's hard to overstate this: Excel (and indeed all spreadsheet packages) are not designed for word processing. They struggle with readability and layout, and printing can be tricky. This means that Excel is just not well suited to presenting lengthy text information.
However, there are times when lengthy text and figures go hand in hand. For example, you might need to add commentary to a set of Excel accounts, or document how to use a financial model. In this Tip, we're going to look at a few options for how to work text in to your workbooks.
Just type it
Pros: Easiest to do
Cons: No wrapping of text that goes wider than the screen. Values entered in later columns will also curtail the text that is displayed.
Manually create line breaks
Pros: Can make readable text without needing to resize cells
Cons: Difficult to do; very annoying to edit text after the fact; adding content into later cells still blocks text.
Use a wider cell and enable Wrap Text
Pros: Flexible column width allows text to all be seen within one screen width
Cons: Tall row and wide column have to carry over the entire sheet; row and column resizing may become temperamental
To do this, select the text and the number of columns you want the text to fit in, then go to Home => Fill => Justify:
Pros: Creates readable text width and line breaks without resizing cells or extensive copy/pasting
Cons: Can only take 255 characters at once (in above example the text is cut short)
Insert a text box
This can be done from the Insert menu:
Pros: Easy text readability and resizability; no impact on the sizes of rows and columns
Cons: Text sits on a separate layer to the cells of the spreadsheet and can obscure the cells behind; will resize if rows / columns are resized
Embed a Word document
This can be done from Insert => Object => Create New => Microsoft Word Document:
The pros and cons are the same as for the Text Box option, plus:
Pros: The full Word toolbar can be accessed by entering the embedded document
Cons: Filesize will increase; moving between the Excel and Word environments takes a few seconds for each jump and the Ribbons may not display correctly.
There's no perfect way to include text in your Excel workbook. For my money, the Text Box option is the best one, but that will depend on your particular needs. But this post will hopefully give you an idea of what the alternatives are, so you can pick what's right for you.
Previous post - Spaces in formulasTOTW index Next post - Revisiting Go To and Go To Special
For a set of accounts I would use Word to get a proper set of writing functions, then link in my Excel tables. If you're doing more than a few text blocks in Excel I'd say you're in for a very frustrating time. Saying that, I've also found linked Excel tables in Word nightmarish in the past.
There are a few options available within the Format Shape menu (right click the text box and Format Shape):
It's worth experimenting with these to get the results you want. These also overcome some of the Cons of text boxes (e.g. resizing when rows or columns are resized)
It's worth noting that in a text wrapped cell, you can force a line break by using Alt + Enter. This can help with formatting the text to look the way you want, and helps to prevent the appearance changing significantly when the column gets wider. Most of the other Cons of this method still apply.
It would be useful if text boxes could be anchored to a designated cell.