Illustrated Excel 2016 Module 3: SAM Project 1b Kyle Hudson 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

 

Illustrated Excel 2016 Module 3: SAM Project 1b

Kyle Hudson

1. Ethan has three part-time jobs that keep him extremely busy. He is using an Excel workbook

to figure out which jobs give him the highest income so that he can focus his time where he

can maximize his earnings. Ethan has asked for help formatting his workbook to make his

analysis clearer and easier to understand.

Switch to the Income worksheet. Use AutoFit to resize column A to fit its contents.

Change the width of a column.

2.Set the width of column E to 16, so that the words “Monthly Total” are completely visible in

cell E4.

Change the width of a column.

3. Apply the Title cell style to the range A1:E1, then increase the font size in that range to 20

pt.

Apply a cell style to a range of cells.

Change the font size of a range of cells.

4.Merge and center the contents of cell A3:E3, then bold the cell contents

Merge and center a range of cells.

Change the font style of cell contents.

5.Center the text in the range B4:E4, and change the font color to White, Background 1 (1st

column, 1st row of the Theme Colors palette).

Align cell content.

Change the font color of cell contents.

6.Use the Format Painter to apply the format from the range A7:E7 to the range A9:E9

Use the Format Painter.

7.Italicize the contents of cell A11 and change its font to Tw Cen MT.

Change the font style of cell contents.

Change the font of cell contents.

8.In the range A11:E11, apply a Top and Double Bottom Border to the cells, then change the fill

color to Orange (3rd column, 1st row of the Standard Colors palette).

Add a border to a range of cells.

Change the fill color of a range of cells.

9.Ethan’s wants to see at a glance which jobs have an hourly rate that meet his needs.

In the range B13:D13, use Conditional Formatting Highlight Cells Rules to format cells whose

contents are greater than 6.5 with Yellow Fill with Dark Yellow Text.

Apply a conditional formatting rule.

Format a conditional formatting rule.

10.Apply the Short Date number format (e.g., 6/20/2018) to cell C16.

Set the number format for a cell.

11.Apply the Percentage number format to cell C17 and decrease the number of decimal places

shown to zero.

Set the number format for a cell.

12. Ethan would like to see the earning threshold that he set in cell C18 in a monetary and

rounded format.

Apply the Accounting number format to cell C18, then decrease the number of decimal places

shown to zero. (Hint: The Accounting number format may change to Custom after you

decrease the number of decimal places).

Set the number format for a cell.

13. Find and replace all occurrences of the text “Hrly” with Hourly. (Hint: You should make two

replacements).

Find and replace a cell value.

Find and replace a cell value.

14. Check the Spelling in the workbook to identify and correct any spelling errors. (Hint: You

should find and correct at least two spelling errors.)

Update a value in a cell.

Update a value in a cell.

15.Ethan added a row to his worksheet to account for his income in June, so he no longer needs

the reminder note in cell A20.

Delete row 20.

Delete a Row.

16. Rename the Income worksheet Jan-Jun Income, then change the tab color of the worksheet to

Light Blue (7th column, 1st row of the Standard Colors palette).

Change the name of a worksheet.

Change the color of a worksheet tab.

17.Ethan has also tracked last year’s overall balance in this workbook.

Switch to the Balance worksheet. For the range B4:C15, decrease the number of decimal

places displayed to zero.

Set the number format for a range of cells.

18. In the range B4:C15, use Conditional Formatting to create a Data Bars rule, using the

Gradient Fill Green Data Bar color option for these data bars.

Apply a conditional formatting rule.

19. Move the Balance worksheet before the Work Schedule worksheet. (Hint: The order of the

worksheets in the workbook should be as follows: Documentation, Jan-Jun Income, Balance,

Work Schedule.)

 

Change the tab order.