5 Spreadsheet Tips to Improve Your Workflow
From the The Money Files Blog
by Elizabeth Harrin
A blog that looks at all aspects of project and program finances from budgets, estimating and accounting to getting a pay rise and managing contracts.
Written by Elizabeth Harrin from RebelsGuideToPM.com.
Recent Posts
How to learn AI the sensible way
Making sense of project cost reports
How real PM mentoring actually works
The Accidental Product Manager: What project managers need to know
How healthy are your project finances?
Categories
accounting,
agile,
ai,
appraisals,
Artificial Intelligence,
audit,
Backlog,
Benchmarking,
benefits,
Benefits Management,
Benefits Realization,
Bias,
books,
budget,
Business Case,
business case,
business case,
Career Development,
Career Development,
carnival,
case study,
Change Management,
checklist,
collaboration tools,
communication,
Communications Management,
competition,
complex projects,
Conferences,
config management,
consultancy,
contingency,
contracts,
corporate finance,
corporate finance,
cost,
Cost Management,
cost management,
credit crunch,
CRM,
data,
data security,
debate,
Decision Making,
delegating,
digite,
earned value,
Education,
Energy and Utilities,
Estimating,
events,
FAQ,
financial management,
financial management,
forecasting,
future,
GDPR,
general,
Goals,
Governance,
green,
Information Technology,
Innovation,
insurance,
interviews,
it,
Knowledge Management,
Leadership,
Lessons Learned,
measuring performance,
Mentoring,
merger,
methods,
metrics,
multiple projects,
negotiating,
Networking,
news,
Olympics,
organization,
Organizational Culture,
outsourcing,
personal finance,
Planning,
pmi,
PMO,
PMO,
Portfolio Management,
portfolio management,
presentations,
privacy policy,
process,
procurement,
product management,
productivity,
Program Management,
project closure,
project data,
project delivery,
Project Success,
project testing,
prototyping,
qualifications,
Quality,
quality,
Quarterly Review,
records,
recruitment,
reports,
requirements,
research,
resilience,
Resource Management,
resources,
risk,
Risk Management,
ROI,
salaries,
Schedule Management,
Scheduling,
scope,
Scope Management,
security,
small projects,
Social Impact,
social impact,
social media,
software,
software,
software,
Stakeholder Management,
stakeholders,
Strategy,
success factors,
supplier management,
team,
Teams,
testing,
testing,
timesheets,
tips,
training,
transparency,
trends,
value management,
vendors,
video,
virtual teams,
workflow
Date
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:
Posted on: April 16, 2019 09:00 AM |
Permalink
Comments (8)
Please login or join to subscribe to this item
Good One.. Thanks for sharing it.
Hi Elizabeth, MS spreadsheet is most widely used, but the vast potential it has, is not fully tapped. Thanks for sharing and refreshing the knowledge.
Rami Kaibni
Community Champion
Senior Projects Manager | Field & Marten Associates
New Westminster, British Columbia, Canada
Frank Leslie Pinto
Senior Project Manager| Manipal Technologies Limited
Mangalore, Karnataka, India
Thanks for the quick lesson on spreadsheet
My biggest tip for anyone is to utilize Grouping. It makes overwhelming spreadsheets easier to digest as you collapse groups up to summary levels.
Tim Wilson
National Training Manager, Field Service Operations| Ericsson Australia
Melbourne, Australia
Thanks Elizabeth, great tips as always :)
1 to Grace Kilpatrick for her Grouping suggestion as well!
A couple of a suggestions from me:
1. Regarding Tip #2 (version control), if you're not using software version control (e.g. automated versioning in SharePoint) then a useful practice I have used in the past is to create a working version of your file, and call it something like 'my-file-vWorking.xls'.
You only ever work (make edits) in the working file. Any time you need to save a numbered version of the file, you can do so (using a save-as filename like 'my-file-v01.xls') but the is just a snapshot of the file at the time you did the save as.
You would then go back to your 'Working' version of the file to continue work. You never make changes to a version numbered file once its been created.
This helps avoid scenarios where a version numbered file has been saved after being shared ... which would result in multiple versions of a numbered file with the same file name, but slightly different contents in each one.
2. On spreadsheets with large numbers of tabs, I have found it useful to use Excel's ability to create hyperlinks to different places within the same workbook.
You can create a table of contents on you first tab, with clickable links to every other tab in your workbook.
Then on each tab, you create a hyperlink back to the table of contents.
I find that this makes navigation within the workbook a little bit more efficient than scrolling backwards and forwards at the bottom of the screen trying to find a tab.
Ronan O Rourke
Retired Executive Manager, Water & Drainage Operations| Retired
Bray, Ireland
Thanks. These are excellent tips for getting the best from Spreadsheets
Sean Whitaker
Project Management Consultant| Crystal Consulting
Christchurch, New Zealand
Nice simple and easy to apply tips - thanks, I’m going to try these
Please Login/Register to leave a comment.
|
"I am not young enough to know everything."
- Oscar Wilde
|
