Tips for PMO template design

More PMO template design tips

The recent blog post about pmo template design included guidance on designing templates.  However, there are some additional tips that are worth considering so as to avoid mistakes that could impede the benefits of the templates.

Use of Macro’s and complex formulas

Our good friends at Microsoft have done a fantastic job of delivering a great suite of programmes in MS Office.  Word, Excel and Powerpoint really are the cornerstone of most of the documents produced for business including for projects, programmes and the PMO.


It is usually for project and PMO templates to be developed using Word or Excel, especially as Excel allows for multiple calculations and data manipulation.  All ideas that can save time.
However, this does come at a cost.  When a PMO has an Excel wizard (usually fresh out of college or university), they can do great things with Excel.  They design wonderful templates that import data from multiple spreadsheets, flip it into pivot tables, format colours and plot graphs all at the touch of a button.  The first reaction is that this is great and a desire to create even more automation.  However, this is great until it goes wrong (usually long after the Excel guru has moved to another department or moved to another company.

Then the people in the PMO spend hours on end looking at long complex formulas that they do not understand trying to work out why it is not working or producing the incorrect results.  The same is true when additional data needs to be added because a senior manager has asked for additional items to appear on the graph.

In both cases the best course of action is to start again.  Positive is that you get control over your destiny, negative it takes a long time to develop the new template.

User input error

Sometimes there is nothing wrong with the formulas.  Project templates are completed by people, usually the project manager.  People have a tendency to enter data incorrectly either because they don’t understand what format should be entered or a genuine error.  Both can cause a formula to fail.

Then there is the very common occurrence of the project manager inserting extra rows and columns as they have additional information they want to include.  Even though the instructions clearly state not to insert rows or columns they still do.  This again leads to errors.

 Action Plan

  1. Only use complex formulas and macros where absolutely necessary, preferably not at all
  2. Design simple templates that are easy to understand and use
  3. Review the templates with friendly project managers to identify gaps so rows and columns can be added before wider distribution
  4. Use field or workbook protection where appropriate

Some simple ideas that are easy to implement and will save you time and ensure that you keep all of your stakeholders happy.

If you are looking to save time implementing your own set of project or pmo templates, you can achieve this by investing in readymade professional templates.