A Beginners Guide To Excel for Sports Betting

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.

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.

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.

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.

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.

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.