Calc: Pivot table with text starting 0 does not display correctly

  1. Open LibreOffice type into cells:
    A1: Customer No
    A2: '010 → notice I have put the ’ character to make it display like text
    A3: '100
    B1: Sells Quantity
    B2: 1000
    B3: 1100

  2. Mark cells from A1 to B3.

  3. Data | Pivot Table | Create.

  4. Current Selection and OK button.

  5. Move Customer No to Rows Fields.

  6. Move Sells Quantity to Data Fields.

  7. Click on OK button.
    Now data appears in pivot table. Notice in cell A4 there is customer no. “10” displayed instead of 010.

Is this a bug? Can I overcome this settings?

LibreOffice 3.5.0 on Windows XP.

Hi Anonymous, Are you still seeing this bug in the latest builds of LO? I’d like to resolve this question if possible. Thanks!

Yes, it is a bug. LibreOffice should not identify as a value if you added the text identifier (’)

I think you can’t overcome this problem but you could add a symbol e.g. '#010 and '#100 to force it to do what you want :wink:

On data page I have also changed '010 value to '#010 value and executing pivot and value in new-pivot sheet still appears as “10” - so characters ’ and # are removed. Is this a known bug, someone already reported it? Should I report a bug? If yes, I will wait for 3.5.1 to be officially released, probably today.

I’m working with LO 3.4 under linux (fedora) and this problem doesn’t occur here. However, in your case, I think you can try to configure the cells format as 000.But to use this solution you will need to delete the text indentifier (’), ok?

I have tried to format cells with 000 and formatting works fine on data sheet. But when creating a pivot table new sheet is created with corrupted values and I can’t imagine to influence with some settings to the sheet that does not even exist (in the moment before pivot is executed). The only way I have manage to solve the problem (work-around) is to add 000 formatting to newly created pivot sheet, but this is annoying to do every time I create a pivot table.

So, try to change the default style configuring its cells format to 000. Maybe, when the new worksheet is created, this pattern can be adopted. However, this option can bring more problems than solutions, depending on the nature of the rest of your data. I hope that works!

Hi Anonymous,

If you’re still experiencing an issue here, please file a bug. The QA team will be happy to help you track down this issue!

If you do file a bug, please post a link to it in a comment below using the format “fdo#123456”.