Case Study #1 is intended to test your knowledge of how to summarize data using pivot tables in Excel using the data file, including using filters and interpreting the results – Excel sheet for this assignment is attached in the files.
After watching the video How to Create Pivot Tables in Excel – https://www.youtube.com/watch?v=qu-AK0Hv0b4
Using the excel sheet attached in the files create the following 8 pivot tables
Two (2) pivot tables – Total population and count of counties in 2010 and 2019 by Region
Two (2) pivot tables – Total population and count of counties in 2010 and 2019 by Division within each Region
Two (2) pivot tables – Total population and count of counties in 2010 and 2019 by State Name
Do the following for the state you currently reside in:
State currently reside – delaware , state to compare – maryland
One (1) pivot table – Total population in 2010 and 2019 listed by County Name
One (1) pivot table – Pick another state to compare the previous pivot table results to and show both in one table (total population in 2010 and 2019 listed County Names)
Helpful hints/Tips when doing this:
First, notice in the raw data that the state name is also included in the COUNTY NAME column (example below for Alabama).
If you include this data in your sum of population and count of counties, your answers will be incorrect. For the population, you will double count the values since this row represents the total state population. For the number of counties, you will erroneously add 1 county to each state. Was this done to be tricky??? Not at all. Rather it is an opportunity to show you another powerful feature of pivot tables. You could clean up all of the data by removing these rows manually, but that is tedious and time consuming (and prone to error). And when you work with hundreds of thousands of rows of data, you want to avoid manual intervention. Let Excel address this for you by filtering!! When you create the pivot table, click and drag the COUNTY CODE field to the “Filters” area in the Pivot Table Fields:
Now, when you create the pivot table, you can have Excel filter out all data where the COUNTY CODE equals zero. In your pivot table at the top, click on the funnel where it says multiple items. Make sure 0 is unchecked and all other values are checked and hit OK.
Now your analysis will be correct; Excel just automatically excluded any rows where COUNTY CODE equals 0 — see how powerful pivot tables are?
Second, you are asked to identify the largest and smallest values. You can do this by looking at the pivot table and merely finding the lowest and highest values. But I’ve had students get the answer wrong when they did this. And what if your pivot table had dozens of rows? Would you want to do this manually? Again, let Excel do the work for you! You can sort the data within the pivot table. Don’t be scared – you won’t harm the numbers! To sort any variable, click on any cell within the pivot table for that variable (e.g., 2010 POPULATION). Now right click your mouse. Select Sort, and then either smallest to largest (ascending) or largest to smallest (descending). The pivot table will automatically sort all of the data and results for you. Very powerful!! You can quickly get the answers without worrying about making a mistake! Want to sort another variable? Just click on any cell in another column and follow the same procedure.
