Google sheet Filtering
Setup
What is a cost report, if not a filtered and organized list of items? Generating cost reports is, for some reason, troublesome for many. Every production department is of course backed by one or several production accountants, who’s responsibilities include generating these reports. But in practice, all the information you need is already in your hands.
Provided that you keep a log of all your invoices, payroll and other expenses (credit cards, petty cash…), it’s easy to obtain the total on any given project or expense category within a few seconds, and more that I got the chance to improve it by adding an elo booster in the system so it would be much easier to access and work on the invoices and organized items, you might want to Click here for more.
I’ve created a mockup invoice log, and using data validation, I made sure that every invoice get tied to a proper show and expense type. For that, you have to create two things:
- An official list of all your shows
- A list of all the expense types you want to regroup invoices by
Basic Filtering
The first thing we can do is filter our expenses by show. This is the most used case of cost reporting. For the sake of simplicity, I will create a cost report tool in the same document, but ideally, it makes more sense to have a separate spreadsheet that imports data from various logs (I will explain how to link spreadsheets in a future post).
Next to my invoice log, I have repeated the header, except for the “show” column.
Using the FILTER function, we are going to list all the invoices that relate to a specific show, then tally the amounts. You can see that I have already created a “total” line at the bottom. This setup works in my case because I have very few lines. If you have hundreds of invoices, I would recommend tallying everything at the top of the page.
In Cell H2, I have set up a drop down menu based on column B. This way, I can only look up a show that exist in the list of invoices.
The FILTER function searches through a range of cells, and returns results based on one or several arguments. In Human speak, we need to:
- Look at the columns A through E
- Look at the show name in cell H1
- Return every row with a show name that matches H1.
In cell G2, I type the following formula:
=IFERROR ( FILTER ( A:E , B:B = H1 ) )
Forget for a second about the IFERROR function. The FILTER function’s first argument is the cell range (columns A through E). Then, the condition for filtering is that column B is equal to cell H1.
I’ve wrapped the formula in the IFERROR function in order to avoid displaying a #N/A error when no show is entered in cell H1.
If you select a show in cell H1, it will now list all the invoices that match that name.
Advanced Filtering
There are many, many ways to filter data. In this example, I want to filter by show and expense type. I have added a dropdown menu for the “TYPE” of expense:
Before we create the filter formula, we have to take an additional step to create our “TYPE” dropdown. Ideally, this dropdown should only reflect the type of expenses incurred by the show I’m looking at. In other words, if there aren’t any invoices for a location on the food show, then I don’t need to see “location” in my dropdown.
In a separate tab (call it “categories”, or “formulas”), I created a simple filter that says: “list the items in column B if the show name matches what I have in cell H1”. Then, I replaced the data validation in cell I1 to only show me the results from this filter.
Now, I can create my FILTER formula in cell G2:
=IFERROR ( FILTER ( A:E , B:B = H1 , C:C = I1 ) )
This formula says:
- Look at columns A through E
- return the result that match these 2 conditions:
- column B matches cell H1
- Column C matches cell I1
And there you go! You can now show exactly the type of expense you’re looking for, without any clutter or information that isn’t pertinent. You’ll also noticed that I added a little bit of conditional formatting to guide the user (see this post to get started on conditional formatting).
0 Comments