Pivot Table Issues

Show Missing Dates by Month in an Excel PivotChart

Natalie TaylorNatalie Taylor Nov 18, 2026 870 views

Question details

Display days without incident records in a monthly or year-to-date PivotChart.

Show Missing Dates by Month in an Excel PivotChart
Product
Microsoft Excel
Device & OS
not provided
Scenario
Chart incident counts by date for a selected month or year-to-date period.
Observed behavior
The chart should include dates with no incidents without repeating every day of the year under each month.
Before you start

Check that incident dates are stored as Excel dates and identify the date range the chart must cover. If incident dates include times, create a date-only column with =INT(cell) for the relationship.

Solution 1Recommended

Use a calendar table and the Excel Data Model

Use this approach to include dates with no records while filtering the chart to a month or year-to-date period.

Use actual calendar dates on the chart axis rather than combining separate month and day fields. This avoids creating combinations of dates that do not belong to the selected month.

1
Create a calendar table

On a new worksheet, enter a Date column containing every date in the required period, with no duplicates or blanks. Select the range and choose Insert > Table.

2
Add both tables to the Data Model

Format the incident data as a table with Insert > Table. Add the calendar and incident tables to the Data Model, then use Data > Relationships to link the incident date-only column to the calendar Date column.

3
Build the PivotTable and chart

Choose Insert > PivotTable and use the workbook's Data Model. Put the calendar Date field in Rows and an incident identifier in Values as a count. Select the PivotTable and choose Insert > PivotChart.

4
Include empty dates

Right-click the calendar Date field in the PivotTable, open Field Settings > Layout & Print, and select Show items with no data. If needed, open PivotTable Options > Layout & Format and enter 0 for empty cells.

5
Filter the period

Use the calendar Date field's filter to select the desired month or a date range from the start of the year through the reporting date. Keep the actual Date field on the axis so only dates in that period are displayed.

Use a calendar table and the Excel Data Model
Check blank values: Depending on the Data Model measure and chart settings, dates with no incidents may appear as blanks rather than zero-height points. Check the Values field and chart display after filtering.
Chart every date in WPS

Show Missing Dates in a WPS Spreadsheet Chart

Create a complete date list and count incidents for each day in WPS Spreadsheet, then chart the selected month or year-to-date range. This creates a regular chart rather than an Excel PivotChart.

  1. 1. Open the incident data: Open the workbook in WPS Spreadsheet and check that the incident dates are in a single column on an Incidents sheet.
  2. 2. List every date: On a new sheet, enter the first required date in A2, enter =A2+1 in A3, and fill the formula down through the end of the reporting period.
  3. 3. Count incidents by day: In B2, enter =COUNTIFS(Incidents!$A:$A,">="&A2,Incidents!$A:$A,"<"&A2+1), adjusting the sheet and column references to match your data. Fill the formula down beside the date list.
  4. 4. Create the chart: Select the date and count cells for the desired month or year-to-date period, then choose Insert > Chart and select a column or line chart.
Shows dates with zero incidents in a selected reporting periodOpens and saves Microsoft Excel XLSX filesProvides familiar spreadsheet formulas and chart tools
microsoft office alternative - wps office

Frequently Asked Questions

Why are dates with no incidents missing from my PivotChart?

A PivotChart based only on incident records has no source rows for those dates. Add a calendar table containing every required date, relate it to the incident table, and use its Date field on the axis.

How do I show only one month without repeating dates from other months?

Put the calendar's actual Date field on the axis and use its date filter to select the month. Avoid placing separate month and day fields on the axis if they create unwanted combinations.

What if my incident dates include times?

Add a date-only column to the incident table using =INT(cell), format it as a date, and relate that column to the calendar Date field through Data > Relationships.

Can I show a year-to-date period instead of a full year?

Yes. Use the calendar Date field's date filter to select the range from January 1 through the reporting date. Ensure the calendar table covers that entire range.