How Do You Record Income and Expenses in Excel?

Written by Jeff Campbell in Budgeting

I still remember when I first decided to start a budget. I didn’t want an app and I didn’t like the premade budget sheets I saw. So I wondered how do you record income and expenses in Excel?

  1. Put all paychecks and income at the top of the sheet
  2. Have the far left column be for monthly expenses
  3. Ideally, put those expenses roughly in the order they get paid
  4. Have a column of formulas to the right that automatically subtracts the expenses from the total income
  5. Use a column to the right of the formulas for notes on payment dates, whether it’s auto-draft, etc
  6. Once created, lock the formula column to ensure the formulas don’t get messed up
  7. Have a blank version saved as a template
  8. Then copy the template each month for the next month’s budget

But don’t worry. I’ll walk you through each of those steps one by one with screen shots below!

Budgeting is a dirty word. We panic. Then we freeze. We get stuck in analysis paralysis. It’s scary! We foolishly believe we’re better off not knowing the state of our finances or that it’s somehow more stressful dealing with it than ignoring it.

Unfortunately learning how to make a monthly budget isn’t something typically taught in school.

The budgeting process steps weren’t taught to me by my parents.  At best I learned how to balance a checkbook.  For those of you under 40, a checkbook was an ancient paper artifact used to issue payment when cash became uncool.

In this post, I’m reviewing the key steps a successful budget needs to work. But I’m also giving you a copy of my free budget template which you can download below.

Want an easy and cheap way to spend less on your monthly expenses?

The app Trim automatically looks at your monthly bills & spending habits and then cross-checks that with savings programs almost all vendors have.

When they match up, you save. They’ll even handle the hassle of canceling memberships you no longer want or renegotiate bills for you like insurance and cable bills. Just sign up and spend on your Visa card and earn automatic savings back on your statement!

CLICK HERE to learn more about Trim on their website.

Table of Contents:

income expenses excel lg

How to create a budget template in Excel from scratch (step-by-step)

Step 1- Create the basic template

To get started, just open a blank Excel workbook.

We’re going to have 4 basic columns: labels, expenses, a formula column to calculate the running bank balance, and a column for notes. I like to put the income at the top off to the right, but you can put that wherever you like. Essentially this is just a balance sheet like a business would use.

It should look something like this:

Step 2- Organize your expense categories

Then begin to list all your monthly expenses in column 1, ideally, in the order they get paid on a monthly basis. Make notes in the column if they are autodraft or have to be paid manually.

It should now look like this:

Now you’ll notice that some of the columns aren’t quite wide enough for the text. You might also want to make the text size larger so it’s easier to read.

In that case, if you aren’t that familiar with Excel, we’re going to set the font size where we want it (I like 14), and then double click in between the column we want to widen and the next one over (you can also just click and drag).

Like this:

Step 3- Add your income

Now we need to add your income.

You might get paid once a month, twice, or even weekly. And if you are married, you and your spouse might each get multiple paychecks.

But for my example, we’ll assume it’s 2 paychecks.

To get really precise with the budget we can put the 2nd one halfway down the month, that way we aren’t thinking that money is there before it really is.

But first, we’ll just put the dollar amounts for the paychecks. This should be your net pay.

Don’t worry, we’ll get to the formulas next.

So first, let’s total the paychecks so we know exactly how much money we have for the month. For this to work, the expenses HAVE to be less than the income. Some recommend having it be a zero-based budget where literally nothing is left at the end.

Personally, I like to have a little cushion in case I forgot something or if there was an unexpected expense (like a high electric bill), or if a provider recently raised their rates.

Step 4- Adding formulas in Excel

So in the cell to the left of where it says “Total Income”, we’ll simply put this sum function formula:

=sum(G2+G3)

Of course, G2 and G3 may not be the exact same cells you’re using, so make sure to use the actual cell labels for the cells where the dollar amounts of each paycheck are.

Here’s what that looks like:

It’s also worth pointing out that if you have several paychecks, rather than just adding up each one, you can do a range to add using this formula instead:

=sum(G2:G3)

Just press enter after entering the formula. That will simply add every number from the first to the last.

Step 5- Have a running balance

