As I’ve begun my little 21-game project between the Monday Monsters and the Tuesday Terribles, I of course wanted to keep stats. That was part of the fun, right? To establish how bad the Terribles were as opposed to the mighty Monsters.
But did I really want to re-invent the wheel? Did I have to re-create a whole new stats environment within Excel for a simple 21 game project?
Fortunately, no. I really just had to copy one team spreadsheet from my current 1966 replay stats setup and use that. I made a copy and renamed it to something more appropriate. Then I cleared all stat data from it including players’ names. Once I started filling in the names and stat data from the MCM-TCT project, it worked like a dream.
Below, I’m linking my team spreadsheet that I’m using for the MCM-TCT project. I believe I’ve made a version of this available before but since then, I have refined it a bit and made some revisions that make it work for me a lot better.
Before I go on, I know a lot of you have used BallStat and are quite happy with it. It honestly sounds like quite a program and I tell myself I really should try it some time just so I can properly review it. For those interested, Jeff Pappas did review of BallStat on The APBA Blog a while back. For now, I’m pretty tied to my method of keeping stats using Excel simply because I can customize to my needs. And quite honestly, I like to tinker. Just playing with the method of keeping stats is part of the fun for me.
A quick look
Because the Monsters-Terribles project is a little unconventional to put it mildly, I’ve created a spreadsheet with some fictional names who play for the “Hometown Champions” and some random stats in the sheet just so you can get a sense of how it works. You can delete the stats from the individual players’ worksheets and use it how you wish.
Note: when you delete the stats for your own use, delete them from the player’s worksheet NOT from the Team worksheet.
The players entry page:
With the system that I use, each file represents one team and each player has his own worksheet within the spreadsheet. Almost all stat entry takes place in these worksheet pages.
One of the features I added was a instant total line at the top so it gets immediately calculated as you enter in stats. It certainly wasn’t necessary but I liked it. Also, the formatting of the ‘Game’ column can be in number format as shown above or date depending if you’re doing a tournament/short season or a more strict season replay.
As I mentioned, each player and pitcher on the team has his own worksheet. Here’s an example of a pitcher’s worksheet.
Now on to the Team sheet. Once I got this worksheet set up the way I wanted it, I don’t edit this sheet in any way. All data is linked to this sheet from the individual players’ sheets described above. If you add a game’s stats to Jerry Ribbie’s sheet for example, his totals immediately and automatically get updated on this sheet. This is how it looks with just three hitters entered…
…and if you want add another hitter, you can do so. If you navigate to the sheet just past Homer Batta (by the way, Control-Page Down or Page Up is very handy in doing this), you can enter the new player’s name and stats and they will show up on the Team sheet in the row right under Homer Batta.
For both hitting and pitching, the team stats automatically get updated and displayed below the players’ stats.
You’ll note that I have a column for Team and have the team initials “HC” (for Hometown Champions) in every field. Why would I do that if everyone is on the same team? Well, if I am doing a league or tournament project, I will usually link the stats from the team sheet to a master spreadsheet. Doing this will allow me to carry the team affiliation along with the player. This is handy for leaderboards and such. See the example pivot table from my Monsters-Terribles project at right for an example.
That is definitely a topic for another article but if you are interested, look into pivot tables. If you like to play around with Excel spreadsheets, they are a fun way to do leaderboards.
I generally calculate BA, OBP, SLG and ERA on the team sheet but if you want, you can insert any formula you want. There’s K/9 IP, BB/9 IP, K/BB ratio, it’s really up to you. I do a lot of these on the fly.
Download the Spreadsheet
That should be enough to get you started if you’re interested in trying this out. Here is the link to the actual Microsoft Excel file I’ve been referring to in this article. If you like it, feel free to use it. Just clear out the fictional names and data from the individual worksheets and put in your own names and stats from your project. You can use the spreadsheet on its own or as I’ve mentioned above, link it to a master league spreadsheet which totals the league stats, puts together a players registry and calculates leaderboards. I go into a little detail on this with a series of articles I did a while back.
If you have any questions, ideas or suggestions, please leave a comment. I hope this is helpful to some of you out there.
A lot of credit for this spreadsheet goes to Mike Bunch and John Brandeberry. One of them (not sure which) created the template for this spreadsheet a long time ago and both them have made improvements to it before I took it and made my own changes.
Tom,
I started doing stats with pencil and paper. Then used an Apple II GS, then excel. I recently adopted your methods. Then leader boards got so much easier – but I still haven’t figured out how to get the team name included. Doing stats got easier, too. I also like how you can see recent performance/trend. So Thanks! Bob
I started with pencil and paper too. Graph paper even. Don’t forget the typewriter. I had a Smith Corona. I used Liquid Paper when I made a mistake or if I could get away with it, I’d type over the mistake. :)
As for leaderboards, I link all league stats into one table (including Team names or initials). I used to do leaderboards manually by just sorting by category then copying and pasting. That was fine but pivot tables do it automatically. This video discusses pivot tables about 2/3 the way in. It’s about the best tutorial I can find.
http://www.youtube.com/watch?v=YiNHBeu_WJI
Can not get the excel program to recognize batting ave., ob, slugging era, and whip after inputs. Please send tips to correct. I am not savvy with excel so send step by step techniques to get results.
THanx, steve
Pingback: Excel Roundup 20140602 « Contextures Blog
Thanks Tom, it’s GREAT!!
Great spreadsheet, very easy to use. My question is, if I wanted to add in some stat categories such as CS, OPS, WHIP, BS, etc., how do I go about doing that so it links between the player pages as well as the main team page. Thanks.
It wouldn’t be too difficult. Just add the columns (stat categories) to each players page then make sure to add the total at top (or bottom your preference).
Then copy and and “Paste as link to the apprpriate cell on the team page for each player.
If doing the first step for each player seems a bit tedious, you can apply the changes to all the players’ pages if you “group” their worksheets (highlight the sheet then shift-click on the last sheet in the range). Then all changes you make will affect every sheet in the group.
Make sure you “ungroup” when you’re done though! :)
Thanks. Also, can I add more rosters spots than the 18 hitters and 14 pitchers. I’m having a hard time trying to figure that out. Sorry for the questions, I’m not too Excel savvy.
If you want to add another player, just add another worksheet (I just copy an existing worksheet and edit it to save time). Then insert a row in the team sheet and copy the the totals row from the player’s page and “paste as link” in that row.
A lot of doing this is establishing templates and reusing them. For example, once you have one player page, you don’t have to do another. Just copy that one and edit that one to work for that player.
Also, if you have multiple teams, once you have a team page set up, just copy that file and edit that file and rename the players. No need to re-invent the wheel each time. Excel has built-in shortcuts like that.
And I always like to remind people. Control-page down and page up will navigate between the worksheets. No need to go from keyboard to mouse all the time. :)
I followed your instructions on adding players, but it’s not calculating as the others. When I change the persons name on the added sheet, it doesn’t update the team sheet. Neither does the stats. Any guidance on what I need to do would be greatly appreciated. Thx.
walks and hit batsman do not calculate properly according to the version I downloaded.walk and HP’s should not count as at bats.
for instance, if you enter 4 at bats, 2 hits and 2 walks batting average should be 1.000 however, the sheet shows .500 average
Rob – I think the problem is you’re entering a walk as a valid ‘at bat’. Base on balls do not count as a hit or an at bat (just plate appearance and time on base)
Have you figured this out yet?
Change the formula in Q3 to =F3/(D3-K3) and Q21 to =F21/(D21-K21). This will subtract BB from the AB.
I’m confused about the “H” category. If a player hits a double, am I to enter a value for both “H” & “2B”? When I enter in only the “2B” value, the batting average doesn’t change, but the slugging % does.
Hi Brett,
Yes, enter in a value for the Hit column. I could have conceivably had “singles” column and had it calculate as you describe. I’ve just been used to using the Hits as its own column.
Doesn’t that cause you to count hits twice for slugging %? I would think that changes your output.
Love this spreadsheet! I am using it to keep stats on my 13 year old girls softball team. Thanks for the effort!
What is the cost of this program?
what does SH, GS and CYL stand for in the offensive categorie?
I had the same question. Best I could finder were Sacrifice Hit, could be bunt or fly; Games Started, versus games played; and then guessing hit for the cycle? However, that would be a rare state to devote a whole column too.
SH = Sac Hiy
GS = Game Started (assuming tracks the number of games)
looking for CYL.
I like your program – nice and simple, if you want it that way. One question: If a pitcher goes 6 2/3 innings, for example, how do you get the IP column to accept the 2/3 and enter it into the calculations?
Pingback: Hockey Stats Template Excel Spreadsheet | Shop Hockey!
Thank you!
Pingback: Hockey Stats Worksheet | Shop Hockey!
Pingback: Keeping Hockey Stats | Shop Hockey!
reached on error?
hi, thanks for this program
im in charge of a little league stats
can you help me set up for a league ?
Good job!!!
Your worksheet is excellent. Can I simply add a column for total bases by a player? I understand slugging % but this would be helpful. Also can I widen the first column to insert the opposing teams name and carry it across the worksheets as a template? Thank you.
George I did that. I added a column for each player labelled TB (obviously).
My formula is =(E4-(F4+G4+H4))+(F4*2)+(G4*3)+(H4*4)
E4 = Hits column
F4 = Doubles
G4 = Triples
H4 = HR
Then I modified the SLG formula to L3/C3
C3 = AB (I chose to use AB instead of PA for simplicity sake)
L3 = TB
How do you add more players to show up months grand total and have it still do the calculations? I’m a complete failure with excel
This program is great! Thanks for sharing it.
Is there a sheet for defensive stats?
And I’m the Aunt of Illini alumni, DK #12
still trying to figure out the CYL stat, and am i missing it or is there a caught stealing column?
Your columns that perform a division function (AVG, SLG, OBP,ERA) need to be modified to eliminate the #DIV/0! errors.
For example: For AVG change it from +F3/D3 to =IFERROR(F3/D3), 0)
Thanks for this Thomas!
1 question- the OB% doesn’t seem to calculate correctly.
For example, the first game 1 batter had 3 AB and 3 Walks….his OB% is calculating as .500 but it should be 1.000
Let me know if there is an easy fix.
Again- really appreciate this!
It’s not an AB when you walk.
How do I add extra lines for players? I have 22 guys on my squad.
I am going to start using this for our rep team this summer. I cant figure out what CYL and GS are acronyms for on the batters sheet.
Can anyone help me with that.
Looks like this question has been asked previously.
Ways Sports Reference Can Help Your Website, a blog post listing tools and resources we provide for writers, publishers and other users of the sites.
Great worksheet! Is there a column for reaching base on a dropped third strike? It is helpful in little league. Thanks!
Thank you for this. I play SherCo table top baseball, but it works just as well for that game. I added a separate column for total bases to use when calculating SLG. It gives another raw data total to look at as well.
Looking at how to combine my four team spreadsheets into a leaderboard. Anyone have any solutions for that?
Cant figure out how to make this work right. I had to add a few fields PA, 1B and HBP for pitchers but even when copying across the formulas they are not populating on the main sheet.
This is great!!! Thanks so much for creating it and taking the time to explain it! ???
Did the formula for Slugging Percentage change since you wrote this spreadsheet 10 years ago? I looked it up on mlb.com and they state the formula is (1B + 2Bx2 + 3Bx3 + HRx4)/AB.
The version of your spreadsheet calculates it as (1B + 2B + 3Bx2 + HRx3)/AB
Thanks for the help and for sharing your knowledge and tools!
Hi Xander,
Either is fine. I just do it a slightly different way. Since I don’t have a specific column for singles, I calculate:
Hits + Doubles + 2*Triples + 3*HR
hope that helps!
Tom
Tom,
Trying to use your spreadsheet for my grandson’s Little League team. However, it does not include HBP and BB in batting average. Example: AB 7, Hits 2, BB1, HBP 1. Spreadsheet reflect BA: 286, OBP: 375. HBP and BB needs to be subtracted from ABs which would reflect a correct BA: .400. They should be added to ABs which would to correct OBP: .571. I have doing this manually, but was wondering if you have an update to alleviate me from manually updating.? Thanks