Make a Personal Budget on Excel in 4 Easy Steps
Whatsapp Pinterest

Updated by Ryan Dube on 18 September 2017.

Unlock the "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Years ago, my wife and I were saddled with so much debt that we thought it would take the rest of our lives, or at least the next sixty years, to pay it all off. There came a moment when we realized that we either had to make a personal budget that could outsmart the system or it would keep us enslaved for our entire adult lives.

That’s when I sat down with a blank Microsoft Excel spreadsheet and just started playing around, using various techniques to cut down our budget to bare bones. My goal was to create a debt plan 3 Reasons Why the Debt Snowball Beats the Debt Avalanche 3 Reasons Why the Debt Snowball Beats the Debt Avalanche The debt snowball and debt avalanche are both great ways to pay off debt, but these three reasons will show you that the snowball is usually a better choice. Read More that wouldn’t take decades to eliminate our debt, but also wouldn’t keep us eating macaroni and cheese for dinner until we retire. In the end, I was able to eliminate all of our credit card debt in only five years, and we even had good enough credit, in the end, to get approved for a low-rate mortgage to buy our first home.

Today, I’m going to share a few of the nifty spreadsheet techniques that I used to generate a usable (and useful) budget. Also, I’m going to share a technique to pay down your debt How to Visualize Your Debt & Stay Motivated While Paying It Off How to Visualize Your Debt & Stay Motivated While Paying It Off It's tough to stay motivated to keep paying off your debt, but visualizing it can make the process easier. Read More in a fraction of the time using the same exact payments you’re making today.  It’s a trick that I’ve seen a lot of people trying to sell elsewhere on the net — I’m going to share it with MakeUseOf readers right here, for free.

Step 1: Structure a Personal Budget Spreadsheet That Doesn’t Drive You Nuts

Anyone who has tried to make a personal budget knows the basics. You need to make a log of all of your bills and all of your income. Your bottom line is how much you have left over for fun, or how much fun you have to cut out of your lifestyle before you go bankrupt 3 Tips to Beat the Debt Collectors When Facing Bankruptcy (Or Late With Bills) 3 Tips to Beat the Debt Collectors When Facing Bankruptcy (Or Late With Bills) Serious financial problems are among the most stressful situations a person can encounter. Follow these tips to let technology take away some of the burden. Read More . It sounds easy, but when you start entering all of your details into a spreadsheet, things get very messy very quickly. A lot of people give up after the first attempt.

The basic layout is easy enough. List your bills in the first left column, and then in the next few columns list total balance you owe, monthly required payments, and the date that the bill is usually due.  These four columns are really all you need to create a budget.

Make a Personal Budget on Excel in 4 Easy Steps excel budget1

Here I’ve gone an extra step and added a column to the right for each month for easy expense tracking 6 Ways to Track Your Monthly Expenses (And Stick to Your Budget) 6 Ways to Track Your Monthly Expenses (And Stick to Your Budget) Tracking your expenses is the first and most important step to saving money. Here are six ways to track your money. Read More .

However, once you get a large number of columns and rows, the screen starts to scroll and you can’t always see the bills to the left or the header at the top. The quick and easy technique to fix this is using the Freeze Panes feature.

Make a Personal Budget on Excel in 4 Easy Steps excel budget2

First, select the box where the intersection at the upper left represents the row and column that you don’t want to scroll when you use the spreadsheet’s scrollbars. Select View > Freeze Panes.

Make a Personal Budget on Excel in 4 Easy Steps excel budget3

Now, when you scroll up or down (as shown here), the header and left column remain static, so you always know what the value you’ve selected applies to. This is a very useful feature and since I have a very bad short-term memory, it has saved me a great deal of frustration where I normally would have had to keep scrolling back to check which bill I’d selected.

If you’re new to Excel and need some tips on getting started with creating spreadsheets, make sure to check out Excel courses for beginners Learn Excel Starting Today with These 5 Excellent Udemy Courses Learn Excel Starting Today with These 5 Excellent Udemy Courses Microsoft Excel can help you earn more in your job. If you don't know where to start, these five excellent Microsoft Excel courses on will take you through till the end. Read More .

Step 2: Lay Out an Organized Budget Using Shading

I remember looking for a free budget spreadsheet Save Money and Set Budgets With 5 Apps, Sites, and Free Ebooks Save Money and Set Budgets With 5 Apps, Sites, and Free Ebooks The ability to manage you finances and save money is a crucial life skill. Have you mastered it yet? Follow the right guidance and take the help of these apps. Read More back then and finding all of these templates filled with data that just made my head ache. Without clear lines separating the major sections of your budget, you’ll have a hard time zoning in on the area that you’re interested in. The best way to organize a budget spreadsheet is by shading each summary section between your major groups.

Make a Personal Budget on Excel in 4 Easy Steps excel budget4

As you can see here, the first section of the budget pertains to bills, including household utilities and fixed bills, as well as another section devoted to only credit cards. At the bottom of this particular section, the total for fixed bills is highlighted with light green shading so it’s clear and easy to find.

Make a Personal Budget on Excel in 4 Easy Steps excel budget5

As you can see, once you start shading rows, the entire spreadsheet becomes much more organized and easier to follow.

Make a Personal Budget on Excel in 4 Easy Steps excel budget6

The Fill tool is located on the Excel menu bar under the Home menu and appears as a paint can tipping over with paint pouring out. Just highlight the entire row (click the numbered gray cell to the left) and then click the Fill button and select what color you’d like to use.

Step 3: Use Excel Formulas to Project Your Credit Card Balances Into the Future

Now that you can make a personal budget that is well organized and structured in a way that’s very easy to follow, the next step is attacking that nagging credit card debt that’s been plaguing you for years. In these next examples, I’ve used the same formatting techniques to create a list of credit card balances and monthly payments.

Make a Personal Budget on Excel in 4 Easy Steps excel budget7

Set up your debt log in the same way — split and freeze the panes, but this time list each month along the left, and your credit card balances How to Save Thousands With a Credit Card Balance Transfer How to Save Thousands With a Credit Card Balance Transfer A credit card balance transfer can save you thousands of dollars if you go about it in the right way. Check out our advice and recommendations on balance transfer cards! Read More (and monthly payments) to the right. After you’ve entered in your current balance in the top cell (for example, in this case, Capital One is $3,000), in the next cell below it you would enter a formula that multiplies that balance by your card’s interest rate and divides by twelve. That is your estimated monthly interest.

Then you subtract your monthly payment from the balance and add the interest that you just calculated. Once you’ve got that first cell calculated correctly, you can duplicate the formula for every month below it by clicking and holding the small box to the lower right of the cell you just calculated, and dragging it down as far as you like. Each month will have a new calculated balance based on the previous month’s balance.

Make a Personal Budget on Excel in 4 Easy Steps excel budget8

When you do this projection, you’ll eventually find the spot where the balance is completely paid off. As you can see from my own calculations, when I maintain a $250 payment every month until it’s paid off, it’ll take me until July 2012 to pay off the entire Advanta credit card balance.

For more on saving money and reducing your spending 5 Free Sites and Apps to Save Money and Reduce Spending 5 Free Sites and Apps to Save Money and Reduce Spending Are you spending more money than you have? These apps and sites will help you set a budget, reduce spendings, and save money. Read More , take a look at these helpful apps and sites:

Step 4: Recalculate Payments Based on Interest and Eliminate Your Debt

By playing around with this kind of spreadsheet, I uncovered the very simple, common-sense solution that a lot of scammers out there are charging people for. Instead of maintaining constant payments on each of your credit cards until it’s paid off, you pay the minimum balance on all of them and divert all of your current “debt-payment” money toward the credit card with the highest interest. Here is how it works.

Make a Personal Budget on Excel in 4 Easy Steps excel budget9

