Replies

I just make column headers for whether the item has cleared the bank (or was bought on a credit card), the date the purchase was made, a “General” purchase category, a “where it was purchased from” column, and a more in-depth “description” column if I need it. I put in the purchase amount (use negative numbers for purchases, positive numbers for income), overall account balance, a small column to drop an “x” into to show that my books are balanced up to this point (a la the old checkbook), and I also drop in month indicators for which pay period this all falls into.

For the “General” and “Where” categories, I use cascading dropdown boxes (using the INDIRECT() function) from lists I create on another tab—this is to keep values consistent across all purchase categories for analysis later if I need it. I also use conditional formatting so all positive values are green and all negative values are red, just as a quick at-a-glance of where my paychecks, etc. are versus my purchases. I also use a “c” indicator under a “Credit Card” column against all of my credit card purchases so I can always tell which purchases were done on a CC.

All my analysis is done via Pivot Tables, which are super handy for pulling together large amounts of data.

I know all of this sounds complicated, but I really didn’t know how to do much of it either. I just knew that I wanted certain information available and I googled how to do it. I even have budget lines built into the monthly data sheets—for instance, a monthly grocery budget is already placed at the end of the month, and as we make grocery purchases, it subtracts from that amount so my overall dollars available for that month doesn’t change (unless we go over budget, which can happen).

As complex as it is now, it started off very simply and I gradually expanded it as my desire for better financial analysis increased. Quicken, Money, and the like just weren’t terribly flexible, and Excel tutorials are a dime-a-dozen on YouTube and other spots online.