Excel's PivotTable 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 PivotTable feature and learn about its uses. You will learn how to use PivotTables 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 PivotTable is provided on the 'Pivot' sheet. Click on the 'Pivot' tab to select the PivotTable sheet. Inside the PivotTable1 area right-click and select Show Field List. The PivotTable 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. 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 List and then drag it to the rows box in the PivotTable pane. You will see the four regions enter the PivotTable. Next, drag Meal Price ($) into the values box of the PivotTable 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 $ 00000 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 PivotTable (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 PivotTable pane. What are the total sales for each Quality Rating? Excellent $ Total Sales Good +A $ 000 Very Good $ 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 PivotTable pane. Then drag Region from the Field List into the rows box. The intercoction of West and in the West? To answer this question, proceed as follows. Drag Quality rating from the rows box to the column box in the PivotTable pane. Then drag Region from the Field List into the rows box. The intersection of West and Excellent in the PivotTable 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 PivotTable as is appears from Question 3 above. Next, go to the Values box in the PivotTable 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 ($). Average meal price in the East Region is $ (to the nearest cent). Average meal price for Very Good meals in the East Region is $ cent). (to the nearest template Question 1 North Good 2 V VeryGood " Easi 3 South Good North North VeryGood 23 Sout 22 West 6 Vest Good 27 8 South Good 24 Question 2 9 West Good 45 10 East Excel 15 12 12 Good 13 12 North Good Very Good 14 15 14 North Good 30 Question 3 15 North VeryGood 20 17 16 South Excelent 39 28 17 North Excelent 21 18 East Good 15 Question 4 20 19 South VeryGood 46 21 20 West Exc 22 22 West VeryGood 27 Average Meal Price (Very Good Ea 23 East VeryGood 28 24 Sou VeryGood 25 South Good 28 East VeryGood 29 East 30 33 28 31 North 32 Sout 33 South 34 North VeryGood Good Excele Excelent WeyGood Good 35 East 3 North VeryGood 25 Geod 21 39 South Good M 40 North So 42 South Good ST VeryGood Good 43 North VeryGood 14 44 North VeryGood 12 45 East VeryGood 20 We VeryGood 23 47 Excelent 15 45 South Good 35 40 North Exce " 50 North Good $1 51 North VeryGood 52 East 53 East VeryGood " Good " 54 West Good 21 55 South Excntent 18 East VeryGood " 15 63 Exit 64 North Good Good 28 2 67 North VeryGood 41 North Exc " 70 South Extent 23 12 West Good 53 73 Exete 27 " 74 East 75 North 77 West 78 West Excellent 23 Good 26 VeryGood 33 Good 50 30 79 West Good 25 BO West VeryGood 33 82 West Good 27 So Excelent 25 5 North VeryGood 87 North 88 North VeryGood Good 23 22 " 00 East East 12 Nov VeryGood Good template Region Quity Rating C 93 West Good 42 " 100 301 North VeryGood South VeryGood 100 Good M 102 101 North VeryGed 43 102 Views Good 304 Good 105 106 107 108 108 115 47 25 111 Soun Exce 112 North Excelent 114 115 114 North VeryGood 116 11 Good 117 South Excee 119 130 321 120 Good 14 122 121 South VeryGood 123 124 123 So Good 125 524 South Exce 14 126 125 West Good 129 128 South 130 129 Excelent 133 135 North Excelent 47 135 134 North VeryGood 136 137 138 We Good 138 140 141 Good 20 143 142 144 345 Good 40 146 145 21 147 1 547 East Excel Excele 17 150 14 150 112 153 152 35 154 153 North VeryGood M 155 154 Sout 156 155 Excellent 25 157 156 North Good 26 158 100 100 159 South VeryGood 100 South Excelent 13 So Good 163 39 164 165 184 East VeryGood 34 166 10 106 South VeryGood 23 33 170 2 171 170 54 172 172 Good 26 1M 173 Good 175 574 W Good 25 177 IN 177 Sout Good Very Good 179 180 179 South 181 180 South Exce 3 182 183 South Good 184 Data template estaurant on Quality at le 186 187 1 Good 187 East Good 19 190 Good 51 103 20 194 195 134 South Good 196 197 136 West Good 196 0 199 200 29 201 200 North Good 201 South Ex 201 Good 47 201 203 East VeryGood 22 205 204 South VeryGood 206 205 Sout 17 " 200 208 South Good 210 209 West 211 210 Very Good Exce 21 212 213 212 South VeryGood 214 215 254 South Good 216 215 West Good 217 216 East VeryGood 218 219 218 South Good 221 222 13 15 223 " 224 224 East Good 10 2 22 229 00 227 W Good 229 200 229 North Good 13 231 230 Sout Good $1 212 200 20 234 235 234 North Good 32 256 235 East VeryGood 43 237 236 North Good 201 240 230W 241 242 240 241 Noth WyGood 33 41 20 242 East 243 West V 245 244 West God 240 240 245 250 251 250 West 252 251 East Good VeryGood 253 47 254 253 Excelent 53 255 254 South Good 42 256 257 25 252 North Good 21 250 201 " 262 263 22 264 205 25 267 Noth Ex Good 23 270 259 South VeryGood 271 270 West Good 272 273 Excellent 27 214 273 West 275 274 275 Noth Good 27 47 E Ede template Restaurant Region Quality Rating Meal Price ($) E 273 272 East Excellent 37 274 273 West VeryGood 37 275 274 East Very Good 20 276 275 North Good 43 277 276 South Very Good 47 278 277 East Good 45 279 278 East Very Good 55 280 279 East 281 280 South Excellent Very Good 25 32 282 281 West Good 24 283 282 East Very Good 18 284 283 South Very Good 29 285 284 East Good 44 286 285 South Good 48 287 286 East Very Good 50 288 287 North Good 35 289 288 North Excellent 12 290 289 West VeryGood 50 291 290 East Good 29 292 291 West Excellent 41 293 292 North Excellent 11 294 293 North Good 55 295 294 North VeryGood 52 296 295 West Good 20 297 296 South 298 297 North 299 298 South VeryGood Very Good Excellent 41 46 34 300 299 South Good 40 301 300 West Excellent 43 302