2.1K Shares

(For those looking for affordable colleges, I’m updating this post. Because I’ve updated the spreadsheet several times since I wrote this post, some of the numbers in the text won’t match the results in the graphics. You can see a video demonstration at the end of the post.) In this post, I’m going to show you how to use the DIY College Rankings Spreadsheet to find private colleges most likely to provide generous merit aid in just four easy steps. These are schools that students with high Expected Family Contributions (EFC) should target for non-need based aid. This is in no way a guarantee. Rather, consider this a way to improve your chances for merit scholarships given the available information. The idea is to use this list as a basis to create the final list of colleges to apply to.

I’m using the DIY College Rankings Spreadsheet for this demonstration. You can download a free sample or use the embedded sample here. However, you can download the data yourself directly from the IPEDS Data Center.

After you open the spreadsheet, the first thing you need to do is to make sure you have cleared all existing filters. I find that when working with the data I sometimes set filters and then later forget to clear them. This means that the next time I start using the data I get some very weird results.

It’s easy to check each time. Just go to the Data tab and click on the Clear option if it’s available. If it’s not, good deal, you don’t have any filters set.

Step 1: Find Out Which Colleges Provide Institutional Aid to the Most Students

Filter for all schools where 80% or more of freshman receive institutional aid.

Reason: The higher the percentage of students receiving aid directly from the school, the less likely the school is only providing need-based aid. In other words, they must be providing merit scholarships as well.

You select the filters by clicking on the arrow key for the column % Freshman Receiving Institutional Grants and selecting the following menu options.

When you reach the actual dialog box, just enter “80” and select OK.

You’ll see in the bottom left hand corner of the spreadsheet that 816 records have been selected.

Step 2: Check to See How Much Institutional Aid Colleges Award

Filter for all schools where the institutional aid freshman received averaged $15,000 or more (column: Avg Amount of Institutional Grants for Freshman).

Reason: Institutional aid includes both merit and need-based aid. Anything less than $15,000 probably means smaller awards for merit aid if the school is making any sort of serious attempt to meet students actual demonstrated need.

You select using the same process as before. I wanted to show this screen because you can now see that a filter exists on the % Freshman Receiving Institutional Aid column.

At this point, you’ll have 507 records.

Step 3: Make Sure It’s Enough to Actually Make a Difference

Filter for all schools for an Average Net Price of less than $32,000 (column: Avg Net Price After Gift Aid (2017-2018)).

Reason: $15,000 in merit scholarships means a lot more at a school where the total cost of attendance is $45,000 than at one where it’s $60,000. The higher the average net price is over $32,000, the more likely that regardless of the amount of aid being awarded, the school is still likely to be expensive.

There are now 424 schools remaining.

Step 4: Look for Colleges with Incentives to Attract Students with Merit Aid

Filter for all schools that admit 50% or more of applicants (column: % Admitted-Total).

Reason: Colleges that have enough applicants to turn away more than half of them are less likely to need to provide financial incentives such as merit scholarships for students to attend. Of course, some will but this is just one way to narrow your list to start with.

We now have 388 colleges to consider for merit aid.

I know 388 seems like a lot but it really isn’t since we haven’t considered any other factors that students might apply.

There are only 204 schools that have graduation rates of 50% or better.
None of the colleges on this list are in 8 states. Five states have only 1 school.
There are only four colleges on the list with more than 10,000 full-time undergraduates. Only ten have more than 5,000 full-time undergraduates.

This is meant to be a starter list. It’s not likely to meet the specific needs of any one family. Each family will need to adjust the filters to accommodate their situation and include additional ones as necessary. But it should generate a large enough list to start a focused search. You’ll still need to use net price calculators and research academics. It’s not perfect, but I think it provides a better starting place than just looking at the top schools of US News Best College Rankings.

Get your own copy of the DIY College Rankings Spreadsheet

Try the Video Version