Copying conditional formatting / Changing absolute to relative

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!