It's the most wonderful time of the year - college football bowl season! The college football conference championships were played this past weekend which means the 2024 NCAA college football bowl season is here again. It’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season is being able to talk trash to your relatives about their terrible bowl picks.
This year has the added bonus of the new, expanded college football playoff - growing from four to twelve teams. As such, I've included a college football playoff bracket within this Excel file. There is the option to play just the bowls, just the playoff bracket, or both! If you've used my Super Bowl Squares template, you'll be familiar with how it works - there is a setup screen where you pick your options then click a button and the spreadsheet adjusts to how you want to play.
The bowl prediction sheets include the football helmet designs for every team (taken from my college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!
There are multiple ways to play college football bowl pick'em. What I call "regular" is every game is worth 1 point. The other way I call Confidence Picks: each player "bet" points 1 to 35 on each game based on how confident they are their choice is correct. Player with the most points wins Example: player bets 35 points and gets it correct = 35 points. Bets 25 points on another game, gets it wrong = 0 points awarded.
For the college football playoff bracket, the pool manager can decide if each game is worth the same amount (1 point) or if each round the points increase: round 1 = 1 point, round 2 = 2 points, etc.
I was thinking about trying to transfer one of my most popular spreadsheet templates, Super Bowl Squares, from Excel to Google sheets. However, macros do not work in Google sheets. I was thinking about trying to recreate at least some of them, and the first problem to solve was how to make a random number generator script in Google Sheets.
Here's how you can create a Google Apps Script to generate random numbers between 0 and 9 in cells A1 to A9 without any repeats.
Steps to Create the Google Apps Script:
Open your Google Sheet.
Click on Extensions in the menu.
Select Apps Script.
Delete any existing code in the script editor, and paste the following code:
functiongenerateRandomNumbers() {
// Create an array with numbers 0 to 9var numbers = Array.from({length: 10}, (_, i) => i);
// Shuffle the arrayfor (var i = numbers.length - 1; i > 0; i--) {
var j = Math.floor(Math.random() * (i + 1));
var temp = numbers[i];
numbers[i] = numbers[j];
numbers[j] = temp;
}
// Get the active spreadsheet and sheetvar sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Place the shuffled numbers in cells A1 to A9for (var k = 0; k < 9; k++) {
sheet.getRange(k + 1, 1).setValue(numbers[k]);
}
}
Save the script by clicking the disk icon or pressing Ctrl + S. You can name it something like RandomNumberGenerator.
Close the Apps Script editor.
Back in your Google Sheet, go to Extensions -> Macros -> Import and then select your generateRandomNumbers function.
How to Run the Random Number Generator Script:
To run the script, go to Extensions -> Macros -> generateRandomNumbers.
The script will place random numbers between 0 and 9 in cells A1 to A9, with no repeats.
If this is your first time running the script, Google Sheets may ask you for permission to run the script. Approve the permissions to proceed.
In my templates, I make macros easy to use by running them from a button click. Yes, you can run the script from a button in Google Sheets too! Here’s how you can set it up:
Step 1: Create the Script
Open your Google Sheet.
Click on Extensions in the menu.
Select Apps Script.
Delete any existing code in the script editor, and paste the script provided above.
Save the script by clicking the disk icon or pressing Ctrl + S.
Step 2: Add a Button to Google Sheets
Insert a Drawing (for the Button):
Go to your Google Sheet.
Click on Insert -> Drawing.
Click on the Text Box icon in the Drawing toolbar and draw a text box.
Type in a label for your button, such as "Generate Numbers."
Format the text and shape as you like.
Click Save and Close. The button will now appear on your sheet.
Assign the Script to the Button:
Click on the drawing (the button) you just created.
Click on the three vertical dots in the top right corner of the button, and select Assign script.
In the text box that appears, type the name of your script function, which is generateRandomNumbersInRow.
Click OK.
Step 3: Use the Button
Now, whenever you click the button, the generateRandomNumbersInRow script will run, and random numbers will be placed in the cells G3 to P3.
This provides a user-friendly way to trigger the script without needing to go through the menu every time. The first problem is solved! Onto the next one...
There are two primary methods most websites appear to be ranking the 2024 medal count. Most sites rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. And others rank by other factors like per capita or GDP.
Pictured below is a bar chart showing all medals won for the top countries (as of the time of this posting on 2-14-22). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.
I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, previous Olympic results suddenly became quite interesting. However, for the 2024 Summer Games not too much actually changes (so far, will revisit after more events are completed). The first five countries ranking is unchanged. It gets really interesting in the 25-35 range.
I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country. How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!
If you've been following me for any amount of time you know I love a good football spreadsheet game, like Super Bowl Squares or College Football Bowl Pick'em. Recently a reader made me aware of a new game I had never heard of before and I just had to turn it into an Excel template to play this fall.
Introducing the NFL 33 Pool spreadsheet!
Here's how to play: players are assigned an NFL each week of the 18 game season and if your team scores 33 points, you win!
But that's not all. If that was the only way to play the spreadsheet would have been far too simple to make. My Excel template allows the pool manager to select different options so you can play how you want to.
First, select the number of players, which can be between 1 and 32.
Second, choose if you want the target score to be the same every week, or change every week.
Next, choose if the winner requires hitting the exact target score, whoever gets closest on a weekly basis, or add up the scores for the whole season.
Finally, choose if you want players to keep the same NFL teams all season or to be assigned a different team every week.
How to Play NFL 33 Game
Watch the video below to learn how to play football 33 and see how the spreadsheet works:
How the NFL 33 Pool Spreadsheet Works
In the Pool sheet from column C to T a random number is generated. Each number corresponds to team on the Lists sheet. Change the numbers manually if you want to manually change the teams. Uses conditional formatting to turn cell green if scores exactly match. This sheet uses macros. Macros must be enabled. Document must be saved in trusted location. Go to Trust Center settings to modify if needed.
I've sold 227 items on Facebook marketplace. Here's what I've learned:
The day of the week when I make the most sales on Facebook marketplace is Wednesday, followed by Tuesday and Sunday. The worst day of the week for sales is Friday, followed by Saturday and Monday.
I've been selling on Facebook Marketplace since July 2020 and by far the best months for most sales have been November and December, with February and April being the worst.
56% of my sales have been picked up by the customer in-person, while 44% paid for shipping on top of the cost of the item.
The average length of time to sell an item on Facebook Marketplace is 112 days. The longest sale took 828 days (or 2.2 years)!
54% of customers will try to barter or negotiate a lower price than what you have initially listed the item for. On average, customers will try to reduce your price by 20%, so you should account for this when listing your items.
Example: if you want to make $50 off a product, list it for $60.
I recently updated my template. You can now use this one spreadsheet to track sales for various marketplaces, such as Mercari, Ebay, Craiglist, Whatnot, etc. Previously you had to manually drag down formulas and added new listings at the bottom. Now you simply click a button and a new row is automatically added via VBA macro at the top for you to add the listing info. All the data is in the Summary sheet and update automatically via Excel formulas.
Have you ever sold anything on Facebook Marketplace and if so do you track your sales? Have any tips for me or questions? Let me know via comment or email!
The 2024 NFL schedule was released yesterday and since I used my Excel skills to automate the creation of this spreadsheet I was able to create the NFL Helmet Schedule in less than a minute! All 32 teams, all 18 weeks of the season (now 17 games plus one bye week), all in one spreadsheet. But instead of looking like this boring mess:
Watch the video below to see just how easy it is to update the NFL helmet schedule spreadsheet since it has been automated:
As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!
Please note, an email is required to download it. I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.
As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?
Choose between American or Decimal odds by using a drop down list on the first sheet. All bets need to use the same odds type and you shouldn't change it once set.
What's the difference between American and Decimal odds?
Decimal odds (European odds) represent the potential return of a bet, including the original stake, as a multiple of the stake. The odds are presented as a single number, usually with two decimal places. Example: 2.5
American odds, also known as moneyline odds, are a popular format for expressing the likelihood of a particular outcome in sports betting, especially in the United States. American odds can be positive or negative numbers and indicate how much profit a bettor would make on a $100 wager if the bet is successful. They provide a straightforward way for bettors to understand the potential profit or risk associated with a particular bet. Example: -205, +155
Change to Macro Enabled Worksheet
The format of the bet tracking spreadsheet has been changed from .xlsx to .xlsm as I've added a few simple macros.
Click the "Add New Bet" button to input a new bet. Previously you had to scroll to the bottom of the Bet Tracker sheet to add a new bet and potentially drag down all formulas to track the stats. Now the newest bet will always be at the top and you won't have to manually manipulate any formulas.
If you want to clear all your bets there is a button that will do that but be careful - the action cannot be undone.
I don't think macros will work on MAC/iOS/APPLE devices, so you might have to manually drag down the formulas.
Cash Out and Voided Bets can now be tracked
The ability to Cash Out has also been added as well as void or push bets can now be tracked. If you cash out, enter the value you cashed out for in column K, otherwise leave column K blank. If you want to track void or cancelled bets you can select Void from the drop down then delete your wager amount and leave it blank since you get that money back.
How to Transfer Information From Old Versions
In the Bet Tracker sheet, I think columns A through K should be the same so you can copy and paste those, then drag down the formulas in row 2 for columns M-Y.
Let me know what you think about the improvements to the Sports Bet Tracking Spreadsheet in the comments below! And as always, I valuable your feedback if you have ideas for even more improvements.
If you're looking for 2024 March Madness Brackets, no, I won't be posting any here or making my own. Once again, I will be using the best March Madness brackets in Excel, created by David Tyler (and I will continue to use his until he decides to no longer update them). They’re very polished and easy to use. There are only 68 teams in the field but the spreadsheet is already setup to handle up to 128 teams, if they expand in the future. There are two sheets: the bracket and the pool manager. Instructions are included but its very intuitive. Take a look:
As I’ve said countless times before, you can learn a lot by looking at Excel templates made by others. Here are 5 things I do when examining a new spreadsheet using David's March Madness brackets as example.
March Madness Squares
When I created my new Super Bowl Squares Unlimited spreadsheet for last month's football game, I had the goal in mind to be able to use the same sheet for other sports, like basketball. I tweaked my template a little and now you can use it for March Madness Squares!
1. Everyone keeps the same random numbers for every game 2. The random numbers change for every round 3. The random numbers change for every game
Here's a quick demo of how to use squares for basketball:
You can play either way with my spreadsheet. I designed it to be very versatile. Payout percentage can change per round as well. Update the settings how you want them then you click Generate Scoreboard once for every game, so 32 times in the first round. There are a lot of other options built in that can be ignored - it's all up to you!
Will you be playing 2024 March Madness Brackets, March Madness Squares, or both? Let me know in the comments below.
It's finally time to play Super Bowl Squares! As the biggest sporting event of the year approaches, hardcore football and casual fans alike gear up for an unforgettable Super Bowl experience. To add an extra layer of excitement to your game day festivities, I'm here to introduce you to the magic of Super Bowl Squares and simplify the process with my specially crafted and powerful Excel template. Whether you're a seasoned veteran or a newcomer to this classic game of chance, my template is designed to make organizing and tracking your Super Bowl Squares pool a breeze. Let's get to it!
I’ve been making Super Bowl Squares templates available for you to download since 2012 and the latest versions are the most versatile yet. There are two templates to choose from, I'll explain the pros and cons of each.
2024 Super Bowl Squares - 54 Ways to Play
Released in 2021, this is the more "classic" and straightforward way to play. When you first open the sheet, it has a nice clean look with only one sheet visible. You select the options for how you want to play, click Generate Squares, and it sets up the sheet exactly for the version you want to play.
The way it works is all the scoring systems are already built into the sheet. If you go to the Squares or the Manager sheet you will find many hidden rows or columns. The Generate Squares macro simply hides or unhides the data based on the user’s selections.
54 ways to play apparently wasn't enough as I still received numerous requests for customizations and more features. This lead to the creation of a new approach to the template: Super Bowl Squares Unlimited. The main difference in how it works is there are no pre-built scoring systems. Instead, they are built on the fly by macros based on the user's input; therefore there are an infinite number of ways to play.
This sheet offers the most flexibility but might take some getting used to. At first it may be a little difficult to understand how the scoring works, so I recommend you watch this video:
My daughter is in Girl Scouts and her troop wanted to sell Girl Scout cookies for the first time this year. In order to sell Girl Scout cookies you need an adult to be the TCPC: Troop Cookie Program Coordinate. Or as my daughter said, "We need you to be the Cookie Daddy!" I figured all this meant was they needed someone to manage an Excel spreadsheet for all the cookie sales so I agreed.
Little did I know what was in store as there is much more to it. First is all the training and webinars, background check, volunteer forms, etc. Then when it comes to finally sell cookies, there are no spreadsheets; they've got Digital Cookie online selling platform and eBudde management system. While the eBudde system and tools seem to have all the information needed on the backend, the front end is very confusing for a new user. It's very confusing and cumbersome and the key information the TCPC needs to know is spread out over different pages. Seriously, if you have to have numerous training sessions to explain how everything works and people are still asking tons of questions, your system is too complicated.
With this spreadsheet you still have to manually input all the cookie sale information from eBudde but now all the information you need to know is shown visually on just two sheets and is easy to understand. Here's how to use the cookie tracker:
On the sales summary page, copy and paste your Girl Scouts. Then input each of their Digital Cookie sales, which are broken into four categories:
1. Cookies to be hand delivered (from the Initial Orders page in eBudde)
2. Donated cookies for Operation Salute
3. Any cookie sales from paper order forms that need to be input manually
4. Orders that will be shipped direct from the bakery (from the Girl Orders page)
Next, you can input each Girl Scout's goal for how many packages they want to sell. These will add up at the bottom to show the overall Troop Cookie Goal. There is a donut graph to show the troop's progress towards their goal.
On the right it shows the Instant Rewards and how many packages it takes to reach. Once a girl has reached the goal, the formula automatically shows as having achieved it.
On the next sheet, we have a breakdown of how many of each type of girl scout cookie has been sold (thin mints being the most popular, obviously). As the cookie daddy and the person responsible for picking up the cookies from the cookie cupboard to distribute to the troop, the most important info is the number of cases of each type of cookie I need to pickup.
There is another sheet in my template that explains some of the terminology, the key being the word "box" is never used. The girls sell "packages" of cookies and 12 of those packages make up a case. The cookies are never mixed up in different cases and if you only sell one package of LemonUps, you have to pickup an entire case of LemonUps.
The Girl Scout Cooking Tracking Spreadsheet shows you exactly how many cases of each cookie you need and how many unsold packages in each case. The unsold cookies from the initial order can be used to sell in-person during the booth phase.
I added a sheet you can use as a template for booth signups.
