Set your data using filters in Microsoft Excel

Introduction

One of the first things to do when you receive data, be it information in a spreadsheet form or 10,000 files on a hard drive, is to understand the range of data that you have got.

This is fairly difficult when information is received on a USB memory stick or similar, because you generally have no idea how the information is organised (if it is), or whether data is duplicated etc. (If you have this problem when sending a USB memory stick, see this article).

Therefore, when I receive such data, the very first thing I do is catalog it, using one of our Filecats programs. As I generally use Excel, I use Filecats Standard if I want a quick overview, or Filecats Professional if I want to go deeper. One of the main advantages of doing this is that I can then have the information in Microsoft Excel, and so I can use all the powerful tools in Excel.

(If you don’t use Excel, then Filecats Explore and Filecats Metadata do a similar job, and I have written another article on how to understand the range of data. But I digress.)

Therefore, for this article, I will assume that either you have received a spreadsheet and want to investigate it, or you have received a CD of files (or equivalent), and have catalogued it in Microsoft Excel. If you want to follow along with the examples, I will be using this spreadsheet.

What is a filter?

A filter temporarily hides rows which do not meet your criteria.

For example, you may want to see all rows which have the letter “A” in column E. Therefore, all other rows have to be hidden.

Note that, when a filter is active, the row numbers in the filter turn from black to blue.

Alternatively, you may want to see all rows with a date in August 2014 in column J.

These are examples of filters.

What filters do not do:

1. They do not delete information. All information that has temporarily been removed (filtered) are just hidden. When the filter is removed, the hidden data is re-shown.

2. They cannot filter (hide) columns of data; they only filter rows.

Setting up the filter

If you have catalogued your files into Excel, then the filters will already be set up, and you can skip this section.

But let’s say you have received a spreadsheet instead and there are no filters set up. Then there is a two set process.

Step 1 – highlight the relevant data, with the headers being in the top row. This means two things:

1. You need to have headers. If you have data without any headers, you will need to add them. This is because Excel assumes that the first row of your highlighted information is not data, but rather information about the column.

2. You need to highlight the information. This is technically optional, however you would then be asking Excel to guess what your table is. Any gaps in the columns, or any gaps in the rows, and Excel will think that your table is smaller than it actually is.

Step 2 – In the Home tab, go to Sort & Filter – Filter.

You can now see the filters – they are the little drop-down boxes in the bottom-right hand corner.

Now they are set-up, you can use them.

What data do I have?

To see the type of information that you have in a column, you need to access the filter.

Click on the filter in the relevant column.

You will then see all of the data in the column, in alphabetical or numerical order, with duplicates removed.

For example, this spreadsheet only has files of the type “.ini”, “.jpg”, “.m4a” and “Folder”.

Does it always show all of the data? No. If there is more than 10,000 unique values (i.e. after removing duplicates), then you see a little message at the bottom “Not all items showing”. This is important, because information that is not shown cannot be selected.

Also note that you cannot copy and paste this information – in other words, you cannot copy this list into another spreadsheet. However, there are two ways you can do this.

1. You can highlight the data, and go to Data – Data Tools – Remove Duplicates. However, bear in mind that this actually deletes the duplicate data, so if you want to do this, copy the relevant column into another spreadsheet first, and then Remove the Duplicates.

2. You can create a PivotTable of this column. This sounds complicated, but as this article shows, creating a PivotTable is easy.

How can I use filters?

Using a filter is the subject of the next article in this series.

Did you like this article? There are more articles here.