Tutorial Overview: Workflow Automation Tracker Report in Excel

Introduction

• Explore the workflow automation tracker report using Microsoft Excel.
• Suitable for both beginners and experienced Excel users.
• Learn to leverage key features for dynamic and interactive project tracking.

Key Features

1. Project Management

   • Project Name: Identify the project you manage.
   • Project Start Date: Define the official start date.
   • Project Process Percentage: Monitor overall project completion rate.

2. Task Management

   • Fields: Task Title, Responsibility, Remarks, Task Duration, Status, and Process Percentage.
   • Dynamic Search Bar: Search tasks quickly within the task and responsibility column.
   • Task Process Percentage Bar Chart: Visual progress tracking.

3. Workflow Calendar Chart

   • Overview of all 53 weeks of the year.
   • Detailed insights into the start weekday number and name.
   • Visual timeline from start to finish.

4. Navigation Buttons

   • Located on the left side for seamless project switching.

Step-by-Step Guide

Workflow Calendar Chart Setup

1. Add Project Start Date (Cell G6)

   • Enter project start date; this serves as the reference point.

2. Link Start Date to First Column (Cell N8)

   • Use the formula = G6 to link cells. Updates adjust the calendar chart automatically.

3. Populate Year Numbers

   • Show weekly dates; use formula = N8 + 7 to add 7 days incrementally across cells.

4. Change Number Format

   • Format cells to display only the day of the week using custom 'D' format.

5. Week Numbers Display (Row 7)

   • Add or calculate year week numbers manually.

6. Short Day Names Display (Row 9)

   • Use a formula to show short names of the days correlating to dates.

Conditional Formatting for Task Duration

• Set up using task start/end dates in cells G11 and H11.
  • Formula for Conditional Formatting:
    • = N8 >= $G11 AND N8 <= $H11
  • Ensure format applies only within the date range.

Creating Navigation Buttons

1. Insert Rectangle Shape

   • Position as the base of navigation buttons with gradient colors.

2. Add Button Shapes

   • Choose shapes from Excel's library and place appropriately.

3. Add and Link Text Boxes

   • Label with project numbers.
   • Link each text box to corresponding project sheet using hyperlinks.

Conclusion

• This tutorial covers setting up a dynamic Excel workflow calendar chart.
• It includes conditional formatting and navigation buttons for efficient project management.
• For downloadable templates and additional resources, visit other-levels.com.

Final Remarks

• Share the tutorial and ask questions in the comments for assistance.
• Check out more Excel tips on the channel.