Yes, I know there are lots of online or downloadable programs for tracking your poker results, but if you’re old school like me, or just like to do shit yourself, this is the article for you.
Excel is a far more powerful of a program than most people know. It has the ability to do things that you’ve never even thought of.
For example, I once worked as a tech-guy/website developer/network engineer… read GEEK for a large retail company.
One aspect of this company is having multiple locations across Canada and the U.S.A, all with their own inventory and full service physical locations. The business also makes a lot of money from its website selling the same stuff, but online.
Now, to keep shipping times and costs as low as possible, all physical locations ship product for web orders, rather than having one warehouse dedicated to online ordering. This is a cost effective way to operate, but requires some in-depth solutions to inventory management and control.
To manage this we created an excel spreadsheet which automatically updated with the actual inventory count for each product for each store location.
It did this buy parsing the accounting software end of day sales figures. On top of this it stores months of sales results allowing forecasting and trending of product sales, all updated automatically and fully configurable.
If Excel can do something like this, it can certainly handle your poker stats with flying colors, you just have to learn how to use it. Here are some quick tips and tricks you can use to make your spreadsheet a little more rad.
How to Use Excel: Conditional Formatting
A block of solid data can be hard to read at a glance, and forces the reader to invest time comprehending what he's actually reading. Since we're lazy, you want to add some formatting to make the spreadsheet easier to read at a glance.
In this first picture you'll see negative values are in red, this is done with standard formatting, which I'm sure you all know how to do. If you don't know how to format the text in a cell, you need to spend some time with Excel, or pick up a beginners guide, this article is not for you.
This is better than nothing but why not make it even more obvious? By using the conditional formatting tool, we can change the red text, to this:
To do this you're going to want to highlight the cell(s) you want to format and choose "Conditional Formatting" from the main "Home" toolbar (this is in Office 2007. For earlier versions it will be in the toolbar somewhere. Go and find it).
Now, we want to highlight the boxes if they're negative, so choose "Highlight Cells Rule" then "Less Than". Then it's simple, put 0 in the text box, and choose red as the color.
Another thing you can do is highlight cells along a gradient scale depending on the value of their amounts. Here's an example using BB/h. I set the scale as red for -50 (or beyond), blue for 0 and green for +50 (or beyond) resulting in something like this.
To do this you need to choose "Conditional Formatting" then "New Rule". In the input box change 2-color scale to 3-color scale, set the values to numbers, input your numbers, choose colors and you're done.
Using the Excel Drop Down Box
For some entries you only ever choose one from a small selection of options. Rather than type these in each time (which can result in formatting errors and typos, which can break calculation codes), we can create a drop down box with your selection choices.
The easiest way to do this is to use a named range.
The first thing you need to do is click on a tab at the bottom of your spreadsheet to go to a new page of the same document. As you can see my document has three parts:
We want to focus on the data page for this task.
The first thing you want to do is create a list with all the values you want. As you can see I have the title for my lists to the left of each list, with an ascii arrow. I did this to make my life easier. Even though it's not required I recommend you do the same.
Now, click on the column header and where is says B1 write in the name of that list, I chose Limits for the Limits List.
Now go back to the sessions tab. Click on the box (or boxes) where you want the drop down, and choose the data tab from the main toolbar. Click on the option named "Data Validation".
From here change the Allow field to "List", and in the source box write =Limits. If you named your range something else, replace =Limits with =Whateveryouchoseasyourname.
Now you'll have a dropdown with everything in that named range. If you like you can enter an input message when in the data validation dialog box. That will show up like this:
Conditional Equations, or IF Statements
If you're anything like me (and if you're reading this blog, I assume you are) having all of your calculated fields defaulting as 0 or #VALUE! will annoy the shit out of you.
I'm really not sure why M$ hasn't come up with a more elegant solution for this problem yet, but in the meantime we're going to use a simple IF statement. For the programmers out there, this will be a breeze. For the rest of you, don't worry, it's easy.
Here's how IF statements work in Excel:
=IF(condition,output if true, output if false)
Here are some other things you need to know:
= : Equal to
<> : Not equal to
"" : Null, or no input
AND(condition1,condition2) : Both conditions must be true to return a true result
OR(condition1,condition2) : Either of the conditions must be true to return a true result
So, using the result field as my example, the equation in the box reads as:
This means that if both F2 and G2 has anything in them (they <> (do not equal) "" (blank)) then output G2 minus F2 to give us our result.
If either box is empty, a standard formula will return an error, so with this IF statement it returns "" (blank) instead.
Excel is able to do very detailed equations, in my Reports page I use nested IF statements, such as this:
As you can see, you can make it as complex as you like, depending on how fancy you want to get. The SUMIF function is a pretty useful little tool. Here's how it works:
=SUMIF(range to pick from, condition, range to add up)
Range to pick from - range of cells to use in formula, typically A2:A11, or a named range like I used.
Condition - Anything you want. If you wanted just results for one limit your condition would be "$2-$5".
Range to Add Up - This is what actually gets added. The range to pick from are dates, but we want to add up the results for those dates.
As you might have expected this is only scraping the surface of what you can do with the program. But for formatting poker stats, this is about all you need to know. If you have any questions, drop them in the comments and I'll see what I can do.