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: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Negative Time

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.

Read more ›

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

Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List

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,

Read more ›

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

Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

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),

Read more ›

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

Tom’s Tutorials for Excel: Dynamic Conditional Formatting By Group

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.

Read more ›

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

Tom’s Tutorials for Excel: Identify unique and duplicate entries

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…

Read more ›

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