Add row not in table to pivottable?

Question

Ok the question sounds weird, the case is the following. I have a pivottable that summarizes how many incidents occured on a day. Howwver, when there's no incident, there's no rows in the table. Is there a way I could add the count of zero to a pivottable? This is my current table:

Basically I'd like to add a pivot table row with "2017.10.04 0" before showing the grand total. Is there a way to do this? I'm currently counting the occurrences of the dates, so I understand this might not be possible.

h__g · Accepted Answer · 2017-10-05T08:52:14.640

0

To change the summary function:

Select a cell in the Count of ID column of the pivot table.
On the Ribbon, under PivotTable Tools, click the Options tab In the Active
Field group, click Field Settings

In the Value Field Settings dialog box, in the list of functions, click on Count Numbers, then click OK

finally pivot table shows a zero or blank cell or an incorrect count.

edit: How to treat pivot table column names as integer in pivot chart (Excel)

this link look like show same problem to yours

edited Oct 05 '17 at 08:52

answered Oct 04 '17 at 16:53

h__g

84
4

I'm sorry I don't understand these instructions. There is no row with "2017.10.04" in my data source, so I cannot count it. If I add it and count it, it will be 1 and not 0. – lte__ Oct 05 '17 at 08:03

score 0 · Answer 2 · answered Oct 05 '17 at 00:32

0

About all you can do is add the 'missing' dates to your data source as a blank row, so that it shows up in the PivotTable.

answered Oct 05 '17 at 00:32

jeffreyweir

4,668
1
16
27

The moment I add in "2017.10.04" in my source data, there will be one occurrence of the date "2017.10.04" and thus the count of fields with "2017.10.04" will be 1, not 0. – lte__ Oct 05 '17 at 07:59
Add another column called COUNT into the data source, and put a 1 in it for all items except for the ones you manually added (which get zero), and SUM them. – jeffreyweir Oct 05 '17 at 19:49
Ye that will solve it, but I was wondering whther you can directly edit the pivot table. – lte__ Oct 06 '17 at 09:11
You can't I'm afraid. Given I've answered your question, can you please consider selecting my answer as the accepted answer. – jeffreyweir Oct 18 '17 at 21:31

Add row not in table to pivottable?

2 Answers2