Pivot Table Issues

Filter an Excel Heat Map by Start and End Date

Ayan MasoodAyan Masood Nov 22, 2026 868 views

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.

Filter an Excel Heat Map by Start and End Date
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.
Before you start

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.

Solution 1Recommended

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.

1
Build the PivotTable

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.

2
Add the timeline

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.

3
Add category slicers

With the PivotTable selected, choose PivotTable Analyze > Insert Slicer and select Priority, Department, and Hour. Use the slicer buttons to narrow the displayed calls.

4
Connect related PivotTables

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.

5
Apply heat-map colors

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.

Filter a PivotTable heat map with a timeline and slicers
If a PivotTable is not listed: Report Connections only lists compatible PivotTables. Rebuild the heat-map PivotTable from the same source or Data Model if necessary.
Build a filterable heat map

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. 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. 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. 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. 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. 5. Save the workbook: Choose Menu > Save As and save the workbook in XLSX format if you need to share it with Excel users.
Filter heat-map counts with start and end date cellsCount calls by priority, department, and hourCompatible with Microsoft Excel XLSX and XLS filesFree spreadsheet tools with a familiar interface
microsoft office alternative - wps office

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.