Baseball Lineup Generator in Excel for Fair Playing Time

Every youth baseball coach knows the Saturday morning problem: twelve kids, nine positions, and parents watching closely to see who sat on the bench twice. A baseball lineup generator in Excel takes the guesswork out of it. In this guide you will build a sheet that rotates the batting order from game to game, moves every player through the fielding positions, and spreads bench time so evenly that no child sits more than one inning more than anyone else. All it takes is a few formulas, plus an optional VBA macro to shuffle the roster at the start of the season.

I built a version of this for a coach a while ago (you can see it in my baseball lineup generator project), and the core idea is simple enough to rebuild yourself in about half an hour. It works in any recent version of Excel, including Excel 2016 and 2019.

How the fair rotation works

Before typing any formulas, it helps to understand the logic, because it is what makes the lineup fair rather than just random.

Imagine a list of “slots” as long as your roster. The first nine slots are the real positions (P, C, 1B, 2B, 3B, SS, LF, CF, RF) and the rest are Bench. With 12 players there are 12 slots: nine positions and three bench spots. In inning 1, player 1 takes slot 1, player 2 takes slot 2, and so on. Every new inning, everyone moves forward by the number of bench spots and wraps around at the end of the list.

Moving forward by the number of bench spots is the trick. It means the three kids who just sat out move straight onto the field, and a fresh group of three takes the bench. The result:

• Every inning has exactly one player at each position, because each player gets a different slot.
• Nobody sits two innings in a row.
• Over a game, and over the whole season, bench innings differ by at most one between any two players.

If you have exactly nine players, nobody sits, and the sheet simply rotates everyone one position per inning.

Set up the roster and settings sheets

1. Create a sheet named Roster. Type “Player” in A1 and the player names in A2 downward, with no gaps. Leave room for up to 15 players.
2. Select A2:A16 and type Roster in the Name Box (left of the formula bar) to create a named range.
3. Create a sheet named Settings. In A2:A16, type the slot list: P, C, 1B, 2B, 3B, SS, LF, CF, RF, then Bench in the remaining six cells. Name this range Slots.
4. Still on Settings, add these single cells and give each a name with the Name Box:

The order of the nine positions in the Slots list matters more than you might think. With 12 players, each child cycles through every third slot, so with the order above one group plays P, 2B and LF, another plays C, 3B and CF, and the third plays 1B, SS and RF. Each group gets a mix of infield and outfield. Reorder the list if you want different combinations.

Lineup formulas for batting order and fielding

Rotating batting order

Create a sheet named Lineup. In A6:A20 type the batting spots 1 to 15. In B6, enter this formula and fill it down to B20:

=IF(ROWS($B$6:B6)>PlayerCount, "",
    INDEX(Roster, MOD(ROWS($B$6:B6)-1 - (GameNo-1)*BatShift, PlayerCount) + 1))

This uses a continuous batting order, where everyone bats, which is the norm in most youth leagues. ROWS($B$6:B6) is the batting spot (1, 2, 3…). Each new game, the order rotates by BatShift places, so with BatShift at 3 the last three batters of game 1 lead off game 2 and everyone else moves down three spots. Excel’s MOD always returns a positive result here, so the subtraction wraps around correctly.

Fielding positions and bench

On the same sheet, type the inning numbers 1 to 6 in C5:H5. In C6 enter the formula below, then fill it right to H6 and down to H20:

=IF($B6="", "",
    INDEX(Slots, MOD(MATCH($B6, Roster, 0) - 1
        + ((GameNo-1)*Innings + C$5 - 1) * Step, PlayerCount) + 1))

Here is what each part does:

• MATCH($B6, Roster, 0) finds the player’s place on the roster, so the fielding rotation follows the player, not the batting spot.
• (GameNo-1)*Innings + C$5 - 1 counts innings across the whole season, so game 2 continues the rotation exactly where game 1 stopped. That is what keeps bench time fair over the season, not just within one game.
• Multiplying by Step moves everyone forward by the bench size each inning.
• MOD(..., PlayerCount) + 1 wraps the slot number back to the start of the list.

