4/27/23, 12:10 PM about:blank
Peer-Graded Assignment: Final Assignment – Part
2
Estimated time needed: 45 minutes
You have now completed the first part of this final assignment. In this second part of the final assignment,
you will take some cleaned and prepared data and create some pivot tables from it to help you analyze the
data.
Software Used in this Assignment
The instruction videos in this course use the full Excel Desktop version as this has all the available product
features, but for the hands-on labs we will be using the free ‘Excel for the web’ version as this is available to
everyone.
Although you can use the Excel Desktop software if you have access to this version, it is recommended that
you use Excel for the web for the hands-on labs as the lab instructions specifically refer to this version, and
there are some small differences in the interface and available features.
Dataset Used in this Assignment
The dataset used in this lab comes from the following source:
https://data.montgomerycountymd.gov/Government/Fleet-Equipment-Inventory/93vc-wpdr under a Public
Domain license.
We are using a modified subset of that dataset for the lab, so to follow the lab instructions successfully please
use the dataset provided with the lab, rather than the dataset from the original source.
Assignment Scenario
In this final assigment, you will be following the scenario of a recently hired Junior Data Analyst in a local
government office, who has been tasked with sorting and analyzing fleet inventory data that was previously
imported and cleaned. You plan to use pivot tables to analyze the data in preparation for the results to be
visualized in a dashboard and added to a data findings report later.
Guidelines for the Submission
Download and open the Montgomery_Fleet_Equipment_Inventory_FA_PART_2_START.XLSX file in
Excel for the web.
Use the course videos from Module 4 and the lab ‘Hands-on Lab 7: Using Pivot Tables’ to help you complete
these tasks.
Tasks to perform:
about:blank 1/3
4/27/23, 12:10 PM about:blank
1. Format the data as a table: Use the Format as Table option to format the data as a table.
2. Use AutoSum to calculate values: Use AutoSum to find the following values for column ‘C’ and
record each of the values:
SUM
AVERAGE
MIN
MAX
COUNT
3. Create a Pivot Table: Use the PivotTable feature to create a pivot table that displays the Department
field in the Rows section, and the Equipment Count in the Values section, so that the pivot table
displays the sum of equipment count by department.
4. Sort the pivot table data: Use the Sort By Value setting on the pivot table to sort it in descending
order by the sum of equipment count.
5. Make two more pivot tables exactly the same as task 3: Follow the same steps you performed in
Tasks 3 and 4 to create two more identical pivot tables so that you end up with 3 worksheets that
contain identical pivot tables.
6. Analyze data in the pivot table: Use the PivotTable Fields pane to manipulate and analyze data in the
two copied pivot table as follows:
In pivot table 2 add the Equipment Class field below the Department field so that the different
vehicle types appear under each department with their respective counts.
Collapse all fields except the top one - Transportation
In pivot table 3 add the Equipment Class field above the Department field so that the different
vehicle types appear first, with the different departments listed underneath each vehicle type
with their respective counts.
Collapse all fields except the top one - CUV
7. Download your workbook: Use‘Save As’and select‘Download a copy’ to download your completed
workbook as Montgomery_Fleet_Equipment_Inventory_FA_PART_2_END.XLSX.
Note: In Excel web version, files are auto saved.
Grading Information
For your assignment to be graded in a subsequent step in this course, you will be required to upload the
completed Excel workbook that you saved in Task 7.
The main grading criteria will be:
Is the data formatted as a table?
Are the AutoSum values correct?
Has the correct pivot table been created and sorted?
Has the pivot table been created two more times?
Have the correct fields been used in pivot tables 2 and 3?
Has the workbook been saved correctly?
You will not be judged on:
Your English language, including spelling or grammatical mistakes.
The content of any text or image(s) or where a link is hyperlinked to.
about:blank 2/3
4/27/23, 12:10 PM about:blank
Author(s)
Steve Ryan
Other Contributor(s)
Sandip Saha Joy
Changelog
Date Version Changed by Change Description
2022-04-
1.4 Anita Verma Updated step 7
12
2021-10-
1.3 Malika Updated dataset source link
11
2020-10- Edited to use the most recent version of the Final Assignment
1.2 Steve Ryan
02 instructions
2020-09-
1.1 Steve Ryan Edited to use separate datasets for Parts 1 and 2
02
2020-08-
1.0 Steve Ryan Post ID review and published in course
24
2020-08- Sandip Saha
0.2 Converted to markdown in GitLab
23 Joy
2020-08-
0.1 Steve Ryan Initial version created in Word
22
© IBM Corporation 2020. All rights reserved.
about:blank 3/3
