Search Contextures Sites
Excel Tutorial Videos 06
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
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
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
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
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
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
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
Excel Tutorial Videos 01
Excel Tutorial Videos 02
Excel Tutorial Videos 03
Excel Tutorial Videos 04
Excel Tutorial Videos 05
Excel Tutorial Videos 06
Excel Tutorial Videos 07
Excel Tutorial Videos 08
Excel Tutorial Videos 09
Excel Tutorial Videos 10
Excel Tutorial Videos 11
Excel Tutorial Videos 12
Contextures Inc., Copyright ©2012
All rights reserved.
Last updated: July 27, 2011 7:11 PM