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

    • internpete profile image

      Peter V 

      5 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!

    • StephanieBCrosby profile image

      Stephanie Bradberry 

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

    • rfmoran profile image

      Russ Moran - The Write Stuff 

      6 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

    • Riverfish24 profile image

      Riverfish24 

      6 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!

    • krsharp05 profile image

      Kristi Sharp 

      6 years ago from Born in Missouri. Raised in Minnesota.

      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

    • ercramer36 profile imageAUTHOR

      Eric Cramer 

      6 years ago from Chicagoland

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

    • farmloft profile image

      farmloft 

      6 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 imageAUTHOR

      Eric Cramer 

      6 years ago from Chicagoland

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

    • Simone Smith profile image

      Simone Haruko Smith 

      6 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!

    • greatstuff profile image

      Mazlan 

      6 years ago from Malaysia

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

    • leahlefler profile image

      Leah Lefler 

      6 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!

    • jpcmc profile image

      JP Carlos 

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

    • ercramer36 profile imageAUTHOR

      Eric Cramer 

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

    • yoginijoy profile image

      yoginijoy 

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

    • spartucusjones profile image

      CJ Baker 

      6 years ago from Parts Unknown

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)