Excel Pivot Tables
Review sections 1-4 of the “Excel Easy” PivotTable tutorial (Links to an external site.).
Use the data file below to follow along with the tutorial and replicate the PivotTables shown as close as you can. Work each section on a new worksheet.
Quant Check 6 Pivot Table Data.xlsx Download Quant Check 6 Pivot Table Data.xlsx
The data contains information about produce sales for a given company to various regions in a given sales year. Each row is a single order that was shipped.
Direct links to the relevant required sections are given below:
Introduction to PivotTables (Links to an external site.)
Grouping PivotTable Items (Links to an external site.)
Multi-level PivotTables (Links to an external site.)
Frequency Distributions (Links to an external site.)
Section 4 demonstrates our preferred method for generating a histogram, and does not use the “Histogram” chart or the Data Analysis “Histogram” option.
In addition to recreating the tables in this tutorials, complete the following:
Create a PivotTable which can show whether the distribution [by count] of orders for fruit vs vegetable products differs across the 7 sales regions.
Modify your table using “Show Values as” so that you can meaningfully compare the split between categories across regions which have different subtotals.
Recode the “Category Recode” column in the spreadsheet to a 0/1 “dummy” coding, choosing “fruit” as the “1” category. Relabel the column so it is titled “Fruit”.
Create a PivotTable which calculates total orders [by count] in each region, and then uses this “dummy” variable with the Sum and Average functions [for three total entries in “Values”] to calculate the number [sum] and percentage [average] in each region that were for fruit products. [This is similar to the example we did of showing % public vs private schools, and the %s should look identical to your PivotTable in A above.]
Create a PivotTable which calculates the average sales per order for each product category (fruit vs vegetable) across the 7 sales regions. Also answer (to the best of your ability with the limited information) whether there could be anything to explain the differences in the $ sales (or order count) between categories across the 7 regions–is this necessarily indicative of a regional preference? What lurking variables or confounders could there be?
Attach your completed Excel file as your submission. It should have a worksheet for each of the 4 tutorial sections and the additional questions from me above.