Now we’re ready to add the formulas which keep a running balance of the money left after each expense. But first, we need to plug in dollar amounts for each expense. If some vary like electricity or groceries, go with the average and err on the high side; better to be pleasantly surprised than in the hole.

I used these numbers as an example:

Now let’s create those formulas to keep us on track.

The very first one will be different since it pulls the income over. So let’s look at that formula first. We’ll use the formula below in the cell to the right of the rent amount:

=G2-B2

This simply takes the first paycheck (cell G2) and subtracts the rent amount (cell B2). It should look like this:

Step 6- What to do if you get paid 2 or more times a month

Now for the cells below that, before we add in the 2nd paycheck, they will all get the same formula. To get a little better specifics, I’m going to break up groceries and gas into 2 areas; one for each paycheck.

So in the cell to the right of the electric bill amount, we’ll use this formula:

=C2-B3

That takes the amount of money left over after paying rent (cell C2) and subtracts the amount of the electric bill (cell B3). It should look like this:

Now we’ll just copy that same formula for the cells below. When you copy and paste, Excel will automatically move the cells down one.

So simply click on cell C3 (where we just placed that formula) and hit copy (control C on a PC or Command C on a Max).

Then click 1 cell down and hit paste (control V on a PC or command V on a Mac). It should look like this:

Then just keep copying and pasting all the way down, stopping where we want to add the 2nd paycheck. In this case, I’ll add that 2nd paycheck after the internet bill.

So to add the 2nd paycheck in, we’ll use this formula:

=SUM(C6+G3)-B7

What this does is add the current balance (cell C6) and the 2nd paycheck (cell G3) and then subtract the internet bill (cell B7).

Now we’ll just continue copying and pasting the formula from any of the cells above the one we just did (I’ll use cell C6) into the remaining cells below.

If you’re using the same dollar amounts as my example, it should look like this:

Rather use my FREE Excel budget sheet than create your own from scratch?

No problem! Just Download your CLICK HERE to download a copy totally for free right now!

Making an Excel budget from premade templates

If you want to use an Excel budget but don’t want to create your own budget from scratch, the easiest way to do so is by using the library of pre-made free templates that come with the software.

These spreadsheet templates provide a great starting point for your budget, and can help you get a better understanding of what you should include.

All you have to do is open up Excel, go to File>New, and search for “budget.”

You’ll find a variety of templates that can help you get started, such as a family budget, personal expense calculator, vacation budget, and more. This is an easy and fast way to build your budget in Excel.

For example, if you choose the family budget spreadsheet, it will come with a Cash Flow chart on the first tab.

You can change your family name and budget title in this tab, but it’s important not to mess with any formulas. On the other two tabs – Monthly Income and Monthly Expense – you can input your data which will then be automatically fed into the Cash Flow tab to keep track of your earnings and expenses.

Can you customize a premade Excel budget template?

If you want to add something to an existing Excel budgeting spreadsheet, just click on the select cell where you want to add a box and right-click.

Scroll down to “Insert” and choose either “Table Columns to the Left” or “Table Rows Above”.

This should automatically sync the new information with the existing tabs. If it doesn’t, click on the small arrow next to the “Projected” column and you will be able to include your new column in the calculations.

If you don’t need a certain row in your Monthly Expenses tab, you can easily remove it. Just right click the tab, select “Delete” and then “Table Rows” to get rid of it.

Frequently Asked Questions

What If You Don’t Have Excel?

If you need to create a spreadsheet but don’t have Microsoft Excel, there are several options available to you.

The first option is to use an online spreadsheet program such as Google Sheets or Zoho Sheet.

Both of these programs are free and offer a wide range of features that are similar to those found in Microsoft Excel. They also allow you to collaborate with others on the same document, making them ideal for group projects.

Another option is to use a desktop spreadsheet program such as OpenOffice Calc or LibreOffice Calc. These programs are also free and offer many of the same features as Microsoft Excel, but they do not have the same level of collaboration capabilities.

Finally, if you need more advanced features than what is available in either of these programs, you can purchase a copy of Microsoft Excel or another commercial spreadsheet program. These programs will provide more powerful features and will allow for greater collaboration between users.

