How to Make a Spreadsheet to Keep Track of Expenses

As an accountant, I work with numbers all day long. Spreadsheets make life easier and are an invaluable tool to track, analyze, and manipulate data. They also save accountants from having to count beans.

Tracking personal expenses is important, especially in rough economic times. Knowing what you are spending will help you to manage your money better. Using a spreadsheet to keep track of expenses is convenient and will save you time in the long run.

It is important to take some time to think about how you want your spreadsheet to look and what kind of outputs you desire. Most expense tracking spreadsheets online are very simple and limited to one tab. This is great for once a month expenses; however, for frequent transactions, amounts must be entered into a formula that can easily be messed up. A second problem with this approach is that there is not any detail behind any of the numbers. You will have to go back to the individual receipts in order to find out what is in each bucket. My advice is to develop a more complex spreadsheet that uses multiple tabs and takes advantage of Excels powerful tools to manipulate the data into a useable format. The multiple tab approach also offers simple automated reporting along with a detailed transaction register.

Setting up your Spreadsheet

Open a new workbook in Excel and save it with whatever filename that you like and change the "Save as Type:" box to be “Excel Macro-Enabled Workbook” Excel 2007 requires this format in order to use Macros. A Macro is easy to setup and will repeat any commands that you program it to.

Control Tab

Rename one of the tabs as “Control Tab” by right clicking on it and selecting “rename”. This tab is a resource tab, where data tables are stored for use on other tabs. Feel free to box and shade your data tables as you like. I prefer to make them standout.

1. Click and highlight cells B2:C2 and then select “Merge and Center” from Excel's "Home" toolbar above.

2. Enter “Months” into cell B3.

3. In cell B4, enter “1”.

4. In cell C4, enter “January”.

5. Highlight B4:C4 and drag down the little box in the bottom right hand corner of the selected area until all twelve months are listed.

6. Merge and center B17:C17 and type “Year” into the cell.

7. Do the same thing in B18:C18 and type “2012” into the cell.

8. In cell E3, enter “Categories” as the title to the table.

9. Enter all of the categories that you would like to track from F3 down. I would strongly advise shading this table and leaving a few extra rows at the bottom so that you can add more categories as they become relevant. When finished adding categories, sort them alphabetically (A-Z).

Select all of the categories that you have entered and type “Categories” into the “Name Box”, which is located on the far left hand side of the screen next to the formula bar.

Transaction Register Tab

Rename one of the other tabs as “Transaction Register” by right clicking on it and selecting “rename”. This tab will be where you enter all of your expenses into, much like a checkbook register. You can choose whether to add more or less detail.

1. Enter a title for the Transaction Register in cell A1.

2. In cell A2, type “=” and then click on the “Control Panel” tab and select cell C18. Before pressing enter, press the F4 key to anchor the cell reference.

3. In row 5, enter the following: (A5) “Transaction Description”, (B5) “Date”, (C5) “Category”, (D5) “Amount”, (E5) “Comments”, (F5) “Month”, and (G5) “Lookup”. Format cells A5:G5 by adding a bold border around it. Also, change the fill color and the text color to make this row pop and add a border to keep it clean looking.

4. In cell F6, enter the following formula to lookup the corresponding month based on the date field (Column B) and copy it down to row 1,000:

=IF(TYPE(VLOOKUP(IF(B6="","",MONTH(B6)),Control!$B$4:$C$15,2,FALSE))=16,"",VLOOKUP(IF(B6="","",MONTH(B6)),Control!$B$4:$C$15,2,FALSE))

5. In column G, enter the following formula to enable the “Dashboard” tab to summarize the detail and bucket it into the corresponding month and category. This will make it easy to use a pivot table and some vlookups inorder to summarize the data on the "Dashboard" tab. Copy down the formula as far as the formula in column F:

=C6&" - "&F6

6. In column C, add a drop down box that lists all of the categories listed on the “Control” tab. Select C6 and on Excel's “Data” tab click on the “Data Validation” icon. It will open a new dialogue box. Click on the “Settings” tab and click on the “Allow:” drop down menu and select “List”. Now click on the “Source:” box and enter “=categories”. Click on the “Ok” button to accept the changes. A down arrow icon will appear when cell C6 is selected. Copy and paste this cell down as far as the previous two formulas in Steps 4 & 5.

