4 Easy Steps to Find 254 Colleges for Potential Merit Scholarships

Person crossing stepping stones representing steps to finding merit scholarshipsFor those still 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 734 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 327 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 $30,000 (column: Avg Net Price After Gift Aid (2013-2014)).

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 $30,000 suggests that regardless of the amount of aid being awarded, the school is still likely to be expensive.

There are now 269 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 254 colleges to consider for merit aid.

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

  • There are only 147 schools that have a graduation rate of 50% or better.
  • None of the colleges on this list are in 10 states. Seven states have only 1 school.
  • There are only three colleges on the list with more than 10,000 full-time undergraduates. Only five 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.

(Interested in using a custom list but don’t have the time to do it yourself? Check out the Custom College Money List)

Try the Video Version



Leave a Reply