No matter which option you choose, creating a spreadsheet without an Excel file is possible and can be done quickly and easily with the right tools.

Do you have to use cash only for a budget to work?

A budget can work whether the budgeter uses cash, debit cards, or credit cards to pay for expenses. The key is to budget all expenses and not make emotional spending decisions that have not been planned for.

And for credit cards, while I know that’s a dirty word to Dave Ramsey, they work just fine as long as you pay them off in full each month.

After all, if you boil Dave’s core message down, he simply wants you to be intentional with your money and actual spending choices.

And you can do that with credit cards and then have the convenience of getting points and avoiding the hassles many rental car companies give you if you only have a debit card.

It just takes discipline and a plan.

If spending like you’re in Congress sounds familiar, I highly recommend you take a moment and check out my previous post about how to Stop Living Paycheck to Paycheck.

It’s only a 5-minute read and I walk you through, step by step, how to better manage your money! Just click that link to read it on my site.

How does the cash envelope system work?

The cash envelope system simply uses several envelopes for different spending categories like groceries, gas, eating out, or clothing.

Each payday, the budgeted amount of cash is withdrawn and placed in each envelope. Then when the envelope is empty, nothing more gets spent until the next payday.

In our house, we pay recurring bills online through our bank’s online payment system.

And for years, until we got out of debt and started building wealth, almost everything else we pay in cash using Dave Ramsey’s envelope system.  In this system, you take out the cash on payday and divide it up into the various categories on envelopes (groceries, gas, spending, etc).

Just click that link to read my deep dive on the system right here on my site.

The categories and amounts you and your spouse agree to in advance.  Then when the money’s gone, it’s gone until next payday.

Learning how to make a monthly budget teaches you very quickly to watch how you spend your money.

If you don’t have a spouse or significant other in some ways that’s easier.  You don’t have to come to terms with anyone else’s opinion.  The downside is there also isn’t anyone holding you accountable.  So just know you may need to be extra disciplined.

If you know the envelope system is right for you, Dave has a cheap starter envelope system on Amazon Prime for under 20 bucks!

How does budgeting help in managing personal finances?

Budgeting helps manage personal finances and financial goals as it tracks all actual income and every expense before they happen.

It helps ensure there will be enough money for the month and helps to avoid overdraft charges. It allows spending choices to be made knowing the exact impact it will have on the household’s finances.

Your family WILL benefit when you use a budget. As I already covered, you can eliminate debt by living on a budget.

When you have no debts (other than your mortgage) and you aren’t throwing money away to your bank you can begin to:

  1. Build long-term savings
  2. Save for your kid’s college (instead of going into debt again with student loans)
  3. Have an emergency fund (so you aren’t reaching for the credit card every time the car breaks)
  4. Start to save for your retirement (instead of wondering which kid will welcome you to their couch in your “golden years”)

At first, your family may question WHY you are budgeting.

They may wonder why you aren’t going out to eat in restaurants 5 times per week.  They might balk at not getting what they want every time they ask.

These are good lessons.

The world isn’t kind to people who feel entitled to everything.  It just isn’t.  Learning the value of a dollar and the value of working for what we want are lost arts.  Let’s bring them back!

Want an easy and cheap way to spend less on your monthly expenses?

The app Trim automatically looks at your monthly bills & spending and then cross-checks that with savings programs almost all vendors have.

When they match up, you save. They’ll even handle the hassle of canceling memberships you no longer want or renegotiate bills for you like insurance and cable bills. Just sign up and spend on your Visa card and earn automatic savings back on your statement!

CLICK HERE to learn more about Trim on their website.

How do you deal with variable expenses in an Excel budget?

When it comes to dealing with variable expenses in an Excel spreadsheet, a great way to do so is by using a monthly average of the prior 6 months of actual bills. This will give you a good idea of what your average monthly expenses are and can help you plan accordingly.

For example, if you have a utility bill that fluctuates from month to month, you can look at the last 6 months of bills and take the average. That way, when it comes time to budget for that expense, you know exactly how much money to set aside each month.

Another way to deal with non-monthly expenses in an Excel budget is by accruing an amount each month into a savings account that will equal the bill when it comes due.

