How to build your Expense Tracker from scratch! | Debbbag Deborah Ho Expense Tra

How to build your Expense Tracker from scratch! | Debbbag
Deborah Ho
Expense Tracker 2.0 Tutorial
Hey everyone, it’s Deb! I created the expense tracker that went viral on TikTok, and since then, I’ve had a lot of requests to show you how I made it from scratch. It’s actually super easy to build, even if you’re not an expert in Excel. In this video, I’ll be building a new and improved version of the expense tracker using the exact same methods I did with the original one.
The new version of the expense tracker has a summary of your expenses and income, as well as their own dashboard. You can download Expense Tracker 2.0 from the link in my description. Let’s get started!
Creating the Expense Form
Create a form for your expenses in Google Drive by going to “New” > “More” > “Google Forms”.
Set the title of the form as “Expenses”.
Add the following questions to the form:Purchase Date (automatically updates).
Item (short answer).
Amount (number only, with required condition).
Category (drop-down menu with five options).
Receipt (file upload).
Preview the form and make any necessary adjustments.
Create a spreadsheet to link with the form by going to “Responses” > “Create Spreadsheet”.
Open the newly created spreadsheet and rename it as “Expenses”.
Creating the Income Form
In Google Drive, go to “New” > “More” > “Google Forms”.
Set the title of the form as “Income”.
Add the following questions to the form:Date (required).
Income Source (drop-down menu with multiple options).
Income Amount (number only, with required condition).
Invoice or Pay Slip (file upload).
Preview the form and make any necessary adjustments.
Link the responses from this form to the “Expenses” spreadsheet by going to “Responses” > “Create Spreadsheet” > “Select Existing Spreadsheet” > “Select” (choose the “Expenses” spreadsheet).
Create a new tab in the “Expenses” spreadsheet and name it as “Income”.
Adding Sample Data
To build our tracker around, let’s add some sample data to both the expense and income forms:
Expense Form:January 1st – Sample 1 – $70 – Category 1
January 1st – Sample 2 – $80 – Category 2
January 1st – Sample 3 – $90 – Category 3
January 1st – Sample 4 – $100 – Category 4
January 1st – Sample 5 – $110 – Category 5
Income Form:January 1st – Workplace 1 – $1,000
January 1st – Workplace 2 – $2,000
January 1st – Workplace 3 – $3,500
Creating the Dashboard
Add a new sheet and name it as “Main Dashboard”.
Create the axes for the dashboard:Month
Categories 1-5
Total Expenditure
Total Income
Gross Savings
Format the colors for each category and data section.
Adjust the formatting for better readability.
Now you have your Expense Tracker 2.0 ready to use! Feel free to customize it further to suit your needs.
To customize the date format, go to Format > Number > More Formats > More Date and Time Formats. In the format options, delete the day, dash, and slash. Then, select the month as full name and choose the numeric year. Apply the changes to display the date as “January 2020” for the entire year.
To visually separate the category section from the rest, add a left border between the sections. Additionally, create a gray bar labeled “Total” and a green bar labeled “Budget” at the bottom of the sheet.
To calculate the basic sums, use the sum formula. For the total, the formula should be “=SUM(B2:B13)”. Drag this formula across the desired range. For the total expenditure, use the formula “=SUM(B2:F2)” and drag it down the rows. For gross savings, the formula is “=H2-G2”, and it should be dragged down accordingly.
To create an income dashboard, duplicate the main dashboard, rename it, and customize it by replacing the workplace names and removing unnecessary columns. Delete the total income and gross savings columns, and rename the remaining column to “Total Income”.
The expense tracker formula consists of five parts. It collects the data and sums it if the expense falls within a specific date range and category. It uses the SUMIFS formula with specific conditions for each part: getting the expense amount from the “Expenses” sheet in column D, specifying the start and end dates from column B, and selecting the category from column A. The formula uses dollar signs to lock certain parts of the formula while allowing others to adapt when copied to different cells.
Apply the formula to the spreadsheet by copying and pasting it. Then, drag it across and down to display the expenses. Format the numbers as accounting for a more professional look.
For the income dashboard, paste the formula but replace “Expenses” with “Income” to reference the correct sheet. Adjust the columns accordingly. The income amounts should now be displayed.
First, let’s transfer the sum of the income from the Income Dashboard to the main dashboard. To do this, select the income data and go to the Income Dashboard. Then, use the formula “=SUM(income)” to transfer the sum.
To color code the expense list, go to the Expenses sheet and select the entire column. Then, go to Format > Conditional Formatting. Create rules to format text based on categories, such as Category 1 in red, Category 2 in orange, etc.
Next, select the column where the income sources are listed. Go to Format > Conditional Formatting and create rules to format text based on workplaces, such as Workplace 1 in red, Workplace 2 in orange, etc.
To add the budget section and adjust the cells, go to the main dashboard. Enter the budget values and duplicate them across. Then, select the entire budget section and go to Format > Conditional Formatting. Use a custom formula to compare the budget values with the assigned budget and format cells if the value is greater than the budget.
Make some finishing touches by making the title and total bold, and adding color to certain sections. Calculate the total budget and format the income dashboard section.
To create graphs, select the data range and go to Insert > Chart. Choose the desired chart type, such as stacked bar chart for the yearly breakdown and donut chart for the categories. Customize the chart settings, including titles, colors, and labels.
Repeat the same process for the income dashboard graphs, including stacked bar chart for the yearly income and donut chart for the workplaces.
Let’s start by customizing the pie slice. Click on “Place One” to change the colors. Then, change the title to “Income Sources”. Go to “Pie Chart Slice Label” and then “Value”. Once you’re done, drag it down here. And that’s it! You’ve created your own expense tracker from scratch.
Now, let’s talk about transferring old data to your new expense tracker. If you have some old data from your CSV file or previous expense tracker that you want to transfer, it’s super simple to do. Just make sure that the purchase date, item, amount, and category are in the same columns that you’re going to copy over.
To transfer the data, copy all of the old expenses and go to your expenses in the new tracker. Then, simply paste the data. This also works for recurring payments, so you don’t have to manually input them every month.
Once the data is in, you’ll see that the payments are already registered. To keep your new expenses organized, it’s recommended to select all of the rows and move them to the top. This way, any new data that comes in will continue to be filled out below.
Here’s an example: Go to expenses and enter “Cake” for $3.99 under the category. Submit it and then go back. You’ll see that it continues listing the expenses after the previous ones. The payments listed above don’t affect the order of your new expenses.
That’s it! You now know how to transfer old data to your new expense tracker. If you want to download my expense tracker 2.0, you can find the link in the description below. It’s free and simple to use. You can customize it to suit your changing lifestyle by playing with the graphs, form responses, and colors. The possibilities are endless with Google and Excel sheets. If you want to customize your tracker even further, check out my tutorial linked below. Good luck and thank you for joining me on this tutorial!