4 Easy Steps to Find Colleges for Potential Merit Scholarships

Person crossing stepping stones representing steps to finding merit scholarshipsFor 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 EFC’s 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 aid given the available information.

I’m using the DIY College Rankings Spreadsheet for this demonstration. You can download a free sample or use the embedded sample here. However, the same spreadsheet techniques can be applied to any similar data you have collected.

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: The higher the percentage of students receiving aid directly from the school, the less likely the school is only providing need-based aid.

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 775 records have been selected.

Records 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.

Average Institutional Award for freshmanAt this point, you’ll have 460 records.

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

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

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

There are now 392 schools remaining.

Step 4: Look for Colleges With Incentives to Attract Students

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 for students to attend. Of course, some will but this is just one way to narrow your list to start with.

We now have 358 colleges to consider for merit aid.

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

  • There are only 183 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 is only one college on the list with more than 10,000 full-time undergraduates. Only seven 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. 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






Consider this a way to improve your chances for merit aid given the available information.
  1. […] Because some colleges are more likely to meet student need than others. Furthermore, many schools practice some form of preferential packaging. This means that schools provide more desirable financial aid, grants and scholarships, to students they really want to attend. For less competitive students, colleges may offer financial aid consisting of various loans. Families need to know this information so that students can apply to colleges most likely to meet their financial need. (See 4 Easy Steps to Find 254 Colleges for Potential Merit Scholarships) […]

Leave a Reply