Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.
The Conditional formatting rule for unique entries in range A3:A14 is
=COUNTIF($A$3:$A$14,A3)=1
The Conditional formatting rule for non-unique entries in range B3:B14 is
=COUNTIF($B$3:$B$14,B3)1
The Conditional formatting rule for repeated entries in range C3:C14 is
=COUNTIF($C$3:C3,C3)1
…
Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges
Here is how you can verify if a matching value is found in both a horizontal and vertical range. In Picture #1 a match is not found, but in Picture #2 a match is found.
The formula in cell A1 is
=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0=1,”Found”,”Not found”)
Conditional formatting is applied to cell A1 for two conditions.
…
Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times
Here is how you can use Conditional Formatting to identify times that are more than, or less than, a margin of acceptability. In the picture, the cells holding airline flight arrival times that occur more than 60 seconds earlier than their scheduled arrival time are shaded yellow.
…
Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List
Here’s how you can utilize Conditional Formatting to identify the highest or lowest numbers in a list. Despite the literal title of this lesson, you can highlight the highest or lowest 3,
…
Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells
There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data.
…
Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date
In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.
…
Tom’s Tutorials For Excel: Formatting Dates as Past or Future
Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture,
…
Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting
Here’s how you can automatically show a checkmark in a cell, using standard formatting for a font type, and Conditional Formatting to identify when the checkmark exists. There is no programming code involved; it’s all native Excel formatting.
…
Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.
With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.
Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.
…
Tom’s Tutorials For Excel: Monitoring Changes in Data With Conditional Formatting
Without any programming involved, you can monitor a workbook for cells that were changed from their original values.
In the first set of pictures, range B5:B8 is being monitored by Conditional Formatting in hidden columns D:E.
…
Tom’s Tutorials For Excel: Calculating Negative Time
Here’s an example for calculating the difference between times, and expressing a negative result when a larger time is subtracted from a smaller time.
In the picture, runners are listed for comparing their 1000 meter run times against the record time.
…
Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List
When you want to highlight more than just the single lowest (minimum) or single highest (maximum) value in a list, here’s how.
In the above picture,
…
Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences
Here’s how you can format differences in a long list when comparing it to a shorter list.
In the picture, column A holds a comprehensive list of names, and column B contains a shorter list where some names are found in column A (colored green),
…
Tom’s Tutorials for Excel: Dynamic Conditional Formatting By Group
To improve the readability of your lists, you can conditionally format alternating groups of primary items. The challenge is to make this happen when the count of each client name varies in the list, which can change at any time.
…
Tom’s Tutorials for Excel: Identify unique and duplicate items in a list.
Today we have the second of Tom Urtis’ super tips to include screen shots. Enjoy!
In the example, Conditional Formatting is applied to range A2:A12.
Select range A2:A12 and then…
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
