Colour a Row in Excel Based on One Cell's Value
In Excel, follow these steps to check the value in one cell, and colour all other cells in that row, if the value is above a specified amount.
Watch this video to see how to colour a row in a table, by using Excel's conditional formatting. A formula checks one cell in each row.
To get the Excel workbook, go to my Contextures website:
http://www.contextures.com/xlcondform...
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra's weekly Excel tips: http://www.contextures.com/signup01
More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html
Subscribe to Contextures YouTube: https://www.youtube.com/user/contextu...
#ContexturesExcelTips
VIDEO TRANSCRIPT
With Excel's conditional formatting, you can easily highlight a cell if it's over or under a certain value, or if it meets a value that you've set.
But in some cases, instead of just a single cell, you might like to highlight a whole row in a table, if one of the cells in that row is over a certain number or under.
In this case, we would like to highlight each row in this list if the number of units sold is greater than 75.
So to do that, I'm going to select all of the rows, all of the columns in each row. So I've selected from A2 down to D10.
On the Ribbon, on the Home tab, I'll click Conditional Formatting, and none of these preset rules will do exactly what I want. So I'm going down to New Rule, and in here I'll select a formula.
So I'm going to use a formula to determine how to color each row.
When I click that, there's a spot where I can put the formula.
I want to, in each row, look at the value that's in column B. So I'll type =
And we want, from every column, we want to look at column B. So we have to lock that cell. We don't want it to be relative, we want it to be absolute.
So type a $ to lock that in. And then B.
And we want, in this case, the active cell we can see is white, where the other cells are highlighted with blue.
We can see that, in the name box, A2 is showing up. So that's the active cell, so the active row is 2. So I'm going to type 2 here.
We're going to check what's in B2 and see if it's greater than 75. So that's our test.
And if it is greater than 75, we want to format it. So I'll click Format and I'll choose a fill color, maybe a blue color and click OK, and click OK again.
And now, any row where the number of units is greater than 75, all four cells in that row are colored blue.
Смотрите видео Colour a Row in Excel Based on One Cell's Value онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Contextures Inc. 23 Декабрь 2010, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 475,88 раз и оно понравилось 69 людям.