Read the descriptions to find the exercise

D7 x x fx A B C D E F G H I K L Description or Problem Statement Analysis of Valentine's spending. 1 2 3 Part Points Task 1 1 Apply conditional formatting to the spending categories in year 2020 (row 23). The formatting should highlight the highest spend (top 1) cateogry. The result should be formatted with green fill with dark green text. 2 1 Format all the spending numbers in the table to 1 decimal place (the columns labeled Candy to Gift Cards) Press CTRL+5 to save after each problem 4 5 6 7 8 9 10 Valentines Spend ($B) 11 12 13 14 Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 Candy 1.2000 1.5000 1.5000 1.7000 1.4000 1.7000 1.8000 1.7000 1.8000 1.8000 2.4000 Flowers 1.7000 1.7000 1.9000 1.9000 1.9000 2.1000 2.0000 2.0000 2.0000 1.9000 2.3000 Jewelry 3.0000 3.5000 4.1000 4.4000 3.9000 4.8000 4.4000 4.3000 4.7000 3.9000 5.8000 Greeting cards An evening out 0.8000 3.3000 1.1000 3.4000 1.0000 3.6000 1.2000 4.0000 1.0000 3.5000 1.0000 3.6000 1.1000 4.5000 1.0000 3.8000 0.9000 3.7000 0.9000 3.5000 1.3000 4.3000 Clothing 1.5000 1.6000 1.5000 1.6000 1.7000 2.0000 2.0000 1.9000 1.9000 2.1000 2.9000 Gift card/gift certificate 1.2000 1.5000 1.2000 1.5000 1.2000 1.5000 1.7000 1.4000 1.5000 1.3000 2.0000 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 C16 > fx 3 D E G H I J K L . N O 1 Creating Data 3 Task You've been tracking your marathon training in the table below, but you accidentally deleted the distance values. Use the Fill Series Tool to fill in the Distance column with values from 3 (cell C16) to 30 (cell C25) 6 in increments of 3. When you do, the Speed formula will calculate correctly. Then answer the question about fill series in the green cell (613) G13 7 8 Add a comment to cell E18 with the marathon date, 4/10/2021 9 10 11 Press CTRL+S to save after each problem 12 13 When you used the fill series tool, did you select rows or columns to fill this table? 14 15 Distance (miles) Time (minutes) Speed (miles/hour) 16 3 25 8.3 17 44 #DIV/0! 18 72 #DIV/0! 19 90 #DIV/01 20 100 #DIV/0! 21 145 #DIV/0! 22 148 #DIV/0! 23 160 #DIV/0! 24 162 #DIV/0! 25 182 #DIV/01 26 27 28 29 30 31 32 33 34 35 36 37 38 39 F19 x fx F | J K A B C D E G H 1 Description or Problem Analysis using the Zipcodetabulation areas data from the Census Bureau. 2 Statement 3 Part Points Task 4 1 0.5 Add a border under the column headers in the zipcode table below. 5 2 0.5 Format the Census Date column as a short date instead of a number. 6 3 1 Use an Excel tool learned in module 2 to split the ZCTA name column into 2 columns. One column should contain the city, Lexington, 7 and the other should have the rest of the text, Fayette urban, KY. Put the new data in Columns) and K (under labels) 8 4 0.5 In cell F19, write the name of the tool you used to split the data (yellow) 9 5 1 Sort the data first by block, low to high, and secondarily by population, low to high. If done correctly, all records 10 for block 1000 will be at the top listed in order from low to high population. 11 6 0.5 Create a unique list of Lexington Zip codes starting in cell N26. 12 7 1 Use the list you just created to make a data validation drop-down list in cell F21 (gray). 13 14 8 1 In cell F22, use a summary of function to answer the question about total population of the zipcode listed in cell F21. 15 If a different value of zipcode is chosen from cell F21, the calculation should automatically update. 16 Press CTRL+S to save after each problem 17 18 19 What Excel tool or feature did you use to split the ZCTA field? 20 21 Zipcode List: 22 What is the total population of the zipcode listed above in cell F21? 23 24 25 City Fayette urban Population (2010) 20 6 17 26 3 26 27 28 29 30 31 31 32 33 34 35 36 37 38 39 7 Census Date 40269 40269 40269 40269 40269 40269 40269 40269 40269 40269 40269 40269 40269 40269 annan State code County code 21 21067 21 21067 21 21067 21 21067 21 21067 21 21067 21 24 21067 21 21067 21 21067 21062 21067 21 21 21067 21 21067 21 21067 21 21067 21057 Tract 1.01 1.01 1.01 1.01 1.01 1.01 1.01 1.01 1.01 101 1.01 101 1.01 1.01 1.01 1.01 1.01 101 1 ZIP census tabulation area 40507 40507 40507 40507 40507 40507 40507 40507 40508 40507 40507 40507 40507 40507 40507 40507 Block 1000 1001 1003 1004 1005 1006 1008 1009 1010 1011 1012 1013 1014 1016 County name ZCTA name Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY Fayette KY Lexington-Fayette urban, KY 14 28 39 2 4 2 17 2 an 71 1010 ACO Cotto VV lowinnton Guttouch V