Viewing Time: Project Management Procedure Using Google Sheets

Viewing Time: Project Management Procedure Using Google Sheets

Posted by R0bin_L0rd

The short version of this post: Project management is an essential part of our marketing work, but planning and visualization of projects over time is difficult. this job easier for you.

I found this system useful in several ways. So I share my models here in case it would reduce your day. I will begin with a brief overview of what the sheets do, but in the last part of this article, I will also expand on them so that you can modify them according to your needs.

If you want to skip this publication and go directly to the templates, you can access it here (but I recommend you read a bit on how they work first):

  • Planner Version (All You Need to Know, plus Gantts)
  • Stakeholder Version (a cleaner version for bosses, customers, or people doing the work but not managing a project)
  • Missed version (combined view of many different projects, telling you if you forgot to talk about work to someone, deadline or planning)

It should be mentioned that I do not consider these leaves as the only solution. It's a free solution that I found very useful, but I have colleagues who swear by Smartsheet and Teamwork.

It should also be noted that different tools work better or worse with different styles. My goal with these cards is to have a pretty concrete plan for the next three or four months, then a broader set of ideas to go further. When I complete these sheets, I also focus on results rather than processes – this helps to reduce the time spent updating sheets and makes everything that people can read more clearly.

The long version of this article looks a lot like the short version above, but I'm talking in more detail about some of the principles I'm trying to follow and how this configuration fills them (shocker, huh?). As promised, the last section will describe how the leaves work, for anyone who has problems or wants to do something.

Table of Contents (if you simply want to move to a specific section):

The 3 Principles (Concerning Both People and Leaves)
An Early Conclusion
Appendices & Instructions
How to Add Tasks to the List
Splitting Tasks over Multiple Periods
Using the Monthly View Tab (Scheduler and Stakeholder Versions)
How to Operate the Gantt Charts ( and add categories)
How to Operate Gantt Charts Looking Forward, Can Be Filtered by Category
How to Create the Partner's View
How to Update the Version of God-I

Principles (which concern both people and the use of leaves)

Principle 1: We should not need to store all our information in our heads.

This is simple – if we have to understand something complex on a regular basis, especially if it changes over time, this information must be on the page. For example, if I try to plan a marketing strategy and I have to constantly watch the information on the screen and mix it in my head to find what we have time to do month after month, I I'll lose the thread and eventually my mind.

The purpose of Planner is to solve this problem in several ways. First, you write all the tasks in the tab the period at which you complete them is on the far left (in my example, it is the month for which the task is planned), and there are other columns like status and category – but initially, it can simply be a brain drain of what needs to happen. The idea is that when you start writing everything, you do not have to think about it too much – you can easily change the dates and add more information later.

The tab View tab takes the information from the Task List tab and rearranges it according to the months listed in column A of the job view. . be of other periods, as long as it is consistent).

In this way, you can look at a period, see how much resources are left behind, and read all that you currently have planned (the calculation of the remaining resources will also take into account recurring tasks that you do not always want to write, such as meetings ).

While the Month View tab can help you focus on specific periods, it does not give you a long-term view of plan or task dependencies. So we have two Gantt views. The tab Gantt tab contains everything it was sixty days ago or more, provided you did not simply mark the task as "Later". The Gantt that can be filtered by category focuses only on the elements planned for the next six months.

As its name indicates, you can filter this second Gantt to display only specific categories (you label the tasks with categories in the Task Display tab ). This filter is intended to help with more general trends that are more difficult to notice – for example, if the most important part of the project is a social campaign or site change and you do not get it not for six months, you may need Of course, everyone is aware and agrees. Similarly, if you need to be impactful but spend most of your time reporting, you can change your plan or to make sure everyone understands why things are planned in this way.

Principle 2: Nobody knows everything (and they should not have everything).

If you are working on a project where you have all information, then one of two things is likely to happen:

  1. You've really doubled the neurosis we share
  2. You wear this thing – you should just quit your business and sell your own pearl-selling business * or something like that.

have more context than we do on broader plans and pressures. They may know more about broader strategies, than their boss blames each time a project is mentioned or that a colleague has not yet announced his resignation . Although a Google form is never an acceptable substitute for real communication, our customers or bosses can also get an idea of ​​where the project will go, what they did not communicate or did not understand. .

