I've always admired the way GitHub presents your contribution activity with a slick, heatmap-styled tracker. Let's create our own version in Excel.
Contents:
3.1. Conditional Formatting
3.2. Cell Size
3.3. Hide Text Values
Habit Tracker & Calendar Heat Map
There are a two main parts that we need to convey with our heatmap. First we need to format our data in a way that accurately represents the calendar year, and second we need present the data to show relative strength between days.
1. How to Set up Your Data
Let's start with the raw data. Start with a list of dates in one column, and the values you want to display in another.
The date column of the table should extend from the very first day of the year (1/1/XXXX) to the last day (12/31/XXXX).
In the sales column, add the values you want to display. If you're missing values for days, or are only part-way through the year, you can leave those values blank.
2. Formula to Format Data into a Calendar Format
Once we have the data set up, we can use a formula to convert the single column of data, into a calendar format:
=WRAPCOLS(sales_data,7,"")This formula takes your sales data as the first input, and wraps it every seven rows (once for each day of the week). The final "" at the end is our padding in case there aren't enough days to end the year cleanly.
You should see your values split across 7 rows. This forms the base for the heatmap.
3. Conditional Formatting Rules
3.1. Create Color Gradient
Now to turn these numbers, into a color gradient to visually display the relationship between all of the values.
For this we'll use one easy conditional formatting rule. Go to The home tab > conditional formatting > and insert a new rule.
Select the "Format all cells based on their values", then change the format style to 3-color scale.
Then choose your lowest value as a light grey, midpoint as a light shade, and then your maximum value as a darker shade. I used green here, but any color or combination will work.
You'll be left with a grid of values color coded by their ranking.
3.2. Formatting Cell Size
Next we need to make sure the cells are square in shape.
Match cell widths with cell heights. For example, if your row height is 18 pixels, set column widths to 18 pixels wide.
3.3 How to Hide Text Values
To hide the text values from the heatmap, select the entire grid, and go to the Home ribbon > Number > More Number Formats. Under "Custom" and in the Type box, enter ";;".
The two semicolons will remove all formats from the numbers, hiding them from view.
3.4 Creating Cell Borders
It's looking pretty good now, but we need to create some dividers between the cells to give it a more defined look.
Go to the Home ribbon, and press the drop-down arrow next to the borders icon.
Go own to the very bottom and select Line Color, and pick the white option and then go to Line Style, and pick the thickest option down at the bottom.
Apply it to all of the selected cells using the "All Borders" option
3.5 Adding Month and Day Names
Since there are 52 weeks in a year, place the month names approximately ever 4.33 weeks.
There will be some overlap between months, but this graph isn't meant to give users exact day/month numbers, but instead give an overall look at an entire year.
To add days, take the first day (in this case a Monday on 1/1/2024) and use that as the first row's day.
4. How to Adjust for Non-Monday Beginning Years
To shift the days between rows or account for a non-Monday beginning year, add plug dates at the beginning of the data table.
For example, if you wanted the week to start on Sunday, or since 1/1/2025 is a Wednesday, we can add dates from the previous year, to the top of the table.
This will shift the dates down by one. Moving Monday to the second row.
And that's all! A GitHub style activity tracker, made completely in Excel that updates dynamically to fit your data.