Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this chapter, we're going to take a look at some of the advanced formatting options in Excel 2007. I'm going to open up a new spreadsheet, underneath my office button. And for those of you that would like to follow along, it's included in folder 05_Advanced_Formatting, and it's called EatCake. E-mail list.. And What it's going to show here is a list of the employees that are working for our eat cake organization. We're tracking their last name, their first name, their e-mail address, and When they started with the company. I kept the list fairly small so that it's easy to see commands that were going to be exploring in a moment. Filtering for unique values and removing duplicates.
The real power of these two commands are going to be seen when you have hundreds of rows of data to work with. What we want to do is we want to make sure that we don't have any duplicate information included in our list. And if you take a look at the information that I've got right here, you'll see that row 2, Joe Smith's information, is duplicated again in a row 8. This is fairly easy to see because it's a small list, but let's imagine we have hundreds of pieces of information in here, and we just don't notice it. The two commands that we're going to explore right now are going to help us do that.
when you're checking for duplicate information, it's important to note that every single column of data within that row, needs to be exactly the same for Excel to determine that that's a duplicate. If there's anything different within any of the Columns, it's not going to show up as a duplicate, so it has to be an exact match. Another important point to note is the difference between filtering and removing duplicates. In filtering for unique values, you're going to notice that all it does is hide the value, it doesn't actually remove it. So it just gives you a clean list of information, where as, removing duplicate data actually removes the information from your spreadsheet.
So that's a much more powerful command than just filtering for unique values. If you want to cleanse your list, the proper order to work with is to filter your data and then remove the duplicates. And that's exactly what we're going to do. The first thing we're going to look at is filtering for unique values. If you haven't already done so, make sure that you selected the Data tab on your ribbon. This allows you access the commands are going to be working with, much easier. As well, make sure that you selected your table by selecting one of the cells within that table.
Once you've identified the table that you're going to work with, go over to the Advanced commands, select that, and you get your Advanced Filter dialog box coming up. It identifies the table that were working with, and the table range. And visually, it identifies it by placing the neon sign around the table that we're working on. You two options; you can filter the list in place, or you can copy the filtered list to another location. That's the one we're going to start with, just to show you how this works. It identifies the area where the list will be copied to, just to make sure that that's where you're going to place it, you can always click off of the Advanced Filter dialog box, and identified the new location right there.
You also want to select the Unique records only option, so that it removes any duplicate entries. Click OK, and you'll see the difference in the table percentage for you. In the table above, you have 1, 2, 3, 4, 5, 6, 7 entries, and then below, you've got 1, 2, 3, 4, 5, 6 entries. It's removed the final Joe Smith duplicate entry. If you wanted to do that same functionality but do it with the list in place, we'll just repeat those options. You click the Advanced tab, you filter the list in place this time, selecting Unique records only, and clicking the OK button. Notice that in this case, it's hidden row 8.
It hasn't removed it, it still presents the same information, but it's hidden row 8. If I take a look at my row numbers, I've got 5, 6, 7, no 8, and popped over to 9. That's the main difference when you're filtering on unique values with the information still in place. Let's undo that last command so that we re-establish our list with the duplicate in it. What's the difference between what we just did, and the ability to remove duplicates command? Let's take a look. I select my list again, I go up to Remove Duplicates.
It asks me what columns am I comparing the data on? In this case, I'm going to select them all and I'll say OK. And it removes that information, and it identifies how many duplicate values were found, and how many remain. When I say OK, I am presented with my cleansed list immediately. When we're talking about removing duplicates, it's important to realize that the information that Excel is looking for is the displayed information, not the information that's housed in the cell itself.
And even though I have it displayed in this format, it's housed in the format that you see in the formula bar, which is very similar to what is displayed in row 5. So, if I was to look at this, I could see that I have a duplicate entry in D2, D4, and D5. Now, is Excel as smart as me? Let's check it out. If I select this table, I click Remove Duplicates, and I just concentrate on the Start Date column.
Check for duplication and click OK. What does it give me? Well, it tells me it found one duplicate value, but I was really expecting to see it grab two duplicate values. Well, what happened? Let's just click on OK and see. What it did, was that it removed the same value that was displayed on the table itself. So, it removed the duplicate value for Janice Smith on the start date of the 30th of November, 06. It did not remove the displayed value for Frank Doe.
Even though it is the same date. So the key to removing duplicates, is that Excel looks for exactly the same displayed value on the spreadsheet. Let's take a look at advanced filtering in the next movie.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98459 Viewers
80 Video lessons · 141507 Viewers
59 Video lessons · 59848 Viewers
52 Video lessons · 73042 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.