I want to keep track of my household finances in a Google Spreadsheet.
I also want to do a thorough analysis.
Do you have such a problem?
PivotTable is a tool that allows you to aggregate and analyze large amounts of data.
It is recommended for those who want to do a thorough analysis, as it allows analysis from various perspectives, such as by expense item, by account, or by store.
Features of the household account
- Enter expense items, account names, and store names on the setup sheet
- Record daily household accounts on the input sheet
- Compare monthly household accounts on the annual sheet
- Pick up only the information you want to know by expense, account, store, etc.
How to use the household account book (Google Spreadsheet)
Setting Sheet
The name of the expense item, account name, and store name are entered on the setup sheet.
Expense items are categorized into income and expenses (taxes, savings, fixed and variable expenses).
All items can be registered up to 10.
The information entered on the setup sheet is reflected on the input sheet.
Input Sheet
On the input sheet, enter the information in the table below.
Select the items with pull-downs, and enter the rest.
Entry Details
- Date: Enter date (e.g. 22/1/3)
- Category1: Income OR Expenses
- Category2: Income1, Taxes, Savings, Fixed Expenses, Variable Expenses
- Item : Expense (expense line item for what you set in Category2)
- Product name: Can be left blank
- Amount: Amount of goods or items
- Store name: Enter the name of the store, if applicable
- Account: Enter the name of the account, if applicable
- Note: Enter any notes you wish to make.
- Formula: Please do not delete the formula.
The amount column should be negative for all expenditures.
Category2 and the Item column will change based on the selection made in the left row.
If you select Expenses in Category1, Category2 will show Taxes, Savings, Fixed Expenses, and Variable Expenses.
If you select Fixed Expenses in Item the Expense column will display the fixed expenses.
To add a row, select the last blank row and drag it down.
To make entry as easy as possible, there is a column on the right side of the table for setting fixed and other expenses.
By entering items other than variable expenses in this column, you can copy and paste this information into the table on the left each month.
Correct the date and amount if necessary.
How to change currency units
Select the Amount row and click on Format,Number,Custom currency at the top.
The default setting is set to dollars.
When you have finished entering the information, move to the annual sheet.
Annual Sheet
There are no items to be entered on the Income and Expenses sheet.
The information entered on the input sheet will be displayed as a table by month and by expense item.
The bottom line, Total, shows the amount of income minus the amount of expenses.
I want to display them separately by expense item, by store name, by account, etc.
A slicer is available for such occasions.
Slicer Functions
For example, you can enter a specific product name in the item name on the input sheet to display only that specific product name.
This is also useful if you want to know how much you spent on Amazon in a month or how much you spent on your credit card in a month.
Click on the symbol to the left of the slicer and check only the names you want to search.
At first, all are checked, so it is easy to click clear and then make your selections.
Only items where the account is A-Card are now displayed.
To undo, go back to the slicer and click Select All.
If you have added new data on the input sheet, change the data range from Edit.
If you have changed the content of the data, this is not necessary.
You can edit the data by clicking on the Edit button under the total on the bottom row.
Please select data from the input sheet or choose from the Suggested.
How to Use Household Budget Bookkeeping (Microsoft Excel)
Setting Sheet
The name of the expense item, account name, and store name are entered on the setup sheet.
Expenses are categorized into income and expenses (taxes, savings, fixed and variable expenses).
A table is automatically added as you enter the information.
There is no limit to the number of entries.
Input Sheet
On the input sheet, enter the information in the table below.
Select the items with pull-downs, and enter the rest.
Input details
- Date: Enter date (e.g. 22/1/3)
- Category1: Income or Expenses
- Category 2: Income1, Taxes, Savings, Fixed Expenses, Variable Expenses
- Item: Expense (expense line item for what you set in Category2)
- Product name: Can be left blank
- Amount: Amount of goods or items
- Store name: Enter the name of the store, if applicable
- Account: Enter the name of the account, if applicable
- Note: Enter any notes you would like to make.
The Amount column should be negative for all expenses.
Category2 and the Item column will change according to the selection made in the left row.
If you select Expenses in Category1 will show Taxes, Savings, Fixed Expenses, and Variable Expenses.
If you select Fixed Expenses in Item, the Expense column will show Fixed Expenses.
To make entry as easy as possible, there is a column on the right side of the table for setting fixed and other expenses.
By entering items other than variable expenses in this column, you can copy and paste this information into the table on the left each month.
Correct the date and amount if necessary.
How to change currency units
Select an amount cell and right-click.
Select ”Format Cells ”and click on ”Currency”.
Select your local currency unit in Symbol.
The default setting is set to dollars.
Annual Sheet
After entering the information on the input sheet, open the annual sheet.
There are no items to be entered on the annual sheet.
The information entered on the input sheet will be displayed as a table by month and by expense item.
The bottom line, Total, shows the amount of income minus the amount of expenses.
Clicking on the button for the month labeled "Jan" will display the amount by day.
Click again to return to the original amount.
If you do not see monthly totals, you will need to group them.
Right click on Date and select Group.
Click on Year, Month, and Day.
Slicer Functions
For example, you can enter a specific product name in the item name on the input sheet to display only that specific product name.
This is also useful if you want to know how much you spent in a month at a particular store or how much you spent in a month by credit card.
To undo the change, click the faction mark in the upper right corner of the slicer.
If you have modified the information or added new data in the input sheet, click the Update button.
Click on a part of the pivot table (anywhere) and click on Pivot Table Analysis, Update at the top.
Free Download
Click here to download the Google Spreadsheet.
After downloading, please make a copy of the file before use.
The downloaded file is not editable.
Click here to download MicrosoftExcel.
