1. Open e09m1Sales and save it as e09m1Sales_LastFirst
2. Group the five regional worksheets and complete the following steps:
   1. Insert a new row above row 1 of the existing data. Type Agent in cell A1. Apply the Heading 2 Style in the styles group on the home tab.
   2. Type Qtr1 in cell B1. Use the fill handle to add Qtr2-Qtr4 in cells C1:E1. Apply the Heading 3 Style and apply center horizontal alignment in cells B1:E1
   3. Type Total in cell F1. Apply the Heading 2 style and horizontal center alignment.
   4. Click cell F2, type =SUM(B2:F2), and press Enter. Use the fill handle to complete the summary information to the range B3:B19
3. Keep the five worksheets grouped (not including the Summary sheet) and complete the following steps:
   1. Type Total in cell A20. In cell B20, insert a function to total Qtr1 sales. Copy the function to the range C20:F20.
   2. Format the ranges B2:F2 and B20:F20 with Accounting Number Format and zero decimal places.
   3. Format range B3:F19 with comma style and zero decimal places.
   4. Add top and double bottom borders to cells B20:F20
4. Ungroup the worksheets and click the Summary sheet tab
5. Build formulas with 3-d references by completeing the following steps:
   1. Click cell B2 and enter a formula that refers to cell B20 in the North region worksheet
   2. Click cell B3 and enter a formula that refers to cell B20 in the East Region worksheet
   3. Click cell B4 and enter a formula that refers to cell B20 in the South region worksheet
   4. Click cell B5 and enter a formula that refers to cell B20 in the Midwest region worksheet
   5. Click cell B6 and enter a formula that refers to cell B20 in the West Region worksheet.
   6. Select range B2:B6 and copy the formulas to the range C2:F20
6. Apply Accounting Number Format with zero decimal places to the range B2:F2 and Comma Style with zero decimal places to the range B3:F26. Adjust the column widths if they are too narrow to display the formula results.
7. Create a hyperlink to create a ScreenTip such as Click to see North Region Sales.
8. Set a Watch Window to watch the formulas in the range B2:F6 on the summary sheet.
9. Use the data in the summary sheet to answer the summary questions 1,3, and 4 in the Q&A sheet.
10. Click the North Region Sheet tab, change the value in Cell B2 to 3,000,000 and observe the changes in the Watch Window. Got to the Q&A Sheet and answer question 2.
11. Create a footer with your name on the left, the sheet name code in the center and the file name code on the right side of the summary and Q&A sheets.

