Power BI Webinar
The Excel Addict - Help with Excel 2013, 2010,
2007, 2003

February 9, 2017
 
Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes (The Excel Addict)Today I came across an article online titled 'Why Excel Sheets Have Saved My Life' by Sara D, a Chicago university student who recently discovered how Excel can be used for all kinds of everyday things that are not necessarily finance related.

Yes, Excel is great for for pivot tables, macros, dashboards, etc... but many 'non-financial' people have discovered that Excel can be an awesome 'one-stop shop' for just about everything. From note taking to floor plans to keeping random lists of things to graphic design. The list of everyday things you can use Excel for is virtually endless.

Excel may not save your life, but when you look outside the box, you will discover that there many other cool ways that Excel can make your life a little easier rather than by just crunching numbers.

I shared before that, among other things, I use Excel to create all of my graphics for my newsletter and website. It's an awesome graphics design tool.

Do you use Excel 'outside the box'?

I
n today's 'Excel in Minutes' tip I'll show you how easy it is to 'Remove Duplicate Values From Your Data in Seconds'.

If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you can set Excel up to 'Open One or More Specific Workbooks Every Time Excel Starts'. You can read that tip here.

I hope you have a great weekend and keep on Excelling,

Keep on
Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


 

If you missed my last newsletter, you can click here to view it online.


 
Quote of the Day

"Freedom and greatness belong to those who master their day." 

-- Brendon Burchard
--
 
If you have a favourite quote, send it to me and I may post it in my newsletter.

Excel in Minutes with TheExcelAddict.com

Remove Duplicate Values From Your Data in Seconds

When you need to remove duplicates from a list, that could mean deleting records based on duplicate values in a single column or based on duplicates found in two or more columns.

Click here to download practice file for this tip

Remove Duplicates in Microsoft Excel 2007 2010 2013 2016 365
Beginning with Excel 2007, a Remove Duplicates option was added that has saved Excel users countless hours of manually deleting duplicate records.

Just Highlighting the Duplicates
If instead of removing the duplicates you'd prefer just to highlight them, click here to see how to 
Easily Highlight Duplicate Values.

Since the Remove Duplicates feature deletes the duplicate values from your data, it's always a good idea to make a backup copy of your original data, just in case you later discover that you need to retrieve it. A simple way to backup your list is to copy the sheet tab by holding down the CTRL key and dragging the tab to the right.

Quickly copy a sheet tab in Microsoft Excel

If your data is in an Excel Table, when you choose the Remove Duplicates option, the entire Table will be automatically selected and, based on the columns you select, the
entire records (i.e. entire rows) related to those duplicates will be removed from the table .

When removing duplicates from a 'normal' range of cells you need to be careful. If you select only a single column within the range, 
you will be given a warning that there is data next to your selection that you may want to include. You can then choose whether to expand the selection or not.

Expand Selection Remove Duplicates in Microsoft Excel 2007 2010 2013 2016 365
However, if you select two or more columns within the range, you won't get that warning and only those cells within the selected columns will be removed.

Remove Duplicates No Warning in Microsoft Excel 2007 2010 2013 2016 365
In the example above, if you highlight only the data in columns B and C and choose Remove Duplicates, the adjacent cells in columns A and D won't be removed. As you can see, if the data in columns A and D are related to the cells in columns B and C, as is usually the case in Excel, this can really mess up your data.

Some people think they first need to select the column(s) of data which they want to remove duplicates from. However, it is a better practice to select just a single cell in the range and when you choose the Remove Duplicates option, the entire range will be selected and you then will be given the option to choose which column(s) you want to base your duplicates on.

1) Select a single cell in your data;

2) 
From the Data tab, in the Data Tools group, click Remove Duplicates;

3) In the Remove Duplicates dialog, all columns are checked by default. This means that if there are rows with duplicate values in every column, duplicate rows subsequent to the first instance are removed from the Table or selection.

Remove Duplicates All Columns in Microsoft Excel 2007 2010 2013 2016 365
To remove the entire rows for which only specific columns contain duplicate values, deselect the columns you don't want checked for duplicates. If your working with a lot of columns and you want to select only a few columns, you may find it easier to click the Unselect All button first and then select only those columns you want checked for duplicates;

Remove Duplicates Select Columns in Microsoft Excel 2007 2010 2013 2016 365

4) Click OK. A message will be displayed indicating how many duplicate values were removed and how many unique values remain.

Duplicates Removed Confirmation Dialog in Microsoft Excel 2007 2010 2013 2016 365
Note that at this point you do have the option to Undo (CTRL+Z) the Remove Duplicates action.

 


If you've found this tip helpful, please share it.



Click here to get more tips from The Excel Addict every week

Thanks for supporting this newsletter and website



Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630