figure 1.4Īfter clicking format a “Format Cells” dialog box will appear in screen, where you select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK as shown in f igure 1.5 figure 1.5 In our example figure 1.4 we have selected duplicate to format. The cells which are unique i.e, those data which appear only one time in the selected range of cells will be highlighted when “unique” is selected. The duplicate will highlight those cells which appear more than one time. Under the Edit Rule Description option select duplicate or unique. Here select “Format only unique or duplicate values”. In this example, we have selected the product id column as shown in figure 1.2 figure 1.2Īfter selecting the range of cell, select conditional formatting, a dropdown box will appear here select “New Rule” option as shown in figure 1.3Ĭonditional Formatting > NewRule > Format only unique or duplicates only figure 1.3Īfter selecting the new rule option, a dialog box with the selected rule type will pop up. Without selecting a cell or range of data the return value will not be highlighted. The next step is to select the cell or required range of cells in a spreadsheet for which we want to apply the conditional formatting. From this data we will find duplicates in this available data. We have data of various product id, product name, and its selling price.
#Excel find duplicates but not blanks how to#
In this article we will learn how to use this “Format Only Unique Or Duplicates Values”. In order to find duplicates and unique value, Excel has special function of format only unique or duplicates values under conditional formatting. While working on big data we come across many duplicates in the working data which may create confusion. The conditions can be, based on the selected cell’s contents, or based on the contents of another cell.Ĭonditional formatting was introduced in the year 2007. Here you can create a new rule/condition for the cell. Conditional formatting will not return the condition if the cell is blank. In conditional formatting, you have to select a cell or range of cells to apply the format. ⦁ The formatting will be applied if the condition or rules that we supplied are met. ⦁ Border color and border style (but not border thickness) But conditional formatting does not have the option of changing the size of the font.
![excel find duplicates but not blanks excel find duplicates but not blanks](https://www.addictivetips.com/app/uploads/2019/04/format-blank-cells.jpg)
![excel find duplicates but not blanks excel find duplicates but not blanks](https://i0.wp.com/basicexceltutorial.com/wp-content/uploads/2019/07/070919_1254_HowtoRemove1.png)
![excel find duplicates but not blanks excel find duplicates but not blanks](https://i.stack.imgur.com/AAyhk.png)
One can apply the following formats using conditional formatting The important function is to grab the attention of the users to the most important data in that spreadsheet. Conditional formatting highlighting the data using colors and icons to cell or range of cells. The foremost function of conditional formatting is to present huge data in an understandable manner. Conditional formatting is a tool useful in presenting data to the users in an Excel spreadsheet in a unique way.