How to Make a Budget in Excel – Step by Step Tutorial

Written By Jay

Published on: August 07, 2022
How_To_Make_A_Budget_In_Excel

Do you want to learn how to make a budget in Excel? This step by step tutorial will walk you through every step with detailed instructions and pictures. At the end you will have made a simple but very effective and customizable budget in Excel that you can use for months or years to come. 

Before we get started, I’ve included answers to a few frequently asked questions below. If you have any more questions, please feel free to leave a comment and I’ll do my best to answer you at the bottom of this post.

What period should my budget cover?

As with most things, this is dependent on your individual situation. If you get paid monthly or twice a month, a monthly budget would work well for you. If you get paid every two weeks, perhaps a two week budget period would be more appropriate. This is really up to you and what you feel works best. Nothing really changes, just the title and applicable expenses and income.

Why are some of the savings funds included in expenses?

Even though you are setting aside money and not spending it, you are expending money and reducing your available income for the period. This is why it is being treated as an expense. 

Why budget in Excel?

Learning how to make a budget in Excel comes with some huge benefits over other formats for budgeting. Excel is infinitely customizable and allows you to manipulate data in order to analyze trends, review budgeted versus actual spending, and gain insights into your finances. 

You can use Excel to track expenses, income, savings, debt payments, retirement, and literally anything else you can think of. Excel is used internationally and workbooks can be used, with some slight differences in functionality, in Google Sheets.

Basically, using Excel puts you in control and allows you to manipulate your data however you see fit. If this sounds like too much, don’t worry. Your workbooks can be as complicated or as simple as you want. That’s the beauty. It’s all up to you. 

Today, we’re going to keep it simple and go over how to make a budget in Excel. I’ll go over everything that you need to build a simple and functional budget with room to grow and customize as you see fit. If at the end of this it all seems like a bit too much for right now, you can download this budget workbook for free. 

Let’s get started!

Create a new workbook

The first thing you are going to need to do is open up Excel. For this tutorial, I will be using Excel in Office 365 but any differences should be minor. 

Once Excel is open you will want to click on “New blank workbook.”

ExcelNewWorkbook_How to Make A Budget In Excel

Now that you have a new workbook open, take a moment to envision your future budget. There is a lot of blank space and a lot of possibilities. There are countless ways to set up the layout for your budget but, again, for this example I’ll be keeping it simple. Everything that I go over can be applied to different layouts. 

Create the income section

The first part of your budget that you are going to want to set up is the income section. This is where you are going to record all of your income for the month. 

Some people will have a single paycheck that comes in each month with little to no changes from month to month. Others may have multiple jobs with irregular income. This will determine the scope of your income section. 

There are a few basic elements that you will want to add for this area:

Income source column: A column that labels each income source, ie. Main, Secondary, Side Work, etc. This is up to how you want to identify each source. For this template, I will be labeling these Income 1, Income 2, and so on.

Date column: A column that shows the date received for each income amount. This is not strictly necessary but adds value in that you can get a better idea of the timing of your income in relation to your expenses.

Expected column: A column for the expected amount of each income source. This one will be easy if you have a regular salary. If, however, you work hourly with a schedule that is different every week, this will be your best estimation. This can also come in hand if you know you can work more hours and are aiming to save more toward a specific goal.

Actual column: A column for the actual amount of each income source. This is going to show what you actually got paid and will allow us to determine how well you estimated your income in the expected column.

Difference column: A column that shows the difference or variance between your expected and actual income. This will allow you to see if you are hitting the goals you set yourself for income each month.

Total row: A row that totals the amounts in the Expected, Actual, and Difference Columns. This will show us our total expected and actual income, as well as the difference between the two.

Helpful Hint

Whenever I am creating a spreadsheet, unless I am using absolute referencing, I will place my work somewhere away from the top and sides of the worksheet that I am working in. This will allow me to more easily format and make things look nice when I am finished by giving me more room to visualize how I want things to look. When I’m ready, I will just select everything and move it into place. 

Label the income section

When making a budget in Excel, we want to label everything so that we know what is going on. Some columns that we set up will be blank, patiently awaiting our input. If these aren’t labeled, not only will it look a little weird but we also risk forgetting what they are for. 

