Module 2 Formatting a Worksheet - Skills Review

1. Format values.
a. Start Excel, open IL_EX_2-2.xlsx from the location where you store your Data Files, then save it as IL_EX_2_Investments.
b. Format the range B3:B7 using the Accounting number format.
c. Change the format of the date in cell B9 so it appears as 18-Jun.
d. Increase the number of decimals in cell D1 to 1, using a button in the Number group on the Home tab.
e. Save your work.
2. Change font and font size.
a. Select the range A3:A7.
b. Change the font of the selection to Calibri.
c. Increase the font size of the selection to 11 point.
d. Increase the font size of the label in cell A1 to 11 point.
e. Save your changes.
3. Change font styles and alignment.
a. Apply the Heading 2 cell style to cell A1.
b. Use the Merge & Center button to center the label in cell A1 over columns A and B.
c. Apply the italic and bold font formats to the label in cell C1.
d. Use the Format Painter to copy the format in cell C1 to the label in cell A9.
e. Change the alignment of cell B2 to Align Right using a button in the Alignment group on the Home tab.
f. Save your changes.
4. Adjust column width.
a. Resize column C to a width of 21.00 characters.
b. Use the AutoFit feature to automatically resize both columns A and B at the same time.
c. Change the text in cell B2 to Total Managed Assets.
d. Adjust the width of column B to display all of the content in cell B2.
e. Save your changes.
5. Insert and delete rows and columns.
a. Use the Insert dialog box to insert a new row between rows 1 and 2.
b. Use a column heading button to insert a new column between columns B and C.
c. Type Fee in cell C3 and center the label in the cell.
d. Create a formula in cell C4 that calculates the fee for the Boston office by multiplying the total managed assets in cell B4 by the annual fee percentage in cell E1. (Hint: Make sure you use the correct type of cell references in the formula.)
e. Copy the formula in cell C4 to the range C5:C8.
f. Use a row heading button to delete the Philadelphia row from the worksheet.
g. Save your changes.
6. Apply colors, borders, and documentation.
a. Add an outside border around the range A3:C7.
b. Apply the Green, Accent 6, Lighter 80% fill color (second row, last column) to the range D1:E1.
c. Change the color of the font in the range A9:B9 to Green, Accent 6, Darker 50% (last row, last column under Theme Colors).
EX 2-20
d. Add a header in the center section of the worksheet that contains the sheet name.
e. Enter your name in the center section of the worksheet footer.
f. Save your changes.
7. Apply conditional formatting.
a. Select the range C4:C7, then create a Highlight Cells rule that changes cell contents to green fill with dark green text if the value is greater than 50000.
b. Select the range B4:B7, then apply Gradient Fill green data bars. (Hint: Click Green Data Bar in the Gradient Fill section.)
c. Open the Conditional Formatting Rules Manager dialog box and view the conditional formatting rules for the worksheet. (Hint: Click Manage Rules on the Conditional Formatting menu, then click the Show formatting rules for arrow.)
d. Review the rules for the worksheet, making sure your rules are correct, then close the dialog box.
e. Save your changes.
8. Rename and move a worksheet.
a. Rename the Sheet1 tab to Active Management and rename the Sheet2 tab to Passive Management.
b. Add a sheet to the workbook, then name the new sheet Total Fees.
c. Change the Active Management tab color to Green, Accent 6, Darker 50%.
d. Change the Passive Management tab color to Blue, Accent 5, Darker 50%.
e. Reorder the sheets so that the Total Fees sheet comes before (to the left of) the Active Management sheet.
f. Delete the Total Fees sheet.
g. Activate the Active Management sheet, then save your work.
9. Check spelling.
a. Move the cell pointer to cell A1.
b. Use the Find & Select feature to replace the word “Boston” with New York.
c. Use the Spelling tool to check the spelling on the worksheet and correct any spelling errors, using suggestions as appropriate.
d. Save your changes, then compare your Active Management sheet to FIGURE 2-27.
e. Preview the Active Management sheet in Backstage view, submit your work to your instructor as directed, then close the workbook and close Excel

Comments

Post a Comment

Popular posts from this blog

Module 1 Getting Started with Excel - Independent Challenge 1

download The CFO at Riverwalk Medical Clinic has hired you to help him analyze departmental insurance reimbursements. He also would like to see what quarterly revenues would look like with a 20% increase in quarterly reimbursements. You’ve been given a worksheet for this project that contains some but not all of the data. a. Open IL_EX_1-3.xlsx from the location where you store your Data Files, then save it as IL_EX_1_Reimbursements. b. Enter the data shown in TABLE 1-4 in the range E4:E11. c. Type your name in cell A17. d. Move the label in cell F2 to cell A15. e. Use the Clipboard to copy and paste the label in cell F3 to cell A12. f. Use the formula bar to correct the spelling error in the label in cell A6. (Hint: The correct spelling is Immunology.) g. Edit cell A8 to correct the spelling error in the label. (Hint: The correct spelling is Ophthalmology.) h. Type Quarter 1 in cell B3, then use Auto Fill to enter Quarter 2, Quarter 3, and Quarter 4 in the range C3...
Read more

Module 1 Creating Documents with Word - Visual Workshop

download Create the letter shown in FIGURE 1-34. Before beginning to type, click the No Spacing button in the Styles group on the Home tab. Type the letter before formatting the letterhead and applying the theme. To format the letterhead, change the font size of the first line of text to 40 point and apply the Gradient Fill: Blue, Accent 5, Reflection WordArt style. Change the font color of the address line to Blue, Accent 5. Add the bottom border to the letterhead. When the letterhead is formatted, change the font size of the body text to 12 point, apply the Organic theme, then change the font color of the letterhead text to Green, Accent 1. Save the document as IL_WD_1-SakuraDoksa to the location where you store your Data Files, submit the letter to your instructor, then close the document and exit Word.
Read more

Module 1 Creating Documents with Word - Independent Challenge 1

download You work at the Riverwalk Medical Clinic, a large outpatient medical facility staffed by family physicians, specialists, nurses, and other allied health professionals. Your boss has drafted an information sheet to help seasonal allergy sufferers and asks you to edit and format it so that it is eye catching and attractive.  a. Open the file IL_WD_1-5.docx from the drive and folder where you store your Data Files, save it as IL_WD_1_AllergyInfo, then read the document to get a feel for the content. FIGURE 1-32 shows how you will format the info sheet. FIGURE 1-32 Riverwalk Medical Clinic Advice for Seasonal Allergy Sufferers b. Show the rulers in your document window if they are not already visible. c. Accept or ignore all suggested spelling and grammar changes in the document. d. Insert the Riverwalk Medical Clinic logo file Support_IL_WD_1-6.jpg at the top of the document. Change the height of the logo to 1", then position the logo in the bottom right with...
Read more