Excel users: Try my spreadsheet for keeping baseball stats

image

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:

player 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.

pitcher page

 

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…

team page

…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.

imageYou’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. 

Posted by: | Category: Resources | Tags: , , | 33 comments

  • Novauofm says:

    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

  • Jim Currie says:

    Thanks Tom, it’s GREAT!!

  • TJ23 says:

    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! :)

  • TJ23 says:

    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. :)

      • TB says:

        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.

  • Rob Poole says:

    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

  • Brett says:

    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.

  • Frank says:

    Love this spreadsheet! I am using it to keep stats on my 13 year old girls softball team. Thanks for the effort!

  • Anonymous says:

    ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
    ?? ????????????????????????????????????????????????????????????????????????????????????????? ?? ???????????????????????????????????????????????????????????????????????????????IWC????????????? http://www.okakaku.com/brand-8-copy-0-cheap-0-max0-attr0-3-sort_order%20Desc%2cgoods_id-DESC.html

  • Mike Moore says:

    What is the cost of this program?

  • michael says:

    what does SH, GS and CYL stand for in the offensive categorie?

    • Jan says:

      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.

    • Henry Hernandez says:

      SH = Sac Hiy
      GS = Game Started (assuming tracks the number of games)

      looking for CYL.

  • John G. says:

    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?

  • Mike Loomis says:

    Thank you!

  • bb says:

    reached on error?

  • edward says:

    hi, thanks for this program
    im in charge of a little league stats
    can you help me set up for a league ?

  • Gabriel - Astros Softball team - Argentina says:

    Good job!!!

  • George Grieger says:

    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.

  • Jos says:

    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

  • Heidi says:

    This program is great! Thanks for sharing it.
    Is there a sheet for defensive stats?

  • […] Thomas Nelshoppen shared his workbook for keeping baseball statistics. […]

  • […] Excel users: Try my spreadsheet for keeping baseball stats … – About this Entry. Excel users: Try my spreadsheet for keeping baseball stats was posted on May 28th, 2014 at 6.35pm and is filed under Resources. This entry has 20 … […]

  • […] Excel users: Try my spreadsheet for keeping baseball stats … – About this Entry. Excel users: Try my spreadsheet for keeping baseball stats was posted on May 28th, 2014 at 6.35pm and is filed under Resources. This entry has 22 … […]

  • […] Excel users: Try my spreadsheet for keeping baseball stats … – About this Entry. Excel users: Try my spreadsheet for keeping baseball stats was posted on May 28th, 2014 at 6.35pm and is filed under Resources. This entry has 24 … […]


  • Leave a Reply

    Your email address will not be published. Required fields are marked *