This works well for expenses that don’t occur on a monthly basis, like a trash bill that gets paid quarterly or property taxes that get paid annually.

By setting aside a certain amount each month into a savings account specifically for these types of bills, you can ensure that when they come due, you have enough money saved up to cover them without having to dip into other areas of your budget.

Not quite sure how to do this correctly?

I have an entire article on exactly how to budget for variable and occasional expenses. Just click that link to read it on my site.

What are the downsides of not budgeting?

The biggest downsides of not budgeting are risking overspending, incurring overdraft charges or interest charges, being unprepared financially for unexpected expenses, and living paycheck to paycheck instead of building wealth.

In short, if you delay using you’re putting your future at risk.

Not budgeting is the financial equivalent of just throwing darts at a dartboard with a blindfold on hoping you hit the target.  Sure you may occasionally get lucky, but you’ll put a lot of holes in the wall in the process.

There are really 4 main reasons people procrastinate on setting up a budget.

Curious if they sound familiar? Check out my recent article where I get into all 4 and show you how to get past them. Just click that link to read them on my site.

Having no budget means continuing to spend money based on your emotions. 

When we shop for groceries when we’re hungry, we buy more.  When we have a fight with our spouse, that new TV or clothes store is awfully tempting.

We make decisions not based on knowledge or facts but on our inner child’s emotionally immature voice saying “I want it now!”

A lack of a budget could lead to bankruptcy!

At our country’s all-time high a little over 10 years ago, according to Debt.org, 1 in every 55 households in the US filed for bankruptcy.  Think about that for a minute!  That is about 2 million families!

It’s a safe bet those families don’t use a budget.

And while I know people who make poor choices, file, continue to make poor choices, and file again, that’s not how you want to live your life.

For starters, every time you file for bankruptcy you’re destroying your credit for 7-10 years.  15 years ago, credit scores weren’t looked at as often as they are today.

Now a horrible credit score can affect:

  1. Your ability to get a mortgage or refinance
  2. What employers will hire you

If you struggle with bad credit, you will definitely want to check out my post called Credit Report Repair.

You don’t need to pay a credit repair company!  I walk you through every step you need to take to legally clean up your credit report.  It’s not always quick, and it pays to be tenacious, but it’s not hard to do yourself.

It’s also not as widely known that bankruptcy doesn’t eliminate all outstanding debts.

The following types of debt will most likely NOT be eliminated if you file for bankruptcy:

  1. Owed income taxes
  2. Child support
  3. Alimony
  4. Student loans (owed to the government)
  5. DWI fines
  6. Consumer debts over $500 incurred within 90 days of filing bankruptcy

Think you can’t budget if you’re broke?

That’s actually when you need a budget the most. And you CAN make it work even on a low income. Check out my recent article where I walk you step-by-step on exactly how to do it. It’s not that hard, but there’s 1 step that’s crucial to success.

Just click that link to read it on my site.

Income and expense Excel sheet – free download

I have a copy of my free Excel personal budget template available at no charge.

(hint: it’s the same one my family used to pay off over $80,000 in debt!) – Now that you know the budget processing steps, you need a good system!!

This is the very same Excel template I have used since I first started listening to Dave Ramsey in 2007.

It’s a simple, highly customizable, free spreadsheet and you can download it quickly and easily FOR FREE by clicking below!

budgeting-process-steps-free-household-budget-template-spreadsheet-middle-class-dad

Once you click above the budget worksheet will be in your inbox in minutes.

If you have questions about how to use it, I also have a short video tutorial on YouTube that walks you through EXACTLY how to use it.

Check that out here:

How to Use the Best Excel Budget Spreadsheet Template Step by Step

Final thoughts

In this post, I reviewed the top household budget strategies that my family has used for over 14 years.

We went from upwards of $80,000 in debt to being debt-free and you can too!

But more importantly, I gave you access to my free Microsoft Excel budget template which you can download today for a better tomorrow!

If you like this post, please follow my Budgeting board on Pinterest for more great tips from myself and top budgeting experts!

–

Photos that require attribution:
Image by Fateh Muhammad Raja from Pixabay

Top Related Posts