Conditional Formatting in Excel enables you to lớn quickly format a cell (or range of cells) based on the value or the text in it.
Bạn đang xem: Conditional formatting trong excel
For example, below I have an example where I have student’s scores and I have used conditional formatting khổng lồ highlight all the scores that are above 80.
If you’re interested in learning all the amazing things you can vày with conditional formatting, check out this tutorial where I show some useful conditional formatting examples.
Once you have phối the conditional formatting rules for a cell or range of cells, you can easily copy it khổng lồ other cells on the same sheet or other sheets.
In this tutorial, I will show you how to lớn copy conditional formatting from one cell to lớn another cell in Excel. I will cover multiple ways to vày this – such as simple copy-paste, copy and paste conditional formatting only, và using the format painter.
So let’s get started!
This Tutorial Covers:
Copy Conditional Formatting Using Paste Special
Just like you can copy & paste cells in the same sheet or even across sheets or workbooks, you can also copy and paste the conditional formatting from one cell to lớn another.
Note that you can not just copy và paste the cell. You have to lớn make sure that you copy a cell but only paste the conditional formatting rules in that cell (and not everything else such as the value or the formula).
And to lớn make sure you only copy và paste the conditional formatting, you need to lớn use Paste Special.
Suppose you have a dataset as shown below where I have conditional formatting applied khổng lồ column B (the Math score) so that all the cells that have the value more than 80 get highlighted.
Select all the cells where you want the copied conditional formatting khổng lồ be applied
Pro tip: In case you want to copy the conditional formatting & paste it on multiple cells or ranges (that you can not select at one go), click on the Format painter icon twice. That will keep the format painter active và you can paste the formatting multiple times (unless you hit the Escape key).
Xem thêm: Chảo Chống Dính Điện Máy Xanh, Top 10 Chảo Bán Chạy Nhất Quý Iii
Once you have the format painter activated, you can use it on the same sheet, on some other sheet in the same workbook, and even on the other workbook.
Again, just like with paste special, Format painter also copies all the formatting (including the conditional formatting).
Issue when Copying Conditional Formatting
In most cases, you will have no problems copying and pasting conditional formatting from one cell to lớn another.
But you may face issues if you have used a custom formula to determine which cells lớn format.
This option allows you to create your own formula and formatting is applied in the formula returns TRUE for a cell and not applied when the formula returns FALSE.
If you have used a formula in conditional formatting that uses absolute or mixed references, then it may not work as expected when copied.
For example, in the below example, I have used the formula =$B2>=80 lớn highlight all cells in column B that have a value higher than 80.
But when I copy this conditional formatting khổng lồ column C, it still references the B column & I get the wrong result (as shown below).
So, if you copy conditional formatting from one cell lớn another & don’t get the expected result, it’s best to kiểm tra the formula used & adjust the references.
For example, in this case, I can change the formula khổng lồ =B2>=80 & it should work fine.
If you’re wondering where the formula goes, click on the trang chủ tab và then on Conditional Formatting.
In the options that appear, click on ‘New Rule’. In the New Formatting Rule dialog box, click on the option – Use a formula to determine which cells lớn format.
This will show you the field where you can enter the formula for the selected range. If this formula returns TRUE for the cell, it will get formatted and if it returns FALSE, it will not.
So these are two simple ways you can use khổng lồ copy conditional formatting from one cell lớn another in Excel – using Paste Special and Format Painter.