Conditional formatting based on the previous cell

问题:

I am trying to indicate improvements in the scores with the icon set of arrows. Since I have multiple columns each cell should be indicating incline or decline based only on previous cell.

If I use a normal formatting provided by excel in the number order: 20,30, 40

Excel indicates that decline has been made with number 30 (arrow pointing downwards) however it should be indicated as increase. Can someone please help me?

thank you for your time, review and answer.

回答1:

There is a way to apply a conditional format based on other cells contents. This does not give you icons (as arrows), but colors the cells, for instance. It might be good enough for you.

Conditional Formatting -> New Rule -> Use a formula to determine which cells to format

The figure shows an example of what you may obtain (the icons row is described below).

It is essential that you use relative referencing in the formula. Check this answer.

There are other options which involve creating additional helper cells, e.g., row 4 in the figure. The amount of extra effort, and whether it is ok for you, depends on the size and structure of your worksheet.

For the row of icons, I used something similar to 2 and 3 below. Enter in C4 the formula =IF(C3>B3,3,IF(C3=B3,2,1)) and copy to the right. Then apply the Conditional Formatting -> Icon Sets, and select Show Icon Only.

Check

1. https://superuser.com/questions/591120/excel-conditional-formatting-using-icon-sets
2. Conditional formatting: Showing direction arrows for a series
3. https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/, go to Example 2. Add icons based on another cell's value.

回答2:

If you want a formula that can be copied (as text and a cell, not just copying a cell), which makes it more versatile, you can use this:

=INDIRECT(ADDRESS(ROW()-1, COLUMN()))>INDIRECT(ADDRESS(ROW()-1, COLUMN()-1))

This is to apply the formatting for a rise; change the comparison operator for equal and decrease formatting.

Here's how it works:

• =ADDRESS(ROW(), COLUMN()) refers to the current cell (i.e. the one to be formatted).
• Similarly, =ADDRESS(ROW()-1, COLUMN()) refers to the cell above the current cell while =ADDRESS(ROW()-1, COLUMN()-1) refers to the cell above and to the left of the current cell. These are the two cells you want to compare. Then all I do is compare the two.