Facebook Marketplace Sales Tracking Template Updated

 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.

Do you want to track this data for yourself? 

Download my free Facebook Marketplace Sales Tracking spreadsheet here.

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!

Elevate Your Sports Betting Game with Our Free Excel Tracking Tool

Sports gambling just became legal here in Ohio and we've been flooded with sign-up offers. Since just about every sportsbook will give you free bonus bets (around $200) for a small ($5 or $10) deposit, I decided to dabble a little bit into sports betting to see what all the fuss is about. I signed up for three different sportsbook sites and quickly realized I needed a way to organize and track all my bets.

Naturally, I decided to make an Excel template to track which bets I'd made, track my earnings, and make sure I was still making a profit. First, I created a Lists sheet for all my drop down lists including which sportsbook, which sport, result, and so on. Personally, so far I've only stuck to football because that's what I follow the most and already have some interest and knowledge in it.

The Bet Tracker sheet is used to track every individual bet including the odds, wager amount, and formulas to determine the potential winnings and the actual result.

When you bet with your own money, if you win the bet you get the winnings plus your original bet back. But when you use Bonus money to place your bet, that "fake" money is not added to the winnings. I had to account for this in my Potential Winnings column formula.

If your own money then: Winnings = ((Odds/100)*Wager)+Original Wager

If bonus money then: Winnings = (Odds/100)*Wager)

=IF(E2="My$",((G2/100)*F2)+F2,((G2/100)*F2))

If you get lucky and win a bet, you'll be able to withdraw the money into your own bank or Paypal account. There's a summary sheet to track exactly how much money you've put in versus what has come out. The most important stat to me is my running total, and making sure it is well in the positive otherwise it's time to quit. 

How have I done? In the past two weeks, I placed 33 bets and won 8 of them. I've only had to input $27 of my own dollars, while I've gotten to bet $650 bonus bucks. I'm not huge into taking risks, so instead of betting on crazy parleys with larger payouts, I've bet on safer, more likely outcomes but with smaller rewards. What do I plan to do with my winnings? Probably invest in an AI company since they're changing the game.

In all my years of making sports related Excel spreadsheet templates, I've never really gambled on sports. I'm not sure I'll keep gambling once my free funds run out, but it is a fun experiment while it lasts. Like playing fantasy football or Super Bowl Squares, it gives you an extra incentive for watching the game. Hey, speaking of Super Bowl Squares, I've got a new version coming out this weekend and I can't wait to hear your thoughts on it...

.Download Sports Bet Tracker.xlsx file here

How to change relative hyperlink to exact link in Excel

You may have used the HYPERLINK function in Excel before but what you may not have released is your links can be either relative or absolute (or sometimes called exact or specific). What does this even mean? And how do you change from relative to exact?

Free Meeting Scheduling Excel Template

How often do you ask friends, family, or coworkers which day is the best to get together? Whether it’s a meeting, party, trip, or some other occasion I find myself in these situations all the time. There are several apps and online calendars available to help you coordinate which day works for everyone involved. I used to use a site called Doodle all the time.  The problem with these services is they are not always free, they’re constantly changing and you have to keep relearning how to use them, you have to make an online account, and so on. So I did what I always do – I decided to make an Excel template to determine which day works best for meetings and events!

I used a lot of the same concepts and macro code from my Super Bowl Squares spreadsheet. When you first open the spreadsheet you are greeted by a simple, clean setup page. Here you’ll manually input the number of participants, meeting subject and description.

Next, you’ll enter the start and end dates of the days you want the participants to choose from. It’s very important that these two cells ONLY contain dates. To restrict a user to only being able to enter a date in a cell, go to Data > Data Validation. Under Allow select Date. Be sure to enter a custom error message so if a user makes a mistake they understand what needs to be entered.

When the user clicks Generate Schedule the Schedule sheet is unhidden. The schedule can handle up to 100 participants and up to 365 days. However, you probably won’t need all that so the macro will automatically hide all the rows and columns not needed to make it easy for the user to input their information.

