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:
I put this infographic together to show the various different levels of project financial management maturity, as outlined by the P3O guidance from AXELOS. My view is that most companies should be looking to aim for Level 4. Level 5, with the implications that you are using Monte Carlo simulations and other types of advanced estimating tools, is probably overkill for most smaller projects or businesses without the exposure to risk that this helps mitigate. What are your thoughts? Is Level 5 where we should all be heading?
If you’d like to read more about the different levels, you can in this article.
Keep the records on paper? Electronically? Or shred them? This video will tell you what project financial paperwork you need to keep (and how) and what you can destroy.
It’s the time of year when project managers (and everyone else) are looking to make resolutions. You know, the kind of promises you make to yourself in the dark days of winter and then have completely forgotten by Easter.
On the off chance that you’ll be making resolutions this year, here are some you could consider. They all have a money-related theme, so if you want to brush up your budgeting or polish your financial management skills in 2013, these could be great resolutions for you to adopt. So here we go: 5 promises for better money management over the next 12 months.
1. I will look at historical data for forecasts
When you are managing projects that are repetitive in nature and that the team has a lot of experience of, it’s very tempting to simply let them estimate the length of tasks and assume that they know what they are doing. Most of the time, they probably will. But it is worth validating their estimates against historical data from timesheets and previous project schedules. Use your online project management software to pull up reports of how long things took the last time you did them.
This could be at the level of an individual task, like completing a particular piece of coding, or a project phase, like testing. Or both. The purpose of checking is to make sure that your estimates really are sound and that the people who are estimating are not making the same mistakes about task duration on every project.
2. I will do my timesheets in a timely fashion
This is a personal resolution for you, although you could extend it to all your project team members. The risk of not doing your timesheets on time is that you forget exactly what it was that you were doing. As a result, you block out 8 hours per day for a task called ‘project management’ which doesn’t give you any breakdown of how you actually spent the time. Worse, you could be booking time to one project when in reality you got pulled off that project to spend half a day on some other project. These things happen in real life, to you and your team members.
By aiming to complete your timesheets at least weekly you’ll not have long enough to forget what you were working on!
3. I will understand Earned Value Analysis (or teach someone else how to do it)
If you don’t understand EVA, make 2013 the year when you get your books out and study how it works. If you do understand EVA, make a resolution to share your knowledge with someone else this year. Even if you don’t use EVA on your projects, it is a very useful skill to have.
4. I will do my expenses on time
Most project managers will incur expenses in the course of their job, such as travel to meetings. Not doing your expenses on time means that you are out of pocket. Many companies only pay expenses once a month in the monthly pay run, so don’t let your expense bill mount up – that’s effectively a loan to your company.
Get your personal paperwork in order by keeping receipts together, noting down your mileage after every trip and understanding the schedule for submitting expenses so that you don’t miss the deadlines.
If your expenses are being cross-charged to your project it is even more important to get your expenses in on time. If you don’t, your project budget will reflect that you have more ‘in the bank’ than you actually do.
5. I will review my budget quarterly
You do this already, don’t you? If not, make 2013 the year when you review your project budget forecasts regularly. If your project runs over two quarters you’ll probably be asked to do this by your finance team anyway, but even if you are not, it is still good practice to get out your spreadsheets and just check that you are still on track to stick within your budget tolerance limits.
Have you chosen any of these as your resolutions for 2013? If not, what are you having as your resolutions instead?
Project expenses aren’t always spread evenly throughout the life of a project. Understanding how costs are split is one way to make sure that your project budget is appropriately phased. For example, if you will incur more costs at the beginning, then you’ll need to factor that in to the budget. If you will pay out lots of money at the end, you need to factor that in as well, to make sure that you have some money left!
Here are 3 ways that costs can be linked to project activities.
Start of activity
Some projects incur the majority of their costs at the beginning. Examples would be:
In this case, you’ll want to make sure that your project budget is available to you as soon as you start the project, and isn’t phased by quarter or reliant on some cash flow issue being resolved. You’ll need to spend a large chunk of your budget from day 1, so talk to your project sponsor or your project accountant and make sure that the provisions are available for you.
End of activity
Some projects incur costs at the end of the project. This could be, for example:
In this situation, you’ll need to make sure that your project funds are still available to you at the end of the project. Finance teams often review budgets and if it looks as if you aren’t spending much of your project budget, they might ask for some of it back! It’s good to be able to justify why you need to hang on to the cash, so that you guarantee it is still available to you when you come to need it at the end.
Uniformly spread across activity
The third way that project costs can be linked to activities is through being spread evenly across the lifecycle of the project. Some examples of this are:
This is probably the easiest type of split to manage. Regular payments for regular activities means that your budget will be consumed in an even way across the lifecycle of the project. You can quickly see if you are half way through the project then you should be about half way through your budget too.
Of course, one project could use all of these, as different activities could have different spreads of costs. You’ll have to take that into account when you plan how your budget is phased. If that is the case for you, you’ll also have to consider phasing per task/activity/item as well as what the overall profile looks like. That way you can build up a picture of how much money you’ll need when.
Which is the model that your project uses, or do you phase your project budget in a different way?