This tab is now ready for use. Enter all of your transacations into this tab with as much or as little detail as you want. If you need more rows down the road, be sure to insert those rows above the bottom row to ensure that all of the formulas stay intact.

Pivot Tab

The purpose of the “Pivot” tab is to consolidate like data and summarize it behind the scenes. This tab will power the expenses by month summary on the “Dashboard” tab.

1. On the “Transaction Register” tab, highlight cells A5:G1000.

2. Click on Excel’s “Insert” tab and then click on the icon for “Pivot Tables” on the far left side. A new dialogue box will pop up. Click “Ok” and it will automatically insert a new tab into your spreadsheet.

3. On the right-hand side of your screen a “Pivot Table Field List” box will pop up. Check the boxes next to “Lookup” and “Amount.” Both will show up under “Row Labels” in the bottom section. Click and drag the “Amount” bar over to the “Values” box. Left-click on the "Amount" bar and select “Value Field Settings”, which will open a dialogue box. Change the selection from “Count” to “Sum”. Click on the “Number Format” button and select either “Accounting” or “Currency” and two decimals.

4. Rename the tab “Pivot”.

Dashboard Tab

The beauty of using dashboards is that they update either automatically or with the touch of a button. I love dashboard tabs because they offer a ton of useful information all in one place. Creating one looks harder than it really is.

1. Insert a new tab into the spreadsheet and rename it “Dashboard”.

2. In cell A1, enter a title for the tab, such as “Expense Tracker Dashboard” or whatever else that you like.

3. In cell A2, type "=" and then click on the "Contorl" tab and select cell C18 and hit the F4 key to anchor the cell reference.

4. Enter the following headings in row 5: (A) “Category”, (B) “Jan” – (M) “Dec”, and (N) “YTD”. Add color and borders to this row as you prefer.

5. Under the “Category” heading, put the following formula that will automatically pull the “Category” list from the “Control” tab:

=IF(Control!E4="","",Control!E4)

Copy the formula down as many rows as you have listed under “Categories” on the “Control” tab.

6. Under each month, enter the following formulas and copy them down as far as the formula in column A:

=IF(TYPE(VLOOKUP($A6&" - January",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - January",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - February",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - February",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - march",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - march",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - april",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - april",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - may",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - may",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - June",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - June",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - July",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - July",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - august",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - august",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - September",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - September",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - October",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - October",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - November",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - November",Pivot!$A$4:$B$14409,2,FALSE))

=IF(TYPE(VLOOKUP($A6&" - December",Pivot!$A$4:$B$14409,2,FALSE))=16,0,VLOOKUP($A6&" - December",Pivot!$A$4:$B$14409,2,FALSE))

7. Under the “YTD” column, enter in a sum formula to add up the entire year’s activity and copy it down to the bottom.

8. After the last row of the table, enter “Total” in column A and a formula to sum each of the months and the YTD columns.

9. Finally, make a macro that will update the “Dashboard” tab with the push of a button. Under the Windows menu, select “Excel Options”. The Excel Options window will open. Check the box for “Show Developer tab in the Ribbon” and click “OK”. Now click on the “Developer” tab and click on the “Record Macro” button. A Macro window will open up. Enter “UpdatePivot” as the name of the Macro and make sure that it says to store the macro in this workbook. Click Ok. Anything you do from this point forward will be repeated in the macro. Click on the “Pivot” tab and right-click anywhere on the pivot table and select “refresh”. Click back on the “Dashboard” tab and select the cell where you want the cursor to be when the macro is run. On Excel's “Developer” tab, click “Stop Recording”.

10. Click on the Windows menu and select "Excel Options". Now click on "Trust Center" on the left hand side. On the far right side of the page, click on the "Trust Center Settings . . ." button. A new window will pop up. Select "Macro Settings" on the left hand side. Select "Enable on Macros" and click "Ok" on both windows.

11. On Excel's “Developer” tab, click on the folder icon with the tools on it and select the rectangle button under “Form Controls”. Now click and drag your mouse on the “Developer” tab to draw the button about the size of one cell. When you take your finger off of the mouse, an assign macro window will appear. Select “UpdatePivot” and click “OK”. Right-click on it and select “Format Control” and here you can decide if you want to change the font, font size, color, etc.

Now you have a fully functional expense tracker that is ready to use. Feel free to customize it as you like, maybe add some charts or graphs to the “Dashboard” tab. You can also add more pivot tables to better analyze your data. The options are endless.