![]() ![]() Copy this entire setup for each month of the year. Don’t forget to click on the Done button to apply these changes. Finally, under Formatting style, set the Text color to black and the Fill color to Close to light gray 3 ( #f2f2f2 ). In the text box that appears below this dropdown, type in 0. Under the Format rules section, select Greater than through the dropdown. This applies the conditional formatting to that range.Į. When the Conditional format rules window pops up, click Apply to range and set the range as G12:G18. Navigate to the Conditional formatting option through the Format menu from the uppermost ribbon.ĭ. For the Diff column, apply conditional formatting to each row, changing the text color from gray to black when the difference value is greater than 0.Ĭ. Resize the monthly Budget, Actual, Diff, and Utilization columns to a column width of 50. Note that there is a blank column ( D ) between Income I Earn and Budget to improve readability.įor the SPARKLINE options, create an array in which charttype is set to bar chart, the max value is set to the Total value of column Actual ($F$18), and the color is set to Light green 1, with hex code #93c47d. Set the initial data value to cell F13, which represents the Actual income from the Income Category Salary. The SPARKLINE function allows you to add pictorial graphs in the cells. To create this, copy and paste the following formula in cell H13 and drag it down the column to H18. For the Utilization column, we will create a Data-Bar-like structure to show the actual income compared to the budget amount. Type the formula = F13 – E13 in cell G13 to calculate this difference and drag the formula down the column to G18. The Diff column represents the difference between the Actual and the Budget columns. Repeat this process to create the total for the Actual column using the formula =SUM( F13:F17 ). In cell E18, type in the formula =SUM( E13:E17 ), which allows you to generate the total for the Budget column. Below each month, set up four columns representing Budget, Actual, Diff, and Utilization, respectively. The headers should read JAN, FEB, MAR, …, and DEC, with all the headings listed across row 11. In the columns to the right of column C, create 12 categories for months across the year. Add a thick Bottom Border for the Total INCOME cell. Set the cell color for Total INCOME to Close to light green 13 (hex code #cdf3cd ).ĭ. For cell C12, the font color is white, and the cell color is Close to dark green 2 from the color options (hexadecimal code #1d7e1d ).Ĭ. This will be the standard format throughout this sheet.ī. ![]() The font style for this particular template is Roboto, with the header set at font size 10 and all other cells at font size 8. You can see our examples in the screenshot below.Ī. Below the header, type the income categories that apply to your situation. This section will contain your income categories. In cell C12, Type “INCOME I Earn” as the column header. Open a new blank spreadsheet where you wish to create the Budget and Expense Calculator. Now let’s see how to make our own from scratch. Once you have set up the folder, click Make a copy in the bottom right corner to save a copy of this sheet.Ĭreating the Income and Expense Categories Since we are working with Google Sheets, we recommend storing it on your GDrive so you can work on it online. Rename the file as needed and save it in an appropriate folder either on your Local Computer or your GDrive. This will open up the Copy document dialog box. To make a copy of the file, open it from the link provided, go to the File menu, and click the Make a copy option. This also gives you a glimpse of important sources of income and key expense categories that you should keep an eye on.įinally, at the top of the sheet, create your own analysis of your Income and Expenses : how much you are saving, where to put your savings based on certain categories, and a visual representation of your savings.ĭownload a copy of this worksheet from “Please put the link of file from your shared path where you save all files for the articles. Review the difference between your Budget and Expenses through the Diff column, while the Utilization column provides a small pictorial infographic with the help of a custom formula to show how your Income and Expenses move across each category. With this tool, create Income and Expense categories and add rows to each to customize the list according to your personal lifestyle. Take a look at this Budget and Expense Calculator template you can create in Google Sheets: Does Google Sheets have a budget template?.How do I make a budget in Google Sheets?.Does Google Sheets have a budgeting tool?.Creating Budgets in Google Sheets: FAQs.Creating the Income and Expense Categories.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |