Hey everyone, thanks for coming here. For this post I'll teach you how to make a budget template that will automate based on what you enter in a tracker show you how much money you have left in each category and will change based on what month you select in the drop down list. And the best part is you can do it in less than fifteen minutes. So let's get started.

All right. So the first thing we need to do is create a table that will be entering transactions into. So adding headers is the first part I'd like to have these headings, but you could probably do whatever you want to. I add months, the date, the description of the transaction, the category of the transaction belongs to a column for income values, a column for debit values and then the running balance. Now I'll turn this into a table with alternating rows. I'll do that by highlighting the columns I want to turn into a table and then select format as table from the home ribbon bar green with the alternating color seems to make sense for a budget, but pick whatever color you want once you get the pop-up. Ensure that the table has headers option is checked, then stretch the columns to a size that satisfies with you.

The next step is to add the necessary formulas and number formatting to the table to get it to do the most work for us. I'm going to add the starting date so that we can build one of our formulas off of that. Then I'm going to make the date format. Umm...something that I like by highlighting column b and right clicking and selecting cell format here, I can change the date format by clicking date on the number tab.

Now I'm going to set up a formula in column a to pull only the month number from the cell in column b. I can do that by typing equals month opening parentheses, then clicking on cell b two, then closing parentheses and hit enter the formula should autofill for the rest of the column since this is part of a table. Now when i enter new dates, it will autofill the month for that date. We'll use this later.

The next thing I'll do is add drop down lists in column d. This will allow me to pick what category expenses are in. How to do that? I am going to highlight all of column d, but then hold down the control key and unselect the header now from the data table, I'm going to select data validation from the ribbon when you see the pop up. Choose a list from the allow drop-down list in the source type the categories you want with a comment in between each. I'm going to add income, mortgage, utilities, grocery and gas and entertainment.

The next step is the format column e through g s currency that's simple, highlight the columns and then click on the dollar sign from the ribbon in the home table.

The next step is to add a formula to get a running balance before that. I'm going to add a bunch of transactions to the list, so you can see how this works in a simplest form. So now that I have a bunch of expenses and some income added, we can work on the formula to get a running balance. It's simple actually. I'm going to do this in two steps. I'm going to type the actual starting balance and sell g. Now in the next cell, I'm going to put equals some then opening parentheses, then take the balance in the previous cell and then add income and subtract expenses on this role, closing parentheses and hit enter.

You'll get an option over here to hit autofill. We aren't going to do that for this part because it would replace what we have in g two for the starting balance. Instead, we are going to copy what we have in g three and select all of column g then while holding the control key down, unselect the header cell and the starting balance cell, now hit control v to paste the formula in the remainder of the column. There is a running balance you can see now that if I add expenses or income, the running balance changes.

Now we are ready to start adding the progress bars, which will show us how much income we still need to make and how much we can still spend in each of our categories. I'll start that process by adding all of the categories we want to track column i. Then I'll add a total column in j. Then I'll add a month. I want to focus on and sell i. Now I am ready to start the summit formula. I'll type equals some ifs, then opening parentheses, then I'll select the income column as my some range than the month column as my first criteria range. And i one as the first criteria, then I'll add the category column as my next criteria range. An income as my second criteria. Now I can close it with a parentheses and hit enter this formula will sum the total income in January.

Now let's work on the expenses. I'll type equals summits opening parentheses, then select the debit column as my sum range the month column as my first criteria range. The month as the criteria, the category column. As my next criteria range and then gas groceries as my criteria closing parentheses and hit enter. Now I should be able to just drag the formula down now.

Now I am ready to start adding my progress bars, which will show me how much income I still need to make and how much money I have available yet to spend in each category. Let's start with income. First, I'll click on the cell with the value in it from the home tab. I'll press conditional formatting and select data bars. I'm going to choose green for income. I'm also going to stretch the calm to make it long.

Now, when I am still on that cell, I'm going to click on conditional formatting and then on manage rules from there, I'll double click on my rule. Now I'm going to change the minimum and maximum to numbers. I'll leave the minimum at zero and since I expect to make about four thousand dollars in a month. I'm going to change the maximum to four thousand, then I can click O.K. apply and O.K.. You can see then since I've tracked two thousand worth of income so far in January, then I'm about halfway there. You can also see that if I change the values of income and the tracker, the progress bar will update.

Now I'll do the same for expenses, but I'll do them in red. A budget four hundred a month for gas and groceries. A budget, a hundred dollars for entertainment. I'll budget two hundred and twenty five for utilities. And our budget exactly one thousand twenty three dollars and eighty cents for mortgage since it's auto pay and it's the same amount every month. Now I'll just clean this up and make it pretty by centering the month, adding some cell borders and adding outside border. The coolest thing about this is that it resets every month.

You can see that if I add expenses for February and then changed the month or two, the progress bars will start over and that's how you make a simple automated budget in under fifteen minutes. Thanks for coming and read this post "automatic budget calculator" here everybody. See you next time.