Pivot Table Is Not Picking up Data in Excel (5 Reasons and Solutions)

Let’s work with a dataset containing date-wise sales of some mobile phones to make a Pivot Table.

Reason 1 – Excel Pivot Table Is Not Gathering Data If Table/Range Is Not Valid

Often, while inserting a pivot table, if you enter the Table/Range incorrectly, the Pivot Table will not pick data. For example:

• We have entered the below range in the Field/Range.

• We will get the below message in a different dialog box after pressing OK.

Solution:

• Click any cell of our dataset (B4:D14).

• Go to Insert, PivotTable, and From Table/Range.

• The PivotTable from table or range dialog box will show up. Ensure that you have entered the correct range in the Table/Range.
• Click on OK.

• We will get the below Pivot Table.

Reason 2 – Data Is Not Showing in Pivot Table as Source Data Contains Blanks

If the source data contains blank cells, the Pivot Table created from that dataset will not pick that data. For instance, the below dataset contains two blank cells.

So, the Pivot Table created from the above dataset will contain blanks too.

Solution:

• Right-click on any cells of the Pivot Table and select PivotTable Options.

• From the Layout & Format tab, enter zero (0) in the For empty cells show field and click OK.

• You will see zeros (0s) in the blank cells like in the below screenshot.

Reason 3 – Pivot Table Is Not Picking up Data If a New Row Was Added to Source Data

Suppose we have a Pivot Table created previously based on a certain dataset. If you add new rows of data to the source dataset and refresh the old Pivot Table, new data won’t be included in the new table.

Consider the current dataset:

We have created the Pivot Table below from the above dataset.

Later, we added two new rows to the dataset (B4:D14).

Now, if you refresh (Press Alt + F5) the older PivotTable, the table will not update according to the new data. The table will remain as it was.

Solution:

• Click on the Pivot Table and go to PivotTable Analyze > Change DataSource > Change Data Source.

• Clicking on the Change Data Source option will take you to the source dataset.

• Update the dataset range in the Table/Range field and click OK.

• The Pivot Table is updated with data in new rows.

Reason 4 – Pivot Table Is Not Gathering Data for Blank Column Header in Excel

If any of the columns of the source data do not contain a header, you will not get it in the Pivot Table.

For example, we have the below dataset, where the 3rd column of the dataset does not contain any header.

Now, if you try to create a Pivot Table from the above dataset, the below message will show up in a separate dialog box.

Solution:

• Put a header ‘Quantity’ to the third column of the dataset (B4:D14).

• Select the dataset and insert the below Pivot Table following the path: Insert > PivotTable > From Table/Range.

Reason 5 – Pivot Table Is Not Showing Data If Source Data Has Empty Columns

If the source dataset contains an empty column, you cannot get a pivot table from that source. For instance, in our dataset (B4:D14) the 3rd column is empty.

If you want to create a Pivot Table from the above dataset, the below message will pop up.

Solution:

• Right-click on the empty column and select Delete.

• The Delete dialog box will show up. Select the Entire column and click OK.

• The empty column is deleted from the dataset.

• Select the dataset and get the expected Pivot Table following the path: Insert > PivotTable > From Table/Range.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Related Articles

• How to Hide Source Data in Excel Pivot Table
• Excel VBA to Change Pivot Table Source

<< Go Back to Pivot Table Data Source | Pivot Table in Excel | Learn Excel