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

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I explain why Pivot Table is not picking up data while doing several operations in MS Excel. Pivot Table is a very essential tool in Excel. But sometimes, they show some frustrating errors while representing data. For example, data is not showing in the table while inserting or refreshing the Pivot Table. Luckily, solutions to these problems are simple and I will discuss several of them. To serve my purpose, I will work with a dataset containing date-wise sales of some mobile phones.

Pivot Table is Not Picking up Data


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


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.

Excel Pivot Table is Not Gathering Data If Table/Range is Not Valid

  • Consequently, we will get the below message in a different dialog box after pressing OK.

Excel Pivot Table is Not Gathering Data If Table/Range is Not Valid

Solution:

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

Excel Pivot Table is Not Gathering Data If Table/Range is Not Valid

  • Go to Insert > PivotTable > From Table/Range.

Excel Pivot Table is Not Gathering Data If Table/Range is Not Valid

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

Excel Pivot Table is Not Gathering Data If Table/Range is Not Valid

  • As a consequence, we will get the below Pivot Table.


Reason 2: Data is Not Showing in Pivot Table as Source Data Contains Blank

Sometimes, the source data contains blank cells. In that case, the Pivot Table created from that dataset will not pick data and also contain blank cells. For instance, the below dataset contains two blank cells.

Data is Not Showing in Pivot Table as Source Data Contains Blank

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

Solution:

If you do not want to show blank cells in the Pivot Table:

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

Data is Not Showing in Pivot Table as Source Data Contains Blank

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

Data is Not Showing in Pivot Table as Source Data Contains Blank

  • As a result, you will see zeros (0s) in the blank cells like in the below screenshot.


Reason 3: Pivot Table is Not Picking up Data If New Row Added to Source Data

Suppose, we have a Pivot Table created previously based on a certain dataset. Later, 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. Such as we have a Pivot Table based on the dataset (B4:D14).

Pivot Table is Not Picking up Data If New Row Added to Source Data

And we have created the Pivot Table below from the above dataset.

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

Pivot Table is Not Picking up Data If New Row Added to Source Data

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:

To solve the above problem, we have to change the data source of the Pivot Table.

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

Pivot Table is Not Picking up Data If New Row Added to Source Data

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

Pivot Table is Not Picking up Data If New Row Added to Source Data

  • Now, update the dataset range in the Table/Range field and click OK.

Pivot Table is Not Picking up Data If New Row Added to Source Data

  • As a result, 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 the Pivot Table.

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

Pivot Table is Not Gathering Data for Blank Column Header in Excel

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

Pivot Table is Not Gathering Data for Blank Column Header in Excel

Solution:

To solve the above problem, follow the below steps.

  • First, give a header ‘Quantity’ to the 3rd column of the dataset (B4:D14).

Pivot Table is Not Gathering Data for Blank Column Header in Excel

  • Then 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 Column

Likewise in Method 4, 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.

Pivot Table is Not Showing Data If Source Data has Empty Column

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

Pivot Table is Not Showing Data If Source Data has Empty Column

Solution:

To solve the problem, we have to put data in the empty column or delete the column. Here we will delete the empty column following the below steps:

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

Pivot Table is Not Showing Data If Source Data has Empty Column

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

Pivot Table is Not Showing Data If Source Data has Empty Column

  • Consequently, the empty column is deleted from the dataset.

Pivot Table is Not Showing Data If Source Data has Empty Column

  • Now, like before, 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.


Conclusion

In the above article, I have tried to discuss reasons and solutions elaborately when Pivot Table is not picking up data. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

2 Comments
  1. Hi Hosne – I have an issue with a Pivot table. When I run the Pivot it includes data that doesn’t exist on the data sheet. For example there is no-one called Edwyyn on the worksheet (data) but when I run the Pivot this person is added to the worksheet which then mixes up all my data. I have checked by searching the data and the person added doesn’t appear on the data sheet prior to running the pivot. Any ideas?

    • Hello Ed McCann,
      It’s possible that the Pivot table is referencing a range of cells that includes data outside of the intended range. Here are a few steps you can try to troubleshoot the issue:

      1. Check the source data range: Make sure that the Pivot table is referencing the correct range of cells that contain your data. Select any cell inside the Pivot table, go to the “Analyze” or “Options” tab in the ribbon, and look for the “Change Data Source” or “Select Data” button. Clicking on this button will show you the range of cells that the Pivot table is using as its data source.

      2. Check for hidden data: It’s possible that there is hidden data in the source data range that is being included in the Pivot table. Select the source data range, go to the “Home” tab in the ribbon, and click on the “Format” dropdown. From here, click on “Hide & Unhide” and then “Unhide Rows/Columns” to reveal any hidden data.

      3. Refresh the Pivot table: If none of the above steps work, try refreshing the Pivot table. Select any cell inside the Pivot table, go to the “Analyze” or “Options” tab in the ribbon, and click on the “Refresh” button. This will recalculate the Pivot table based on the current data source.

      I hope these steps help you troubleshoot the issue with your Pivot table.

      If the problem persists, then you can send your excel workbook to this email: [email protected]

      Regards
      Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo