A very Basic skills in Excel/A Skills Approach: Excel 2013
Chapter 2: Formatting Cells
skill review 2.1
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:
Step 1
Download
start file
Using Undo and Redo
Merging Cells and Splitting Merged Cells
Applying Cell Styles
Inserting and Deleting Cells
Aligning Cells
Changing Fonts, Font Size, and Font Color
Using Format Painter
Adding Borders
Cutting, Copying, and Pasting Cells
Wrapping Text in Cells
Applying Conditional Formatting Using the Quick Analysis Tool
Applying Conditional Formatting with Data Bars, Color Scales, and Icon Sets
Applying Conditional Formatting with Highlight Cells Rules
Removing Conditional Formatting
Applying Conditional Formatting with Top/Bottom Rules
Replacing Formatting
1. Open the start file EX2013-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.
2. 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.
3. Merge and center the worksheet title across cells A1:H1.
a. Select cells A1:H1.
b. On the Home tab, in the Alignment group, click the Merge & Center button.
4. Apply the Title style to the worksheet title.
a. If necessary, select the merged cells A1:H1.
b. On the Home tab, in the Styles group, click the Cell Styles button.
c. Click the Title style.
5. There are extra cells to the left of the patient name and daily cost. Delete them.
a. Select cells B2:B3.
b. On the Home tab, in the Cells group, click the Delete button.
1 | Page
skill review 2.1
Last Updated 4/3/15
A Skills Approach: Excel 2013
Chapter 2: Formatting Cells
6. The patient name would look better aligned at the right side of the cell.
a. Select cell B2.
b. On the Home tab, in the Alignment group, click the Align Right button.
7. Format the Patient Name and Daily Cost labels with bolding and the Blue-Gray, Text 2
font color.
a. Select cells A2:A3.
b. On the Home tab, in the Font group, click the Bold button.
c. On the Home tab, in the Font group, click the Font Color button arrow, and select the BlueGray, Text 2 color from the first row of the theme colors.
8. Use Format Painter to apply the label formatting to the data table header row (cells A5:H5).
a. If necessary, click cell A2 or A3.
b. On the Home tab, in the Clipboard group, click the Format Painter button.
c. Click cell A5 and drag to cell H5 to apply the formatting.
9. 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.
a. If necessary, select cells A5:H5.
b. On the Home tab, in the Font group, click the Borders button arrow, and select More Borders…
c. In the Format Cells dialog, on the Border tab, expand the Color palette, and select the Blue-Gray,
Text 2 color from the first row of the theme colors.
d. Click the bottom border area of the preview diagram to add the border.
e. Click OK.
10. The data in row 13 are misplaced and belong in the data table. Cut it and insert the cut cells
above row 8.
a. Select cells A13:H13.
b. On the Home tab, in the Clipboard group, click the Cut button.
c. Click cell A8.
d. On the Home tab, in the Cells group, click the Insert button arrow, and select Insert Cut Cells.
11. Apply the Note cell style to the note in cell A12.
a. Select cell A12.
b. On the Home tab, in the Styles group, click the Cell Styles button.
c. Click the Note style.
2 | Page
skill review 2.1
Last Updated 4/3/15
A Skills Approach: Excel 2013
Chapter 2: Formatting Cells
12. The note text is much longer than the width of cell A12, 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.
a. If necessary, select cell A12.
b. On the Home tab, in the Alignment group, click the Wrap Text button.
13. Apply conditional formatting using solid blue data bars to cells H6:H10 to represent the relative daily
cost of each supplement.
a. Select cells H6:H10.
b. Click the Quick Analysis tool button.
c. Click the Data Bars button.
14. Apply conditional formatting using Highlight Cells Rules to the cost per bottle data (cells F6:F10)
to format cells with a value greater than 20 with light red fill with dark red text.
a. Select cells F6:F10.
b. On the Home tab, in the Styles group, click the Conditional Formatting button.
c. Point to Highlight Cells Rules, and select Greater Than…
d. In the Greater Than dialog, type 20 in the Format cells that are GREATER THAN box.
e. Click OK.
15. There might be too much conditional formatting in this worksheet. Remove the conditional
formatting from cells G6:G10.
a. Select cells G6:G10.
b. On the Home tab, in the Styles group, click the Conditional Formatting button.
c. Point to Clear Rules, and select Clear Rules from Selected Cells.
16. You would still like to highlight the least expensive cost per pill. Apply conditional formatting to
cells G6:G10 using Top/Bottom Rules to format only the lowest value with green fill with dark
green text.
a. If necessary, select cells G6:G10.
b. On the Home tab, in the Styles group, click the Conditional Formatting button.
c. Point to Top/Bottom Rules, and select Bottom 10 Items…
d. In the Bottom 10 Items dialog, type 1 in the Format cells that rank in the BOTTOM box.
e. Expand the formatting list and select Green Fill with Dark Green Text.
f.
Click OK.
17. Click cell G11 so the cost per pill data is no longer selected.
18. 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.
a. On the Home tab, in the Editing group, click the Find & Select button, and select Replace…
b. In the Find and Replace dialog, ensure that there are no values in the Find what and Replace
with boxes.
3 | Page
skill review 2.1
Last Updated 4/3/15
A Skills Approach: Excel 2013
Chapter 2: Formatting Cells
c. If necessary, click the Options >> button to display the Find and Replace options.
d. Click the Format… button next to the Find What box.
e. 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 $.
f.
Click OK.
g. Click the Format… button next to the Replace with box.
h. 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 $.
i.
Click OK.
j.
Click Replace All.
k. Click OK.
l.
Step 2
Upload &
Save
Step 3
Grade my
Project
4 | Page
Click Close.
19. Save and close the workbook.
20. Upload and save your project file.
21. Submit project for grading.
skill review 2.1
Last Updated 4/3/15
Recommended Vitamins and Supplements
Patient Name Xin Zhu
Daily Cost $20.7157
Supplement Daily dosage Per pill # pills per day Pills per bottle Cost per bottle Cost per pill Daily cost
CoQ10 400 200 2 60 $19.99 $3.0015 $6.0030
Calcium Citrate 500 250 2 40 $12.99 $3.0793 $6.1586
alpa-Lipoic Acid 400 200 2 60 $22.99 $2.6098 $5.2197
Boswellia 450 450 1 100 $29.99 $3.3344 $3.3344
Note: Daily dosage and dosage per pill are in mg unless otherwise specified.
Multi-vitamin N/A N/A 2 60 $8.99 $6.67 $13.35
