Conditional Formatting - Baseball Lineup


New Member
Oct 10, 2014
I'm a youth baseball coach and I'm trying to make creating my lineup or player positions easier. What I want to do is this:
I have a small table set up for each position (LF, CF, RF, 1B, 2B etc). At each of these I have the table set up with 6 blank rows.
Off to the side I have 6 columns labled 1 thru 6. In each of these I have my players first name listed (12 players).

When making a position rotation, the frustrating part is as you go, unless you're marking the names off of a list you lose track of who you already put down and who you haven't put down yet. This is exactly what I'm trying to do. I want to be able to type in "Chris" in the first slot (first inning) at catcher and Chris' name automatically highlights yellow under the "1" column to the right. Thus indicating that for the first inning, Chris has already been placed and then I can move on to the next one.

I'm pretty good with Excel and I've been trying all kinds of ways to do this, but can't make it work.

If it's possible I can send you what I have now, so you can see exactly where I am.


Some videos you may like

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

General Ledger

Active Member
Dec 31, 2007
I might try this. On a single sheet in Excel:
1. Have a grid area that has positions down the first column and innings across the first row. In this matrix, enter the names of the players for the various positions and innings.
2. Skip a few rows down.
3. Have a grid area that has player names down the first column. In this matrix, enter a COUNTIF formula that counts the number of times the player name appears in the same column in the above rows. Since a player should only be in one position for a particular inning, each player should have a count of 0 (if not playing that inning) or 1 for each inning.

Add a sum formula at the end of the row of each player to sum the 0's and 1's across all innings. This will total the number of innings a play played.
Add a sum formula just below the rows of player names to sum the 0's and 1's for each inning. This will total the number players assigned to positions in the inning.

Bonus: You can use Data Validation to ensure names entered in the matrix of positions and innings match the names of players in the list of play names below.

Best of luck,


Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics