Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Character Limits for Cells.
by Allen Wyatt
(last updated March 25, 2019)
Carolyn describes a situation in which a coworker has a worksheet with large amounts of text in several cells. A few cells will not display all the text even with text wrapping and a smaller font selected.
It sounds as if this workbook is being displayed in Compatibility mode, meaning that it is in the older file formats supported by Excel. In versions of Excel before Excel 2007 there was a problem displaying everything that may be entered in a cell. While Excel allowed you to enter up to 32,767 characters in each cell, it would only display the first 1,024 characters of what was in the cell. In other words, if there is anything more than this in a cell (which could be likely in some circumstances), then it won't display; Excel pretended like it wasn't even there.
The easiest way to get around this problem is to save the workbook in Excel's native format, either XLSX or XLSM (depending on whether you have macros in the workbook or not). Save it in the desired format and reopen the workbook, and you should have no problem seeing everything in the cells.
The caveat here, of course, is that if you want to give the workbook to someone else who is using an older version of Excel, they won't be able to see all the text in the cells. In that case you might look at dividing up what is in the cells so there is less than about 1,000 characters in each cell.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10231) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Character Limits for Cells.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If you have a lot of text in your workbook, at some point you might want to split out sentences into individual cells. ...Discover More
If you need to swap the contents of two cells in your worksheet, Excel provides a number of ways you can approach the ...Discover More
Importing data into Excel that was generated in other programs can have some interesting side effects. For instance, you ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.