How to show date gaps in PivotChart?

[excelos]

Hello!

I have a Table like the below. When I create a PivotChart with x-axis being a timeline and y-axis being the count of A, B, C, it does not show the gaps in the dates as Zero or Gaps. In fact, it connects e.g. 01/02/2023 with 03/03/2023 as if there are no dates inbetween.

This obviously gives a wrong impression when plotted so I have to generate a date sequence with empty cells under the A, B, C values. However, this is not neat as it can mess up the rest of the table (in case you want to do other table transformations etc).

Is there a way to fix that neatly? Thanks!

01/02/2023​	A
01/02/2023​	C
03/03/2023​	A
03/03/2023​	C
02/04/2023​	A
02/04/2023​	C
02/05/2023​	A
02/05/2023​	B
01/06/2023​	B
01/06/2023​	A
01/07/2023​	B
01/07/2023​	C
31/07/2023​	B
31/07/2023​	B
30/08/2023​	B
30/08/2023​	A
29/09/2023​	C
29/09/2023​	A
29/10/2023​	C
29/10/2023​	A
28/11/2023​	C
28/11/2023​	A

 

[awoohaw]

I'm not sure how to insert dates with no data that aren't in the source into a pivot table. But, you can create a crosstab with 365 rows and a date column then the counts of A,B,C 's and do countifs with this formula

=IF(COUNTIFS($B$2:$B$29,D$1,$A$2:$A$29,$C2)=0,NA(),COUNTIFS($B$2:$B$29,D$1,$A$2:$A$29,$C2))

This is assuming your data location is A2:B29, and the data crosstab is at C2:F366.
And you want a column chart.