The pivot table can be a great analytics tool when it is used wisely — unfortunately, most Excel users don't even know what a pivot table is. Here's a brief intro to this powerful feature, with a simple four-step tutorial to create your own.

 

What is a Pivot Table?


The pivot table is one of Excel’s most powerful features. 

It's a data processing tool that allows you to organize, analyze and compare large amounts of data quickly to be able to make more informed decisions.

Unfortunately, it's earned a (quite undeserved) reputation as one of the most complex features to use. As you will see, it's actually quite simple to create pivot tables, and you'll be able to create your own within minutes of reading this article.

Why You Should Use Pivot Tables

There are many use cases for pivot tables, but here are two simple examples that will help you get the gist of it.

Identify where your resources are spending time 

When your company works on several projects in parallel, it's not always easy to know which projects are the most time consuming. How much time are you spending on your important projects? How much are you spending on secondary administrative tasks?

To get an answer to these questions, you can rely on a pivot table which will tell you exactly how much time each of your employees is spending on each project.

To create that pivot table, you will have a worksheet containing your projects and resources as well as the amount of time that they have spent working on a certain activity.

Screen Shot 2018-03-12 at 18.01.02.png

In our example, the pivot table allows you to see that you're spending over 40% of your time on the Lighthouse of Alexandria project. You should ensure that it's bringing in 40% of your revenue and that you're on track, otherwise you may be spending time unwisely.

See if you're investing enough time in revenue-generating activities


Now that you know what activities you're spending time on, you'll also be interested in learning which of them generate cash, and which of them burn cash.

Breaking down your projects to separate billable and non-billable hours will help you do just that, and give you valuable information about how you should allocate your efforts.

And... you can also do that with a pivot table!

This time, you will need a worksheet containing your resources, your projects, the time spent on each project, and whether or not the time is billable.

That table will give you the number of billable hours your resources spent on revenue generating activities. 

Screen Shot 2018-03-12 at 18.07.46.png

 

In our example, you can now see that you're spending about 40% of your time on non-billable hours. Since that represents a significant share of your time, you should perform a review of your non-billable activities and see if some of them could be billed to your clients.

Keep essential information at hand

Subscribe to our blog to get our articles, case studies and even more...

How to Create Pivot Tables in Excel

Now that you understand the purpose of pivot tables, here's how to actually build one.  

full full steps official !!!!.gif

 

Important: Before generating your pivot table, please make sure that you have entered the data you want to analyze in the cells and ensure that there are no empty cells otherwise you will not be able to create your pivot table.

Note: Microsoft Excel version 2011 was used for this article. If you are using a different version of Excel, or Google Sheets you can do the same thing.

Step 1

Highlight your data table then click on “Data” in your toolbar, then click on “Pivot table. 

Step 1 official !!.gif

Step 2

A “Pivot Table Builder” panel will appear on your right,  you will have to choose your settings and click "OK". In our example, the Pivot Table will appear in the existing worksheet.

Once the Pivot Table has been created, you must decide which fields to add. A "Field" is the column header from our data table.

In the Pivot Table field list you will select each "Field" you want to add. In this example, we want to identify the number of Billable hours each Resource has spent on an activity. Therefore we will check the Type of hours, Resources and Number of hours fields.  

Step 2 official.gif

Step 3

Then you can simply drag & drop your selected fields into the different boxes below.

Type of hours & Resources have been added to the Rows box while Number of hours has been added to the Values box. 

Step 3 official !!.gif

Step 4

The Pivot Table will compute and summarize the fields that have been selected. In our example, the pivot table presents the number of Billable vs Non- Billable hours each Resource has completed. 

Screen Shot 2018-03-12 at 18.06.47.png

If you are facing any other issues with Excel, let us know and we’ll be happy to help!