Uncategorized

Setting up a basic budget in excel

The easy way to write an excel budget

Note: if you don’t have excel, you can create a spreadsheet using google docs.  Google sheets (the spreadsheet product through docs) is very similar to excel and provides the same functionality described below.  In addition, google docs are available anywhere you have internet, which can make budgeting even easier! 


As promised, I’m here to walk you through setting up an excel budget.  I’m going to be using excel, but of course you can use another sheets tool or a pen and paper to do this.  I think using a spreadsheet like excel has a number of advantages, especially when it comes to functionality.

With a little work, you can set up an interactive document that can produce reports and stats about your spending in a way you like to see.  Remember my budget?  Something I love more than anything is the personalization and color coding that I have in my personal excel document.

If you are a budgeting pro, this is going to look super basic.  But for beginners, this is where you start!  Just tracking your money in a simple spreadsheet on a regular basis can make a world of difference in how you take care of your money.  You can do it!

Are you ready to set up your excel budget?

Let’s get started!  Open up a new document.  Lets start with expenses.  Start by listing the most obvious ones, the bills you pay every month, and and subscriptions you have.

Next, think of any expenses you have on a yearly basis.  This might include insurance premiums or subscriptions that are paid once a year.

I like everything to look tidy, so double-click on the line between column A and B to expand the column out to fit all the text nicely!

Add your income

Now that we’ve gotten all of our expenses, we are going to start the income section.  My husband works full-time and I blog part-time.  Your income will look different.  Any sources of income, even irregular ones, should be accounted for.

Excel trick: grouping

The budget is getting a little crowded, so we’re going to work a little magic to make things nice and tidy.  Of course you can skip this, but it’s always fun to learn a new excel trick to show off to your neighbor.  On the ribbon up top, navigate to the Data tab.  Look for the group button.  (If you’re confused, this short tutorial can clear this up OR you can skip forward!)

Highlight the categories under expenses and hit the group button.  Repeat for the income categories.

Now we can shrink these up really nicely using the “-” button on the far left.  Let’s quickly add the sums for all the categories.  Expand out the expenses and income by pushing the “+” icon on the far left.

Enter formulas

Excel has built-in formulas so you don’t have to add each cell individually.  Just type an “=” sign in the cell to the right of Expenses.  After the equals sign, you will need to enter a formula.

To do a summation of all of your expense categories, you can type “sum” and the formula options will pop up.  You can either hit the “Tab” key or manually select the SUM formula.

After you select the SUM formula, an open parenthesis will appear.  This is where you can add in all the cells you want to be summed up.  You can easily do this by highlighting all of the cells to the right of your expense categories (remember, this is where the numbers will go!).  Hit “Enter” on the keyboard and you will get a nice little “0” to the right of expenses.

 Hit “Enter” on the keyboard and you will get a nice little “0” to the right of expenses.  Do the same thing with the “Income” section.

Now our remainder formula is easy to set up!  Minimize the income and expenses categories by pushing the little “-” on the far left.

Calculate remainder

Working from the cell to the right of Remainder, enter an equals sign.  After the equals sign, you will need to enter the formula formula…

Your remainder each month is your expenses subtracted from your income.  Simply highlight the cell to the right of income, then type a minus sign, then select the cell to the right of expenses.  Then you can hit “Enter” on your keyboard, and everything is in order.

Now it’s up to you!  Go fill in your expenses, your income, and see what you have left.  If you have a positive number, that is awesome!  Put some of that money into savings!  If you have a negative number, now is a great time to see what categories you are spending too much in.

To figure out your monthly rates on insurance or yearly fees, just take the number and divide it by the number of months between payments.  That gives you your monthly “payment” number.  Eventually, we want to put these monthly payments into a separate savings account, but for now, just put the number in your budget so you can see how things stack up.

Format for currency

If you want everything to look really official, go back to the Home tab on the ribbon.  Highlight all of column B by clicking on the “B” at the top, then hit the $ sign. This formats your numbers for currency.

Finished!

Now you are done!  Start tracking your expenses every month and see how you’re doing.

UPDATE:

I wrote a few posts for tweaking your spreadsheet.  You can learn how to group to make your spreadsheet more readable, and I also wrote about adding data bars.  I love data bars because they help make the numbers more visual.

Now that you have the basics, are you interested in pimping out your excel sheet?  I’ve been thinking of writing a series on excel tips and tricks for budgeting.  Would you find that helpful?  Let me know in the comments!  Thanks friends!

Leave a Reply

Your email address will not be published. Required fields are marked *