Conditional formatting - Get Digital Help

Get Digital Help > Excel > Conditional formatting

Archive for the ‘Conditional formatting’ Category

Track progress
Sankalp asks: I am working in a railway project as planner. How can i create a dynamic strip chart in excel? Assume total length be 10 kilometers and each cell of 100 meters. If I update the progress from 9.01 […]

Learn Excel’s Subtotal feature
You can group rows or columns manually using the "Group" feature. That will tie a range of cells together so they can be collapsed or expanded. First you need to prepare your data, remove blanks, sort the data and insert […]

Highlight unique values in a filtered excel table
Conditional Formatting has some amazing built-in features, for example it lets you highlight unique values in a list without entering a CF formula. If you don´t know how to do this, follow these instructions: Select your list Go to "Home" tab […]

Count unique distinct values with a condition
Stephen asks: Could you help me with (1) the Count the number of unique Divisions where ALL products have been delivered. (2) The Count the number of unique Divisions where NOT ALL products have been delivered. Determined by the presence […]

Counting conditionally formatted cells (vba)
UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you tweak the conditional formatting formula to count conditionally formatted cells. The user defined function in cell D2 counts cells with the […]

Highlight date ranges overlapping selected record (vba)
The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional formatting highlight date ranges overlapping the selected record in the table. Instructions VBA Code Right click on sheet name Click […]

Highlight duplicates in a filtered excel defined table
You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want to find duplicates among filtered visible values. Example, the table below has filtered values from January 2012. Create a new […]

Highlight events in a calendar
Steven asks: I got 6 events with different dates... Event - DATE START - DATE END 1 2 3 4 5 6 On sheet 2 i have a Year Calander (365 Days)I need to do a conditional formatting to highlight […]

Highlight events overlapping federal holidays
Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have a schedule table with event in 1st column, start date in 2nd column, end date in 3rd column, and a […]

Use a drop down list to change conditional formatting
You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is how I did it. Create a drop down list Select cell D3 Go to tab "Data" Click "Data Validation" button […]

Applying conditional formatting to related tables
It can be really hard trying to follow a lookup in related tables. I will show you how to use conditional formatting for easy identification. If you add more rows to the tables, the conditional formatting expands automatically. Conditional formatting formula applied […]

Excel calendar (vba)
This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day are shown below the calendar. Today´s date is highlighted yellow. Days with one or more events are also highighted. How […]

Shift Schedule
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., […]

Highlight values within specific ranges
Merit asks: I have a large array of numbers and a row of numbers. I want each number in the array to become higlighted one color if it is within 4 of any number in the row and another if […]

Working with formulas in conditional formatting
In this post I am going to try to explain formula basics in conditional formatting. It is really good if you have some knowledge about absolute and relative cell references but it is not necessary. I think the examples shown here explains […]

Search with conditional formatting
I would like to show you how to search a table using conditional formatting. The criteria highlight matching column and rows. I am going to explain how to create the highlighting and the conditional formatting formulas behind. Setting up the […]

Excel vba: Create dynamic conditional formatting
I have noticed when I apply conditional formatting on larger cell ranges, file size increases rapidly. This post demonstrates how to create dynamic conditional formatting. What is dynamic conditional formatting? As new rows or columns are added conditional formatting is […]

How to quickly compare pricelists in excel
Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2010 and year 2011. To make things more interesting, price list 2011 is not sorted. […]

Quickly highlight records containing text strings in excel (AND Logic)
This blog post shows you how to highlight rows where text string criteria are found. Example, continents criterion (cell B3) is only searched in column Continents (B7:B28). Color criterion is searched for in column Color, and so on. Conditional formatting […]

Quickly highlight records in a list in excel (AND logic)
This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change criteria by editing row 3 and 4. Conditional formatting formula: =SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7)))) How […]

Quickly highlight records in a list using multiple criteria in excel
This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in row 3. A row is highlighted if: Date criterion is found in column B or Color criterion is found in column […]

Highlight closest value in excel
This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the second closest value (or more). Conditional formatting formula =OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2), ROW($A$1:INDEX($A:$A, $C$3)))) This is not working in excel 2010 and later […]

Filter and highlight duplicate column records in excel 2007
This article describes how to highlight duplicate records and filter duplicate records. There is one record in each column, cell range B2:E5. See picture below. The array formula and conditional formatting formula in this article contain "Countifs", a function introduced […]

Highlight duplicate rows in excel 2007
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1 + ENTER How to use conditional formatting formula Select cells A1:C30 Click "Home" tab Click […]

Highlight duplicate values and overlapping dates in excel
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it the dates are overlapping as well. Is there anyway to work this out? eg Serial | Start date | End […]

Highlight duplicates on same date, week or month using conditional formatting in excel
Highlight duplicates on same date Conditional formatting formula: =SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1 Highlight duplicates on same week Conditional formatting formula: =SUMPRODUCT(--($B16&"-"&YEAR($C16)&"-"&$D16=$B16:$B$16&"-"&YEAR($C16:$C$16)&"-"&$D16:$D$16))>1 Highlight duplicates on same month Conditional formatting formula: =SUMPRODUCT(--(YEAR($C29)&"-"&MONTH($C29)&"-"&$D29=YEAR($C29:$C$29)&"-"&MONTH($C29:$C$29)&"-"&$D29:$D$29))>1 Download excel sample file for this article. Highlight-duplicates-within-same-date-week-month-year.xls (Excel 97-2003 Workbook *.xls) Functions […]

