As a small business owner, I know how important it is to stay organized and keep track of projects. Excel is an excellent tool for project management. In this video, I'll be sharing my top Excel tips for managing projects effectively.
Let’s open the Excel project tracker template that I got from the Microsoft Create website (hint: if you're interested in other Excel templates, we have you covered). Once we have the project tracking template open, we can customize it to our needs.
Tip #1: Track the status of each step with a drop-down menu
The first tip to track projects in Excel is to monitor the status of each step. You can do this using the drop-down menu. Here's how to do it: Highlight the cells, go to Data Validation > List. In the Source field, type in the options for the drop-down menu (Not started, In progress, Done), then select OK.
Now I can click on the cell and select the status for each step. This way, I use the Excel project tracker to quickly see which steps are still pending, which ones are ongoing, and which ones are complete.
Tip #2: Track how much time is left in each project
The second tip is to track the number of days left to complete the project. In the "Days Left" column, type =Due Date-TODAY().
In this formula, Due Date references the “Due Date” column, which we subtracted from today’s date to get the remaining days before the task is due.
Using this formula in the Excel project tracker helps me to stay on top of deadlines.
Tip #3: Track your progress for each project
The third tip is to calculate progress for each project. To track progress for each project in the Excel project tracking template, first you count the number of completed tasks for each project using the COUNTIF function.
The COUNTIF function is used to count the number of cells in a range with certain criteria. So let’s type in progress column =COUNTIF(.
Now select the range, where range is the range of the cells to count. Now, it should look like this: =COUNTIF(ProjectTracker3[@[Task '#1]:[Task '#3]],
“Criteria” is the value we want to count. In this case, we want to see when it’s done, so type “done” in quotation marks, and it should look like this: =COUNTIF(ProjectTracker3[@[Task '#1]:[Task '#3]],”done”.
Now close the brackets, and we’re almost there: =COUNTIF(ProjectTracker3[@[Task '#1]:[Task' #3]],”done”).
Then, we divide that number by the total number of tasks, which is 3, to get the percentage of completed tasks for the project.
Tip #4: Visualize your progress
The fourth and final tip for project tracking is to visualize your progress in Excel with a data bar. Highlight the progress column, Go to Conditional Formatting > Data Bars > Choose a color.
Now I can easily see progress for each project in the Excel project tracker and see which one needs more attention.
As a small business owner, I have a lot on my plate, and these Excel tips help me to manage my projects more effectively and stay on top of my deadlines! Try using the Excel project tracker yourself.
Learn more
Interested in Excel and/or project management? Be sure to check out these other great articles from our creative pros: