How to Skirt the 255 Character Limit Within Excel Pivot Tables

Spreadsheets and graphs on a desk
xfgiro/istock

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:

=REPT(“A”,255)

We can then use concatenation to add another 100 instances of the letter B:

=REPT(“A”,255)&REPT(“B”,100)

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:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

avatar
Jan 20th 2017 13:56

quest diagnostics
quest diagnostics
quest diagnostics appointment
quest diagnostics login
quest diagnostics results

quest diagnostics billing

Thanks (0)
avatar
By snaagar
Jul 10th 2017 10:11

nice article and thanx fpor shairing
hey

Thanks (0)