Skip to main content

How to highlight values that appear X times in Excel?

Author: Siluvia Last Modified: 2019-12-02

Normally, you may apply the Conditional Formatting feature to highlight duplicate values in a range of cells in Excel. However, if only the values that appear a specific X times (such as 3 times or 5 times) need to be highlighted, how can you do? This tutorial provides a step-by-step guide to help you quickly solve the problem.

Highlight values that appear X times with Conditional Formatting
More tutorials for Conditional Formatting…

Highlight values that appear X times with Conditional Formatting

Please do as follows to highlight values in an Excel list that appear X times.

1. Select the list you will highlight the values, click Home > Conditional Formatting > New Rule.

2. In the New Formatting Rule dialog box, you need to:

  • 2.1 Click Use a formula to determine which cells to format option in the Select a Rule Type section;
  • 2.2 Copy the below formula into the Format values where this formula is true box;
  • =COUNTIF($C$2:$C$13, C2) = 3
  • 2.3 Click the Format button to specify a font or fill color you need;
  • 2.4 Click the OK button. See screenshot:


  • 1. In the formula, $C$2:$C$13 is the selected range, C2 is the first cell of the selected range, and number 3 is the frequency of values.
  • 2. If you want to highlight values that appear 5 times, please change the number 3 to 5.

You can see values appearing 3 times in the specified list are highlighted immediately.

Related articles

Highlight largest / lowest value in each row or column
If you have multiple columns and rows data, how could you highlight the largest or lowest value in each row or column? It will be tedious if you identify the values one by one in each row or column. In this case, the Conditional Formatting feature in Excel can do you a favor. Please read more to know the details.

Highlight row if cell contains text/value/blank in Excel
For example we have a purchase table in Excel, now we want to find out the purchase orders of apple and then highlight the entire rows where the orders of apple are in as the left screen shot shown. We can get it done easily with Conditional Formatting command or Kutools for Excel's features in Excel.

Highlight cell based on adjacent cell value in Excel
If you need to highlight cell where adjacent cell is equal to or greater than it, of course, you can compare them one by one, but, are there any good and quick methods for solving the job in Excel?

Highlight cell if value exists in or equals to cell in another column in Excel
For example, you need to highlight cells in column A if these cell values exist in column B as well. This article will provide two methods for you.

More tutorials for Conditional Formatting…

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What can I do if I want the frequency of values to be a vlookup value?
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations