How to Remove Duplicate Entries from Multiple Columns in Excel

By
In our last blog post, we have learned to remove duplicates entries in excel. In this blog post, we will go little advanced to remove the duplicate entries from multiple columns.

Removing Duplicates Entries from 02 Columns:

We will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in both the “Fruits” column and “Price/Kg” column are highlighted with different colors.


To remove duplicate entries in these 02 columns, follow the steps provided below:

1. Select the data from which we want to remove the duplicates.


2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image.



3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicate” will appear on our excel screen.


4. If we check the checkbox for “My Data has headers” then the header row from our selection will be excluded automatically and under “Columns” section we will see the name of header for each column. Below screenshot will help you to understand this.


5. If we uncheck the checkbox for “My Data has headers” then header row will get included in the selection and under “Columns” section we will see the names of respective columns and not the name of headers. Below screenshot will help you to understand this.


6. In our case, we have checked the checkbox for “My data has headers” and selected both “Fruits” column and “Price/Kg” column.


7. Once we click on “OK” button, we can see another dialog box will appear on our excel screen which will show us how many duplicate entries are removed and how many unique entries will remain in our data.


8. Once again we have to click on “OK” button which is appearing in this dialog box and then we can see all the duplicate entries from our selection will get removed and only unique values will remain.


Removing Duplicates Entries from 03 Columns:

In this section we will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in three columns “Fruits”, “Price/Kg” and “Items (in Kg)” are highlighted with different colors.


To remove duplicate entries in these 03 columns, follow the steps provided below:

1. Select the data from which we want to remove the duplicates.


2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image.



3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicate” will appear on our excel screen.


4. If we check the checkbox for “My Data has headers” then the header row from our selection will be excluded automatically and under “Columns” section we will see the name of header for each column. Below screenshot will help you to understand this.


5. If we uncheck the checkbox for “My Data has headers” then header row will get included in the selection and under “Columns” section we will see the names of respective columns and not the name of headers. Below screenshot will help you to understand this.


6. In our case, we have checked the checkbox for “My data has headers” and selected three columns “Fruits”, “Price/Kg” and “Items (in Kg)”.


7. Once we click on “OK” button, we can see another dialog box will appear on our excel screen which will show us how many duplicate entries are removed and how many unique entries will remain in our data.


8. Once again we have to click on “OK” button which is appearing in this dialog box and then we can see all the duplicate entries from our selection will get removed and only unique values will remain.


In this way, we can remove duplicate entries from multiple columns in excel.
Microsoft Certified Excel Expert, Enthusiastic to teach excel tricks and basic excel concepts which will help to save time and efforts in our daily office work.

Comments

Post a Comment

Related Topics..

Fill Up, Down, Right & Left in Excel

By
Image
Excel has many great tricks which can help us to save our time while working on big data. In this blog post we are going to see one of those tricks where we can fill the data to the upwards of the selected cells, to the downwards of the selected cells, to the right of the selected cells and to the left of the selected cells. There are many situations where we may need to copy the data from selected cell to the upward, downward, left and right cells. But how can we do so?? In excel we can do so with the help of “Fill” command. This command we can found in: Home Tab --> Editing Group --> Fill Command. Let’s take an example. We have inserted a text “Tejraj” in a cell “D5”. Now we will see how to fill this text from cell D5 to Up, Down, Right & Left. Fill Down: 1. Let’s assume that we have to fill the data from cells D5 to D10. For this, select the Cell Range D5:D10. Keep in mind that the data from 1st cell of the selected Cell Range will fill down in entire selection. 2. Select
Read more

Select all cells highlighted with specific color at a time

By
Image
Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s
Read more

Excel Shortcuts Ctrl A to Ctrl Z

By
Image
Use of keyboard shortcuts while working in excel will be a great help for us. It will save a lot of time. In this post we are going to see Excel Keyboard Shortcuts for alphabets A to Z while using Ctrl key. Below table will give you a brief idea for each shortcut and its functionality: Now, we will see in detail how each of above listed shortcut combinations will work: Ctrl + A :  It will select all content present on the worksheet. If active cell is in the table then it will select entire table. Ctrl + B :  It will convert selected text into Bold . Ctrl + C : It will copy the selected content. The content which is copied can be seen in the Clipboard. Ctrl + D :  It will fill down the content from upper cell. For example we have inserted a number “1” in the cell A1. If we select the cell range from A1:A5 and press Ctrl + D, then this number “1” will automatically fill down in the entire cell range . Ctrl + E
Read more

