Agile Ideas: Create a Kanban Board in Microsoft Project

Featured_Image_Kanban_Board

Editor’s note: I am not a historian, but I can read Wikipedia, so feel free to correct me if I am incorrect on any historical records.

Back in the 1940’s, Toyota invented the Kanban board as part of their just-in-time manufacturing process. The idea behind the Kanban board is simple. You simply post items that you need to do, are doing, and are done.

In 2001, a group of developers met and created the Agile Manifesto. There are a lot of ideas and concepts that came out of the manifesto, but these are the raw basics:

  • There is a backlog of work (to do’s).
  • When someone is working on an item, it moves from the backlog to work in progress.
  • When the item is complete, it is marked as done.

As you can imagine, visualizing this methodology is very much like Toyota’s Kanban board. The adoption of Agile methodologies has introduced a whole new software market for apps that produce these Kanban-like boards. As of this writing, the most popular of the general purpose products are Trello and Microsoft’s new Office 365 Planner product.


Banner_Ad_Fundamentals_No_Dates

Use code CAMBERBLOG for $50 off at checkout.

The obligatory waterfall discussion

If you are not familiar with the term, waterfall is a traditional approach to managing projects. You plan out everything that needs to get done, starting with the first thing and ending with the last thing. A project manager carefully links all the tasks in a schedule together using what we call predecessor and successor relationships. When the project is done, you will see a Gantt chart with all these  lines trickling down the screen like a waterfall.

Post_Kanban_Waterfall

Waterfall assumes you do not jump from one task to another. Sure, if you can get something done a little earlier or another thing takes a bit later, that is okay, but you stick with the plan.

In the Agile world, specifically Agile in software development, you can technically work on any backlog item you want (within reason). This means the developer can be working on something today that a traditional waterfall project may not list as needed for another two months.

Have your cake and eat it too

Post_Kanban_Board_Cake

At this point, you can see how my comments open up a can of worms (sorry for mentioning worms and cake but I am embracing my metaphors today). People in the Agile camp say you cannot model their work in Microsoft Project.Project managers want to track the project in some way, and it is not always just about software development.

Project managers want to track the project in some way, and it is not always just about software development. For example, project managers have lots of other non-software development tasks to worry about, like change management, training, resource management, and so on.

This need for software developers to be independent of a strict schedule and project managers wanting to track the project is an ongoing debate. For this post, I am not going to throw my hay into this discussion as I want to present you with something that will be helpful to any teams.

Get on the Agile bus

Have you ever heard of the trickle-down effect? The idea is someone makes a change, and that trickles down to everyone else. Many years ago, I worked for a company that was failing. The trickle-down effect was many of us got pay cuts, and others were laid off.Agile is having a trickle-down effect on other parts of business now. Here is an example:

Agile is having a trickle-down effect on other parts of business now. Here is an example:

I rent the Adobe creative suite so I can use Photoshop, Illustrator, and other products. Every few weeks, a bunch of new features automatically update on my computer. This, I imagine, is the direct result of Agile teams churning out new features. Suddenly, I have a new feature that never existed before, and I have to learn it.

If Photoshop has a cool new feature, the marketing team needs to advertise it, sales people need to communicate it, training companies need to update their material, authors need to release a new edition of their books, and so on, and so on.

See what is happening? Agile methodologies are trickling down to your line of business, like it or not.

Where to start (hint: reports!)

Editor’s Note: Go to the bottom of this post if you want to purchase a copy of the report in Microsoft Project format.

As a project manager, I have always struggled to find the right reports to help me track my projects. Forget whether my projects are Agile or waterfall for the moment. At the end of the day, I really (really) like the idea of using a Kanban board to see what is coming up, what is in progress, and what is complete.

Even if you build a waterfall style project plan, you can build a Kanban board in Microsoft Project to track tasks in your project. This is what the rest of my post covers.

Let’s get started

Microsoft Project has a fancy new reporting module but to follow on with this article, you will need Microsoft Project Professional 2013 or 2016 (if you are renting Project Pro from Office 365, that works too).

Create a new report

  1. Run Microsoft Project and open your favorite project, create one from a template or just build a simple one yourself.
  2. Click on the report tab.
  3. Locate the View Reports section and click the New Report icon. A menu appears as shown in the next image. Select the Blank item.
  4. The Report Name dialog appears. Type Kanban Board.

