Create an Excel Heat Map by Weekday, Hour, and Date
Question details
Create heat maps of visitor counts for each hour and date across several weeks or a full month.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants separate weekly views of hourly visitor totals, with date labels such as Monday, 01.
- Observed behavior
- The desired report shows every date in the selected month and all 24 hours, with colors indicating visitor counts.
Arrange the source data with one row per date and hour, including a numeric visitor count. Make sure dates are stored as dates without time components and hours are stored as Excel time values.
Build weekly heat maps with SUMIFS and color scales
Use this method to show each date separately and include every hour of the month.
The example uses a Data sheet with Date in column A, Weekday in column B, Hour in column C, and Visitor Count in column D. Weekday is optional for the calculation because the heat map headers display it from the date.
Each seven-date block needs its own table. A 29-day month requires five consecutive blocks if the first block starts on the first day; a calendar-aligned layout can require more.
On a sheet named Data, enter dates in column A, optional weekday names in column B, hourly time values in column C, and visitor counts in column D, starting in row 2.
On a new sheet, enter the first seven dates of the reporting period in B1:H1. Select those cells, open Format Cells, choose Custom, and enter dddd, dd. Enter the 24 hourly time values from 12:00 AM through 11:00 PM in A2:A25.
In B2, enter =SUMIFS(Data!$D$2:$D$1000,Data!$A$2:$A$1000,B$1,Data!$C$2:$C$1000,$A2), then fill the formula across to H25. Extend the source ranges if your data goes beyond row 1000.
Select B2:H25, then choose Home > Conditional Formatting > Color Scales and select a color scale.
Copy the table to another area or sheet, replace its date headers with the next dates, and repeat until the full month is covered. Leave unused date columns blank in the final block and clear their calculated cells.

Create the same visitor heat map in WPS Spreadsheet
Use WPS Spreadsheet to organize hourly visitor data, calculate totals with SUMIFS, and apply color scales to each weekly table.
- 1. Open your data: Open WPS Spreadsheet and use Open to select your workbook, or create a new workbook with Date, Weekday, Hour, and Visitor Count columns on a sheet named Data.
- 2. Create the layout: On a new sheet, enter up to seven dates in B1:H1 and the 24 hourly time values in A2:A25. Use Format Cells > Custom with dddd, dd to show weekday and day labels.
- 3. Summarize the counts: Enter =SUMIFS(Data!$D$2:$D$1000,Data!$A$2:$A$1000,B$1,Data!$C$2:$C$1000,$A2) in B2, then fill it across and down through H25.
- 4. Color and extend the report: Select B2:H25 and choose Home > Conditional Formatting > Color Scales. Copy the table and update the date headers for each remaining block of the month.

Frequently Asked Questions
How do I display a date as Monday, 01?
Select the date cells, open Format Cells, choose Custom, and enter dddd, dd. The cells must contain actual dates rather than text.
Can four weekly tables cover a 29-day month?
Four seven-day tables cover only 28 days. Add a fifth block for the remaining date, or use calendar-aligned tables with enough blocks to include the entire month.
Why does SUMIFS show zero for an hour with visitors?
Check that the Data sheet's dates have no time component and that its Hour values match the heat map's time values. Also confirm that visitor counts are numeric and the formula ranges include all source rows.
Can I use the same colors to compare different weeks?
Yes. In Excel, select each heat map and use Home > Conditional Formatting > Manage Rules to edit its color-scale rule. Set consistent minimum and maximum values across the tables.




