Chart & Visualization Issues

Create an Excel Heat Map by Weekday, Hour, and Date

Emma BrownEmma Brown Nov 22, 2026 868 views

Question details

Create heat maps of visitor counts for each hour and date across several weeks or a full month.

Create an Excel Heat Map by Weekday, Hour, and Date
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the source data

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.

2
Set up date and hour headers

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.

3
Calculate visitor totals

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.

4
Apply the heat map

Select B2:H25, then choose Home > Conditional Formatting > Color Scales and select a color scale.

5
Add the remaining dates

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.

Build weekly heat maps with SUMIFS and color scales
Check the color scale: Separate tables may use different minimum and maximum values for their color scales. Edit each rule through Home > Conditional Formatting > Manage Rules if you need comparable colors across weeks.
Build an hourly visitor heat map

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. 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. 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. 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. 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.
Visualize visitor counts by date and hour with conditional formatting.Open and save Microsoft Excel XLSX files.Use formulas to summarize multiple entries for the same date and hour.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

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.