Advice

A Beginners Guide To Excel for Sports Betting

(Last Updated On: July 27, 2010)

There are always a number of questions about how to use Excel for analysing your betting information, whether this is your bankroll management, selection management or system building.

In today’s article I am going to take a look at the basic functions that you need to understand when using Excel, or any spreadsheet, for your betting.

When you open Excel you are greeted by a blank worksheet. Below is a screenshot of what welcomes you when opening Excel. Please note that I am using Excel 2010 here and so it may be slightly different to your version. However all of the features that I am discussing are available in all versions.

A Beginners Guide To Excel for Sports Betting 1

The red areas that I have highlighted are the areas that we are going to discuss today.

In a number of the videos I have made, I copy and paste information from the Racing Post or the Sporting Life into Excel and many of you have difficulty in replicating this. After you have read through the next few sentences this will never be a problem again.

A Beginners Guide To Excel for Sports Betting 2

As you can see from the close-up picture of the paste button there are a number of different options that you are given when pasting into Excel. If you don’t have this version of Excel then you may find these options under the ‘Paste Special’ menu item.

You only need to know about two of these options. The first is a normal paste which can be done by pressing the Crtl+V button on your keyboard. I am sure that you already know how to do this. When using this type of paste you copy in all the formulas and formatting that you have copied.

The button circled in red in the close-up image is called ‘Paste Values’ and this is what it will be named if you have an older version of Excel. This button only pastes in the values that you have copied. What this means is that you don’t get any formula or formatting. If you are copying from a cell with a formula then only the value that formula has ended in will be pasted into the new cell.

Next up are the Sort and Find menus.

A Beginners Guide To Excel for Sports Betting 3

These two menus are pretty self-explanatory but it is important to introduce them. The Sort menu will allow you to sort your data by any of the fields. You can also click the filter button and this will allow you to filter the data. For example you may only want to see the information from horses that ran within the last 7 days. By clicking the filter button you choose the column where the DSLR information is and enter ‘<=7’ and it would only show those rows. A very useful facility

The Find menu does exactly as it says, it finds things. This can be useful if you are looking for a specific horse or race but the most useful feature, I think, is the Replace button. This allows you to replace data.

When would this be useful? Let us imagine that you are using ratings and there are a number of fields which are empty because some of the horses couldn’t be rated for that factor. It may be that you want to use the average rating for these horses so that you can analyse all the runners in the same way. You would select the column, go to the Replace button and look for blank fields, replacing them with the average for the column. A few seconds later everything has been replaced and you can start your analysing.

A Beginners Guide To Excel for Sports Betting 4

At the bottom of the screen you may have noticed different Sheet numbers. You can have as many of these sheets as you want in a workbook. A workbook is the excel file you are using and the sheets are different pages within that file. This can be a very useful way to split up data for system building and system testing, for example. It is possible to reference any sheet in your workbook in a formula. If you had a selections Sheet and then different Sheets for different staking plans you would only need to reference the selections Sheet for each staking plan, rather than entering the data multiple times.

A Beginners Guide To Excel for Sports Betting 5

The image above is a close-up of the formula bar. This is where you are going to be spending most of your time when you are working in Excel. Whenever you type something into a cell this is where you will see it appear. You can either edit directly in the cell or just select the cell and edit in the formula bar.

To type a formula into Excel you need to start with an = sign. The equals sign shows that the cell is going to equal whatever formula you put in it. When you have entered a formula then you can copy the formula down a column or across a row. When you copy, the column and row references automatically update.

Below is a list of basic operators that you can use when creating your formulas and I shall follow this on with a simple example of a formula.

< Less than

> More than

<= Less than or equal to

>= More than or equal to

<> Not equal to

Minus

+ Plus

* Multiply

/ Divide

If() Allows you to perform multiple tasks based on criteria within the brackets

Sum() This adds together the value of all cells in between the brackets

Product() This multiplies together the value of all cells in between the brackets

$ Prevents column and/or row references from changing when copying formula

AND() Can be used to perform multiple queries within the brackets

OR() Can be used to perform one query for different values within the brackets


Let’s take a look at a couple of formula examples:

=sum(A1:A5)

This adds up all the values in cells A1 to A5.

=if(A1<=4,sum(A1:A5),0)

This says that if cell A1 is less than or equal to 4 then add together cells A1 to A5, otherwise enter 0.

As you can see you can start to build up quite complex formulas.

Michael Wilding

Michael started the Race Advisor in 2009 to help bettors become long-term profitable. After writing hundreds of articles I started to build software that contained my personal ratings. The Race Advisor has more factors for UK horse racing than any other site, and we pride ourselves on creating tools and strategies that are unique, and allow you to make a long-term profit without the need for tipsters. You can also check out my personal blog or my personal Instagram account.

7 Comments

  1. Very good article for those of us that are statistically minded! And too few out there like this one. Yes, it’s very basic, but the forum is necessary and critical for those serious about making this all work. Well done! Writing from the US here. If anyone cares to share some bet tracking spreadsheets, or correspond about this, feel free to drop a line: altusgroup1@yahoo.com

    Thanks,
    Alan

    1. Thank you. Good thought about the bet tracking spreadsheet, I shall see if I can get something up in the members area.

  2. Hi

    yes i enjoyed that and look forwards to others as i`m sure their`s a few members
    like myself who havent fully got their heads round the full workings of excel
    so thanks
    regards
    glen

  3. Yes that is the kind of help I need also, I only have a rough self taught rudiment of it but too much racing and other things, so excel gets put to one side when a spread sheet works do not fix it. Oh but when it does not, that is when you need to know why. Thanks for these.

  4. For me this article was much anticipated and appreciated.
    I succeeded in converting and importing a USA (equibase) pdf racing form into an excel worksheet. However it is not usable as the form data is not in any way categorically separated. It is thus impossible for me to edit the info into my own categorical numerical rating system.
    Any idea how to solve this problem?
    Appy

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top button
Close