Advisor DIY: Creating a MCAT/GPA Matrix

By: Kate Fukawa-Connelly M.S.

You may have referred premed advisees to the AAMC grid that shows acceptance rates at various MCAT and GPA ranges. This matrix can help a student visualize their chances relative to past applicants on a national scale. It also reinforces holistic review: some applicants with top metrics receive no acceptances, some with relatively low metrics become physicians. 

If you have a relatively large applicant pool, you can use the magic of pivot tables to create a similar matrix so that your advisees can see how their metrics correspond to recent outcomes for your specific institution. This post will take you through the process step-by-step using a sample data set that has been fabricated using random name and number generators. You can access my data set and follow along in this Excel file:

Step 1: Download your AIS Roster of Applicants from a recent application cycle.

There is a gold mine of data available to you as an advisor via the AAMC Advisor Information System (AIS). If you are designated as a health professions advisor for your institution and you don’t have access, you can request access.

To download the AIS Roster of Applicants, go to the Roster of Applicants tab, choose the year you’d like to report on, and select Download Standard (Bio) Report. A file will download to your computer. Each applicant who released their info to you as their adviser will appear as a row. Each column contains data about the applicant. 

Step 2: Format for ease of use.

1. Select the top row of your sheet. Go to the Data menu and select “Filter”. This will let you easily sort your data by column.
2. Go to the View menu and select Freeze Panes  Freeze Top Row. This will keep your headers visible when you scroll through your document.

Step 3: Delete the columns you don’t need.

To create the GPA/MCAT matrix maintain these columns and delete the rest:

• AAMC ID
• First Name
• Last Name
• AMCAS Status
• CU CUM GPA (undergraduate cumulative GPA)
• Total Score (MCAT score)
• Accepted Count

You won’t actually use the name or AAMC ID – I keep those out of habit and because I like to occasionally reference the people attached to the numbers. 

The first few entries of my fabricated data set. I have removed all of the columns of extraneous information. 

Step 4: Remove records for applicants you do not wish to include.

You must determine the parameters for what data to include in your data set. You might include all students who graduated from your institution, or all students in touch with your office, or only students for whom you wrote a committee letter—whatever standards you use, be sure that you have a rationale for doing so and report on the same set from year to year to analyze trends in your outcomes. 

To keep track of which records you remove:

1. Copy and paste all of your records into a new tab in your Excel workbook.
2. Create a new column named Include in data and then identify any students who will not be included in the data set and enter an N. 
3. Add a brief rationale for why you’re excluding them in a not included info column. 
4. Sort the data by your Include in data column so that all applicants who you wish to exclude come to the top of your spreadsheet.

Data sorted so that all records that will be excluded from the report come to the top of the spreadsheet

Step 5: Add GPA ranges to your data set.

1. For simplicity, copy and paste all of the records into a new tab in your workbook, then delete the records that you identified that you wish to exclude.
2. Sort the spreadsheet by CU CUM GPA.
3. Create a new column in the spreadsheet named CUM GPA Range. The ranges you will be using in this column are: Less than 2.00; 2.00–2.19; 2.20–2.39; 2.40–2.59; 2.60–2.79; 2.80–2.99; 3.00–3.19; 3.20–3.39; 3.40–3.59; 3.60–3.79; Greater than 3.79. These mirror the AAMC grid.
4. Fill in the column with the values that correspond to each student’s GPA. For example, Dustin Cooper has a 2.84 CUM GPA, so you would input 2.80–3.00 as their CUM GPA Range. For Bradley Brooks, whose GPA is 3.02, you would input 3.00–3.19. 
   Do the same for every record in your data set. Use copy and paste or “Fill Down” to fill in your GPA Ranges more quickly.
5. Don’t forget to save your data from time to time!

Step 6: Add MCAT ranges to your data set.