Since we will be making five columns, we will need five labels: “Source,” “Date,” “Expected,” “Actual,” “Difference.” To set these up, simply click in cell C4 and type each cell. You can hit the Tab key to move the next cell to the right or use the right arrow key. 

Label_Income_Section

Income source column

For the income source column, create your labels for each income source you have each month plus a few extra in case you find money, or earn an unexpected bonus. 

The first step is selecting the first cell of your income source column. As I mentioned in the Helpful Hint above, I like to space things out more in the middle of my workbook to allow room for formatting and labeling later. 

Click in cell C5 and type “Source 1.” 

Income_Source_Column_03

For the rest of your income sources, instead of typing out “Source 2,” “Source 3,” etc., you can just click on the bottom right corner of the green box that is around the selected cell.  

Source_column_04

Then, while still holding the click down, drag the corner until you have selected the number of income sources that you want for your budget. Excel will auto fill the cells for you. 

Income_Source_Column_05

Date column

Next, we need to set up the Date column. Since the dates will depend on your own timing of income, I will fill these in with various dates to serve as place holders. 

Click in the D5 cell and type a date. You can either press the Return or Enter key to move down one cell or you can hit press the down arrow key. I put two dates near the beginning of the month to represent a typical paycheck timing and one later in the month. Let’s say it’s your birthday and you received some unexpected birthday cash. Congratulations!

Date_Column_01

You may have also noticed that Excel recognized that these were dates entered and formatted the cells at “Date” in the dropdown above in the Home ribbon. 

Date_Column_01

This defaults to the Short Date format which is what we typed in but this could be changed to the Long Date format, if you prefer, which shows the day and month written out. It’s all up to you.

Date_Column_02

Expected column

On to the Expected column. Here we will add in what we expect to get paid. 

Click cell E5 and type in what you expect for Source 1 and Source 2 income. I will put a placeholder amount of $3,000 for Source 1 and $2,500 for Source 2. 

For Source 3, since it is unexpected birthday money, I will place $0. I will add this amount to Source 4 and Source 5 as well, since there is no expected income for these in my example. 

Expected_Column_01

Notice that the formatting for these numbers don’t look like they represent money. If you look at the formatting dropdown where we saw the Date formatting from earlier, you will notice that the format is General. We want to change this to Currency. 

Do this by selecting all of the cells in the Expected column, cells C5 through C9. To select the cells, click on the first cell, cell C5, and while holding the click, drag the cursor to cell C9 and let go. 

Expected_Column_02

Next, click the drop down menu and select Accounting. Currency will work just as well for this but I am an accountant so I am biased. Also, the dollar sign is aligned to the left, which I prefer, versus up against the number. This one is up to you. 

Expected_Column_3

Actual column

Now we want to add the amounts that we actually got paid. To do this, click in the F5 cell and enter in the amount for Source 1. Then hit the Return key (or the Enter key or down arrow key) and enter in the amount for Source 2, and so on. For Source 3, your birthday money, I was very generous and gave you $500. 

Actual_Column_01

Again, you will notice that the formatting is General. You can either do what we just did for the Expected column or you can select the cells in the Expected column, cells C5 through C9, and click Format Painter on the ribbon above under Home. For our convenience, it looks like a paint brush. 

Actual_Column_02

Once you’ve clicked on the Format Painter icon you will see the selected cells have a moving dashed border around it, showing that you’ve selected these cells for an action. This same dashed border appears when copying cells, for example. You will also notice that your cursor has a paint brush to the right of it. 

Actual_Column_03

The next step is to select the cells that you want to apply the new formatting to, in our case cells F5 through F9. Click cell F5 and drag down to cell F9 to select these cells. Your formatting will automatically be applied. 

Actual_Column_4

If you see hashtags or pound signs in some of your cells, that just means that the contents of that cell exceed the space in the cell. This is an easy fix. 

One way is to pull the edge of the “F” at the top of the column to the right to expand the width of the column. Do this by placing your cursor on the line between the “F” and the “G” at the top of the column. Your cursor should become a dark black line with an arrow pointing to the right and to the left. Then just click and hold while dragging to the right to expand your column however much you need. 

Actual_Column_05

Or you can click the triangle in the corner of your worksheet, above the “1” row and to the left of the “A” column to select the entire worksheet. 

Actual_Column_06

