Remove blanks from pivot table
yosra_miagi
Posted messages
110
Status
Member
-
Anonymous user -
Anonymous user -
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.
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
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
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
In the pivot table, you can also right-click on the (empty) cells and choose 'hide'.
eric