Forget Premium Tools, Here's How I Use Excel for Project Management

Quick Links

You might be tempted to browse the web for top-of-the-range programs to help you with your project management. But stop—I’m going to talk you through some of Excel’s tools that you can use and reuse to efficiently manage your project without having to fork out for expensive software.

Use Drop-Downs

Drop-downs are a great way to speed up your work processes, and make your project management system more professional. First, create the options to appear when you click a drop-down cell. Click the “+” at the bottom of your workbook, and double-click the new tab to rename itOptions.

I need employee names and task status as drop-down options in my workbook, so I’ll create the lists for these here.

Now, create another sheet where the tasks will be managed and rename itTasks.

After creating a table with the task names on the left and an appropriate header at the top, select the cells that will contain the first drop-down with the options you just created on your Options sheet.

Next, in the Data tab on the ribbon, click “Data Validation.”

In the Allow field of the Data Validation dialog box, choose “List.” Then, click the Source field arrow, head to your Options sheet, and select the appropriate values for this drop-down. In my case, it’s the values underneath the Employee Names heading, and their cell references will then show in the dialog box field. Even though the list of names runs from A2 to A10 on our Options sheet, I’ve selected A2 to A100 for our data validation, as this means any new names I add to the list will also be picked. Finally, click “OK.”

You will then see the list of names appear when you click any cell in the Assignee column.

Now, repeat the process for the Status column, and anytime you want to add a drop-down list to your workbook, you can use andhide your Options sheetto create the choices.

Create Gantt Charts to Track Progress

A Gantt chart is a simple but effective table that shows you what task needs to be done in a project and when they need to be completed.

Excel has tools for creating simple Gantt charts, but they are less adaptable than those created from scratch. Keep reading to see how to create a more dynamic Gantt chart.

Step 1: Mark Your Timings Manually

Click “+” at the bottom of your workbook to create a new sheet, and call itTiming. On this new sheet type the tasks' names on the left and the months at the top. Next, map out your proposed timings using manual color fill. It doesn’t matter what color you use, as this will be covered up later when we add more settings. Select the first cell you want to color, hold Ctrl, and then select the remaining cells. Then, go to the “Fill Color” drop-down in the Home tab on the ribbon and choose a color.

Step 2: Color the Cells According to Progress

I now want to color the cells according to their status. I’ll do this by referring to the status I set for each task on the Tasks sheet set up in the section above. In the first colored cell of your Gantt chart,use the VLOOKUP formula:

wherexis the cell reference in the chart you’re looking up,yis where Excel should look to find the corresponding value, andzis the column number within the array.

So, here’s what I’ll type into my first Gantt chart cell:

Because I have usedmixed and absolute references (using the $ symbol) within our formulas, I can copy (Ctrl+C) and paste (Ctrl+V) this formula into the other colored cells.

If you use a black color fill, use white font so that you’re able to see the values against the black backgrounds.

Finally, use Conditional Formatting to color the cells based on the values they contain.

Select all the cells in the Gantt chart, and in the Home tab on the ribbon, click Conditional Formatting > Manage Rules.

Click “New Rule” in the dialog box that appears, and create the following conditions (after you have set each one, click “OK” to set the next):

You will then see the relevant Gantt chart cells change color based on their status in the Tasks sheet.

Step 3: Highlight the Current Month

Start by typing the first date of each month in short form where you originally typed the name of the month. So, for example, replace the text in the cell containing January with01/01/2024. Then, do the same for February, beforeusing AutoFill to complete the remaining months. Doing this tells Excel that these are dates, and not just text. Next, change the font color of these dates to gray.

To turn these back into the names of the months, select all the dates, click the Number Formatting drop-down option in the Home tab on the ribbon, and click “More Number Formats.”

In the Format Cells dialog box, open the “Number” tab, and click “Custom” in the Category list. Then, in the Type field, typeMMMM.

Then, click “OK” to see the result. To now make the current month stand out, select all the months in your Gantt chart, and click Conditional Formatting > New Rule. In the dialog box, click “Format Only Cells That Contain,” select “Dates Occurring” in the first drop-down menu, and “This Month” in the second. Next, choose the formatting you want to use to make the current month stand out, such as black and bold text. Finally, click “OK.”

You now have your completed and dynamic Gantt chart with the progress displayed and the current month emphasized.

Create a Progress Tracker

Using the Gantt chart created in the steps outlined above, you can now create a progress tracker. You can either do this on the same sheet as where your Gantt chart is located or on a new tab. In my case, I want to show how many squares in our Gantt chart are marked as Done, In progress, Not started, and Changes required, and then calculate an overall progress percentage.

To do this, I’ll need touse the COUNTIF function, which follows this syntax:

So, for the Done count, we will type

Then, copy and paste this formula for the remaining details in your progress tracker, changing valueyto match the value you’re looking to count.

Next, calculate the overall progress using the following formula:

Whereais the number of cells in your Gantt chart containing the word Done, andb,c, anddare the number of cells containing the other status markers. Remember to change the number format of this cell to a percentage.

With the cell containing the newly calculated percentage selected, in the Insert tab on the ribbon, click the Chart button highlighted below, and select a 2-D bar chart.

Then,format your chartto remove any details you do not require, resulting in a progress bar showing your overall progress.

Remove the gridlinesto make your progress bar easier to read and look more professional.

Display Due Dates and Time Remaining

As well as tracking your project’s progress, you can also track the time elapsed and time remaining.

1. First, type the start and due date manually using a date format that suits your region. Excel will automatically convert this to a date format, and you canamend the date formatif required.

2. Next, add today’s date by typing

and pressing enter.

3. Third, calculate the days elapsed so far using the following formula:

wherexis the cell containing today’s date, andyis the cell containing the start date. Then, calculate the weeks elapsed by dividing the days elapsed by seven.

4. you’re able to also calculate the days remaining with the following formula:

whereais the cell containing the due date, andbis the cell containing today’s date. Again, calculate the weeks remaining by dividing the days remaining by seven.

5. Then, create a percentage of the time passed with the following formula:

Wherecis the total number of days elapsed anddis the total number of days remaining.

6. Finally, create a 2-D bar chart using the method described in the previous section.

Now you’ve created the perfect spreadsheet for project management, considerusing Excel to help you monitor your budgets!