1. Sort the spreadsheet by Total Score
2. Create a new column in the spreadsheet named MCAT Range. The ranges you will be using in this column are: Less than 486; 486–489; 490–493; 494–497; 498–501; 502–505; 506–509; 510–513; 514–517; 518+.
3. Fill in the column with the values that correspond to each student’s MCAT. For example, Ashley Sanchez scored 509, so you would input 506–509. For Kristen Edwards, whose MCAT is 513, you would input 510–513. Do the same for every record in your data set. Use copy and paste or “Fill Down” to fill in your MCAT Range data more quickly.

Data Set with CUM GPA Range + MCAT Range data added

Step 7: Add “Outcomes” data to your data set.

Now you need a column that will state whether or not a student was accepted. Right now, you have acceptance count, but for this report, you just want a “YES” or “NO” rather than a discrete number. Note that this example only reports on MD acceptances and doesn’t account for DO acceptances.

1. For simplicity, copy and paste all of the records into a new tab on your spreadsheet.
2. Sort the spreadsheet by Accepted Count
3. Create a new column in the data sheet named Accepted?
4. For any student with one or more acceptances, enter YES. For students who were not accepted, enter NO.

Outcomes data added

Step 8: Create Pivot Table magic.

1. Put your cursor into a cell that has data in it. 
2. Go to the “Insert” tab in Excel and click “Pivot Table” and “OK”. This will create a Pivot Table in a new Sheet in your Excel file.

3. In this new Sheet, on the right, you’ll see that your PivotTable Fields are automatically generated. Each field corresponds to a column in your data that you can include in the Pivot Table.

4. Click on the boxes for AAMC ID, MCAT Range, GPA Range, and Accepted? Excel will try to guess how you’d like your data to display by automatically putting your fields into the layout areas below the PivotTable Fields. This is not how you want your data to ultimately display! You’ll drag and drop the fields to rearrange them.

• Drag MCAT Range to Columns. 
• Drag GPA Range above Accepted under Rows.
• Make sure that the field in Values reads “Count of AAMC ID” (you may have to click on the down arrow in this field and adjust the Value Field Settings). 

Your data will magically arrange itself into a table based on your selections. 

The bolded numbers in the pivot table show the total number of applicants at a given GPA range & MCAT range combination.  For example, in the 2.80–2.99 GPA range, there was one applicant in the 506–509 MCAT range, four in the 510–513 range, and one each in the 514–517 and 518+ ranges, for a grant total of seven.

Step 9: Make Your Pivot Table prettier.

1. Rename Row Labels and Column Labels to indicate what data is represented (GPA Range and MCAT Range, respectively). 
2. Resize so that the columns are of equal width.
3. Sort by GPA Range “From Z to A”—this will put the 3.80–4.00 data at the top of your table.
4. Align the data to be center justified and add borders for ease of reading.
5. Recolor the GPA Rows so that they stand out.

Step 10: Transfer data from Pivot Table to MCAT/GPA grid.

Now that you have all of the raw data that you need, you can create your grid. One easy route is to download a copy of the AAMC grid (Table A-23) as an excel sheet, or use mine in the sample workbook, then change the color scheme and formatting to align with your aesthetics. You’ll enter your numbers of applicants and acceptees into the sheet, then use simple percentage formulas to calculate the percentage accepted.

Final MCAT/GPA Matrix

I created this sample to exactly mirror the presentation of AAMC data. You may want to adjust the GPA or MCAT ranges represented for your population. 

To protect some anonymity for my applicants, I don’t include the exact data for any GPA/MCAT combination with fewer than five applicants.

Future Directions

Once you start to work with pivot tables, you can adjust based on your needs.

• If your population is too small, consider using multiple years of data. For my pool of about 120 applicants, I used four years of admissions data. 
• To show the effect of postbac work, I have one grid for all applicants and one for applicants without postbac/graduate coursework.
• I’ve created a grid for BCPM GPA as well as Cumulative GPA, just to see how the outcomes shift.  

As a self-taught user of Pivot Tables, there may be an even more elegant way to create this grid. Please share your hints and tips if you have any!