Highlight odd/even months using conditional formatting in excel 2007
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: =MOD(YEAR($B6),2) Color even years Conditional formatting formula: =NOT(MOD(YEAR($B6),2)) Border between months Conditional formatting formula: =MONTH($F7)<>MONTH($F6) Border between years Conditional formatting […]

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
This blog article is one out of two articles on the same subject. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part […]

How to highlight max and min value in every month in excel
Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value in every month: =B2=MIN(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Named ranges Date_rng (A2:A169) Close (B2:B169) What is named ranges? How to customize the […]

Highlight more than once taken online course in any given day in excel
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any employee is free to take a plethora of different courses and they could take them all on the same day […]

Highlight unique distinct values in two ranges combined using conditional formatting in excel
Conditional formatting formula in B2:E5: =IF(ROW(B2)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(B2)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), B2)+COUNTIF(OFFSET(tbl, MIN(ROW(B2)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(B2))), B2), COUNTIF(OFFSET(tbl, 0, 0, 1, MIN(COLUMN(B2))), B2))=1 Conditional formatting formula in B7:E10: =IF(COUNTIF(tbl, B7)+IF(ROW(B7)>MIN(ROW(tbl1)), COUNTIF(OFFSET(tbl1, 0, 0, MIN(ROW(B7)-MIN(ROW(tbl1))), MAX(COLUMN(tbl1))), B7)+COUNTIF(OFFSET(tbl1, MIN(ROW(B7)-MIN(ROW(tbl1))), 0, 1, MIN(COLUMN(B7))), […]

Highlight duplicate values in two ranges combined using conditional formatting in excel
Question: How do I highlight duplicate values in two ranges combined? Answer: Range 1, A1:D4, named tbl Range 2, A6:D9, named tbl1 Conditional formatting formula in A1:D4: =IF(ROW(A1)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(A1)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), A1)+COUNTIF(OFFSET(tbl, MIN(ROW(A1)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(A1))), A1), COUNTIF(OFFSET(tbl, […]

Highlight smallest duplicate value in a column using conditional formatting in excel
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in A2: =MIN(IF(COUNTIF(List, List)>1, List, ""))=A2 How to implement conditional formatting formula to your workbook in excel 2007 For example, your […]

Highlight duplicate values in a range using conditional formatting in excel
Question: How do I highlight duplicate values in a range (A1:D4) using conditional formatting? Answer: Conditional formatting formula: =IF(ROW(G5)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, ROW(G5)-MIN(ROW(tbl)), MAX(COLUMN(tbl))-MIN(COLUMN(tbl))+1), G5)+COUNTIF(OFFSET(tbl, 0, 0, ROW(G5)-MIN(ROW(tbl))+1, COLUMN(G5)-MIN(COLUMN(tbl))+1), G5), COUNTIF(OFFSET(tbl, 0, 0, 1, COLUMN(G5)-MIN(COLUMN(tbl))+1), G5))>1 Highlight all values having […]

Highlight unique values and unique distinct values in a range using conditional formatting in excel
Highlighting unique distinct values in a range using conditional formatting Repeated values are not highlighted, only unique distinct values. Conditional formatting formula: =IF(ROW(A1)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(A1)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), A1)+COUNTIF(OFFSET(tbl, MIN(ROW(A1)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(A1))), A1), COUNTIF(OFFSET(tbl, 0, 0, 1, MIN(COLUMN(A1))), A1)) […]

Highlight common values in two lists using conditional formatting in excel
Question: How do I highlight duplicates in two lists using conditional formatting? In other words, how do I highlight cell values in List 1 that also exist in List 2? And vice versa? Answer: The conditional formatting formula in A2:A6: […]

Create a dynamic border to your list using excel conditional formatting
Question: I have a list that I keep adding rows to. How do i create a border that expands as the list expands? Answer: Adding more rows to your list expands the border automatically. See picture below. How to create […]

Highlight duplicates using conditional formatting in excel
To the right is a picture of an excel list. (B3:B11) The second or more duplicates are colored and are easily identified. To sort all duplicates to the bottom of the list for removal, creating a unique distinct list. See […]

Highlight dates within a date range using conditional formatting
Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a column Highlight values in a row Highlight records Sort values Highlight values in a column How to apply the conditional […]

How to create a unique list using conditional formatting in excel 2007
You may have tried to create a unique distinct list, using conditional formatting with excel 2007, merging duplicates into one distinct value. The problem is that it only highlights all values occurring only once. See picture. If you highlight all […]

« previous entries

Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.
Don´t forget to add my RSS feed and subscribe to new blog articles by email.
Sign up for free blog updates

Email
Search website
Popular articles

1. How to return multiple values using vlookup

2. How to extract a unique distinct list from a column

3. How to create a dynamic chart (excel 2003 and 2007)

4. Match two criteria and return multiple rows

5. How to create a list of random unique numbers

6. Create dependent drop down lists containing unique distinct values

7. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

8. Concatenate a cell range without vba

9. Auto refresh a pivot table

10. Excel udf: Lookup and return multiple values concatenated into one cell
10,292 subscribers