Some have asked for my updated spreadsheet, so here it is. This spreadsheet uses a LOT of formulas to keep track of stuff automatically for you (wins/losses, stats, records, etc).
Dynasty Template is the template and it's a pretty bared bones spreadsheet, but it has some stuff in there applicable to my dynasty (user team names, etc) to show how it should be setup.
Coach Mode Career '14 is the file for one of my old dynasties. This is a couple decades into the dynasty and shows how it should look when everything's filled in.
A lot of these instructions are already in this thread, but I'm gonna basically redo them.
I made this for myself so I know all the ins and outs of it. I'll try to explain stuff that's not obvious, but I'm sure I'll miss some stuff. The examples I give will be from the completed "Coach Mode Career '14" file, so use that as a reference. I'll just go through the tabs 1 by 1 and explain stuff.
If you're familiar with excel and its formulas (the more familiar the better) it really helps. If you get lost in spreadsheets, you probably shouldn't even bother with this. As long as you can search/replace, you should be fine.
Schedule
This has every season's schedule, as well as your various career records (vs. top 25, @ home, @ each of your schools, against other teams, etc).
Upper left: "Current Season" is just set to be equal to the current season record for your current year (this isn't shown on the screen because my monitor is too small which is why I put it in the upper left). Every year set those squares ("7-1" and "4-1" in this pic) to be equal to those of your current season (IE Current wins = square D620) and it'll be updated after each game.
Every season fill out your current schedule. This is the "Conf, Rival, H/A, and Opponent columns.
Use "X" to denote Conference foes and Rivals in those columns.
For H/A, "N" is for neutral site regular season games. "@" is for road games. Blank is for home.
Weekly, as you play the games, include both teams' rankings and your POTG (if you want).
GGS is where I put notes about the game (My Greatest Game Score if applicable, # of OTs).
Add your score and your opponents scores in their columns the spreadsheet will automatically figure out if you won ("W") or lost ("L).
Note the encircled note in the top center. There are hidden columns that handle most of the calculations (note how it skips from column "P" to column "HN". You can highlight columns P and HN -> right click -> "unhide" to see all of the formulas. Most of the column names are hopefully self explanatory (IE. Win, Conf?, Conf L, Rivalry W, etc). Here are some notes about them:
I only tracked wins/losses if I was the HC. So the"HC?" column looks @ column C to see if it says "HC" or "OC" or "DC". If you want to track wins/losses, makes sure column C has all "HC"s.
To see what each column is looking @ you can check the formulas in each square, but there are a LOT of nested "if" statements in some of them.
I track my record against every team that I play. These are displayed at the top, and are calculated in the hidden columns. I don't have every team listed so you'll have to edit these if you play teams not in my list.
- The rightmost hidden columns are placeholders. To add a new team, highlight both columns ("W" and "L") and do a search/replace, replacing all "temp" with your new team (IE. "New Mexico").
- If you run out of "TEMP" placeholders, or simply know you'll never play some of the teams I've included, you can replace them by doing a similar search/replace, just replacing the old team (instead of "temp") with your new team.
- To display these, you'll need to change/add the new team (IE. New Mexico) at the top and change the "W" and "L" column sums to the new ones. IE. I could change Minnesota (L8) to "New Mexico" and then change their win total (M8) from "=SUM(BF6:BF1454)" to the new columns I search/replaced to be New Mexico (IE. maybe it's now =SUM(HH6:HH1454))
- The various team records are just dumb pattern recognition which presents a couple of problems. 1. if you mispell the team name in your schedule (IE. "Naw Mexico") it won't track your win/loss. 2. When doing a search/replace to remove my teams for yours, be careful you don't replace too many. IE. replacing "Michigan State" with "New Mexico" is fine. But, replacing "Michigan" with "New Mexico" will work for Michigan, but will also turn "Michigan State" into "New Mexico State" and "Western Michigan" into "Western New Mexico", etc.
- To make sure every opponent registers, every year after I enter the schedule I go team by team and put a 1 in the "my score" column and make sure that I get a win added to my talley against that opponent. Every now and then it won't update and I'll see that I mispelled that team.
Top/left I have my career record and my record @ each school. The career records @ each school will need to be manually edited. This is just simply a SUM of the W/L columns (columns W and X are normally hidden) for the years that I was there. IE. I started @ UMass in 2014 (which is row 54) and left after 2015 (row 97) so my UMass SUM is "SUM(W54:W100)"
I use an 8 team playoff so I have CCG, Quarters, Semis, Finals for every year. If I don't make the playoffs, then I change "Quarters" to "Bowl." If you don't use a playoff, you can change it to have fewer games and just copy/paste it all the way down.
I think that's about it for the schedule tab (this tab does the most heavy lifting by far and requires the most detail).
Dynasty Info
There's not much to say here as everything here is manually added. I think it's all pretty self explanatory. I use the red boxes for general notes (IE. record breakers, bad injuries, conference realignment, etc).
@ the top there's a box where I track the yearly conference and national champs. below this is where I track my coordinators and their records as HCs @ other schools.
House Rules
Just a list of the house rules I use and where I track some of them like my QB AWR edits.
It's also a worksheet where I do my playoff shuffling every year.
Recruiting
Each season's recruits is manually added on the left. Here are a few notes:
- For JUCOs, enter "SO" or "JR" for whichever year they are and it'll color code them to differentiate them for you.
- Pipeline states are highlighted in orange. The spreadsheet figures this out by simply coloring it orange if there's a space. So, for your pipleines, end the state w/ a space (IE. "MI" is NOT going to show as a pipeline, but "MI " will).
- I enter each recruit as I sign them. Then after the class is finished I do a sort by "Prestige" high to low and then sort "OVR" high to low. This is just my preference.
- The toal # signed is tallied for you and is simply a COUNT of the HGT column.
- The bottom gets your average OVR and Prestige for you.
The upper left is my recruiting history and is mostly manual with a few exceptions. Every year, manually add your TEAM, Prestige, RANK (recruiting class rank), and the # of 5* - 1* you signed.
Every year, I copy/paste my last recruiting class, delete all of the players, and search/replace the old year w/ the new year. The "Total" column is simply a SUM of your 5*-1* that you manually added. The OVR AVG column is equal to the average for each year. IE. in 2015, the OVR AVG (K5) = U40. Every season, when I copy/paste the recruits, I set that year's OVR AVG cell to = the new AVG for the new season. (I didn't explain that well, but look @ the "formulas" in cells K4 - K24 and it should be obvious. Each of those was created when that season was the current one).
Pass, Rush, ....., (S)KR, (S)PR
Besides the schedule tab, these tabs are the heart of the spreadsheet and handle all of the stat tracking.
There are 2 types of tabs here, source tabs (IE. (S)Pass), and the linked tabs (IE. Pass).
Those w/ (S) in front are the source tabs. It says this on every source tab, but to reitterate:
NEVER, EVER
SORT THESE
SHEETS!!!!!!!
FILTERING IS
FINE.
If you sort them, it'll FUBAR all of your stat tracking.
There is a template @ the bottom of each of these (S)ource tabs. For every player on your team (when you start) copy/paste this template. IE. if you have 3 QBs, then copy/paste the TEMPLATE 3 times in the Pass tab.
Then you do a search/replace on each template to change the name from "FR. TEMPLATE" to your players name. The easiest way to do this is to highlight every "FR. TEMPLATE" for the current player and have your search/replace setup to replace "*" with "Rocket McStudly". "*" is a wildcard and it will replace everything highlighted (or EVERYTHING in the entire tab if nothing is highlighted) with "Rocket McStudly" (so make sure you only highlighted the FR. TEMPLATE cells). Then you highlight the next player's FR. TEMPLATE and all you need to change is the replace string (IE. "BBGun McNoodleArm"). This is helpful for non-Pass tabs as you'll need to change the FR. TEMPLATEs and the POS (position) columns for every player.
Do this for every player when starting a new spreadsheet. Once you're going you'll only have to do this for every new recruit of each season. This is the procedure for each new season going forward:
- Update the templates. Change the "Game 20xx", "Season 20xx", and "Career 20xx-xx" years. The easiest way is to search/replace them going backwards. IE. if your template goes from 2021-2024, replace all 24s with 25s, 23s with 24s, 22s with 23s, and 21s with 22s. Now your template is for 2022-2025. Just changing 24 to 25 (as opposed to 2024 to 2025 or Game 2024 to Game 2025) takes care of every Game, Season, and Career cells
- Update your new season's schedule for the current year of the template
- Copy your template from Player column -> Opponent column (but NOT the POS column)
- Go to the next tab, and paste the new template (from Player to Opponent) over the old one. Do this for every (S)ource tab
- Now, for each tab, copy/paste the entire template for each NEW player that'll get those stats. IE. for my spread option offense, the Rush tab includes QBs, HBs, and WRs, the Rec. tab includes HBs, WRs, TEs)
- Search/replace all of your FR. TEMPLATEs and POS for each new player
- That takes care of every new player, but you need to update the old ones. Copy your schedule (only the schedule part of the template). Go to the top of the tab and search for your current year. This will take you to your 1st returning player. His "Opponent" section for the current year will be blank. Paste the schedule there and scroll down and paste the schedule for the current year for every returning player.
Once you're comfortable with the process, it only takes about 15 minutes to add every new recruit to your stat tracking.
So that's creating/updating the (S)ource tab info. The actual stats are manually entered every game. The easiest way to do this is filtering the tab (
FILTERING IS FINE, BUT NEVER, EVER SORT THE (S)OURCE TABS). Filter the "Game" column to only show the current season's game (IE. "Game 2022"). If you have few players for that stat (IE. Pass, Punt, KR) this is enough. For other stats filter it even further to only show your current opponent. Otherwise there'll be too much to show on 1 screen.
Enter the stats each game that aren't calculated for you (These will usually have "#DIV/0!" when no stats are entered) IE. QB Rating, YPC, etc. Enter the rest of the stats and your season and career totals are tallied for you.
Okay.... that's the (S)ource tabs. The non-source tabs are just copies of the source tabs. You'll need to manually link these every now and then (every season or few seasons or so). This is very simple to do. Simply highlight the entire Header row (IE. for the Pass tab highlight A1 "Player" through R1 "Games"), grab the square in the lower right of this highlighted section, and drag it down as far as you need to include every player in the associated (S)ource tab. These cells are all just copies of the (S)ource tabs. IE. in Pass A1, you can see that it "='(S)Pass'!A1"
Once the non-source tabs are populated you can sort/filter to your hearts content. You can see cool stuff like:
Highest career QBR w/ at least 100 completions
Most career yards after contact:
Most tackles per game in a season:
etc, etc.
I think (hope) that covers pretty much everything. Go ahead and explore the different cells and hopefully things you're unclear about will become clear.
I hope you enjoy it as much as I do, but again, I made this for me and it may not be designed in the most user friendly fashion, but it's designed in just about the most jello1717 friendly fashion (which is REALLY convenient for me, but probably not for you).