Upskill: Google Sheets Pivot Tables
How to use pivot tables to easily extract important information from your data.
I know many professionals, students, and business owners are tasked with inputting, cutting up, pulling, and making sense of data. One of the primary tools used to do this is Google Sheets since it’s so good for teams that need to be able to collaborate and make sense of all the information they deal with. Pivot tables allow users to gain important insights and once they’re set up, they update on their own!
If you’re not sure if you’d benefit from learning pivot tables. Take my word for it, if you use Google Sheets or Microsoft Excel you need to learn how to create and use pivot tables. If you’re not convinced, here are a few examples of how a pivot table can be used. After this, I’ll show step-by-step how to use pivot tables to calculate how to “Tip Out” employees for a business with multiple employees and a tip jar.
Pivot Table Use Cases:
What is the most profitable day of the week for a business.
What percentage of a TV show’s viewers are under 18 vs over 18.
What’s the yearly average temperature of every city you are considering living in.
What percent of your employees work on average less than 30 hours per week.
Example: Calculating Employee Weekly Tips
Imagine you run a coffee shop, bar, or any other type of retail business that regularly takes tips. You’ve got a tip jar full of dollars and change and you need to divide it up in a manner that is fair to all the employees that worked that day. You also, don’t want this entire process to take two hours EVERY day. Here’s how you can make sure all the employees get their tips and you aren’t pulling your hair out anymore.
1. Setting up sheets to get started
We’re going to start with four sheets; Tips, Hours, Employees, and Weekdays. The Tips and Hours sheets are what get updated daily to capture our business information. The Employees and Weekdays sheets are “lookup sheets” that will be used for VLOOKUPs.
Note:
If you don’t know what a
VLOOKUPis I wrote a great article on how to do it in Microsoft Excel. Luckily for us, Google Sheets and MS Excel are nearly identical so the instructions there will work here as well.
2. Create “Weekday” column on the Hours sheet
We’re going to create a “Weekday” column on our Hours sheet since we would like to know that information at a glance. Here’s the formula I used:
=VLOOKUP(WEEKDAY(A2),Weekdays!A:B,2)
3. Pivot Table: Date, Employee, and % Hours Worked
The first thing we need to know to figure out how to split up tips is how many hours did each employee work each day. More specifically we need to know what percentage of the hours each employee worked for each day. Here’s how to do it.
Select columns “A” through “C” on the Hours sheet. Then select “Insert” on the ribbon at the very top of the screen. After that, select “Pivot Table”. A pop-up should appear after selecting “Pivot Table”. We’ll be creating a new sheet for this pivot table, so leave the settings as they are.
Change the newly created sheet’s name. It’s now called the Date_Employee_PercentHours_Pivot sheet. If you did things correctly your screen should look like this
.
Just as the sheet name says. We want the date first. Click on the “Add” button next to “Rows” under the “Pivot table editor” menu. After this click on “Date” in the pop-up menu. You should have this now.
Add the employee information now by clicking on the “Add” button in the “Columns” section under the “Pivot table editor”. It should look like this with “Date” and “Employee” information.
We’ll now add the percent hours to the pivot table. Click on “Add” in the “Values” section under the “Pivot table editor”. You’ll notice that our table was populated with the hours each of the employees worked on each date. This is NOT what we want. We want the percent of hours worked. The default settings look like this:
Change the “Show as” dropdown from “Default” to “% of row”. The pivot table should now look like this.
This table still has stuff on it that we’d rather not see. We’ll get rid of the empty rows and columns by adding a filter. Click the “Add” button in the “Filters” section under the “Pivot table editor” and then select “Date”. Next click into the drop-down menu.
Click on “Filter by condition” so you can see the expanded menu. Then click into the drop-down menu. Change the selection from “None” to “Is not empty”. Then click “OK”.
The pivot table no longer has the empty rows and columns and should look like this.
The last thing we’ll do to this pivot table is remove the “Grand Total” column that has “100.00%” for every entry. This information isn’t helpful. Click on the green checkmark to de-select it for the “Employee” information under the “Columns” section. This will get rid of the column we don’t want. It should look like this now.
4. Create “Percent Hours” Column on Hours Sheet
Go back to the Hours sheet and create a new column called “Percent Hours”. In the first empty cell, E2, enter the following formula and populate the column accordingly.
=VLOOKUP(A2,Date_Employee_PercentHours_Pivot!A:F,VLOOKUP(B2,Employees!A:B,2))
5. Create “Total Tips” Column for Hours Sheet
On the Hours sheet create a column called “Total Tips”. In the first empty cell, F2, enter the following formula and populate the rest of the column accordingly.
=VLOOKUP(A2,Tips!A:B,2)
6. Create “Tip Out” Column for Hours Sheet
Create a “Tip Out” column on the Hours sheet. Enter the following formula in the first empty cell, G2, and populate the rest of the column accordingly.
=F2*E2
7. Pivot Table: Tip Out
We’re almost done! Here’s how to create the pivot table we want so that we are able to determine how much each person needs to be tipped for each day.
Highlight columns “A” through “G” on the Hours sheet. Now click on the “Insert” tab at the top of the screen then select “Pivot Table”. We’re going to create a new sheet so click “OK”. Change the name of the sheet to something that makes sense, I’m making it the Tips_Pivot sheet. Your screen should look like this.
Do the following then your pivot should look like this:
“Date” informatio for the “Rows”
“Employee” information for the “Columns”
“Tip Out” information for the “Values”
“Date” for the “Filters”
Now get rid of the information we don’t want. Add a filter for “Is not empty” to get rid of the blank rows and columns.
We’re done! Our pivot table looks like this.
It’s super easy now to see exactly what each employee should be tipped for any given day. The only work to do now is to add the tip amount to the Tips sheet and the number of hours worked by each employee to the Hours sheet. The pivot table will update in accord with the new rows of information.
Thank you for reading this article. I hope it was helpful to you. If you’d like to support my writing or want to reach out to me for feedback or requests please become a paid subscriber.
If you are just interested in reading more articles like this to “Upskill” or are interested in Science, Technology, Engineering, Art, or Mathematics please become a free subscriber to get the latest things written by me.
Again, thank you and as always, Full STEAM Ahead!