Excel 2007 : Copying Conditional Formatting into Multiple Ranges

Good Afternoon,

I am working with a pivot table containing bill invoice data. In the pivot table, each row represents a different line item on the invoice with columns organized by month. I have added 4 rules with conditional formatting to a few rows to highlight any month that is outside of 2 or 3 standard deviations of the mean. My goal is to have these rules set up on all the rows. If I select the entire table, all the data will be used in calculating a standard deviation, so I have to add the rules by row. Unfortunately, I have not been able to figure out how to copy from row to row to save myself time setting up 4 rules over and over.

Any suggestions would greatly be appreciated!

Thanks!

Are your references relative in the conditional formulas?

Perhaps you can copy a row that has the conditions, then select the rows you want to copy to and right-click Paste Special... Formats.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Is the attached what you are looking for?

The way I did it was I selected E8 to E19 and invoked Conditional Formatting through the Home menu and selected New Rule

I selected "format only cells that contain"

And selected Cell Value >> greater than from the drop downs and enter =C8

which represents the cell to compare the top most cell I selected with... (E8).

Note that there are no $ signs so that the reference is totally relative to the vertical and horizontal position it is in.

With Conditional formatting, if you select a range and apply the formula based on topleft most selected cell, it will automatcally adjust the formula for the other cells within the selected range....

So you could have selected all the ranges to affect at once and apply the exact same formula and it would adjust relatively.

But I took an alternative path and after apply the above, I copied the same range, and selected all the other ranges, did a right-click >> Paste Special and selected Formats.... this has the same affect (and will copy any other formatting within the original cells.. this is why the last few columns turned blue).

Hope that helps.

I don't see an attachment.

I am not quite understanding the requirement....

Yes.. with examples of what should be outputted.