Ask Your Question

Pivot Table empty line field [closed]

asked 2013-05-10 19:15:51 +0100

danieldc gravatar image

I have a pivot table with 3 line fields (the 1st field is to classify, others are only to show some text information) and a single data field (sum).

How to get rid of the "(empty)" value? I would like to change it to something else... but data source will not be changed (that's, source has empty cells).

I want to change "(vazio)" to "..." or something else like " " (single space).

thanks very much, yours, Daniel, from Brazil

**Source Data**
Class   InfoText1   InfoText2   Data
1       qwe         here        10
2       rty         there       20
3       asd                     30
1       fgh         now         10
2       zxc         then        20
3       vbn                     30

**Pivot Table**         
Class   InfoText1   InfoText2   
1       fgh         now         10
        qwe         here        10
2       rty         there       20
        zxc         then        20
3       asd         **(vazio)**     30
        vbn         **(vazio)**     30
Total Resultado             120
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-31 21:22:40.817781


Thanks very much for suggestions.

Best solution was to create a new column with function "IF(CellIsEmpty; SpaceChar;CellValue)" and use this new column as data source for pivot table.

Changing value of data source (by Find&Replace) is not applicable. Users will not fill empty cells with something only because they shall/must do it... Empty cell is what it is, empty value.

LO-CALC must handle this in a better way, as MO-Excel does! This looks like bugware :-(

danieldc gravatar imagedanieldc ( 2013-05-21 15:52:15 +0100 )edit

4 Answers

Sort by » oldest newest most voted

answered 2013-05-11 08:30:05 +0100

ROSt52 gravatar image

@danieldc - A possible workaround could be to create an additional column and the IF function to replace the empty cells by something, which matches your needs. It would not be too much work. If assistance is needed please post your question.

edit flag offensive delete link more

answered 2013-05-13 19:26:25 +0100

hrothgar gravatar image

Or ... instead of leaving the original data cells empty (that is, NULL) you could type a space character in them. It's what I do!

edit flag offensive delete link more

answered 2013-05-10 20:27:10 +0100

froz gravatar image

(vazio) in Brazil language means (empty). If you would like to replace (vazio) with empty string each time you execute pivot table, then: 1. Edit | Find & Replace (or press CTRL+H). 2. In Search for box type in (vazio). 3. Click on Replace All button.

edit flag offensive delete link more

answered 2013-05-11 07:46:28 +0100

oweng gravatar image

updated 2013-05-11 07:51:33 +0100

This is bug fdo#47523. Status has been set to an enhancement request. It essentially relates to how the Identifies categories check box (under More options...) treats the original data. The Calc Guide v3.4, p.205 has this to say:

With this option selected, if the source data has missing entries in a list and does not meet the recommended data structure, the Pivot Table adds it to the listed category above it. If this option is not chosen, then the Pivot Table inserts (empty). [...] Without category recognition, the Pivot Table shows an (empty) category [...] Logically, the behavior with category recognition is better. A list showing missing entries is also less useful, because you cannot use functions such as sorting or filtering.

As @froz indicates "vazio" is Portuguese for "empty".

edit flag offensive delete link more

Question Tools


Asked: 2013-05-10 19:15:51 +0100

Seen: 2,614 times

Last updated: May 13 '13