Insert Multiple Rows in Excel Based On Cell Value

By
Image
In this blog post we will learn to Insert Multiple Rows in excel based on cell value with the help of VBA Code. For this we have prepared dummy data as shown in below image. In this data we have listed few “Products”, their “Quantities” and their “Unit Price”. Now we want to insert multiple rows based on their “Quantities”. For example, “Product 1” has 04 quantities so we have to add 04 rows below the Product 1, “Product 2” has 02 quantities so we have to add 02 rows below the Product 2 and so on. To avoid wasting our time by manually inserting rows one by one, we can use VBA code which will do our work within seconds. To do this, just follow the below steps: 1. Add “Developer Tab” on the Ribbon if it is not already added. If you are not aware about how to add Developer Tab on the Ribbon then click here . Once added on the Ribbon, “Developer Tab” will looks like as highlighted with red in below image. 2. Now, click on the “Visual Basic” command as highlighted in below image. 3. Once
Read more

Title Bar in Excel

By
Image
Title Bar is the bar holding title of the excel file. But apart from showing title, it also contains many other important functionalities. so let's get familiar with Title Bar in this blog post. Title Bar is divided in 03 parts: 1. Quick Access Toolbar 2. File Name 3. Minimize, Maximize/Restore, Close buttons Let’s see in detail about this. Quick Access Toolbar:  The commands which we are using frequently can be added to Quick Access Toolbar. Following steps will help you to add desired commands here. 1. Right click on the desired command. (In below screenshot we have selected “ Format Painter ” as a desired command) 2. Select option “Add to Quick Access Toolbar”. 3. Now, you will see “ Format Painter ” command is added in the Quick Access Toolbar. 4. To remove desired command from Quick Access Toolbar, just right click on the command and then select option “Remove from Quick Access Toolbar”. 5. No
Read more

Conditional Formatting: Highlighting Top 10 Percent Values in Excel

By
Image
This blog post is in continuation to our series of posts on “Conditional Formatting”. In this blog post we will learn how to highlight “Top 10%” values from selected set of values with the help of “Conditional Formatting”. Conditional Formatting Top 10% option we can find in Styles group under Home Tab.  Now the main question is what does it mean by highlighting “Top 10%” values from selected set of values? It means “Conditional Formatting” will highlight Top 10% values from total no. of values.  If our total values are 10 then excel will highlight Top 1 values. If our total values are 20 then excel will highlight Top 2 values. If our total values are 30 then excel will highlight Top 3 values. This is the basic concept behind this option. So let’s learn how we can highlight these Top 10% values using “Conditional Formatting” in excel.  To explain this topic in detail, I have listed total 50 values as shown in below image. We will see how we can highlight Top 10% values from selected se
Read more

Excel Pivot Table

By
Image
Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, “Create Pivot Table” dialog box will appear on screen. This is like as shown in below image. This “Create Pivot Table” dialog box is very important to create Pivot Table. There are two important options a
Read more

Customizing Sparklines in Excel

By
Image
In our previous blog post we have learned to insert Sparklines in excel . Now, we will go little advanced in this blog post and learn to customize the Sparklines. If you have not visited our blog post on inserting Sparklines in excel then please click here before proceeding for this blog post, as we are going to use the same data which we have used previously. We have crated 03 types of Sparklines: Line, Column and Win/Loss as shown in below image. To customize Sparklines in excel, we have to first enable the “Design” tab on the ribbon. Design tab will get enabled if and only if we click on the Sparklines. If we click on any portion where Sparklines are not created then Design tab will get disabled. Once we click on the “Design Tab” then we can see the commands which are divided in various groups. In this blog post of “Customizing the Sparklines”, we will see all the commands “Show Group” which are highlighted with Pink color and the commands to change the “Sparkline Color” and “Mark
Read more

Format Cell Values in Excel

By
Image
In this blog post we will learn to “Format Cell Values” in excel. Many times while working in excel we have to use various formats like text, number, date etc. to our cells values. So here we will learn how to format our cells and how many formats are there which we can use to our cell values. First of all we will see where these various formats are available on our excel screen. For this we have to click on “Home” tab and check the “Number” group. If we click on the drop down menu as shown in below image we can see all the formats which we can use to format our cells. We will see all these formats in brief in this blog post. To change the format of a cell values, first select the respective cell and then select the desired format from the below image as per your requirement. General Format: By default “General” format is applied to excel cells. It means there is no any specific number format is applied to the cells. Number Format:  This format is used to display the numbers. For examp
Read more