Build a FREE Excel Task Tracker with Automatic Schedule (No Macros!)

Setting Up Drop-Down Lists

• 💡 Subjects and Types (assignment, coursework, exams) are defined and formatted as Excel tables named 'subject' and 'type'.
• 📌 This ensures that drop-down lists will automatically update as new items are added to these tables.

Creating the Task Tracker Table

• 🎯 A main table named 'tasks' is created with columns for task, subject, type, due date, time required, status, days available, notes, and links.
• ✅ Data validation is applied to the 'subject' and 'type' columns, using the defined table names for dynamic lists.
• 🛠️ A data validation list for 'status' is created directly within the dialog box with options: 'not started', 'started', and 'completed'.

Enhancing Task Visibility with Formatting

• 📈 A 'days available' column is calculated using the formula =TODAY() - Due Date and formatted as general.
• ⚠️ Conditional formatting highlights overdue tasks (days available less than zero) with a light red fill.
• 🎨 Statuses are color-coded: 'not started' in dark blue, 'started' in purple, and 'completed' rows are grayed out with a strikethrough effect.
• 🚦 Conditional formatting rules are managed to ensure the 'completed' status formatting takes precedence.

Interactive Dashboard with Slicers

• 📊 Slicers for 'subject', 'type', and 'status' are added to the table, allowing for intuitive filtering and interactive dashboard functionality.
• 🖼️ Slicers are reformatted to fit designated spaces, enabling users to focus on specific tasks and identify issues quickly.

Building the Automatic Schedule View

• 📅 A separate sheet is set up to display a schedule, extracting non-completed tasks from the tracker using SORT and FILTER functions.
• 🗓️ A horizontal date row is generated using the SEQUENCE function, spanning from the earliest to the latest due date in the tracker.
• ✍️ Custom number formatting is applied to dates for clarity (day and month name), and formulas reference spilled arrays using a hash (#).
• 🎨 Formatting is applied to the date row, including bolding, blue font color, and top/bottom borders.
• 📌 Panes are frozen at cell G6 for easier navigation of the data.
• ✨ Conditional formatting is used to replicate banded rows for readability, as tables cannot be used with spilled array formulas.
• 📍 Conditional formatting also highlights each task's due date on the schedule by comparing the task's due date with the dates in the horizontal row, ensuring the highlight rule is prioritized over banded rows.