Filtering is undoubtedly one of the most important tools available to an Excel user. Starting from an unruly mess of data, with just a few clicks you can hone in on exactly the items you want, hiding all the unnecessary information and leaving you free to concentrate on the things that really matter.
Excel isn't perfect for this job, by any means, but its combination of advanced functionality and ease-of-use has made it indispensable for many users.
In this blog post, we'll see how Schematiq enables you to perform the same filtering techniques that are second-nature in Excel, and show how Schematiq allows you to overcome some of the problems that you might encounter when using Excel alone.
We'll work through the following example: a list of transactions from November and December 2017, categorised so that we can analyse our spending. First we'll filter the data, then we'll show how to output the results as an Excel table.
*** Note: You can download the example spreadsheet here, and follow the steps yourself (this will require an active Schematiq subscription! Please contact us if you'd like a trial licence).
Filtering your data
We'll break down our example into the two most common cases - filtering by a column containing text, and by a column containing numbers. We'll also mention how to use the Table Wizard within the Viewer to filter tables in a more visual way.
In each case, we'll first show how to filter the table using Excel, and then do the same using Schematiq - hopefully, this will show how easy it is to work with tables in Schematiq.
1. Text matching
Suppose that money is a bit tight, and we're looking for ways to cut down on spending. Some things we can't change (like rent) and others are essential (grocery shopping), but we could look more closely at what we're spending on enjoyment, to see if we could cut down a bit.
So, what we'd like to do is filter our table by category, and only include "Enjoyment". How can we do this?
First let's look at how we'd do this only using Excel. We first select a cell in the data table, and enable Filter mode by clicking Data → Filter within the toolbar. We can then click on the arrow beside the "Category" header, and in the drop-down menu use the tick boxes to select "Enjoyment" only.
We first create a Schematiq table from our data and its headers:
= tbl.Create(B3:E16, B2:E2)
We then apply the tbl.Filter function to it to create a new filtered table:
=tbl.Filter(table, inputColumnNames, function, [allBut = FALSE])
We use the function argument to choose how we want to filter our table - for each row, we take the columns specified by inputColumnNames, apply the function to them, and then include them in the new table if the result is TRUE.
Here, we want the input column to be "Category", and we want our function to be TRUE when the value of "Category" is "Enjoyment". We can create a snippet to do this for us:
x => x = "Enjoyment"
Looking at the right-hand side (after "=>"), the expression x = "Enjoyment" will be TRUE only if the input x is "Enjoyment".
Now, we just need to call tbl.Filter to filter the original table using our function applied to the "Category" column:
= tbl.Filter(D18, "Category", D22)
Checking the Viewer output, we see that it's worked!
2. Number matching
Instead of looking at our spending on enjoyment, we might want to look at all of our outgoings. For this, we want to see all transactions where "Amount" is negative.
This time, we click on the arrow next to "Amount", and in the drop-down menu we select "Number Filters" and then "Less Than".
In the Custom AutoFilter form that pops up, we enter "0", and click "OK".
This gives us only the rows where "Amount" is less than zero:
As before, we first need to construct a snippet to filter our data. Our input will be the "Amount" column, and we want to check whether it is less than 0:
x => x < 0
We can then apply this filtering function to the original table using tbl.Filter:
= tbl.Filter(D18, "Amount", D27)
Alternative: Using Schematiq's Table Wizard
Sometimes, it can be easier to work with tables directly within the Viewer, because we get instant visual feedback on whether our filter is working correctly. We can replicate both of the above methods using the Viewer's Table Wizard.
Taking the example of number matching, we click the "Amount" heading, and select "Filter". In the menu that appears, we enter the same snippet as before:
x => x < 0
The Viewer then not only gives us the filtered table, but also the formula to do it! Clicking on the clipboard symbol lets us paste the formula into an empty cell, so that we can then work with the filtered table.
Presenting your results
In Excel, we have a problem - if we want to change the filtering criteria, the current results will be lost! A common way of preventing this is to copy the filtered table and paste the values elsewhere, preserving the results for future reference.
However, this can introduce errors, and it means that the values must be refreshed manually every time the input data changes!
We don't need to worry about this with Schematiq. Each time we filter the original table, we create a new table, and leave the original data unchanged. This means that we can apply different filters to the data at the same time, and store the results of each separately. We can also continue to work with our filtered data table as it is, without having to create a new (manual) copy.
To get the values of a filtered table back into our spreadsheet, we can use tbl.GetValues.
*** Note: Because this returns a range, and not a single value, we need to enter it as an array formula (ctrl+shift+enter).
Taking our table filtered by Category = "Entertainment", we can display the results in our sheet:
PRO-tip: Date matching
Filtering is not confined to just text or numbers - we can also filter by boolean values (TRUE/FALSE), errors (to exclude rows where calculations have failed), and more.
Here, we'll look at another common type - dates.
Suppose we want to look at transactions month-by-month, starting with November.
Excel automatically detects that the column contains dates, and within the drop-down menu we are given the option to check/uncheck particular months and years. Here we want to check 2017 - November only:
We have a column of dates, but need a snippet that will match only those from November. Handily, Excel has a number of date functions which can interpret a given input date, and return useful outputs (e.g. the day, month, or year of that date).
*** Note: When looking for Excel functions to use, Office's online support can be helpful - for instance, see their 'Date and time functions' page for this example.
The MONTH function looks like it could be helpful, and we can use it to construct a snippet which will only match dates in November:
x => MONTH(x) = 11
Using tbl.Filter with the "Date" column as an input, along with this snippet, we obtain a table containing only those transactions taking place in November:
= tbl.Filter(D18, "Date", D32)
Try it yourself
- Learn how to remove rows and partition your data using tbl.Filter's allBut option:
Filtering Schematiq tables in Excel: allBut settled
- See how to use regex to filter tables within Excel:
Advanced Table Filtering in Excel: Using Regular Expressions and Schematiq to Filter Data