SOLVED

How to add a row in PivotTable which is not included in the data?

Copper Contributor

Good day. I am a beginner of excel user. I have met a trouble in using Pivot Table.

I want to make a report but i found that i can't add a blank row for the category which is not include in the data. 

 

For example, I obtain a transaction record of customer in Jan and I would like to make a transaction report for customer A,B,C,D,E. It is noted that customer E did not make any transaction in Jan and thus there is no record for Customer E in the database (Transaction Record). When I use the pivot table, it works properly for customer A,B,C,D. But how can I add one more row for customer E in the pivot table in order to record for the zero transaction in the report?

 

Thanks.

 

3 Replies

hi,

try to add this information in your data sheet, for example add a row , client E, jan 0. in your pivot table, right click and press "refresh".

good luck

 

best response confirmed by Qweaqwes1500 (Copper Contributor)
Solution

Another way, not to add dummy records every time, you may add separate table with list of all your customers, create relationship with your transactions table on customers, and use customers name from the second table creating Pivot Table. When create it, check 'Add data to data model' box. In Pivot Table options, Display tab, check 'Show item with no data on rows' box.

 

If you have long table of transactions where all customers exist, and you filter Pivot Table on month in which some customers make no transactions, it'll be enough to check 'Show items...'

Pivot tables generally use data from an array elsewhere.  You would have to add data to the source of the pivot table, and then change the source data size on the "Analyze" tab of the pivot table.

 

I find it easiest to make the source of the pivot table a table and then when adding data just clicking on "Refresh" on the Analyze tab of the pivot table.

 

This is kind of a rough description but I hope it helps.

1 best response

Accepted Solutions
best response confirmed by Qweaqwes1500 (Copper Contributor)
Solution

Another way, not to add dummy records every time, you may add separate table with list of all your customers, create relationship with your transactions table on customers, and use customers name from the second table creating Pivot Table. When create it, check 'Add data to data model' box. In Pivot Table options, Display tab, check 'Show item with no data on rows' box.

 

If you have long table of transactions where all customers exist, and you filter Pivot Table on month in which some customers make no transactions, it'll be enough to check 'Show items...'

View solution in original post