Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!
Before starting this article, I'm going to level with you...
PivotTables having a LOT of working parts. Therefore, they can get pretty confusing.
However, they are extremely valuable to know how to use. So, let's first understand the value, that way we can comprehend why they're so important. Then, we'll get into how to configure them for problems on your exam.
Imagine you were recently hired as the school administrator of the prestigious Coding for Crammers Academy.
To begin your new role, you first want to determine which courses have the lowest grades, that way you can contribute your efforts to improving the learning experience in them. To help you gain this insight, you have the following table of student records.
As school administrator, you've been given a table full of all students at the school and important metrics about them.
Let's first quickly walk through how we'd gain this insight without PivotTables, and then learn how to do so with PivotTables.
Solving without a PivotTable
Imagine that we wanted to see the average GPA for each Secondary Focus.
Without PivotTables, we'd have to manually put rows for each Secondary Focus in a group...
...and then use the AVERAGE function on all GPAs in each grouping.
While this works, it's inefficient and not flexible to other future insights we might want to make on our data. What if we wanted to determine how many students were in each course? Or what the average grade was for each year of student? These insights would all require more copying and pasting... and messiness.
Solving with a PivotTable
We can do the above analysis in literal seconds with PivotTables like so:
See how fast that was? PivotTables, while annoying to learn, enable you to make analysis quickly.
Coding a PivotTable
To create a PivotTable, first highlight the data that you want in the PivotTable.
Then, go to "Insert" tab and select "PivotTable". We'll then choose to place this PivotTable on an existing sheet, and select the cell "L2" on our current sheet to place it in.
And just like that, we've got our PivotTable! Now, let's customize it to show some insights!
How to assign rows/columns
Let's say we wanted to determine the number of students in each Primary Focus in each state?
To start, click on the PivotTable graphic on the right.
You must click on the PivotTable in order to begin editing it!
Now, grab the Primary Focus field and drag it into the "Rows" section.
Then, grab the State field and drag it into the "Columns" section.
Notice how we've got the desired row and column values, but no values are populating the PivotTable yet. To do so, we must place a field in the "Values" section to get a count of each record. In our case, let's place the First field in "Values", since every student has a fist name.
Boom! We now see how many students belong to each Primary Focus in each state!
If we wanted to, we could flip the Primary Focus and State fields like so:
Notice how this displays the same data, but just is flipped in orientation.
We could even place both Primary Focus and State in the "Rows" or "Columns"!
Once again, displays the same data, but just in a different manner.
How to select specific values for rows/columns
What if we only wanted to see counts for students for each Primary Focus from OH, IL, and IN?
To do so, we'd click on the downward-facing arrow by "Column Labels" and select only OH, IL, and IN.
PivotTables enable you to automatically "slice-and-dice" your data, essentially meaning you can filter with ease and visualize insights in an instant.
How to use a Slicer to filter
Slicers enable users of the PivotTable to easily filter without needing to click the downward-facing arrow.
To add one to your PivotTable, simply click the "PivotTable Analyze" tab in our ribbon and select "Insert Slicer". In our case, we'll select State to slice on.
Now, we can easily click which states we want to see in our PivotTable!
Slicers are very similar to filters, they just look better and are easier to use!
How to show subcategories
What if we wanted to see data for each Secondary Focus as well? To do so, all we need to do is drag Secondary Focus into "Rows" below Primary Focus!
Now, we can dive deeper into the data and see more drilled-down student details!
When creating subcategories, the higher field will be the parent category and the bottom field(s) will be the subcategories!
How to display only the top/bottom results
What if we only wanted to show the Primary Focuses that had the top 3 most students? In other words... how can we make our PivotTable only show Access, Excel, and SQL student information (since Python has the least number of students at 21)?
Click the arrow by "Row Labels", then in the Filter section select "By value:" > "Top 10". When prompted, select you only want to see the top 3 items.
Notice that now, Python's information does not appear in our PivotTable since it was out of the top 3!
How to only include records that meet a certain condition
What if we only wanted to include students with a GPA over 3.9 in our PivotTable?
To do so, we'd need to place GPA in the "Filters" section.
Then, we'd need to select all values above 3.9.
Now, only students with GPAs above 3.9 are included in our PivotTable's results!
If you only want certain values to be considered within your PivotTable, use the "Filter" section!
How to include "all other values" in a different column
Let's say that Coding for Crammers Academy had renovations done to improve the student experience, including a brand new pool, basketball court, zip-line, and mega quantum-computer (#nerdy). The renovations were completed right before the 2020 school year started, with the primary goal of making students happier, therefore increasing GPAs.
What if we wanted to create a PivotTable that assesses whether or not the renovations accomplished their purpose of improving the student experience (assessing whether or not GPAs increased)?
Essentially what we're asking here is... how did GPAs in 2020 compare to all other years?
To visualize this insight, we need to modify our table slightly by adding a column. This column will be called "Renovated?" and will have a value of "2020" or "Pre-2020" for each record, determining whether or not the student's information correlates to a year when the school was renovated or not.
The formula for this column will be an IF function. To start, here is the formula for the IF function:
=IF(logical_test, [value_if_true], [value_if_false])
If the year is 2020 (meaning that the school was renovated)...
=IF(D2 = 2020, [value_if_true], [value_if_false])
...then the function should output "2020".
=IF(D2 = 2020, "2020", [value_if_false])
If the year is not 2020 (meaning that it's all other years), then the function should output "Pre-2020".
=IF(D2 = 2020, "2020", "Pre-2020")
Let's plug this into the first row of our table and copy it down through all cells.
Now, let's created a PivotTable including this new "Renovated?" column.
In this PivotTable, we'll place the Renovated? field in the "Rows" section and the GPA field in the "Values" section, selecting the Average calculation for GPA.
To use different calculations functions in the "Values" section, click the information button on the respective field!
Now we've successfully grouped all years before 2020 in the "Pre-2020" row! Too bad for Coding for Crammers Academy, it looks like the renovations didn't really work all that well... the GPAs actually decreased a little. At least students can now go hang by the pool in between classes now though!
How to display rows with no data
What if we wanted to show the sum of financial aid received for IL students in each Primary Focus, showing all Primary Focuses (even if one has no financial aid)?
We'd place the Primary Focus field in the "Rows" section and the "Financial Aid" field in the "Values" section...
...and then filter based off of the State field, only selecting IL.
Oh no! No IL students are involved in the "Python" Primary Focus! How devastating!
We must fix this and still show the "Python" Primary Focus, because the question is asking us to show a Primary Focus even if it has no financial aid. How, though?
Simply click the "i" icon next to the Primary Focus field in "Rows" and check the box saying "Show items with no data"!
Now we're able to see the "Python" Primary Focus, despite it not having any financial aid data.
If you need to see rows without data, just click the information button next to the field and check the box saying "Show items with no data".
How to determine the order of row/column values
With the above example, we ended with the following PivotTable:
What if we wanted the order of Primary Focuses to be (1) Excel, (2) Access, (3) SQL, (4) Python?
It's actually much easier than you'd think! All we need to do is highlight each row and rearrange them how we wish!
Whenever you need to have a custom order of row/column values just highlight each row/column and rearrange them how you wish!
How to compute percentages...
...with a subset of data
Above, we created the "Renovated?" column that determined whether or not the student's information was pre or post Coding for Crammers renovations, which occurred in 2020.
Out of each Primary Focus, let's show the percentage of students involved pre and post renovations.
To do so, place Primary Focus in "Rows" and the Renovated? field in "Columns". Then, place the "First" field in "Values", because each student has a first name.
Notice how currently, we're seeing the count of students in each Primary Focus pre and post renovations. We want instead to see the percentage of students pre and post renovations involved in each Primary Focus, respectfully.
To do so, click the "i" icon next to "Count of First" in the "Values" section. Then, in the pop-up box, select "Show data as" and select the "% of Row Total" option.
Now, we're able to see the percentage of students enrolled pre and post renovations, in respect to each Primary Focus.
...considering all records
What if instead, we wanted to see the percentage of students enrolled pre and post renovations within each Primary Focus in respect to the entire student population?
Click the "i" icon next to "Count of First", then "Show data as". This time, select the "% of Grand Total" option.
Notice how each row doesn't result in a sum of 100%. That's because each percentage is now in respect to the entire student population, not just the students within each Primary Focus.
Multiple percentage calculations are available for each field. To access, press the information button next to the field, select "Show data as", and select your desired calculation for percentage.
How to group records in ranges of values
Let's say that we have a PivotTable that shows each Credit Hour and the corresponding sum of Financial Aid number of credit hours that students are taking.
What if instead of showing each Credit Hour, we wanted to organize them in groupings of 3, resulting in 12-14, 15-17, 18-20?
To do so, click any Credit Hour value in the PivotTable, then go to the "Analyze" tab in the top ribbon and select "Group Selection". Here, we can specify that we want each group to encompass 3 numbers in the "By:" input like so:
Now all Credit Hour values are in groups of 3!
You can group your rows/columns into specific value ranges with the "Group Selection" feature.
How to create a calculated field
Let's say that we have the total financial aid given to all students each year in a PivotTable.
What if we wanted to create a calculated field that computed the total amount all students would've received had Coding for Crammers Academy increased financial aid by 15%?
To do so, we'd click on the PivotTable, then click the "Analyze" ribbon at the top, then in the "Fields, Items, & Sets" category select "Calculated Field...".
Within the pop-up window, let's name the formula "Increased Aid" and enter the formula to calculate the weighted GPA, which is detailed below:
='Financial Aid' * 1.15
Notice that now we can see our calculated field in our PivotTable for each year!
Calculated fields enable us to gather calculated insights in our PivotTable without modifying the original source of data.
Given the student table from above, determine the average GPA and average Credit Hours for Secondary Focus. The order of the Secondary Focuses should be "Web Services", "Databases", "Testing".
Excel (Exam 2) Follow-along Guide
It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!
As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!
You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!
Next, let's learn about the COUNTIF, SUMIF, and AVERAGEIF functions!
Fill-in-the-blanks for highlighted, key points in the concepts below!
Step-by-step walkthrough for each of the questions you need to be ready for!
"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."
Oct. 26, 2021