HQ Radio Jun 2, 2023: Friday Full Edition w Vlad SedlerUsing Excel 4: Sorting & Filtering
by Patrick Davitt
Click here to return to the Education page.
- Downloading the Data
- Naming
- VLOOKUPS
- Sorting & Filtering
- Tips & Tricks
Up until now, we've acquired and named files and data, and used VLOOKUP to slot data into a list of selected players.
This time, we'll look at Sorting and Filtering, and then we'll wrap up next time with a few extra tips, techniques and tools you can use to analyze and manipulate the data available in BHQ's web site offerings.
SORTING and FILTERING
One very useful Excel capability allows you to sort through data or to "filter" it looking for particular attributes.
Sorting is not a formula command. It is a tool you'll find in the DATA menu. To see how it works, open a worksheet that already has data in it. If you don't have one, download and re-name the most recent projections files according to our earlier protocols. Then it's good practice to immediately re-save using a different name so that your main data files don't get mixed up or changed in a way that causes headaches down the road.
Let's use the pitchers this time. Open up the pitchers tab for the league type in which you play (AL, NL or MLB). Click-and-drag to highlight the full category title bar (white-on-green) and all of the pitchers and all of their stats.
Let's suppose you are interested in which pitchers will have the most strikeouts. From the DATA menu, click "Sort..." to open the Sort dialog box. Near the bottom, it should say "Use Header Row." If not, click the button beside that choice.
Now you pull down the top "sort by" list and click on "K" and choose "Descending" to sort from most strikeouts to least. If you want to break ties, select a second category in the first "then by" list. Remember to change to "ascending" if you want to look at ERA or WHIP since lower is better.
Once you have your sorting choices, just click OK, and Excel will sort the list. If you want to sort the list in multiple ways and keep your results, the best bet is to copy the sheet (hold down the CTRL key and drag the tab), as many times as you need then repeat the various sorts. Another good idea—re-name the sheet tabs to reflect the sorts that appear on them ("NL_P_by_Wins").
FILTERING
A similar and very powerful tool is filtering. There are two ways to filter lists—a simple and a complicated. We'll use the simple.
Again being sure to use a copy of your master databases and not the originals, open up the pitcher sheet for your league. We're going to filter for pitchers who meet LIMA criteria.
First, we need to make a small formatting change to make the title row very deep and put cell values at the top of their cells. Type CTRL-A to highlight the whole sheet. Click the "Format" menu and choose "Cells". On the "Alignment" tab, choose "Top" from the Vertical pulldown list. Click OK. This aligns all the data to the top of their cells. Now select any cell in the category title (white-on-green) row, click Format...Row...Height ... and enter 24.
Now put three or more blank rows above the category titles. There are a number of ways to do this, but the simplest is to click and drag on the row numbers for the category title row and the two or more rows beneath it. Right-click and choose "Insert". You need those blank rows to make the filter work properly.
Now click in any cell in the first row below the category titles (the data row for the first player). Pull down the DATA menu, click "Filter" and choose "Autofilter". You will see little grey pulldown arrows in each cell of the category title row. This, by the way, is why we made the category row so tall—if we hadn't, we wouldn't be able to see the categories because the arrows would be sitting right on top of the category labels!
Since we are planning to filter for LIMA criteria, Go over to the little grey arrow in the "K/9" cell and click it. You will se a long list starting with (All), (Top 10...) and (Custom...), followed by a very long list of values. This latter part of the list reflects every value found in the k/9 values. We will not be using these; we want to see pitchers above a certain threshold.
So on that pulldown list, choose "(Custom...)". In the resulting dialog, choose in the first pulldown "is equal to or greater than" and then type 5.6 (the LIMA lower limit for Dom) into the corresponding box to the right. Click "OK". You will see that the list now shows only pitchers with k/9 of 5.6 or higher! Repeat this (Custom...) filtering in the columns with the other LIMA criteria, remembering that bb/K and HR/9 are "less than or equal to" categories.
With all this filtering done, you will have a small subset of pitchers who meet all the criteria. Click-and-drag down the row numbers to highlight the title row and these filtered rows. Right-click and choose "Copy", move the cursor to a blank sheet or down to a cell in Column "A" below the last entry on this sheet, right-click and choose "Paste". It's important not to use Cut-and-paste because Excel will cut all the rows, including those you can't see, namely the rows you filtered out. When you paste, you'll have the unfiltered list!
Copy-and-paste has how given you a filtered list of players who meet this set of LIMA criteria. Note that you could also filter for Wins, Saves, ERA or WHIP above or below certain thresholds.
Note that in the (Custom...) filtering dialog box, you can choose a second criterion, with an and/or operator in between. This is very useful for filtering in ranges. For instance, you could enter in the first box "is greater than or equal to" $5, choose "And", and in the second set of boxes use "is less than or equal to" $20. This would filter the list for players in the $5-$20 range.
The key thing to remember when you're filtering is that you can filter for values in any or all of the columns, and that each filtering reduces the list. So if you first filter for k/9 "is greater than or equal to" 5.6 and then filter for bb/9 "is less than or equal to" 3.0, what's left is the records of players who meet both criteria. Choosing the second criterion did not obviate the first. If you do want to ignore the first criterion and filter only for a different one, pull down the grey arrow in the first criterion column and choose (All).
One other thing—if you are filtering for offensive positions, make sure you go down the pulldown list in the (Custom...) dialog until you click on "contains" the appropriate position value (ie "4" for 2Bs). If you choose "is equal to" 4, you will miss all those who have 2B among multiple eligibilities.
Also, if you intend to filter for position, and your league does not use differentiated OF positions (LF, CF, RF), then you will either have to filter for "contains" 7 or "contains "8", then copy the results, then re-filter for "contains" 9. You will end up with several duplicates, namely all the players who have RF eligibility and LF or CF eligibility. A useful trick is to just use search-and-replace in the Positions column to change "7", "8", and "9" to "OF", then filter for "OF".
Sorting and filtering are great tools to help you create draft lists by stratifying players for value and by filtering for skills. As you get more comfortable with these tools, you will find yourself combining them to narrow your player searches to the exact meld of skills and value to suit your drafting needs.
