![]() The formula you write will not be used to compute the cell value, rather, it will be used only for formatting. 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. You want to write the formatting formula as if you are writing it into the active cell and use the appropriate cell references and reference styles, such as absolute, relative, and mixed. Since this concept is absolutely critical, I don’t want to just skip through it, I want to unpack it for a moment. ![]() The key thing to understand about writing the formula is that the active cell is the reference point for the formula. If the formula returns false, the formatting is not applied. If the formula returns true, then the desired formatting is applied. The formatting formula needs to be set up so that it returns a true or false value. We want to use a formula to determine which cells to format. At the bottom of the list we find the option we need. 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. Home > Conditional Formatting > New Rule.Let’s highlight the entire transaction listing (B7:F36) first, and then open the conditional formatting dialog box using the following Ribbon icon: Fortunately, it is not very difficult to set up such a formatting formula. When we want to format a cell based on the value in a different cell, we’ll need to use a formula to define the conditional formatting rule. Expanding this to the entire row, we want to format B7:F7 based on the value in D7.Įxcel 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. This means that we want to format a cell, B7, based on the value in a different cell, D7. Considering a single cell for a moment, we want to format B7 based on the value in D7. That is, we want to format the TID, Date, Status, CustID, and Amount columns based on the value in the Status column. To highlight the entire transaction row requires us to format a cell based on the value in another cell. While this technique is easy, it does not meet our goal which is to highlight the entire transaction row, not just the Status column. Excel would then apply the formatting to the cells within the Status column that are equal to Open. In the Equal To dialog box, we could enter the word “Open” and pick the desired formatting and click OK. Home > Conditional Formatting > Cell Rules > Equal To.To perform this, we could simply highlight the Status column, and the use the following Ribbon command: That is, we would be formatting a cell based on the value within that cell. It would be simple because the cells we are formatting are the same cells that have the values to evaluate. Using conditional formatting, it would be pretty easy to highlight just the Status column. We’ll highlight the transaction rows with cell formatting…or, more precisely, a conditional formatting formula. These techniques are fairly straightforward, so, let’s explore another method. Another way is to filter the listing to show only the open invoices. 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. Since this is Excel, there are many ways to accomplish any given task. Here is a screenshot of our sample invoice listing: Let’s say that you have an invoice listing and your objective is to identify the open invoices. Here’s an example that will allow us to put this feature into context. This post explores the details of formatting a cell or range based on the value in another cell. 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.
0 Comments
Leave a Reply. |