Money may seem complicated, but using program Google Sheets, everyday expenses can be easily managed. And you don’t have to purchase another software either.
To save money avoid overspending the minimal form of expense tracking that one can and should use is Google Sheets.
What Your Spending Tracker Should Include
The process may sound like a lot if you always avoided tracking your spending in the first place; that is why it is essential to keep everything as simple as possible to get started. When dividing your google sheets, make two titled sheets for the most important steps involved in the process: the expenses log and the report sheet.
The first sheet should have the following five columns at least:
- Place: the store you went to
- Category: the type of item you bought
- Amount: the cumulative sum which you paid in that particular transaction
- Date: the date when you received the particular you had purchased
- Month: the actual date which was in a form of month of the year that the purchase was made—this column will be incorporated in the aggregation of the results.
One could try to expand the tracking to items but that would double the time and one might lose enthusiasm. Always bring along your receipt or if you usually forget the items you purchased at a store, include a miscellaneous notes column.
Creating a Color-Coded Dropdown Menu for Your Spending Categories in Google Sheets
Google Sheets make it easier to add categories to transactions by using dropdown lists. Performing this will enable you to easily activate an option within two clicks rather than typing it time and again. It also lets you maintain coherence and order in your input to find and filter them more easily later on.
You can do it by following these steps:
1. Bearing this in mind, it is important to focus our attention to the Category column. Pressing holding the CMD or CTRL key, right click on the title cell to deselect it.
2. Each time, take your right hand and right click on the mouse or the track pad. Instead, click on Insert in the top bar of letterhead. Click Dropdown. If you do this, it will open a sidebar.
3. Insert the categories you would like displayed in the text fields. Clicking on Add another item will create more fields or could just press Enter or Return on your keyboard.
4. When you’re finished, click Done and use the dropdown alongside the text field to color code your categories.
If you want to make changes or insert more data into your created dropdown later, it is also achievable by right clicking any selected cell where there is the used dropdown and click Dropdown. You can also get there from the Data at the top of your screen by clicking it and then on Data validation. Now you’re going see the list of rules, and we can select the rule of your choice.
Formatting Your Tracker
If you want to keep a consistent date and currency formatting under each cell in your Google Sheets, here’s how to do it:
Date Formatting
Should you utilise a date column, and you wish to stick to the current formatting of the workout cells, you can insert certain formats unto your cells. To do so:
1. Choose the whole column as one tap by the letter at top.
2. From your toolbar, select More formats, its icon is 123.
3. Choose Date—near it there is an example of the date it will appear as.
Or you can do this by selecting Format, located at the top of the screen, then selecting Number, then either Date or Custom date and time.
Month Formatting
By using TEXT function which has default option of extracting month name from the purchase date, instead of typing month every time any transaction is made. To do so, go to the first row in Month column and enter the following:
So, if the date is in cell D2, the formula will be below:
This will give the first three letters of month in the Month column. You can next apply it to the first cell and then change the cursor to a plus sign and you can just select the first cell and extend it to all other cells down the rows of this column.
Currency Formatting
To include the currency formatting to your amount column, right now the amount column > More formats and you click the dollar sign ($) there. The option is also available in the Number option on the toolbar under Format. Here Currency, Currency rounded and Custom currency options are available.
Custom currency enables the conversion of how the decimal and symbol are displayed or the type of dollars being used, or altogether another currency type.
Creating a Summary of Expenses Using Functions
After making the sheet for logging of expenses, shift to the other new sheet for the summary of the expenses.
Starting from cell B1 to M1 input the first three letters of every month’s name. For category total, Column N will be used. In the same way, from the second cell of column A, you begin to input the expense categories.
It is also important to provide the same categories as in the dropdown list of the first sheet. Next to each of the categories, a new cell will be used for monthly totals after all categories are entered.
Sel issue for populating the summary and calculate the total is that now while you have the basic structure, use the SUMIFS function. Depending on the multiple conditions this function will add the total of the selected range.
In cell B2, type the formula
Next, convert all cell references to absolute by going to a cell with cell reference selected, pressing the F4 key. After that, proceed to clear $ sign lying before 2 in formula A2 and B in the cell B1.
Once placed in the formula, choose cell B2 and pull it down right before the row labeled Monthly total. Then, choose the active cell B2 up to the last, cell before the monthly total and extend it horizontally across the columns M.
Next, in column N2, enter the formula =SUM(B2:M2) and drag it down. Similarly in the Monthly Total summarize expenses of all the categories incurred in a certain month by using the SUM function.
Now, whenever you input the expenses into the first sheet, then Google Sheets will always input them into that certain month and button in the summary. Also, you can see how much you spend in a month and how much you spent within a year for the selected category.
Visualizing Expenses With Pie Charts and Sparklines
In order to learn more about your spending behavior, you can enter a visual on expenses in Google Sheets.
First, insert sparklines to see how you spend on a particular category changes over the course of the month. In cell O2, enter the formula below:
After that, insert a pie chart to give the overall picture of your expenditures in different categories. To do so, choose insert and chart. This will give a new chart and the chart will open in the sidebar in the chart editor tab.
Next from the chart type dropdown list, click Pie chart or 3D Pie chart. In the Data range, add the range N1:Your last cell should be N8 or whatever cell you ended recording data into. Then, click on Add label and the four squares icon to select any range of data for labels.
Here, enter the range A1:A8. Now, Sheets will present a pie chart showing a division of the expenses made.
Using Filters to View Spending Categories
If you wish to sort or sometimes, you hide the transactions, you can then include a filter in your Google Sheets. To do this, pick the column you want to sort and click on the Create a filter icon at the top of the screen. Three lines will appear before the first cell of the column.
So just tapping the three lines will bring up a list of options. In the category columns , filter by value let you to quickly show only one category using the box below the header check all and uncheck it using its name.
So, with that simple expense tracker, you are able to track your expenses. Further, the thought of beginning a new project from scratch using a template you obtain from the internet is actually less fulfilling than getting a ready spread sheet, especially if one thing goes wrong with it you’ll understanding how to repair it.
It is very helpful to record transactions using the Google Sheets app when you on the move with your phone. That way, you are not lagging behind through out the month at the times when the tasks are still many. Even better, you can compile a budget worksheet so you can have a prior thought on where your money would go.
