Do you know the reasons why you shouldn't do resource planning in Excel? Turns out you're making your job way harder than it needs to be.
Whether you love it or hate it, Excel has probably been a staple of your career since day one, particularly if you work in project management.
Everyone is familiar with it to a greater or lesser extent. By some estimates, nearly 60% of knowledge workers have at least an “intermediate” grasp of how to use it.
Whether you’re creating a project schedule, managing an inventory, or even doing some personal organization like a household budget, Excel is so adaptable.
The trouble is that a “Jack-of-all-trades” tool is the master of none. You might be able to muddle along with it for a while, but as soon as the going gets tough - perhaps the team grows, or the projects get more complicated - it's going to cease being helpful. In fact, it's going to become a time-sink, and it's not going to repay the effort.
Ultimately, Excel just isn't a sustainable solution for your resource planning challenges. And here, we're going to explain why.
The short answer is yes, Excel can be used for resource planning. But this comes with a big caveat.
Excel really shines when it's being used to process large, numeric datasets (financial reporting is a classic example). Outside of this use-case, though, it is hardly ideal - you're not really using the software the way it was intended, and so it's no surprise that you might experience issues.
If you break down the elements of resource planning, you’ll be looking to do the following things:
Now, there’s nothing stopping you from doing these tasks in Excel. But the trouble is, they are far from easy. Some of the formulas you will need might be fiendishly complicated, and easy to break.
Other times, updating the information will be so manual and time-consuming - and at the end of it, you might not even be sure that you’re working with the right numbers. Things move fast and emergencies happen.
Let’s take utilization as an example. The formula to work out an individual’s billable hours worked against their total available hours is not difficult (you can read about it here), but say you wanted to aggregate individual utilization rates to get a team utilization rate, and then compare these across teams to see which team is most profitable.
It starts to get a bit more complicated, right? Easy for one little bit to go wrong, and scupper the whole endeavor. And at that point, you might just give up and become one of the surprising number of professional service firms that don’t track their team’s time and utilization (even though it's terrible for business).
Similarly, there’s nothing to stop you from tracking team availability in Excel. But if you already use an HR platform for managing employee time off, wouldn’t it just be easier to have this tool integrate with whichever resource planning solution you’re using?
This way, it would show you the most up-to-date outlay of the team’s availability - without you having to go in and manually update people’s time off.
Technically, you could schedule automatic data pulls into Excel if your HR tool has an open API…but this is a difficult route to go down.
In short, what we’re getting at is this: yes you can handle your resource planning in Excel. But why the heck would you?
But let’s pull back for a minute and give Excel a fair hearing. So many resource managers and project managers use it - it stands to reason that not all of those people are misguided, bad at their jobs, or wrong.
Let’s take in some of the good reasons for using Excel for resource planning, before reflecting on the cons.
As we’ve mentioned, Microsoft Excel is ubiquitous. It’s in the toolkit of most knowledge workers - though relative proficiency is admittedly more variable! But because Excel is so familiar to all of us, there’s no onboarding needed - no new tool to learn. Just the same old Excel. This can make it easier when it comes to new people joining the team.
This isn’t so much a “pro” as a reminder: if you’re paying for MS Office licenses, you’re basically already paying to use Excel.
If you’re a new business just getting established and really watching where the pennies are going, you may feel that Excel is a good option for you simply because it won’t cost you anymore than you are already paying.
Say you’re the founder of a new agency. You’ll have a list as long as your arm of operational processes that you need to get up and running as soon as possible. Establishing a process for resource planning is simply one job that needs to be done.
So what do you do? You open up Excel and you build a simple resource management plan or tracker. You plug in everyone’s names, associate a rate with their hours, and then start blocking out the team's time for different projects. If the team is small, and the projects fairly straightforward, this’ll probably work fine for the time being.
But this can cause problems down the line if the supposedly “temporary” fix ends up staying in place longer than intended.
Excel spreadsheets that get too big and too complex fail to function well. Large datasets can make Excel sluggish and slow. Using a lot of complicated formulas, conditional formatting, and graphical elements can exacerbate the problem.
However, if you are doing your resource planning in Excel, you will probably want to use all the functions that are known to slow it down. You may feel that it is limiting your ability to improve your resource planning process, simply because it doesn’t have the heft to do what you need.
This pain will be particularly acute if your organization is scaling. As you grow to take on more projects - bigger teams, more resources to manage, multiple projects to juggle - you'll find only the frustration increases.
A simple formula error or typo can make all the formulas in your spreadsheet stop working properly. This problem is inevitably compounded when you have multiple users accessing the same spreadsheet at the same time.
Updating endless spreadsheets is not a great use of a project manager's precious time. Every hour spent doing manual resource management admin in an Excel spreadsheet is one hour less that they have available to spend working with the project team on initiatives that actually add value.
Tasks that amount to little more than simple data entry are particularly to blame for this, which leads into our next point...
Simply put, relying on lots of manual data entry and updates means a high risk of mistakes made through basic human error.
Going back to the example of time off, if you are manually updating resource availability in a spreadsheet, it’s easy enough to accidentally type 11/15/2022 when you actually meant 11/16/2022. Not to mention that Excel is famously fussy about date formatting.
Working in departmental spreadsheets, disconnected from the tools that the rest of the team is using, you’re going to find yourself in a silo.
Now, this might not be such an issue in a small business where you have lots of face-time and all the team managers can still fit together in the same room for a regular catch-up. But what if you've grown beyond that?
As Christine Robinson reflects, this is the point at which spreadsheets can actually get in the way of Resource Managers working effectively and making big-picture decisions. And as consultant with years of experience across resource management and HR, Christine has seen it happen. This is her take:
Administratively it can become very burdensome when organizations are growing to that level of not simply knowing everyone by name. By that point, you’re going to struggle to manage day-to-day things on spreadsheets - let alone to make tactical, data-driven decisions. Imagine a world where you really understand what the forecast is, how that relates in terms of utilization pipeline. Or if you’re in a service organization and you have a number of sales individuals in the business, imagine being on the same page as them - having that connectivity, having access to that data so that you as a Resource Manager can really understand what’s coming. You never want to be in a situation where there's an iceberg ahead and you didn't see it coming. But if you have a system that joins up information from other parts of the business - even if it's quite basic information around, say project scheduling - having that insight and making decisions based on that information is going to help massively.
Rather than benefiting from the knowledge and the information that’s coming in from all sides of the business, sticking to your resource planning spreadsheets means that you’ll be stuck trying to make your best guess based on what’s in front of you.
It’s not sustainable. In the long term, this lack of joined-up decision making is going to hinder growth opportunities and make achieving strategic goals much harder.
Given that there are plenty of dedicated resource planning tools on the market, why are so many resource and project managers still doing their resource planning in spreadsheets?
Change is challenging - both from an organization-wide, implementation perspective, and from an individual, psychological perspective.
Many people are reluctant to be a part of shake-ups to the status quo - it can be very disruptive to break away from long-standing processes in order to institute something new, even if the long-term effects will be beneficial. It can even make people feel very insecure in their roles, feeling that the new changes may leave them out of their depth, or make their position unnecessary.
If you are pro-change and you're facing negativity from your colleagues, it can be very frustrating. If you want to read more about countering change resistance, we have a resource for you ➡️
Moving to a project resource management solution sounds like it might come with a significant bill attached. But it really doesn't have to. There are solutions out there at all different price-points, depending on what features you would like, how many resources need to be added into the platform, and so on.
Now, don't get me wrong. Some of the major names and old classic solutions are expensive. And many would claim the cost is worth it. However, some of the younger, more innovative players on the market offer features that are just as robust - but keep their price reasonable.
Let's face it: client work nearly always has to take priority. If you're swamped with deadlines, allocating time and resource to internal process improvement can seem like an unaffordable luxury.
We know that avoiding this kind of operational improvement work is a false economy, but even so - if you're having to remove work from people's plates in order to create space for digital transformation projects, you're going to need to find additional resource. And that just might not be achievable at the moment.
For some teams, Microsoft Excel will really be enough for their resource management. Usually this is the case if the team isn't too big, or if there aren't too many clients to consider, or if the organization rarely takes on multiple projects at once.
As we've established, resource planning really isn't the right use-case for Microsoft Excel. Moving to a dedicated resource planning software is going to make a lot more sense, especially if your organization is growing.
Resource planning tools are be built with the challenges of project resource management in mind. They offer features that will save you time on resource management tasks, surfacing the data that you need to see, and staying up-to-date with changes to resource availability.
Let's look at an example: Runn, for instance, warns you when a resource is overallocated. But, not only that, it also makes it simple for you to find a resource with the same skills who does have availability in their schedule - and transfer the assignment to them with the click of a button. The resource in question will then receive a notification to let them know about their new assignment.
So, with this scenario, not only have you eliminated fussing around with spreadsheets - you've also cut down on the emails and messages you would need to send, too.
However, finding the best resource planning tool for your organization is going to take some time, and a bit of shopping around. To prepare for this process, you're going to need to compile a list of features that your ideal solution must have, as well as any "nice to haves" that you might appreciate (perhaps pre-made resource reports?).
If you are creating your project plans and tracking project progress in an Excel sheet too, you might want to look at project management software platforms that can nail resource planning, capacity planning, and project planning all in one place.
If you need an overview of the best solutions on the market at the moment, we've got you covered ➡️
We're all looking for ways to work smarter, not harder. But using Excel for resource planning is a true example of making things harder for yourself by not picking the right tool for the job.
If you were fixing something at home and needed to place a screw, you could maybe do a rough job of driving it in with a hammer (...and cause some damage in the process). But why would you? You'd use a screwdriver instead, because it's designed for the task at hand.
In the same way, the right resource planning tool is going to save you heaps of time and trouble, and it will be able to scale and meet the demands as your organization grows.
Ultimately, the success of your projects depends on whether you have the right people doing the right work, at the right time. And so, it is pretty crucial that you get resource planning spot on - otherwise you're setting yourself up for failure.
Resource planning is worth the investment. Trust me, it will pay dividends.
