Excel Tip of the Week #283 - Presenting text in a spreadsheet

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.

The options

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

Fill Justify

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.

In conclusion

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 formulas
TOTW index 
Next post - Revisiting Go To and Go To Special

Anonymous