Then you will place your cursor on one of the lines between the letters of the columns and double click. It doesn’t matter which columns you chose to click between. This will adjust all of the columns in the worksheet to adjust to the contents in each column. If you drag, all cells will widen  together .

Actual_Column_07

Difference Column

The last column that we need to complete for the income section is the Difference column. This is going to show us the difference between the Expected and Actual income for each income source. 

First, we want to click in cell G5. Here, we are going to enter in a simple formula that will show how much more (or less) our actual is versus our expected income. 

Every formula will begin with an “=” followed by the formula. For this formula, we will simply be subtracting the Expected amount from the Difference amount. 

The formula will look like this: 

=F5-E5

You can either type this out or you can enter “=” and then click on cell F5, then enter “-” and then click on cell E5. When you click on a cell while entering in a formula, it will be bordered by a color. This is helpful to keep track of what cells are involved in your formula. 

Difference_Column_01

Difference_Column_02

Difference_Column_03

When you are done entering in your formula, simply press the Return key. Now you have a value in the Difference column calculated based on your Expected and Actual columns. 

Difference_Column_04

To apply this formula to cells G6 through G9, select cell G5 and then click and hold the bottom right corner of the green border around the cell and drag it down to cell G9 the release. Excel will auto populate these cells with the formula and will apply the formula to the correct cells in the Expected and Actual column. 

Difference_Column_05

Difference_Column_06

Total row

To wrap up the income section of our budget, we will want to total our income so we can see what our totals are for the month. 

To do this, click in cell C10 and type “Total.”

Total_Column_01

We will want to total the amounts in the Expected, Actual, and Difference columns. To do this, we just need to enter in another simple formula. For this one, we’ll use the SUM function. 

