A Spreadsheet for the Coaching Nerd…

Follow this link to download an Excel spreadsheet that you may find useful to organize your coaching, position assignments, batting order, stats, etc. The sheet is rather extensive, and you can use as little or as much as you find useful. Below is a description of each of the pages in the spreadsheet and instructions on how to use it. If you have ideas or suggestions for improvements, contact David Chopp at . Of course, you should feel free to modify it however it suits you best.

When you load the sheet, you may be warned that the spreadsheet contains macros. None of the macros are essential, so if you are concerned about viruses, you may safely delete the macros without loss of functionality.

The workbook has 7 sheets, which are detailed below.

Attendance

This is where you should enter the names of the players on your roster. The names entered in the first column will be copied through the rest of the sheets so that you only have to enter them once. If you have any attendance requirements for your league, then you can also track attendance on this page.

What to enter:

  • Names in left column. Important: do not add/delete rows where players are listed. There are many formulas in this spreadsheet that will be affected if you do this. If you have more than 13 players on your roster, contact David Chopp to get a specially modified version. Leave blank lines if you have less than 13.
  • If you have a minimum practice attendance requirement, put the % of attendance requirement in box M3. Column M will then indicate which players have satisfied the requirement.
  • If you take attendance, put the dates of the practices in row 3, and place any text, e.g. “X” in boxes of those that attended practice, and leave blank those that did not.

Position Choices

Sometimes it’s helpful to remind yourself where kids want to play, especially to remember those that want to play pitcher, catcher, and/or first base. This sheet is a simple matrix that lets you record that information. There’s no formulas or other stuff on this sheet. It should be self-explanatory. Note that there is a position “SC”. If you are in a league that plays 10 fielders, this is the 10th fielder. It used to be called “Short Center”, hence the name.

Full Lineups

This is the main workhorse sheet for game planning, and it is also one of the most complex, so we’ll go through it carefully. On this sheet you will enter positions and batting order for each player, inning and game. If you follow these steps, you will hopefully find it useful.

What to enter:

  • First enter the dates of your scheduled games in row 1 beginning in box R1, followed by Y1, etc. It’s important to enter the dates in chronological order because a later sheet expects that.
  • Positions are entered for each player and inning in the array under the date of the game. E.g. for your first game, positions will be entered in R3:X15. The options to enter are: P, C, 1B, 2B, 3B, SS, LF, CF, RF, SC, X, W. The first 10 should be clear enough. The X means the player is unavailable, and W means the player is warming up to pitch later. A blank space also indicates a player is on the bench. Use X if, e.g. a player will be arriving late to a game or will be absent from a game. Use blank if the player is present, but sitting on the bench; it is equivalent to W for counting purposes.

To help, the middle rows will be yellow if the position is missing in that inning and red if the position is assigned twice. If you need the SC position to appear in the list, simply unhide row 23 to bring it back.

  • At the bottom is where you enter batting order information. Enter the numbers 1-13 or however many players you have. Leave blanks for players that will not be playing that game (or if you have less than 13 players). The batting order should be entered in the bottom section under the first inning. There are a couple things that can assist with this process. First, you can enter a random batting order by copying the numbers in cells L28:L40 and then pasting the numbers into the desired column. Alternatively, if you want to try and balance the batting order so that the those that have batted toward the end of the order are place first, copy the numbers in cells C28:C40 into the desired column. Of course, you can hand enter your own batting order as well.

Other information:

  • The array in B3:L15 counts how many times each player plays each position during the course of the season.
  • Sometimes it’s helpful to assign clean-up tasks to players after games. I have sometimes used the 7th inning to assign clean-up tasks instead of entering positions (this is for leagues with 6 inning games). In the 7th inning, you can enter “Helmets”, “Catching Gear”, “Bats”, “Balls”, or “Clean-up”. The first four should be clear, the “Clean-up” label is to assign the chore of cleaning the dugout or however else you see fit. This information will appear on the game-day sheet (see below), and will be tallied in the array M3:Q15 so you can share the chores equally. Be sure to do this after completing the field assignments because these labels will be counted as not sitting out an inning.
  • The cells B28:B40 show the average position in the batting order over the course of the season.
  • In the bottom section, below the second inning, the number of times each player is sitting out is shown. In some Mustang and Bronco, no player is supposed to sit out 2 innings more than another player. This spot will help in keeping this straight. Use “X” label for players that are absent so that this data will be correct. For example, if John is going to miss the game, and the remaining players will each sit out at least once, then place an “X” in innings 1-5 (for a 6 inning game) so that he is counted as sitting out one inning too. A “W” or blank indicates present, but sitting out.

