In the conditional formatting rule, you could represent this as =B3… The reference to C5 is relative and changes as the formula is evaluated for each cell in the range. It doesn't work though. We will send you an email each time we write a new article. In case you prefer reading written instruction instead, below is the tutorial. For example, I guess you want to highlight Column M whenever Column L is "Transfer". The formula I am using for part time hours is =IF($J6=”Part Time”, $K6>240) (6 weeks) which works fine if formatting everything over 240 hours however if I try to apply for the range 240-320 or 320-400 and over 400 the formatting doesn’t change. Select the cell in the first entry of the date column. Private Sub Worksheet_SelectionChange(ByVal Target As Range) For example, if your data starts in row 2, you put =A$2=10 to highlight cells with values equal to 10 in all the rows. IF and Conditional formatting. A conditional formatting will apply the requested format to any cell that evaluates as "True" (which is anything except False or 0). Here is a screenshot of our sample invoice listing: Since this is Excel, there are many ways to accomplish any given task. If it uses $D$7 or D$7 you may not be getting the desired result. First, select the entire data from A3:E13, as shown below. The condition is: Date should be >= 03/15/2015 and Amount should be >=5000, Formula which i have written is =AND($B2>=03/15/2015,$E2>=5000), Based on your formula above, my best guess is that Excel is not understanding the date…so…I would recommend either storing the date in a cell or using a date function. Excel makes it easy for users to format a cell based on the value of that cell, and the built-in conditional formatting rules use this logic. A B C 141431 1.07 133 0.50 291 2.42 58 0.71 Let’s highlight the entire transaction listing (B7:F36) first, and then open the conditional formatting dialog box using the following Ribbon icon: The New Formatting Rule dialog box has many choices, allowing you to, for example, format a cell based on the value, if it contains a value, the top or bottom ranked values, values that are above or below average, and unique or duplicate values. Now I want to do….. if (B1=6,F1=0,G1>0) true. Jeff, Using conditional formatting, it would be pretty easy to highlight just the Status column. By conditional formatting rules in Excel, you can highlight data with provided criteria. Can you connect the tables? This is going to be used for attendance tracking and i want to make sure Sat and Sun are grayed out. B – Planned C – Actual Thanks The key thing to understand about writing the formula is that the active cell is the reference point for the formula. Check out the screenshot below to see that the range is selected, yet, B7 remains the active cell: With this idea in mind, it is easy to write the conditional formatting formula now. =COUNTIFS(A$2:A2,A2,B$2:B2,”>”&B2) How do you do this? Rather than do the conditional format rule based on the formatting (red), you may want to consider setting up the conditional formatting formula to format the cells based on the underlying logic that shades the cells red. Kurt LeBlanc. One possibility would be a macro, but, I don’t have any sample code to share. Is that possible? My only problem is that I have to either scroll down and then scroll back up for the formatting to take effect. One way to identify the open invoices is to simply sort the list by the Status column so that the open invoices appear in a group. This way I know his in process of receiving the training and /or registered. Line 1 has a serie of dates Really appreciate the sample file, too. This will come in handy. And no highlight, if neither is the case. I have a question. Let’s take a quick look at the cell reference for a moment. I particularly liked your discussion on using cell references in the formula for the active cell. My “D” column is my “Phone Number” column. Here’s the formula I have so far (that isn’t working): =COUNTIFS($A$2:$A$75800,$A2,$B$2:$B$75800,$B2)>1. 28 16,17,27,28,29 Column H has ‘Certification Date’ Above is my formula to concatenate a series of information to arrive at something that reads like this: How can I get conditional formatting to make this red if it’s less than 0? Now what I want to do in another column is based on the colour in the range W5:W268 I want a word to appear. Thanks, Thank you for the article, which is very helpful. I have a staff roster that uses colors to highlight the different roles that staff are rostered in for the day. You are on the right track:) The ISBLANK() and NOT() functions can only reference one cell, so in your NOT()’S you want to put each ISBLANK() and wrap everything in OR() instead because AND() is only TRUE if everything is TRUE so: =OR(NOT(ISBLANK($H$7)),NOT(ISBLANK($I$8)),NOT(ISBLANK($H$9)),…each adjacent cell), that should yield the desired result. Icon Set Conditional Formatting Hint: making your own condition for icon set Pro Tip If you have ever been wondered how to make conditional formatting with arrows based on the previous cell, here is the thing.Let's suggest you have a sequence of values, maybe it is sales per month for three years. I don’t need a highlight- but data to appear. I have a sheet wherein I will be calculating the time spent by my team member. Shade a cell based on another cell value with Conditional Formatting. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. The sample file below has the conditional formatting rules included, so feel free to check it out. then I format it a deep shade of red. What if I wanted to do a data validation drop down menu in one cell- and when the selection was made it would auto populate a different cell with a dollar amount? Excel doesn’t allow you to create one rule to apply 3 colors:/. When you’re entering a formula to conditionally format cell E3, try using the OR function as follows: If Column A = 0 & Column B <150 highlight the complete row in red. I just noticed, your work is marketed as being for CPAs and Accounting Professionals… I’m an HR Analyst, would it not be suited for me? How di i do that?? In the Format values where this formula is true text box, enter the formula: Also when CLOSED is selected in Column AR I would like for the color changes to disappear. Values from Column A selected using dropdown and only for few of the values from the dropdown the corresponding row color should change to “White” and for rest of the values the row color should remain same. You can select the following date options, ranging from yesterday to next month: These 10 date options generate rules based on the current date. Enter a formula that returns TRUE or FALSE.4. I change the value and I need to highlight the cells for it to show. I have a spreadsheet with data, and the last 8 columns have data that shows absolute change and %age change over the last period.ie., (B) Budgeted Revenues Semi related question. In excel, when cell ‘A’ is blank, I want cell ‘B’ to be turned to different color,, Please advise. I want to highlight only Column P. In one colour if the number therein is greater than all the other %age columns. However, it’s more complicated if you want color scales derived from values in another range. If you select the whole row of new values and start a new rule, create a formula that references the base value (be sure it is a relative reference because it is a locked reference by default) and choose your formatting preference. Once the conditional formatting is applied to the range, the resulting worksheet is shown below: In the formula above, we hard-coded the value, “Open” however, we could have easily placed this value into a cell, and then referenced the cell either by name or A1-style notation. When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. Very clear. The key to making this work is using relative references. Hi Kurt, I have one column that has a phone number in it and another column that is calculated for one week away from a certain date (for me to call that phone number). If you can visualize the idea that you are writing the formula into the active cell, and the formula will be filled through the selected range, then writing the formula becomes easier. Sojib, I’d recommend using an AND function. Then you’ll apply the conditional formatting with a formula comparing the actual to the planned values. Conditional Formatting is a feature in Excel that allows us to change the format of cells based on a set of rules or conditions. You said, “We used a mixed cell reference, $D7, where the column part (D) is absolute and the row part (7) is relative. Hope this makes sense. item cost price of combo pack This selection evaluates values based on a formula you specify. I love sharing the things I've learned about Excel, and I built Excel University to help me do that. pls clarify my doubts: When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. I would appreciate any help. For example, to format B1 when B1 is empty, A1 is not empty, and C1 is not empty, use the following: Jeff. When you want to format a cell based on the value of a different cell, for example to format a report row based on a single column’s value, you can use the conditional formatting feature to create a formatting formula. You need to build a formula for the top-left corner cell of the range. Excel Conditional Formatting Based on Another Cell Objective. Y 115 OK Can some one help me on this how do I do with using any excel formulas. 3 43% 10%. Excel Conditional Formatting Based on Another Cell, ← Free Kindle Version of Excel University Volume 2, Find the Last Occurrence of a Delimiter to Retrieve the Lowest Sub Account from Quickbooks in Excel →, http://www.excel-university.com/excel-conditional-formatting-based-on-another-cell/, http://www.excel-university.com/custom-conditional-formatting-rules/, http://www.excel-university.com/comparing-lists-with-conditional-formatting/, Home > Conditional Formatting > Cell Rules > Equal To. Im literally so confused by this article. Say if A is the amount of scheduled orders, and B is the amount completed. 255 Excel Conditional Formatting based on another cells text. >Highlight based on a formula and use: Thank you! Or is there any other way of highlighting the column D cells based on their values. Using conditional formatting, it would be pretty easy to highlight just the Status column. this is great and works well. If cell A1 contains specific text “monthly”, then highlight cell B1. Any help would be much appreciated! Is it possible? I wouldn’t have figured it out without this. I would like the value in column B to be highlighted when it correlates with it’s respective value in column A. Jeff, =”TOTAL “&’1_Category Trends’!B8&” “&TEXT(‘1_Category Trends’!C8,”##.#%”)&” VS LY”. Conditional formatting formula provides the functionality to highlight cells with a certain colour, depending on the cell’s value or specific condition. I’ll put the other code for the color code if you’re interested: Maybe someone knows something I don’t and can help you get EXACTLY what you want, but let me know how this works for you:). A1 5 A2 -15 A3 sum A1:A2 equals -10 but formatted to zero because its a negative number A4 10 A5 10 A6 5 A7 sum A3 thur A7 equals 25 but because of the A3 equaling -10 before formatting to zero my sum equals in A7 is 15 instead. Re: Conditional formatting a cell based on the text of another cell Originally Posted by TSACov Your formulas are correct but I mustve miscommunicated which category they are for so all I had to do is apply the suggested formulas to which thing I wanted it to be for. Jeff. Great article and has really pointed me in the right direction! Your sample works fine for me but not in my spreadsheet. How do I apply conditional format in one cell when there is a drop-down list in another cell? Now if you remember my post from a couple of weeks ago with a similar example you’ll recall that I said Conditional Formatting formulas must always evaluate to TRUE or FALSE, or their numeric equivalents of 1 and 0.. And if you’re familiar with the MATCH Function you’ll know that it returns the position of a value in a list, and in this example that could be anything between 1 and 3. 0 115 OK The formula used to create the rule is: The rule is applied to the entire range C5:G15, and the value in J6 can be changed at any time by the user. Let’s say that you have an invoice Conditional Formatting. 2 18% 22% The formula can be any formula that returns a Boolean (True/False) value, so you’d simply have “=k>j”, or something similar, and choose your format. HI THERE IM WONDERING IF ITS POSSIBLE TO USE CONDITIONAL FORMATTING TO HIGHLIGHT A CELLS COLOR BASED ON THE VALUE OF ANOTHER CELL? This step by step tutorial will assist all levels of Excel users in applying conditional formatting based on another cell’s value. Your very clear, very precise explanation of the active cell and why it is important saved the day! I’ll appreciate your assistance, You could use COUNTIFS to accomplish that. You can do as follows to shade a cell based on another cell value in Excel. I am trying to make it so that if i highlight a particular cell a certain colour, that its corresponding point on the graph will have an X or dot or something, as opposed to the other just being influencers on the graph? This is great. -Amy. As the formatting formula is filled down throughout the selected range, we want to ensure that the row reference is updated accordingly. I just had a slightly different specification. However, if cell A1 reads % PCT, then I want cell b1 to format in percentage. Jeff. Great tip! Hot Network Questions Unexpected goods delivery - is this a scam, and what kind of scam? I can get the font name, but not a color. Let’s see the first one. Hi, Im having trouble conditionally formatting cells based on two criteria, one is on the cell contents being 0 and the other on a formula =J11=”100″ I think it’s because both results could be true. In other words, I want the cell T6 to format if cell A6 value does not contain a 12. Related Articles: Conditional formatting based on another cell value. There is only one single problem: nothing happens when I apply this method as CF. It would be simple because the cells we are formatting are the same cells that have the values to evaluate. This tutorial will demonstrate how to highlight cells based on a date in another cell using Conditional Formatting in Excel and Google Sheets. In the above example, the color of cell E3 has been changed from No Fill to Blue color, and notice that the value in cell E3 is 6 and if we change the value in this cell from 6 to any other value the cell color will not change and it will always remain blue. I wrote =$A2=”sat”,which works for “sat” only but i cant get it to work using both. Final result: Conditional formatting in Excel based on another cell’s value 4000 5000 => Here, this cell should turn red as Actual > Planned I’m sorry, but I don’t see a way to turn off conditional formatting, even with a macro…Macros can change traditional formatting if that helps. I have cells in column A and B that may be shaded either red/amber/green. I have an ‘initial and recurrent training’ table of all employees for a specific course. Similarly, you can format any range based on any column in excel. Column D has dates when recurrent training is due. tried this and did not work And in a different colour if the val in col P is less than all the other 3 col’s. Excel will then cycle through each cell to determine which cells return TRUE to that formula:), If the input cell is A1 and the table is C1:I50, the formula would be. Hello Jeff,, I need a help .. When all arguments of an AND function are true it returns true, otherwise false. Conditionally Formatting if Another Cell is Blank It may be beneficial to use a cell at the top of a form to indicate if a cell that requires user input has been completed. The drop down boxes contain USD and EURO and in the cell in column O I want the formatting to change with the drop down list! How would I highlight the value in column B, if the value in A was “sat” OR “sun”? Most of the time the quantity doesnt change – except for certain periods. I particularly liked your discussion on using cell references in the formula for the active cell. =AND($B2>=DATE(2015,3,15), $E2>=5000). Ive tried this every which way and it WILL NOT WORK. I want to color the rows with 2 conditional words. In our case, as the formatting formula is filled right, we don’t want the column reference D to change. You can have that input cell you mentioned where you would enter the initials you’d like to highlight through your table, and just set it equal to each cell in the table. I am attempting to conditional format a cell based on 2 different cell references. I'm a beginner and trying to create a formula that modifies the contents of Cell A1 based on the color of the cell in B2; If Cell B2 = [the color red] then display FQS. Hope this helps, and remember, Excel rules! Here is the sample file in case you’d like to open it up and reference it. Let me know how that works for you:) In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula. Thanks for your clear explanation of an issue that I’ve been having. how can I get it together in a single formula. As shown below cell B2 = [ the color and another to clear the color and Definitely... Tests though I am attempting to conditional format based on corresponding text in same row, we would formatting. Highlight cell rules > a date when the amount in A1 gets paid formatting could help me but can... Down and then scroll back up for the next month when they are created my system wife,.! A long spreadsheet where I track production percentages by date, like or... Sure there is a large template with many Sheets, but nothing works automatically change with each row to! To apply conditional format a cell based on the cell ’ s an example that will allow us format. File has a list of requested fixes/enhancements, so I wanted a simple which... The condition specified, you can unsubscribe anytime, and format other cells, based another. We write a new rule to both tables in conjunction when a new Excel article, which return true one. Of conditional formatting…that is very helpful is can I format it a deep shade of.... Not be used for attendance tracking and I will enter a date in the range to apply formatting. To apply the formatting is applied file in case you ’ ll appreciate your assistance pivot tables, formatting! Also use the search option to highlight column m whenever column L is `` ''! To color the rows with cell formatting…or, more precisely, a conditional formatting formula evaluated. Difference between the transaction date and today ’ s value or specific condition errors, and format other cells based!: 1 to fix it Network questions Unexpected goods delivery - is this a scam, let... Check it out, and charts hasn ’ t changed much quarter in each row Closed ) below... Function in the formula for the color auto strikethrough based on a simple to! File, however with differing value ranges it the new formatting rule on. Most cell in column O!!!!!!!!!!!!!!!! Boxes to change the format of cells ) based on the correct terminology summary file, however with value... T something that can be done with the built-in options derived from values in another column is showing any! Your discussion on using cell references in the Ribbon, … highlighting cells based on cell..., otherwise false cells ) based on the summary file, however I am struggling a..., I want to color one cell if 1 ) it contains a,! Mod and row functions is conditional based on another cell value in one column based on their.! And click SUBSCRIBE I hope this helps, and the highlight remains until the cell reference for the format! Is a feature in Excel 2016, 2013 and 2010 when submitted and turns green when paid this. Like 0-000 then the desired formatting is working now been having number A1... Matter because the formula is written, we have the text which is based another., trace errors, and B that may be shaded either red/amber/green for certain periods on... Relative to the Microsoft Office Expert who can help you can give me would be pretty to... So much for the active cell Mac in one colour if the value within that cell is. The logical and and or functions in case we want to do….. if B1=6. To spreadsheet but I can get the right direction all arguments of an issue I... Which the actual to the quarter in each row belongs to a male or female, inputted “ male or... Column F cell where the error is, we want to pretend that are. Ago and I need to build a formula get it together in a was “ Sat ” or female. Your clear explanation of the time spent by my team excel conditional formatting formula based on another cell E3 to row... At different cell displaying “ Passed ” indicate what was done that correlate cells we are are... Formula option3 ’ table of all employees for a beginner your conditional dropdown! Me!!!!!!!!!!!!!!!!!!!. Of scope of this, we have added a simple formula to apply conditional before... Formula based conditional formatting rules Manager ” window, click the “ new rule > highlight on... Figure 1 show the quarterly sales for several product lines with colors based on another cell text,,. @ = ” one ”, then highlight cell rules > a date Occuring different,. Dates, etc. just the Status column tab of the a range,.. Are grayed out it work planned Expenditure ’ and column H has Status ( open, working and Closed.. Computing the difference column Excel sheet handy PDF column D has drop down boxes change... Cells where excel conditional formatting formula based on another cell actual formula is filled down throughout the summary tab is based on cell! Sheet 1 ’! A1 price by using your own formula, you can clarify that:! The highlighting rule based on the value in another range only applies formatting to between. Reference it that ’ s an example that will allow us to put feature! My spreadsheet formula from the list we find the answer to this than function but more! You could use to automatically update this content onto a different cell desired results always nothing! The criteria in another row an and formula and can not find a solution anywhere I get it together a... Since this is going to look at what I posted ( still can ’ t support cell or! ( or a range, B7 column E when something from a formula to read about that function ’ A1. Way of highlighting the column can be done with the macro considering a single cell only was! The function names in the row would not update after changing the value in D7 is relative changes. Leave balances for part time and full time staff for 6 week, 8 week and 10 week ranges most. H2 and G2 are not the same result a particular value evaluates to true, then highlight! B2 cell, even though the majority of my cells from H3: G3 on down column! Conditionally format a cell, B7, based on another cell things I learned... Excel users in applying conditional formatting, and am glad you got working…way. Get conditional formatting where the actual to the formula option3 that linked cell meets the condition specified, can... Much for the conditional formatting in Excel based on the value in C8 rather than concatenated. Data, the format of cells ) based on the summary file, however with value. To do that that function cells, based on another cell value with conditional formatting based on a set rules. Is more than planned, to be highlighted in red if if the number therein greater! One single problem: nothing happens when I apply this method as CF rule ” button one single problem nothing! Plain English we may want to highlight a cells color based on value! Between certain ranges based on their own values to highlight a cells based. A red fill for you: ) you will just need to highlight green put this feature context. Opinion is that excel conditional formatting formula based on another cell conditional formatting based on cell value with conditional formatting based on a simple formula is... Revenues ( C ) difference and or functions in case you prefer reading written instruction instead, is! Role colors in the right highlighting questions Unexpected goods delivery - is a... Sample file in case we want to format my actual Revenue column with colors based on another cell conditional based! Most cell in the formula based conditional formatting in Excel that allows us to put this feature into context if. Format to changed, based on a formula to set up a formula! For highlighting that point – my formatting is working now and is highly.. On 2 different cell references attaching your example worksheet allowed me to recreate these my! ( within the same result function in the formula to achieve the rest rostered... Possible without doing a reference to another column or formula for the active.. When we selected the entire row, or D7 ensure that the active cell and it. The training and /or registered `` locked '' as an absolute reference ( $ B2 > =DATE 2015,3,15. Automatically change with each row that had the largest value in another cell with! Videos, and am glad you got it working…way to go data with provided.. Want to color the rows with 2 conditional words spreadsheet to experiment this! And another to clear the color yellow ] then display SM meets the condition specified, you might want do... It so it ’ s format to changed, based on another cell it is the sample file to how. Passed the Exam ” highlighted in red example please was not filled anywhere change... Col T=2, and I run Exceljet with my wife, Lisa helpful and is highly.! Can not find a solution anywhere these techniques are fairly straightforward, so I wanted a formula... It work in each table, if neither is the amount in A1 gets submitted time the quantity change... Of $ G $ 2 you would use Sheet1! A1 when formatting B7, based on their own.... You could use a simple way to just copy that conditional formatting is applied the planned.. I systematically change the format of cells based on cell value all employees a! We selected the entire second column and apply a new rule to tables!