Lesson 4: PRESENTING DATA VISUALLY USING FORMULA WITH CONDITIONAL FORMATTING

Question 1

A powerful feature which allows you to quickly create a visual analysis on a worksheet by applying different formats on specified cells based on their contents

Answer 1

Conditional formatting

Question 2

It can help you highlight the most important information in your spreadsheets and identify variances of cells’ values with a quick glance.

Answer 2

Conditional formatting

Question 3

This will highlight cells that are greater than, less than, between or equal to values that you can specify.

Answer 3

Highlight Cells Rules

Question 4

This option will allow you to highlight the top or bottom numbers or percent in the selected cells.

Answer 4

Top/Bottom Rules

Question 5

Will display colored bars that are indicative of the value in the cell.

Answer 5

Data Bars

Question 6

Will use different shades of color to represent different values, from low to high

Answer 6

Color Scales

Question 7

Will use sets of similar icons that will visually indicate a cell’s value.

Answer 7

Icon Sets

Question 8

Conditional formatting rules in Excel define 2 key things:

Answer 8

1. What cells the conditional formatting should be applied to, and
2. Which conditions should be met.

Question 9

If none of the ready-to-use formatting rules meets your needs, you can create a new one from scratch.

Answer 9

1. Select the cells to which you want to apply the conditional format and click Conditional Formatting> New Rule.
2. The New Formatting Rule dialog opens and you set the needed rule type. For example, let’s choose “Format only cells that contain” and opt to format the cell values between
3. Click the Format… button and set up your formatting exactly as we did in the previous example.
4. Click OK twice to close the open windows and your conditional formatting is done!

Question 9

To Apply Conditional Formatting:

Answer 9

• In your Excel spreadsheet, select the cells you want to format.
• Go to the Home tab > Styles group and click Conditional Formatting.
• Since you need to apply conditional formatting only to the numbers less than 0, choose Highlight Cells Rules > Less Than…
• Select the format you want from the drop-down list.

Question 10

a conditional formatting function that provides you a Stop if True utility which can stop processing on the current rule when the condition meets and ignore the other rules.

Answer 10

Stop If True Formatting Rule

Question 11

HOW TO EDIT CONDITIONAL FORMATTING RULES IN EXCEL

Answer 11

1. Select any cell to which applies and click Conditional Formatting > Manage Rule
2. In the Conditional Manager Rules Manager dialog, click the rule you want to edit, and then click the Edit, Rule… button.
3. Make the required change Formatting Rule window change and save the edits.
   The Edit Formatting Rule window looks very similar to the New Formatting Rule dialog you used when creating the rule, so you won’t have any difficulties with it.

Question 12

Copy Conditional Formatting

Answer 12

1. Click any cell with the conditional formatting you want to copy.
2. Click Home > Format Painter. This will the mouse pointer to a paintbrush. Note: You can double-click Format Painter if you want to paste the conditional formatting in several different ranges of cells.
3. To paste the conditional formatting, click on the cell and drag the paintbrush down to the last cell in the range you want to format.
4. When done, press Esc to stop using the paintbrush.
   Note. If you’ve created the conditional formatting rule using a formula, you may need to adjust cell references in the formula after copying the conditional format.

Question 13

HOW TO DELETE CONDITIONAL FORMATTING RULES

Answer 13

To delete a rule, you can either:

1. Open the Conditional Manager Rules Manager (as you remember, you open it via Conditional Formatting > Manage Rules..), select the rule and click the Delete Rule button.
2. Select the range of cells, click Conditional Formatting > Clear Rules and choose one of the available options.