Excel Pivot tables can be a useful tool when doing any kind of data aggregation; however, it is important to understand your Raw data fully before summarizing it, lest you add your data together incorrectly.
Unique Counts – Number of employees at company
Say you have a list of employees that are working for specific projects and customers of yours. You log the hours each one of them spends working with that customer.
Count of Employees
Those less familiar with Pivots and their data may make the mistake of counting the EMPLID field by putting it into the value section which would end up counting all records, reporting eight employees at the company, when there are three.
There are two ways you can count these employees here. Both demonstrated below.
- Put ONLY the EMPLID in the row label section of the pivot to get each unique EMPLID.
- Select the employee IDs to see a count in the bottom.
- Put ONLY the EMPLID in the row label section of the pivot.
- Copy the unique IDs into a new column.
- Run a pivot on that
Pivot tables aggregate data based on the fields you put into row labels box.
I want to count the number of projects my employees are working on For Each customer. Watch below how the numbers change for each field I add into the Row Labels.
Counting projects gives me the same result as counting employees, just a count of the records in my raw data.
Adding in Employee Name will give me a count of projects/records per employee.
Adding in Customer Name will give a count of the number of projects per Employee and Per customer.
This last screenshot could be what I was looking for; though, it’s important to know your data. Every bit of data will have certain anomalies that come with it. Look at Bob’s record for example. He billed time twice to the same project and the pivot table is showing he is working on two projects for Customer2 even though he is only working on one.
If you want to get a unique count of project per Employee/Customer, you can do a process similar to the Employee Count we did earlier.
- Bring the project field into the row label section so that you get a unique view of each Employee-Customer-Project
- I like to setup my fields in tabular form so that each field is it’s own column and I like to remove subtotals for this since I will be copying the data elsewhere.
- Copy and Paste the data and then run another pivot on it like we did above.
In the next post we will go over things to watch out for with a pivot table’s data source.