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:
5 Tips for Better Decision Making [Infographic]
When decision making falls on your shoulders, you need to be able to step up and make the decision quickly! Here are 5 tips for making better decisions.
You can read more about some of the ideas on this infographic in this article.
Wyzant (say: Wise Ant, I think), a marketplace that matches up tutors and students for in-person and online study sessions, has done some research into how people feel about working with numbers.
They surveyed 235 students who recently struggled with statistics. Only 5.5% of these students were pursuing a career in a maths-related field, and many called out business or project management as a potential career path.
That’s a lot of people who don’t intend to use maths as their ‘full time’ day job who are using statistics as part of their course and possibly their future job. Nearly 45% of the students said, “I’m not a maths person,” which is the answer I would have given too. If you hold that view about yourself, you’re creating more stress, anxiety and mystery around the basics of statistics.
What People Struggle With
70% of all the students struggled with the same two statistics concepts, hypothesis testing and probability.
Personally I don’t use hypothesis testing in my project management work, but I’m sure understanding it is essential in some industries. It hasn’t been since university that I’ve had to think about hypotheses, thankfully. My days of having to understand T-tests and ANOVA are hopefully long gone… if I ever truly understood them at all.
Probability, though – we’re all exposed to that as a function of risk management. It’s often so simplified though that risk assessments are subjective: “I think that my risk is not likely, likely, quite likely, almost definitely going to happen.”
Wyzant worked with expert tutors in the field of statistics to identify the concepts and break them down in ways we can all understand. The article quotes PhD candidate, Brian, who tutors university students in stats:
“Typically, when students are introduced to the normal distribution, they’re given a curve and told probability is the area under the curve. But this is still confusing.”
He says it’s easier to think of probability if you break up the area under the curve into 100 squares, each equal in size.
“If you can look at the distribution and say each of these squares is equal to 1% probability, you can just count the squares to develop good intuition about what the normal distribution is and what it means.”
Image credit: Wyzant
I can see how thinking about probability in this way would make it clearer. The bell curve of a normal distribution is all well and good but blocks really call out the way that the 100% is made up and how it spreads out across the distribution.
What do you think?
The website has some helpful guides for common statistics and probability concepts as well.
You might also find this book interesting: Math for Grownups. I certainly found it helpful!
How to demo your project deliverables
Demos and prototypes save your project time and money because you can get early feedback. I’ve talked about that before (in this video) but a couple of people have asked me for some more tips around setting up demos. And I’m very happy to oblige.
Let’s get on with it then, shall we?
The demo environment
Pick a nice room. By that I mean one that is large enough to fit everyone in comfortably and that’s got enough power sockets. Everyone brings a device along these days and they all need plugging in.
Understand the room’s heating and lighting controls. You don’t want people getting fidgety because they forgot their jacket – you want them concentrating on your amazing project deliverable.
If you are doing your demo via a web conference, get the software set up well before you expect everyone else to join the call. Test your microphone and headset and make sure you can share control of the screen with your co-presenters, if you have any.
Manage the expectations of the people in the room. Are you showing them a very rough outline of a product, a prototype that doesn’t quite work properly yet, a feature-rich almost-finished product, or the final thing? Set their expectations around what they are going to see so they aren’t disappointed when features don’t work or when you tell them it’s too late to change the colour because you’ve already ordered 30,000 in blue.
Review your objectives
What is it that you want people to get out of this demo? You can organise a demo or show people a prototype for a number of reasons such as:
Think carefully about why you want to do this demo and what outputs you are expecting. Do your demo attendees have the same understanding as you? It’s worth running through the objectives at the start of the meeting, just in case they don’t.
Practice, practice, practice. A complete dry run is a good idea. You want your audience to notice what you are saying, not get cut off halfway through your web conference because you don’t know how to use the meeting controls.
Walk through the demo in preparation, whether you are doing it in front of a ‘live’ audience or via a computer screen.
Prepare for questions
Be ready to answer questions. You are showing them your project deliverable in anticipation of some kind of feedback so expect them to have questions about what it does, how it does that and what else it could do. Be prepared to manage the ‘wouldn’t it be great if…’ type questions if you aren’t able to consider any modifications at this point.
Provide back up materials
Your demo attendees will hopefully be so excited about what you have built that they will want to share it with their teams. Have some materials ready so that they can do that: screenshots or handouts are great, but a test login (if software) or samples (if something else) and details of how to use it are better.
This gives them the chance to play with what you have created and if you want further feedback, let them know that you are open to their ideas and provide details of how to get them to you – direct contact, via an online request form or so on.
Demos and prototypes are a really powerful tool, especially if you are delivering a software product or a tangible item. End users particularly find this sort of workshop or meeting a very valuable session as they can see what they are getting. In my experience, showing someone a demo of your product helps build engagement too, as they start to get excited and they can see the idea become real.
However, make sure that if you are doing a demo that you are in a position to comment about when they are likely to get to access the final deliverable. There’s nothing worse than seeing a demo and getting excited about the project only to be told, or you can’t have it for 18 months. Set expectations carefully!
How have you used prototypes and demos? Let us know in the comments.
Elizabeth Harrin also blogs at A Girl’s Guide to Project Management.
|In this video I share 4 tips to help you get the best out of attending a project management conference.|