In this tutorial: Excel Conditional Formatting Based on Another Cell, you will learn using two video tutorials how to format one or more cells based on another cell.
You might want to look through the other posts related to Excel Conditional Formatting:
- Excel Conditional Formatting Formula
- Excel Conditional Formatting Dates
- Excel Alternating Row Color with Conditional Formatting
You might even go with Chris Newman’s blog post on Hiding A To-Date Column When Reporting Month or Quarter End Results on his blog TheSpreadsheetGuru.
Table of Contents
- Excel Conditional Formatting Based on Another Cell – Highlighting Rows
- Excel Conditional Formatting Based on Another Cell – Show Total Row
- Download Exercise Files, Video Summary & Practice Problems
- Video Transcripts
- Read More…
Excel Conditional Formatting Based on Another Cell – Highlighting Rows
In this video lecture:
- You will learn how to highlight a whole row using formula based conditional formatting.
- And you will work with Absolute, Relative and Mixed Cell Referencing again in this video.
Excel Conditional Formatting Based on Another Cell – Show Total Row
In this video lecture:
- You will learn how to hide the data of a cell based on the data of another cell. To do this, you will use formula based conditional formatting.
- You will use COUNT() function to build the formula.
Download Exercise Files, Video Summary & Practice Problems
Transcript of: Excel Conditional Formatting Based on Another Cell – Highlighting Rows
In this video tutorial, we shall learn how to highlight a whole row using formula based conditional formatting. You see in the worksheet, there are some names here, and there are some values relevant to the names. The row that have the name George is highlighted. This is done using a formula based conditional formatting. Without conditional formatting rule, you cannot expect to perform this type of job. In the next worksheet, I copied this same data. Let’s see how we can highlight this George row using conditional formatting. To understand this example, you have to know how relative reference works. If you don’t know, just search the topic in the “Search Topic Dashboard”, find out the video number and watch the video. OK, I click on Alice. Then click on Conditional Formatting drop down, New Rule, New Formatting Rule dialog box appears.
I click on the last rule type, “Use a formula to determine which cells to format”. In the formula field I type an equal sign, then input cell reference A3, again equal sign, type cell reference B1. The formula is not complete. Before completing the formula I want to give you a brain storm. When I will copy the formula for this cell to the next what will be the formula? The formula will be B3 equal to C1. But in all cases, B1 must be fixed, so I make it an absolute reference. Just click on cell reference B1 and press F4, B1 has now been an absolute reference. To highlight the whole row, we have to make cell reference A3 as dollar sign A and 3.
Now If I copy this formula to the next cell, the formula will be: A3=B1. For the next cell: A3 equal to B1, for the next cell A3 equal to B1 and so on. For the next row the formula will be: A4 equal to B1, for the next cell in the same row, the formula will be: A4 equal to B1, for the next cell, same row, A4 equal to B1, and so on. In this way, when the formula appears in this cell, cell A9, the formula becomes: A9 equal to B1 and this is true. A9 is George and B1 is also George. For this whole row, the formula becomes same: A9 equal to B1, so it highlights the whole row. Let’s see whether this formula work or not. I click on the Format button, as fill color I select this light green color. I click OK and OK. Now I copy the format and select the whole range, you see the George row is highlighted. This is an extra ordinary way to highlight a row using conditional formatting.
Transcript of: Excel Conditional Formatting Based on Another Cell – Show Total Row
In this video tutorial, we shall learn how you can hide the total row when all the data are not entered in a specific range. You see some data in this worksheet and the total at the last row. When I delete any data row from the range, Say I delete the 4500 value, you see the Total row is vanished. Actually the Total row is not vanished, click on the cell where the Total row was: you see the Total is showing in the Formula bar, but the cell is showing blank. I just made the Font Color of the Total row to White. SO it is not showing.
I click on Font Dialog box launcher, and you see the Font color is white. If I make it to Automatic and click OK, you see the Total text and the value are showing now in the cells. So it is all about Formatting. I undo my last action. I enter again a value as the Qtr-2 value, for now I input value 2500, press return; you see now the “Total Row appears”. I have copied this same data in the Sheet2 worksheet. You can change the font color of any cell using conditional formatting. Let’s do it. I select these two cells.
Conditional formatting, New Rule option from the list, New Formatting Rule dialog box appears. I click on the Last Rule Type. In the formula field, I type this formula. The formula starts with an equal sign, count, opening parenthesis, C2, colon, C5, I select the range; press F4 in the keyboard, the range becomes an absolute cell reference, closing parenthesis, and equal to 4. I click on the Format button, as Font color, I select Automatic, as Fill color I select this orange color. Click OK. So how this formula works? You know about count function, it counts how many cells have number values in a range. Right now cell C2 to C5 range only have 3 numbers, one is blank. So count function will return 3. “3” is not equal to 4. So this formatting will not work. When you will enter a number value, the count function will return 4, 4 equal to 4, it’s true, so the total row will be visible with this formatting. I click OK. The total row is not visible. I enter a value in QTR-2, 4500; you see the total row is now visible with the total of the four cells. So this is how you can hide a row using conditional formatting.