Filter an Excel Heat Map by Start and End Date
Question details
The user wants to filter a service-call heat map by a start and end date as well as priority, department, and hour.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A service-call heat map displays counts and needs interactive date and category filters.
- Observed behavior
- A Between date filter changes the reported data, but the heat-map counts remain unchanged because they use COUNTIFS formulas.
Check that the service-call date column contains actual Excel dates and identify whether the heat-map counts come from a PivotTable or COUNTIFS formulas. Make sure priority, department, and hour are available as fields in the source data.
Filter a PivotTable heat map with a timeline and slicers
Use this approach when the heat-map counts can be built from a PivotTable or a shared Data Model.
A timeline filters connected PivotTables, but it does not automatically change ordinary COUNTIFS formulas that reference the full source range. Build the heat map from the filtered PivotTable counts instead.
Select the service-call data and choose Insert > PivotTable. Add a call field to Values as a count, and arrange the date and hour fields in Rows and Columns to form the heat-map grid.
Select the PivotTable, choose PivotTable Analyze > Insert Timeline, and select the date field. Set the timeline to Days and select the required start-to-end range.
With the PivotTable selected, choose PivotTable Analyze > Insert Slicer and select Priority, Department, and Hour. Use the slicer buttons to narrow the displayed calls.
If the heat map uses another PivotTable, select the timeline or a slicer and open Report Connections. Check the heat-map PivotTable if it appears; the PivotTables must share a compatible source or Data Model.
Select the PivotTable count cells and choose Home > Conditional Formatting > Color Scales. Change the timeline and slicer selections to verify that the counts and colors update.

Add date and category criteria to a COUNTIFS heat map
Use formula criteria when you want to keep a worksheet-based heat map instead of rebuilding it as a PivotTable.
Create a date-filtered heat map in WPS Spreadsheets
Use COUNTIFS criteria in WPS Spreadsheets to calculate service-call counts for a selected date range, priority, department, and hour, then apply a color scale.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to load your Excel workbook. Put call timestamps in column A, priorities in B, departments in C, and numeric hours from 0 to 23 in D.
- 2. Enter the criteria: Enter a start date in F2, end date in G2, priority in H2, and department in I2. Add dates down J6:J and hour numbers across K5:AH5.
- 3. Calculate the counts: In K6, enter =COUNTIFS($A$2:$A$1000,">="&MAX($F$2,$J6),$A$2:$A$1000,"<"&MIN($G$2+1,$J6+1),$B$2:$B$1000,$H$2,$C$2:$C$1000,$I$2,$D$2:$D$1000,K$5). Adjust the source ranges, then fill the formula across and down.
- 4. Format and test the heat map: Select the count cells and choose Home > Conditional Formatting > Color Scales. Change the criteria in F2:I2 to check that the heat map updates.
- 5. Save the workbook: Choose Menu > Save As and save the workbook in XLSX format if you need to share it with Excel users.

Frequently Asked Questions
Why does my Excel date filter not change the heat-map counts?
COUNTIFS normally evaluates its full referenced ranges, including rows hidden by a worksheet filter. Add date criteria to the formulas or build the counts from a filtered PivotTable.
Can one timeline control more than one PivotTable?
Yes, if the PivotTables share a compatible source or Data Model. Select the timeline, open Report Connections, and check each PivotTable you want it to control.
How do I include calls made on the end date when timestamps contain times?
Use an exclusive upper bound of the day after the end date, such as a COUNTIFS criterion of "<"&G2+1, where G2 contains the end date without a time.
How can I filter the heat map by priority and department?
For a PivotTable, choose PivotTable Analyze > Insert Slicer and select the Priority and Department fields. For a formula-based grid, reference cells containing those selections in the COUNTIFS criteria.




