Santa Monica College Chapter 2 Vitamins and Supplements Project

In this project you will add formatting to a daily vitamin and supplement plan to make the spreadsheet more attractive and easier to read.

Throughout the project, use the Undo command ( Ctrl + Z) if you make a mistake.

Skills needed to complete this project:

Using Undo and Redo (Skill 2.5)

Merging Cells and Splitting Merged Cells (Skill 2.7)

Applying Cell Styles (Skill 2.12)

Inserting and Deleting Cells (Skill 2.3)

Aligning Cells (Skill 2.6)

• Changing Fonts, Font Size, and Font Color (Skill 2.9)
• Using Format Painter (Skill 2.13)
• Adding Borders (Skill 2.10)
• Cutting, Copying, and Pasting Cell Content (Skill 2.1)
• Wrapping Text in Cells (Skill 2.4)
• Applying Conditional Formatting Using the Quick Analysis Tool (Skill 2.14)
• Applying Conditional Formatting with Data Bars, Color Scales, and Icon Sets (Skill 2.15)
• Applying Conditional Formatting with Highlight Cells Rules (Skill 2.16)
• Removing Conditional Formatting (Skill 2.18)
• Applying Conditional Formatting with Top/Bottom Rules (Skill 2.17)
• Replacing Formatting (Skill 2.21)
• Open the start file EX2019-SkillReview-2-1. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
• If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.
• Merge and center the worksheet title across cells A1:H1 and cells A2:H2.
• Select cells A1:H1.
• On the Home tab, in the Alignment group, click the Merge & Center button.

Select cells A2:H2.

1. On the Home tab, in the Alignment group, click the Merge & Center button.

Apply the Title style to the main worksheet title.

Select the merged cells A1:H1.

1. On the Home tab, in the Styles group, click the Cell Styles button.

Click the Title style.

Apply the Accent5 style to the worksheet subtitle.

1. Select the merged cells A2:H2.

On the Home tab, in the Styles group, click the Cell Styles button.

1. Click the Accent5

There are extra cells to the left of the patient name and daily cost. Delete them.

Select cells B3:B4.

1. On the Home tab, in the Cells group, click the Delete button.

The patient name would look better aligned at the right side of the cell.

Select cell B3.

On the Home tab, in the Alignment group, click the Align Right button.

Format the Patient Name and Daily Cost labels with bolding and the Blue, Accent 5 font color.

1. Select cells A3:A4.

On the Home tab, in the Font group, click the Bold button.

On the Home tab, in the Font group, click the Font Color button arrow, and select the Blue, Accent 5 color. It is the second to last color in the first row under Theme Colors.

1. Use Format Painter to apply the label formatting to the data table header row (cells A6:H6).

If necessary, click cell A3 or A4.

On the Home tab, in the Clipboard group, click the Format Painter button.

Click cell A6 and drag to cell H6 to apply the formatting.

Add a border beneath the data table header row to separate the titles from the data. The border should be the same color as the font.

1. If necessary, select cells A6:H6.

On the Home tab, in the Font group, click the Borders button arrow, and select More Borders…

1. In the Format Cells dialog, on the Border tab, expand the Color palette, and select the Blue, Accent 5 color from the first row of the theme colors.

Click the bottom border area of the preview diagram to add the border.

Click OK.

1. The data in row 14 are misplaced and belong in the data table. Cut it and insert the cut cells above row 9.

Select cells A14:H14.

On the Home tab, in the Clipboard group, click the Cut button.

Click cell A9.

On the Home tab, in the Cells group, click the Insert button arrow, and select Insert Cut Cells.

1. IMPORTANT: You must complete step 11 correctly in order to receive points for completing the next steps. Check your work carefully.

Apply the Note cell style to the note in cell A13.

Select cell A13.

1. On the Home tab, in the Styles group, click the Cell Styles button.

Click the Note style.

1. The note text is much longer than the width of cell A13, and it looks odd with the cell style applied. Apply text wrapping so all the text is visible within the cell formatted with the Note style.

If necessary, select cell A13.

On the Home tab, in the Alignment group, click the Wrap Text button.

1. Apply conditional formatting using solid blue data bars to cells H7:H11 to represent the relative daily cost of each supplement.

Select cells H7:H11.

Click the Quick Analysis tool button.

Click the Data Bars button.

Apply conditional formatting using Highlight Cells Rules to the cost per bottle data (cells F7:F11) to format cells with a value greater than 20 with yellow fill with dark yellow text.

1. Select cells F7:F11.

On the Home tab, in the Styles group, click the Conditional Formatting button.

Point to Highlight Cells Rules, and select Greater Than…

1. In the Greater Than dialog, type 20 in the Format cells that are GREATER THAN box.

Click the with drop-down arrow and select Yellow Fill with Dark Yellow Text.

1. Click OK.

There might be too much conditional formatting in this worksheet. Remove the conditional formatting from cells G7:G11.

Select cells G7:G11.

1. On the Home tab, in the Styles group, click the Conditional Formatting button.

Point to Clear Rules, and select Clear Rules from Selected Cells.

You would still like to highlight the least expensive cost per pill. Apply conditional formatting to cells G7:G11 using Top/Bottom Rules to format only the lowest value with green fill with dark green text.

Select cells G7:G11.

On the Home tab, in the Styles group, click the Conditional Formatting button.

1. Point to Top/Bottom Rules, and select Bottom 10 Items…

In the Bottom 10 Items dialog, type 1 in the Format cells that rank in the BOTTOM box.

1. Expand the formatting list and select Green Fill with Dark Green Text.

Click OK.

Click cell G12 so the cost per pill data is no longer selected.

1. Find all of the values that use the Accounting Number Format with four digits after the decimal and change the formatting to the Accounting Number Format with two digits after the decimal.

On the Home tab, in the Editing group, click the Find & Select button, and select Replace….

In the Find and Replace dialog, ensure that there are no values in the Find what and Replace with boxes.

If necessary, click the Options>> button to display the Find and Replace options.

Click the Format… button next to the Find What box.

1. In the Find Format dialog, on the Number tab, click Accounting in the Category list. If necessary, change the Decimal places value to 4. Verify that the Symbol value is $.

Click OK.

Click the Format… button next to the Replace with box.

In the Replace Format dialog, on the Number tab, click Accounting in the Category list. If necessary, change the Decimal places value to 2. Verify that the Symbol value is $.

1. Click OK.

Click Replace All.

Excel should make nine replacements. Click OK.

1. Click Close.

Save and close the workbook.