This is also why I love Excel. By using the ability to autofill the monthly balance calculations, I tested different scenarios to pay off debts faster. Instead of paying $100 on Capital One and taking until 2025 to pay that balance off, I paid $200 extra and will have it paid off by February 2022. Then I take that $300 and add it to the existing payments on my other credit cards.

If you have additional credit card balances, you simply “snowball” the payment and eliminate your debt in months rather than years. Excel allows you to see that concept in a very cool graphical form by using the formula autofill feature.

Using Excel for Budgeting

The power of Excel for budgeting and debt planning like this can’t be understated. And even if you don’t use Excel, you should at least explore the various budget and debt planning tools available out there. Make the decision to try a few out and at least make a commitment to regularly sit down and work on it a little at a time. At the beginning it may take work, but in the long run you’ll be very glad you did.

And, be sure to check out these ways to avoid overspending 5 Simple Ways to Set a Budget and Avoid Overspending 5 Simple Ways to Set a Budget and Avoid Overspending Do you set a budget and adhere to it? These five simple methods will help anyone set a budget that they can live comfortably with. Read More and then bookmark some of these helpful sites that keep you on top of the financial market The 10 Best Finance Sites to Help You Stay on Top of the Market The 10 Best Finance Sites to Help You Stay on Top of the Market Looking for the best finance websites to keep you on top of the market? Here are the best sites for news, investing, and more. Read More :

Image Credit: AndreyPopov/Depositphotos

Explore more about: Budget, Credit Card, Debt, Microsoft Excel, Microsoft Office Tips, Money Management, Save Money, Spreadsheet, Tax Software.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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

  1. Linda
    January 7, 2018 at 11:33 pm

    I know about just enough excel to do this. . Have already used it for some things, but this information will help. Hopefully we will be able to do better to calculate & record our budget transactions this year! Thank you

  2. Kate
    February 5, 2017 at 5:10 pm

    This was such a great tutorial and really helped me get organized. How can I carry over my balance from the credit card sheet to the balance on the budget sheet? I want it to change and reflect the current balance as I make payments every month.

  3. Claire
    December 6, 2016 at 12:09 am

    Any chance you have a full template of this?? It's hard to know where to place different parts.


  4. Tim
    November 18, 2016 at 9:37 pm

  5. teltn84
    May 8, 2016 at 11:44 am

    Hi husband and I just bought our first house and at this point I really really want to start a good budget! So I just came across all of this on pinterest. I am not too keen on Excel and without going into a long drawn out explanation, is there a template out there? I've clicked on pretty much all the links but they're all gone, except for one that has a fee. I'm not saying I won't pay a fee but if one of you has a file that I could use for free that would be great. Suggestions?

  6. Nina
    May 4, 2016 at 2:20 pm

    This looks so amazing, I have been wanting to take control over my life for a while and this is what I have been looking for. thank you so much.
    Question if you don't mind, I have some outstanding bills as well, will this calculate if I am making payments to break down certain bills as well until they are payed off?
    If you have a template for your spreed sheet that I could change to suit my personal needs I would be forever grateful.

  7. Jessika
    April 2, 2016 at 5:05 pm

    So glad I found this! I've made April the month to get my life together and this is exactly what I needed. I always get frustrated with finances, but I'll try my best to better understand the credit card debt. Have you ever thought about creating a video for this?

  8. Brian B.
    February 11, 2016 at 11:37 pm

    You have composed an excellent tutorial ! Kudos my friend. Your methodology is reaching hundreds (or more) people that truly need tbis advice....myself especially


    • Ryan Dube
      February 12, 2016 at 12:28 am

      Thanks Brian - really appreciate your kind words!

  9. Katherine
    December 31, 2015 at 5:11 am

    I am getting ready to set up our budget and plan to financial freedom to be ready on the 1st. Thank you for this amazing tutorial!

  10. Anonymous
    July 1, 2015 at 1:21 am

    Hello. would it be possible if you can email me this excel file and I would change all the data to my information. I tried creating it and I\'ve been running into issues. My email is Thank for all you help and on the post. this makes life a lot easier. Peter

  11. Tami G
    March 30, 2015 at 5:11 am

    This was so helpful...I loved following your instructions for the credit card debt and seeing the difference of what even an extra few dollars a month can make toward eliminated those balances. I bought a book from a well known "manage your finances" guy quite awhile back who uses the snowball payment theory and just couldn't get into the blah blah blah of it. You nut shelled it here and I thank you for that. You are right....Excel really makes the goal clear and it was fun to play with the payment amounts and watch the timeline shorten for payoff!

  12. Anne Kleiber
    February 3, 2015 at 4:55 pm

    omg i totally love these steps!! gives me so much life and love in my body. So fulfilling. Praise Ryan and Jesus!


  13. Rhys
    January 4, 2010 at 2:48 am

    Hi Ryan

    I have created a website (EasyBudgeting) that makes using an Excel budget planner simple and straightforward.

    Excel is a great tool for budgeting but some people find it difficult or a bit overwhelming. I invested my time and knowledge to provide people with a solution to their budgeting needs without having to master Excel.

    It's helped 100's of people worldwide and may be useful for your visitors.

    Good luck, Rhys

  14. Factopo
    November 17, 2009 at 6:36 am

    These are some good tips, especially in the economy right now.

  15. Peter
    November 16, 2009 at 3:52 am

    I have always been the type that buys stuff until my account shows a low balance, then I back off until next payday. It's gotten me into some trouble a few times but has never been a huge issue because I wasn't dealing with massive amounts of money and was living with my parents. Well I got married this summer and both my wife and I haven't changed our ways much. We buy what we need, with extra's here and there (magazines, lunches, etc). And as of now it's not a problem, but after reading your article and thinking about it, I realize that it could become a problem later on when we have more debt. I also think we could be saving a lot more money for future purchases (house mainly, future kid's college), if we only paid more attention to our finances.
    Sorry for the wall-o-text, but I genuinely thank you for posting this. I am in the process of entering all my info now into a spreadsheet, and for the first time in a while I feel good about my financial future.

    • Ryan Dube
      November 16, 2009 at 7:42 pm

      Thanks Peter - it feels good reading this and I'm very happy that the article served as inspiration to crack open a spreadsheet and give it a shot. Best of luck to you and your wife - and here's to a great financial future!

      • Anonymous
        July 1, 2015 at 1:26 am

        ryan do you have this file created. I'm running into a few issues

  16. Teach Children to Save Money
    November 16, 2009 at 3:05 am

    Excellent Excel wizardy! As a single parent that was *less* than good with money throughout my youth, teaching children about money is CRUCIAL, in my mind. I’m not going to blame parents, schools, etc, but quite simply, I clearly “didn’t get it”, and I am still paying for those mistakes a decade later! And quite frankly, I hate the position I got myself in, everytime I pay off my past debts… I could have used my time/money sooooo much better.

  17. brenda
    September 20, 2009 at 10:56 pm

    Thank you sir for your great budget tutorial! I created one for my boyfriend, when I punched in his CC info he wasn't going to pay off his balance until 2017! We worked out the numbers with your formula and he will be paying it off much sooner. Thank you!

  18. Igor
    September 18, 2009 at 3:23 pm

    Hi guys. I've been doing budgeting in Excel for years and eventually decided to automate the process heavily, which lead to the birth of Arixcel Accounts - it's currently a free tool and it can import transaction to Excel from saved bank statements as well as do other useful things.
    If somebody gives it a try and leaves me a comment, I would appreciate. Here's the link:

  19. Igor
    September 18, 2009 at 3:25 pm

    Sorry, the link to Arixcel Accounts:

  20. Natuernut
    August 15, 2009 at 3:51 pm

    I've used excel for budgeting before and liked how simple it was. I'll have to try your tips out because they look like they'll help me out a lot.

    An easy (and free) way to back up your excel (word, and PowerPoint too) is on Officelive. All you need to do is sign up (it's free), follow the directions and you're ready to go. I use it myself for both my excel and word files. Here's the address:

  21. thecolor
    August 6, 2009 at 9:04 am

    Personally, I really enjoy the incite gives me. I don't have to use my CC just to get info. It'll accept many (so far all) of my financial services and map all that info for me. Spends no matter how small, anytime. It's quite convenient. I've been making the attempt to revert back to using my debit card (cash in plastic), the hardest part is watching my bank account. Previously I'd just use my CC and pay it off at the end of the month, but I never really watched the spending. So, it's going to be an effort I have not done in years.

  22. Darren
    August 5, 2009 at 8:52 pm

    I set up a Google Docs spreadsheet for my wife and I to track what we spend our cash on. When we did our budget recently, we had good visibility on our credit card expenditure, but zero insight into our cash spend - the only record of cash spend was our ATM withdrawals. We used the Google Docs spreadsheet for about 6 weeks and it gave us a great idea about how much cash we spend, and on what. Here's the link to a post I wrote about this:
    [Broken Link Removed]

  23. Robert
    August 3, 2009 at 9:36 pm

    I love using Excel to take care of budgetary means and using it to make predictive graphs and such. also has cool credit card payment calculator that is a very easy premade way of doing the simulating in the last part of this article. It's here:

  24. Donald Scott
    August 3, 2009 at 10:04 am

    I have been on a budget for years.

  25. Guy McDowell
    August 2, 2009 at 10:00 am

    Ryan, seriously, this is possibly your best article ever. I hope millions read it because taking responsibility for your personal finances is what will end recessions and depressions.

    I do this already, but you've helped me hone my spreadsheet even more. Thanks bro!

    • Ryan Dube
      August 2, 2009 at 10:16 am

      Hey - thanks Guy! I know what you mean, if only more folks would work harder at grabbing their personal finances by the horns, the economy might be in a better place. We can at least hope for the best!

  26. Rarst
    August 2, 2009 at 6:12 am

    Thanks for hints. :) Made one in OpenOffice just now (was going to for a while). I've poked some software for home budget in the past but it seemed overcomplicated for my needs.

  27. Phaoloo
    August 2, 2009 at 5:58 am

    Using Excel is much simple and flexible than Quiken, GNUCash or other budget software else because you manage your budget your own way.

  28. thecolor
    August 1, 2009 at 4:32 pm

    Here is one I created years ago with a similar purpose in mind. It's not functional via the link directly, but you can download it or use it within your google account and adjust it accordingly.

    It takes bills you've setup, there amounts, your monthly or hourly wages, etc., etc. and adds them up compares them (you know) to see what you need to make in order to "stay alive" for a lack of better terms. :) Enjoy and let me know if you mash anything else up... I'd love to see it.

  29. Ryan Dube
    August 1, 2009 at 3:09 pm

    Hey Mark - I agree. My wife calls it "the spreadsheet" (with some sarcasm of course) You're right - there really is no better way to track spending and manage a budget. Thanks for the great tips btw.

    • nicole
      December 6, 2016 at 6:25 pm

      do you have something to download to use this template?

    • nicole r
      December 6, 2016 at 6:26 pm

      do you have a template that can be dowloaded?

  30. Mark
    August 1, 2009 at 12:26 pm

    I'm a huge fan of excel for my financial tracking. It tracks everything in my life. Mortgage, Savings & Checking Account balances, daily expenses, car repairs & mileage, projected budgets and investments. Plus a bunch of other crap. My wife refers to it as The Grid.

    My suggestions:
    1. Learn to use the SumProduct formula. It's basically a sumif, but with the ability to check multiple conditions. Vlookups are also very important.
    2. Track every penny you spend. You won't know how much you spend until you track it. And you will surprise yourself.
    3. Back it up often. My recommendation is throw it on Dropbox. That way you get versioning capability and it's backed up off site and available wherever there's an internet connection.
    4. Get to know Conditional Formatting.
    5. Continually refine your workbook. I started mine 10 years ago and it gets better with time.