Share via

How to create a formula with conditional formatting

Ray Soto 20 Reputation points
2025-08-13T01:28:17.35+00:00

I use Excel to track my investment portfolio. I have created a spreadsheet that lists the investments & I track the individual investment values monthly. I want to use conditional formatting to visually represent whether a particular investment has gained or loss value based on the previous month's values. As I input a particular value into the monthly spreadsheet, I want to see visually whether or not the investment has increased, or decreased in value, based on the previous month's value. At present, I am doing this manually. When I enter the current month's value in the spreadsheet, I use conditional formatting to compare the individual investment value (greater than, or less than) to the previous month's value. It's tedious to do this for the various investments. Is there a way to create a formula that when a value is input, the formula looks back one month to the previous month's value & provides the conditional formatting rule (greater than or less than) automatically?

Windows for business | Windows 365 Business
0 comments
Answer accepted by question author
  1. Kimberly Olaño 23,545 Reputation points
    2025-08-13T02:56:20.59+00:00

    Yes that's right, however for the conditional formatting, you can use the format painter button under Home tab to copy the formatting to a different cell.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kimberly Olaño 23,545 Reputation points
    2025-08-13T02:41:08.8833333+00:00

    If you put $ in front of part of the reference, you “lock” it. For example:

    • $B2 would always use column B but move down rows.
    • B$2 would always use row 2 but move across columns.
    • $B$2 would be fixed on exactly that one cell for all comparisons.

    Once you're done locking the reference, you can drag the formula or copy it to the new cell, the conditional formatting will also be copied.

    1 person found this answer helpful.
  2. Ray Soto 20 Reputation points
    2025-08-13T02:31:22.0233333+00:00

    Thanks for the reply Kimberly. I have a question though. Your explanation of how the formula characters work is very clear & I understood all of it. Except: How is the formula copied across all the other cells in the spreadsheet? In your example I can clearly see that cell B2 will have the conditional formatting, but what about all the other cells (C2, D2, E5)?

    0 comments
  3. Kimberly Olaño 23,545 Reputation points
    2025-08-13T02:04:48.72+00:00

    Hi Ray, this is Kimberly. I'm glad to assist you today.

    Yes, you can absolutely automate that in Excel with one single conditional formatting formula per rule, so it automatically compares the current month’s value to the previous month without you having to set it up cell-by-cell. Here are the complete steps:

    Step 1 — Select your data

    Select all the cells where you enter monthly values (e.g., B2:E10).

    We’ll create two separate rules:

    One for gain (green fill)

    One for loss (red fill)

    Step 2 — Formula for Gain (green)

    While your data range is still selected:

    Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.

    Enter this formula:

    =AND(B2<>"",B2>OFFSET(B2,0,-1))

    Explanation:

    B2<>"" ensures we only apply formatting if there’s a value.

    OFFSET(B2,0,-1) gets the value in the cell one column to the left (previous month).

    B2>OFFSET(...) checks if the current value is higher.

    Click Format, choose a green fill, and hit OK.

    Step 3 — Formula for Loss (red)

    Repeat the process for a second rule, but with:

    =AND(B2<>"",B2<OFFSET(B2,0,-1))

    This will highlight cells where the value is lower than the previous month.

    Step 4 — Important Note

    The first month column (e.g., Jan) has no previous month, so OFFSET will point to your “Investment” column — which is text, so the comparison will simply fail (no formatting).

    You can extend this approach across your entire table at once — Excel will automatically adjust the formula for each cell in the selection.

    Step 5 — Optional for Equal Values

    If you also want a rule for no change (maybe yellow fill):

    =AND(B2<>"",B2=OFFSET(B2,0,-1))

    Let me know how it would go, thanks.

    Best regards,

    Kimberly

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.