A lot of good Excel tips can be gleaned from examining the event timing spreadsheet. Here's a quick summary of what can be learned by dissecting this free Excel template:

• How to add and use Option buttons
• How to use data validation to restrict entry in a cell to a date
• How to use data validation to restrict entry in a cell to an email address
• How to get the day of the week from a date
• How to use command buttons and assign specific macros to them
• How to send emails from Excel with hyperlinks
• How to use a formula to show only weekends in Excel
• How to use conditional formatting to change cell color based on cell value
• How to hide command buttons by macro

Download the Meeting Scheduler Template here.

Watch How to Coordinate Meetings with Excel

If you want to see how this spreadsheet works and some tips like how to limit a cell where a user can only input a date then watch the video below:

Try it out and let me know if you think it’s a legitimate replacement for Doodle, Calendly or whatever meeting scheduling apps you currently use. 

Excel Tips from the Best 2021 March Madness Brackets

After a year hiatus it’s finally here, the college basketball March Madness brackets are back! Last year, I made a NFL Draft Game spreadsheet for the first time to try to compensate for the loss of the basketball brackets but it just wasn’t the same (but I did still update it for 2021). This year, the 2021 NCAA men's basketball tournament will be unlike any March Madness that has come before. All games will be played in Indiana, with most in Indianapolis. The schedule has also been change. The First Four would typically be played on Tuesday and Wednesday night with the first round being played on Thursday and Friday. 

Here's the 2021 March Madness schedule:

• First Four — 4 p.m. start on Thursday, March 18
• First round — 12 p.m. start on Friday, March 19, and Saturday, March 20
• Second round — 12 p.m. start on Sunday, March 21, and Monday, March 22
• Sweet 16 — 2 p.m. start on Saturday, March 27, and 1 p.m. start on Sunday, March 28
• Elite Eight — 7 p.m. start on Monday, March 29, and 6 p.m. start on Tuesday, March 30
• Final Four — 5 p.m. start on Saturday, April 3
• NCAA championship game — 9 p.m. Monday, April 5

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. 

The First 5 Things I Do When Examining Someone Else's Spreadsheet

As I’ve said countless times before, you can learn a lot by looking at templates made by others. Here are 5 things I do when examining a new spreadsheet:

1. Unhide hidden sheets, columns, and rows: When you make a template others are going to be using, you want to make it look nice and clean and hide anything that could cause confusion to a first time user, which leads to hiding rows, columns, or even entire sheets in a workbook. So, the first thing I do when examining someone else’s template is look for the hidden data. Right click on the sheets tab and click “unhide”. I unhide all the hidden sheets if there are any to see what data is present. Look for any hidden columns or rows as well by seeing if any letters or numbers are skipped.

2. Understand the NamedRanges: Go To Formulas > Name Manager and examine what the named ranges are, what sheets and cells they refer to. Hopefully they're all named well, like in David's brackets.

3. Look at conditional formatting rules: On the Home tab, go to Conditional Formatting, click Manage Rules, then Show formatting rules for This Worksheet to view them all.

4. Look through the formulas: On the Formulas tab, click “show Formulas” to show if they were manually typed in or if there is a formula calculating the values

5. Look through the macros: Hopefully, the person writing the code left lots of good comments so it’s easier to follow along with what each piece of code does

Watch me quickly walk through David's 2021 March Madness brackets going through the five points listed above:

2021 NFL Draft Game Spreadsheet Template

It’s been one year since the COVID-19 pandemic began. Last year, since March Madness and other sports at the time were cancelled, I started thinking about what other things I could do to fill in the void of not having any March Madness brackets to fill out. The answer came in a suggestion from a reader to create an NFL Draft game spreadsheet. I’ve updated the template for this year.

Inside this template I've listed the top 100 draft prospects according to ESPN. Each draft game player (and the template is currently setup to handle ten players) are randomly assigned ten future NFL players by using a randomize macro. The earlier your players get drafter the better, as the draft position counts for points and the lowest number of points wins!

