Blog Archives

Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.

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

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Formatting Dates as Past or Future

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Monitoring Changes in Data With Conditional Formatting

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,