The Excel Attraction – what’s a project manager to do?

Several years ago I worked directly the Microsoft Project Team as part of their Enterprise Project Management Partner Advisory Council.  One topic that came up more than once during the 5 years I spent with the group was the impact of Excel on Project Management.  At the time Microsoft estimated that over 30 million copies of Microsoft Project were in use somewhere around the world.  Their own internal estimates were that over double that used Excel for Project Management.

So what’s the attraction of Excel?

As my wife said when I asked her, “You can get stuff done.”  (Ok, she didn’t say it exactly like that but this is a family show!

She’s right though.  Excel has been around for ages and it wasn’t even the first spreadsheet on the market.

In the early 1980s I was introduced to VisiCalc on a CPM PC and was mesmerized.  There was no end to the calculations and formulas I could create.  Yes, it was only text at that time but for those who had ever done bookkeeping in a hard covered wide ledger book filled with green pages lined with green lines, this was a miracle!  Not only did it catch my errors, it recalculated instantly.  Forget a line?  Just insert and there it was and the formula also caught the new update immediately.

VisiCalc gave way to Multiplan for me which Microsoft was selling.  (Yes, they sold a different spreadsheet before Excel!) and then in the late 80s of course, Excel and I’ve never looked back.

For project managers they have available a myriad of options for project management tools that do so much more specific calculations than Excel was ever designed for.  After all, Excel starts off basically with a blank page (unless you’re using templates of course).  So why is it still so popular?

In a short answer, not everyone needs the rigor of project management practices in order to be effective.  For many project management tasks, the exercise of getting all your tasks defined and all your roll-ups organized and all the rates and all the resources and all the cost centers can be an overwhelming bottom-up challenge.

Let’s say you just need to do a high-level budget.  Sure, you can do something in Project, but it can be so much faster in Excel.  And, once you have some numbers, generating charts and graphs is a snap.  The boss will be delighted.

Let’s say you need the same numbers but organized differently.  Well, there’s a Pivot Table if you’re so inclined but you might just copy/paste to a new tab and resort and recalculate.  Hey presto – solution delivered.

But, you say, I have complex multi-currency, multi-category issues.  Excel has shown time and time again that it has an almost infinite flexibility in extending formulae and calculation concepts into different directions.

So why don’t we abandon other project management tools and just use Excel?

The problem with Excel for project managers isn’t that it’s incapable, it’s that it’s such a great personal calculator tool that it doesn’t lend itself well to being a centralized source of auditable data and that’s where the mischief begins.

Some people create tools with Excel that become so popular that they are asked to extend the tool to more division wide or corporate use.

I’ve seen dashboards that start as a simple personal tool and then management asks why we don’t see all the projects in this format rather than just the ones from that individual.  It’s hard to say no when management can see the hard copy results right in front of them.  So the individual’s spreadsheet becomes a multi-page concept or even programmatic in nature.  Soon we’re using Excel’s data connectors to pull data from all kinds of sources.  Does it work?  Sure.  But there’s no audit of where the data is coming from.

So a tool that might have started as a simple display or calculator for one project manager finds itself stretching further and further and further until at last it is being asked to form the basis of a corporate tool.

Examples

Let’s take a look at a couple of examples from my own experience.

Excel Timesheets

One client of ours (to be fair, this has been the case for numerous clients of ours) had an Excel timesheet that started as a simple grid for a team of a half-dozen staff.  The creator was a project manager who was just trying to gather more data in the absence of a standardized timesheet.  The grid started as an Excel template (There are several) and the project manager populated this with some drop down choices for tasks and projects.  Each employee filled in a new file each week and emailed it to the project manager who then used a summary spreadsheet to read the results from predictable cells in each file and make a summary.

Worked like a charm!

In fact, it worked a little too well.  “Why can’t we expand this to the department?” our PM was asked.  So they did.  Now, instead of 6 files to collect, there were 60.  The work increased by an order of magnitude.  “Where are the missing files?” became an every week challenge.  When it was just the 6 of them, it was a matter of calling across the cubicle.  Now, people were all over.  Still, our PM prevailed, putting in the time to hunt down files.  With more people involved, some users felt they could be helpful by reformatting files from time to time to make them even more awesome.  That was a problem for our PM as his summary spreadsheet didn’t take into account freelance Excel helpers.  This meant manual manipulation of 5 or 6 files each week to make sure they worked.

Management didn’t see this extra work of course.  They were delighted.  “Let’s do this for all 300 of us,” they suggested.  Well, at this point, each week’s summaries became a logistical nightmare.  Every week there were challenges.  Some files wouldn’t appear.  Some people were sick from time to time or took vacation.  Files were altered and no longer worked with the summary page and had to be manually re-entered.  The amount of work had turned our once-eager project manager into an almost full time timesheet clerk and this left him frustrated.  Moreover, the summaries no longer were as efficient and management complained.

In the end, our PM was able to convince management that the one-time efficient process couldn’t possibly continue and if they considered how much time and effort was going into manually manipulating Excel files every week, it would be much cheaper to get onto a commercial timesheet system.

Migrating to TimeControl, which was how this turned out, was helped by TimeControl’s many links and support of Excel but one challenge of the deployment was the affinity for the existing process with people who had no idea how much manual effort was behind the scenes making it work.

Project Management

It started as a simple Project Update chart created by one Project Manager to see his projects and major milestones in a format that was easy for him to follow.  When we met him for the first time, his one-time Excel file had ballooned from 3 projects to 130 and was out of control.  Using Excel’s network functionality to link many network-saved worksheets into one summary, many project managers were updating files and expecting that that Project Management Office was able to see the current results as well as they could.  That wasn’t the case.

The PM who was in charge of the summary found himself working more for the PMO than as a PM because of the massive display on the centralized worksheet and while that might have been great for his career at one time, now it was doing damage to his reputation.  The centralized spreadsheet was rarely 100% up to date and, even if it had been, it was rarely 100% accurate.  In the end, management finally admitted that what had been a great tool for a tiny group was not well suited to a much larger group.  Interestingly, this same management had a tough time understanding why it would takes months to replicate the same dashboard view in an enterprise project system.  Creating the view wasn’t hard.  It probably took as long as it did in the original Excel example.  But, the view wasn’t reliable until the data was of sufficient quality that the confidence in the dashboard could be established.  That was all about process and corporate culture, not about technology.

So, are you saying we need to give up on Excel?

Absolutely not. Excel is an excellent tool for when it’s appropriate.   How we dealt with this in our own TimeControl timesheet product at HMS was to embrace Excel as a likely tool to be used.  We made imports and exports directly with Excel files.  We made it possible to save reports directly into Excel.  We allowed Excel dashboard views to be embedded right into a TimeControl dashboard.  We made numerous views of data saveable right into Excel.

This allows the centralized system to put the controls in place that make the data collected of appropriate quality.  If we use timesheets for billing or for payroll, we have to be sure the data is accurate.  Business Validation Rules and tests for missing timesheets are something that can be built into the application.  Automated reports can still be saved in Excel and emailed to recipients but all the manual intervention of a personal tool is taken out of places it doesn’t belong.

Excel will not go away.  Spreadsheets have made a place in our business lexicon that make them the go-to solution for many on-the-fly analysis.

Where we can run into trouble is when demands for a view of our data goes from personal analysis to becoming a programming challenge.  Then we’ve made our PM spreadsheet expert into a programming shop.  We can become more effective by making sure that the right tool gets applied to the right problem.

Tags:

Author: chris.vandersluis

Comments are closed.