Written by Allen Wyatt (last updated August 17, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Need to quickly select a range of cells? Perhaps the easiest way is to use both the mouse and the keyboard together, as ...
Discover MoreWhen editing worksheets, it is important to know how to add rows and columns. Excel provides a couple of quick ways you ...
Discover MorePaste some information into a worksheet and Excel helpfully displays some options related t the paste operation. If you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-05-11 18:50:04
Cadeau
Thank you!
2016-01-23 23:25:33
Pete
They other (pseudo) limitation is the maximum displayed size of a single cell. On Excel 2013 that is 545 pixels high x 1789 pixels wide (on my laptop, at least).
Merging adjacent cells overcomes this to some extent but there may be reasons why the user cannot work with merged cells.
But for all practical purposes this limitation doesn't affect the user much.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments