10 Easy Steps To Creating A Budget Spreadsheet

girl-on-phoneWe talk a lot about managing budgets here at TFD — Chelsea and I even made a video about it. Today, I’m going to go into more granular detail as to how one can actually create a monthly budget using my personal favorite method — Excel Sheets. Now, a lot of people prefer using apps to manage their budgets, and there are truly a ton of them out there. However, this is simply what works best for me, and I encourage you to try out some different options for yourself.

Personally, I use Google Excel Sheets to manage my expenses because I’m honestly too lazy to download the software to my computer. It’s also simpler to manage and keep track of everything since it’s stored in the cloud, and I have access to it whenever wherever.

Today, I’m going to take you through setting up a basic Excel Sheet for the purpose of tracking your monthly budget which will include: tracking your income, seeing how to track your savings goals, and tracking/organizing your expenses. Again, this is being done through Google Sheets, so you’ll have to have a Google account to use this feature.

DO NOT be intimated by the number of steps or photos! Seriously, I see these guides online and I’ll scroll through and think I could never understand them, but it’s really very easy once you understand how to set up basic formulas. Here we go!

STEP 1: Open up Google, head to your Sheets page, create a new sheet, and name it.

Screen Shot 2015-11-13 at 11.03.24 AM

STEP 2: Create a column, label it “Income,” and list out all your sources of income. If you’re a freelancer, this section will most likely look different each month, but leave yourself enough room to add and remove items as needed.

Screen Shot 2015-11-13 at 11.22.00 AM

Step 3: In the column over, add the numbers that correspond to each source of income. These numbers should reflect your income after tax.

Screen Shot 2015-11-13 at 11.22.44 AM

Step 4: Add a “Total” cell at the bottom so you can see what you’ve brought in that month in full (I’m using totally random numbers here for the purpose of this post).

Screen Shot 2015-11-13 at 11.23.15 AM

STEP 5: Next, select the cell where you want the “Total” to appear — we’re going to create a very simple formula that will calculate the total and does the math for you. Navigate over to the “Functions” icon located on the right-hand side of your screen. It looks like a Greek symbol.

Screen Shot 2015-11-13 at 11.24.39 AM

STEP 6: After you’ve selected the “Sum option” from the “Functions tab,” you’re good to go. Simply hold down the control key, and select the columns which will make up that “Total” number.

Screen Shot 2015-11-13 at 11.24.49 AM

This is what it looks like with real (although completely arbitrary) numbers.

Screen Shot 2015-11-13 at 11.26.06 AM

It works, YAY!

STEP 7: Now, we can move onto the automatic savings section of your monthly budget. For example, organizing how much you portion out of each paycheck for savings, retirement savings, and your safety net. These percentages will vary for everyone depending on what their goals are and how aggressively they save for retirement. To illustrate this step for our purposes today, I set 15% of the monthly income to be portioned directly into savings, 5% for 401k, and another 5% for the safety net.

Screen Shot 2015-11-13 at 11.57.35 AM

