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:
In this instalment of my irregular column chatting to expert project managers, I caught up with Chris Cook. Chris is the author of a new book, The Entrepreneurial Project Manager. It talks about how you can improve your skills to take a cutting-edge approach to project management, as an entrepreneur does in their own business.
How does that work? We’ll get on to that. First, I asked Chris to share his background and how he got into project management.
Chris, tell me about your career path.
Out of high school, I started working for my stepfather’s road construction company. I started as a general labourer eventually becoming a surveyor (of sorts).
I was attending college studying building construction management. After graduation, I enrolled in the graduate project management program at the same university. I was working and going to school. The field and classroom knowledge blended well.
Did you always know you’d write a book?
Five years ago, I was laboring for my stepfather’s company while studying project management in graduate school. I was coming home with dirt head to toe, tired like a dog from the weather and long hours, and I had no idea what the next move was going to be.
Would I stay working for him and maybe take over one day? Would I leave and spread my wings with a larger company to put my studies to use?
While working as an assistant project manager for a different company, a professor from graduate school offered me the opportunity to write a book for a publisher she has worked for. I jumped at the opportunity. Over the past year, I have been focusing on writing the best book possible, learning about the book writing process, and pursuing more knowledge of the project management profession.
Now, I am 1500 miles from home in Colorado having written a book, completed graduate school, and became a PMP®. Only one of those goals was on my mind at the time.
I’m guessing it wasn’t the book! What was the writing process like for you?
I segmented the process into three parts: research, writing, and editing.
The research phase took the longest. Writing notes, picking materials to use, watching hours of webinars and videos, and then placing those notes into designated chapters. I wanted to use materials not only within project management but also outside the project management world.
I tend to look at project managers as leaders and coaches, so I reference sports quite a bit throughout to make the comparison more concrete.
The writing phase was transcribing those notes through a project management filter (in my head) relating coaching and leadership to a project manager.
My goal was to write on a subject then set the computer down. A piece of advice I took to heart was if you still have something to write about, stop writing. Pick that thought up the next day to keep the momentum going.
Editing was the last phase and most excruciating for me because I had to then read what I wrote and become critical. I had amazing people who read my work and gave excellent feedback along the way which drastically improves my writing.
You did a lot of research for the book. What in particular did you do on the subject of entrepreneurship?
Entrepreneurship and outside-of-the-box thinking are often linked. I tried this same approach in my research. Instead of going to best-selling entrepreneurship books that most people read, I attempted to look for outside sources that can be linked similarly.
For instance, I read many coaching and leadership books because a project manager holds a similar position on his or her team. Hall of Fame coaches Bill Walsh and Bob Knight are two of the sources I went to for more information on taking teams to a championship level.
I also applied Stoic and Taoist philosophies because it is important for people who are leaders to understand their personal stances and why they do what they do, or believe what they believe. Making individuals perform for the sake of performance does not inspire or lead to betterment.
However, if you give reasons and data as to why these tasks are essential, the group is more likely to respond positively. “Because that is how it is done” or “this is how we do things” is not a substantial enough answer.
People might think that intrapreneurship is more relevant to project managers working in a big organization. What are your thoughts on that?
Intrapreneurship is a great concept that gives employees the opportunities to use company resources with expansive thinking and testing to innovate.
The first time I came across intrapreneurship was the PMXPO 2016 when Robbie Bach gave a presentation regarding his time with Microsoft and developing the Xbox gaming system. The only knowns Bach had were competing with Sony for the living rooms of consumers and a deadline. Other than that, the project was open ended. Each person became a firefighter coming into work not knowing what fire they would have to put out that day. Even with a $4-6 billion overrun, Bill Gates and Steve Ballmer still considered the lessons learned worth it. Bach implemented his 3P framework: Purpose (Pursue the Northstar), Principles (Ground Rules), and Priorities (The Art of Leaving Things Undone).
Watch the video of Robbie Bach’s presentation here: PMXPO 2016: The Xbox Story: Lessons in Leadership, Strategy, and Team Management
Most of the time, people get caught up in the bureaucracy, hierarchy, and rules of an organization that creativity and innovation get lost. A lot of time is spent performing defined tasks and schedules, and before you know it, the day is done, and the train keeps on moving.
I like the idea of finding time to brainstorm, revert to a child’s mind, and be playful with idea creation. Not all ideas are good, but that should not deter someone from shouting them out just in case.
The Entrepreneurial Project Manager by Chris Cook is published by CRC Press
In this video I talk about what to look out for when you are hiring someone. It can be expensive to bring people on to the team, so it's definitely worth getting it right first time! Here are some tips for making sure that your recruitment efforts don't go to waste.
In this video I talk about the costs of hiring a new employee. There are a lot of elements involved in hiring and they are all an investment!
Do you manage your projects ‘in the cloud’? It’s the buzzword that has stopped being ‘buzz’ and is now a critical part of being able to operate successfully as a project manager – even if you don’t manage software projects.
Growing up in cold and rainy Hamburg, Mauricio Prinzlau had a semi-legitimate excuse for passing his time in front of the computer as a kid. After a degree in Business Communication Management, he joined the Cloudwards.net team as a managing editor and backup expert, where he's in charge of the cloud backup and storage reviews section.
I asked him what project managers need to know about cloud computing, starting with the really, really basic stuff.
Mauricio, what is 'the cloud'?
The cloud is obviously a very trending topic with a variety of definitions. The cloud leverages computing power and makes it available via the Internet so that businesses, organizations, and even individuals can use the cloud to solve business problems, upload photos, or host a website.
What else do people use it for?
Project managers use it for hosted project management collaboration tools but also other, non-project management apps that they are using in their day jobs or deploying to others.
For example, businesses use the cloud to make operations more efficient and leverage the computing power of servers to store and archive files cheaper and more secure. What used to be a complete on-site infrastructure that needed maintenance, security patches, and staff is now outsourced to cloud data centers that are off-site, thus reducing cost and risk for businesses and organizations.
Outside of work, you probably use the cloud on a daily basis without really noticing it. Facebook relies on cloud infrastructure, iCloud uses the cloud to backup people’s photos, Google Now helps us with day-to-day tasks. And there are many more examples we could come up with where the cloud plays a central role in people’s lives now.
So let’s say I need to back up all my project files. How do you choose the right cloud storage solution for your project?
How do you choose the right car? Just kidding, of course, it is very hard to give a general answer. What do you need the cloud for? Do you have a team of 5 people who need to collaborate and share files? Then you need to look at solutions such as Dropbox or Google Drive.
But what if security is a major concern? Then Dropbox and Google Drive are not an option. Project managers need to look at solutions such as Sync.com, who put security at the forefront of their business model.
Think about what your project teams and organization needs regarding features because it’s easy to overpay for a cloud service that you don’t need.
And what about the projects I might be delivering? Outside of file sharing and back up, what sort of things am I going to be hearing my tech architects talk about?
If you need virtual computers in the cloud to do calculations, then you you might hear them talk about Amazon Web Services because they offer great flexibility and scalability of computing power.
There are other options, of course, and what I would say is, there is no one-size-fits-all cloud. You need to compare features carefully, decide on your budget and then make an informed decision.
Can the cloud or online solutions be a cost-effective way to back up files?
In one word: yes. Online and cloud backup are terms used synonymously because there is not a real difference. Cloud (online) backups are one of the cheapest ways for businesses to get files off-site and protect them from disasters (theft, fires, flood etc.).
There are even services such as CrashPlan or Backblaze, who offer unlimited cloud backup, so they put no limit as to the amount of storage you can send.
Sounds great. Surely there are disadvantages we should know about?
There can be risks associated with cloud storage. That’s why I recommend my clients not to rely on one single cloud with all their data. Data loss can happen even to the best companies, so a fallback (ideally with a local backup) is always best.
Security is a concern if a cloud backup or storage company does not encrypt files before they are sent to the cloud (sometimes called end-to-end encryption). Always make sure you choose a service that supports this feature.
If your company has certain limitations as to the location of where the data is hosted then most companies have to be aware that the majority of cloud storage companies are located in the US. This is not ideal for most European businesses.
Yikes. So much of my project data is confidential. Is it safe to put stuff in the cloud?
It is safe if a) you encrypt files yourself before you send them, or b) a cloud storage service offers local encryption (or zero-knowledge privacy). I would personally recommend Sync.com, which is a Canada-based service, they work in the same way Dropbox does, but with zero-knowledge privacy.
I don’t even know what zero-knowledge means!
Oh, sorry! There’s a detailed explanation in this article but it’s too much to cover in this interview today, but it’s a way to store your data so that even the storage company can’t get into it.
OK, I’ll look at that later, thanks. How do you balance accessing work files on your work cloud and your personal stuff in your personal cloud, like Dropbox, for example?
Well, for one you could create two different accounts, one that you use for personal and one for business.
Dropbox and other cloud storage service offer business versions with enhanced privacy and collaborative features. For business collaboration, I tend to use Google Drive because my team and I can work together in real time on documents and spreadsheets, but I do not use the public clouds (Google, Dropbox) for very sensitive files like contracts. That’s where I would personally choose a zero-knowledge cloud.
Can you give me a short answer to what do managers need to know about the private/public cloud overlap and how best to manage it so that employees don't put confidential business records at risk?
That’s one of the major problems larger organizations face: the consumerization of the cloud. Employees use Dropbox, OneDrive or Google Drive to share business files. That’s why enterprises need to offer their employees a solution that is as easy to use as the public clouds. We have been working with Autotask Workplace as a solid solution for enterprises because it offers the same flexibility and ease-of-use as Dropbox, but without the security holes. Users can synchronize files (personal or business files) and managers can keep an eye on what gets in and out.
It sounds like an industry that is moving a lot. Where is next for cloud storage - what are the trends that you are seeing?
Cloud storage services do not focus on storage anymore, because, well, some argue that there is no money to be made with storage.
We clearly see a development into intelligent solutions for project and document management. Dropbox has hundreds of millions of users, all using their service in different ways, so in a couple of years, I believe we’ll be seeing a lot of artificial intelligence added to it, which helps us organize information automatically based on our specific business.
Thanks, Mauricio. Any final words?
I would say, don’t put all your eggs in one basket, especially when using the cloud for backup. But also, think about what your project teams and organization needs regarding features because it’s easy to overpay for a cloud service that you don’t need.
Overall, I’m excited to be close to seeing the developments in this industry and am looking forward to more innovative features and services based on the cloud.