How to highlight values that appear X times in Excel?
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:
Notes:
- 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.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
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.
Best Office Productivity Tools
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in