Click in cell E10 and begin your formula by typing “=” followed by “SUM” and then an opening parentheses “(.“ When typed out, it should look like this:

=SUM(

Total_Row_02

Next, we need to select the range that we will be adding, or finding the sum of. This can be done a number of ways. 

  • You can click and hold on cell E5 and then drag down to cell E9.
  • You can click on cell E5, then hold down the Shift key and click on cell E9.
  • You can simply type the cells that you wish to total. In this case it will be “E5:E9.”

Total_Row_03

Total_Row_05

Then press Return. Your total will appear in cell C10. 

Total_Row_04

To apply this formula to cells F10 and G10 we will do what we did for the Difference column formula. Select cell E10 and click, hold, and drag the bottom right corner of the green border to cell G10. Alternatively, you can select cell E10 and copy the formula by either right clicking and selecting copy, or press Command “C” for Macs, or Control “C” for Windows. 

Next, select cells F10 and G10 and either right click and select the clipboard image with the italic “fx” on it under Paste Options (this will paste the formula), or press Command “V” for Macs, or Control “V” for Windows.

Total_Column_06

Total_Column_07

The last thing to do is to make sure the Total row matches the Expected, Actual, and Difference column formatting. I chose to go with the Accounting format.

Helpful Hint

When right clicking and selecting a paste option, you can paste the numbers only without the formula. This can be handy if you are pasting into another workbook where cell referencing will not move over but you want the values. 

Create the expense section

The income section of our Excel budget is complete. The next step is to create our expense section where we will be putting all of our monthly expenses. 

Many of the steps will be similar to the steps used to create the income section. Instead of a Source column there will be a Description column and the Date column will become Date Due. The Expected, Actual, and Difference columns will remain the same. 

We will also want to separate our expenses into two large categories of fixed and variable expenses. This will let us analyze each separately and gain better insight into what areas we can work on improving. There will also be a Total row for fixed and variable expenses. 

That means the important elements for our expense section are:

Description column: This is where we will enter in the description of our expenses.

Date Due column: This is where we will enter the date that our various expenses are due. If they do not have a specific due date, such as gas, this column will be left blank for that particular expense.

Expected column: This serves the same purpose for our expenses as it does for our income. We will be putting what we expect our expense amount will be for each particular expense.

Actual column: Again, this serves the same purpose for our expenses as it does for our income. We will be putting what our actual expense amount is for each particular expense.

Difference column: This will also serve the same difference as it does for our income. This will show us if our actual expense amount is higher or lower than our expected expense amount.

Fixed vs Variable Expenses: One final difference between our income and expense sections is that we want to keep track of our fixed versus variable expenses separately. This will be added in after we set up everything else but before adding the total rows because we want to be able to see the totals for fixed and variable expenses separately.

Total rows: Just as with our income, we want to be able to see the totals for our Expected, Actual, and Difference columns.

Fixed Expenses

For this example, we will be splitting the expense section into fixed expenses and variable expenses. Everything will be the same except for the types and descriptions of the expenses. We will start with the fixed expenses first, things like rent or mortgage, car payments, etc. and then set up the variable expenses, things like gas, for example. 

Before creating our columns, we will want to label this section “Fixed Expenses.” Do this by clicking in cell C14 and typing “Fixed Expenses” then press Return. We will be going back later to format everything and make it all look more pleasing. 

Fixed_Expenses_Column

Description column

To start off our Fixed Expenses section, we will begin with the Description column. Again, leaving some space between our income section and our expenses section will be helpful when we go back to format and make things look nice. 

Click in cell C15 and type “Description.” Press the Return key. 

Description_Column

After this, we want to have the descriptions for all of our expenses listed in our Description column. These are going to be for our fixed expenses so this will be populated with things like your rent or mortgage, car payments, phone, internet, gym membership, daycare, and anything else that is more or less the same each month.

For this example, I will be entering in “Fixed Expense 1,” Fixed Expense 2,” and so on. You can drag the corner of the first cell down to cell C25 to auto fill in the rest of the column.

Description_Column_02

Description_Column_03

Date Due column

For the Date Due column, click in cell D15 and type “Date Due” and press the Return key. 

Date_Due_Column_01

If your screen looks like mine and things are getting a little crowded, just use our trick from before to adjust our cells to their contents by clicking the triangle at the top left corner of our worksheet cells (between row “1” and column “A”) then double click between one of the lines between letters at the top of two columns. 

Date_Due_Column_02

Date_Due_Column_03

Since this is for the fixed expenses, there is probably a due date associated with each of these. I will put in place holders like I did for the income section. Feel free to format as Short Date or Long Date as you prefer. 

Date_Due_Column_04

I will leave some of these blank since they will likely not all be used. If, however, you have more fixed expenses than the 10 that I put, feel free to add more to fit your situation. Remember, this is your budget template. 

Expected column

The Expected column for expenses will be the same as for the Expected column for income. Click in cell E15 and type “Expected” then press the Return key. 

Expected_Column_Expenses_01

Next, enter in the expected amounts for each fixed expense. Since these are fixed, there probably won’t be any difference or at least a very small difference. That’s okay, in case there is a difference, we will be prepared. 

Expected_Column_Expenses_02

I put zeros for the fixed expenses that are not being used. You could just delete these if you wish. However, if a new fixed expense begins during the month, like a gym membership, you could just fill these in along with the due date. 

We want this to be formatted the same as our income section so select cells C16 through C25 and use the format drop down menu in the Home ribbon to select the Accounting format (or Currency if that is what you used before). 

Expected_Column_Expenses_03

Actual Column

Click in cell F15 and type “Actual’ then press the Return key. 

Actual_Column_01

Next, enter in the actual amounts for each fixed expense. These will probably be the same as the expected amounts but I will throw in a few slight differences for fun. Maybe your internet bill was increased a few dollars going forward. 

Actual_Column_02

Format the Actual column just like you did for the Expected column. 

Actual_Column_03

Actual_Column_04

Difference Column

Click in cell G15 and type “Difference” then press the Return key. 

Difference_Column_Expense_01

Now we want to enter in the formula for the Difference column. This will be similar to what we used in the income section except instead of subtracting the Actual amount from the Expected amount, we want to subtract the Expected amount from the Actual amount. 

Why? This way, if our expenses increase it will show up as a negative number. This will allow us to net the differences of expected and actual income and expense totals together which will show if we have a net increase or decrease in total difference each month. 

The formula will look like this:

=SUM(F16-E16)

Difference_Column_Expense_02

Difference_Column_Expense_03

Difference_Column_Expense_04

Copy and paste this formula to cells G17 through G25 then press the Return key or drag the corner of the cell down to cell G25. 

Difference_Column_Expense_05

Difference_Column_Expense_06

Total row

The last step is to add our Total row. Click in cell C26 and type “Total” then press the Return key. 

Total_Expense_01

Next, we want to add the same formula that we used to calculate the totals in our income section for the Expected, Actual, and Difference columns. First, we add it to cell C26 to calculate the total for the Expected column. 

The formula should look like this:

=SUM(E16:E25)

Total_Expense_02

Total_Expense_04

Then, copy the formula to cells F26 and G26 to calculate the totals for the Actual and Difference columns. 

Total_Expense_05

Total_Expense_06

You can see that even though our fixed expenses went up, it is represented as a negative number. This lets us net against our increase in income to see how much extra money we earned this month, or lost, compared to what we expected. 

Variable Expenses

Our Fixed Expenses portion of our Expense section is complete. Now we need to create the Variable Expenses portion of our Expense section. Since each step is the same as the Fixed Expense portion above, I will not go over it again to save time and space. Just repeat the steps above. There will, however, be no due date unless you have expenses due the same time every month that fluctuates significantly each month, like utilities.

I will be leaving a one row gap between the Total row of Fixed Expenses and the “Variable Expenses” title below. When you finish, it should look like this:

Variable_Expense

Create additional sections

Congratulations! You have a budget with an income section, and an expense section that compares expected versus actual with totals and differences. These are the bones of a budget. There really isn’t much else that you need. 

However, I would like to go over a couple of additional sections that you may want to add to your budget. These will be useful and will give you an idea of how to customize your budget to fit your goals and situation. 

I would like to go over two additional sections for this budget:

Discretionary Expenses: These are expenses like eating out, weekend trips, coffee runs to Starbucks, etc. Keeping track of these separately will give you a better overall picture of where your money is going each month and what you can cut, if necessary.

Funds and Savings: There are a bunch of different types of funds and savings out there. Many people use sinking funds to save for expenses that you know will take place during the year but aren’t every month. Adding these in will help you prepare and budget for these expenses. Holiday funds are another popular way to prepare for expected one time expenses. 

Discretionary Expenses

The first section I would like to add is discretionary expenses. This will include eating out, trips, superfluous shopping, etc. This will help you see how much you spend on “extras” each month. 

Click in cell C42 and type “Discretionary Expenses” then press the Return key. 

Discretionary_Expenses_01

Next, we will use the same labels that we used in the expense section except we will use Date instead of Date Due. Add these in cells C43 through G43. 

Discretionary_Expenses_02

After this, fill in the description for any planned discretionary expenses. Include any planned trips, dinners out, shopping trips and anything else that you expect to spend on during the month that would fall under this category. 

Discretionary_Expenses_03

Next, add expected and actual amounts, as well as the same formula that we used in the rest of the Difference columns from the expense section.

Finally, add a Total row just like the ones used in the other sections and format everything to match. When it is all done it should look like this:

Discretionary_Expenses_04

Funds and Savings

Both sections will be identical. We will be labeling the titles the same for these sections. The descriptions will be different, representing your funds and savings, respectively. Amounts, of course, will differ as well. I’ve added zero amounts for your savings area.

Funds will represent various funds including emergency funds, sinking funds, etc. While savings will include any longer term savings accounts you may have such as CD accounts or money market accounts. To learn more check out my article Best Types of Savings Accounts and Funds to get a breakdown about which funds and savings accounts might be right for your personal budget.

The expected amounts will represent our expected contributions and the actual will be our actual contributions. The difference here will be calculated the same as our expenses. If we contributed more than we expected, we spent more money and we want this to show as a negative number. 

Completed, this section should look like this:

Funds_Column_01

Savings_Funds_01

Add analytics

Analytics will help you read your budget and figure out what is really going on with your finances. This can be as simple as knowing that you spent more than you earned in any given month. It can also be as complex as projecting figures into the future based on past data or other factors. This is one reason Excel is so powerful. 

For this budget, I want to keep things simple but still provide some useful information. This means including a simple comparison of total actual versus expected income and expenses, total income (loss) for the month, and what percentage of your expenses are discretionary. 

These can all be customized to suit your own wants and needs.

Total Expected vs Actual: This will compare total expected vs actual for income and expense. This will show how accurate your estimations are to better inform your estimations in subsequent months.

Total Income (Loss): This section is going to show you how much extra money you have at the end of the month or how much more you spent compared to how much you brought in. This will help inform you whether you need to cut expenses or bring in more money each month to effectively cover your expenses.

Percentage of Discretionary Expenses to Total: This will show how much of your expenses are discretionary. Knowing this will help you understand how much of your expenses can easily be reduced going forward.

Total Expected vs Actual

A comparison of total expected versus actual income and expenses will give an overall idea of how well you are estimating your income and expenses. If you are significantly underestimating your expenses and overestimating your income you may find yourself running into issues. This will help.

Since our budget is beginning to get rather long, I will be adding the analytics at the top of the worksheet to the right of the income section. This way, it will be easy to find and read. 

The first step is to title the section. Click in cell I3 and type “Total Expected vs Actual” then press the Return key. 

Next, click in cell I5 and type “Income” then press the Return key. In cell I6 type “Expenses” then press the Return key. 

Finally, in cell J4 type “Expected,” in cell K4 type “Actual,” and in cell L4 type “Difference”  then press the Return key.

Total_Expected_Vs_Actual_01

Now we want to collect the total expected amount from all of our income and all of our expenses as well as the total actual amount for all of our income and all of our expenses. This will be done using the SUM function. 

First we will click in cell J5 and begin our formula by typing “=SUM(“. After this we are going to need to select each total expected amount for each section. For the income section there is only one total so this one is easy. Just select cell E10 which is the cell that has the total expected income. 

Your formula should look like this:

=SUM(E10)

Note: because there is technically no addition happening here you could also simply type “=E10.”

Total_Expected_Vs_Actual_02

Total_Expected_Vs_Actual_03

Total_Expected_Vs_Actual_04

Now, do the same thing in the actual column using the amount from the total actual income in cell F10. Your formula for cell K5 should look like this:

=SUM(F10)

Total_Expected_Vs_Actual_05

In the expected column for the expenses row(cell J6), we are going to do the same formula but we will be totaling all of the total expenses, including our fund contributions. We do this by beginning our SUM formula and selecting each cell while holding down the Command key for Macs or the Control key for Windows. After you have selected each total press the Return key. 

Your formula should look like this:

=SUM(E26,E40,E49,E58,E67)

Total_Expected_Vs_Actual_07

Total_Expected_Actual_01

Next, do the same thing in cell K6 by beginning your SUM formula and, while holding control, select all of the total amounts for the Actual column. 

Your formula should look like this:

=SUM(F26,F40,F49,F58,F67)

Total_Expected_Acutal_02

To add the difference formula, use the same formula that we used for the income difference for the income row, and the same formula that we used for the expenses in the expenses row. 

Your formulas should look like this:

Income: =K5-J5

Expenses: =J6-K6

Total_Expected_Vs_Actual_10

Total_Expected_Vs_Actual_11

Total Income (Loss)

This is going to show you how much extra money you have at the end of the month or how much more you spent than what you made. 

Start by adding your title by clicking in cell I9 and typing “Total Income (Loss)” and pressing the Return key. 

Total_Income_loss_01

To calculate this we are simply going to subtract our total actual expenses from our total actual income. We just calculated these amounts in the above section so we will use these cells in our formula. Click in cell I10 and begin your formula with “=“ and then select cell K5. Next type “-” and then click on cell K6 and press the Return key. 

Your formula should look like this:

=K5-K6

Total_Income_loss_02

In our example we have an extra $1,518 of income after all of our expenses. This should be put toward savings, investing, and paying off debt. If you wanted to, you could set it up so that extra income is automatically allocated to various savings accounts, investments, or debt payments. 

Percentage of Discretionary Expenses to Total

Our last analytic for this example is to show what percentage of our expenses were from discretionary expenses. In other words, how much of our spending was not necessary. This can be a good way to see where spending can be reduced. 

Start by titling the section by clicking in cell I13 and typing “Percentage of Discretionary Expenses to Total” and pressing the Return key. 

Percentage_of_Discretionary_Expenses_to_Total_01

Next, in cell I14 type “Discretionary Expenses” and press the Return key, then in cell C15 type “Total Expenses” and press the Return key, then in cell I16 type “Percentage” and press the Return key. Format as necessary to accommodate large titles in cells. 

Percentage_of_Discretionary_Expenses_to_Total_02

Now we need to add in the amounts. In cell J14 type “=” and then select the cell containing the total actual amount for discretionary expenses. 

Your formula should look like this:

=F49

Percentage_of_Discretionary_Expenses_to_Total_03

For Total Expenses, type “=” in cell J15 and use the amount from the section above in the Actual column of the Total Expected vs Actual section for the expenses. 

Your formula should look like this:

=K6

Percentage_of_Discretionary_Expenses_to_Total_04

Finally, to calculate the percentage we want to divide the discretionary expenses by the total expenses. In cell J16 begin your formula by typing “=” then click cell J14. Type “/” and then select cell J15 and press the Return key. 

Your formula should look like this:

=J14/J15

Percentage_of_Discretionary_Expenses_to_Total_05

This is going to return a nasty looking decimal which works but doesn’t look very nice. Click on cell J16 and, from the formatting dropdown in the Home ribbon, select Percentage. Now your section should look like this:

Percentage_of_Discretionary_Expenses_to_Total_06

Percentage_of_Discretionary_Expenses_to_Total_07

You can see that in our example, $465 or 10% of your expenses were from discretionary spending. 

Make it look nice

Now that we’ve got all of the pieces put together we want to make this a little more friendly on the eyes. Formatting is all a matter of preference as is the overall layout of this budget. We could have (and still could) move everything around into different positions if we were so inclined. 

For now, I’ll leave everything put and simply add some additional labels and format things in a way that should help make everything easier to read at a glance. We will be doing two types of formatting for this budget:

Titles: We will be formatting each title to make it stand out from the data. We will also be adding titles that have not been added yet.

Colors: Colors are a good way to break up different types of data and make it more easily distinguishable.

Titles

The first thing that we should add, since this is a monthly budget, is the month. We can make this nice and large at the very top so we always know which month we are working in with just a glance. 

Our budget populates columns C through L so I will make the month centered over these columns. To do this, select cells C1 through L1 and in the Home ribbon where it says “Merge” click and select “Merge & Center.”

Titles_01

Next, in the merged cells type “August” then press the Return key. You should see the world “August” centered in the merged cells. Now we want to format the title so it stands out. Click on the merged cells and, in the Home ribbon, click on the font size dropdown and select a size that you feel is large enough; I chose 36. I also selected the “B” option to the right of the font to make the title bold. Select a font from the dropdown that you like, or you can leave it at the default font. 

Titles_02

Perhaps you may have noticed that we didn’t add a title for the income section or the Expense section. Let’s fix that. Click in cell C3 and type “Income” then press the Return key. Now click in cell c13 and type “Expenses” then press the Return key. Let’s also make these titles bold and a little bigger. I think 18 point font looks pretty good.

Titles_03

For the subcategories of expenses, we want to make sure they stand out from the data but also aren’t as large as the Expenses title. 16 point font works well without using the bold formatting. Apply this formatting to all of the expense subcategories.

Titles_04 

Also, let’s apply the formatting that we used for the income and expenses titles to the analytics section. We should also make the subtitles in each section (Description, Date Due, Expected, Actual, and Difference) bold to stand out a little bit more. 

Titles_05

Colors

Adding color is a good way to make certain sections really stand out from others. This isn’t strictly necessary so if you don’t want any color, you definitely don’t have to add it.

Adding color is easy. If you select any cell and click the paint can drop down in the Home ribbon, you can select a color that will fill the selected cells. 

Colors_01

Similarly, you can change the font color by clicking the large “A” drop down in the Home ribbon. 

Colors_02

Whatever you decide, make sure that you like how it looks and that the formatting makes it easier to use for you. Also, always remember to have fun!

Free Template

That’s it. I hope you enjoyed this tutorial on how to make a budget in Excel. If this just seems like too much work and you like the look of the budget that I created in this example, you can download it below.

Feel free to edit it and make it your own according to your own preferences by adding or deleting categories. In the end, a budget is supposed to be a very personal thing to help you with your finances.

When you finish with a month, simply copy the worksheet into a new sheet tab at the bottom of the sheet and reliable the month at the top. 

Happy budgeting!

DISCLOSURE: THIS POST MAY CONTAIN AFFILIATE LINKS AND/OR PAYED PLACEMENT. PLEASE READ MY DISCLOSURE FOR MORE INFO.

0 Comments

Submit a Comment

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

Recent Posts

21 Shares
Tweet
Share
Pin21
Email