We can also be sure that people working on individual tasks have a good idea of ​​the problem – for example, if we give too little time to a task. We can try to be as informed as possible, but they are likely to know something .

Although we do not agree that some things should be a priority or priority, having a transparent and shared plan helps us to engage in difficult conversations with a common understanding of the plan . The less everyone has to reprocess the information to understand it (see Principle 1), the more likely we are to eliminate problems quickly.

This is fine, but expect someone to absorb everything on a project is likely to have the opposite effect. We need a source of data that everyone can refer to, without overburdening their thoughts or conversations with things that we, as project leaders, need to worry about.

That is why we have the Stakeholder Version of our leaves. When we write everything in the version of the planner the planned tab contains only the relevant items for people who are not us (all the tasks for which the l? state is nonexistent). are not "disheveled", "canceled", "forgotten" or empty) without any information about the resource or project identifier.

We never have to fill the sheet Stakeholder Version – it simply captures the information from the tab Planned using importrange () and creates all the same Gantt charts and monthly views – so we do not have to worry about different plans showing different information.

* Bees?

Principle 3: I'm going to miss things (minus that's more).

I'll be honest: I spent a lot of time preparing tracking systems that I do not check enough. I keep filling them but I do not spend enough time figuring out what is needed where. If we have a Stakeholder Version that eliminates anything that is irrelevant to other people, we need the same thing for us. After all, it's not the only thing we think about either.

The Version of Name of God – I Missed Date () now extracts data from all your project management records and gives you a place where go to remember all the things you have forgotten and messed up. It's like having dinner with your parents in a Google listing. You're welcome.

The three places to check in this version are:

  1. Alert Dashboard Tab which indicates the number of upcoming or missed deadlines, the budget to be budgeted or allocated and the unscheduled budget by project and month (internal budget only). people-hours, because is still ).
  2. Task Issues Tab which gives a filterable view of everything over the next three months the alerts you see in step 1).
  3. The Deadline tab so you have a quick reminder of what you need to finish soon.

A quick conclusion:

Often when I argue my point of view, people tell me that they hope I will finish early. This section is primarily a proof of personal growth.

This is also because everything that follows is specific to the use, modification or understanding of the project management sheets that I have shared. So just read the following if you want to use the sheets or how to create them. (I really recommend using a few uses of filter () and query (), especially with RegEx formulas).

Other than that, I hope you find these resources useful. I've derived a lot of value as a way to plan collaboratively with people and separate the concept of "project manager" from "person who needs to know all things", but I would really be interested have on how to improve them or whatever you think I have missed. Do not hesitate to comment below!

Access the card templates here:

  • Planner Version (All You Need to Know, plus Gantts)
  • Stakeholder Version (a cleaner version for bosses, customers, or people doing the work but not managing a project)
  • Missed version (combined view of many different projects, telling you if you forgot to talk about your work, missed a deadline or planned)

Appendices and instructions

Some general remarks [19659049] Notes Quick Tips to Avoid Problems:

  1. Make sure that when copying sheets, sharing permissions for scheduler view are based on email or at least on organizations The Parties View Stakeholders will display the URL Planner View ). It is a good idea to retain the authorizations of God-I Version by e-mail.
  2. Try to follow the existing format of words and numbers as closely as possible when creating new information.
  3. You want a new line, insert a line, select the one above, copy it to the new line, and edit the information – this way, the formulas in the hidden columns should still work for you.
  4. If you want a new column, it could break one of the query () functions; Once you have added it, quickly find the formulas with the help of = query () and consider modifying the columns to which they refer.

Notes on Problem Solving:

Here is a list of things to check if you have changed something and it is not reflected in the sheet:

  1. Browse all tabs in Stakeholder View and view all hidden columns.
    1. They usually contain a formula that reformats the text for our searches to work. See if any of these items is missing or broken.
  2. Try copying the formulas from the line above or beside the cell that does not work.
  3. Try to delete the = iferror part of formulas.
    1. Many cells are set to be empty if they break. It is easier to read the sheet, but it can be more difficult to know if something is empty or seems empty.
  4. If one sheet does not correctly pull the data of another, look for the formulas = importrange () and make sure that there is one that matches the URL of the sheet you are trying to reference and that you have allowed the formula to work – you will need to click a button.
    1. Check tab View tab of the Stakeholder Version and URL of the version of the God- I
  5. Have you just called a "Part 4" task or the like? There is a RegEx formula that will remove that.
  6. Have you forgotten to give a task a task? If this is the case, the Gantt view will warn you in the Status column.

