Column widths in Excel are limited to 255 characters, whether inside a pivot table or not, and confusion arises because we can store up to 32,767 characters in a worksheet cell. This article will addresss how to work around the issue.
Outside of a pivot table, assuming adjacent cells are blank, a cell with more than 255 characters will simply overlap adjacent cells to display the text in its entirety. However, data within a pivot table must conform exactly to the 255 character limit. Although you may never come up against this particular problem with your data, the ability to see how to think creatively about working around constraints can serve you well elsewhere in Excel.
If you’re unsure how to create a line of text that contains more than 255 characters, I have a unique solution for you:
- This approach involves the REPT function in Excel. The following formula will repeat the letter A 255 times:
We can then use concatenation to add another 100 instances of the letter B:
Copy this formula to cells A2:A11 of a blank worksheet.
Carry out the following steps to complete the sample data set for your pivot table, as shown in Figure 1:
To access all of the content on our site, register (it's free!) or login to your existing account.
BONUS: If you register now you can opt to receive a digital copy of "Transform" , Richard Francis' new book for growing firms when it's available on March 30th.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.