Data Review View DYMO Label Tell me what you want to do B52 File Home Insert Page Layout Formulas % Cut Calibri -11 A A EL Copy - Paste BI U B. A Format Painter Clipboard Font General === s Wrap Text Merge & Center - Alignment $ - %, o Conc Form A19 x fe F G H I J K L I A B C D E 1 1 RAMIREZ 2939259 2342557 2341995 1025534 2 WELCH 2708938 934941 898727 2118207 3 PORTER 1985302 1482065 1893811 2635528 4 VARGAS 786918 1114782 1897215 2310209 5 GARCIA 1955357 1285401 1013555 964520 6 ROWE 1198055 837533 2948099 2405536 7 WARNER 1416594 2490559 1049595 2188222 8 CHAVEZ 1602912 1953681 2944475 2816219 9 PERKINS 2002916 1957926 1226160 2142514 10 MOSS 2009325 2696398 1554603 2966339 11 PEARSON 2227845 1620944 1070441 1661592 12 JENKINS 2805388 1087934 994042 2422076 13 LANE 2153962 1811986 2648657 854296 14 BOWEN 1578964 2039752 2476669 2684340 15 VASQUEZ1611203 1533909 2596551 2477467 16 JOHNSTON 2241904 1642838 2532364 2772816 17 GRIFFIN 1094276 2561899 1236239 2843057 18 Murray 2865956 1713440 1492076 2414096 38 30 North Region East Region South Region Midwest Region West Region Summary Q&A Ready View DYMO Label Tell me what you want to ESSE File Home Insert Page Layout Formulas Data Review % Cut Calibri 11 - AA === En Copy Paste Format Painter BI U. . A Clipboard Font General Wrap Text Merge & Center - $ %, 88 cc For Number Alignment E27 : X for F G H I J K L A B C D E 1 WELCH 2970278 2745075 2770899 2884316 2 DIAZ 927680 1403126 2722449 1888857 3 FERNAND 1298315 2860017 1379505 866085 4 WARNER 1137958 2375952 2644342 2491575 5 CASTILLO 1464278 2778588 2899269 2408810 6 MCKINNE 2168348 2627221 2126781 757688 7 HERNAND 1976664 1705329 1424847 1967925 8 SIMMONS 1184096 880239 2539630 1669728 9 HAMILTOI 1817772 961310 2562967 2039712 10 WADE 2311082 881120 997747 2341476 11 ROMERO 1478519 1267136 2088804 2562605 12 LYONS 2195341 1933184 2104351 2742345 13 EDWARDS 1337622 1443008 2018262 870457 14 FLEMING 2586901 2932777 2155382 837248 15 BRYANT 1666247 2391302 1513867 2980500 16 ELLIS 1965896 2346107 2465415 2441747 17 ALLEN 2623582 1892202 1350966 2014078 18 HICKS 884933 2742915 1493625 2542342 North Region East Region South Region MidWest Region West Region Summary Q&A Ready Formulas Data Review View DYMO Label Tell me what you want to do... B52 File Home Insert Page Layout % Cut Calibri EL Copy - Paste Format Painter B I U - 11 A A = = = General - > A Wrap Text Merge & Center, Alignment $ - % , 8 Condition Formatting Clipboard Font Number Al F G H I J K L M N X for ALVAREZ B C D E 1 ALVAREZ 1975768 908438 2467861 995968 2 BURKE 1870819 1491954 1518462 896725 3 MILLS 1637883 2311459 1244294 1093819 4 TERRY 867586 2275925 2282891 1591631 5 SMITH 2991287 1102679 2174872 2210533 6 MCKINNEY 2500625 949943 2312326 2206369 7 LAWRENCE 2641456 2856196 2804293 2286926 8 CALDWELL 1178942 853327 2708574 1390769 9 CHAVEZ 2156812 1620090 2976606 1972243 10 MENDOZA 1194540 2579485 2163933 2095896 11 PARKER 1657328 1537088 1975515 1322098 12 NEWMAN 1027437 2297171 2177741 1784112 13 BURGESS 2511636 1179560 2971420 1828892 14 KNIGHT 2296964 988094 2154476 1397234 15 HARRIS 768872 887052 1947573 2448898 16 FRAZIER 2001350 879004 2700442 2236324 17 TATE 2016258 1518096 2948162 1429012 18 LONG 1392973 1775752 1775501 1915520 North Region East Region South Region Midwest Region West Region Summary Q&A Tell me what you want to do B52 File Home Insert Page Layout Formulas Data Review View DYMO Label % Cut Calibri - 11AA === D Wrap Text EL Copy - Paste BIU- 0 - A- 3 Merge & Center - Format Painter Clipboard Font Alignment General $ - %, 88 Con Forn Number A1 x fc Your answer 1 Your answer! Question What is the original value of the first quarter sales for the North? What is the value of the first quarter sales for the North after changing the sales in cell B2 in the North Region sheet? Which region had the highest sales? Which region had the lowest sales? North Region East Region South Region Midwest Region West Region ummary Q&A Ready Data Review View DYMO Label Tell me what you want to B52 File Home Insert Page Layout Formulas % Cut Calibri -11 A A EL Copy - Paste BI U B Format Painter A Clipboard Font General === s Wrap Text Merge & Center - Alignment $ - %, Number E27 F G H I J K L A B C D E 1 HALL 1394804 1290368 2994190 2208089 2 WARREN 1150267 901066 1290596 1296213 3 HOPKINS 2899187 1588620 992712 792446 4 ROSS 1947976 2756713 1556203 1001991 5 JENKINS 2352456 2557974 2716598 2183820 6 JACKSON 1723803 2068687 1449571 991752 7 SHAW 2465888 1697547 2349519 2886002 8 NGUYEN 1909540 2529002 1593794 1762829 9 HERRERA 1391854 1925678 2194039 2870594 10 REYNOLD: 1578825 2122391 2692190 968543 11 HAYES 1935134 1429178 1979955 2870766 12 MONTGO 1856128 1779924 2745238 2695877 13 FRAZIER 2105346 904099 2466783 1105979 14 TERRY 1964945 1135473 2400140 2679221 15 MORRISO 1446907 2550093 1084032 2786046 16 SIMMONS 2702980 2790368 1337388 1554623 17 BOWMAN 1891488 2777437 1539645 1919893 18 GUTIERRE 1216521 1008204 2237252 2729235 38 30 North Region East Region South Region Midwest Region West Region Summary Q&A Ready Formulas Data Review View DYMO Label Tell me what you want to di 11 A A = = = General B52 File Home Insert Page Layout % Cut Calibri EL Copy - Paste Format Painter BIUB Clipboard Font B2 - X fic A Wrap Text Merge & Center - Alignment $ - %, o Cor Forn Number 1 Qtri Qtr2 Qtr3 Qtr4 Total 2 North Region 3 East Region 4 South Region 5 Midwest Region 6 West Region North Region East Region South Region Midwest Region West Region Summary Q&A Ready Data Review View DYMO Label Tell me what you want to do B52 File Home Insert Page Layout Formulas % Cut Calibri -11 A A EL Copy - Paste BI U B. Format Painter A Clipboard Font General === s Wrap Text Merge & Center - Alignment $ - %, o Conc Form Number E27 1 PIERCE 2624800 947581 2611777 1993664 2 LEONARD 1681872 918613 1822016 2082706 3 RODRIQU 1549235 2856064 1748243 1362159 4 WARNER 843834 1526741 2744670 1908102 5 LEWIS 2804089 984534 2870560 1008779 6 MAY 2145347 1483153 1979662 2161455 7 GARRETT 1422082 1019641 1765741 1802178 8 INGRAM 998925 1591635 1260782 2065599 9 BANKS 2270402 810836 2453180 1543745 10 ALEXAND 2668235 1749025 926826 2573515 11 CASTRO 2621058 1995817 2617120 1077592 12 HARPER 1759296 1762300 1241501 1665819 13 HARPER 862388 1275482 2022112 2164729 14 HANSON 2112504879159 2218303 1674575 15 FISHER 806408 902013 1382580 1680301 16 LOVE 1001762 2327923 1143574 2826178 17 BELL 772718 1966803 2833988 2968909 18 THOMAS 972679 1282196 1222990 2234261 38 30 North Region East Region South Region Midwest Region West Region Summary Q&A Ready Data Review View DYMO Label Tell me what you want to do B52 File Home Insert Page Layout Formulas % Cut Calibri -11 A A EL Copy - Paste BI U B. A Format Painter Clipboard Font General === s Wrap Text Merge & Center - Alignment $ - %, o Conc Form A19 x fe F G H I J K L I A B C D E 1 1 RAMIREZ 2939259 2342557 2341995 1025534 2 WELCH 2708938 934941 898727 2118207 3 PORTER 1985302 1482065 1893811 2635528 4 VARGAS 786918 1114782 1897215 2310209 5 GARCIA 1955357 1285401 1013555 964520 6 ROWE 1198055 837533 2948099 2405536 7 WARNER 1416594 2490559 1049595 2188222 8 CHAVEZ 1602912 1953681 2944475 2816219 9 PERKINS 2002916 1957926 1226160 2142514 10 MOSS 2009325 2696398 1554603 2966339 11 PEARSON 2227845 1620944 1070441 1661592 12 JENKINS 2805388 1087934 994042 2422076 13 LANE 2153962 1811986 2648657 854296 14 BOWEN 1578964 2039752 2476669 2684340 15 VASQUEZ1611203 1533909 2596551 2477467 16 JOHNSTON 2241904 1642838 2532364 2772816 17 GRIFFIN 1094276 2561899 1236239 2843057 18 Murray 2865956 1713440 1492076 2414096 38 30 North Region East Region South Region Midwest Region West Region Summary Q&A Ready View DYMO Label Tell me what you want to ESSE File Home Insert Page Layout Formulas Data Review % Cut Calibri 11 - AA === En Copy Paste Format Painter BI U. . A Clipboard Font General Wrap Text Merge & Center - $ %, 88 cc For Number Alignment E27 : X for F G H I J K L A B C D E 1 WELCH 2970278 2745075 2770899 2884316 2 DIAZ 927680 1403126 2722449 1888857 3 FERNAND 1298315 2860017 1379505 866085 4 WARNER 1137958 2375952 2644342 2491575 5 CASTILLO 1464278 2778588 2899269 2408810 6 MCKINNE 2168348 2627221 2126781 757688 7 HERNAND 1976664 1705329 1424847 1967925 8 SIMMONS 1184096 880239 2539630 1669728 9 HAMILTOI 1817772 961310 2562967 2039712 10 WADE 2311082 881120 997747 2341476 11 ROMERO 1478519 1267136 2088804 2562605 12 LYONS 2195341 1933184 2104351 2742345 13 EDWARDS 1337622 1443008 2018262 870457 14 FLEMING 2586901 2932777 2155382 837248 15 BRYANT 1666247 2391302 1513867 2980500 16 ELLIS 1965896 2346107 2465415 2441747 17 ALLEN 2623582 1892202 1350966 2014078 18 HICKS 884933 2742915 1493625 2542342 North Region East Region South Region MidWest Region West Region Summary Q&A Ready Formulas Data Review View DYMO Label Tell me what you want to do... B52 File Home Insert Page Layout % Cut Calibri EL Copy - Paste Format Painter B I U - 11 A A = = = General - > A Wrap Text Merge & Center, Alignment $ - % , 8 Condition Formatting Clipboard Font Number Al F G H I J K L M N X for ALVAREZ B C D E 1 ALVAREZ 1975768 908438 2467861 995968 2 BURKE 1870819 1491954 1518462 896725 3 MILLS 1637883 2311459 1244294 1093819 4 TERRY 867586 2275925 2282891 1591631 5 SMITH 2991287 1102679 2174872 2210533 6 MCKINNEY 2500625 949943 2312326 2206369 7 LAWRENCE 2641456 2856196 2804293 2286926 8 CALDWELL 1178942 853327 2708574 1390769 9 CHAVEZ 2156812 1620090 2976606 1972243 10 MENDOZA 1194540 2579485 2163933 2095896 11 PARKER 1657328 1537088 1975515 1322098 12 NEWMAN 1027437 2297171 2177741 1784112 13 BURGESS 2511636 1179560 2971420 1828892 14 KNIGHT 2296964 988094 2154476 1397234 15 HARRIS 768872 887052 1947573 2448898 16 FRAZIER 2001350 879004 2700442 2236324 17 TATE 2016258 1518096 2948162 1429012 18 LONG 1392973 1775752 1775501 1915520 North Region East Region South Region Midwest Region West Region Summary Q&A Tell me what you want to do B52 File Home Insert Page Layout Formulas Data Review View DYMO Label % Cut Calibri - 11AA === D Wrap Text EL Copy - Paste BIU- 0 - A- 3 Merge & Center - Format Painter Clipboard Font Alignment General $ - %, 88 Con Forn Number A1 x fc Your answer 1 Your answer! Question What is the original value of the first quarter sales for the North? What is the value of the first quarter sales for the North after changing the sales in cell B2 in the North Region sheet? Which region had the highest sales? Which region had the lowest sales? North Region East Region South Region Midwest Region West Region ummary Q&A Ready Data Review View DYMO Label Tell me what you want to B52 File Home Insert Page Layout Formulas % Cut Calibri -11 A A EL Copy - Paste BI U B Format Painter A Clipboard Font General === s Wrap Text Merge & Center - Alignment $ - %, Number E27 F G H I J K L A B C D E 1 HALL 1394804 1290368 2994190 2208089 2 WARREN 1150267 901066 1290596 1296213 3 HOPKINS 2899187 1588620 992712 792446 4 ROSS 1947976 2756713 1556203 1001991 5 JENKINS 2352456 2557974 2716598 2183820 6 JACKSON 1723803 2068687 1449571 991752 7 SHAW 2465888 1697547 2349519 2886002 8 NGUYEN 1909540 2529002 1593794 1762829 9 HERRERA 1391854 1925678 2194039 2870594 10 REYNOLD: 1578825 2122391 2692190 968543 11 HAYES 1935134 1429178 1979955 2870766 12 MONTGO 1856128 1779924 2745238 2695877 13 FRAZIER 2105346 904099 2466783 1105979 14 TERRY 1964945 1135473 2400140 2679221 15 MORRISO 1446907 2550093 1084032 2786046 16 SIMMONS 2702980 2790368 1337388 1554623 17 BOWMAN 1891488 2777437 1539645 1919893 18 GUTIERRE 1216521 1008204 2237252 2729235 38 30 North Region East Region South Region Midwest Region West Region Summary Q&A Ready Formulas Data Review View DYMO Label Tell me what you want to di 11 A A = = = General B52 File Home Insert Page Layout % Cut Calibri EL Copy - Paste Format Painter BIUB Clipboard Font B2 - X fic A Wrap Text Merge & Center - Alignment $ - %, o Cor Forn Number 1 Qtri Qtr2 Qtr3 Qtr4 Total 2 North Region 3 East Region 4 South Region 5 Midwest Region 6 West Region North Region East Region South Region Midwest Region West Region Summary Q&A Ready Data Review View DYMO Label Tell me what you want to do B52 File Home Insert Page Layout Formulas % Cut Calibri -11 A A EL Copy - Paste BI U B. Format Painter A Clipboard Font General === s Wrap Text Merge & Center - Alignment $ - %, o Conc Form Number E27 1 PIERCE 2624800 947581 2611777 1993664 2 LEONARD 1681872 918613 1822016 2082706 3 RODRIQU 1549235 2856064 1748243 1362159 4 WARNER 843834 1526741 2744670 1908102 5 LEWIS 2804089 984534 2870560 1008779 6 MAY 2145347 1483153 1979662 2161455 7 GARRETT 1422082 1019641 1765741 1802178 8 INGRAM 998925 1591635 1260782 2065599 9 BANKS 2270402 810836 2453180 1543745 10 ALEXAND 2668235 1749025 926826 2573515 11 CASTRO 2621058 1995817 2617120 1077592 12 HARPER 1759296 1762300 1241501 1665819 13 HARPER 862388 1275482 2022112 2164729 14 HANSON 2112504879159 2218303 1674575 15 FISHER 806408 902013 1382580 1680301 16 LOVE 1001762 2327923 1143574 2826178 17 BELL 772718 1966803 2833988 2968909 18 THOMAS 972679 1282196 1222990 2234261 38 30 North Region East Region South Region Midwest Region West Region Summary Q&A Ready