The query function

The = query () function of Google Sheets is impressive – this greatly facilitates the task, especially with regard to automation of data manipulation. Most of these tasks can be done with = query, but I have often used = filter (which is also very powerful) because = filter is apparently faster in Google Sheets and sometimes these sheets have a lot to deal with.


You should not need to know RegEx for this sheet, but is useful in general. Here, RegEx is mainly used to delete the "part number" in multi-part tasks (see below) and search for everything that corresponds to several options – for example, when selecting multiple categories in the [19659005] tab (see below). RegEx is only used here in RegExmatch (), RegExextract (), RegExreplace () or as part of the query function where we say "matches".

Query / filter and isblank

A large number of the formulas in these sheets are filter () or query () or are wrapped in = if (isblank () – this is essentially because filter and Query functions can fill more cells than the one you put the formula in. For example, they can fill a row, a column, or an entire sheet, which means that other cells calculate or search for empty cells or not, so I added the check isblank () so that the cells do not break when there is no information somewhere, but like you add information you do not have to do as much copy / paste of formulas.

Check the boxes

Checkboxes are relatively new in Google Sheets. If you need it from another, simply copy it from an existing cell or select it from the "Insert" menu. When I've used checkboxes, the sheet often contains another formula that filters the rows based on the checked boxes, and then creates a RegEx based on the checked values.

You do not need to understand this to use the leaves, but you can see it in the unmasked lines in the Gantt-specific category-specific tab of the game taking part Version .

Tip: If you want all boxes checked / unchecked and you do not want to do one by one, you can copy a checked box or not to all other cells.

How to add tasks to the list

In the task view, the most important items to include are the task name, expected period, cost and type.

For convenience, when creating a new task, I recommend you insert a line, copy the line above, and then edit the information so not to miss hidden formulas.

Again, do not bother to change the Stakeholder Version. Once you have added the URL of the version of the planner to the = importrange () function, it will automatically be extracted from the version of the planner .

Splitting tasks over multiple periods

You can put more than one thing in the period of a task, simply by separating it with "," (comma space). Indeed, when we get the complete list of months, we gather all the individual cells with "," then we divide them by "," and deduce the list. Thus, several months in a cell are treated like all others. month.

  = unique (transpose (split (JOIN (",", "task view"! A: A), ",", 0)))

The cost-per-month formula in the tab of the job list counts the number of commas in the month column for that row, and then divides the planned cost by that number, which means that the cost is evenly divided across all the months listed.

  = H2 / (len (REGEXREPLACE (A2, "[^,] *", "")) + 1)

If you do not want the task to be completely divided between different periods, you can write "Part 1" or "Part 2" next to a task. As long as you simply write "Part" and then the numbers at the end of the name, this will be removed in column O of the task list tab so that the different parts of a task are combined into a single record in things like the Gantt Chart.

  = REGEXREPLACE (B2, "Part  d + $", "")

Using the Monthly View tab (Scheduler and Stakeholders Version)

Some key elements are available in the tab Monthly View . First, we receive all the periods listed in the Task View .

Since the months do not always display in the correct format (which means the subsequent filters do not work), we then use the formula = text () in the hidden column B months remain in the format we need.

Then, in the "deliverables" section of this tab, we use the formula below:

  = if (not (isblank (A12)), iferror (TRANSPOSE (FILTER (& # 39; task view & # 39 ;! B: B, RegExmatch (& # 39; task view & # 39 ;! A, B12))), ""), "")

What we do above is to check if the cell "month" of this line contains anything. If corresponds to per month, we filter the tasks in the job view only for those that contain this month in the month column of the text. Then we use the transpose () function to change our filtered tasks from a vertical list to the horizontal list we see in the sheet.

Finally, we use the formula below to filter the costs we have listed in the Task View tab in the same way that we have filtered the task names above. Next, we add all the costs for the month (plus the ongoing monthly costs) and subtract the total time / hours we have to spend. In this way, we calculate how much we have left to play, or if we are crushing ourselves.

  = if (isblank (A12), "", ((D12-SUM (FILTER (& # 39; Tasks View & # 39 ;! I: I, RegExmatch (& # 39; Task View & # 39; A: A, B12)))) - sum ($ D $ 6: $ F $ 8)))

We also pass this value to our God-I Version to see at a glance if we have planned or passed.

How to Operate Gantt Charts (and Add Categories)

Column C of the Task View Tab is the category; You must also complete this form for the Gantt charts to work. I did not force the kind of categories you to use because each project is different, but it's worthwhile to use consistent categories (up to the uppercase letter ) because we deduplicate the task categories of the names being consistent.

What happens in the Gantt chart is that each cell is a combination of filter and vlookup (the table below is more complicated than it is).

  = iferror (if (not (or (isblank ($ D6), ISBLANK (F $ 1))), vlookup (filter (view tasks) $ C: $ C, & # 39; Task View $ O: $ O = $ D5, REGEXMATCH ('Task View' $ A: $ A, F $ 2)), Status & Code of color $ C: $ E, 3.0), ""), "")

The formula first checks whether the task or month cells are empty. If this is not the case, he looks in the cell of the month in his column and cross-references with the task cell in his row. When the intersection of a month and a task corresponds to a task in our Task View (because in this line runs during the month in this column), the filter formula returns the category. For those interested, it may also have been done with index-match, but filter allows us to match with RegEx so we can give multiple matching options and they do not have to match exactly . Since we allocate tasks over several months, we need to be flexible in our correspondence.

The reason we check whether the task or month cells are empty, as mentioned above, is that we can paste the formula above into all cells of the Gantt chart and fill them. months and tasks, rather than having to copy and paste the formula each time.

When our filter formula returns the specific category of our task, we take this value and execute a vlookup in the Status and Color Code tab . (This is only necessary so that I can configure conditional formatting for you so that it does not break when you change the specific category names.)

For the moment, the Gantt charts are configured to color code the first 7 categories, plus a category Deadline if necessary. If you want to add more, they will initially appear in the Gantt chart as a black block and you will need to configure a conditional formatting to color code them.

To add automatic color formatting to multiple categories, repeat the process below for each of the Gantt chart views in the sheets Planner and Stakeholders :

  1. Select all cells in the
  2. tab Select "Conditional formatting" on the Format menu
  3. Find the rule with the black box next to it and note the currently targeted number
  4. Create a new new rule for all that is equal to the number in step 3, then set the same color for the background and the text of this rule
  5. Modify the rule that contains a black block to target a greater number

do the work of category-filterable Gantt charts

This tab uses our old friends, the functions = filter () and = query (). We first use the filter to enter the complete list of categories in the tab Status and color code we have already mentioned:

  = FILTER (G Gantt View! A6: B, RegExmatch (G Gantt View! A6: A, ". * [a-zA-Z]. *"))

Next, we placed beside them the glossy checkboxes of Google Sheets (this will help us to easily filter our data).

Normally, we hide the first line, but it is visible to display a formula that examines all categories and filters them only to those where the check box is checked. If there is none, it returns "( d | Deadline)" meaning "either a number or the word Deadline" in RegEx-speak (so any thing in our list ), because the vertical tube "|" means "or" and " D" means "number".

If there is a check mark next to one or more categories, the formula will return these things, separated by "|", which again means "or" in RegEx.

  = if (countif (C3: C, True)> 0, CONCATENATE ("(", JOIN ("|", FILTER (B3: B, C3: C = True, not (isblank (B3: B)) )), ")"), "( d | Deadline)")

Then, in cell E3, we have a query formula. The reason we use = query and non = filter is that we need to look for items in more than one column; The filter can only really handle one column at a time.

The query function then checks the first six columns of our original Gantt chart, looking each time for the category numbers we checked (the conditional formatting is that the category numbers are in ). this original Gantt, they are just the same color as the shading of the cell). If no check box is ticked, it returns everything that has fallen into a category within the next six months. Once we start ticking the boxes, it will only return the elements of the next six months in one of the categories we have selected.

  = query ("Gantt View" D1: 1056, "Select D, E, F, G, H, I, J where D <> and (E) corresponds to "& B1 &" or F ") & B1 &" where G is "& B1 &" where H is "& B1 &" or I is "& B1 &" where J is "& B1 &" & # 39;) ", 1)

How to create the view of the stakeholder

The sheet of the planner version has a tab called Planned . You do not need to fill in this tab – it contains a query that retrieves information from the tab of the job view using the = query () function:

  = QUERY ({Tasks View # A1: F, "Tasks View" O1: P}, "Select * where no (Col6 contains & # 39; lancé&#39; ou Col6 contient &#39;annulé&#39; ou Col6 = &#39;&#39; ou Col6 = &#39;Oublié&#39;) ")

Toute la formule ci-dessus consiste à prendre les colonnes de tâches Mois, Tâche, Description, Bloqueur, État, Catégorie et Complet, puis à afficher tous les enregistrements dont le statut n&#39;est pas «non piqué», «annulé», «oublié, ”Ou vide. Cela nous donne un onglet avec les informations que nous sommes prêts à partager. Nous pourrions également obtenir ceci avec = filter () si nous avons réorganisé les données dans l&#39;onglet de la vue des tâches mais cet ordre de données est plus facile à utiliser, nous utilisons donc simplement = query () et ne sélectionnons que colonnes que nous voulons ici, combinant les plages horizontalement en les listant entre {} au début de cette formule.

Ensuite, l&#39;onglet de la vue des tâches de notre fichier de feuille Stakeholder Version utilise = importrange () pour cibler la liste nettoyée que nous avons créée. Pour vous assurer que la version de parties prenantes continue de fonctionner lorsque vous créez des copies de ces deux fichiers, il vous suffit d&#39;accéder à la nouvelle feuille Version planificateur et de copier l&#39;URL de la page. , puis accédez à la Stakeholder Version recherchez l&#39;onglet Task View et mettez à jour la formule importrange () dans la cellule A1 pour avoir la nouvelle URL de votre Version de planificateur drap. La cellule sera recalculée, vous devrez accorder l’autorisation, puis elle devrait fonctionner normalement.

Comment mettre à jour la version de Dieu-I

Cette vue vous donne les informations suivantes:

Un rapide aperçu du nombre total de tâches dans tout projet qui:

  1. Avoir une échéance dans les 10 jours
  2. Avoir passé une date limite (la tâche n&#39;est pas terminée)
  3. La date limite n&#39;est pas fixée
  4. Ne sont pas informés ou ne sont pas budgétisés pour la prochaine trois mois

Il vous donnera également un aperçu du montant du budget non planifié par projet, par mois, pour vous assurer que vous n&#39;avez pas oublié de planifier un mois et que vous n&#39;avez pas planifié chaque mois.

La version de God&#39;s-I fonctionne de manière similaire à la Stakeholder Version dans la mesure où elle extrait des informations en utilisant = importrange (), mais la principale différence est que nous voulons tirer de plusieurs feuilles. La réécriture de la formule peut s&#39;avérer difficile, de sorte que nous pouvons générer la formule dont nous avons besoin dans l&#39;onglet URL du projet .

Les seules choses à faire sont:

  1. Ajoutez l&#39;URL de la nouvelle feuille Affichage du planificateur que vous souhaitez inclure dans l&#39;onglet URL du projet de la Version de Dieu-I
  2. pour cette feuille pour accéder à cette feuille (vous pouvez cliquer sur l&#39;alerte qui apparaît dans la colonne A)
  3. Copiez la valeur dans la cellule B1, accédez à l&#39;onglet Toutes les vues de tâches importées et sélectionnez la cellule A2, puis collez la valeur dans la barre supérieure. Il est important de ne pas coller directement dans la cellule ou la feuille exécutera la formule concaténée plutôt que la formule de requête que nous avons créée.

Il convient de noter que cette feuille contiendra toutes les informations sur chaque projet gérant. Une fois la configuration terminée, vous ne devriez plus partager d’accès à personne, sauf si vous êtes satisfait de voir tous les détails de budgétisation de chacune des feuilles.

Une conclusion tardive:

Pourquoi cherchez-vous une conclusion ici? C’est en plein milieu du billet sous le titre de “Une conclusion rapide”, bien sûr. Passez une bonne journée!

Inscrivez-vous à The Moz Top 10, un logiciel de publipostage bimensuel vous informant des dix meilleures nouvelles, des astuces et des liens radicaux SEO que l&#39;équipe Moz a découverts. Pensez-y comme à votre condensé exclusif de choses que vous n&#39;avez pas le temps de traquer mais que vous voulez lire!

Leave a Reply