How to Change Conditional Formatting of a Cell Based on a Change in the Cell Value

Question

I am desperately looking for a way that allows me to conditionally format a cell depending on whether its value has increased, decreased or stayed the same.

However, I don't want to compare its value with the value of other or adjacent cells.

Rather, what I would like to do is that Excel applies an upside arrow in the case that the value of the cell increased, and upside arrow if the value of the cell decreased and an arrow showing to the right if the value stayed the same.

Here is an example to illustrate what I mean:

Let's say the value of a cell is 5 and once this becomes 6 or higher, it should get an upside arrow.

Is there a way to get this done? Thanks!

Solution

Excel does not keep a history of what a cell's value was in the past. Hence it cannot detect whether the current value is an increase or a decrease compared to the previous value of the cell. There is no conditional format and no logic that can look at just the cell to determine if the value has gone up or down.

In order to achieve such formatting, the previous cell value would have to be stored somewhere.

I suggest you take a step back and describe the bigger picture before focusing on a specific technique.

Comments

[Dave]

Can you explain what you've tried please, it's easier for us to see your efforts and help as it means we don't provide suggestions which won't work. As it is, this question is very broad and open to many potentially correct answers :(

[Gaijin565]

Sorry for having expressed myself not clear enough. However, @teylyn already answered my question in saying that Excel doesn't keep a history of values in the past. Thanks!

[Gaijin565]

Thanks. I see your proposal is going into the same direction as the one of teylyn below ...

[Gaijin565]

Thanks a lot. I didn't know that Excel doesn't keep track of values in the past. My mistake ...

[Gaijin565]

Do you know by chance about a formula which may automatically post the value of one cell let's say every day or so to a predefined cell in another sheet? Then, as I understood you, I could take this value as reference for what I want to achieve, right?

[teylyn]

A formula cannot post into another cell. You'd need to use VBA to copy cells or the whole sheet. That can easily be done when the file is opened. If you need help with that, ask another question.

[Gaijin565]

Thanks! Well, VBA is still something I got to learn ... More of a reason to learn it, I guess ...