February 9, 2017
Hi fellow 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'?
In 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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.
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.
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.
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.
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.
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;
4) Click OK. A message will be displayed indicating how many duplicate values were removed and how many unique values remain.
Note that at this point you do have the option to Undo (CTRL+Z) the Remove Duplicates action.
|"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