part 1

1. Open the following excel file: excel2016_cellbasics.xlsx download
2. Select cell D6 and type hlee.
3. Clear the contents in row 14.
4. Delete column G.
5. Using either cut and paste or drag and drop, move the contents of row 18 to row 14.
6. Use the fill handle to put an X in cells F9:F17.part 2
7. Open this excel file: excel2016_modifyingcells.xlsx download
8. Autofit Column Width for the entire workbook.
9. Modify the row height for rows 3 to 14 to 22.5 (30 pixels).
10. Delete row 10.
11. Insert a column to the left of column C. Type SECONDARY CONTACT in cell C2.
12. Make sure cell C2 is still selected and choose Wrap Text.
13. Merge and Center cells A1:F1.
14. Hide the Billing Address and Phone columns
15. part 3
17. Open the following excel file: excel2016_formattingcells.xlsx download
18. Click the Challenge worksheet tab in the bottom-left of the workbook.
19. Change the cell style in cells A2:H2 to Accent 3.
20. Change the font size of row 1 to 36 and the font size for the rest of the rows to 18.
21. Bold and underline the text in row 2.
22. Change the font of row 1 to a font of your choice.
23. Change the font of the rest of the rows to a different font of your choice.
24. Change the font color of row 1 to a color of your choice.
25. Select all of the text in the worksheet, and change the horizontal alignment to center align and the vertical alignment to middle align.part 4
27. Open the following excel file: excel2016_numberformats.xlsx download
28. In cell D2, type today’s date and press Enter.
29. Click cell D2 and verify that it is using a Date number format. Try changing it to a different date format (for example, Long Date).
30. In cell D2, use the Format Cells dialog box to choose the 14-Mar-12 date format.
31. Change the sales tax rate in cell D8 to the Percentage format.
32. Apply the Currency format to all of column B.
33. In cell D8, use the Increase Decimal or Decrease Decimal command to change the number of decimal places to one. It should now display 7.5%.
34. part 5
37. Open the following excel file: excel2016_multiplesheets.xlsx download
38. Insert a new worksheet, and rename it Q1 Summary.
39. Move the Expenses Summary worksheet to the far right, then move the Q1 Summary worksheet so that it is between March and April.
40. Create a copy of the Expenses Summary worksheet by right-clicking the tab. Do not just copy and paste the content of the worksheet into a new worksheet.
41. Change the color of the January tab to blue and the color of the February tab to red.
42. Group the worksheets September, October, and November.

Part 1

2. Open excel file: excel2016_introformulas.xlsx download
3. Click the Challenge tab in the bottom-left of the workbook.
4. Create a formula in cell D4 that multiplies the quantity in B4 by the price per unit in cell C4.
5. Use the fill handle to copy the formula in cell D4 to cells D5:D7.
6. Change the price per unit for the fried plantains in cell C6 to $2.25. Notice that the line total automatically changes as well.
7. Edit the formula for the total in cell D8 so it also adds cell D7.
9. Open excel file excel2016_complexformulas.xlsx download
10. Click the Challenge worksheet tab in the bottom-left of the workbook.
11. In cell D7, create a formula that calculates the tax for the invoice. Use a sales tax rate of 7.5%.
12. In cell D8, create a formula that finds the total for the order. In other words, this formula should add cells D3:D7.
13. In cell D9 create a formula that calculates the total after a 10% discount. If you need help understanding how to take a percentage off of a total, check out our lesson on Discounts, Markdowns, and Sales (Links to an external site.).
15. Open excel file: excel2016_cellreferences.xlsx download
16. Click the Paper Goods tab in the bottom-left of the workbook.
17. In cell D4, enter a formula that multiplies the unit price in B4, the quantity in C4, and the tax rate in E2. Make sure to use an absolute cell reference for the tax rate because it will be the same in every cell.
18. Use the fill handle to copy the formula you just created to cells D5:D12.
19. 1. Part 2 – cell referencing to another worksheet
    2. Click the Catering Invoice tab. You may need to click on the left arrow in the tab area to see the Caterinig Invoice tab.
    3. Delete the value in cell C5 and replace it with a reference to the total cost of the paper goods. Hint: The cost of the paper goods is in cell E13 on the Paper Goods worksheet. See tutorial on cell referencing to multiple worksheets.
    4. Click on the Menu Order worksheet and use the same steps used to calculate the sales tax for each item on the Menu Order worksheet. The total cost in cell E14 should update. Then, in cell C4 of the Catering Invoice worksheet, create a cell reference to the total you just calculated. Note: If you used our practice workbook to follow along during the lesson, you may have already completed this step

    5. Part 4

       1. Open excel file: excel2016_functions.xlsx download
       2. Click the Challenge tab in the bottom-left of the workbook.
       3. In cell F3, insert a function to calculate the average of the four scores in cells B3:E3.
       4. Use the fill handle to copy your function in cell F3 to cells F4:F17.
       5. In cell B18, use AutoSum to insert a function that calculates the lowestscore in cells B3:B17.
       6. In cell B19, use the Function Library to insert a function that calculates the median of the scores in cells B3:B17. Hint: You can find the median function by going to More Functions > Statistical.
       7. In cell B20, create a function to calculate the highest score in cells B3:B17.
       8. Select cells B18:B20, then use the fill handle to copy all three functions you just created to cells C18:F20