# How to Copy Conditional Formatting with Relative Cell References in Excel – 2 Methods

The sample dataset showcases 4 students’ marks at Midterm and Final Term in Biology and Physics.

In Biology, the final term marks are lower than the Mid Term marks and were highlighted in yellow, using conditional formatting:

The conditional formula used inÂ \$C\$6:\$C\$9Â is =C6<B6. To copy the conditional formula and paste it into F6:F9:

### Method 1 – Utilizing the Format Painter to Copy the Conditional Format with Relative Cell Reference in Excel

Steps:

• Select a cell with the conditional formatting you want to copy. Here, C6.
• In the Home tab, click Format painter in Clipboard.

• A paintbrush icon is displayed.

• Move the icon to F6, left-click and drag it to F9.

F6:F9 display conditional formatting.

• Observe the GIF below.

• To check the formula of the conditional formatting of a cell, click the cell and go to Conditional Formatting.
• Choose Manage Rulesâ€¦

• Here, F7 displays: (F6<E6).

### Method 2 – Copying Conditional Formatting with Relative Cell reference Using the Paste Special Feature in Excel

Steps:

• Select the cells that contain conditional formatting. Here, C6:C9.

• Press Ctrl+C to copy.
• Select F6:F9 and right-click.
• Select Paste Special.

• Select Formats and click OK.

F6:F9 display conditional formatting.

• To check the formula of conditional formatting, follow the steps described described in Method 1.

## How to Copy Conditional Formatting to Another Sheet in Excel

Steps:

• Click a cell containing conditional formatting. Here, C7, in 1. Format Painter sheet.
• Click Format Painter in Clipboard.

• Go to another sheet. Here, 3. Copy in Another Sheet.

• Left-click the top cell (here,Â C6) to paste the format and drag down the Fill Handle.

• Check the formula of the conditional formatting, as described above.

## Related Articles

<< Go Back to Copy Conditional FormattingÂ | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF