Tip:
Highlight text to annotate it
X
The Data AutoFilter, is an extremely useful tool, and if you have never used it before,
I highly recommend that you look at our video clip on the Auto Filter basics.
In this video clip however, we are going to show you how to use the Data AutoFilter to
cleanup a list of data for further reporting or summarizing on.
In this example we have a report from an IT system, which shows a report of our sales
people. We would like to summarize and graph this information. But the report has generated
headers and footers which get in the way to using Excel features properly.
The AutoFilter feature will allow you to quickly cleanup the data, but before you do anything
you follow the two cardinal rules of manipulating data.
You must establish and document a control total.
In this case at the bottom of the report we have a Grand Total amount. This will ensure
that as you manipulate the data, you don’t add or loose any bits of information. You
must make a copy of the data, and keep it safe from manipulation, because you never
know when something can go wrong and you need to get back to the original data.
To activate the Data AutoFilter,
you highlight the data that is relevant to the exercise,
And you click Data Filter
AutoFilter And the arrows will appear
You can now start the cleanup operation
Have a look at the report and just know in your mind what information you are looking
to keep, and what information you are looking to get rid of,
Then when you click on the arrow you will be shown all the information that currently
exists in that column and you can start working through it.
So these dotted lines;
if we highlight them all, you’ll see they are brought up,
you can highlight them, delete the row,
and when you go back, they will have been removed,
you can continue working through the items individually, or else
you can click on the Custom option, you can show me the items where it equals,
and we’ll take the first dotted lines, or, where it equals lets say example, Company,
when you click ok, those items will be pulled up
I advise you just page down and just make sure that data that is important to you is
not included in here, because IT reports are not always consistent.
When you are happy that all those items can be deleted
you just highlight the section, you delete the rows,
and when you go back to All you’ll see that slowly but surely your information
is being cleaned up. Another clever use for the Custom function,
is you’ll see that here we have page 1, 2, 3, 4, 5. If you had to go through and individually
highlight each one it would take a bit of time, what you can rather do then is;
go to Custom when you click on the options you’ll se
that as you work down you’ll find one the that says contains
and this case anything that contains the word Page, you want to see,
and there you’ll have all the items that contain page which appear to not contain any
other data that we need, so you can highlight them,
you delete the row and now those items will also be removed
Looking through the report, you may have noticed that at the bottom of each page there seems
to be a maximum and minimum row, which just repeats the information above. As it contains
what could be information that is important, if you just looked in this column, what you
could do is;
click in the other column and highlight in this case all the maximums
and delete them, and then all the minimums, or
you could have used the Customs feature and highlighted maximum or minimum and that
will remove all those items. You may also want to delete these blank rows,
this can easily be achieved if you
click on the arrows at the very bottom you will see an option saying highlight the
Blanks, if you click on it, it brings up any cell
in this column that is blank, now you’ll see we’ve got subtotal here,
we know that we don’t need these, however just an example if this was
important to you, you could then go here,
click on that arrow and say show me the Blanks here,
so now where ever the column this cell is blank, and that cell is blank is highlighted
you can then highlight selected rows, delete them,
and when you see them all again you’ll see all the blank rows have been removed.
If you continue with this process of highlighting what you don’t need
And deleting the rows, you will eventually end up with something
that looks like this, and you can then finally eliminate the last
few items and when you look at all the information it
will be nicely setup in a format that Excel handles well
and if you want, you can just delete this top row
As a final check;
you should go to the bottom, and do a quick sum and just make sure your
control total that you’ve come up with is equal to the control total of the original
information, and if that is correct you can then work on
this data generate your graphs, pivot tables or summaries
that you require