ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Make a Spreadsheet to Keep Track of Expenses

Updated on March 24, 2014
Easy to follow instructions to create an expense tracking spreadsheet
Easy to follow instructions to create an expense tracking spreadsheet | Source

Poll Question

Do you currently track your expenses every month?

See results

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.


Planning your Spreadsheet for Tracking Expenses

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.

Easy to Follow Instructions

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 Screenshot
Control Tab Screenshot | Source

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 Screenshot
Transaction Register Screenshot | Source

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 Screenshot
Pivot Tab Screenshot | Source

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 Screenshot
Dashboard Tab Screenshot | Source

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.

Excel Options Window: Check "Show Developer's Tab in the Ribbon"
Excel Options Window: Check "Show Developer's Tab in the Ribbon" | Source

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”.

Changing Macro Settings in Excel
Changing Macro Settings in Excel | Source

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.

Button (Form Control)
Button (Form Control) | Source

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.

Comments

    0 of 8192 characters used
    Post Comment

    • spartucusjones profile image

      CJ Baker 5 years ago from Parts Unknown

      You offer some practical tips on how to make a spreadsheet. Very informative hub!

    • yoginijoy profile image

      yoginijoy 5 years ago from Mid-Atlantic, USA

      Great details! I do this currently but in a notebook with pen and paper. I think everyone should keep track of their expenses and live within their means. It is very important not to get into too much debt. Voting up and useful.

    • ercramer36 profile image
      Author

      Eric Cramer 5 years ago from Chicagoland

      Thanks! I tried to break it down in to easy to follow instructions. I agree it is vital to track your expenses and spend less than you earn.

    • jpcmc profile image

      JP Carlos 5 years ago from Quezon CIty, Phlippines

      WOW this is a cool hub. Although there are many commercially avaiable progrms out there both free and otherwise, it's a great achievement to make one yourself.

    • leahlefler profile image

      leahlefler 5 years ago from Western New York

      This is so awesome - I love the way you broke down the steps to creating a spreadsheet in Excel. This hub might help me get over my "allergy" to accounting!

    • greatstuff profile image

      Mazlan 5 years ago from Malaysia

      This will be useful for keeping tab of my son's wedding expenses, thanks for sharing.

    • Simone Smith profile image

      Simone Haruko Smith 5 years ago from San Francisco

      This is a great guide to using Excel as well as to creating a spreadsheet to track expenses. SO useful!

      I've been tracking every single expense I've had since I turned 18. Over the years, it has become something of a comforting practice, and it's much easier for me to be on top of my budget- as well as to save up for something I want- because I have tabs on exactly how much money I typically spend on different necessities.

      Thanks for sharing the template and tips, ecramer36!

    • ercramer36 profile image
      Author

      Eric Cramer 5 years ago from Chicagoland

      Thanks! Tracking expenses is important. Excel is a great tool to aid in that process.

    • farmloft profile image

      farmloft 5 years ago from Michigan

      I like the pivot table and dashboard explanations. I think making your own template will help you tailor it more to your circumstances. Voted useful. I'll be reading and trying more of your hubs!

    • ercramer36 profile image
      Author

      Eric Cramer 5 years ago from Chicagoland

      Thanks! I agree that ease of customization is a great benefit of making your own template.

    • krsharp05 profile image

      krsharp05 5 years ago from 18th and Vine

      This is very useful, scary but useful. I'm going to have to print it out or have you to dinner...one of the two. Excellent information. -K

    • Riverfish24 profile image

      Riverfish24 5 years ago from United States

      WOW..you have a fab excel training outlined here. I plan to print this too and try it out, if not for tracking expenses, but just as a learning. I mean I have been using Excel forever now, but its all hit and trial and I don't think I could jot down all that you have said based on your user experience. Thanks!

    • rfmoran profile image

      Russ Moran 4 years ago from Long Island, New York

      I've used Excel for years, but this Hub taught me some things I never thought of, like the dashboard tab. Thank you for sharing such useful info

    • StephanieBCrosby profile image

      Stephanie Bradberry 4 years ago from New Jersey

      I recently started using Excel to have a spreadsheet to track spending for my home-based business. But I had to look high and low for something easy enough for me to follow. Many plans start out easy enough but become a bit much for what I need to do. So, for now I am sticking to my simple rows and columns that add up cells. Maybe when I have more time I will implement the tips you have here. Thanks for sharing.

    • internpete profile image

      Peter V 4 years ago from At the Beach in Florida

      Great hub, very detailed and useful! I like using excel for almost anything, so I am going to give this a try. Thanks!

    Click to Rate This Article