How to Skirt the 255 Character Limit Within Excel Pivot Tablesby
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:
- Enter the word ‘LongText’ in cell A1.
- Enter the word ‘Amount’ in cell B1.
- Select cells B2:B11, type the number 100, and then press Ctrl-Enter to fill all 10 cells at once.
At this point your spreadsheet should look something like Figure 1.
Figure 1: Create a list of data where the text in the first cell is greater than 255 characters.
Now let’s create a pivot table:
1. Click on any single cell within your list.
2. Select Excel’s Insert menu.
3. Click the Pivot Table button.
4. Click OK to create the pivot table.
5. Click Long Text within the pivot table field list.
6. Click Amount within the pivot table field list.
Figure 2: Create a pivot table to see how only the first 255 characters are displayed.
Depending upon your screen resolution, you may not be able to see the entire column at once. If necessary, click any single cell within the first column of your pivot table and then choose Zoom to Selection on Excel’s View menu. At this point you should be able to determine that Excel has truncated the text in your field after the 255th character. Depending upon your font, you may see slightly fewer characters.
When situations like this arise we sometimes have to call upon inelegant workarounds, such as shown in Figure 3:
1. Enter the word Part 1 into cell C1 of the worksheet where you created the data for your pivot table.
2. Enter this formula in cell C2:
This will return the first 240 characters from cell A2. I chose 240 because depending upon the font you choose using 255 may result in some characters not being displayed.
3. Enter the word Part 2 into cell D1 of the worksheet where you created the data for your pivot able.
4. Enter this formula in cell D2:
This picks up where the LEFT function in column C leaves off, capturing the next 240 characters starting at the 241st position.
5. Select cells C2:D2 and then double-click the notch in the right-hand corner of cell D2 known as the Fill Handle. This will copy the formulas to the bottom of your list.
Figure 3: Separating the long text into two parts will enable us to work around the 255 character limitation.
The next step is to inform Excel that you’ve expanded the source data for your pivot table:
1. Click any cell within the pivot table.
2. Select the Analyze menu in Excel in 2013 and later or the Options menu in Excel 2010 and earlier.
3. Click Change Data Source.
4. Update the Table/Range field in the Change PivotTable Data Source dialog box to encompass the two additional columns you added.
5. Click OK to close the dialog box.
Figure 4: You must always inform Excel when you’ve added new columns to the source data for a pivot table.
You’re now ready to instruct the pivot table to use the two new fields, as shown in Figure 5:
1. Click on any pivot table cell to display the PivotTable Fields List.
2. Uncheck the Long Text checkbox to remove that field from the pivot table.
3. Click Part 1 to add the first new field.
4. Click Part 2 to add the second new field.
Figure 5: Replace the one long field that gets truncated with two smaller fields.
As shown in Figure 5, the data wraps onto two lines, and shows the amount on the second row. You can make the data appear on a single line with no subtotals if you wish, as shown in Figure 6:
1. Select any cell within the pivot table.
2. Select Excel’s Design menu.
3. Choose Subtotals.
4. Choose Do Not Show Subtotals.
5. Choose Report Layout.
6. Choose Show in Tabular Form.
7. Click the arrow in the corner of the PivotTable Styles section.
8. Choose Clear to remove the bold font from the first column.
Figure 6: Adjustments you can make to align the text onto single rows without subtotals.
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.