Search Contextures Sites
Custom Search

More Excel Videos

Excel Videos - Index

Data Entry Tips

Names, Macros, Numbers

Mouse Shortcuts

 

 

 

Excel Videos - Conditional Formatting

Excel videos show how to use conditional formatting features. Highlight a row, hide duplicates, and more.

Conditional Formatting Basics 

Conditional Formatting -- Highlight a Row 

Conditional Formatting -- Hide Duplicates 

Conditional Formatting -- Refer to Cells on Another Sheet 

Conditional Formatting -- Hide Questions for Data Entry  

Conditional Formatting -- Data Bars

Conditional Formatting Basics

With conditional formatting you can select cells, and set rules for formatting them. With the rules, you can set the cell font, the fill colour and the border settings, based on the value in the selected cell, or the contents of another cell.

There's a limit of 3 conditional formatting rules per cell, in Excel 2003 and earlier versions. If your rules (conditions) are met, then the conditional formatting is applied.

You can add formatting to highlight cells. For example, you can set conditional formatting so a cell is coloured blue if it has a value that's higher than 75. As the second condition, you can make the cell turn green if it contains a value lower than 50.

Watch this video to see the conditional formatting applied to a group of cells. For written instructions for this Excel tutorial, see: Conditional Formatting

Conditional Formatting -- Highlight a Row

You can add conditional formatting to an entire row if one cell is over a set amount. The conditional formatting checks the value in a specific cell in each row, and applies formatting to other cells in the same row, based on that value.

For example, you could colour the entire row in a table, if the values in column B are over a set value. In this video, we'll colour cells blue, if the number of units, in column B, is greater than 75. After the conditional formatting is applied, we'll be able to see at a glance, which dates have a high number of units.

For written instructions for this Excel tutorial, see: Conditional Formatting

Conditional Formatting -- Hide Duplicates

In a table of Excel data, each row should have data entered in every column, to enable sorting and filtering. That might make the the table harder to read, because it's not clear where each group of data starts.

However, you can use Excel conditional formatting to hide the duplicate values, and make the list easier to read. In this video, when the table is sorted by Region, the second (and subsequent) occurrences of each region name will have white font colour.

For written instructions for this Excel tutorial, see: Hide Duplicate Values

Conditional Formatting -- Refer to Cells on Another Sheet

Just like data validation, conditional formatting displays an error message if you try to refer to cells on another worksheet. However, you can name the range on the other sheet, and refer to the named range in the conditional formatting. For some reason, Excel is okay with references to named ranges on another sheet.

For written instructions for this Excel tutorial, see: Conditional Formatting Other Sheet

Conditional Formatting -- Hide Questions for Data Entry

In some Excel files, you could have a series of questions, and some of those questions could have two or more subsequent questions. For example, a user could select Yes or No, in answer to the question, “Do you have any dependents?”

If Yes is selected, the next heading and data entry box appear, "Please list dependent names and ages."

To make it easier for users to work with the file, you can use conditional formatting to hide the subsequent questions, and show them only if the applicable answer is selected in a drop down list.

For written instructions for this Excel tutorial, see: Create Secret Hidden Questions in Excel

Conditional Formatting -- Data Bars

Use Excel conditional formatting data bars to create a mini-chart in the worksheet cells. This gives a quick picture of the data, to make it easier to see the overall trend.

For written instructions for this Excel tutorial, see: Excel Conditional Formatting - Data Bars

More Excel Videos

Excel Videos - Index

Data Entry Tips

Names, Macros, Numbers

Mouse Shortcuts

 

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

   

 

Privacy Policy

 

Contextures Inc., Copyright ©2016
All rights reserved.

 

Last updated: May 31, 2016 7:46 PM