How to Remove Duplicates in Excel: A Step-by-Step Guide for Beginners
Microsoft
Excel is a wonderful tool for organizing, maintaining, and managing
information. However, there are occasions when the data you add to the program
appears multiple times in the spreadsheet. When the same email address, product
name, or employee record appears two or more times in the list of your data,
you have to learn how to remove duplicates in Excel.
Don’t worry
about it — anyone who works in Excel has to deal with repeating data now and
then. However, there are a few handy options that make removing duplicates easy
and error-free. In this guide for Excel beginners, we will explore how to
remove duplicates in Excel using built-in options such as Remove Duplicates,
Conditional Formatting, and filters. We will also discuss Excel keyboard
shortcuts, basic Excel concepts, and give you some tips for managing
spreadsheets. Let’s begin.
How to Remove Duplicates in Excel: Beginners’ Guide
How to
Remove Duplicates in Excel Using the Built-In Feature
First and
foremost, let’s begin with the simplest approach for deleting duplicates in
Excel. In this case, you can use the Remove Duplicates built-in feature. It
allows you to delete all the duplicate records in the selected range of cells.
For example,
let’s say that you have a list of 100 names, and some of them appear twice.
Obviously, you can’t delete all the duplicates because, in this case, you will
be removing data that is needed. Fortunately, you can use the Remove Duplicates
feature and choose which records to delete. Before you proceed with any
actions, make sure to save an extra copy of the file in case something goes
wrong.
Here are
the steps for removing duplicates in Excel: How to Remove Duplicates in Excel
1. Open your
Excel file
Launch
Microsoft Excel on your PC. Open the workbook that contains the duplicated
data.
For
demonstration purposes, we will be using this table:
Name City
Ali Lahore
Sara Karachi
Ahmed
Islamabad
Ali Lahore
Fatima
Multan
Sara Karachi
In this
example, the duplicated records contain the same name and city. However, in
your Excel sheet, the duplicated entries can be different. Your goal is to
delete the extra records that appear in the spreadsheet.
2.Select the
Range of Cells Containing the Duplicates
In Excel,
click any cell in the range you want to scan for duplicates. If the information
appears in a block of cells, Excel will recognize it as such. Alternatively,
you can select the entire range of cells that contain the data. It is critical
that you select the entire table or column — otherwise, you will be removing
extra information that you don’t want to delete.
For
instance, if you have a table that contains the name, city, and phone number of
the client and you select only one column, Excel will remove the extra phone
numbers. However, the names and cities will still contain duplicates.
3. Open the
Data Tab
At the top
of the Excel window, you should see the ribbon. Click the Data tab and look for
the Remove Duplicates option. Depending on the version of Excel you are using,
it can be found in the Data Tools group.
4. Select
the Columns Containing the Duplicates
After
clicking the Remove Duplicates, a dialog window will appear. On the left, you
should see a list of columns. Make sure that the My data has headers checkbox
is selected.
Then, choose
the columns that you want to scan for duplicates. For example, if the list
contains the name and city, selecting both will allow you to remove the rows
with the same values in both columns.
5. Delete
the Duplicated Records
Click OK,
and Excel will remove the extra records. At the end of the process, a dialog
window will appear and inform you of how many duplicated values were removed.
In our
example, the resulting table will look like this:
Name City
Ali Lahore
Sara Karachi
Ahmed
Islamabad
Fatima
Multan
As you can
see, Excel deleted the duplicated names and cities. In this case, it was easy
to select the duplicated rows because they contained the same information.
However, if you have a table with unique IDs, make sure to select the entire
row when using the Remove Duplicates options.
How to
Remove Duplicates in Excel From One Column
Sometimes,
you may want to delete the extra information from only one column. For
instance, let’s say that you have a list of email addresses, but some of them
appear twice. In this case, you need to delete the extra rows with the same
email addresses.
Here are the
steps for removing the duplicates in Excel from one column:
1. Open the
Excel file
2. Select
the column with the duplicated data
3. Click the
Data tab
4. Select
Remove Duplicates
5. Make sure
that the selection contains the header
6. Click OK
However,
when working with Excel data, you have to make sure that the column you
selected is not part of a larger table. If it is, removing the duplicated rows
from only one column can produce erroneous results. For instance, if an
individual’s name appears multiple times in the spreadsheet, you may also want
to remove the extra phone numbers and addresses.
In this
scenario, it would be best to select the entire table and specify which columns
contain the duplicated data. This way, you will also be able to maintain the
relationship between the name, phone number, and address.
How to
Remove Duplicates in Excel Without Deleting the Original Data
In some
cases, you may want to keep the duplicated data but display only one instance
of it. For example, if you have a list of client emails, you may want to see
only one email per client. In this case, you can use the Advanced Filter
option. Here are the steps:
1. Select
the range of cells
2. Open the
Data tab
3. Select
Advanced
4. Choose
Copy to another location
5. Make sure
the list range is selected
6. Enter the
reference cell
7. Click
Unique records only
8. Click OK
As a result,
a new table will appear with the unique records. If you need to compare the two
tables, this method will help you analyze the information.
The Advanced
Filter option is useful because it leaves your original data intact. However,
it can be time-consuming if you have to compare two large sets of data. In this
case, you can use Excel formulas to display the unique values.
How to
Remove Duplicates in Excel Using Formulas
If you need
an alternative to conditional formatting and the Remove Duplicates feature,
formulas are a good choice. They can help you identify the duplicated records
and manage them. For instance, you can use the COUNTIF function to find
duplicated values.
Let’s
suppose that your Excel data looks like this:
1. In cell
B2, enter the following formula:
=COUNTIF($A$2:A2,A2)
2. Copy the
formula down the column.
As a result,
you should see the following:
Name Formula
Ali 1
Sara 1
Ali 2
Ahmed 1
Sara 2
In this
case, the COUNTIF function counts the number of times the name appears in the
list. The first instance of the name returns 1, and the following instances
return a number higher than 1.
Using this
method, you can easily see which names are duplicated. In this example, Ali and
Sara appear twice. Then, you can use the Remove Duplicates feature to delete
the extra records.
In a similar
fashion, you can use this COUNTIF function to remove duplicates in Excel
between two sheets.
How to
Find Duplicate Values in Excel Using Conditional Formatting
So far, we
have discussed methods that allow you to delete the extra data. However, what
if you want to review the duplicated records first before deleting them? In
this case, the best option is Conditional Formatting. Here are the steps:
1. Select
the data you want to examine
2. Open the
Home tab
3. Click
Conditional Formatting
4. Select
Highlight Cells Rules
5. Choose
Duplicate Values
6. Pick an
option and click OK
As a result,
Excel will highlight the duplicated cells so that you can easily spot them. In
this example, you can see which names appear more than once.
As mentioned
above, Conditional Formatting is a great tool for finding duplicates in Excel.
It is quick and easy to use. However, keep in mind that it only highlights the
duplicated data — it does not remove it.
How to Remove Duplicates in Excel Using Advanced Filtering
We already
discussed the Advanced Filter option that creates a new table with unique
records. It is a great choice if you need to review the data because it leaves
your original data intact.
How to
Remove Duplicates in Excel Without Conditional Formatting
Suppose that
you want to delete the extra records, but you do not want to use Conditional
Formatting. In this case, you can use the following formula:
=UNIQUE(A2:A10)
This
function creates a list of unique values from the range of cells A2:A10. In
other words, it will display the values from this range but delete the
duplicates.
This formula
is perfect for removing duplicates in Excel without Conditional Formatting. It
is also great because it is easy to use. Simply select an empty cell, enter the
function, and press Enter. The result will appear right away.
However,
this option is only available in newer versions of Excel because it uses
dynamic arrays. If you are using an older version of Excel, this function will
not work. Also, note that UNIQUE is case-sensitive, so it will not consider
values to be the same if they have different capitalization.
How to
Find Duplicates in Excel Using Formulas
Sometimes,
you may want to identify the duplicated values and keep the extra data. In this
case, you can use formulas to find duplicates in Excel. There are several
options to choose from, but the easiest one is the COUNTIF function. For
instance, if you have a list of names in column A, you can use the following
COUNTIF formula to find duplicates:
=COUNTIF(A:A,A1)>1
This
function will return TRUE for all of the duplicated values in the list. Then,
you can use Conditional Formatting to highlight the cells with TRUE values. As
a result, it will be easy to identify and delete the extra records.
How to
Use Filters in Excel
Sometimes,
it can be helpful to display only the values you need. In other words, you want
to apply filters in Excel. Here are the steps:
1. Select
the data you want to filter
2. Open the
Data tab
3. Click
Sort & Filter
4. Choose
Text Filters
5. Select
the option you need
For
instance, if you have a list of client names in an Excel spreadsheet, you can
use filters to sort the data in alphabetical order. Sorting is especially
useful when working with large sets of data. In this case, it will save time
and effort.
How to
Use Conditional Formatting in Excel
Conditional
Formatting is a great tool that allows you to highlight the data based on
specific criteria. For instance, you can use it to find duplicates in Excel.
Here are the steps:
1. Select
the data you want to examine
2. Open the
Home tab
3. Click
Conditional Formatting
4. Choose
New Rule
5. Select
the option and click OK
Now, you can
use Conditional Formatting to find duplicates, sort data, and more.
How to
Delete Duplicate Rows in Excel Using Conditional Formatting
Deleting
duplicate rows is similar to finding them. Again, you can use Conditional
Formatting to highlight the duplicated rows. Simply follow the instructions
provided above. After that, right-click the highlighted rows and select Delete.
How to
Delete Duplicates in Excel Online
Excel Online
is useful because it allows you to work with Excel files in the web browser. If
you have an Excel file that contains duplicated data, you can use the following
steps to delete the extra rows:
1. Open the
Excel file in your web browser
2. Select
the range of cells with duplicated data
3. Go to the
Data tab
4. Select
Remove Duplicates
When using
Excel Online, the process of removing duplicates is similar to the desktop
version. However, the location of the Remove Duplicates option may vary. Again,
the best way to find it is to go to the Data tab.
Common
Mistakes When Removing Duplicates
Learning how
to remove duplicates in Excel is not enough. To truly improve your Excel
skills, you need to know the common mistakes that occur when removing
duplicates. For instance, here are some of the most common errors that occur
when using the Remove Duplicates feature:
1. Not
saving a copy of the file
Before
deleting any data, make sure to save an extra copy of the file. It is
impossible to predict what will happen when using the Remove Duplicates option.
2. Selecting
the wrong columns
When using
the Remove Duplicates feature, always make sure that you selected the correct
range of cells. Otherwise, you may accidentally delete the data that you do not
want to remove.
3.
Mistakenly treating similar values as duplicates
Sometimes,
Excel will not recognize similar values as duplicates. For instance, if the
name Ali appears in the list twice but with a space after one of the names (Ali
), Excel will not recognize it as a duplicate.
In this
case, you can use the TRIM function to remove extra spaces. The function will
look like this:
=TRIM(A2)
4.
Mistakenly deleting duplicate names
When working
with Excel data, you have to understand that there may be two people with the
same name. In this case, using the Remove Duplicates feature will produce
erroneous results.
5. Failing
to review the deleted data
When using
the Remove Duplicates feature, always make sure that it produced the desired
results.
Excel for
Beginners: The Basics
At this
point, you should understand how to find and remove duplicates in Excel. Now,
let’s take a closer look at the basic concepts of Excel. The more you learn
about Excel, the easier it will be to manage and maintain large sets of data.
Understanding
Cells, Columns, and Rows in Excel
An Excel
worksheet is actually a large grid that consists of rows and columns. Every
cell in an Excel sheet has a unique address that is formed by combining the
column and row numbers. For instance, A1 refers to the first cell in the first
row and first column.
Rows are
horizontal lines, and they are identified by numbers. On the other hand,
columns are vertical lines, and they are identified by letters.
Cells are
the building blocks of any Excel spreadsheet. By placing the data in cells, you
can organize and manage it with ease. When working with Excel worksheets, you
should spend some time learning how to use formulas and functions. They are
incredibly powerful because they allow you to perform calculations with the
data in your Excel sheet.
Learning How
to Enter and Edit Data in Excel
To enter
data into Excel, simply click an empty cell and type the information. When you
are done, press Enter to confirm the entry.
To edit an
existing cell, double-click it or select it and make changes in the formula
bar. In some cases, you can press F2 to enter edit mode, depending on the
version of Excel you are using.
When you are
learning how to use Excel for beginners, it is a good idea to enter different
types of data in different columns. For instance, it is a good idea to store
names, phone numbers, email addresses, and other information in different
columns. This way, the data will be easier to organize, sort, and analyze.
Learning
Basic Excel Formulas
Now that we
understand the basics of how Excel works, it is time to learn how to use
formulas. Formulas always begin with an equal sign (=). With that said, there
are several basic Excel formulas that you should learn:
Add two
numbers
To add two
numbers, simply use the following formula:
=A1+B1
Sum a range
of numbers
To sum a
range of numbers, use the SUM function:
=SUM(A1:A10)
Calculate an
average
To calculate
an average, use the following formula:
=AVERAGE(A1:A10)
Using Excel Formulas to Make Calculations
Spreadsheets
are primarily used to organize, maintain, and analyze numeric data. Formulas
play a crucial role in this process because they allow you to make calculations
with the data in your Excel sheet.
For
instance, you can use the SUM function to add two numbers or an entire range of
numbers. You can also use formulas to calculate the average, standard
deviation, and other statistical values.
Using
Formulas to Keep a Running Total in Excel
Using
formulas, you can keep a running total in Excel. In fact, there are several
ways to do that. One of the easiest ways is to use the SUM function.
To keep a
running total, enter the following formula in the first empty cell:
=SUM(A1:A2)
Then, drag
the formula down the column.
Another way
to keep a running total in Excel is to use the SUM function and absolute cell
referencing. To do that, enter the following formula:
=SUM($A$1:A2)
Now, you can
keep a running total without having to manually update the range of cells.
Sorting Data
in Excel
Sometimes,
it is useful to sort data in Excel. For instance, you can sort numbers in
ascending or descending order or sort text in alphabetical order. Here are the
steps for sorting data:
1. Click the
data you want to sort
2. Open the
Data tab
3. Click
Sort A to Z or Sort Z to A
To sort text
in alphabetical order, follow the same steps. If you want to sort an entire
table in ascending or descending order, make sure to select the entire table.
Otherwise, the operation will be applied to the first column only.
Using
Filters in Excel
By using
filters, you can display only the data that you need. For instance, if you have
a list of clients in an Excel sheet and you are only interested in those who
reside in Lahore, a filter will help you find them quickly. Here are the steps:
1. Select
the data you want to filter
2. Open the
Data tab
3. Click
Filter
4. Click the
drop-down menu and select the value you need
Now, only
the records that meet the specified criteria will be displayed. Again, using
filters is much better than deleting extra data because the deleted data can be
restored by simply clearing the filter.
Using Conditional Formatting in Excel
As we
discussed above, Conditional Formatting is a powerful tool that allows you to
highlight cells based on specific criteria. For instance, you can use it to
find duplicates in Excel or highlight cells with values that are higher than a
certain number.
To access
Conditional Formatting, follow these steps:
1. Select
the data you want to examine
2. Open the
Home tab
3. Click
Conditional Formatting
4. Choose
the option you need
Using
Keyboard Shortcuts in Excel
Using
keyboard shortcuts is a good way to improve your productivity in Excel. Instead
of clicking various menus and buttons, you can use a few keystrokes to perform
common operations. Below, you can find a list of some of the most common
keyboard shortcuts in Excel for PC users:
Shortcuts
Description
Ctrl+C Copy
the selected data
Ctrl+V Paste
the copied data
Ctrl+X Cut
the selected data
Ctrl+Z Undo
the last operation
Ctrl+Y Redo
the last operation
Ctrl+S Save
the Excel file
Ctrl+F Find
a specific word or number
Ctrl+H Find
and replace a specific word or number
Ctrl+A
Select the current range or all data, depending on the context
Ctrl+B Bold
the selected text
Ctrl+P Print
the Excel file
Ctrl+1
Access the Format Cells window
Ctrl+Shift+L
Enable/disable filters
Alt+= Insert
the AutoSum function
F2 Edit the
selected cell
Ctrl+Home
Jump to the beginning of the sheet
Ctrl+End
Jump to the end of the sheet
These Excel
tips and tricks should help you learn how to use Excel for beginners. As you
can see, there are many different ways to perform common operations.
A Simple
Exercise for Excel Beginners
To help you
practice Excel skills, we present you with this simple exercise. First, open a
new Excel sheet and enter the following information:
Name
Department Salary
Ali Sales
30000
Sara
Marketing 35000
Ahmed Sales
32000
Ali Sales
30000
Fatima
Finance 40000
Now, we
challenge you to do the following:
Task 1
Remove
duplicate records.
Task 2
Sort the
list of names in alphabetical order.
Task 3
Calculate
the total salary.
Task 4
Find one of
the names in the list.
Task 5
Highlight
duplicates in the list of names.
By
completing this activity, you will gain some valuable experience working with
Excel. In particular, you will learn how to remove duplicates in Excel and
perform other common operations.
Frequently
Asked Questions
Can I remove
duplicates in Excel without using a formula?
Yes, you can
use the Remove Duplicates feature to remove duplicate records. To do that,
select the range of cells, go to the Data tab, and click Remove Duplicates.
Does Excel
keep the first duplicate or the last one?
Usually,
Excel keeps the first occurrence of a record and removes the following
instances. If you want to keep the last one, you need to sort the data.
Can I remove
duplicates in Excel online?
The online
version of Excel has similar tools for removing duplicates. To do that, simply
select the range of cells with duplicated data and go to the Data tab.
What is the
difference between Remove Duplicates and Conditional Formatting?
The Remove
Duplicates feature deletes the extra records from the list. However, using
Conditional Formatting, you can highlight the extra records and delete them
individually.
Is there a
formula to show unique values in Excel?
Yes, there
is the UNIQUE function. It displays unique values or rows in the selected
range.
Conclusion
When using
Excel, it is important to understand the fundamental concepts and operations.
For instance, as a beginner, you may want to learn how to remove duplicates in
Excel. Duplicated records can appear in any Excel sheet, and it is important to
know how to deal with them. At the same time, Excel has a wide variety of tools
for managing data. By using built-in options such as Conditional Formatting and
Advanced Filtering, you can easily remove, find, or highlight duplicated
records.
To
summarize, in this guide, we discussed the most common techniques for removing
duplicates in Excel. We also presented you with several Excel tips and tricks
and explored how to learn Excel for beginners.
Related Reading:
Comments
Post a Comment