Excel 2016 Tips and Tricks

Lesson 1 – Using Conditional and Custom Formats

Excel 2016

E XERCISE

U SING C ONDITIONAL AND C USTOM F ORMATS



T ASK Use conditional formatting and custom formats in a worksheet.

1. Open REGION25.XLSX . 2. Apply conditional formatting to the range B5:E9; have cells with values greater than $45,000 display in Red Text . Then clear the rule you have just created. 3. Create a new rule for the same range B5:E9; have cells with values less than $45,000 display with a Light Red Fill with Dark Red Text . 4. Add a second conditional format to B5:E9; have cells with values greater than $50,000 display in Green Fill with Dark Green Text . 5. Delete the conditional format for cells with values less than $45,000. ( Hint: Use 7. Select H5 and create a custom number format that displays positive numbers in blue, with the currency format and no decimal places, and negative numbers in red, with parentheses, the currency format, and no decimal places. ( Hint: Use the $#,##0_);[Red]($#,##0) custom format and add the color blue to the positive numbers.) the Conditional Formatting Rules Manager.) 6. Apply purple Data Bars to the range F5:F9.

8. Copy the custom format to H6:H9. 9. Close the workbook without saving it.

Page 18

OFFICEPRO, Inc.