Mini-project: build your own exam study dashboard

The best way to learn Excel is to build a tool you need anyway. You need a study tracker. Let’s kill both birds.

Sheet 1, the plan

In A1:B4, four inputs: Exam date, Weekly hours, Target total hours (300 for a prelim), Start date. Now generate every study week in one formula:

A7: =SEQUENCE(CEILING((B1-B4)/7,1), 1, B4, 7)

Next to it, cumulative planned hours:

B7: =SEQUENCE(ROWS(A7#),1,1,1) * $B$2

That A7# is the spill reference. It means “however many weeks SEQUENCE produced.” Two formulas, whole plan.

Sheet 2, the log

Three columns: Date, Hours, Topic. Every session, one row, ten seconds. This log is the whole discipline, everything else is decoration.

Back on Sheet 1, the reckoning

Cumulative actual hours up to each week:

C7: =SUMPRODUCT((Log!$A$2:$A$999 <= A7#)*(Log!$B$2:$B$999))

And the column that changes behavior, pace:

D7: =C7# - B7#

Negative means behind plan. Add conditional formatting: red fill when D is below −5, green when ≥ 0. That red cell staring at you on a Tuesday is worth more than any motivational video.

The 100-hours check

One cell of truth: =B3 - MAX(C7#). Hours remaining. Divide by weeks left for the required weekly pace: watch how skipping two weeks quietly turns “8 hours a week” into “13.”

What you just practiced

SEQUENCE, spill references, SUMPRODUCT with date conditions, and conditional formatting, four interview-grade skills, disguised as a study plan. Want the deluxe version with a mistakes bank, syllabus weights and pace forecasting? Add them yourself, one tab at a time. The dashboard you build teaches you more than any dashboard I could hand you.