Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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: Text Truncated in PivotTable.
Written by Allen Wyatt (last updated October 23, 2020)
This tip applies to Excel 2007, 2010, 2013, and 2016
Anil complained that when he created a PivotTable, some of the text in some of the source cells was truncated when it was placed in the PivotTable. He wondered if there wes a way around this.
The first thing to do is make sure that the text is actually being truncated. When text is transferred to a cell in a PivotTable, it works much the same as text in the original worksheet. This means that the text is "cut off" when there is data in the cell to the right of the text cell. The full text is still there, but it cannot be displayed because there is not enough room to do so within the cell.
Testing has shown, however, that PivotTables will only transfer up to 255 characters from a source cell. Anything after that is truncated. This limit seems to be hard-coded into Excel, and there is no way around it that I could discover. The limit of 255 characters may seem arbitrary, and it is. I can only surmise that Microsoft needed to establish a length limit on text, and figured that 255 characters should be sufficient for most purposes.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11784) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Text Truncated in PivotTable.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When you refresh the data in a PivotTable, Excel can play havoc with whatever formatting you applied. Here's how to ...
Discover MoreOne of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique ...
Discover MoreIf you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-04-20 13:42:25
Tomek
Sorry, I attached wrong screenshots to my earlier posting.
@Paul: What shows on the Ribbon depends mainly on the screen (or more specifically window) resolution. Just to check this issue I changed my screen resolution from 1920x1080 to 800x600 and found that no styles whatsoever showed in the style gallery. The same when I opened Word in a much smaller window. However, I could get all the styles in my style gallery by pressing on the Styles button. (see Figure 1 below) (see Figure 2 below) . I guess displaying Styles panel permanently may not work well with your limited resolution.
Also, I was able to get some of the styles visible in the styles gallery by removing from the Home Ribbon groups that I do not use from the ribbon (Editor and Add-Ins). And remember, you can re-organize your ribbon and add those that you remove from one place to a custom group on another tab or to your own custom tab, or even QAT.
So, if you cannot increase your screen resolution, consider one of the above approaches.
Figure 1.
Figure 2.
2024-04-20 10:42:17
Brian Lair
Or add a button in the Quick Access Toolbar for each of your favorite styles.
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.
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.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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:
Comments