ArticlesHow ToTips

How to use Excel to keep track of your income (and outgoing expenses)

As you may or may not know already, Excel is a very versatile program when it comes to organizing statistical data.  What many might not realize of course is that it can also be used to maintain detailed financial records, whether we’re talking about keeping track of personal expenses or that of a business.  This is typically referred to as creating a budget spreadsheet and you can think of it as a rather cheap alternative to actually purchasing software that’s dedicated solely to the task of budgeting (assuming that you already own or have a copy of excel installed on your machine right now).

There are essentially two ways to approach this.  1.) You can compile data from over the course of a month or two and take note of exactly what you’re spending money on so that you have the correct number of categories laid out.  Or 2.), you can rely on your memory to guide you in the creation of specific categories.  In either case, the various types of expenses will go in the left hand vertical column labeled “A”.  Naturally, this means the horizontal bar on top will contain the various months of the year.

For our purposes, let’s just jump right in and set everything up as best we can in order to start imputing data as soon as possible.  Obviously, the first thing you need to do is open Excel and create a new file.  Once you’ve done that and created/added the vertical and horizontal items as previously indicated we can move on to the next step.  Note* – the terms in the vertical column on the left should be things like “Income”, “Gross pay”, and perhaps even “contract jobs” if you have another job or source of income which needs to be listed.  Right under those items place a box containing your “Total Income”.   Next, you’re going to want to create another list right underneath that one (separated by a space) which contains your expenses, each of them clearly listed.  Note* – your expenses should include everything you tend to spend money on each month, including food, rent, purchases, bills, etc…  Don’t forget to include a section for unexpected expenditures, which will act as a sort of “catch all” for items like repairs.

Right underneath your last expense you’re going to want to add a “Total” box.   Now here comes the tricky part, you’re going to have to add the =sum formula to each box on the “Total Income” line underneath each month of course.  For example, if you’re going to be adding the contents of B3 – B5, simply use the following “=SUM (B3:B5)”.  This will give you your total income for each month in that box.  Following that you’re going to want to do the same for the boxes underneath pertaining to your expenses, which will likely be something like “=SUM (B8:B11)”, now do that for each box in that row (one for each month) in the “Expenses row”.

Lastly, create a row under them all which says “Over/Short” and use the following code which will subtract your expenses from your total income “=SUM(B#-B#2)” {# = the total income line and #2 = the total expenses line}.  Now repeat this process for each lettered column.  If you’ve done everything right you should immediately see just how your budge worked out for each month (whether you were over or under for that month).


Show More

Related Articles