How to shade a cell based on another cell value in Excel?
Supposing you want to shade a cell (says A1) with a certain background color if cell B4 meets the criteria such as has a certain value or the value is greater or less than a specific number. What would you do? This article will show you the method.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
You can do as follows to shade a cell based on another cell value in Excel.
1. Select the cell (says A1) you want to shade based on another cell value, then click Conditional Formatting > New Rule under Home tab.
2. In the New Formatting Rule dialog box, you need to:
2.1 Select Use a formula to determine which cells to format option in the Select a Rule Type box;
2.2 If you want to shade cell A1 when value of B4 is greater than 500, then enter formula =$B4>500 into the Format values where this formula is true box;
And if you want to shade cell A1 when B4 has a certain value such as “test”, the use this formula =$B4=“test”. You can change the variable in the formula based on your needs.
2.3 Click the Format button to specify a background color;
2.4 Click the OK button when it returns to the New Formatting Rule dialog box. See screenshot:
Then you can see the specified cell is shaded based on the value of another cell as below screenshot shown.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 8 days agowhat if I want a cell to be shaded based on the presence or absence of text in a different cell?
To post as a guest, your comment is unpublished.· 8 days agoHI what if I want to condition a cell based on the presence or not of text in a different cell?
To post as a guest, your comment is unpublished.· 5 months agoTo apply this to multiple rows, use the same formula (Excel automatically continues the formula with relative values).
e.g. for the following sheet,
1| 0 0
2| 0 1
3| 1 0
4| 1 1
to make A1 depend on B1, and A2 depend on B2, and so forth, use =$B1=0. To make all the cells in A depend on one cell in B, use =$B$1=0, and to make the cells in A depend on the cells in the next row of B, use =$B2=0 (with the A cells selected from A1).
To post as a guest, your comment is unpublished.· 5 months agoHo do I apply this rule by row
For example, I want Cell I2 to be formatted by G2. I3 by G3 and so on
To post as a guest, your comment is unpublished.· 8 months agoHow can we apply the rule on multiple cells with a single formula? Applying the rule on every single cell will be a hectic job.
To post as a guest, your comment is unpublished.· 11 months agoHow might i format column N to be shaded according to value in column L? (Eg higher red, lower blue, equals green, no value - no shading)
Can i write a formula for column N in conditional formatting?
K L M N
DD 0.2 0.8
AM 0.8 1.0
TB 1.0 1.0
AM 1.0 1.0
TB 0.6 0.8
AM 0.4 0.4
AM 0.8 0.8
Any help appreciated
To post as a guest, your comment is unpublished.· 11 months agoHi Deb,
Sorry can't help you with that. Thank you for your comment.
To post as a guest, your comment is unpublished.