Post_Kanban_Blank_ReportAdd a title

  1. When the new report display, you will get a default title, like Report 1. Change the text to read My Project’s Kanban Board.
  2. This is going to be a wide report, so resize the title to stretch across the screen. Optionally, you can click the Format tab and add some flourish like the following image.

Post_Kanban_Title

Add some headings

  1. Click the Design tab, locate the Insert section, and then click the Text Box icon.
  2. Draw four text boxes onto the reporting surface. Optionally, you can give them a unique color by using the Format tab.
  3. Move the text boxes so they align next to each other under the title text box.
  4. Modify each label and type the following titles (left-to-right): BacklogIn Progress2 Week Planning Window, and Completed. Your screen should look something like the following image.

Post_Kanban_Add_Headings

Add the backlog table

  1. Click the Design tab, locate the Insert section, and then click the Table icon.
  2. A table will automatically draw on the report surface. Make sure you click on the table.
  3. Move and resize the table so it fits nicely under the backlog heading. It will look pretty ugly at first, but you will fix that in the next few steps.
  4. Locate the Field List pane at the far-right of the screen (note: if you do not see it, click the Table Tools Design tab and then click the Table Data icon). Locate all the fields in your table and de-select all but the Name field. The image below shows how the table will look.

Post_Kanban_Table_With_Name_Only

Display tasks in the table

At this point, your table just has the name of the project in it. You want to show unstarted tasks. Fortunately, Microsoft Project comes with a built-in filter for this. The filter will display all tasks that are 0% complete.

  1. Click the Backlog table.
  2. The field list pane opens on the right-side of the screen. At the bottom of the field list pane, click the Filter pick list and select the Unstarted Tasks item.
  3. Click the Group By pick list and select the No Group item.
  4. Click the Outline Level pick list and select the All Subtasks item. Optionally, you can modify the Sort By item, but since the tasks automatically sort by the start date, I do not recommend changing it. See the image below to view the settings.

Post_Kanban_Filter_Group_Outline

Where things stand now

At this point, your screen should look something like the following image, with only one backlog table on the screen. In the following steps, you will finalize the report.

Post_Kanban_Backlog_TableCreate the remaining tables

  1. Either copy and paste the backlog table 3 times or add three more tables and configure them as you did previously.
  2. Arrange each table so they appear under the various headings. Optionally, you can modify the color of the tables under the Table Tools Design tab.
  3. Select the in progress table. The field list displays on the right-side of the screen. Click the Filter pick list and select the In Progress Tasks item.
  4. For the time being, you will skip the 2 Week Planning Window because there is some more work we need to do on this one.
  5. Select the in progress table. The field list displays on the right-side of the screen. Click the Filter pick list and select the Completed Tasks item.

At this point, you should have a report that is nearly complete and should look like the image below.

Post_Kanban_No_Two_Week_Planning_Window

Create a custom field for the 2-week planning window

Microsoft Project has a lot of built-in filters, but any that specify a date range requires you to manually enter a date range. You want the Kanban board to display tasks within a 2-week planning window, and you do not want to modify the date ranges all the time manually.

The approach you take to make this work is twofold. First, you create a custom field that calculates the date range, and then you create the filter to use your custom field. In this section, you will create the custom field. In the next section, you will create the filter.

  1. Click the Project tab, locate the Properties section, and then click the Custom Fields icon.
  2. The Custom Fields dialog appears. Click the Task radio button, click the Type pick list, and then select the Flag item.
  3. Select the Flag 1 item, click the Rename button and type 2 Wk Pln Window. Click the OK button.
  4. Click the Formula button. the Formula dialog appears. type the following text (this is not going to look pretty) and then click the OK button.
    (([Start]>DateAdd(“d”,-14,now()) Or [Finish]>DateAdd(“d”,-14,now())) And ([Start]<DateAdd(“d”,14,now()) Or [Finish]<DateAdd(“d”,14,now()))) Or ([Finish]<DateAdd(“d”,14,now()) And [% Complete]<100)
    Note: This formula looks at tasks that start or finish within a 14 day planning window.
  5. Click OK to close the custom fields dialog.

The image below shows how your custom fields and formulas dialogs should look.

Post_Kanban_Custom_2_Wk_Custom_Field

Optionally, you can add the 2 Wk Plan Window field to the Gantt Chart as you can see in the image below.

Post_Kanban_2_Wk_Column_In_Gantt

Create the 2-week planning window filter

At this point, you have a field that displays yes for tasks that start or finish in the next two weeks and no for any other tasks. Now, you need to create a custom filter that your report will use.

