Forum Discussion
Conditional Formatting with two columns and rows of data to 500 x 500 date set
Hello Everyone,
I`m trying to create a conditional formatting excel where
1) Two columns
i) Being the lower end of a value <- 1st Column
ii) Being higher end of a value<- 2nd Column
If the values in the 500 x 500 data set vary of these two particular row elements of the column then that row should be highlighted with red color.
The values in the two columns are changing row to row.
Let me know if there is more explanation required.
Thanks
Dev
6 Replies
Not everything is clear, at least for me. Looks like
- we have the range with 500 rows and 500 columns size;
- somewhere outside this range (aka data) there is another range with 500 rows and 2 columns size;
- In columns of the later are defined bounds for the correspondent rows of the data range, min in first column and max in second one
- all values in data range which are out of this bounds for the correspondent row shall be highlighted by red color
- Hello Sergei,
You got this correct.!
In columns of the later are defined bounds for the correspondent rows of the data range, min in first column and max in second one
- all values in data range which are out of this bounds for the correspondent row shall be highlighted by red color.
This is exactly what I`m trying to do where as the bounding values for each row are varying.That is close to what Ramiz_Assaf suggested. Assuming SI is column with down bound and SJ is with upper one, you may use the rule with formula
=(A1<$SI1)+(A1>$SJ1)
There are several methods, few I would like to show here:
How it works:
- Select Q2:R8, and hit Conditional Formatting
- Find Top & Bottom Rule, from menu hit More Rules.
- Select Format only Top or Bottom ranked values.
- Select TOP then 1 & Bottom then 1 and apply an appropriate format, finish with Ok.
N.B. For Applied to the range, for the TOP 1 the range must be, R2:R8. and for Bottom 1 the range must Q2:Q8.
Another is the dynamic way:
- In any blank cells find Maximum & Minimum from both columns.
- Use these as rules:
=$U2=$X$2 and =$V2=$X$1
- You may adjust cell references in the formula.
- Hello Rajesh,
In the provided example of the Excel sheet. The first two columns would be deciding factor for the rest of the dataset. I would want to highlight all the row items which wouldn't fall under this two row items of the columns.