The random number macro is pretty simple:

'define range of cells for random numbers

Dim Player1 As Range

Set Player1 = Range("AN2:AN101")

Player1.ClearContents

For Each a In Player1

Do

a.Value = (Int((100 * Rnd + 1)))

Loop Until WorksheetFunction.CountIf(Player1, a.Value) < 2

Next

Based on some good user feedback I added the option where each player can now try to predict which team will select their players for additional bonus points. Well, negative bonus points that is, as it subtracts points from your total score (remember, lowest score wins).

See how the Draft Game spreadsheet works in the video below:

Even if you have no interest in the NFL, football, or drafts, you can still learn a bit about Excel by examining the random number generator macro, or the vlookup and sumif formulas used. The scoreboard uses a “rank without ties” formula:

 =(IF(D3<>"",(RANK(D3,$D$3:$D$52)+COUNTIF(D$3:D3,D3)-1),""))

For now, the spreadsheet is setup to handle ten players. To add more, the formulas and macro will need to be modified. If enough people are interested in using this sheet, I will work on making it scalable so it can automatically adjust to the exact number of players.

2021 NFL Draft Game Spreadsheet Template.xlsm 

Let me know if you like this game or if you have any suggestions or questions.

COVID-19 Vaccine Distribution Tracking Template

Not sure if this is helpful to anyone but I made a COVID-19 Vaccine Distribution Tracking document in Excel. It’s hard to make a spreadsheet that you’re probably not going to use personally yourself. But I tried to envision being in management of a company or nursing home or school that needed to:

 (1) prioritize which employees or residents or teachers should get the vaccine first and

 (2) track which people have actually gotten the vaccine and when.

 

What Order to Distribute the Vaccine

The first item I tackled was how do you decide in what order to give out the vaccine. Knowing that supplies are limited, who gets the first dose?

I use a scoring system to decide the priority order: the highest score is first in line to get their shots.

 Here’s how the score is calculated:

1. Age: the score starts with the person’s age. 30 years old = 30 points. The older population automatically has a higher score.
2. Age multiplier: I made up a tier system based on age. Based on earlier data I’ve seen, the chance of dying from COVID if you’re 59 years or younger is only around 1% or less, but this jumps up to nearly 20% if you’re 80 or older. In my scoring system, those 60 and older get varying amounts of “bonus” points added to their score depending on what age range tier they fall into.
3. Priority person: Yeah, you could just rank vaccination order by age and be done with it but I didn’t want age to be the only limiting factor. There are other variables that I wanted to account for. I added the ability for a person to be marked as “high priority”, maybe due to immune deficiency or because they’re a first responder, or pregnant – whatever you want it to be. This adds 11 points to their score to help bump them up in the distribution order. Again, the spreadsheet is easily customizable so you can change the bonus to whatever value fits your needs.
4. Male or female: This might be controversial, but I added a segment to the scoring where males get a slight score increase over females. From the data I have (and please send me any new information if you think I’m wrong) males have a ~3% higher death rate from COVID than females (women are generally better at fighting off infections I’ve heard) so they get +3 point bonus while females get none. Of course, feel free to change this setting to make it what you want. Give woman 1,000 bonus points and men 0 – it’s up to you and can be easily changed in the spreadsheet.

The total score is calculated by adding up the age, the age tier multiplier, the male/female bonus, and the priority bump. The biggest factor is age, as that is the biggest factor of death rate. While getting the highest score means you’re first in line to get the vaccine, that also means you have the highest risk of dying from COVID.  I then use the RANK function in Excel to rank the individuals from highest to lowest score. There’s your priority list of who gets the vaccine first.

Vaccine Distribution Tracking