In I6, count bench innings per player with =COUNTIF(C6:H6, "Bench") and fill down. Then add a conditional formatting rule to C6:H20 that shades cells equal to “Bench” in light gray, so the dugout list jumps out on the printed sheet.

Quick fairness check. Under the grid, in C22, add =COUNTIF(C6:C20, "P") and fill right. Every value should be 1. Swap “P” for “C” or any other position to check those too. And =MAX(I6:I20)-MIN(I6:I20) should never be more than 1 for a full roster. If either check fails, look for a duplicated or misspelled name on the Roster sheet, because MATCH needs every name to be unique.

Optional VBA macro to shuffle the roster

The formulas are fair but predictable: player 1 always starts at pitcher in the first game. If you want a random starting order at the start of the season, this short macro shuffles the names on the Roster sheet using the Fisher-Yates method, which gives every possible order an equal chance.

Option Explicit

' Randomly reorders the player names on the Roster sheet.
' Run once at the start of a season (or a new rotation cycle).
Sub ShuffleRoster()
    Dim RosterRange As Range
    Dim PlayerNames As Variant
    Dim PlayerTotal As Long, i As Long, j As Long
    Dim TempName As Variant

    Set RosterRange = ThisWorkbook.Worksheets("Roster").Range("A2:A16")
    PlayerTotal = Application.WorksheetFunction.CountA(RosterRange)
    If PlayerTotal < 2 Then Exit Sub

    ' Read only the filled cells into an array
    PlayerNames = RosterRange.Resize(PlayerTotal, 1).Value

    Randomize
    For i = PlayerTotal To 2 Step -1
        j = Int(Rnd * i) + 1          ' random position from 1 to i
        TempName = PlayerNames(i, 1)
        PlayerNames(i, 1) = PlayerNames(j, 1)
        PlayerNames(j, 1) = TempName
    Next i

    RosterRange.Resize(PlayerTotal, 1).Value = PlayerNames
End Sub

Press Alt+F11, choose Insert, Module, paste the code and save the workbook as .xlsm. You can then add a button on the Settings sheet (Developer, Insert, Button) and assign the macro to it.

One warning: because every lineup is calculated from the roster order, shuffling mid-season changes the lineups of past games too. Before shuffling, keep a record of earlier games by copying each printed lineup and pasting it as values on a History sheet.

Common mistakes to avoid

• Gaps in the roster list. COUNTA counts filled cells, so a blank row in the middle breaks the count. Keep names in one unbroken block.
• Duplicate first names. Two players called “Sam” confuse MATCH. Use “Sam R” and “Sam T”.
• Forgetting to change GameNo. If GameNo stays at 1, you print the same lineup every week.
• Adding a player mid-season without thinking. A new name changes PlayerCount and resets the pattern. Add them at the bottom and accept a small imbalance for that game.
• Manual swaps that break fairness. If a child cannot pitch, swap them with another fielder in the same inning, never with a bench player.

Frequently asked questions

Does this work in Google Sheets?

The formulas work in Google Sheets as written, including named ranges, MOD, INDEX and MATCH. The VBA shuffle does not, but you can shuffle names with Data, Randomize range instead.

What if only some kids can pitch or catch?

Let the formula build the fair base lineup, then swap positions between two fielders in the same inning. That keeps bench time unchanged while respecting the restriction.

Can I use it for softball or 10-player fields?

Yes. Add the extra position (for example a short fielder) to the top of the Slots list and change the 9 in the Step formula to 10.

How many players does it support?

As built, up to 15 players, which covers almost every youth roster. Extend the Roster, Slots and Lineup ranges if you need more.

Want a ready-made version?

If you would rather have a polished, printable lineup tool with pitching limits, season bench totals and one-click game history, I build custom workbooks like this through my Excel VBA automation service. Get an instant quote and tell me how your league works.