Google Sheets Use Case: Pipeline and/or Work in Progress Management

This is one of the most helpful things that google sheets can be used for. It works by way of some conditional formatting and use of the today(), days, and networkdays functions. I have used this with a few clients and there are all kinds of variations you can get with it. I am making the base template version available for free.


Access to the template: https://docs.google.com/spreadsheets/d/1Q1OrbpyB_6x-kMlhku3Oy3wS9v9mSZIUuQmVqoOHs4w/edit#gid=0

*Note, you must hit "File>Make a Copy" once you go to the sheet in order to get your own that you can edit.

Much of the details are gone over within the video so here I am going to talk about the high level use case and what is going on.

So, the idea is you could have a certain order or object that moves through stages.

Common Use Cases:
  • Mortgage company tracking the stages of the mortgage process. You would have days between various things being completed and it can flow linearly across from one thing to the next.
  • Law firm that tracks various stages of a case and needs to know how long it has been since a given event has happened.
  • Manufacturer that wants to track how long a given order takes to be completed through various stages.
Note I just used 3 events in the template, but there could be as many as you need.

The nice thing is that once you build this data, there is a lot of meta data an automated reporting you can do on top of it. For example, you can have a weekly/monthly/annual running tally of how many successful mortgage closures a given agent has had.

A lot of additional countdowns and tracking can be used with the time between date functions such as a countdown to a certain date. For example, if you know the end date must be completed within 60 days or 60 working days of the start date, you could have a column that tracks how many days are left until the deadline. This piece of logic was not added in the video, but it is just one example of more complex uses.

In this template, I also added some automated filtering so as things change and new things are added, you can get a snapshot of all open items that are red (meaning they are past the determined amount of days) as well as red closed items and green closed items. The filter formulas for that you will find in the google sheet cells A2 on the red and green colored tabs at the bottom.

If you want me to build customized process management builds, you can retain my consulting services here.