Open In App

How to Count Duplicates in a Column in Excel (All Methods)

Last Updated: 16 Dec, 2024

M

Midhu M
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

Counting duplicates in Excel is a crucial task when analyzing data, managing large datasets, or spotting repetitive entries. Whether you’re working on sales reports, attendance sheets, or large-scale inventory lists, identifying and counting duplicates helps maintain data accuracy and improve efficiency.

In this guide, we’ll explore all the methods to count duplicates in a column in Excel, step-by-step. From beginner-friendly techniques to more advanced solutions, you’ll learn how to clean, analyze, and organize your data effectively.

How-to-Count-Duplicate-Values-in-a-Column-in-Excel-copy

Find Duplicates in a Column in Excel

Whether you’re a data analyst, business professional, or student, mastering these methods will save you time and improve your Excel skills.

Using the COUNTIF Function

The COUNTIF function in Excel is a powerful tool for counting duplicates in a dataset. This function allows you to count how many times a specific value appears in a column or range of cells. Here’s a step-by-step guide to using COUNTIF to count duplicates in Excel.

Step 1: Open MS Excel and Prepare Your Data

Ensure your data is organized in a single column without empty rows or irrelevant data that might interfere with the count.

image-

Prepare your Data

Step 2: Select a Column to Reflect Results

Select a column next to your data to display the counts. Here we have selected Column B to reflect Results.

Step 3: Use COUNTIF to Count Duplicates

Select the cell where you want the duplicate count to appear and Enter the COUNTIF formula. Here we have Selected Cell B1. 

=COUNTIF(A:A, A2)

This counts how many times the value in A2 appears in column A.

image

Enter the COUNTIF Formula

Step 4: Drag the Formula Down and Preview Results

Apply the formula to all rows in your helper column and Preview Results. All the Values greater than 1 indicates Duplicates Values.

image-

Drag the Formula and Preview Results

Tips:

Use conditional formatting alongside COUNTIF to visually highlight duplicates.

Using Pivot Tables

Pivot Tables are one of the most powerful tools in Excel for summarizing data, including identifying and counting duplicates. They enable users to analyze large datasets efficiently by organizing and aggregating data into a concise, understandable format. Follow the below steps by step process to Count Duplicates in Excel uisng Pivot Tables:

Step 1: Prepare Your Dataset

Before creating a Pivot Table, ensure your dataset is clean.

image-

Prepare your Data

Step 2: Select the Data, Go to Insert Tab and Select Pivot Table

Highlight the column containing the data you want to analyze for duplicates. If your dataset contains multiple columns, select the entire dataset to maintain context. Go to Insert Tab and Select Pivot Table:

image

Select the Data>> Go to Insert Icon >>Select Pivot Table

Step 3: Configure the Pivot Table

Once the Pivot Table is inserted, a blank table will appear along with the Pivot Table Fields pane.

Drag the Column into Rows

  • Drag the column containing duplicate-prone data (e.g., “Data”) into the Rows area.
  • This will create a list of unique values from that column.

Drag the Same Column into Values

  • Drag the same column into the Values area.
  • By default, the Values area aggregates data using “Sum.” Change this to “Count”
image

Drag the Column to Rows and Values Filed

Step 4: Preview the Results

The Pivot Table will now display a list of unique values in the Rows area and their respective counts in the Values area. Any value with a count greater than 1 indicates duplicates.

image

Preview the Results

Using Conditional Formatting

Conditional formatting is an intuitive and efficient method for identifying duplicate values in Excel. By visually highlighting duplicates, you can quickly spot and analyze repetitive entries in your data without making any changes to the actual values. Follow the below steps to Highlight Duplicates in Excel using conditional Formatting:

Step 1: Highlight the Column

Select the range of data in the column.

Step 2: Apply Conditional Formatting

Access the Conditional Formatting Menu:

  • Go to the Home tab in the Excel ribbon.
  • Click on Conditional Formatting, which is located in the Styles group.