The next component, after deciding the vaccination order, is to then track if these people actually got the vaccine. Complicating matters, there are (right now) two different vaccines being distributed in the US requiring two separate doses and the time between doses is different. Each vaccine also has a minimum age requirement that is different so there is a formula to make sure the individual is old enough to get the selected vaccine. If they’re not eligible, meaning you’re too young, your score automatically goes to 0 and I use condition formatting to turn the cells black to show the individual shouldn’t be scheduled to get a shot. Once you get the first dose, enter the date and the spreadsheet will tell you when the earliest is you can get the second dose based on which vaccine you got. Finally, you can quickly see how many of your employees or residents have received the first or second doses of the vaccine.

 Download the spreadsheet here: https://gumroad.com/l/covid-tracker

I’ve been wanting to put my Excel powers to good use rather than just making Super Bowl squares or football related spreadsheets. Not sure the legality of it but if I was in charge of a company with employees working from home I would want to know when it is safe to bring that back into the office. Try it out and let me know what you think!

Ask Excel Help: How to add or count across multiple sheets in Excel

I recently received a question from a reader on our Excel Help Facebook page asking the following:

=SUM(First:Last!A1) adds up numbers across a series of sheets in the same workbook only if A1 contains a number. How do I add up the total number of times an A1 cell contains a text value x?

First of all, if you’re not familiar you are able to add values across different sheets using SUM function. Say you have 3 worksheet named Sheet1, Sheet 2, and Sheet3. You can use this formula to add up all the values in cell A1 in all three sheets:

=SUM(Sheet1:Sheet3!A1)

This special syntax is referred to as a 3D Reference.

