Excel – Visualize increase/decrease of value based on cell above

microsoft excel

I'm working on a very big data set of visitor numbers to a location. I wish to find a way to mark increase or decrease either by arrows or color to quickly analyse the entire data set. Usually I would be using graphs, but there are to many factors now so the graph is unreadable.

The result in this video is a good example of the result I want: https://www.youtube.com/watch?v=SFU5ZlHUdH0

The problem with that method is that my numbers are in total values, with no + or – sign used, and manually adding – isn't really an option in a set this big.

Is there any way to make color changes in a cell based on the cell above when to total value is lower or higher?

Best Answer

You can use conditional formatting using a formula. You apply the formatting (color etc.) based on the difference between the two cells. Say, you had data in cells A1:A10 you would -

  1. Highlight A2:A10 (start in A2)
  2. Go to Home
  3. Go to Conditional Formatting
  4. Go to New Rule
  5. Use a formula to determine which cells to format. In the formula bar. Enter =(A2-A1)<0.
  6. Click Format and decide what type of formatting you'd like (change font, fill etc.).
  7. Click Ok.

The result should be that each cell is formatted IF the current cell decreases relative to the preceding cell.

Related Question