To capture the action during play, I would lay out columns in the following order, based on how early in a play you will have the relevant info:
A: team with possession at the start of the play
B-c: Ball on what yardline. B="OWN" or "OPP", C=number
E: Distance to go
All of which you will know before the snap. Next columns:
F: Offensive play type (Pass, Run, PUnt, etc.) Use abbreviations that make sense to you, and use them consistently. You may want more detail, such as SCreen, OPtion, DRaw, etc. As long as you are consistent.
G: Player. Receiver for a pass play, ball carrier for a rushing play.
H: Passer. This is important if a team uses more than one QB in a game.
Those are arranged in roughly the order they happen. Now for the play results:
I: Complete/Incomplete pass
J: Defensive play type (Tackle, Sack, Forced Fumble, Interception, Blocked pass/punt/kick...) Again, use abbreviations and be consistent.
K: Defensive player who made the tackle/interception/forced the fumble...
L: Yards attempted (for incomplete passes) or Yards In Air for completed passes (we can use this later to calculate yards-after-catch)
M: Yards gained. Use negative numbers for losses.
N: Points scored on play
O: Turnover type (D=turnover on downs, F=fumble, I=int)
And finally, the items that happen after the play is whistled dead:
P-R: offensive penalty (Player number in P, infraction in Q, yards assessed in R, with zero yards meaning declined)
S-U: defensive penalty (same pattern)
|29||Team||Ball on||Down||Yds to go||O play||ball carrier||Passer||Complete/Incomplete||D Play||D Player||Yds in air||Play Yds||Points||T/O type||O Penalty||D Penalty|
Then comes the fun part: calculating the stats during the game. By separating the play into 21 columns (so far), we can perform calculations against lots of individual stats, using primarily the SUMPRODUCT function. We can, for example, determine how many rushing plays have gone to player ##, how many yards total he has rushing, how many yards he has receiving, number of pass attempts to him, yards per touch, etc. Because we recorded the down, we can calculate 3rd down conversion rate, 4th down conversion, number of first downs, etc. If we add a "Game Clock" column, we can calculate length of drive, time of possession, etc. On the other side of the ball, we can calculate a given defensive player's number of tackles, sacks, etc. If we add a set of cells somewhere else to hold "Season to date" and "Per Game Average" numbers, we can add/compare today's game numbers to those for tracking things like a player nearing a school/conference/state record, or if today's numbers represent a season high or personal best, etc.