Choose Highlight Cell Rules:

  • From the Conditional Formatting dropdown, navigate to Highlight Cell Rules > Duplicate Values.
image-

Go to Home Tab>>Select Conditional formatting>>Highlight Cell Rules >> Duplicate Values

Step 3: Choose a Formatting Style

Once you select Duplicate Values, a dialog box will appear allowing you to choose how the duplicates should be formatted.

image

Choose a Formatting Style

Step 4: Review the Highlighted Results

After applying the rule, Excel automatically highlights duplicate values in the selected range.

image

Preview the Highlighted Results

Best For:

Quickly spotting duplicate values without altering the data.

Using Advanced Filters

Advanced Filters in Excel are a powerful yet underutilized tool for managing duplicates. They allow you to isolate unique or duplicate records and extract them to a different location for further analysis. This method is particularly useful when you need to preserve your original dataset while focusing on the duplicate entries.

Step 1: Prepare your Data Set

Open MS Excel and Enter Data into the Sheet. Make Sure your Data should be Clean and Should contain Headers.

Step 2: Go to the “Data” Tab and Select “Advanced” Option

Navigate to the Data tab on the Excel ribbon and Click on Advanced option in the Sort & Filter group.

image

Go to Data Tab>>Click on Advanced Option

Step 3: Configure the Advanced Filter

Once you click Advanced, a dialog box will appear with the following options:

Choose the Action

  • Filter the List, In-Place:
    • Use this option if you want to display the filtered results directly within your current dataset.
  • Copy to Another Location:
    • Choose this option to extract the filtered data to a separate location (preferred for duplicates extraction).

Specify the List Range

  • In the List Range field, highlight the column or dataset you want to filter for duplicates.
  • Ensure you include headers in the selection.

Set Criteria and Click OK

Check the box labeled Unique Records Only. This will ensure only unique values are extracted or displayed.

image

Select the Appropriate Options

Step 4: Analyze Output

Excel will extract the unique records to the specified location.

image

Preview Results

Additional Tips & Tricks

  1. Combine Methods: Use COUNTIF with conditional formatting for both numeric and textual duplicates.
  2. Clean Data First: Remove extra spaces with the TRIM function before analysis.
  3. Dynamic Ranges: Use named ranges or structured references for dynamic datasets.
  4. Prevent Duplicates: Use Data Validation to restrict duplicate entries.

Conclusion

Counting duplicates in Excel doesn’t have to be complicated. Whether you use the quick COUNTIF function, the organized summaries of Pivot Tables, the visual cues of Conditional Formatting, or the filtering power of Advanced Filters, Excel has you covered. These methods make it easy to spot and manage duplicates, helping you keep your data clean and organized. With a little practice, you’ll be able to handle duplicates like a pro and focus on what really matters in your work.

How to Count Duplicates in Excel – FAQs

Is there a way to count duplicates in Excel?

Yes, there are a couple of ways to count duplicates in Excel. Here’s the simplest way:

  • Let’s say your data is in column A.
  • In an empty cell (let’s say B2), type this formula: =COUNTIF(A:A,A2)
  • This formula counts how many times the value in cell A2 appears in the entire column A (including A2 itself).
  • Drag the formula down from cell B2 to all the cells where you want to count duplicates for other values.

How do you count duplicates in a list?

There are two main ways to count duplicates in a list, depending on what you want to know:

  1. Are there any duplicates – The simplest way is to convert the list to a set. Sets can’t have duplicates, so if the length of the list is different from the length of the set, there are duplicates.
  2. How many times does each element appear – You can use a dictionary to keep track of how many times you’ve seen each element. Loop through the list, and for each element, check if it’s already in the dictionary. If it is, increment the count for that element. If not, add it to the dictionary with a count of 1.

What is the formula for duplicates in Excel?

The most common formula to count duplicates in Excel is =COUNTIF(range,criteria).

