How To Remove Background In Excel

"I've never been a natural, all I do is effort, try, try."

These Taylor Swift lyrics, in the vocal "mirrorball", perfectly explain my relationship with numbers, math, and annihilation apropos information analytics.

However, as a marketer, data analysis is one of the most important aspects of my job. But like most marketers, who adopt strategy and creativity, numbers and Excel reports don't come naturally to me.

That'south why it's of import to know how to work in Excel and find shortcuts to assistance make the process easier.

Today, we'll dive into one of those processes -- how to find and remove duplicates in Excel.

Download 10 Excel Templates for Marketers [Free Kit]

How to Remove Duplicates in Excel

Find and highlight duplicates in Excel through conditional formatting.
Count duplicates in Excel.
Remove duplicates with the remove duplicates feature.

1. Find and highlight duplicates in Excel through conditional formatting.

The offset pace of removing duplicates volition be to find them. An easy way to do this is through conditional formatting.

Y'all can practice that past post-obit these steps:

Make sure you're on the Home tab.
Select the entire table past hitting the button on the height left.
Click Provisional Formatting → Highlight Cells Rules → Indistinguishable Values.
In the area titled "Format With" , modify how you want duplicates to be highlighted. You tin can choose highlighting, bolding text, changing the color of text, etc.

Excel screenshot of formatting rules.

And voila. Your duplicates are now highlighted. It should look something like this:

Excel sheet with duplicates highlighted.

2. Count duplicates in Excel.

Now that you lot've plant your duplicates, y'all might want to count them and see how many there are, especially if you have a large dataset.

To do this, yous tin can use this formula =COUNTIF(A:A, A2). The formula ways that Excel is going to count how many times a sure value is used in a certain place.

The column A:A stands for the data table you're looking at. This volition probably be a different value on your Excel sheet. And so, A2 references the value y'all desire to count the frequency of.

To exercise this, follow these steps:

Create a new sail in your Excel medico.
I establish that the easiest way to count duplicates in Excel is to create a new sheet in your Excel workbook.

So, copy and paste the cavalcade that you want to count duplicates in. In the example beneath, I copied and pasted the blog titles from the editorial calendar to see if at that place were any indistinguishable titles.

So, create another column for "occurrences." This is where nosotros'll put the formula. Your new sheet should expect something like this:
Insert the formula.

Now, you can insert the formula in the first jail cell under occurrences. You'll blazon in or re-create and paste the formula. And then highlight A:A (we're going to supersede this with your dataset), and click on the sheet in your Excel doc that has the data. Now, you tin can click on the top left to select the entire sheet, or you can simply highlight the column or rows with your data.

For the 2nd value, you lot'll want to become back to your second sheet, highlight the A2, and choose the value next to it on the left. In most cases, this will stay A2, A3, A4, etc.

See what this looks similar in action here:

Excel doc with a count formula.

three. Remove duplicates with the remove duplicates characteristic.

Now information technology'southward time to remove the duplicates from your dataset.

Before you practise this, I'd recommend duplicating/copying your dataset into another sheet or a whole other workbook altogether. You e'er want to keep your original data intact, even though you tin apply Excel to remove and filter the data you want. Yous don't want to lose information because of a wrong click.

One time yous've fabricated a copy of your data, information technology's fourth dimension to remove the duplicates.

To remove duplicates, follow these steps:

Select the worksheet that has duplicate values that you want to remove. Click Data → Tabular array Tools → Remove Duplicates.
Choose the columns where you want to remove the duplicates.
In this example, I only want to remove duplicate blog titles. So I'll choose column D. I kept "My listing has headers" checked because there are 2 rows of headers before the data starts on this canvass.

Remember that Excel will remove the entire row where the duplicate value is.
Review information.

Excel will now prove you have many indistinguishable values were institute, removed, and how many unique values remain.

Screenshot of an Alert from Excel.

At present you can review your information. Comparing my start dataset to this dataset, y'all can run into that all the rows containing the same blog title were deleted.

This is what the canvass used to await like:

Screenshot of a dataset in Excel.

And this is what it looks like now:

Screenshot of a dataset in Excel.

When yous remove duplicates from Excel, information technology's important to note which column you want to remove duplicates from and call up that Excel is removing duplicates within a selected tabular array range. You can highlight the whole workbook or you can just highlight the rows that have data in information technology.

Excel will automatically go on the beginning occurrence of the value.

Working on marketing reports or any marketing Excel canvas tin can leave you frustrated and banging your head against the wall (is information technology merely me?). That'due south why using Excel templates and post-obit these simple instructions on formulas can help you level upward your game.

excel marketing templates