How To Make A Budget Spreadsheet That Makes Budgeting Fun!

Adding Color To The Monthly Bills Section Of Your Spreadsheet

Hey everyone, thanks for joining me for my third post here on the new CNA Finance. On the old CNA, I wrote a whale post about how to make a budgeting spreadsheet, and readers seemed to love it. It was the most shared post in my website’s history through social channels. It was awesome!

The only problem was that I used hypothetical scenarios to make the post. When I actually made my own spreadsheet, I realized that I needed to include much more data, simplify the process of accessing that data, and make it fun! So, I’ve decided to start from scratch!

The truth is, I haven’t made the new budget spreadsheet yet. This time, I figured I’d use my personal data and, document the process to let you, my readers, in on the entire process step by step. So, without further ado, let’s get started…

Time To Make The Spreadsheet

If you would like to see any of the pictures up close, click on it and it will open a blown up version in a new tab for you!

Step #1 – Decide Which Program To Use – When it comes to spreadsheets, there are 3 sources that seem to work out best for me. Start A New Google Drive SpreadsheetThey’re Google Drive (which I’ll be using for this tutorial), Microsoft Excel, and OpenOffice.org Calc. All you need to do is choose the one that you feel most comfortable using. Because I like to be able to access my files everywhere, I often use Google Drive and, it’s going to be my choice for this sheet.

Step #2 – Start Your Spreadsheet – For this tutorial, I’m going to assume that you’re using Google Drive like me. That being said, to start a new spreadsheet, simply log in and click “Create”. Now from the drop down menu, choose the “Spreadsheet” option. Once you click this option, a new tab will open in your web browser with an empty spreadsheet. Now we’ve got our foundation, let’s fill it with data!

Naming Your Budget SpreadsheetStep #3 – It’s Time To Name Your Spreadsheet – You’re going to want to be able to easily access your spreadsheet when you want to find something out, add data, or just see how you’re doing in the different areas of your budget. Therefore, you want to add a name to your budget to make it easy to find when you log into your drive. To add a name to your spreadsheet, all you need to do is click in the upper left hand corner where the words “Untitled Spreadsheet” appear. When you do, a little pop up will come with a field to enter the new name of your spreadsheet. Name your budget whatever you’d like and click “OK”. The popup box will disappear and you will now see your new title in the upper left hand corner. If your title has updated, you got it right!

Budget Spreadsheet With Titles For BillsStep #4 – Title Your CellsAs I said above, I haven’t made the spreadsheet so I’m not 100% sure what I’m going to include in it. That being said, lets start by making sure we include all monthly bills. We are going to want to add titles to an area of the spreadsheet for this. So, starting in Row 1, Cell A, let’s create title cells to track our bills. In Cell A:1, type in “Payable To”, in cell B:1, type in “Amount Due”, in cell C:1, type in “Pay To Address”, and in cell D:1, type in “Due Date”. If you’ve got it correctly, your spreadsheet should look like the image to the left. Don’t worry, I know it looks a little bland now. We’ll be adding tons of data and color to the spreadsheet in the upcoming steps!

Step #5 – Updating Your Monthly Bill Information – Now, add in Budget Spreadsheet With Bill Dataany monthly bill making sure to include all information about that bill that is requested. With regard to the pay to address, you can amend that by placing in Pay by Pone, Pay by Email, ect… You can also add in the website you pay that bill at. No matter how you decide to pay your bill, just make sure that there is a reminder there that shows you what you need to do when it comes time to pay. Also, don’t trust yourself when filling out this page. You want to make sure to go grab your statements. This way, you’re sure of how much money you will need to send to the monthly bill and more importantly that you’ve added all of your monthly bills to the sheet. There are few things more disheartening in budgeting than when you realize that you’ve forgotten a bill and have to move things around to account for it.

