MS Excel Pivot Table Reveiw
ISA235: IT & the INTELLIGENT ENTERPRISE
MS Excel Pivot Table Review
Pivot tables allow you to perform “cross-tab” analysis on data in MS Excel spreadsheets. Cross tabulations are functions performed on aggregated data grouped in different ways, e.g. sales by division by salesperson and date or sales by division by product.
REFERENCES: Again, you should begin this assignment by blowing the dust off of your excel pivot table skills. You can refer back to your material from CSE 148 and here are some additional sites that are solid references.
Pivot Table Intro: https://www.youtube.com/watch?v=BkmxrvIfDGALinks to an external site.
Grouping by dates: https://www.youtube.com/watch?v=24jr3ZUbQS4Links to an external site.
More advanced grouping: https://www.youtube.com/watch?v=St2zFwmwVEELinks to an external site.
REQUIRED: For each of the spreadsheets included below, use pivot tables to analyze the data and answer the related questions. Prepare an MS Word Document with your responses and upload this document to the appropriate Assignment in Canvas. If needed, please use the tutorial materials at the bottom of this page.
Part A. Using spreadsheet “EGPC Sales Rep Expenses”
EGPC Sales Rep Expenses.xlsxDownload EGPC Sales Rep Expenses.xlsx
Which division does not have any expenses for the SU position?
Which position has the highest expenses for the following areas:
meals
phone
hotel
What is the grand total amount spent on gas?
What does each division spend on airfare?
Which division spends the most (and what is the amount) on miscellaneous expenses?
What is the total ending mileage for all sales rep cars for each division?
Which position had the highest and lowest ending mileage?
What is the average miscellaneous expense for division 4 and position S1?
Which sales rep in division 1 had the highest miscellaneous expense?
Which sales rep in position S2 had the most airfare?
Part B. Use spreadsheet “FriendsInNeed”
FriendsInNeedDonors.xlsDownload FriendsInNeedDonors.xls
There is a formatting error in the spreadsheet that needs to be fixed, what is it and how can you fix it?
Here is the fixed spreadsheet, what is fixed? FriendsInNeedDonors-Revised.xlsDownload FriendsInNeedDonors-Revised.xls
Which TCode had the largest single donation and what was the amount?
Which TCode had the most donations and how many?
Which TCode had the largest (and smallest) total donations and what was the total?
Which TCode had the highest average value donation and what was the average?
Which month had the highest number of donations?
Which month had the highest value of donations?
What was the amount donated to TCode 3 in February?
Which donor gave the most number of times?
What months did they give in?
Part C. Use spreadsheet “KleinTrainingSurvey” (assume 1 is bad, and 5 is good)
KleinTrainingSurvey.xls Download KleinTrainingSurvey.xls(NOTE: you need to combine the data)
Using Q4, give a ranked list of our courses using the average scores from the best to worst (and the averages).
For Q8, which location had the highest average?
For Q9, which location had the highest average for course RTS1?
For Q5, which instructor had the highest average score overall?
For Q3, which instructor had the highest average score at location 2 for course RTS3?
Which course at which location had the largest average class size?
Part D. Use “Koko’s Client and Pet Data”
KoKo’s Client and Pet Data.xlsDownload KoKo’s Client and Pet Data.xls
Which walktime in August has the highest average price?
Which walker has the highest average price during walktime 4? Overall?
How many walks were scheduled for walker W004 during walktime 5 in August? In November?
Excel Pivot Table Tutorial:
Warm-Up: PivotTableWarm-Up.xlsx Download PivotTableWarm-Up.xlsxincludes WarmUp data tab and Solution tab. The solution tab includes a pivot table that uses the warm-up data to show top category by month.
Overview of PivotTable.pdf Download Overview of PivotTable.pdf
https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576
