Copying conditional formatting by scrolling

New Contributor

In the attached spreadsheet, e.g. the FAME 3h sheet, I have entered calculations and conditional formatting in cells AD39 to AI39, then scrolled these cells down to row 117 to copy the calculations and conditional formatting. The former worked correctly, and the latter also appeared to do so. However, more careful analysis subsequently showed that the conditional formatting was absent from several rows (e.g. 52, 53, 54, 64, etc.); the cells that should have been formatted but were missed have been manually changed to red font.

I am using the conditional formatting to identify outliers, using Peirce's criterion, for elimination from the data set prior to doing further calculations.

I am using Excel as part of Microsoft 365, version 16.0.14026.20246, on a Lenovo X230 computer running under Office 10.

3 Replies


See the attached version. I created a single conditional formatting rule for the entire range AD39:AI117.

best response confirmed by Gerald_Rowe (New Contributor)

@Hans VogelaarGreat! Please tell me in detail how you did it. Thank you in advance - this has been quite a headache!


Thanks again, Hans, I see how it's done.
Best wishes,