How To Create A Budget Using Excel

*Note – all financial figures contained within are not actual monetary amounts that I posses. They are for demonstration purposes only. Please don’t get the idea that I’m rich, because I’m not. *

To create a budget, whether small or large, simple or complex, is to give yourself a strong pillar to help build your finances upon, in order to break free of the chains that not having organized finances can create. Many methods exist for creating a budget, from as simple as using a pen and paper, to using elaborate applications such as Quicken and Excel. I started out using pen and paper back in high school, but quickly moved toward something that would give me more power and flexibility. Since I didn’t own Quicken, but did own Excel, this is what I used, and it has been the heart of my financial world ever since. Over the years, I’ve expanded and adapted my budget to changing circumstances. All of this, has been made simpler using Excel. When you learn how to create a budget using excel, you are learning to use one of the most powerful tools available to you, for managing your money.

I’ve already talked about the basics of creating a budget. To expand on this, I present to you, how I use Excel to manage my money, and how you can begin to do the same.

Initial Steps

These initial steps are not an exhaustive list of everything that can be done. With enough creativity, the steps that can be taken are enormous. I’m covering the basics of what I have done, but you are free to customize all you want.

Step 1 – Dates

One of the first steps to take, is to define each budget cycle’s cut-off day. This is the day that your budget money gets reset to the cycles amount. Most people will probably use Friday, since this is the end of the week. This is exactly what I do. These cutoff dates are located in the very top row, in order to establish everything below the date, as falling into that cycle. A picture is worth a thousand words:

You can see a couple of points here.

1. I establish each column in the Excel book as representing exactly one week.

2. Each weekly cycle ends on a Friday. This means that the next week begins on Saturday.

The great thing about using Excel, is that if you type in a date in the correct format (the format up above works), and drag the column to extend into adjacent columns, it will fill in the date for you. All you need to do, is place the mouse in the bottom right corner, where your cursor will become a ‘plus’ sign, click and drag to the right. Again, here is a picture of what I mean.

You can drag your date headers as far as you need, whether it be a month, a year, or ten years. I use one year, and then create a new tab for each year.

Step 2 – The Names Column

The next step I recommend taking, is to create a column that represents every account, expense and income that you have. These are basically categories that each financial transaction falls into. This column should be the very first column in your Excel book, on the left. Here’s a picture of what I mean.

Structure
This column sets up what the rest of your budget will look like. It’s very critical that this column gets organized in a way that makes sense to you. You can certainly set it up differently than I have it.

Sections
This column should be broken up into different sections. You can see that I have an ‘Accounts’ section, an ‘Income’ section, sections for ‘Savings Withdrawls and Deposits’, and an ‘Expenses Sections’. Each section has a header, that is displayed in black, so as to further distinguish the different areas.

Color Coordinated
Each section needs to be color coordinated in a way that makes sense to you. Doing this allows for dramatically increased readability, and organization. I have used the obvious choice of green for income, and red for expenses. This is entirely your choice.

Bottom Columns
The columns at the bottom, ‘Over/Under Budget’ & ‘Individual Purchases’ represents where I track everything I purchase, and keep track of whether I’m over or under budget for the week. More on this down below.

Step 3 – Freeze Your Panes

*This step refers to working with Excel 2007. If you require a set of instructions for a different version, please check here.*

This is a critical step that you can take, when it comes to improving the usability of your Excel budget. When you start getting more and more data on screen, often times not all of it can be shown, and will end up scrolling off of the page. This can be fixed by freezing certain columns and rows, so that they don’t scroll, but will stay put, while everything else scrolls.

