Find Colleges with the Most Merit Scholarships in 4 Easy Steps

Person crossing stepping stones representing steps to finding the most merit scholarships(For those looking for colleges with the most merit scholarships, 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.)    

If you’re looking for colleges that provide the most merit scholarships, start with the information the schools report to the Department of Education. This information states how much money institutions distribute in grants and scholarships and the number of students who receive it. In just four easy steps you can use this data to find colleges most likely to provide generous merit scholarships. 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 for free. You can see a simple example of how to do it here.

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.

Clear data filters in Excel

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: Institutional aid includes both merit and need-based aid. The higher the percentage of students receiving aid directly from the school, the less likely the school is only providing need-based aid. For example, if over 90% of students are getting institutional aid, it’s going to include a significant percentage of need-based aid. You can see the difference when you compare it to schools that provide only need-based aid such as the Ivy League schools.
You select the filters by clicking on the arrow key for the column % Freshman Receiving Institutional Grants and selecting the following menu options.

Percentage of freshman receiving institutional aid

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


Percentage of freshman receiving institutional aid box

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

Records selected

Spreadsheet Tip: before you set a filter on a column sort it first. It helps keep weird things from happening if you should decide to copy your final results. 

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: As I pointed out in step one, institutional aid includes both merit and need-based aid. Anything less than $15,000 probably means smaller awards for merit scholarships if the school is making any sort of serious attempt to meet students actual demonstrated need. You need both numbers since you’re looking for schools award the most merit scholarships to the most students.

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.

Average Institutional Award for freshmanAt this point, you’ll have 538 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 $34,000 (column: Avg Net Price After Gift Aid).

Reason: $15,000 in merit scholarships means a lot more at a school where the total cost of attendance is $50,000 than at one where it’s $70,000. The higher the average net price is over $34,000, the more likely that regardless of the amount of aid being awarded, the school is still likely to be expensive. If you want to use only one number to find colleges with the most merit scholarships, this is it. Remember to make sure you distinguish private and public schools when doing your comparisons.

There are now 463 schools remaining.

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

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 when you’re trying to find colleges with the most merit scholarships, this is just one way to narrow your list.
We now have 429 colleges to consider for merit scholarships.

Colleges with the most merit scholarships are just a start

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

  • There are only 346 schools that have graduation rates of 50% or better.
  • None of the colleges on this list are in 6 states. Eight states have only 1 school.
  • There are only three colleges on the list with more than 10,000 full-time undergraduates.
  • Only nine 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 find colleges with merit scholarships. 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

Link to video 4 Easy steps to target colleges for scholarships


Find Colleges with the Most Merit Scholarships in 4 Easy Steps

40 thoughts on “Find Colleges with the Most Merit Scholarships in 4 Easy Steps”

Leave a Comment