Keystone Project Advisory have spent many thousands of hours working on forecasts, reports and dashboards, reviewing and analyzing project data in Excel spreadsheets.

Microsoft Excel is widely used in Project Management and Project Controls, and for good reason. It is cheap, it is widely understood, requires minimal training, and most companies have little restriction or standards around its use.

Excel is certainly a fit for purpose tool to a point, but many companies either fail to identify, or even worse, fail to act when these systems are no longer fit for purpose due to scale or complexity.

Excel Spreadsheets provide an amazing amount of flexibility to develop a worksheet, add or develop new columns, tables, formulas and charts to keep up with the evolution of a growing team, expanding scope, reporting metrics or requirements from project stakeholders.

But the inherent flexibility with Excel can also be its greatest weakness.

One of the biggest issues we see using Excel in project controls, is with spreadsheet growth and evolution from small and simple project tracking, forecasting and reporting worksheets, into complex multi-faceted workbooks, expected to be a total project repository for all project data. In a lot of cases excel just can’t keep up.

As spreadsheets evolve and grow, so does the depth and quantity of data, along with the knowledge of the person or team developing and managing the worksheet. This is problematic with staffing changes or movements, with the knowledge and logic of the initial spreadsheet developer disappearing or becoming difficult to seek assistance.

It is not uncommon to see project teams scratching their heads with an unmanageable tool, that no longer provides an efficient and consistent tool for project tracking, only to see project teams attempt to develop a new and improved version of the same, and subject to the same inherent flaws.

This growth can also push excel to its limits in processing, with spreadsheets with significant volumes of data and complex calculations and lookups taking their toll with worksheets becoming unstable. Most Excel users would have experienced an Excel crash or a data loss 5 minutes before a deadline. Version and Document Control are only in their infancy in the Microsoft packages, and using Excel where version control is required for project tracking proves difficult.

Unlike most specialist Project Performance Management tools on the market which “lock” or “close” a reporting period when the reporting cycle is complete; there is no functionality to “lock” a reporting period without restricting general permissions of the worksheet.

Likewise, it is also very difficult to prevent inconsistency between versions of the same document. A classic example is where a portfolio of projects are set up from a released version of a project tracking or forecasting worksheet, but multiple users will make minor edits, tweaks and adjustments to their individual projects worksheet, however these changes or improvements are not consistently implemented on each project report in the portfolio, leading to multiple versions of a similar workbook.

The flexibility that makes Excel so widely used, is another of the reasons it is highly criticised. It is very easy with Excel to inadvertently delete a cell or range of data or part of a formula without noticing and locking cells or adding permissions greatly impacts the general operation of the work sheet for the user by restricting a significant amount of functionality.

These are just a couple of flaws of Excel that on face value seem manageable, but time and time again we see organisations struggling to report consistently and accurately on project portfolios, simply because Excel is their tool of choice.

There are a number of excellent tools available on the market for managing project and portfolio costs for project controls that have been developed to overcome the flaws that excel present for forecasting and reporting, yet we continue to see organisations persisting with excel driven project tracking and reporting processes, and continually facing issues with consistency and accuracy.

We have seen schedules produced in Powerpoint, Excel and Visio, but this is by no means a common occurrence and not something we want to see. Primavera P6 is an industry standard tool that is globally recognized as a best practice solution.

So if we don’t see project planners producing schedules and gantt charts in Excel, why do countless organisations continue to trust their project tracking, cost control, forecasting, reporting and project performance management with it?

Author: Tim Sutton