What You're Actually Building
The spreadsheet most IMGs and USMDs end up building for residency is a living tracker that combines your program list, rank order form, interview dates, visa status, and personal notes into one file. It looks simple. It isn't. The thing that trips people up is not the data entry. It is keeping the data consistent when every program sends a confirmation email at a different time using a different name format. I built three versions before residency apps, deleted them all, and built a fourth one that survived the entire cycle. It sat on Google Sheets with two shared colleagues who needed updates weekly. The file was 14 sheets, heavily color-coded, and still broke when someone accidentally changed a VLOOKUP range. That is the baseline experience. Read it and then read the next sections. Start with five sheets before you add anything else. Programs, Interviews, Rank Order, Logistics, and Notes. Do not start with conditional formatting or dashboards. Dashboards are decoration until the base data is stable. If you begin with pretty visuals, you will spend your first week fixing broken formulas while the interview season passes.
The Programs sheet needs these columns at minimum. Program name, city, state, program code, NCPDP match ID if you have it, visa requirement, program contact email, application status, interview invite status, rejection status, personal rating from 1 to 10, interview score, waitlist flag, and notes. Keep the personal rating column. You will use it later when you need to rank. The Interviews sheet tracks logistics. Interview date, time, timezone, format, virtual or in-person, travel cost estimate, housing booked, meals tracked, and a simple debrief column. The debrief column is where you write what actually happened. Not a polished impression. Just facts. Did the program visit go long. Were the residents honest. Did the coordinator seem organized. These details matter more than you think when the match comes.
Formulas That Actually Help
Use three formulas. Everything else is noise. First, a VLOOKUP or XLOOKUP that pulls the program name from the Programs sheet into the Interviews sheet using the program code. This keeps names consistent when programs rename things or you type their name wrong in one place. Second, a SUMIF for total interview travel costs grouped by state. Third, a simple COUNTIFS for interview invites received per month. That gives you a velocity curve without needing a chart. I learned the hard way that XLOOKUP is better than VLOOKUP when you have blank cells in lookup ranges. A blank cell will return #N/A with VLOOKUP and silently break your color coding. XLOOKUP with the optional match mode of zero handles blanks cleanly. I switched mid-cycle after spending an afternoon fixing a dozen broken cells.
Get the Full Details
Color Coding That Does Not Lie to You
Color is not decoration. Color is error detection. Use this system. Red row means rejected or withdrawn. Yellow means waitlisted or pending. Green means interview confirmed. Gray means not yet contacted by the program. Blue means interview completed. This mapping takes about five minutes to set up as conditional formatting rules and saves you twenty minutes per week during decision season when you open the sheet at two in the morning and cannot think straight. Do not add more than five colors. If you use seven colors you will forget what one means and start second guessing yourself. The goal is fast scanning, not art.
How to Track the Rank Order Form Properly
This is where most people lose points. The Rank Order sheet should mirror the official NRMP rank order form layout as closely as possible. One column per rank position, starting at rank one. Each cell contains the program code. Do not write program names in the rank column. Program names change. Codes do not. The NRMP does not care about your feelings. They import codes. Keep a separate column for projected rank. This is your mental ranking based on fit, location, program strength, and interview experience. Then keep another column for actual submitted rank once you upload to the NRMP. The delta between projected and actual is a useful signal. If you ranked a program higher than your projected score suggested, ask why. A pattern of over ranking means you are being swayed by outside pressure. A pattern of under ranking means you need more information before the deadline.
Edge Case That Broke Me Mid Cycle
Mid October I had a program change their internal code for interview scheduling. Their ERAS link used code IMH-44 while their NRMP code was IMH044. My XLOOKUP broke for four days because one sheet had one code and the other had the other. I wrote a quick helper column that stripped dashes and compared both versions side by side. Then I added a validation rule that flags any row where the ERAS code and NRMP code differ by more than one character. I do not know if that caught every mismatch. I know it caught the one that mattered. The workaround was ugly. It worked. Ugly works better than elegant when match week is two weeks away.
What This Spreadsheet Cannot Do
It cannot predict if you will match. It cannot replace a mentor who has seen your actual career trajectory. It cannot track the emotional fatigue of interview season. It will also not work well if you try to share it across five devices simultaneously. Google Sheets handles collaboration decently but you will get version conflicts if three people edit the Programs sheet at once. Lock editing to one person during rank order week. Set a clear edit schedule. Everyone gets Thursday through Sunday open. Rest of the week is read only unless the match coordinator sends an emergency update. The spreadsheet also fails if you treat it as a performance metric generator. Tracking sixty programs and auto generating a match probability calculator sounds useful. It is not. That number will be wrong. It will fluctuate. It will make you anxious. Delete that sheet the moment you build it.
Download Template Setup
I do not host a template here. I do not control your environment. What I can give you is the exact sheet structure. Start a new Google Sheet. Create five sheets. Name them Programs, Interviews, Rank Order, Logistics, Notes. Add the column headers I listed. Build the three formulas. Set the five color rules. Add the helper column for code validation. Test it with ten real programs before you add the rest. If it takes you more than forty five minutes to enter ten programs, simplify the sheet. If you want a downloadable file, export it as CSV and keep a local backup on a USB drive. Email it to yourself. Store it in one cloud service and one offline location. The NRMP portal has failed during peak rank submission windows. I have seen it. Your spreadsheet is not a backup for your rank order. It is your working memory.
Practical Tips That Come From Actual Use
Save the sheet twice daily during interview season. Automatic sync covers this but you should manually save before and after every interview. Write the debrief immediately. Memory fades in two hours. Enter your interview data into the sheet before you sleep. You will regret skipping this on night three. Lock cells that contain formulas. Protect the Rank Order sheet with a password. I have seen residents accidentally delete a formula and lose three days of work. A simple password protection on the Rank Order tab prevents this without slowing you down. Do not add a Gantt chart for interview scheduling. It adds no value. You already have dates in the Interviews sheet. A visual timeline will not change your travel decisions. Use a simple calendar export instead. Google Calendar integrates with Sheets via import. It is faster and less fragile.
When to Abandon the Spreadsheet Approach
If you find yourself spending more than two hours a week maintaining the sheet, you have overbuilt it. Cut it down. Remove the dashboard. Remove the velocity charts. Keep the five core sheets. Simplicity is the only feature that survives match season. Use an alternative tool if you need collaborative editing across multiple people or if your program requires formalized reporting. Airtable handles relationships better than Sheets. Notion handles notes better. Neither handles the NRMP rank order import format as cleanly. Pick your tool based on what you actually need, not what sounds impressive. This spreadsheet is a tool. It is not a strategy. It tracks your inputs. It does not replace your preparation, your networking, or your ability to write a coherent personal statement. Use it to reduce cognitive load. Then stop thinking about it and focus on the actual work of matching.
