Have you ever had a need to filter some tables of data in Excel using masks? If so, this post may be useful to you. The principle is simple: place your data in one sheet (Data), create a mask on another sheet (Mask) consisting of cells filled with ones (where you want to keep the data) and zeros (where you want to remove the data), and finally, just multiply the cells of the previous two sheets in the third sheet (Filtered). A simple example is shown in the picture below.

However, the problem arises if your original data contains zeros, so in the destination sheet (Filtered) you won’t be able to decide which zeros are the “true” data and which are the result of masking. Since Excel doesn’t allow creating irregular selections, we are unable just to select all the zeros from the mask and paste it on top of our true data.

One way around this problem is as follows. Copy the whole mask into the destination data sheet right below the filtered data. While the whole mask is still selected, press Ctrl-F to invoke the “Find and Replace” window. In the search field write “0” and check “Match entire cell contents”. After pressing “Find all”, Excel will create a list of all cells (inside our selected mask) that have zero values at the bottom of the same “Find and replace” window. Now, leave this window opened and just copy the values of our filtered (or original) data on top of this mask (inside the “Filtered” sheet). After selecting all elements of the previously created list of cells inside the “Find and Replace” window, we will get the selection that we want to eliminate on top of our data.

This procedure was demonstrated on a very basic example. However, I found it very useful when the data tables become huge, which make manual selection of undesired cells extremely error prone. There is also a possibility to record a macro which would make this whole procedure automatic and bring it to a whole new level.

Zeljko Medenica