While managing project schedules, we often use excel dashboards to keep track of the defined targets and milestones. With tight deadlines, compact schedules, and a large number of activities / people involved, manual tracking usually becomes a time-consuming-cumbersome task and runs the risk of human errors. We need efficient tracking mechanisms and not line by line tracking of an endless list of activities.
One such mechanism allows us to auto-track schedules and any other element we need to monitor. The technique is simple but works like magic and can be used by all.
Here, we use excel formulas to automatically color code approaching dates and missed dates – with the logic given below.
At a glance, the sheet tells the activities that are approaching the due date and the ones which are missed and behind the target – all this without any manual eye scan / reconciliation across a lengthy document.
The formulae use current date – today() function to check if the target date is approaching or missed and accordingly color codes based on the defined rules. This way the dashboard will always show correct status color coding every morning or evening when you open it.
The formulae are embedded in the target cells that need to be tracked. Use the Conditional Formatting menu option to define or edit your rule. The snapshot shows the rule set for the activities that have missed the target date by one or two days.
My excel dashboard is attached for reference and re-use. Tailor it for your requirement and voila you have your own version of efficient auto-tracking devised!
About Author
Seema Kohli has over 18 years of IT experience. For past over ten years, she has been working in IBM in the software testing space in senior delivery management / test consultant roles. These include managing delivery of large and complex testing projects, driving and setting up test process assessments in test consulting and delivery engagements, developing mobile test automation framework, developing / supporting test proposals for different clients. LinkedIn