Note: You can create filters in the report module, but I am showing you how to create a generic filter that a report can then make use of.

  1. Click the View tab, locate the Data section, click the Filter pick list, and then select the More Filters… item.
  2. Click the Task radio button.
  3. The More Filters dialog appears. Click the New button.
  4. The Filter Definition dialog appears. In the Name field, type 2 Week Planning Window.
  5. Select the Show in Menu item (if not already selected).
  6. Click the mouse directly under the Field Name column. A pick list appears. Select the 2 Wk Pln Window ( Flag1 ) item.
  7. On the same row, click the mouse directly under the Test column and type equals.
  8. On the same row, click the mouse directly under the Value(s) column and type Yes.
  9. On the Filter Definition dialog, click the Save button.
  10. On the More Filters dialog, click the Cancel button.
    Note: You click the cancel button because you do not want to apply the filter to your project right now. If you clicked Apply by accident, this is easy to fix. Go to the View tab, click the Filter pick list and select the [no filter] item.

As a recap, you first create a custom field with a formula that determines what tasks are within a 2-week (14 days) planning window. Then, you create a filter that will only show tasks that start or finish within 14 days. The image below shows how your filter should look.

Post_Kanban_2wk_Custom_Filter

Update the 2-week planning window table

  1. Click the Report tab.
  2. Locate the View Reports section and click the Custom icon. A pick list appears. Select the Kanban Board item.
    Note: This is the report you created earlier.
  3. Click the 2 Week Planning Window table.
  4. The Field List pane display at the right of the screen. Click the Filter pick list. A menu appears. Select the 2 Week Planning Window filter.
    Note: The Group By field should display No Group, and the Outline Level field should display All Subtasks.

Post_Kanban_2_Week_Filter_Applied

Banner_Ad_Fundamentals_No_Dates

Use the code CAMBERBLOG at checkout to receive $50 off your training course.

The finished report (ish)

When your report is complete, it should look something like the image below.

Post_Kanban_Finished_Report_No_Burndown

Extra Credit: Add a burndown chart

This report might look fine and good, but there is one more thing you might want to add. A burndown chart shows you how many tasks are left to complete over time. If you use baselines in your project (another topic outside the scope of this post), you can compare your original plan to the new plan. This is a powerful chart because it will tell you whether your tasks are completing promptly.

To add the burn-down chart, I am going to give you some very basic steps, so you will need to do most of the navigation without my aid.

  1. Draw a Text Box onto the report surface (preferably in an area with white space) and add the text Task Burndown.
  2. Click the Report tab, click the Dashboard icon, and then select the Burndown item.
  3. Select the Task Burndown chart (the one to the right) and copy it to the clipboard.
  4. Click the Report tab, click the Custom icon, and then select the Kanban Board item.
  5. Paste the burndown chart and move it to a location under the Task Burndown heading.

The finished report

Great job! Your new Kanban board should look like the following image.

Post_Kanban_Final_Report_With_Burndown

Summary

With your report now complete, you can use it to keep an eye on what is happening right now, and for the next 14 days. The backlog table shows tasks that no one is yet working on. The completed table just shows tasks that are done (100% complete).

The Backlog and Completed tables can get really long, so play around with creating filters that only show 30 or 60 days. That formula I provide for the 2-week planning window can come in handy as a template for creating your own filters.

Here are some common questions people ask me and my answers:

  • Q: How do I update the report? It updates automatically. If, for some reason you turned off automatic calculation on your project, press the F9 key on your keyboard.
  • Q: Can I print the report? Yes. That is why my report is left-justified. When you are on the Print backstage area, click the Page Setup link, set the Orientation to Landscape, select the Fit to and set to 1 pages wide by [leave empty] tall.
  • Q: Can I share the report? Yes. You can use the Organizer. I will not get into all the details here, so I suggest you do a search on the web for Microsoft Project Organizer. To get to the Organizer, click the File tab, select the Info item, and then click the Organizer icon.
  • Q: Can I get a copy of your file, so I don’t have to re-create it? Yes. You can purchase the report for $5. Just click the link below.


Purchase

About Bill Raymond

I am an author, public speaker, trainer, and consultant. While working in the product development, portfolio and project management markets, my personal objective is to help companies and individuals strategically plan and deliver business growth. As a result of my commitment to community, I am the proud recipient of the prestigious Microsoft MVP award for the past 12 years. I have also been honored with speaking awards from organizations such as MPUG and NASA.

Leave a Reply