Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Character Limits for Cells.
by Allen Wyatt
(last updated January 23, 2016)
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.
This is to be expected, because in Excel there are two separate limitations at play: a limit on what can be entered in a cell and a limit on what can be displayed. In most cases, the limit on what can be entered in a cell is not a real issue; Excel allows you to enter up to 32,767 characters in each cell. All of these characters will show up in the Formula bar just fine.
The problem comes with the display limitation. There is a limit that Excel will display only the first 1,024 characters in each 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 pretends like it isn't even there. You can't get around this limit by changing fonts, cell sizes, wrapping status, or anything else.
There are a couple of ways that you might find acceptable as workarounds. You could, for instance, insert the lengthy text selections into text boxes rather than into cells. The text boxes don't have the same display limit, and you can format the contents in any way desired.
Another approach is to actually add the long information to a Word document, copy it, and then paste it into Excel (using Paste Special) as a Word object. You'll need to play with the formatting to make sure the text appears as you want, but this may suffice.
Both of these approaches involve getting the text out of the cells and placing it in a different object that can handle the longer text. A different approach is to simply upgrade your version of Excel. In later versions of Excel (after Excel 2003) Microsoft changed the display limits to match those of the cell entry limits. In other words, you can enter and display up to 32,767 characters in later versions of Excel.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3163) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Character Limits for Cells.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Have you ever typed something in Excel, only to have it replace whatever is to the right of the insertion point? That's ...Discover More
Most of Excel's commands affect whatever cells you select prior to invoking the command. Some commands, however, affect ...Discover More
If you hide the first rows of a worksheet, you may have a hard time getting those rows visible again. Here's a simple way ...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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.