Adding Color To The Monthly Bills Section Of Your SpreadsheetStep #6 – Make Your Monthly Bills Easier To Read – I don’t know about you but, nothing is less appealing to me than a spreadsheet with nothing but a munch of white space and black lettering. When you have a spreadsheet that lacks color, it’s so easy to get lost in the data and difficult to find exactly what you’re looking for when you’re looking for it. So, lets add some color to make it easier to find things on a linear model like a spreadsheet. Let’s start by outlining the title cells. To do so highlight the title cells(Cells A:1 – D:) by clicking on A:1 and dragging your mouse to D:1. Now, click the paint bucket icon in the icon tray at the top of your screen. When you do, a color pallet drop down will appear. Choose a color that is appealing to you and click on it. When you click the color you’d like, the color of the cells you’re editing will change. I like to chose a dark color for title cells which can make it hard to read the text within them. To change the text color, simply highlight the cells again and click the capital “A” to the left of the paint bucket. When you click on it, a color pallet will come down. Choose white to add contrast to the cell.
Now, don’t stop there. Title cell colors are good but, you want to be able to quickly see the information you need. Color can help with this. Highlight the cells below the titles and choose a couple different shades of your main color to make clear lines of data. When you’re done, your sheet should look like the one in the picture with the colors that you’ve chosen.

Adding The Total Of Your Monthly Bills To Your SpreadsheetStep #7 – Adding A Total To Your Monthly BillsIt’s definitely cool to know what monthly bills you have to pay, how much money the bills are individually, who they need to be paid to and when by, but we’re missing one thing. A very useful piece of information that’s missing here is the total amount owed monthly! Underneath your final monthly bill, in the “Payable To” cell, type “Total”. In the cell directly to the right of it, type “=sum(“, click on the first amount due, drag your mouse to the last amount due, and type this symbol, “)”. If you’ve done this right, your cell should look like the one in the image to the left. If so, hit “Enter” on your keyboard and walla, you’ve got a monthly bills total! When you’ve got your total, use what you learned in step $6 to add color to this data and make it easier for your eyes to target

Add Your Income To Your SpreadsheetStep #8 – Add An Area For Tracking Income Into Your Spreadsheet – Tracking your income is a very important part of budgeting. Many people have more than one source of income and, without proper tracking may not know how much money they have available to allocate to their monthly necessities. That being said, leaving one blank cell to the right of your monthly bills section of your spreadsheet, starting in cell F:1, type Income source. Now in G:1 type Net Income Amount. Now, start to list your income sources. For instance, my income sources are my salary and my blog. Well, my blog isn’t making any money right now but, I anticipate that it will some time in the near future. So, for the total income for my blog, I put 0 and I will update that when I start to get some money in. Now. Below your income sources, type “Total” and using the “=Sum(” trick from step #7, add up your total monthly net income. When you’re done add a bit of color and let’s move on to the next step.

Add Random Monthly Expenses To Your Budget SpreadsheetStep #9 – Adding Miscellaneous Monthly Expenses – The fact that you don’t get a bill in the mail for monthly food costs doesn’t mean that you don’t pay for food throughout the month. There are a few expenses that the average household has that they don’t necessarily get a bill for. Let’s take a minute to add those in. To do so, skip one cell below the “Total Income” cell and type in “Random Expense”. In the cell directly to the right, type in “Total Cost”. Now, fill out the cells below. For my random expenses, I have food which is a total of $400 per month and gas which is a total of $255 per month. Below the last expense, type “Total”. In the cell to the right of total, use the “Sum” trick I showed you earlier to total it up. Now of course we’ve gotta add a bit of color and, we’re off to the next step. Of course if you want a good size example of what it should look like when you’re done, click the image to the left!

Add Your Debt Profile To Your SpreadsheetStep #10 – Time To Create The Debt Profile – Any good budget spreadsheet is going to include a debt profile. This is a simple area that lists all of your debts and information you need to know about them. It helps you to prioritize your debts and get them paid off faster. To add a debt profile to your budget spreadsheet, skip a row below your “Total” monthly bills cell and type in “Debt”. In the cell to the right, type in “Account Number”, type “Amount Owed” to the right of “Account Number”. Finally, to the right of “Amount Owed”, type “Interest Rate”. Fill in the cells below to complete your debt profile. You can look to the image on the left for an idea of how this should look. Of course, I blocked out my account number using a nifty little Microsoft program called Paint for security! When you’re done adding in the information on your debts, make sure to add a bit of color to make it more fun to look at!

