It doesnвЂ™t have to be numbers, it can also be duplicates of text (e.i. Select the data that you want to find duplicate values in. This means it can do the job for you very quickly and save you a lot of time, but sometimes you need to be more careful with your data and supervise which entries that are removed.Дoing this manually is a slow and tedious process вЂ“ letвЂ™s use conditional formatting instead!

The вЂRemove duplicateвЂ™ button simple removes the duplicate values without letting you know which ones you removed. ThereвЂ™s a difference between вЂ Removing DuplicatesвЂ™ (accessed from the вЂDataвЂ™ tab) and identifying duplicate values through conditional formatting. Hover your mouse over вЂColor ScalesвЂ™ and hover your mouse over the different options in the next box.Д¬onditional formatting can also be used to identify duplicate values in a data set. Then click the вЂConditional FormattingвЂ™ button on the вЂHomeвЂ™ tab. This time, weвЂ™ll select the вЂPurchase pricesвЂ™ in column E (range E4:E26). Start with selecting the data that you want the вЂњColor ScalesвЂќ applied to. You can also choose to set your color scales up with 3 colors, so the value in the middle of the data is assigned this 3 rd color. All cells and values in between are assigned colors that are a blend of those 2 colors.Another color is assigned to the cell with the highest value.One color is assigned to the cell with the lowest value.Using вЂњColor ScalesвЂќ is basically a way to assign colors to all the value-filled cells of your choice. The difference between вЂњColor ScalesвЂќ and вЂњData BarsвЂќ is just the way itвЂ™s presented.

You just need to click in вЂApplies toвЂ™ at the rule you want to change in the вЂConditional Formatting Rules ManagerвЂ™ box.ДЄlthough a very similar variant to вЂњData BarsвЂќ, вЂњColor ScalesвЂќ are too a very effective tool for giving the reader an immediate overview of the data in Excel. To change the range of cells that the conditional formatting rules applies to, you donвЂ™t need to go to the вЂEdit Formatting RuleвЂ™ box above. If you donвЂ™t want your cells to turn green when it includes the text вЂњL compatibleвЂќ (or something else), you can have it turn yellow instead.ДЄs you see thereвЂ™re enough options, but wait вЂ“ thereвЂ™s more!ДЄll of the above is very nice, but if you accidentally selected the wrong area when you made the conditional formatting rule вЂ“ then it doesnвЂ™t help to change the вЂRule TypeвЂ™ or whether the cells should be red or blue.ДЄnd if you spent 5 minutes entering a unique formula to вЂњtriggerвЂќ the formatting, then it sucks to delete it and do it all over.

From this little (familiar) box you can change the formatting that you applied in the first place.

If you keep the вЂSpecific textвЂ™ you can decide here whether the cell should contain, not contain, begin with or end with the text that you enter in the field to the right.

This means that the formatting applies when a cell is empty or, for example, holds a value greater than, or less than, 100.

Here you edit your rule so it no longer вЂњtriggersвЂќ on a cell containing specific text, by choosing вЂњBlanksвЂќ or вЂњCell ValueвЂќ. If you keep the вЂRule TypeвЂ™ you can change the вЂњ triggerвЂќ of the formatting.You can also make the rule more advanced by basing it on a formula. If you donвЂ™t want the conditional formatting to apply when вЂњsomethingвЂќ happens in a cell, you can change it to apply on all cells and filling them with color based on their value compared to the average of the data. Below weвЂ™ll go through the different options shown in above screenshot.

Iftext in custom formatting excel