Skip to content

Fellowship Excel Guide

Appreciate Mike Thomas for his set up with distance. Link to site

Setup in Excel

  • Find your participating specialty list on the AAMC ERAS Directory
  • For this example, I will be using Cardiology

AAMC Website - Cardiology

  • Copy the list using ctrl+c

AAMC Website - Copy

  • Paste this into Excel using ctrl+v

Excel Paste

Formatting

  • Delete Column C and E
  • Change Column A and B width to 20
  • Center align everything

Creating Headings and a Reference

  • Insert 2 rows above column 1
  • One will become our headings, one will become our reference (destination) Insert Rows Above

Adding a Reference City

  • Label the headings in row 2
  • Label E2 as "Latitude"
  • Label F2 as "Longitude"
  • Label G2 as "Population"
  • Label H2 as "Distance"
  • Label I2 as "Apply?"
  • Enter your preferred destination in cell B1

Adding Reference location

Using Bing's Geography plugin

  • Select your column of cities (without the Reference or headings we created above) in column 2 and navigate to "Data" where you will need to select "Geography" as seen below

Data Tab

  • Notice the maps beside each location Geography/Maps

Adding Latitude, Longitude and Population

  • Right-click on one of your cities in column 2 and select "latitude"
  • This will populate the E2 cell with a data value automatically

Lat/Long/Pop

  • Repeat this step for "longitude" and "population" for your cities as well as your Reference city and you should have something like below

Example

Calculating Distance

  • In cell H3, copy and paste the code below and press enter
=INT(ACOS(COS(RADIANS(90-$E$1)) * COS(RADIANS(90-E3)) + SIN(RADIANS(90-$E$1)) * SIN(RADIANS(90-E3)) * COS(RADIANS($F$1-F3))) * 3959)
  • Select cells E3 through H3 and drag these cells down

Filtering Fellowship Programs

  • To sort your programs, select all of the Headings and select "Filter"
  • This allows you to filter by distance (See [Problem Solving (Errors)][#problem-solving-errors] below if you get #FIELD! type errors)

Filtering

Excel Basic Manipulation

  • Center align everything to make it pretty and color all of the text to be whatever you wish
  • I chose light blue for links and black for the rest of the cells

Example 2

Conditional Formatting (Optional)

  • To make this spreadsheet pretty, I'll show you how to add conditional formatting. We will be making 3 rules to make our choices for application Pop

  • Our 1st rule will be making all "Apply?" column cells turn green if we enter

    "Yes"

  • Find "Conditional Formatting" on the "Home" panel and select "New Rule"

Conditional Formatting

  1. Next, select your entire range of data including the cities as well as your "Apply?" column
  2. Under our rule, choose rule type "Formula" and enter the equation
  3. "=$I3="Yes"
  4. Format this to be green fill with either green or black text
  5. Save and apply this rule

"Yes" Formatting

  1. Next, we'll be doing the same thing for

    "Maybe"

    and

    "No"

    but with the appropriate colors as below

Colorful Example

  • Now, whenever you fill the "Apply?" column, the entire row should change colors!
  • The "Apply?" field is also sortable by color, seen below, making this very useful when deciding where you want to apply

Colorful Example

Problem Solving (Errors)

  • Occasionally you will get #FIELD! errors as seen below

#FIELD! Error

  • These occur because Bing doesn't have a reference to pull from causing an error when pulling the data automatically
  • To resolve these, google the "latitude", "longitude", or "population" that is missing from the referenced location and paste it into the appropriate cell

Example Excel File