Add A Fun Budget To Your SpreadsheetStep #11 – Create A Fun Budget – As human beings, we are social creatures. We like to have fun in social atmospheres, and without proper guidance, this can become very costly. Therefore, it’s best to create a fun budget to keep your fun time spending in check. Your fun budget should be a very small portion of the amount of money that you have left over after paying for all of your monthly bills and your miscellaneous expenses. For Ana and I, I’m going to set our fun budget to $100 per week which should be plenty! At $100 per week, given that most months have 4 weeks in them, we’ll just average it out at $400 per month. To add that in, below your Random expenses area, skip a row. In the cell below the skipped row, type “Fun Budget”. To the right of that, type “Total Spent So Far”. Now, add your fun budget total into the cell below the title “Fun Budget”. As you spend, add the total up and put it in the cell for your total spent so far. Below your budget total cell, type in “Available Spending”. To the right, type “=”, click your fun budget total, type “-”, and click your total spent so far cell. Now, hit enter on your keyboard and you will have the total you’ve got left available for fun spending.

Add An After Expenses TotalStep #12 – Add An After Expenses Total – When coming up with savings and investing goals (which we’ll get into a bit later.), it’s going to be helpful to know how much money you’ve got after all of your monthly expenses are totaled. Doing so is pretty simple. Skip a row below your “Available Spending” cell. In the cell you land on, type “After All Expenses” and color the cell like you would any title cell. Now, in the cell to the right, type “=”, click your total income and type “-”. Now click your total monthly expenses, type “-”, click your total random expenses, type “-”, click your fun budget and hit “Enter” on your keyboard. When you do, the total the spreadsheet gives you should be the total amount of money that you have left over after all of your expenses are covered.

Add Your Savings Goals To Your BudgetStep #13 – Add Your Savings Goals To Your Spreadsheet – Finally, it’s time to track your savings. Lets face it, if you don’t track the progress of your savings and investment accounts, chances are, you won’t make it a point to save every month. So, after skipping 1 row below your final debt, type in “Monthly Savings Goal”. In the cell to the right, type “Saved So Far This Month”, to the right type “Need To Save This Month”, and to the right of that type “Total Savings/Invested”. Now, in the cell below “Monthly Savings Goal” type in the total you have after all expenses. As you put money into savings, update the “Saved So Far This Month” cell. In the cell below the “Need To Save This Month” title, type “=”, click on your monthly savings goal, type “-”, click on the total you’ve saved so far this month, and hit “Enter” on your keyboard. Now your spreadsheet will automatically calculate how much money you still need to save for the month. Finally, add the total amount of money you have saved ever in the empty cell below “Total Savings/Invested”. Now, you’ve got a complete budget spreadsheet!

Common Budget Spreadsheet Questions

Question #1- Is it best to use a budget spreadsheet or an online service like Mint.com?

Answer – That really depends on what you are comfortable with. Although, I think Mint is a great service as you can see in my review of them over at Modest Money, some people aren’t comfortable with keeping financial information online. As far as capabilities go, services like Mint.com are usually much better than spreadsheets. However, actually using online services depends on how comfortable you are with that information being online.

Question #2 – What information should a budget spreadsheet contain?

Answer – A good budget spreadsheet should contain any information you would need with regard to your financial life. For instance, it should be able to track monthly spending, monthly bills, income, savings, investments and more. If it’s got to do with your overall financial picture, it should be in your spreadsheet.

Question #3 - What Is The Best Service Available For Making Spreadsheets?

Answer – There really is no clear cut and dry answer to this question. The truth is, the program that you use is all left up to use preference. I personally love Google Drive but, you may have the same feelings about OpenOffice.org Calc or Microsoft Excel.

Great Budgeting Resources

Mint - Found at Mint.com, Mint is an online software that does an incredible job of tracking just about every detail of your personal finance portfolio. They help make budgeting recommendations, track investments and more!

Penny Pinchin’ Mom’s Downloadable Budget Spreadsheet - Tracie Fobes at Penny Pinchin’ Mom created a great budget spreadsheet. You can download it here!

A Great Post By The Fiscal Flamingo – Taynia wrote a great post about budgeting when you’ve got changing income. Check it out here!

Reader Question

Do you use a budget spreadsheet to track your finances?

This post was written by . If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

