Highlight cells based on ranges
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel Table.
Column A contains numbers from 0 (zero) to 100 in steps of 0.1. Cells in column B are highlighted if the number is inside a range specified in the Excel Table.
For example, cell A1 contains 0 (zero), the corresponding cell B1 is not highlighted. Value 0 (zero) is not in any of the three ranges specified in cell range D4:E6. They are 0.1-0.8, 1.1-1.3, and 1.4-1.6.
I am working on a railway project as planner. How can I create a dynamic strip chart in Excel?
Assume the total length is 10 kilometers and each cell of 100 meters.
If I update the progress from 9.1 km to 9.8 km in the table, how the cell gets highlighted corresponding to the entered chainages.
Conditional Formatting formula
The worksheet above shows you km in column A and corresponding highlighted cells in column B if they are specified in the Excel Table.
You can easily add or remove values to the Excel Table without the need to adjust the conditional formatting formulas.
Conditional formatting formula applied to cell range B1:B101:
How to create an Excel Table
- Select cell range D3:E6
- Go to tab "Insert" on the ribbon
- Press with left mouse button on the "Table" button
Tip! You can also use the shortcut key CTRL + T to create a table
Excel Tables let you easily organize, filter, and format data on a worksheet. They also let you use structured references that can gro without the need to adjust cell references in a formula.
Learn more about Excel Tables
How to apply a conditional formatting formula to a cell range
- Select cell range B1:B101.
- Go to tab "Home" on the ribbon if you are not already there.
- Press with mouse on the "Conditional Formatting" button to expand a menu.
- Press with left mouse button on "New Rule..".
- Press with left mouse button on "Use a formula to determine which cells to format".
- Type above formula in dialog box formula bar.
- Press with left mouse button on the "Format..." button.
- Go to tab "Fill".
- Pick a fill color.
- Press with left mouse button on the OK button.
- Press with left mouse button on the OK button.
Explaining conditional formatting formula
You can't use structured references in a conditional formatting formula unless you use the INDIRECT function for each reference. This applies to Data Validation Lists as well.
Step 1 - Reference an Excel Table in a Conditional Formatting formula
Excel returns an error message if you try to use a reference to an Excel Table in a Conditional Formatting formula.
There is a workaround, simply use the INDIRECT function to make this possible.
Table1[End]
becomes
INDIRECT("Table1[End]")
Step 2 - Check if current cell value is less than the END range values in the Excel Table
(A1<INDIRECT("Table1[End]")
becomes
0<{0.8;1.3;1.6}
and returns {TRUE;TRUE;TRUE}
Step 3 - Check if current cell value is greater than or equal to the START values in the Excel Table
A1>=INDIRECT("Table1[Start]"
becomes
0>={0.1;1.1;1.5}
and returns {FALSE;FALSE;FALSE}
Step 4 - Multiply arrays
(A1<INDIRECT("Table1[End]"))*(A1>=INDIRECT("Table1[Start]"))
becomes
{TRUE;TRUE;TRUE}*{FALSE;FALSE;FALSE}
and returns {0;0;0}
Multiplying boolean values creates integers.
Step 5 - Sum array
SUMPRODUCT((A1<INDIRECT("Table1[End]"))*(A1>=INDIRECT("Table1[Start]")))
becomes
SUMPRODUCT( {0;0;0})
and returns 0.
0 means False so cell B1 is not formatted gray.
Cf misc category
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22Â based on row and column values in column B […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
Conditional formatting category
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Functions in this article
More than 1300 Excel formulas
Conditional Formatting categories
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.