Conditional Formatting across multiple cells based on the same cells of the previous week.

Question

I want to apply conditional formatting in K6-N10 all at once.

I want to make the numbers in each box either green or red based on whether it is greater than or less than the same cell of the previous week.

I can do conditional formatting for each cell, one at a time, but wanting this same format to be applied to all the cells in Week 2 based on Week 1, Week 3 based on Week 2, Week 4 based on Week 3 and Week 5 based on Week 4 will take so much longer if I have to do them all one cell at a time.

Is there a way to apply this conditional formatting to cells K6-N10 in Week 2 based on the same cell in Week 1 without having to do them all one at a time?

I appreciate any kind of help on this.

Answer 1

If you have not altered the sheet names (they have the default values sheet1, sheet2, ...) and if your weekly sheets are ordered, then you can use the SHEET and INDIRECT functions to reference the sheet for the previous week.

For example, if the current week is sheet8, then the formula

=INDIRECT("Sheet"&SHEET()-1&"!C10")

will access cell C10 on the previous week's sheet (7).

Answer 2

Hi, Pennsylvania Kev

You can do this a bit more safely by using OFFSET, so the rule always looks back to the matching cell in the previous week block, instead of manually pointing each cell to another cell.

For example, if Week 2 starts in K6:N10 and the matching Week 1 cells are 5 columns to the left, select the whole Week 2 area K6:N10, then create two conditional formatting rules.

For green, use:

=K6>OFFSET(K6,0,-5)

For red, use:

=K6<OFFSET(K6,0,-5)

Set the “Applies to” box to:

=$K$6:$N$10

The important part is that the first cell in the selected range is K6, so the formula should also start with K6. Excel will then adjust the rule for each cell inside the selected block.

If the previous week is 4 columns to the left instead of 5, change -5 to -4.

For Week 3, Week 4, and Week 5, it is suggested that you copy the Week 2 block and use Paste Special > Formats, or use Format Painter. Then check Conditional Formatting > Manage Rules and make sure the “Applies to” range moved to the new week block. This avoids creating every cell one at a time.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 3

Use one conditional formatting rule with a formula and apply it to the whole block.

Assume:

• Week 1 values are in columns F–I (one week to the left of K–N), and
• Week 2–5 values are in columns K–N.

Adjust the column reference to match where the “previous week” actually is in the sheet.

1. Select the range that needs coloring, for example K6:N10.
2. On Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. For “greater than previous week” (green), enter a formula that compares the active cell to the cell one week to the left, using relative references. For example, if the active cell in the selection is K6 and the previous week is in J6, use:

      =K6>J6
   

   This uses relative references so Excel adjusts the comparison for every cell in K6:N10.
5. Click Format, choose a green font or fill, and click OK.
6. Repeat steps 2–5 for “less than previous week” (red) with:

      =K6<J6
   

   and choose a red format.

Because relative references are used, the same two rules automatically apply to all cells in K6:N10: each cell is compared to the corresponding cell in the previous week’s column, and formatted green or red accordingly. There is no need to create rules one cell at a time.

If the previous week is not exactly one column to the left, adjust the reference in the formula (for example, =K6>H6 if Week 1 is two columns left of Week 2).

---

References:

• Use conditional formatting to highlight information in Excel
• Use a formula to apply conditional formatting in Excel for Mac
• Apply color to alternate rows or columns