How to Build a Habit Tracker Dashboard in Excel

The build, stage by stage

The video is a full 33-minute build. Here are the main stages and the key settings, so you can follow along or jump to the part you need.

1. Set up the January sheet

In a blank workbook, rename the first sheet Jan. You’ll build January completely, then copy it for the other months. Select all cells and give them a light grey fill, set the column width to 4 and centre the text. Make row 1 about 45 high for the title and column B about 35 wide for habit names.

In B3 type Habits. In C3 and D3 type 1 and 2, select both and drag the fill handle right to 31. List your habits from B4 down, for example Drink water, Exercise, Read 10 pages, Meditate.

2. Format the tracker

• Type Habit Tracker in B1: 20 pt bold, white text on a dark green row.
• Shrink row 2 to 5 pixels as a separator, and give the header row (A3:AG3) a green fill with white, bold, centred text.
• Click C4 and choose View > Freeze Panes, so the days and habit names stay visible as you scroll.
• Select A4:AG13 (room for 10 habits), add dotted borders from More Borders, and set the fill to No Fill.

3. Add checkboxes

Select C4:AG13 (only the daily cells, not the headers) and click Insert > Checkbox. A ticked box has the value TRUE and an unticked one is FALSE, which is what lets formulas count them.

4. Count completions and percentages

Next to day 31, add three headers: Completed (AH3), Success % (AI3) and Progress (AJ3). Copy the header formatting across with the Format Painter.

• Completed, in AH4: =COUNTIF(C4:AG4,TRUE) counts the ticked days.
• Success %, in AI4: =AH4/31, formatted as a percentage.
• Progress bar, in AJ4: =REPT("█",AH4/31*20). Insert the full-block character from Insert > Symbol (Arial). Change 20 to make the bar longer or shorter.

Copy all three formulas down for each habit, and type the month name in C1.

5. Copy January for the other months

Right-click the Jan tab, choose Move or Copy, tick Create a copy and move it to the end. Rename the copy. For shorter months, delete the extra day columns (29 to 31 for February, 31 for April, and so on), check that the Completed formula still covers the right range, and change the /31 in the percentage and progress formulas to the number of days in that month.

6. Build the dashboard sheet

Add a sheet called Dashboard and move it to the front. Title it in A2 (24 pt bold). In A4 type Month, then list January to December from A5 down. Put the habit names across B4:E4.

Link each month’s totals: in B5 type =, click the Jan sheet, click its Completed cell for habit 1 (AH4) and press Enter. Repeat for every habit and month. On February, for example, the Completed column is AE because the sheet has fewer days.

Below the table add Year total in A18 with =SUM(B5:B16) copied across, and Year % in A19 with =B18/365 (366 in a leap year), formatted as a percentage.

7. Add the charts

• Line chart of A4:E16: monthly progress for each habit. Delete the gridlines for a cleaner look.
• Stacked column chart of A4:E16: all habits compared month by month, with the gap width set to 50%.
• Pie chart of B4:E4 plus B18:E18 (hold Ctrl): each habit’s share of the year, with white, bold data labels.
• Doughnut chart of the same ranges: a gauge-style view of the year, with the hole size at 50%.

Finish by turning off the sheet’s gridlines, giving it a light grey fill, adding borders to the table and tightening the row heights. No pivot tables or VBA needed.

Go further

My Excel Dashboard & Data Analysis Masterclass goes further, with 10 interactive dashboards built from scratch.