Copying conditional formatting / Changing absolute to relative

[nancynancy]

Hello - I am trying to copy a conditional format down through multiple rows but cannot get my conditional formula to change to relative, it keeps reverting back to absolute.  Is this something not possible? Below is the screen, when I change and save, it reverts back to absolute.

Forgot to mention one more thing, this is across a pivot table

[#2]

There is no formula in your screen-shot that has any relative references. What are you trying to achieve precisely?

[#3]

@nancynancy 

While copying conditional formatting across multiple rows in a pivot table can be tricky due to the dynamic nature of pivot tables, there are workarounds to achieve your desired results. Here are two approaches:

Method 1: Using Formula Auditing:

1. Select the cell(s) with the conditional formatting you want to copy.
2. Go to the Formulas tab and click Formula Auditing > Show Formulas.
3. The formula will be displayed in the formula bar.
4. Make sure the formula uses relative cell references (e.g., A2-B2 instead of $A$2-$B$2). If it's absolute, manually change it to be relative.
5. Click anywhere outside the formula bar to apply the changes.
6. Copy the cells with the adjusted formula.
7. Paste the copied cells into the target rows in your pivot table.

Method 2: Using Conditional Formatting Rules:

1. Select the cell(s) with the conditional formatting you want to copy.
2. Go to the Home tab and click Conditional Formatting.
3. Choose the appropriate formatting rule type (e.g., "Highlight Cells Rules" or "New Rule").
4. In the rule dialog box, pay attention to the formatting formula. Ensure it uses relative references.
5. Click Format to choose your desired formatting.
6. Click OK to close the rule dialog box.
7. Select the target rows in your pivot table.
8. Click Conditional Formatting again and choose the same rule type you used in step 3.
9. Instead of creating a new rule, select the existing rule you just edited.
10. Click OK to apply the rule to the new cells.

Remember that pivot tables automatically adjust references when you add or remove data, so it's good practice to use relative references in your conditional formatting formulas.

Additional Tips:

• If you're working with a large pivot table, consider using the Format Painter (Home tab > Paintbrush icon) to copy the formatting instead of individual formulas. This can be faster and less error-prone.
• Make sure your pivot table settings allow updates to formatting when data changes. Go to PivotTable Analyze > Change Data Source and check the "Refresh data when opening the file" box.

I hope these methods help you successfully copy conditional formatting across your pivot table with relative references!

[#4]

@nancynancy I overlooked the fact that this is in a pivot table. At the top of the C.F. dialog (you posted a screen-shot), don't use the "Selected cells" choice, but select either of the two subsequent options: "All cells showing...". This will cause Excel to automatically expand and contract the conditional format range as your pivot table grows or shrinks.