Here’s my Groovy Expense Tracker Tutorial. The Groovy Expense Tracker can be used in Google Sheets or Microsoft Excel (2010 or higher). The following tutorial is based on Google Sheets.
The Groovy Expense Tracker has the following three tabs:
The first tab, ExpenseLog2015, is where you enter expenses. Here’s a screen shot of what that tab looks like.
I provided some sample expenses to show the functionality of the tracker. The sample expenses you see are “recurring” expenses. We’ll talk more about those expenses later. For now, I want to show you the three columns that have predefined lists for entries.
The first such column is the Category column (column A). This column has four possible entries: Car, Household, Personal, and Vacation. These four categories should cover every expense you might have. If they don’t, and you would like to add or remove a category, follow these steps.
- Select cell A2.
- Hold down the Ctrl key and press the space bar (this keyboard shortcut will select the entire column).
- Hover the mouse over cell A2 and right-click (if you click cell A2, you’ll deselect the entire column, and we don’t want that).
- Scroll down the context menu and select Data validation….
- In the Data validation window that opens, edit the List of items as necessary (see screen shot below).
- When you are done editing the List of items, click the Save button.
- Click cell A1 and then right-click it to bring up the context menu again.
- Again, scroll down the context menu and select Data validation….
- Click the Remove validation button (we don’t need data validation for the column name cell).
The other two columns that have predefined lists for entries are column B (Sub_Category) and column G (Payment_Method). Again, I tried to create lists that covered every expense and every payment method imaginable. If I failed, and my predefined lists don’t meet your needs, you will need to edit them via the same steps outlined above.
The final basic feature I would like to point out is column E (Month). The value in this column is used to create a monthly breakdown of your expenses in the Expense Report (tab named ExpenseReport2015). Here is a screen shot of the Expense Report with the sample data provided. As you add your expenses, the columns for February, March, April, etc., will be added automatically.
Note: The value in the Month column of the Expense Log is based on the value you enter in the Date column. When you insert a row into the Expense Log, the formula for the Month column won’t be inserted automatically. You’ll have to add it manually. Will show you how to do that in the next section.
The first expenses you’ll add to your Expense Log on a monthly basis will be recurring expenses. Recurring expenses are expenses that happen on the same day of every month for the same subcategory (think mortgage, electric bill, service contracts, cable, etc.).
On tab three of the Expense Tracker (RecurringExpenses), I provided some examples of common expenses that recur on a monthly basis. You, of course, will delete my sample expenses and enter your own. To add your recurring expenses to the Expense Log every month, follow these steps:
- Select all the cells that contain the recurring expenses (cells A2 to H12 in the screen shot below) and press Ctrl-C to copy the data.
- Move to the Expense Log tab (ExpenseLog2015) and select the first empty cell in column A (Category).
- Press Ctrl-V. Your recurring expenses for the current month will now be part of your Expense Log. Now, instead of manually entering all of your recurring expenses every month, you can accomplish the same thing with a simple copy and paste operation. Just make sure you update the Date column in the RecurringExpenses tab before you perform the copy and paste operation.
The bulk of your expense entries will be done manually. You don’t have to, but I try to keep my entries in chronological order. Adding an expense that occurs after the date of your last entry is easy. You enter data in the next available row. Say, for example, the last entry in your Expense Log is in row 12 and it has an expense date of 1/15/2015. If the next expense you need to enter has a date of 1/16/2015, you simply enter the next expense in row 13. If the next expense you have to enter has a date prior to 1/15/2015, follow these steps:
- Select the row you want your new row to go above (see the screen shot below that show a highlighted row 12).
- Now move the cursor over any of the highlighted columns and right-click.
- In the context menu that appears, select Insert 1 above (a new row will be inserted above the one you highlighted).
- Enter expense data in the newly inserted row.
- Copy Month formula for the newly inserted cell in column E (see the screen shot with instructions below).
The screen shot below shows that cell E11 is highlighted. This cell contains the formula IF(ISBLANK(D11),,Text(D11,”yyyy-mm”)). To copy that formula to E12, grab the blue box in the bottom-right corner of cell E11 with your mouse and drag your mouse to cell E12. The formula from cell E11 will be copied to cell E12.