Remove blanks from pivot table

Hello everyone:
I have a problem with Excel 2007. I transformed data from a table that exists on a worksheet, and there are two issues:
- If I only select the cells that contain data as the basis for the fields, it gives me a pivot table or chart, but it doesn't update with new data, which is a drawback for my work.

- And if I select the entire table, it returns empty data in my pivot table or chart, and that's incorrect.
Does anyone have a solution that allows me to display data with updates and without empty values?
Thank you all.

1 answer

  • Votes
  • Date
  • Descending date
  1. Hello

    If you take empty cells in the pivot table range, it's normal for them to appear.
    You can also use filters to hide them.

    However, starting from version 2007, you have a very practical "Table" function that you should use at all costs, especially to ensure that the pivot table updates automatically when new rows are added.
    => Place the cursor anywhere in the data range:
    - Home tab / Style group / check "Format as Table".
    - Choose the style you want, then check the data range that appears and confirm.

    The table is automatically named: Table1, if it's your first table in the file, which you can rename. Look in the Name box.

    On your current pivot table, change the data source to Table1.

    You will see that with each addition of rows to the source, the pivot table will automatically consider them.

    To refresh the data, press Alt+F5.

    --
    Argitxu
    1. Thank you very much
    2. Contributor
      Hello everyone,

      In the pivot table, you can also right-click on the (empty) cells and choose 'hide'.
      eric
    3. I tried with the table, but it doesn't change anything; the data doesn't update.
    4. The formatting in table form is to automatically account for the addition of rows.

      To refresh the data... as mentioned above => it's ALT+F5
      shortcut from the PivotTable Tools tab / Options tab