Recently I spoke to Nick Nuss, data manager, Excel expert and blogger. I like to think that I’m OK at using Excel, but one of the things I don’t understand is pivot tables. So I figured it was about time I learned how to use them.
Luckily, Nick was on hand to explain all.
If you’ve ever struggled with pivot tables, you’ll want to read this interview.
Nick, how can I use Excel to better report on data?
Excel is very versatile. You already know this. As a project manager, you probably have Gantt charts and templates saved out. Excel solves many of your data problems. As a data manager myself, I use Excel in a variety of ways but the functionality I use most is the pivot table.
OK, basics please. What does a pivot table do for me?
Pivot tables allow a user to report on a large data set in a table format. For instance, if you have to track anything in excel, you can report on it. Financials, time, counts of sick days, anything that you can lay out in a table, you can report on it.
So what do I need to know?
First, I want to tell you about table design. Then we’ll move on to creating your pivot table. Lastly, I’m going to dive into how to best use the pivot tables.
Great. Tell me about tables.
Good table design is essential for good reporting. Garbage in is garbage out in the data world, so if you create your table in a poor way, your reports will not be accurate.
Let’s talk lingo for a second. I will be referring to rows as “records” and columns as “fields” from now on. When we have a record, it will be a unique instance that we want to track. This can be per person, per date, per person per date, etc.
The lower down you go, the more information you can get later on. By tracking at a department level, you may not get the results you want later had you tracked things at an individual level.
What about fields?
Fields are what we will use in the pivot table to describe the records. These can be dates, IDs, financials and numbers to tell us information about what has or is to happen.
It’s important that fields have the correct formatting. For instance, financials should always be listed with the financial signs, and dates should always be listed as a date. Without the correct formatting, when you go to use the data, your dates may not calculate correctly (i.e. the difference between 11/30/2017 and 12/1/2017 is one day, but if you do not have it in date format, excel may calculate it as 1212017 - 11302017 = -10,090,000!).
Got it! Anything else I should know about fields?
Fields can be further broken down into two levels; descriptors and metrics. The descriptors are ways to split the data, such as department and job role. The metrics will be the things you want to control down the road, like your financials or hours.
You can certainly use metrics as descriptors, such as people who work more than 40 hours per week, against people who work fewer than 40 hours.
How do I do that?
It is usually easier to create the data table before creating the pivot table. You can create these metrics with functions like “=if()” which will take on one value when logic is true and another when logic is false. You can even have the function return “true” or “false” if you don’t enter what to do.
To do this, set up this equation =if(CELL >= 40, “what does it do when true”, “what does it do when false”). Be sure to replace “CELL” with the correct cell for your records! You can then click and drag this down your table to auto populate.
This image shows the beginnings of a data table with the IF formula for working out whether someone works more than 40 hours a week.
OK. Any other tips for the data table?
A good rule of thumb is to also have one field set as “count” where you enter a 1 for each record. This allows you to report on the total number of records later that meet a certain criteria.
Keep your records clean as they are created. Fill in each field for the record and keep them accurate. If you can track numbers as a decimal, do so. The more exact you are, the more exact future reports will be.
Each field needs a label name in record one for a pivot table to work. Be sure to name these with descriptive names that you can use later. These labels should be unique for later on. It’s also good practice to label these with the field type in case you forget. Keep “date” in the name for any dates and “amount” for any financial values.
Now I’ve got a good quality data table. Are we on to the pivot table?
Yes, creating the pivot table comes next. Go to a new tab within Excel to keep your table data separate from your pivot table. Select cell D6, which will give ample space for your pivot table to populate. This is will be the upper left of your new report. When we discuss how a pivot table works, selecting down and over will make more sense.
Now, in the ribbon at the top, navigate to the “Insert” tab and select “Pivot table” from the left.
This will open the data selection. Click where it asks you to enter the range for the data.
Now, click on the worksheet tab where you entered your data and select the upper left cell of your data (this will be field A and row 1). Next, press ctrl+shift+end on the keyboard (PC shortcut). This shortcut will select all records and fields in the table. Press enter and you have successfully created your pivot table!
This image shows selecting a portion of the data source for creating your pivot table. The full data table is shown below.
Pivot tables work best with lots of data. In this table you can see resource names, hours worked per week, whether or not the hours are above 40, a field for 'count' and the week number. That gives lots of metrics to analyse and report on.
Excellent! Is that it?
Not quite. The third step is reporting from your pivot table. By selecting the area of the pivot table that we had before, a wizard will appear at the right. The top section will show all of your fields from the data table and the bottom will contain four quadrants. The lower left quadrant will be where you place what descriptors you want for your data. In the lower right, you will place the metrics you want to display. You can still use your metrics in the lower left quadrant, but you cannot report on descriptors in the lower right.
In this pivot table, we've calculated sum of hours per person worked on the project overall during the first 3 weeks.
My favourite part about metrics is you can use the drop down to select properties, and display an average, sum, or percent of the total.
The top two quadrants are used for additional analysis. If you wanted to cross your descriptors by ANOTHER descriptor, you would want to place the second criteria in the upper right. The upper left quadrant is used as a filter, which tells your report “only by these records.” This limits the records to certain selected values, but they do not necessarily show in the report.
This pivot table shows average hours worked by employees who work over or under 40 hours. We can also add in more columns and see hours worked per week per employee as in the screenshot below.
That’s really helpful! I think I get it. Anything else I should know?
You can create any number of robust reports using a pivot table. The value of using pivot tables grows with the size of your data table. The larger the table, the better analysis you’ll get. Pivot tables are fast and easy to create. You will be amazed at how quickly you can report on things in Excel using pivot tables.
For more advanced pivot tables, you could also get data out of a database or Access file as long as you select the data as an import. You can do this from the data tab to allow you to select the information in a pivot table! Excel is amazing.
About my interviewee:
Who Does What in KPI Project Reporting
For more information and extra detail about who does what when it comes to project reporting at different levels of the organisation, you can check out this guide and infographic.
Project Reporting by Role
Or: Who Does What When It Comes To Project KPI Reporting
So many KPIs, so little time! There are reporting requirements at every step of the project and at all points in the hierarchy. Not sure who on the team should be involved in what kind of progress tracking? Here are some pointers for who should report what. Adapt these to fit your team, but it’s a good starting point.
They should analyse the work they have done themselves, so they’re reporting on progress made and work still to do. And time sheets, if you use them.
There’s an overlap here with skills required, so you’ll need to get them involved with upcoming planning to ensure that they are capable of doing the tasks – otherwise it will take them muhc longer and with much worse outcomes in terms of quality.
Project-level analysis relating to project planning and budgeting. If this is you, you’ll complete the project dashboard and summarise the project performance metrics.
If this is you, you’ll be doing programme level reporting, using inputs from the project managers. As your programme is probably broken down into stages, you can report actual progress (on all the projects) against the forecasted progress for the stage. Do this for time and budget as a minimum.
There may also be other programme level key performance metrics that you choose to track such as resource allocation across the project with a view to managing skill levels.
The kind of thing you’re reporting on is even more rolled up than that of Programme Manager. You’re looking at analysis by the type of work carried out, progress across phases and stages of programme against forecast and juggling the budgets across the portfolio so you’ll want to know top level budget performance to feed into portfolio metrics.
Look at metrics related to time to complete work so you’re also keeping an eye of capability and performance as well as time/cost/quality of the work done.
As a project sponsor, you’ll want to digest the project-level reporting from the project manager. But as a senior exec responsible for diverse portfolios, your data needs are more aligned to the governance and strategic functions you perform.
Look for metrics that analyse time for the different types of work that you have in the business. Splitting activities by runners, repeaters and strangers is a good starting point, and then you can apply the governance metrics to those.
‘Runners, repeaters and strangers’ is a Lean technique to help you know where to concentrate your efforts.
As an exec, look at the time/cost analysis relating to these tasks to see if the business is spending its resources wisely.
So your project is being audited? Lucky you! There’s quite a lot to think about if this is happening to your project, whether the audit is a formal thing or whether you’ve been selected as the project that’s being scrutinised by the PMO this month as part of an informal ongoing review process.
But auditing isn’t that big of a deal if you know what to expect. Every audit process is going to be slightly different but here are some common things that you can prepare for.
Provide The Information
The first step is that you’ll have to provide a whole pile of information to the people doing the audit. Whether that’s your best mate from the PMO or a scary-looking external auditor, they’ll give you a list of the kind of things they want to see. This will include:
The auditor (or audit team) will then take some time to go through all of this. It’s likely to be around two weeks as there’s a lot to digest, and they’ll want to understand it so they might come back to you with follow up questions.
They’re preparing to dig deep into the material and establishing what else they might need to know.
Once the audit team has reviewed your paperwork, they’ll start digging more deeply into the project. At your first meeting (for which there should be an agenda so you can prepare adequately in advance), expect to be taken through a list of points. Consider this the orientation meeting.
It can help if you provide an overview of the project and the objectives as part of the scene setting so be prepared to do that on the spot if asked.
Use the time to really understand what is expected of you and what the process is going forward. For example, what are the reporting expectations during the audit period? If they are asking you for updates, how long will you have to put those together and send them over? In what format?
And if you are expected to make changes to the way you are doing things (which might not be at the request of the auditors but by your manager or the PMO perhaps) then how long will you have for doing that?
Try to establish what the outcome of the audit process will be – they should be able to tell you, and should be able to explain what input you will have to the format and content of the final report. There should be a process by which you can comment on a draft and put your points across before anything is published to senior management.
Find out what your management team or project sponsor intends to use the audit output for, so you can better prepare for any action that is coming your way at the end of the audit.
Following the first meeting you should expect lots of emails! The auditors may want to speak to people in your team or subject matter experts, so make the right people available. If you are submitting follow up or additional documentation you may need to upload this to a secure online repository (if they are external) or provide it over email. There are lots of documents whizzing around in a formal audit situation so try to keep track of what you’ve sent to whom and when.
You might find that after a flurry of activity it all goes quiet for a while. This is the time when the report is being written up. Try to find out when the draft will be coming to you for review so you can block out some time to look at it and add your comments. Be honest in your commentary back but be prepared to challenge anything that you don’t think is accurately representative of your project or the processes being used to manage it.
All that sounds quite daunting but taking part in a project audit shouldn’t be scary – it’s just time consuming and a drain on your team’s resources while you are also trying to get work done. Make sure that your stakeholders and project sponsor know what is going on so that they can give you a bit of grace when it comes to short deadlines and the like during the audit period.
Audits like this won’t take more than about 8 weeks start to end as a maximum, from finalising commercial terms and the engagement letter that kicks off the audit process with an external group to the submission of the final report. However, that time can drag when you are trying to hit project milestones and keep all the plates spinning as well!
Remember that audits are supposed to be highly useful, informative exercises, whether that’s a formal external review or an informal assessment by your PMO peers. The outputs are going to help you manage the project more effectively and get better results for the business, so don’t take the recommendations personally and work together with your team to put the advice into action. Not everyone is lucky enough to go through an audit and what you learn can be invaluable to helping you manage future projects more effectively.
When you’re putting together a new project proposal, there are a number of things to consider. Edoardo Binda Zane has a whole book about proposal writing: Writing Proposals: A Handbook of What Makes Your Project Right for Funding. He says that there are three main elements:
Project proposals of this kind are quite different from in-company project business cases. These relate to projects where you are basically pitching another organisation to give you funding for your project. This happens in research, academia and sometimes other areas like business incubators for start ups.
Let’s say you want to put a funding proposal together for a business incubator. You know you are eligible and you’re gathering the paperwork to prove it. You can put your budget together in the format they want. But the technical proposal…. It’s hard to know exactly what goes in that, even if you know your own solution perfectly and have already costed it. This is about communicating the value and approach of your project so it gets chosen over someone else’s.
It helps if the funding-granting body has given you a template to complete. That certainly takes the guesswork out of it. If that doesn’t happen, you’ll be reliant on your own internal templates and they might not be geared up for this kind of application.
Binda Zane has some pointers for what to include. The headings below are what he suggests goes into your technical proposal along with an explanation of how I would interpret those if it was my project.
Pop something in here to set the scene. Personally I would write the introduction last so I could make it contextually relevant to the rest of the document. If you don’t normally leave it to the end to write the intro, try it next time – it’s so much easier!
Current context and proposal structure
This should cover the context of the project and any background information that’s useful for the decision makers to have. It could also describe the research you’ve done (not in detail) or other sources from which you’ve drawn information to prepare this proposal.
It’s also a kind of executive summary that outlines what they can expect to read in the proposal and gives them the headlines in an easy to digest format.
You’d cover off the project goals and objectives. This would be just like a standard business case.
Binda Zane suggests that you talk about the tools and techniques to be used. This can give the decision makers confidence that you do have some clue about how to manage a project. If you can say that you’re using industry standard best practices and following guidance from PMI (or whatever methodology/standard/processes are applicable to your business) then do.
I’d make sure that governance gets a significant mention here. If you were asking for my money I’d want to know that you had controls in place to spend it sensibly.
Of course, you shouldn’t say that you can follow standards if you actually can’t. That would be a breach of the PMI Code of Ethics and Professional Conduct. Other professional bodies have similar ethics standards.
You’d also want to include a section that outlines your detailed approach, the work packages and the descriptions of each task, at least to a sensible level that tells them what you are all about. If you think this section might go on a bit, move some of the task descriptions or detail to the end and put it in an appendix.
Organisation and Staffing
This section covers what they need to know about the people who will be involved with the project and how the work would be organised.
Binda Zane suggests management of quality control is covered in this section but I think there’s some flexibility to move it elsewhere if that makes better sense to you. I think I’d include this in my methodology section but there could be good reason for keeping it here.
Up until now you’ve only talked about what you will do, and how you will do it, but there hasn’t been any talk of how long that will take. Mention the timescales, major phasing and anything else relevant: key milestones, reporting dates and so on.
You also want to talk about the project team in this section. Describe the make up of the team, the organisation structure for the project and the roles and responsibilities of the key players.
Binda Zane recommends two appendices. You can dump information in here that would take up too much space in the main proposal but is still useful for the audience to have. His suggestion is that you include the CVs/resumés of the project team and also a selection of project references. By that I would surmise that you could include references from past clients about similar projects, awards your team has won for their project work and anything else that makes you look like a solid and stable organisation.
That should give you a solid project proposal – for the technical element, at least. What else would you include in a technical proposal? I’m interested to hear your thoughts about what might be missed out of the list here!