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.
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!
If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines ...
Discover MoreWhen you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being ...
Discover MoreNeed to find that misplaced comment in your worksheet? It's easy to do using the Find and Replace capabilities of Excel.
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