Game Lineup

Once the information is entered in the Full Lineups sheet, the Game Lineup sheet should be populated for the next scheduled game. Simply print this sheet and post in the dugout so that players can see where they are playing, what the batting order is, and what their post-game clean-up duties are. Doesn’t hurt to print a second copy for the bench coach too. Note that clean-up duties also include “fence”. This only applies to Bronco games where the players should help put away the fence.

Only players who are assigned a spot in the batting order will appear on this sheet. If a player will be absent for a game, don’t assign them a spot in the batting order in the Full Lineups and they won’t appear on this sheet.

The Game Lineup page also checks for the main league rules (Mustang and Bronco) concerning positioning of players in the field. The following three tests are performed:

  1. Each player must play 2 innings each at 2 different positions, or 3 different positions (outfield positions are all considered the same so playing once in left field and once in right field is considered playing twice at the same outfield position).
  2. No player shall sit out 2 or more times more than any other player.
  3. Each player should play in the outfield at least once.

If one of these rules is violated, a red note will appear next to the player(s) in violation along with an explanation of the problem.

What to enter:

  • If you wish to print the Game Lineup for a game that is not next in the schedule, you can enter the date of the game in box T1. Leave that box blank to get the default next scheduled game.

Stats Sheet

This sheet is completely optional, but there for the truly die-hard coaches. However, it can be a useful exercise for two reasons. First, it will help in tracking player improvement and will offer insight on how to choose your batting order when going into the playoffs. Second, it can be used to give highlights of player accomplishments at the end of the season. Players always enjoy hearing something special about their play at the end of the season, and this is a way to give some real data. Cherry pick the best highlights for each player from this sheet. For travel coaches, this can be a very useful tool to help plan lineups and batting order.

There are two main sections to this sheet, the offensive stats (top section) and the pitching stats (bottom section).

What to enter:

  • After a game, offensive stats can be entered for each player under the specified game date. Currently, it assumes that players have no more than 6 at bats during a game (fairly safe assumption). In the first 6 columns under the date of the game, you may enter any of the following batting results, “1”, “1H”, “2”, “2H”, “3”, “3H”, “HR”, “BB”, “E”, “EH”, “K”, “CK”, “GO”, “GOH”, “FO”, “FOH”, “FC”, “FCH”. These correspond to single, hard-hit single, double, hard-hit double, triple, hard-hit triple, home run, walk, reach on error, reach on error for hard-hit ball, strike out swinging, strike out looking, ground out, hard-hit ground out, fly out, hard-hit fly out, fielders-choice, hard-hit fielders-choice. The “hard-hit” designations are optional, but some coaches like distinguishing hard-hit balls from weakly-hit balls independent of outcome. Note that the entries do not need to be entered by inning, but simply entered sequentially as shown in the diagram.
  • Under the “R” and “SB” columns following the game date, you can enter the number of runs and stolen bases for each player.
  • Pitching stats for a game are entered in the lower section under the game date. Data is entered for each stat and for each pitcher. The headings are, in order, innings pitched (e.g. enter “=5/3” for 1 2/3 innings pitched), strikes thrown, balls thrown, strikeouts, walks, hits, runs, earned runs.

Other information:

  • Stats are totaled in columns B through R. For the offensive stats, they correspond to: plate appearances, at bats, singles, doubles, triples, home runs, total hits, walks, reach on error, hard hit balls, total strikeouts, strikeouts looking, runs scored, stolen bases, batting average, slugging pct., and on base average.
  • For the pitching stats, the columns correspond to innings pitched, total strikes, total balls, strike percentage, strikeouts, walks, hits, runs given up, earned runs given up, average runs given up per game, earned run average.
  • Some data will be automatically color-coded. Generally green is good, red is bad, and yellow is in the middle.

Goal Sheet

Some coaches may like to have a sheet of player goals for the season, a game, whatever. This is a printable sheet you can use to note player goals. There is nothing to enter in this sheet.

All-star ballot

For some of the leagues, the team players vote for their representatives to the league all-star game and also for the sportsmanship award. This sheet is a simple printable ballot that can be handed to players to fill out. There are different ways to use this sheet. You can have players enter ranked order of choice or checks for their choice(s), or whatever. There is nothing to enter on this page, it is just for printing purposes.