1 F6 ft West E G H Question 1 Total Sales East North South West Grand Total Question 2 Total Sales Excellent Good Very Good B D 1 Restaurant Region Quality Rating Meal Price (8) 2 1 West Good 25 3 2 South Good 18 4 3 West Very Good 30 S 4 East VeryGood 50 6 5 West Good 37 7 6 East Good 40 8 7 North Very Good 55 9 8 North Good 25 10 9 West Good 36 11 10 West Excellent 54 12 11 East VeryGood 10 13 12 East VeryGood 32 14 13 West Excellent 32 15 14 South VeryGood 50 16 15 East Good 48 17 16 East Good 27 18 17 South Excellent 48 19 18 East Very Good 38 20 19 South Excellent 11 21 20 East Very Good 26 22 21 East VeryGood 33 23 22 East Excellent 38 24 23 South Good 51 25 24 North Excellent 42 26 25 North Good 50 27 26 West VeryGood 27 28 27 East VeryGood 20 29 28 West Good 25 Data Pivot + Question 3 Total Sales (Excellent, West) Question 4 Average Meal Price (East) Average Meal Price (Very Good, East) Calculation Mode: Automatic Workbook Statistics F6 E Meal Price ($) D 28 46 41 13 10 44 45 26 48 45 32 Restaurant 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 fx West Region Quality Rating 29 West Excellent 30 North Very Good 31 East Excellent 32 North Excellent 33 East Excellent 34 North VeryGood 35 East Excellent 36 West VeryGood 37 South Very Good 38 East Excellent 39 North Excellent 40 North VeryGood 41 North VeryGood 42 East Good 43 South Very Good 44 South Good 45 West Very Good 46 East Good 47 South Very Good 48 South Very Good 49 East VeryGood 50 North Good 51 North Excellent 52 West Very Good 53 South VeryGood 54 East Good 55 South Good 56 West Excellent 57 West Excellent Data Pivot + 27 55 28 31 12 29 31 14 54 27 30 33 14 16 54 26 43 12 ill L Aral ... Data Quality Rating VeryGood Good Excellent VeryGood Good Good Excellent Excellent VeryGood Good Very Good Good Good Excellent Good Very Good Excellent Good Excellent Excellent Excellent Excellent Very Good Excellent Very Good Excellent Very Good VeryGood Good Pivot + 20 49 21 46 26 17 21 27 34 47 18 18 53 24 51 44 16 32 40 14 48 32 11 53 49 54 45 26 23 = Calculation Mode Automatin SAL E Restaurant Region 262 261 West 263 262 West 264 263 East 265 264 East 266 265 East 267 266 West 268 267 West 269 268 South 270 269 West 271 270 North 272 271 East 273 272 East 274 273 West 275 274 East 276 275 East 277 276 West 278 277 South 279 278 South 280 279 North 281 280 North 282 281 South 282 West 284 283 East 285 284 South 286 285 East 287 286 East 288 287 North 288 North 290 289 South A Data Quality Rating Good Very Good Excellent Excellent Good Good Good Excellent VeryGood Very Good Good Good Very Good VeryGood VeryGood Excellent Good Excellent Excellent Good Very Good Very Good Excellent Good Good Good VeryGood Good Excellent Pivot + Meal Price ($) 29 19 15 35 51 55 52 37 22 55 54 33 29 55 40 53 17 45 37 38 13 49 47 53 35 15 283 55 289 15 15 = Meal Price ($) Quality Rating Excellent Very Good Very Good Very Good Excellent Good Very Good Excellent Good Good Very Good Good Restaurant Region 290 289 South 291 290 East 292 291 East 293 292 East 294 293 South 295 294 West 296 295 North 297 296 South 298 297 North 299 298 North 300 299 East 301 300 South 302 303 304 305 306 307 308 309 310 311 312 313 314 315 15 54 17 49 10 43 14 43 28 48 34 37 Excel's Pivot Table Report provides an excellent way to summarize data for two or more variables simultaneously. The goal of this Excel Graded Tutorial is to familiarize you with Excel's Pivot Table feature and learn about its uses. You will learn how to use Pivot Tables by developing a crosstabulation of region, quality rating, and meal price for 300 restaurants and then answering questions about the data. The data for this problem is located on the 'Data' sheet in columns A through D of the Excel Online file below. An empty Pivot Table is provided on the 'Pivot' sheet. Click on the 'Pivot tab to select the Pivot Table sheet. Inside the Pivot Tablel area right-click and select Show Field List. The Pivot Table task pane should now be displayed on the right side of the spreadsheet. In the field list you should see Restaurant, Region, Quality Rating, and Meal Price ($). Construct a spreadsheet to answer the following questions. HHH X Open spreadsheet Questions 1. What are the sales totals for each region? To answer this question, proceed as follows. First, click on Region in the field Ust and then drag it to the rows box in the Pivot Table pane. You will see the four regions enter the Pivot Table. Next, drag Meal Price ($) into the values box of the Pivot Table pane. The second column in the Pivot Table should now be Sum of Meal Price ($). What are the total sales for each Region and the Grand Total? Total Sales East North $ South $ West Grand Total $ 2. What are the sales totals for each Quality Rating? To answer this question, proceed as follows. First, click on Region in the rows box of the Pivot Table (where it was used to answer question 1) and then drag it back into the Field List. Next, drag Quality Rating from the Field List into the rows box of the Pivot Table pane. What are the total sales for each Quality Rating? Total Sales Excellent Good $ Very Goods 3. What is the sales total for a particular combination of Region and Quality Rating? For example, what is the sales total for Excellent restaurants in the West? To answer this question, proceed as follows. Drag Quality rating from the rows box to the column box in the Pivot Table pane. Then drag Region from the Field List into the rows box. The intersection of West and Excellent in the Pivot Table provides the answer. Total sales for Excellent meals in the West Region Total sales for Excellent restaurants in the West are $ 4. What is the average meal price for the entire East region and Very Good meals in the East region to the nearest cent? To answer this question, proceed as follows. Select all numeric values in the Pivot Table as is appears from Question 3 above. Next, go to the Values box in the Pivot Table pane and click on Sum of Meal Prices and select Value Field Settings. In Value Field Settings select Average. Then click OK. In the Values box, it should now read Average of Meal Price (5) Average meal price in the East Region is (to the nearest cent). Average meal price for Very Good meals in the East Region is $ (to the nearest cent). 1 F6 ft West E G H Question 1 Total Sales East North South West Grand Total Question 2 Total Sales Excellent Good Very Good B D 1 Restaurant Region Quality Rating Meal Price (8) 2 1 West Good 25 3 2 South Good 18 4 3 West Very Good 30 S 4 East VeryGood 50 6 5 West Good 37 7 6 East Good 40 8 7 North Very Good 55 9 8 North Good 25 10 9 West Good 36 11 10 West Excellent 54 12 11 East VeryGood 10 13 12 East VeryGood 32 14 13 West Excellent 32 15 14 South VeryGood 50 16 15 East Good 48 17 16 East Good 27 18 17 South Excellent 48 19 18 East Very Good 38 20 19 South Excellent 11 21 20 East Very Good 26 22 21 East VeryGood 33 23 22 East Excellent 38 24 23 South Good 51 25 24 North Excellent 42 26 25 North Good 50 27 26 West VeryGood 27 28 27 East VeryGood 20 29 28 West Good 25 Data Pivot + Question 3 Total Sales (Excellent, West) Question 4 Average Meal Price (East) Average Meal Price (Very Good, East) Calculation Mode: Automatic Workbook Statistics F6 E Meal Price ($) D 28 46 41 13 10 44 45 26 48 45 32 Restaurant 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 fx West Region Quality Rating 29 West Excellent 30 North Very Good 31 East Excellent 32 North Excellent 33 East Excellent 34 North VeryGood 35 East Excellent 36 West VeryGood 37 South Very Good 38 East Excellent 39 North Excellent 40 North VeryGood 41 North VeryGood 42 East Good 43 South Very Good 44 South Good 45 West Very Good 46 East Good 47 South Very Good 48 South Very Good 49 East VeryGood 50 North Good 51 North Excellent 52 West Very Good 53 South VeryGood 54 East Good 55 South Good 56 West Excellent 57 West Excellent Data Pivot + 27 55 28 31 12 29 31 14 54 27 30 33 14 16 54 26 43 12 ill L Aral ... Data Quality Rating VeryGood Good Excellent VeryGood Good Good Excellent Excellent VeryGood Good Very Good Good Good Excellent Good Very Good Excellent Good Excellent Excellent Excellent Excellent Very Good Excellent Very Good Excellent Very Good VeryGood Good Pivot + 20 49 21 46 26 17 21 27 34 47 18 18 53 24 51 44 16 32 40 14 48 32 11 53 49 54 45 26 23 = Calculation Mode Automatin SAL E Restaurant Region 262 261 West 263 262 West 264 263 East 265 264 East 266 265 East 267 266 West 268 267 West 269 268 South 270 269 West 271 270 North 272 271 East 273 272 East 274 273 West 275 274 East 276 275 East 277 276 West 278 277 South 279 278 South 280 279 North 281 280 North 282 281 South 282 West 284 283 East 285 284 South 286 285 East 287 286 East 288 287 North 288 North 290 289 South A Data Quality Rating Good Very Good Excellent Excellent Good Good Good Excellent VeryGood Very Good Good Good Very Good VeryGood VeryGood Excellent Good Excellent Excellent Good Very Good Very Good Excellent Good Good Good VeryGood Good Excellent Pivot + Meal Price ($) 29 19 15 35 51 55 52 37 22 55 54 33 29 55 40 53 17 45 37 38 13 49 47 53 35 15 283 55 289 15 15 = Meal Price ($) Quality Rating Excellent Very Good Very Good Very Good Excellent Good Very Good Excellent Good Good Very Good Good Restaurant Region 290 289 South 291 290 East 292 291 East 293 292 East 294 293 South 295 294 West 296 295 North 297 296 South 298 297 North 299 298 North 300 299 East 301 300 South 302 303 304 305 306 307 308 309 310 311 312 313 314 315 15 54 17 49 10 43 14 43 28 48 34 37 Excel's Pivot Table Report provides an excellent way to summarize data for two or more variables simultaneously. The goal of this Excel Graded Tutorial is to familiarize you with Excel's Pivot Table feature and learn about its uses. You will learn how to use Pivot Tables by developing a crosstabulation of region, quality rating, and meal price for 300 restaurants and then answering questions about the data. The data for this problem is located on the 'Data' sheet in columns A through D of the Excel Online file below. An empty Pivot Table is provided on the 'Pivot' sheet. Click on the 'Pivot tab to select the Pivot Table sheet. Inside the Pivot Tablel area right-click and select Show Field List. The Pivot Table task pane should now be displayed on the right side of the spreadsheet. In the field list you should see Restaurant, Region, Quality Rating, and Meal Price ($). Construct a spreadsheet to answer the following questions. HHH X Open spreadsheet Questions 1. What are the sales totals for each region? To answer this question, proceed as follows. First, click on Region in the field Ust and then drag it to the rows box in the Pivot Table pane. You will see the four regions enter the Pivot Table. Next, drag Meal Price ($) into the values box of the Pivot Table pane. The second column in the Pivot Table should now be Sum of Meal Price ($). What are the total sales for each Region and the Grand Total? Total Sales East North $ South $ West Grand Total $ 2. What are the sales totals for each Quality Rating? To answer this question, proceed as follows. First, click on Region in the rows box of the Pivot Table (where it was used to answer question 1) and then drag it back into the Field List. Next, drag Quality Rating from the Field List into the rows box of the Pivot Table pane. What are the total sales for each Quality Rating? Total Sales Excellent Good $ Very Goods 3. What is the sales total for a particular combination of Region and Quality Rating? For example, what is the sales total for Excellent restaurants in the West? To answer this question, proceed as follows. Drag Quality rating from the rows box to the column box in the Pivot Table pane. Then drag Region from the Field List into the rows box. The intersection of West and Excellent in the Pivot Table provides the answer. Total sales for Excellent meals in the West Region Total sales for Excellent restaurants in the West are $ 4. What is the average meal price for the entire East region and Very Good meals in the East region to the nearest cent? To answer this question, proceed as follows. Select all numeric values in the Pivot Table as is appears from Question 3 above. Next, go to the Values box in the Pivot Table pane and click on Sum of Meal Prices and select Value Field Settings. In Value Field Settings select Average. Then click OK. In the Values box, it should now read Average of Meal Price (5) Average meal price in the East Region is (to the nearest cent). Average meal price for Very Good meals in the East Region is $ (to the nearest cent)