Easily Manage Your Customer Support with Our Google Sheets Invoice Tracker Template for Customer Support

Google sheets invoice tracker template for Customer Support

welcome to Blooming Excellence today we're diving into how to create a simple and effective monthly planner with an integrated expense tracker using Google Sheets by the end of this tutorial you'll have a powerful tool to plan your month and track your spending effortlessly let's get started first we have the date column here you can select a specific date using the pop-up calendar feature in Google Sheets or if you prefer you can pre-fill all the dates for the month by simply typing the first View and dragging the fill handle down it's quick and hassle-free the income column is further divided into two sections amount and category the amount section is where you record the income figures and the category section is for noting how the income was earned similarly the expense column is also split into amount and category here you can record the expense amount and include details about where the money was spent this setup ensures everything is clear and easy to track then we added the balance column which automatically updates the remaining amount by subtracting the total expenses from the total income next we maintained separate sections for expense categories and income categories for expense categories this helps track how much you've spent on each category over various dates for example let's take the expense categories suppose you spent $1,000 on food on January 2nd 2024 and another $500 on food on January 10th 2024 in this section the total amount spent on food $1500 will be automatically calculated then we created a pie chart to visually represent expenditures across various categories this chart helps you quickly identify which category you're spending the most on providing valuable insights into your spending habits for example let's say the housing category accounts for 33.3% of your total EXP expenses if you increase the amount spent on housing from $4,000 to $6,000 the pie chart will automatically update to reflect the new percentage this makes it easy to track changes and adjust your budget as needed next we added a summary section for the total income and expenses for the month these values are also updated automatically as you enter data to make it even more visually appealing we included a bar chart to compare the income and expenses side by side this gives a clear picture of your overall financial situation at a glance and that's it for the overview in the next section we'll show you step by step how to create this entire setup from scratch so don't skip it let's get started let's get started after opening Google Sheets the first thing we'll do is rename the file to monthly budget then we'll rename the sheet to something like jam if this tracker is for January next we'll set up our columns start with the basics date income expense and balance for the income section add two sub columns amount and category then do the same for the expense section two sub columns for amount and category finally let's create separate sections for income categories and expense categories to keep everything organized and that's it for the initial setup ready to move move on all right let's start by merging the income columns first select the cells you want to merge under the income heading then click on the merge cells option and just like that the cells are combined into one easy right now repeat the same steps for the other sections that need merging next let's add some borders highlight all the columns or cells where you want to apply borders then click the borders icon in the toolbar and select all borders this will neatly outline each cell finally let's add some color to make it pop select the cells or sections you want to fill with color click on the fill color icon it looks like a little Paint Bucket and pick your preferred shade and once everything is done merging the cells adding borders and filling in the colors your table will look super clean organized and Visually appealing this is the final look we were going for and it's all set and ready to use let's start with the First Column which is the date column to select the date from the popup calendar here's how to do it first select the date column then click on the insert option and choose drop-down on the right hand side the data validation rules will appear now set the criteria to is valid dat and click done that's it you'll see a popup calendar and you can select the date from it if you want all the dates to be pre-filled for the month here's a quick trick simply type the first date then drag it down to fill the rest of the month and just like that all the dates will show up super easy so now let's talk about how to set up a drop- down list for selecting categories this makes things way easier than typing them out every time first let's start with the income section select the cells where you want the drop down to appear then head over to the insert option and choose dropdown a panel will pop up on the right side where you'll want to pick drop- down from the options next click on the select data range button a window will pop up just select the categories we already typed in then hit okay and that's it now you've got a drop- down list where you can easily choose a category you can even pick a color for each category if you want to make it look nice once you're done click done now don't forget to apply the same process to the expense section now let's see how to set up a formula for autoc calculation first we want the balance amount to be calculated automatically so click on the first cell in the balance column and use this formula equals B3 minus D3 which is income minus expense next in the following cell type equals F3 + B4 minus D4 this formula finds the current balance by adding the previous balance to the new income and expense now to make sure it works for the entire list drag the formula down to the end of the column that way whenever you type in income an expense the balance will be autoc calculated let's check it with an example let's select freelance as the category and enter the amount as 1,000 in income for the expense select entertainment and enter 500 and there it is the balance updates automatically so now let's figure out which category we've spent the most and the least on this is super useful for bud budget analysis start by selecting the cells where you want to calculate the totals for each category then use the sum IF function in the formula first type Eon e to select the categories column next select the specific category you're working with after that type Deon D to include the expense amounts once that's done drag the formula down to apply it to all the categories let's check it with an example if we select entertainment again and add another 1,000 the total value will update to 1,500 automatically in the entertainment section apply the same to income section also see easy hey there let's dive into how to create a pie chart for Budget analysis in Google Sheets pie charts are such a great way to see how your expenses and income categories stack up visually so let's get started first things first make sure your budget data is ready ideally you've got a table with categories and their totals if not pause here for a second and set that up it'll make things much easier all right once that's ready highlight the data you want to include your categories and their totals now go up to the menu bar click on insert and select chart from the drop down you'll see a default chart pop up probably not a pie chart yet but don't worry over on the right side the chart editor will appear under the setup tab look for chart type scroll down find the pie chart option and click it and there you go you've got your basic pie chart now let's make it look awesome click on the chart then tap the three dots in the top right corner and choose the edit chart this opens the chart editor again and we'll head over to the customize tab here's where the fun starts in chart Style you can change the background color to something that pops and if you're into it enable the 3D option for a cool effect next under the pie chart section select slice label as percentage this way you can see exactly what percentage of your budget each category takes up then in chart and access titles you can rename your chart centeralign the title and even tweak the color to match your Vibe finally in the legend section move the legend to the right it just looks cleaner that way and there you have it a fully customized pie chart to analyze your budget doesn't that look great now that we've set everything up let's add a summary for the budget we'll calculate the total income total expense and the balance to find the total income click the cell where you want the value to appear then type equal Su once you see it press tab now select the range of values in the income column and press enter done we'll do the same for expenses click the sell type equals sum press tab select the range of values in the expense column and hit enter again that's it for the balance just click the cell where you want the balance to show type equals then click the total income cell press the minus sign and click the total expense sell hit enter and your balance is ready and just like that we've created a summary report almost done if you want to visualize this as a bar chart here's how select the cells for total income and expense go to the insert menu and click chart boom a bar chart pops up showing income and expenses you can customize this chart just like we did for the pie chart earlier and there you have it your monthly budget tracker is ready to use it's automatically saved and the best part you can reuse it for any month easy right thanks for watching subscribe and press the Bell icon so you never miss a video