stments Assignment Instructions 2 3 4 5 6 7 Co 8 9 10 Create a table named Transactions based on the data in the Range A4:E248 Freeze the first row containing column headings. Apply White, Table Style Medium 4. Add a new column named Percent Change. Enter a formula in the Percent Change column using unqualified structured references to calculate the percent change between the commodity purchase price and the current price (Purchase Price/Current Price). Apply Percentage Number format to the range F5 F248 Add a Total Row to the Percent Change column. Set the totals to display the average of percent change column Apply Solid Blue Data Bars formatting to the Percent Change column. Filter the data to only display Stocks and Bonds Cat the datab Dealear than his Community, than he C Close 10 554 8 8 10 12 8 X 4 DU AutoSave On File Paste 137 Clipboard 1 Home X 16- 2 A 5 wi Calibri 18 576-4819 19 733-4754 Insert BIU- 20 717-2564 21 921-5213- 22 625-4305 E B Page Layout X Bond Bond Bond CD Bond Bond CD Stock Bond Stock Stock Bond Stock CD CD Font Stock Stock Rond 11 fx C Ranger Williams Bristol Investments 3 4 Transaction # Commodity Broker Purchase Price Current Price 5 435-5493 Penaflor $22.00 6 187-7494 Penaflor $22.00 7 473-3031 $45.00 8 960-9611 $52.00 9 699-6483. $48.00 10 802-5137 $40.00 11 461-4933 12 330-2786 13 159-3539 14 836-1675 15 729-2856 16 287-1853 17 145-2289 Ranger Williams Transactions Ranger Ranger Williams Williams Penaflor Penaflor Hicks Ranger Hicks Williams Ranger Williame alhrout EXP19 Excel Ch04 HOEAssessment Investments... Type here to search Formulas Data AA A EEEEE D 5 $64.00 $68.00 $14.00 $58.00 $95.00 $12.00 $85.00 $31.00 $43.00 $92.00 $43.00 $36.00 E Alignment * $34.00 $100.00 $63.00 $31.00 $31.00 $54.00 $91.00 $84.00 $26.00 $88.00 $63.00 $38.00 $81.00 $34,00 $59.00 $33.00 $80.00 $54 00 Review O 28 View F F Help General $%95 C G - Saved Number H f hp AutoSave Frie Paste 137 Home Insert Page Layout LB Clipboard Fu Calibri BIU- 8 23 603-4162 Stock 24 908-1628 Stock 25 127-2266 CD 26 801-3631 Bond 27 650-4709 28 533-5761 x Stady in CD Stock 29 532-7861 CD 30 841-9431 Bond 31 841-3662 CD 32 393-3257 Bond 33 534-6476 Stock 34 773-3335 CD 35 282-6333 Stock 36 112-4611 Stock 37 426-9757 Bond Stock 38 787-1243 39 682-4714 40 810-2476 41 673-3035 42 919-8340 Stock 43 751-6194 Bond 44 377-6288 Bond AAR: 7800. Dand 888 Font V -11-AA E-A- & C Williams Williams Ranger Ranger Williams Hicks Penaflor Hicks Penaflor Hicks Williams Penaflor Hicks Penaflor Hicks Hicks Williams Hicks Ranger Ranger Penaflor Transactions Ranger anallee Type here to search alhrout EXP19 Excel Ch04 HOEAssessment Investments Saved Formulas Data Review View Help 5 D $76.00 $96.00 $60.00 $37.00 $50,00 $53.00 $58.00 $29.00 $34.00 $9.00 $78.00 $31.00 $$2.00 $68.00 $40.00 $87.00 $46.00 $49.00 $59.00 $36.00 $50.00 $44.00 235.00 0 Alignment $35.00 $78.00 $54.00 $54.00 $65.00 $75.00 $47.00 $43.00 $41.00 $83.00 $77.00 $45.00 $91.00 $95.00 $71.00 $66.00 $71.00 $49.00 $56.00 $52.00 $90.00 $89.00 274.00 O !! 28 Fu General $%98 G Number #D 5 hp Conditional Formatting- # AutoSave Frie Paste 137 Home Insert Page Layout LB Clipboard Fu Calibri BIU- 8 23 603-4162 Stock 24 908-1628 Stock 25 127-2266 CD 26 801-3631 Bond 27 650-4709 28 533-5761 x Stady in CD Stock 29 532-7861 CD 30 841-9431 Bond 31 841-3662 CD 32 393-3257 Bond 33 534-6476 Stock 34 773-3335 CD 35 282-6333 Stock 36 112-4611 Stock 37 426-9757 Bond Stock 38 787-1243 39 682-4714 40 810-2476 41 673-3035 42 919-8340 Stock 43 751-6194 Bond 44 377-6288 Bond AAR: 7800. Dand 888 Font V -11-AA E-A- & C Williams Williams Ranger Ranger Williams Hicks Penaflor Hicks Penaflor Hicks Williams Penaflor Hicks Penaflor Hicks Hicks Williams Hicks Ranger Ranger Penaflor Transactions Ranger anallee Type here to search alhrout EXP19 Excel Ch04 HOEAssessment Investments Saved Formulas Data Review View Help 5 D $76.00 $96.00 $60.00 $37.00 $50,00 $53.00 $58.00 $29.00 $34.00 $9.00 $78.00 $31.00 $$2.00 $68.00 $40.00 $87.00 $46.00 $49.00 $59.00 $36.00 $50.00 $44.00 235.00 0 Alignment $35.00 $78.00 $54.00 $54.00 $65.00 $75.00 $47.00 $43.00 $41.00 $83.00 $77.00 $45.00 $91.00 $95.00 $71.00 $66.00 $71.00 $49.00 $56.00 $52.00 $90.00 $89.00 274.00 O !! 28 Fu General $%98 G Number #D 5 hp Conditional Formatting- # ise Investiments H (p be Assignment Instructions 6 7 8 9 10 11 12 13 O Enter a formula in the Percent Change column using unqualified structured references to calculate the percent change between the commodity purchase price and the current price. (Purchase Price/Current Price). Apply Percentage Number format to the range F5 F248. Add a Total Row to the Percent Change column. Set the totals to display the average of percent change column. Apply Solid Blue Data Bars formatting to the Percent Change column. Filter the data to only display Stocks and Bonds Sort the data by Broker, then by Commodity, then by Purchase Price Click and drag to set the range A4 F249, which is the Transactions table, as a print area Be sure the value (#A follows the table name in the Print afea box Set row 4 to repeat when printed Close hp $ 8 8 10 12 8 12 09 X