I’ve chosen two areas to freeze in my budget – The entire Names Column, which we just talked about, and the ‘Accounts’ section (basically everything down to row 10. This allows me to move around the data in my budget while being able to see what category I’m in, and what affect changes will have on my accounts.

To freeze these sections, depending on which version of Excel you have, find the ‘Freeze Panes’ option in your toolbar. In newer versions of Excel, you don’t even need to highlight the columns/rows you want to freeze.

Simply select ‘View’, select ‘Freeze Panes’, and select both ‘Freeze First Column’ & ‘Freeze Top Row’ as shown below.

This doesn’t work, however, to freeze all the rows for our accounts. In order to freeze multiple rows and columns, you need to select the first cell below the rows you want frozen, and to the right of the columns you want frozen, and then select ‘Freeze Panes’ from the Freeze Panes menu. So to freeze column A, and rows 1-10, you need to select cell B11, and the select ‘Freeze Panes’.

(Note – if you have panes frozen already, the option will say ‘Unfreeze Panes’, as shown in the picture)

For more detail on this, take a look at this video.

The Power of Excel – Hooking It All Up

This is the key step. The true power of using Excel lies in the ability that you will have to use Excel functions to hook up all your income and expenses, to each of the accounts you have set up.

Consider these pictures for a minute:

This may seems complex, but it’s extremely easy to set up and work with, once you understand how it works. Each of the accounts at the top, whether it’s the checking account or a savings account, hooks up to the income and expense that are recorded in the rows directly below them. Each account is hooked up to different pieces of data, and you can set it up how you’d like.

Excel SUM Function
To understand how this works, and set it up, you need to understand the Excel SUM function. The SUM function works exactly how you would think it works – its adds up a series of figures, and displays the total in whatever cell contains the function. This function can contain static data, such as ’10′, ’20′, or ’30′, or can refer to other cells in the spreadsheet. For example, you can add up the total of three cells using the following statement:

=sum(A1 + A2 + A3)

Adding up a series of cell values can be done this way, instead of typing in 20 different values:

=sum(A1:A20)

(Note – Having the ‘=’ sign in there is critical, it won’t work without it.)

For more information on the Excel SUM function, check this out.

Example

I’ll go through the details of how my checking account is hooked up, since it’s more complicated than the rest. Every other account works the same way.

Step 1
The first step, is to total up all expenses in the ‘Expenses’ section. To do this, using the formula above, we enter the following function into cell C44.

=sum(C32:C43)

This will add up the total of all expenses that are entered in cells C32 through C43. Setting up this ‘total expenses’ cell for each column, allows us to use this value in calculating how much we have left in our checking account, after all expenses are taken out.

Step 2
The next step, is to set up the function for the checking account itself. To do this we insert the following function into cell C4:

=sum(B4 + C14 + C15 + C16 + C44 – (C23:C26) – C28 – C29)

Breaking this down, we get the following:

1. B4 – the previous week’s ending checking account balance.
2. C14, C15, C16 – adding in all income for the week.
3. C44 – subtracting out all expenses (we add the value, since the value is negative).
4. C23:C26 – Subtracting out all savings account deposits, since it comes out of checking.
5. C28, C29 – Adding in any savings withdrawals that are made, since it goes back into checking. (subtracting a negative, gives you a positive)

Using this function, you can see that if you make any changes to any of the cells that the function refers to, it will update the balance for your checking account in cell C4. Also, by hooking it up to the previous week’s balance, you can create cascading changes where any change you make, will change your account balances weeks and months down the road. This is a very critical benefit of using Excel, as it allows you to make changes in the present, and reflect on changes you see down the road that result from those changes. To create these cascading changes, you just drag the function into the cells to the right of the starting cell, using the method that I describe above in the ‘Date Section’.

In order to hook up the savings accounts, all you need to do is hook up to the ‘Deposit’ cells, ‘Withdrawal’ cells, and ‘Interest’ cells, using the SUM function I demonstrated above. Also, make sure to hook up to the ‘Previous Week’s Value’ cell for each account.

Bonus Step – Individual Purchases

Last year, I decided that I wanted to begin tracking each purchase that I made using Excel as well. I used to use checking account booklets to do this, but I often found that I wouldn’t add things up right, and I didn’t always keep my budget in sync with the booklets I’d keep expenses in. Instead, now I hook up the total of all my purchases into my ‘Expenses’ section. You may have noticed the ‘Ind Purchases (see below)’ category in my previous pictures. That’s the row that represents the total of all purchases.

Using the following function in cell C43, we can grab the total amount of purchases that we make:

=sum(C46:C66)

This will allow for entry of up to 20 purchases each week, outside of other categories that you have in your ‘Expenses’ section. Putting it in cell C43 places it into the range of expenses that are totaled up for the ‘Total Expenses’ cell in C44.

Row 45, which calculates if I am over or under budget is set up this way:

=sum(145+C43)

This works, by taking a set amount that you give yourself each week (145 in my case) and subtracts out all expenses that you entered in your ‘Individual Purchases’ section. The way that I have it set up, makes a negative value a good value, meaning you are UNDER budget. A positive value means you are OVER budget. I have found this method extremely powerful for controlling your expenses. I’ve also taken this one step further, and have been depositing any underage that I have each week, into a special savings account. (You may have noticed, the ‘Budget Savings’ account I have up above )

Comments
Something that I discovered about using Excel a few months ago, is that you can insert descriptions into each cell, in order to allow for more detailed expense tracking. In order to insert a comment, right-click on the cell, and select ‘Insert Comment’. Then just type away! I don’t know if there is a character limit for comments, but I haven’t found one so far. In order to view the comment, just move your mouse over the cell you want to see.

The Final Result

As you can see, creating the final product yields a very powerful financial tool! As long as you enter everything correctly, you now have access to every account balance, every expense, and every form of income from the past, as well as projected values based on entered information.

Using the steps that I’ve outlined above can give you a very strong base to start building on in order to discover the financial possibilities that your world may contain. Using this method, you can see where you are now, where you have come from, and more importantly, where you are going, financially. While this may seems slightly complex when you first look at it, I can assure you that learning to use Excel can be very rewarding for you and your family. Feel free to contact me if you have any questions about the information contained here!

Enjoyed this article? You may enjoy these as well!

• Create a Two Cycle Budget
• 3 Types of Expenses To Consider
• Niche Blog Challenge Weekly Update 7 of 35
• How to Freeze Bell Peppers
• Niche Blog Challenge Weekly Update 5 of 35

About the author

Jonathan wrote 103 articles on this blog.

Jonathan is the founder and primary writer at CentsToShare. He enjoys reading above all else, but also gardening, building, eating healthy food and being self-reliant. Living a happy, stress-free life is possible, but it must be sought after, and not taken for granted. If you like the site, please let me know in the comments or through email - centstoshare@gmail.com. Thanks!

• Blog

Tagged as: budget, excel, saving money