Here’s what each part means:

  • =COUNTIF( starts the formula to count how many times a certain criteria is met.
  • range is the area you want to check for duplicates, like A1:A10 for cells A1 to A10.
  • criteria is the value you want to count appearances of. This can be a cell reference (like B2) or the value itself (like “Apple”).

What is the formula to count duplicates in sheets?

Sheets uses the same formula as Excel to count duplicates: =COUNTIF(range, criteria).



Article Tags :

M

Midhu M
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

S

Saumya Panday

How to Sum a Column in Excel: 3 Methods

article_img
Knowing how to sum a column in Excel is essential for managing budgets, financial reports, and large datasets. Whether you’re calculating sales totals, adding expenses, or analyzing business data, Excel provides multiple ways to add up a column efficiently. Many users wonder "how to add columns in Excel" or "how do you total a column in Excel", but few realize there are faster and smarter ways to perform these calculations.In this guide, we’ll explore three simple and efficient methods to sum a column in Excel—using AutoSum, the SUM function, and Excel Tables. By the end, you'll have the best techniques to handle calculations easily, whether you’re a beginner or an advanced user.Sum a Column in ExcelTable of ContentTop 3 Easiest Ways to Sum a Column in MS Excel How to Sum a Column in Excel with One ClickHow to Sum a Column in Excel Using AutoSum How to Use the SUM Function to Total a Column in ExcelSum a Column in MS Excel: Easy WaysHere are the 3 different methods showing how to sum a column in Excel. You can do this with the help of the Excel SUM and AutoSum options, you can use Subtotal or turn your range of cells into an Excel Table which will open new ways of processing your d
Read More

V

Vibhuti Jain

How to Find the Nth Largest Value With Duplicates in Excel?

article_img
In Microsoft Excel, in order to find Nth Largest value (with duplicates), an array formula based on MAX and IF functions is used. First, we find the largest value of range using the MAX function. In this case, the range is from B5 to B12. = MAX(range) Let the cell having the largest value be E5 i.e. 45. Once we find the largest value, we simply use the formula given below to check all values in the taken range against the "last largest value". As this is an array formula, we must enter it with Ctrl+Shift+Enter. = MAX(IF(range < E5, range)) range: Range of cells Explanation: After applying the above formula, values lower than the last largest remain the same but any value greater than the last largest becomes FALSE. As the formula gets copied down the column, each row (in this case E5) increments, hence MAX and IF return a new array, forming the Nth largest number series. Note: LARGE function is not used as it returns duplicate values if they exist in data.
Read More

P

pshukla319

Lookup Top N Values Ignoring Duplicates in Excel

Excel is one of the best ways to organize and present a large amount of data. However, finding data in excel requires certain commands. In order to lookup top N values ignoring duplicates, it is important to first learn how to find top N values in Excel. Top N values mean finding the largest value, second largest value, and so on. However, if we want to ignore the Duplicate values, the ARRAY function comes into play, that is, the formula will change and ARRAY will be involved. Let's now understand in detail with proper steps how to look up top N values ignore duplicates, Lookup Top N Values without Ignoring Duplicates in ExcelLet's first understand how to find the top N values, Suppose there is data for the marks of a student in 8 subjects, the marks are out of 100, Step 1: In order to find the largest value, use formula, =large(B1:B8,1) Step 2: Now since 3 of the subjects have 96, if the second and third largest are found, they will be 96 only, which is a duplicate number. Lookup Top N Values Ignoring Duplicates in ExcelStep 1: In order to find the Top N values ignoring duplicates in excel, use the ARRAY formula for the second-highest value without duplicate, =ArrayFormula(large(i
Read More

A

Asha Senthilkumar

How to Remove Duplicates From Array Using VBA in Excel?

article_img
Excel VBA code to remove duplicates from a given range of cells. In the below data set we have given a list of 15 numbers in “Column A” range A1:A15. Need to remove duplicates and place unique numbers in column B. Sample Data: Cells A1:A15 Sample Data Final Output: VBA Code to remove duplicates and place into next column (B) Declare Variables: VariablesData TypeCommentsnonDuplicateBooleanIt is a Boolean value (True/False).uNoIntegerCount no of Unique items in column BcolAIntegerIteration column A cellscolBIntegerIteration column B cells'Variable Declarations Dim nonDuplicate As Boolean, uNo As Integer, colA As Integer, colB As Integer Always first value will be unique, So A1 place to cell B1 'Place first value to B1 Cells(1, 2).Value = Cells(1, 1).Value Initialize variables: 'Initialize uNo = 1 since first number is already placed in column B; Assign True to the variable nonDuplicate uNo = 1 nonDuplicate= True Since the first number is already placed in cell B1, Loop starts from A2 to A15. Take each number from Column A and check with Column B (unique range) 'Use for loop to check each number from A2 to A15 For colA = 2 To 15 For colB = 1 To uNo if the number is already placed in
Read More

S

SweetSakshi

How to Compare Two Columns and Delete Duplicates in Excel?

Many a time while working with Microsoft Excel or MS Excel or Excel, we came up with a situation where we have duplicate values in columns and rows and the first task is to remove all the duplicates before proceeding to the next step. But before removing duplicates, we need to find out where these duplicates are present and for that we need to compare columns and rows to find the duplicate entry. After the duplicate values are found, we need to highlight them and remove them. The above process will be explained using excel commands. Comparing two columns in MS ExcelFollow the following steps to compare two columns in excel: Note: This tutorial is for MS Excel 2013. Steps can be different or the same for other versions of Excel. Step 1: Open the Excel file on which you want to compare the columns. For Example, your data can look like this Step 2: Select the columns and go to the Home tab > Style group> Conditional Formatting i.e first click on Home tab, then go to Style group and then select Conditional Formatting (as shown in the image below). Conditional formatting allows users to automatically apply formattings such as changing colors, data bars, and icons in the cells base
Read More

N

Nikhil Chhipa

How to Find and Remove Duplicates in Excel

article_img
Removing duplicates in Excel is essential when cleaning up data to ensure accuracy and avoid redundancy. Whether you’re working with small datasets or large spreadsheets, Excel provides built-in tools and methods to help you identify and remove duplicates effectively. This guide will walk you through all the methods, with tips for beginners and advanced users.1. Using the Built-In Remove Duplicates ToolThe Remove Duplicates tool in Excel is one of the simplest and most efficient methods for cleaning up your data. Follow the below steps to remove duplicates in excel using the built-in remove duplicate tool. Step 1: Open Excel Spreadsheet and Select Your DataHighlight the range of data where you want to find duplicates.Include column headers if applicable.Select the DataStep 2: Go to the RibbonNavigate to Data > Remove Duplicates in the toolbarSelect Data >> Click on Remove DuplicatesStep 3: Choose Columns to Check DuplicatesIn the dialog box, select the columns you want to check for duplicates.If your data has headers, check the "My data has headers" box.Here, we have selected columns ID, Name and Age.Select Columns to Check for DuplicatesStep 4: Remove DuplicatesClick OK.E
Read More

A

Ayush Dey

How to Find Duplicates in Excel (2025 Step-by-Step Guide)

article_img
Managing data effectively in Excel often requires identifying duplicate entries, which can clutter your analysis or lead to inaccuracies. Knowing how to find duplicates in Excel is essential for maintaining data integrity. From using COUNTIF to find duplicates to applying advanced filters and PivotTables, this guide covers step-by-step methods to locate duplicates and even identify unique values for improved data clarity.Find Duplicates in ExcelHow to Identify Duplicates in Excel (4 Methods)Are you looking for methods to find duplicate in Excel, below are the methods to search duplicates in excel spreadsheet:Method 1: Finding Duplicates in Excel using Conditional FormattingExcel Conditional Formatting Duplicates offers a fast and efficient way to visually identify duplicates in Excel. By highlighting duplicate values with colors, it simplifies spotting them within your dataset. This method allows you to identify duplicates without deleting them. Follow these easy steps to utilize this feature effectively.Step 1: Open MS Excel and Enter the DataOpen MS Excel Spreadsheet and Enter Data into the Sheet. Before finding the duplicates, it's essential to ensure that the data is properly f
Read More

R

Rishabh Chakraborty

Stacked Column Chart with Stacked Trendlines in Excel

article_img
A line that bounds a particular chart and shows the behavior as it propagates is known as a trend line. It is generally used for analytics purposes to get a close approximate idea from the chart. The chart can be of any type like Bar Chart, Scattered Chart, Line Chart, etc. By default, we can't directly plot a Trendline over a Stacked Column Chart. Excel doesn't provide us the flexibility to add Trendlines directly to a stacked column chart. In this article we are going to discuss three different methods to add Trendlines on a stacked column chart using a suitable example shown below : Example: Consider the table shown below. It consists of the details of the number of students enrolled in our various courses from the year 2017 to 2020. Implementation : Follow the below steps to implement a Stacked Column Chart with Stacked Trendlines in Excel: Insert the data in the cells.Now select the data set and go to Insert and then select "Chart Sets". In the drop-down select Stacked Column.Stacked Column Now, to add Trendline(s) in a chart click on the "+" button in the top right corner of the chart. But wait, you can observe that there is no Trendline option. Since there is no Trendline o
Read More

A

Asha Senthilkumar

How to Sum Values Based on Criteria in Another Column in Excel?

article_img
In Excel, we can approach the problem in two ways. [SUMIF Formula and Excel Pivot] Sample data: Sales Report Template We are creating a summary table for Total Product sales. Approach 1: Excel Formula SUMIF Step 1: Copy “Column A” Products and then paste into “Column F” Step 2: Remove duplicate products in “Column F” Step 3: Type “Sales” in cell G1. Step 4: Now make use of the below formula. Syntax: SUMIF(range, criteria, [sum_range]) Where, range : Required. Excel range of cells, to apply criteria.criteria : Required. criteria example : 12, ">12", t7, "5?" or "blue*".sum_range : optional. Excel range to add Now write SUMIF() in cell G2 =SUMIF($A$1:$A$21,F2,$C$1:$C$21) Step 5: Select cell G2 and drag till cell G6 Approach 2: Using Pivot table Step 1: Select the entire data range (A1:C21) Step 2: Now click Insert >> PivotTable to open the Create PivotTable dialog box Step 3: In the PivotTable Fields pane, drag the criteria column name (Product) to the Rows section, drag the column you will sum (Sales), and move to the Values section Pivot Table: Total Products Sales [Column E and F]
Read More

R

Rushi javiya

How to Find the Longest or Shortest Text String in a Column in Excel?

article_img
In this article, we will see how to find the longest or shortest text string in a column in Excel? Usually, for finding the longest or shortest string we can visit the all string in columns one by one and compare them to get results. This seems to work when you have less amount of data in an excel sheet. This is not a particle with a large dataset in Excel. Let's see how you can find the longest or shortest string in the column by using just one single line of formula in Excel. Longest Text String in a Column: We will use the below formula to get the longest text string in a column in an excel file. Syntax: =INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0) Parameters: INDEX: Returns the value of the given position in the columnMATCH: Locate the position of a lookup value in a columnMAX: Returns the largest valueLEN: Returns the length of the given stringrange: Input range of columnExplanation of Formula: Let's start with the inner parenthesis value. LEN(range) will return the array of the length of the cell that comes under the input range. Now MAX will return the maximum numeric value it that is the maximum length from the given array. Now this two are passed to MATCH. It will retu
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox