How to Apply an Advanced Filter to Boilerplate Billing Data
Here’s part 2 of that video which shows how you can use an advanced filter to narrow down your data. Never used an advanced filter? Watch on! It allows you to quickly and easily filter down a dataset on 1 or more column(s) of criteria. I find it to be quicker and easier than using vlookups or index match functions (not to mention there’s no concatenation required).
Hey guys, It’s Michelle here with Integrity Advantage back with another Excel how-to video. So in our last video, we talked about a boilerplate billing scheme and how we could easily use a pivot table to identify claims that met the scheme. Today, we’re going to demonstrate how to use an advanced filter to quickly and easily get all the claim lines for those claims in question. So, as we always say, with Excel, there’s a bunch of different ways to do this, but if you are accustomed to using VLOOKUP or index-match or hand filtering your data, keep watching because I think you’re going to like this one.
Alright, so here we are with our set of claims that met the criteria for our boilerplate billing scheme. And, if you remember, there were 28 claims in all. Now, we need to take our full data set and pull out all the claim lines for these particular claims. So, what I did was I made a copy of our data set and called it “data to be filtered” and just to profile of this data set a little bit, you will notice there are 4261 rows of data. So, I know I want to filter this down to the 28 claims we identified. So I’m going to insert 30 rows of on top of this data just to keep everything in the same spot. And then I’m going to go to my pivot table and I’m going to copy these 28 claims and I’m going to paste them up above. Now, I need to steal the claim header for the data, and its important that these match because Excel is going to use these headers as a guide to filter our data. So we want to make sure that this claim ID is written and noted the same way that this claim ID is. Copy/Paste is a good way to do that.
Now, to apply our advanced filter we can go to Data in our Excel ribbon and go to this advanced filter. It’s a little filter icon with a gear. And what it’s going to do is it’s going to pull up a wizard, which is great (Excel always helps us out with stuff like this) and it’s just going to say OK what’s your list and what’s your criteria? So our list is going to be our entire data set. That is our 4000 rows of data. And then our criteria range is actually going to be our list of the 28 claims that we identified. And so here, you want to make sure when you are selecting, you are getting that field header and all the 28 claims in the row. Then you can filter the list in place or you could copy to another location. For this example, we’re going to filter the list in place. So, if you hit OK, then you will notice looking at the data set that the claim lines are filtered and we can tell because there are hidden rows and these numbers turn blue.
So the final step in this process is to grab the new filtered data set and copy it and put it onto another sheet. Because we want to actually just look at the rows that are filtered; we don’t want to have those hidden rows when we do our next steps, which are likely to be more pivots or other data profiling techniques. So now you can see if we look at the row count, we went from 4000 down to 200. But what’s cool about this : if we use a pivot table to do a little profiling, you’ll see that our 28 unique claims are represented, but when we take a look at procedure codes in this data set, we’re going to have the five that were originally part of our boilerplate billing scheme, but then we’ll also have any other procedure code that was billed on any of the 28 claims in question. So, that’s how the advanced filter really gets the full picture of all the claim lines for each of the claims we identified.
So, that is how to use an advanced filter in Excel to quickly and easily filter a large data set. We hope you found this video useful. We’d love to hear from you: tell us what you liked, what you didn’t like, if you have a better way of doing this, also if you want to see more videos! We’ll talk to you soon, ok? Thanks, bye!
Integrity Advantage is the way healthcare payers reimagine the value of their fraud, waste and abuse program.
We provide FWA services to payers around the country. If you need a program assessment, program growth strategy, investigations, medical reviews or training support -- reach out today.
We are a certified Women’s Business Enterprise (WBE) and an Economically Disadvantaged Woman Owned Small Business (EDWOSB).
For more information click below, call us at 866-644-7799 or email info@integrityadvantage.com.