CASE STUDY 6
Google Sheets -PERSONAL EXPENSES TEMPLATE
A fancy way to select data on a Personal Expenses Template
Google Sheets is used by many in building all kinds of tables, easy or complex calculations for business but also for personal use.
Sometimes, you want to gather all your expenses in one place for various reasons:
- Optimize your spendings (evenly spread the costs throughout the year)
- Correlate income and spendings to ensure positive cash-flow
- Find areas where you can reduce cost
- Moving to another country - see the difference in the cost of living to validate your new income estimates.
The following Personal Expenses Template is addressing a big part of the topics above with a major focus on the Personal Expenses Template part of the exercise. In a separate tab, you can always add the income part of the financial exercise and gather all the info in one, master dashboard.
The scope of this exercise is to:
- Show how you can use the checkbox in Google Sheets to show specific data when a specific checkbox is ticked,
- Demonstrate how to avoid showing the error messages of a division by 0.
Registered members will SEE MORE and will get the link to the Google Sheets - EXPENSES.
In this file, the main functions used are: IF, VLOOKUP, COUNTIF
Monthly Expenses per category:
=if($H$11=TRUE, VLOOKUP(B11,’Monthly expenses’!$A:$P,3,0),)
+if($H$12=TRUE, VLOOKUP(B11,’Monthly expenses’!$A:$P,4,0),) … etc
- We are searching for the monthly expense on the category from the column B in the tab: ‘Monthly Expenses”: VLOOKUP(B11,’Monthly expenses’!$A:$P,3,0),)
- Then, we will show this value ONLY if the month of January is ticked on: if($H$11=TRUE …
- Then, since we have 12 months in the year, we are making the same search for the other 11 months, summing up the results every time.
Monthly Average:
=iferror(C11/COUNTIF($H$11:$H$22,TRUE),)
- If you want to calculate the amount spent on average per month, you need to divide the total we got in the previous step by the number of months you selected.
- Counting the number of months we selected (ticked checkboxes) will be done by the following function: COUNTIF($H$11:$H$22,TRUE)
- In order to avoid an ugly error message for the case when we do not have any month selected, we add the IFERROR function and end up with the final formulas shown in the beginning.
Data validation:
In order to get those checkboxes, you will need to
- select the cells where you want them to show
- right-click on the selection
- select Data Validation
- on Criteria, select “Checkbox”
This content is locked
Login To Unlock The Content!
