Excel project

Skill Review 2.1Windows MacIn 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 Undocommand ( 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)Steps to complete this project:Mark the steps as checked when you complete them.Open the start file EX2021-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 Editingbutton in the Message Bar at the top of the workbook so you can modify the workbook.Merge and center the worksheet title and subtitle across cells A1:H1 and cells A2:H2.Select cells A1:H1.On the Home tab, in the Alignment group, click theMerge & Center button. Select cells A2:H2.On the Home tab, in the Alignment group, click theMerge & Center button. Apply the Title style to the main worksheet title. Select the merged cells A1:H1.On the Home tab, in the Stylesgroup, click the Cell Styles button. Click the Title style.Apply the Accent5 style to the worksheet subtitle. Select the merged cells A2:H2.On the Home tab, in the Stylesgroup, click the Cell Styles button. Click the Accent5 style.There are extra cells to the left of the patient name and daily cost. Delete them.Select cells B3:B4.On the Home tab, in the Cellsgroup, 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 a blue font color. Select cells A3:A4.On the Home tab, in the Fontgroup, click the Bold button. On the Home tab, in the Fontgroup, click the Font Colorbutton arrow, and select the Turquoise, Text 2 color. It is the fourth color from the left in the first row under Theme Colors. NOTE: Your version of Excel may show the color name as Blue, Text 2. 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 theFormat Painter button. Click cell A6 and drag to cell H6to 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. If necessary, select cells A6:H6.On the Home tab, in the Fontgroup, click the Borders button arrow, and select More Borders…In the Format Cells dialog, on the Border tab, expand theColor palette, and select the Turquoise, Text 2 color from the first row of the theme colors. NOTE: Your version of Excel may show the color name as Blue, Text 2. Click the bottom border area of the preview diagram to add the border. Click OK.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 Cutbutton. Click cell A9.On the Home tab, in the Cellsgroup, click the Insert button arrow, and select Insert Cut Cells.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.On the Home tab, in the Stylesgroup, click the Cell Styles button. Click the Note style.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. 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. Select cells F7:F11.On the Home tab, in the Stylesgroup, click the Conditional Formatting button. Point to Highlight Cells Rules, and select Greater Than…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. Click OK.There might be too much conditional formatting in this worksheet. Remove the conditional formatting from cells G7:G11. Select cells G7:G11.On the Home tab, in the Stylesgroup, 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:G11using 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 Stylesgroup, click the Conditional Formatting button. 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. 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.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 Editinggroup, click the Find & Selectbutton, 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 theOptions>> button to display the Find and Replace options.Click the Format… button next to the Find What box.In the Find Format dialog, on the Number tab, click Accountingin 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 $.Click OK.Click Replace All.Excel should make nine replacements. Click OK.Click Close. Save and close the workbook.Upload and save your project file. Submit project for grading. Recommendations
Vitamins and Supplements
Patient Name
Daily Cost
Supplement
CoQ10
Calcium Citrate
alpa-Lipoic Acid
Boswellia
Stephanie Hammett
$
1.9436
Daily dosage
Per pill
400
500
400
450
# pills per day
200
250
200
450
2
2
2
1
Pills per bottle
120
60
90
90
2
60
Note: Daily dosage and dosage per pill are in mg unless otherwise specified.
Multi-vitamin
N/A
N/A
Cost per bottle Cost per pill
Daily cost
$
17.99 $
0.1499 $
0.2998
$
12.99 $
0.2165 $
0.4330
$
27.99 $
0.3110 $
0.6220
$
52.99 $
0.5888 $
0.5888
$
17.00 $
0.28 $
0.57