In my previous article, “PMO Analytics: How to be the Supplier of Relevant Insights” (LINK), one of the principles I provided was the need for your PMO’s data to be accurate and be free of conflict with other data sources. This article focuses on steps you can take to ensure your data is accurate.

Data provides the PMO with credibility. Every PMO customer hungers for accurate, timely information regarding the portfolio. If the PMO fails to satisfy this hunger, then customers will quickly find another source for portfolio information. This article provides 3 general steps to cheaply construct an accurate and up to date data repository.

A Note About Project and Portfolio Management (PPM) Tools:

PPM Tools are a wonderful thing for PMO’s. They offer a structured and largely automated environment to facilitate a wide range of project management activities (including data collection). Unfortunately, they are prohibitively expensive for many organizations. A typical PPM implementation (Software purchase, configuration, training, etc.) will run around $1 million plus or minus 20%. There are also ongoing maintenance expenses to account for on an annual basis running up to 25% of the cost of the initial implementation.

If you are a leader of a PMO that is blessed to have a budget for a PPM tool, then you are going to have an easier time adhering to the steps in this article. However, this article caters to those who must run their PMO’s without a PPM tool, and have to rely on a standard Microsoft Office 365 license.

Step 1: Designating the Data Collection Process at the Project Level:

The data collection process within a PMO needs to be user friendly. User friendly is defined as data that is easy to enter for each project manager and can be combined with data from other projects within minutes. If your data collection process involves copying data from one document and pasting your data into another data set, then you do not have an easy data collection process. You have a data collection process that will result in explaining to your customers why your data is wrong.

All of this starts with designing a simple, easy process to gather all of the data at the project level. I highly recommend that you use Excel to collect your data by instituting a standard one page Excel template for capturing key pieces of information for a project as Excel can easily be referenced in other data collection tools (see example template below). Design the template to be something your community of project managers would be comfortable using to share project status with their stakeholders. This allows for a multifunction tool, that again makes data collection easy for your user community. I recommend building a job aid with high level definitions for each data field within the status report that also includes a tab illustrating what the template would look like when completed.

I recommend against breaking your project status into multiple tabs within the template. For example, do not attempt to show a risk log as one tab and then show a RACI matrix as another tab. These are separate and distinct artifacts stored within your project repository and do not lend themselves well to an Executive level update. Just show a status report showing the executive level update.

Step 2: Automated Assembly of a Portfolio of Data for Your Project Activity:

Your next step will be to create a way to gather the data in each of the individual status reports into a single aggregation of Portfolio data. Luckily, Microsoft’s SharePoint facilitates this process quite easily. Excel can reference data from a cell in one Excel file in SharePoint and store it in a completely different file.

Simply create a file referencing pieces of data from each project file on each line of the file. Below is a simplified example showing how the formulas can be written in each cell of the Excel file:

When viewed in Excel, this file will look something like this:

Once configured, each time you connect to your data file you will get the following prompt:

Click on Update and it will go to each file to receive the most current updates. This automates any copy and paste that would need to be done between this central file and each individual status report.

Step 3: Care and Maintenance of Your Portfolio Data File:

You have a uniform data entry process, you have automated the data collection process, now it is time to ensure that your data is accurate and up to date. In the Data file I created from Step 2, I typically have a tab which profiles the data in each record. This tab shows information about which cells have empty values, which cells have dates that are past due, or even looks at dollars that have exceeded their budgets. This is an automated way to check on the hygiene of your data at a glance and quickly take any corrective action on any data anomalies. The better the hygiene of your data, the higher the PMO’s credibility is going to be. Using functions like conditional formatting to provide quick color referencing can increase your “quick glance” quality checking. See example below for more detail showing where “BLANK” columns in each status report have been highlighted.

Following these three steps is key to ensuring your time is not spent in the “Copy and Paste” business but instead is spent delivering the correct information and insights your PMO’s customers hunger for.

For a good overview of a wide range of PMO principles I recommend viewing my friend Don Clarke’s article “The Value of a Project Management Office (PMO) – Looking beyond the “Price of Admission”” (LINK)



Tell me your thoughts in the comments and let’s open a dialog. I would be excited to hear other opinions on this topic.

Consider joining our LinkedIn Group to continue this conversation as well - CLICK HERE
We hope you will consider joining our Facebook Community as well.  Click on the image to your left to visit and join, or you can CLICK HERE


Reading this article qualifies you to submit a request for PDU’s from PMI.

This Article qualifies as follows:



For more information on registering your PDU’s with PMI – CLICK HERE


At Project Management for Today, we encourage conversation; agree with us or disagree with us, it’s all still knowledge, and we are here to share knowledge. Take a moment to add to the conversation by leaving a comment. It’s an opportunity to engage in the conversation!

If you believe in what we are doing, take a minute to share our articles on your social networks such as LinkedIn and other sites. Use the buttons on the left side of the page.

This article features content from a “Contributing Author” to the Project Management for Today Community. This content is published on this site with the author’s explicit permission. As with all articles on this site, this article is protected by copyright. If you are interested in becoming a Contributing Author to this site, you can learn more by reading the information HERE


You may republish this article in whole or in part with attribution to the author and a direct link back to the full article on this site. Attributions MUST include a hyperlink to the original article, as well as a "Canonical Link" reference embedded in the <head> section of the page.
#pmfortoday / #projectmanagement / #pdu / #pmi / #pmo / #pmbok / #pmblog / #pmoblog / #pmp / #pmi-acp / #pgmp


Karl Hallgrimsson

Karl Hallgrimsson

Co-Founder: Project Management for Today

Karl has worked in many different organizations over his 18+ Year career. These organizations include TeleTech, IBM, DaVita, and Hewlett Packard, Inc. He has served as a change agent in each organization, either by building up strong operational rigor in PMO's, or by greatly improving an organization's Analytics capability. Karl's contributions to this site provides practical recommendations suiting a variety of environments, which will be best suited for readers who are interested in updating their Analytics, PMO Operational, or Portfolio Management capabilities.
Karl Hallgrimsson - Co-Founder
Project Management for Today -
LinkedIn Profile - CLICK HERE
LinkedIn Group - CLICK HERE

Articles by Karl Hallgrimsson - CLICK HERE

Advertisements B