What spreadsheet package do you use? Personally I use Microsoft Excel, although I do have Numbers on my iPad I case I need to look at a spreadsheet while I’m out and about. I sometimes use Google Sheets for personal projects as well, but I’m not massively comfortable with it.
Regardless of what tool you use, spreadsheets are a huge part of life as a project manager. Whether you are exporting data from an enterprise project management tool, creating a budget forecast or using filterable lists as a way to manage tasks, risks or issues, spreadsheets have so many uses in project management.
Here are some tips to make using spreadsheets easier – all tried and tested by me and colleagues over the years!
1. Add Notes and Comments
Add notes to your spreadsheet so you know how the formulae work and what estimates were based on.
You can do this by adding a separate tab to record notes, if there are a lot of them. I record the way I formulated an estimate for the budget, and what the figure includes, plus any assumptions around resource time and so on.
For short notes, I add them as a comment on the relevant cell. This is the easiest way to note when a cell was updated, or to record what was in the cell before you changed the estimate to be something else.
2. Add Version Control
Learn how to do version control on your documents (where version control is not added automatically by your software). Then add a tab to the spreadsheet showing a table with version control numbers.
When version controlling a spreadsheet, don’t try to add an incremental version number every time you update something. That would be time consuming and not add any value. Instead, do a major version update every time something substantial changes, such as changing major formulae or rebaselining. You’ll get a feel for when it’s important to create a new version.
3. Freeze the Top Panes
Project management spreadsheets can get quite large. In Excel you can freeze the top and side rows so that you can scroll through the spreadsheet and the headers stay in place. It makes reading and moving around a big spreadsheet much easier because you don’t lose the column and row descriptors.
4. Lock Cells
Use the functionality of your software package to lock cells that you don’t want other people to update. For example, in a status tracking spreadsheet, you might have cells that you need to have populated with ‘Open, Closed, On Hold’ etc.
You can fix the spreadsheet so that only those options are permitted. You can also lock cells so they can’t be changed, which is helpful if you have a form and you only want to allow people to enter information in certain places.
5. Add Conditional Formatting
Conditional formatting means the cell colour (or whatever section you choose) updates based on a rule. For example, if the contents of the cell is a number less than 50%, make the cell turn red. This is achieved using a red background fill. You can change the text colour, or use other formatting rules.
This is helpful when you have a large data set and you want to instantly draw people’s attention to figures that are outside the acceptable norms. You can colour code information as red, amber and green by setting up conditional logic once, and adding that formatting.
What tips do you have for making it easier to use spreadsheets? Share your best advice in the comments below!
Pin for later reading:
Community Champion
