Microsoft Excel Lab 3 and 5: Pivot Table, excel spreadsheet, pie chart and column chart

User Generated

Xlzben17

Business Finance

Description

please see attach files for instructions. For the lab you need my name First name is Anderson and Last name is Best

Unformatted Attachment Preview

MS Excel 2016 Lab Five Items covered in this Lab ● Create a PivotTable ● Use the PivotTable Builder ● Change the PivotTable Results ● Format and Update the PivotTable Before You Begin. A PivotTable is an interactive table that makes it simple to summarize and analyze large amounts of data in different ways. The quick start webpage at the hyperlink above will provide all the information you need for this assignment. For more detailed information, note the three hyperlinks at the bottom of that webpage. Questions: What are the names of the sales persons located in the United States? What is the total sales amount individuals sold in each city located in the United States? Instructions 1. Open Excel Lab Five Starting File.xlsx 2. Save the file to your PC, One Drive, or a USB Flash Drive as lastname_ExcelLabFive.xlsx. 3. 4. 5. 6. To answer the questions, you will need to create a Pivot Table Click anywhere inside the data in Sheet1 Create a Pivot Table based on the range A1:E800 In the PivotTable Fields panel, check Nation and Employee fields (in that order) 7. Click the dropdown arrow in cell A3; uncheck all countries except the USA 8. In the PivotTable Fields, check City and Sales Value 9. Reposition the fields as highlighted below; rename the Workbook’s Sheets as shown in Figure 1 (click-and-drag). NOTE: The Yellow highlights in Figure 1 are provided for ease of understanding. They are not part of the assignment. 10. Save and upload lastname_ExcelLabFive.xlsx via Blackboard > ASSIGNMENTS > Microsoft Office Assignments 1 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 1/15/2018 Sample Solution Figure 1. 2 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 1/15/2018 ID 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 Nation FR FR FR FR FR FR FR FR FR FR FR EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG EG Employee Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Mohamed Omar Taha Samy Moustafa Ali Mohamed Alaa Magda Khalid Amani Mona Esmat Shaker Mohamed Omar Taha Samy Moustafa Ali Mohamed Alaa Magda Khalid Amani Mona Esmat Shaker Taha Taher Hossam Hassan City Leon Leon Leon Leon Leon Leon Leon Leon Leon Leon Leon Alexandria Alexandria Alexandria Alexandria Alexandria Alexandria Alexandria Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Cairo Tanta Tanta Tanta Tanta Tanta Tanta Sales Value 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Edwards Nick Vickie Richard James Fred John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam Adam Adam Adam Adam John Jane Sam Counter Mine Smith Edwards Nick Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 2444.31 3192.65 818.4 1151.4 1503 392.2 946 4180 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam Adam Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Fred Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 1064 424 299.25 808 6375 353.2 464 120 4806.99 1402.5 2697.5 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam John Jane Sam Counter Mine Smith Edwards Nick Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 1064 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam Adam Adam Adam Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam John Jane Sam Counter Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 54458 584252 112456 1064 424 299.25 808 6375 353.2 464 120 4806.99 1402.5 2697.5 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA CA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA Vickie Richard James Fred Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Adam John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto Toronto New York New York New York New York New York New York New York New York New York New York New York New York New York New York New York New York New York New York New York 54458 584252 112456 1064 424 299.25 808 6375 353.2 464 120 4806.99 1402.5 2697.5 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA UK UK UK UK UK UK John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Yasien John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Yasien John Jane Sam Counter Mine New York New York New York New York New York New York New York New York Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Oxford Oxford Oxford Oxford Oxford Oxford 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK UK Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Yasien John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford 54458 584252 112456 1064 424 299.25 808 6375 353.2 464 120 4806.99 1402.5 2697.5 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 2144 564542 2114 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 UK UK UK UK UK UK UK UK UK UK UK UK GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR James Fred Yasien John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Stacy Stacy John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Oxford Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Stacy Stacy John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Stacy John Jane Sam Counter Mine Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Dortmond Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd 54458 584252 112456 1064 424 299.25 808 6375 353.2 464 120 4806.99 1402.5 2697.5 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Terri John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR James Fred Terri John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Terri John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Stutgaurd Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort 321154 2144 564542 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR GR Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Fran John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred John John Jane Sam Counter Mine Smith Edwards Nick Vickie Richard James Fred Fran Fran John Jane Sam Counter Mine Smith Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort Frankfort 2114 254 21445 21145 54458 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 21145 54458 584252 112456 3654 25487 3654 95446 654 32654 32254 8644 2115 2154 214465 215544 24554 21445 532241 6423 214465 211544 321154 2144 564542 2114 254 21445 1791 1792 1793 1794 1795 1796 1797 1798 FN FN FN FN FN FN FN FN Edwards Nick Vickie Richard James Fred John John Dutch Dutch Dutch Dutch Dutch Dutch Dutch Dutch 21145 54458 21445 532241 6423 214465 211544 321154 Microsoft Excel 2016 Lab Three Items covered in this Lab ● ● ● ● ● ● Format an Excel Spreadsheet Merge and Center Cells AutoSum – Max – Min – Average The Fill Handle Copy and Move Cell Entries Apply Styles ● ● ● ● ● ● Format Workbook Tabs Insert and Move Charts Create a Column Chart Create a Pie Chart Customize a Chart The “Search” lightbulb Before You Begin. Click on the hyperlinks above and read the instructions. This assignment is divided into two parts. Part 1 develops the spreadsheet, Part 2 develops the charts. Instructions Lab Three, Part 1. 1. Open the file: Excel Lab Three Starting File.xlsx 2. Edit Document Properties; place your first and last name in the Author field and “Working with MS Excel 2016” (without quotes) in the Title Field 3. Save the file to your PC, One Drive, or a USB Flash Drive as lastname_ExcelLabThree.xlsx 4. Merge and Center the title “Market Shares of Major Phone Providers in the United States” (without quotes) in the range A1:G1 5. Change the title font to Cambria 14 pt. Bold (the title will not fit; don’t worry, that will change as you complete the assignment) 6. Merge and Center the sub-title “Cellular Phones Sales During Five Years” (without quotes) in the range A2: G2 7. Change the sub-title font to Time New Roman, Bold 8. Bold the range A3:A13 and B3:G3 9. Apply the Total style to the range A10:F10: Home > Style > Total 10. Type =B4+B5+B6+B7+B8 in cell B10, then press enter1 11. Use the AutoSum function to calculate the total in cell C10 for the range C4:C8 12. Use the Fill Handle to copy the function in cell C10 to the range D10:F10 13. AutoSum the range B4:F4 in cell G4 14. Use the Fill Handle to copy the function in cell G4 to the range G5:G8 1 When instructed to type a formula into a cell, you should do that. The copy and paste option does not always work. Also, be sure to enter the entire equation, including the equal sign (=) at the beginning. 1 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 04/04/2018 15. Apply the Accounting format to the range B4:G8 • HOME > Number > Select the small arrow to the right of General > Accounting 16. AutoFit the contents of each column (select all columns and then follow the instructions below) • To select a column, place the cursor on the column letter at the top of the worksheet. When the cursor changes to a small black down-pointing arrow, Click to highlight the column. • Select HOME > Cells > Autofit > Autofit Column Width • Repeat for each column 17. Remove the dollar symbol ($) from cells in range B4:F8 and set the Decimal Places to zero (0) • Select the entire range and Right-Click • Select Format Cells > Decimal Places to 0 and Symbols to None > OK 18. Use the Max function to display the highest value of the range B4:B8 in cell B11 19. Use the Min function to display the lowest value of the range B4:B8 in cell B12 20. Use the Average function to display the average value of the range B4:B8 in cell B13 21. Select the range B11:B13 and use the Fill Handle to copy the functions in these cells to the range C11:G13 22. Delete row 9 23. Rename the worksheet tab. Double-click on the Sheet1 tab and type the new name; Sales Details 24. End of Part 1; save your work. Your spreadsheet should look exactly like the example in Figure 1. If it does not, make corrections before continuing to Part 2 Figure 1. Part 1 should look exactly like this sample. 2 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 04/04/2018 Lab Three, Part 2. 3D Clustered Column Chart 25. Insert a 3D-Clustered Column chart based on the range A3:F8 26. Move this column chart to a new worksheet titled “Column Chart” (without quotes) • Note the small “Search” lightbulb Tab at the top center of the MS Excel Ribbon • Click on the words to open the search field and enter the word “move” (without quotes) (Figure 2. #1) • Locate and select the Move Chart option (Figure 2. #2) • In the Move Chart box, select New sheet and enter the Column Chart title. • Select OK to add the new worksheet. Figure 2. Moving the Chart 27. Position the Column Chart tab to the end of the Workbook sheets. Click-and-hold the Column Chart tab and drag it to the right 28. Change its tab color to Green—right-click on each tab and select Tab color (see Figure 4) 29. Change the chart title to “Five Year Cellular Phone Sales During Five Years” 30. Center the title at the top section of the chart; Make the title Bold, size 16 31. Add a Primary Vertical Axis Title titled “Sales in Dollars” (without quotes) 32. Use the Format Painter to copy the font format from the Title to the Axis Title 33. Apply Style 3 to the chart (Select Design Tab—click on the chart if the tab is not visible) 34. Change the T-Mobile columns color to Light Green 3D Exploded Pie Chart 35. Go back to Sales Details Sheet; select the non-adjacent ranges A4:A8 and G4:G8 (see Figure 3) • Select the labels in column A (highlight A4:A8) • Hold down the Ctrl key and then select the values in column G (G4:G8) • You should see this (Figure 3): 3 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 04/04/2018 Figure 3. Non-adjacent ranges selected 36. Insert a Style 3 3-D Pie Chart based on your selection 37. Move this Pie Chart to a new sheet titled “Pie Chart” (without quotes). Repeat step 26 for this pie chart 38. Position the Pie Chart Tab at the end of the Workbook sheets and change the tab color to Red. (Figure 5) 39. Drag the percent label of each slice outside the Pie Chart 40. Explode the Pie Chart 20% • Right-click on the pie chart and select Format Data Series • Look for the Pie Explosion option in the right column • Use the slide bar to 20% or enter 20% in the field. 41. Change the Pie Chart Title to “Overall Performance” (without quotes) 42. End of Part 2. Your charts should look like the sample charts in Figure 4 and Figure 5. Save and upload lastname_ExcelLabThree.xlsx via Blackboard > ASSIGNMENTS > Microsoft Office Assignments 4 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 04/04/2018 Figure 5. 3D Clustered Column Chart Figure 4. 3D Exploded Pie Chart 5 Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries. The Microsoft trademark, hyperlinks, and video sites are used with permission from Microsoft. 04/04/2018 Market Shares of Major Phone Providers in the United States Cellular Phones Sales During Five Years Q1 2008 Q1 2009 Q1 2010 Q1 2011 Q1 2012 Total Sales Verizon 213554 655487 754665 884657 922354 ATT 323154 421325 512312 554654 864458 All-Tel 402513 521325 521145 564879 587546 Sprint 186545 199844 256455 384564 584654 T-Mobile 152231 251325 321123 564458 654854 Yearly Sales Max Min Average
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

lab three

Market Shares of Major Phone Providers in the United States
Verizon
ATT
All-Tel
Sprint
T-Mobile
Yearly Sales
Max
Min
Average

Q1 2008
213,554
323,154
402,513
186,545
152,231
1277997
402,513
152,231
255,599

Cellular Phones Sales During Five Years
Q1 2009
Q1 2010
Q1 2011
Q1 2012
655,487
754,665
884,657
922,354
421,325
512,312
554,654
864,458
521,325
521,145
564,879
587,546
199,844
256,455
384,564
584,654
251,325
321,123
564,458
654,854
2049306
2365700
2953212
3613866
655,487
754,665
884,657
922,354
199,844
256,455
384,564
584,654
409,861
473,140
590,642
722,773

Total Sales
$ 3,430,717.00
$ 2,675,903.00
$ 2,597,408.00
$ 1,612,062.00
$ 1,943,991.00
3,430,717
1,612,062
2,452,016

Cellular Phone Sales During Five Years

1,000,000

900,000

800,000

Sales in Dollars

700,000

600,000

500,000

400,000

300,000

200,000

100,000

-

Q1 2008

Q1 2009
Verizon

Q1 2010
ATT

All-Tel

Sprint

Q1 2011
T-Mobile

Q1 2012

Overall Performance
16%

28%

13%

Verizon
ATT
All-Tel
Sprint
T-Mobile

22%
21%

lab five

ID
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042

Nation
FR
FR
FR
FR
FR
FR
FR
FR
FR
FR
FR
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG
EG

Employee
Jane
Sam
Counter
Mine
Smith
Edwards
Nick
Vickie
Richard
James
Fred
Mohamed
Omar
Taha
Samy
Moustafa
Ali
Mohamed
Alaa
Magda
Khalid
Amani
Mona
Esmat
Shaker
Mohamed
Omar
Taha
Samy
Moustafa
Ali
Mohamed
Alaa
Magda
Khalid
Amani
Mona
Esmat
Shaker
Taha
Taher
Hossam
Hassan

City
Leon
Leon
Leon
Leon
Leon
Leon
Leon
Leon
Leon
Leon
Leon
Alexandria
Alexandria
Alexandria
Alexandria
Alexandria
Alexandria
Alexandria
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Cairo
Tanta
Tanta
Tanta
Tanta
Tanta
Tanta

Sales Value
112456
3654
25487
3654
95446
654
32654
32254
8644
2115
2154
214465
215544
24554
21445
532241
6423
214465
211544
321154
2144
564542
2114
254
21445
21145
54458
2144
564542
2114
254
21445
21145
54458
584252
112456
3654
25487
3654
95446
654
32654
32254

1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086

CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA

John
Jane
Sam
Counter
Mine
Smith
Edwards
Nick
Vickie
Richard
James
Fred
John
John
Jane
Sam
Counter
Mine
Smith
Edwards
Nick
Vickie
Richard
James
Fred
John
Jane
Sam
Counter
Mine
Smith
Edwards
Nick
Vickie
Richard
James
Fred
John
John
Jane
Sam
Counter
Mine
Smith

Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto

8644
2115
2154
214465
215544
24554
21445
532241
6423
214465
211544
321154
2144
564542
2114
254
21445
21145
54458
21445
532241
6423
214465
211544
321154
2144
564542
2114
254
21445
21145
54458
584252
112456
3654
25487
3654
95446
654
32654
32254
8644
2115
2154

1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130

CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA

Edwards
Nick
Vickie
Richard
James
Fred
John
Jane
Sam
Counter
Mine
Smith
Edwards
Nick
Vickie
Richard
James
Fred
John
John
Jane
Sam
Counter
Mine
Smith
Edwards
Nick
Vickie
Richard
James
Fred
Adam
Adam
Adam
Adam
Adam
John
Jane
Sam
Counter
Mine
Smith
Edwards
Nick

Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto
Toronto

214465
215544
24554
21445
532241
6423
214465
211544
321154
2144
564542
2114
254
21445
21145
54458
2444.31
3192.65
818.4
1151.4
1503
392.2
946
4180
112456
3654
25487
3654
95446
654
32654
32254
8644
2115
2154
214465
215544
24554
21445
532241
6423
214465
211544
321154

1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags