Project Management with Excel

There are countless project management software tools on the market. Many are extremely powerful and provide every aspect of project management imaginable. They also come with an equivalent price tag. But Microsoft Excel has wonderful features that make it an excellent project management tool – and since you probably already have it, it’s great for your budget!

There are many templates available online to help you get started.  But I recommend understanding the data validation and conditional formatting features in Excel to create or adapt dashboards and trackers for your projects. Just adding these features to your existing Excel knowledge will give you all sorts of options for managing your projects.

This is a simple example of a spreadsheet that includes columns for a project’s priority, team member, assignments, details and progress.  Download this example spreadsheet.
Project Management Spreadsheet

How to Set Data Validation:

In my example spreadsheet the Priority, Assigned To and Progress columns all use data validation to select from lists to fill data.  Use these steps to create a list of team members for project assignments.

  1. Data ValidationOpen a blank Excel spreadsheet
  2. Name the first tab/worksheet “Project”
  3. Add a column named “Assigned To”
  4. Add a new tab/worksheet named “Key” for your data
  5. In Key, enter the list of team members in a single column
  6. In the Project worksheet under the “Assigned To” column click in the first cell
  7. On the Data tab, select Data Validation
  8. In the Settings tab, select List from the dropdown menu
  9. Click on the Source button (looks like a spreadsheet) and navigate to the list of team members in the Key Worksheet
  10. Select the list of team members
  11. Click the Source button again
  12. Click OK to complete
  13. Copy the formatting of that cell to the entire column

Now you have a dropdown menu of team members to select from in your “Assigned To” column.  You can add to this list (in the Key worksheet) as your team grows, but you’ll have to update your data validation rule to include any added cells.

Combining Data Validation with Conditional Formatting you can create a dashboard to indicate the progress on each item in your spreadsheet.

How to Set Conditional Formatting:

With this formatting you can easily indicate the status of an item in your spreadsheet as not startedin progress or complete.

  1. New Formatting RuleFollow the steps above for Data Validation, but on the Key worksheet add a column named “Progress” and make the following three entries in the cells below:  -1, 0 and 1
  2. In the Project worksheet, add a column named “Progress”
  3. Click on the first cell under the Progress column
  4. From the Home tab select Conditional Formatting, then select New Rule
  5. In the New Rule Formatting dialog box select Format cells based on their values
  6. Select Icon Set for the Format Style (for this example, choose a set of 3 that display as red, yellow and green)
  7. Check the box next to Show Icon Only
  8. For green, set the value to 1 and change the Type to Number
  9. For yellow, set the value to 0 and change the Type to Number
  10. Red will automatically be set to < 0
  11. Click OK to complete
  12. Copy the formatting of that cell to the entire column

Now you can use the filter feature to sort the columns based on the priority, assignment, progress or whatever you like.  Go a step further and use the data collected in your project management spreadsheet to chart your progress and you’ve got a great way to manage and report on your projects.

Who knew Excel could be such a great communication tool!

Leave a Reply