How to conditional formatting based on frequency (the most common number/text) in Excel?

This method is talking about conditional formatting the most frequent number cells in a range in Excel. Please do as follows:

1. Select the range you will apply conditional formatting to, and click Home > Conditional Formatting > New Rule.

2. In the opening New Formatting Rule dialog box, please:

(1) Click to highlight the Use a formula to determine which cells to format option in the Select a Rule Type section;

(2) In the Format values where this formula is true box, please enter the formula =B2=MODE($B$2:$D$13) ($B$2:$D$13 is the specified range you will find out the most frequent values, and B2 is the first cell of this range);

(3) Click the Format button. See screenshot:

3. Now you get into the Format Cells dialog box, please specify the formatting you will apply to the most frequent number cells, and click the OK button.
In our case, we specify a fill color under the Fill tab. See screenshot:

4. Now you get back to New Formatting Rule dialog box, please click the OK button to apply the conditional formatting.

And then you will see the most frequent number cells are filled by the specified color automatically. See screenshot:

Note: This method is only able to apply conditional formatting to number cells. If you want to find out the most frequent text and then conditionally format, please view the next workaround.

This method will introduce Kutools for Excel’s Find most common value formula to find out the most frequent text/number cell from a column, and then apply conditional formatting based on the found value.

1. Select a blank cell you will place the most frequent value into, says Cell F2, and click Kutools > Formula Helper > Lookup & Reference > Find most common value; next in the opening Formula Helper dialog box, specify the column you will find the most frequent value from, and click the Ok button. See screenshot:

And then you will get the most frequent value in the selected cell.

2. Select the range you will apply conditional formatting to, click Home > Conditional Formatting > New Rule, and then in the opening dialog box please do as follows:

(1) Click to highlight the Use a formula to determine which cells to format option in the Select a Rule Type section;

(2) In the Format values where this formula is true box, please enter the formula =$B1=$F$2 ($B1 is the first cell of the column where you get the most frequent value, $F$2 is the cell with the most frequent value);

(3) Click the Format button. See screenshot:

<imgsrc="//cdn.extendoffice.com/images/stories/doc-excel/conditional-formatting-frequency/doc-conditional-formatting-frequency-6.png" alt="a screenshot of configuring a new conditional formatting rule"/>

3. In the popping up Format Cells dialog box, please specify the formatting as you need and click the OK button. In our case, we specify a fill color under the Fill tab. See screenshot:

4. Now you get back to New Formatting Rule dialog box, please click the OK button to apply the conditional formatting.

And then you will see the entire rows are automatically filled by color based on the most frequent value of specified column. See screenshot:

How to apply conditional formatting across worksheets/workbooks?

How to copy conditional formatting rules to another worksheet/workbook?