Excel 2016 Tips and Tricks

Lesson 1 – Using Conditional and Custom Formats

Excel 2016

Steps

Practice Data

3. Select the Conditional Formatting button in the Styles group on the Home tab. The Conditional Formatting menu opens. 4. Point to the Clear Rules option. The Clear Rules submenu opens.

Click

Point to Clear Rules

5. Select the desired option. The menu closes and all

Click Clear Rules from Selected Cells

conditional formatting rules are cleared from the selected cells or the worksheet.

Click in any cell to deselect the range. Notice that the data bars no longer appear.

Practice the concept: Select cells B3:D6. Open the Conditional Formatting Rules Manager. Select the Top 5 rule, then click the Delete Rule button. Click OK .

Click in any cell to deselect the range. Notice that the bold font style no longer applies to the five highest values.

C REATING A C USTOM N UMBER F ORMAT  D ISCUSSION

You can format a number with a format that does not exist in Excel by creating a custom number format. Custom number formats can contain text, hyphens, and symbols. For example, in a sales worksheet, you can create a custom number format that will display the text per lb to the right of any number entered in the cell, and then apply that format to the desired cells.

When you create a custom number format, you can base it on an existing format. Custom number formats use the following conventions:

1. A number sign ( # ) indicates a placeholder and can be used, for example, to indicate at what position to place a comma. 2. A zero ( 0 ) is used when a number should always be displayed. For example, if there is no number at the specified position, Excel will display a 0, such as 0.5. 3. The format can contain four sections separated by semi-colons: the first section controls the appearance of positive numbers; the second section controls the

Page 14

OFFICEPRO, Inc.