Remember, SUM adds all the numbers in a range. You can type in the formula manually, or, after typing “=SUM(“ hold down the shift key and select the sheets, then select the cells to sum. To sum a range of numbers across sheets rather than an individual cell, the formula would look like this:

=SUM(Sheet1:Sheet3!A1:B16)

If your sheet names are numbers, add apostrophes to the formula:

=SUM(‘1:3’!A1:B16)

So our reader was asking how to count cells with text rather than adding numerical values. COUNT function will count the cells containing numbers, while COUNTA will count the number that are not empty. Same formula as before but replace SUM with COUNTA:

=COUNTA(Sheet1:Sheet3!A1)

This method works for almost any other function, too, such as AVERAGE. However, if you try using SUMIF or COUNTIF you’ll probably get a #VALUE error. Using those functions requires a bit more work using named ranges and INDIRECT, a topic for another day.

If you ever have any Excel questions or need any Excel help please feel free to send them my way! I read each and every message or comment I receive.

Best Excel Spreadsheet Memes - Prepare to Laugh

I've given hundreds of useful Excel tips since I started blogging eight years (!) ago in 2009. But I can't be 100% serious all the time. I've gotta have a little fun from time to time, like when I shared some of the best Excel pranks and practical jokes, not to mention all the sports related templates I produce. And who doesn't love a good joke or meme? So today I thought I'd share the best Excel spreadsheet memes I've collected over the years!

WARNING: Some of these Excel memes might make you literally laugh out loud. You've been warned.

Best Excel Spreadsheet Memes

How'd you know I like Excel? It's not like I write articles or make videos about it...

Yeah, not gonna happen, I'll make my Excel formulas as long as I want to!

Hmmm ok...

I hope not.

Mind = blown.

It's so worth it once you do you learn Excel VBA though.

Gotta have at least one grumpy cat meme.

Ever feel like this?

Me neither. Do people do that? Remove the gird lines by changing the grid lines color to white?

How many have hid non-working formulas before? Admit it if you have!

Haha, not necessarily.

There's always that one co-worker who spends more time formatting then working on the actual information within the Excel sheet.

When writing those super long formulas in Excel....

Drives me crazy.

When can you ever have enough spreadsheets?

I concur Steve!

Which Excel meme is your favorite? Please let me know by commenting below. Or if have a hilarious one that I failed to mention PLEASE send it to me!

Top 5 Excel Help Posts From 2015

2015 is coming to a close so it’s that time of year to sit back and reflect about what transpired this past year and what is to come. Listed below are the top five most popular Excel tips or how-to guides I posted in 2015.

1. 11 Excel lessons from the best March Madness template
2. How to send emails with custom subject lines
3. How to import data from one Excel spreadsheet into another
4. 8 simple rules to make your spreadsheets look better
5. How to input military time into Excel

On December 6th, Excel Spreadsheets Help turned six years old! Sometime in the first quarter of 2016 I expect this little blog to surpass the 1.5 million page view mark, no small accomplishment. Sadly, I posted fewer articles in 2015 than any other year, so my goal for 2016 is to at least double the number of tips I wrote this past year.

What did you accomplish in 2015? What are your Excel spreadsheet goals for 2016? How can I help you achieve your goals? What would you like to see more of in 2016? Anything specific?

Have a great holiday season, thanks for your continued support. See you in 2016!

Schedule Meeting Time Template

Having trouble finding the best time to meet with your team? Use this Excel template to find the perfect date or time for your meeting. Here’s how to use the meeting scheduler template: Enter your name in the input field, then use the drop down menu to add a check mark into the time slots you are available. If you’re not available, then leave it blank. When all the required meeting attendees enter their available times, the spreadsheet shows you the first and second best meeting times.

One thing this template will demonstrate is how to insert a check mark in Excel. Go to the top tab "Insert" then in click the "Symbol" button on the far right. A dialog box will appear and you need to select "Wingdings" from the drop down list at the top left. Scroll through the symbols until you find the check mark (wingdings: 252). If you copy and paste the check into a new sheet you may see a ü symbol instead. Simply change the font of that cell to wingdings to get the check mark back.

The next thing you’ll see by dissecting this template will show you how to use a check mark in a drop down list. Another function you can examine is how to use conditional formatting to color a cell based on the cell’s value. In this case, we want to color our cell green if it contains a check mark and red if left blank. As you can see, you will use the "ü" symbol in the formula.

Download the Easy Meeting Schedule Template.xls 

There are similar online tools to help you schedule meetings but most require a fee to unlock all the features. Instead you can use and modify this free Excel template. You could add more functionality, like adding a formula to automatically send an email once you’ve picked the meeting time.

Do you think this template will be useful to you?

Excel Quick Tips: Count Unique Values

I’ve got an extremely short but valuable Excel tip for you today: how to count the number of unique values in a range. Sometimes you need to count values in a row or column but not if they repeat. To count only the unique numbers or words in Excel, use the following formulas depending on:

If there are no blank cells:

=SUMPRODUCT(1/COUNTIF(Range, Range))

With or without blank cells:

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

Replace Range with A1:A7 for example. 

COUNTIF is probably the function you’re most used to that will count based on a given condition. SUMPRODUCT returns the sum of the product in the range.  SUMPRODUCT functions as an array formula, you just don't have to enter it as such. 

To see how this formula works step by step, click on the cell that contains the formula, then go to Formula tab, and click Evaluate Formula and you can cycle through each step in the calculation.

For more, see the index of Excel tips page.

Quick Excel Help: Date and Time Formats

I received an email from one of my readers asking a question about date and time formats in Excel and I figured I would share my answer here in case anyone else has the same question.

Question: Hi Nick, I am having trouble with Excel time date formats and was hoping you can help. I have a data field with a date time format and I want to separate this out with date in one column, time in another, & hour of the day  in another. Can you please tell me how to do this?

Answer: There are a number of date and time functions in Excel that should be helpful to you. I’m not sure if this is exactly what you’re looking for but try these formulas out:

In column a: =now()

Column b: =month(a2)&”/”&DAY(A2)&”/”&YEAR(A2)

Column c: =HOUR(A2)

Column d: =minute(A2)

Column e: =Second(A2)

Column F: =TIME(C2,D2,E2)

Do you have any other suggestions for this reader’s question?

How to input military time into Excel

I have to admit, the title of this post is a little bit misleading because there is no direct way to input military time into Excel. If you try to input "1300" as 1:00PM, Excel has no way to know that you're entering a time as opposed to a number. In order for Excel to recognize your input as a time and not 1,300 you must enter a colon and enter as "13:00". Basically, formatting the cells for dates and times only affects how the contents of the cell is displayed and not how the information is entered. So if you enter the military time with a colon and format the cell as Time then 1300 will be converted to 1:00PM.

Here's why: Excel stores dates and times as days and fractions of a day, where the number 1 equals January 1st, 1990. Entering 1300 into a cell leads Excel to interpret that as the 1300th day since January 1st, 1900 at 12 midnight.  Enter 1300 into a cell and format it as Time. Notice the value displays this: 7/23/1903  12:00:00 AM. Interesting, no?

Going back to the original question, how to input military time into Excel, my suggestion is this: entering a colon while inputting the military time is extra work. We always want to make data entry fast and painless as possible. So let's use a formula that will enable us to enter military time as 1300 but then display the regular time.  If we input the value of 1300 in cell A1, enter the following formula into cell B1: 

=time(int(a1/100),mod(a1,100),0)

In the picture above, you can see the how the military time is entered and the formula converts it to standard time. This post was inspired by a question from a reader on our Excel Spreadsheets Help Facebook page.

Quick Excel Tips: Dynamic Lookup

Today, I’m going to show you one of my favorite Excel tips: Dynamic Lookup or Dynamic Searching by using a combination of VLOOKUP and MATCH functions. Bascially, this function combo makes it so that the column that you pull the data from is dynamic based on the header making it more flexible than VLOOKUP by itself because you don’t have to rely on knowing the index column number.

In my example (that you can download below), in cell B2 I have this formula that combines VLOOKUP and MATCH:

=VLOOKUP($A2, $D$2:$G$14, MATCH($B$1,$D$1:$G$1,0),FALSE)

The column header in B matches one of the column headers in D, E, F, G - it doesn’t matter which one, you can change it and the values update automatically, which is the beauty of this formula. This makes it easy to add or remove columns without having to update your formulas. Not only is it more dynamic, the index column need not be on the left. Try it yourself!

Download my Dynamic Lookup formula example spreadsheet here.

Horizontal to vertical formula across sheets

Typically, to transfer horizontal rows into vertical columns in Excel you would highlight the cells you want to change and copy, then right click on the cell you want to move to, select ‘paste special’, click ‘transpose’ box, and hit ok. But what if you want to accomplish this task with a formula instead so it is done automatically on the fly? In an earlier post I showed you how to do it by using the OFFSET function combined with a named range. Today, I’m going to show you a different Excel formula to transfer rows to columns.

I recently used these horizontal to vertical formulas in my 2013 NCAA college football bowl prediction pool manager, and I am going to refer to that template as an example, so if you haven’t already I recommend you download the sheet. I needed to get the list of the players from the main sheet, which were listed out horizontally, and get them into the leaderboard sheet. The players needed to be listed vertically in the leaderboard in order for my rank without ties formula to rank the players in order of who picked the most games correctly. The basic formula uses INDEX and ROWS functions and looks something like this:

=INDEX(Sheet2!D$22:H$22,ROWS(A$4:A4))

The INDEX function returns the value of the cell at the intersection of the rows and columns specified in the formula while ROWS returns the number of rows in a reference or array. The exact formula I used on the leaderboard sheet in cell A3 is shown below:

=IF((ROW()-2)<=$L$3,INDEX('Master Pool'!J$39:Z$39,ROWS(A$4:A4)),"")

The formula - notice the range

The range expands as the formula is filled down

It takes all the names in row 39 on the Master Pool sheet from column J to Z and puts them into a vertical column in the leaderboard sheet, starting in row 3. The dollar signs are very important. As you fill the formula down the range increases. I also used the IF and ROW functions to account for the number of players (if the row number minus two, because the formula started in row 3, is less than or equal to the total number of players, then transpose the data, otherwise leave blank). As you can see in the example spreadsheet, the data is transformed from a horizontal row on one sheet to a vertical column in another. This is important because the data in the horizontal column can change and the vertical column will update automatically - no manual revisions needed!