66 responses

    • Hey Taynia, thanks for stopping by and leaving such a kind comment. A fun budget is very important when it comes to a stable, followable budget! Thanks again!

  1. My wife is a spreadsheet god. If I need to get something done in a spreadsheet then I just go to her and she knows exactly how to do it.

    As for using a spreadsheet as a budgeting tool – I really like the idea and in fact it is what my wife and I used for years when we were being very cost conscious. It’s free and easy to use :)
    Glen @ Monster Piggy Bank recently posted…Appreciating the Value of MoneyMy Profile

    • Hey Glen, thanks for swinging by. That’s awesome that you’ve got a spreadsheet god in the family, I hear they’re hard to come by! Thanks again!

    • Hey Kim, thanks for stopping by! I’d love a spreadsheet God of my own. If you find out where to get them, fill me in! Color definitely makes budgeting more fun…I always try to add color to my spreadsheets. Thanks again!

    • Hey Clarisse, thanks for your comment! I’m glad you found this resource useful. I know how annoying trying to track your finances on a cell phone can be for sure. Thanks again for swinging by!

    • Hey Thomas, thanks for stepping by. I’ve gotta have color in my spreadsheets or I’ll never even look at them…good thing your wife’s got you covered there. I’m not a huge fan of excel though. My personal favorite is Google Drive…simple without the clutter!

    • Hey Monica, thanks for swinging by and sharing your thoughts. I’ve never been a big fan of keeping budgets on written docs. It’s so easy to find what you need using spreadsheets that I’m not sure why more people don’t use them. Hopefully this post will help people to notice that’s why they’re there! Thanks again for stopping by!

    • Hey CashRebel, thanks for swinging by. I like Mint too but, they do have a few inaccuracies that can make budgeting hard sometimes. However, I use them both. Thanks again!

  2. Great tutorial, I never thought to use color on my spreadsheets!
    Three thoughts:

    a) you have a lot of fun money.I wish I had even $40 a month for fun money!

    b) I wonder why you don’t use online bill pay for your bills. Does your bank not offer it for free? It is so much simpler than remembering to pay this by mail, this online, this by phone….
    When you get a bill just log in, enter the amount to pay and select the date and you’re done. In fact bills that are the same amount every month I set out ahead of time automatically so I don’t have to think about it every month. So easy.

    c) why is your car loan at over 10% interest? There are a lot of great low loan offers out there now, maybe you should consider refinancing.

    • Hey Hannah, thanks for stopping by. I’m glad you enjoyed the tutorial and yes, color does add a bit of fun to the spreadsheet. Here are my thoughts in response to yours…

      a) I wouldn’t consider myself to have a lot of money, maybe one day…however, I do realize that I’m much better off than many and for that, I’m very thankful!

      b) I’ve never tried online bill pay…gonna give that a look! Thanks!

      c) Yes the interest sucks. It’s because I have a repo on my credit from when things weren’t going so well. I’ve had it for a while now but I’ve got another 3 to 6 months of credit work before a refinance will be possible.

      Thanks again for stopping by!

    • Hey Alexa, thanks for swinging by. I like Mint but, I could imagine manual entries would bite! Why do you use cash as apposed to other options? Also, recording your actual spending will help with your goals in the future. If I’m reading your comment right, my post is getting you to think more about your budget and, I’m glad you shared! That’s the reason I wrote this one so, I guess it worked out! Thanks again for stopping by!

    • Hey MoneyStepper, thanks for swinging by! I can’t say I’m a big excel fan, I’m more of a Drive guy but, to each his own. I definitely agree with you about creating your own spreadsheet instead of using templates. No limits when you make your own!

  3. WOW Joshua, awesome tutorial!! The only thing I’m missing in all of this is MONEY!! Other than that I’m good :)

    Seriously though, when I went into teaching privately, I tried a bunch of budgeting software to keep track of my ins and outs but eventually gave up and used a simple notepad and pen instead, which after three years, I still use. But if I was a tad more technically savvy, I would adopt your system!

    Take care and thanks for the obvious hard work and thought that went into this evergreen post! All the best.

    Lyle
    Lyle @ The Joy of Simple recently posted…10 Ways to Make Your 9 to 5 Work Day A Little Better Until You Find Your WayMy Profile

    • Hey Lyle, thanks for swinging by! Hey, I don’t have a bunch of cash either as you can see in this tutorial. Anyway, I’m not a fan of most budgeting software. Too much clutter of duplicate data if you ask me. That’s why I prefer making my own spreadsheets. I know your not tech savvy but, would you do me a huge favor? Following this spreadsheet step by step, will you make your budget spreadsheet? When you’re done, would you be willing to write your opinion of the ease or difficulty endured during the process and allow me to publish the result…good or bad? If so, I’d greatly appreciate it!

    • Hey SuburbanFinance, thanks for stopping by! That’s awesome that you enjoy budgeting, you’re one step ahead of the game!

    • Hey Daisy, thanks for your comment! It’s amazing how much money some budgeting software costs. Spreadsheets definitely work just fine! Thanks again!

    • Thanks Mr. Utopia! I really appreciate your kind comments and agree that technical aspects are often lacking in posts like this. I’m glad I adequately covered that part of the process. Thanks again for your feedback!

  4. Nice spreadsheet budget tutorial. I prefer to keep track of my gross income, however, and then show things like taxes as monthly “Necessary Expenses.” They’re on my pay stub, and it’s interesting to see, even if they don’t change. That way I also see my 401(k) deposits and can add my Roth IRA deposits, as well as taxable savings and investment deposits. I break my budget into:
    Total Gross Income
    Necessary Expenses (Includes taxes, rent or mortgage, utilities, food (not dining out), phone+internet, all insurance, necessary dependent expenses)
    Discretionary Income = Total Gross Income – Necessary Expenses
    Investment Spending (401(k), Roth IRA, Employee stock plan, Brokerage deposits, other savings)
    Discretionary Income less Investments = Discretionary Income – Investment Spending
    Discretionary Spending (Everything else, like: charity, credit card bills, auto loans, cable TV, mobile phones, garden expenses, dining out, entertainment, travel+vacation, pets, clothing, computer costs, gym membership, beer+Alcohol, discretionary dependent expenses)
    Amount remaining to save or invest = Discretionary Income less Investments – Discretionary Spending

    I can then take all this data, and easily come up with what we would need if both my wife and I lost our jobs. The income taxes would go away, but we would still have to pay property taxes. The utilities wouldn’t change. We could maybe cut back on food. Our healthcare costs would go up a ton from $224/month we are currently paying our employer to $1600/month for COBRA. We would cut all discretionary costs. Even with the increase in healthcare, our overall monthly costs would drop by roughly a factor of 2. That is the dollar amount, multiplied by 6 months, that we need to have in our emergency fund.

    Sorry for getting so long winded, but I wanted to show our more granular budget, and how it can be used to come up with what you need in an emergency fund.
    Bryce @ Save and Conquer recently posted…Ooma VOIP ReviewMy Profile

    • Hey Brice, thanks for swinging by! No need to say sorry for the long winded comment, I really appreciate it. I love how you do things. Maybe sometime in the near future, we could collaborate on a post that would be a follow up to this one! My email is cnafinancehelp@gmail.com, if you’re interested, give me a shout! I’d love to work with you!

    • I’m glad this tutorial made it not so scary or boring, Girl Meets Debt! Thanks for swinging by to share your thoughts!

    • Hey Michael, thanks! I really appreciate your kind comment and great idea. I’m going to do a follow up on this to track for years sometime soon!

    • Hey Untemplater, thanks for your comment! I haven’t done this in the past, but I’m sure it will be fun to go back a few years down the road and look at where I’m at today. Thanks again!

  5. I love using spreadsheets to budget. It’s nice that after doing it for so long we have a lot of data built up to compare our year to year numbers. It also helps remind me of certain things we spent money on in prior year’s budgets…so I don’t forget when those same type of expenditures come up in the present.
    Brian @ Luke1428 recently posted…Are You Ready to Live to 100?My Profile

  6. this is a really well-made tutorial about spreadsheets and budgeting. It’s a good idea to make this yourself instead of relying on a third party software that takes your personal financial information for tighter records of your spending. I admit that thinking for categories for spending items can be tricky and it can get boring recording every minor detail, so perhaps another point to add here is to be a little creative and come up with amusing spending categories. This will make the budgeting process fun in its own rite.
    Tammy @ Loans and Lifestyle recently posted…Basics of Applying for Student Loans – AND – Why You Should be CarefulMy Profile

    • I couldn’t imagine developing entire financial docs for a company in drive. This is more for a personal tracker. Excel is also awesome, but it costs money. So, Drive is a great, free alternative! Thanks for swinging by!

Leave a Reply

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

CommentLuv badge