STEP 7: You can calculate these percentage out of your gross monthly income very easily. This makes it easy to see how much you need to put away. Simply click on the cell where you want the total to appear, and hit the “=” sign (all formulas start with a “=”). Next, select your gross income total, hit the “*” key (which stands for multiply, and put in the percentage you’re multiplying it by. In this this case it’s 20%, so you’d multiple by .20 to arrive at that number. Simple math! This formula will display the total amount of money you should put away to meet this savings goal. Check out the five step image below of how I navigated through this portion of the budget.

step by step automatic savingsGo ahead and extrapolate this out for every category of automated savings you want/need. When you’ve finished that, go ahead and create another “Total” cell where you can see what those numbers add up to by using the same addition formula shown in Step 6. See below for reference of what it should look like.

Screen Shot 2015-11-13 at 3.02.44 PM

STEP 8: We’re nearly done now! The next step is to subtract your take-home pay from what you’ve portioned out for savings. To do this, create a new cell called “Remaining Total.” This formula is very easy to create. Just hit the equal sign symbol (=), select your first “total,” hit the minus symbol (-), and then select the “total saved” number to see what you have left in your budget.

second total step-by-stepSTEP 9: Finally, it’s time to calculate all your months expenditures — everything from rent, to groceries, to utilities, dog food, cell phone bill, shopping, etc. It’s great to use your credit card/debit card statements as a reference, and go through old receipts. Remember, I’m using completely random numbers here.

Screen Shot 2015-11-13 at 3.11.54 PM

Again, use the “Sum” function to see the total of all those monthly expenses.

Screen Shot 2015-11-13 at 3.32.45 PM

STEP 10: The final step is to simply calculate the leftover money that remains in your budget. You can do this by creating a final cell labeled “Surplus.” Hit the equal sign and select the second total and the total expenses. This will show you the money you have leftover in your budget for that month.

final total step-by-step

Don’t let all these photos and steps fool you, I am NO Exel wizard that is for sure, and anyone can set up a simply budget such as this one, to see where their money is going. And there you have it! An easy, step-by-step guide to create your own monthly budget using Excel.

Image via Pexels

  • Heather

    I do this as well and it works wonders for me! One more step that I take is that when I have that surplus number, I create one more column and create a formula that is the surplus minus the sum of all the cells below. Those cells below is where I list out every single purchase I make. This helps me see exactly where my money is going down to the penny, which has helped me cut back in places I wouldn’t think of. I also used to have the tendency to overdraw my account (ugh) so this also helps me know exactly where my checking account will be after all the bills clear (since that sometimes takes a few days).

    In addition, I update this every morning, so whenever I’m about to make an impulse buy, I think “ugh I’m going to feel so shitty tomorrow when I have to enter this unnecessary Forever21 purchase into the spreadsheet and watch my surplus number drop”.

    • Lauren

      Great tip Heather! Thanks for passing that along — I can see how that extra column is helpful to have. =)

  • So glad you put this out there! I currently track all of my expenses in an Excel spreadsheet and it’s so easy and absolutely worth it! Hopefully it’ll help a lot of people out because Excel is not hard to use, and once it’s set up you can just “Duplicate” the sheet, delete the spending numbers you entered last month and start over again.

    • Lauren

      Excellent, so glad the method is working for you too!

  • I so appreciate this! I just moved into my own one-bedroom apartment and I have been looking for a simple budget just like this. I so appreciate the steps and the pictures too…super helpful! Thank you!

    • Lauren

      Yay! It’s nice to know you found it useful =)

  • Keisha

    thank you! I’ve been wanting to try this but I’m sort of technologically challenged and needed a tutorial :]

    • Lauren

      SAME, that’s why I made one for myself.

  • Just so you know, you can also select a range of values to sum rather than selecting each cell individually 🙂

    • Kieran

      or you can just type in a range of cells! A1:A5 is the same as A1, A2, A3, A4, A5.

  • Melissa Klotz

    I can’t praise Mint.com enough. I’ve been using it since 2009. You add all of your accounts (checking, savings, IRA, credit cards, loans), then set budgets and goals. All your transactions are imported automatically, you just have to review it and make sure things are being categorized correctly (like making sure “Starbucks” doesn’t get categorized as “Rent”). You can see at a glance if you’re on budget or overbudget by category or overall. No need for spreadsheets or formulas or anything.

  • I also find it helpful to put dates in my budget next to regular outcoming money such as direct debits. Eg if I always pay my phonebill on the 24th, then it helps ensure I don’t see that money in my account on the 23rd and think, woo, pints all round.

    • Lauren

      ooooo, worth keeping in mind. I’m constantly checking due dates as it is!

  • Linda Dorman

    There are lots of free downloadable Excel spreadsheet templates available online to create a personal monthly household budget. I have several versions, depending on the whether I’m living at home or on the road for long periods. Towards the end of each year, I do a “forecast” version for the upcoming year and look for ways to economize. It’s also a great way to create a version of what it might cost to live in another city if you’re thinking of moving elsewhere!

  • gattita

    Thank you for this!!

  • JessB

    So cool! I had a really big, unexpected expense over the weekend that I was able to pay completely, but it left me completely wiped out- I have $20 left till pay day on Friday.
    I’m glad I could pay it all, but I’m freaked out that all my money is gone.
    I’ve created a budget, and I was actually surprised at how much money I have coming in. I’ve got a pretty good list of spending categories, and I’ve even included the contributions my employer makes to my superannuation (the Aussie equivalent of a 401k, I think).
    I don’t have to start paying off my student loans yet (again, I’m an Aussie), but I decided I’m going to pay off $1 a fortnight (which is my pay period). Just to make a start!
    I have credit card debt, and a personal loan I need to pay off ASAP, but I want to try and cover as many bases as possible, and at least be making tiny contributions across the board.

  • Gaëlle Coulm

    Tnhank you so inspiring and EASY ! I was already having a numbers page for the counts, but I was calculate EVERYTHING by myself ! I know so dumb…