Quick Links
Like most people, you probably worry about your finances often. Which is where creating a budget and sticking to it proves its worth. We’ll show you how to create a personal budget in Excel and learn to manage your money better.
1. Structuring Your Budget in Excel
Before we begin, make sure you gather all your financial information in one place. This includes your bills, bank statements, paychecks, tax forms, and everything else. This will be easier to do if you already have a habit of tracking your transactions.
Once you’re ready, open a new Excel worksheet. Note that we’re creating an annual budget from scratch in this guide, but you can find free templates if you want to expedite this process.
i. Create Headers for Income and Expenses in Your Excel Budget
The first step in structuring your budget is to create headers. Type the names of the months (January to December) in a row from cell C2 to N2, and type “TOTAL” in the O2 cell to sum up the annual figures. Next, we’ll create an income section that lists all your earnings.
Type “INCOME” in the A3 cell. Then, starting from cell B3, list down your income sources like paycheck, bonus, interest, and gifts in a column, one below the other. At the end of the list, type “Other” for income obtained from miscellaneous sources, and type “TOTAL” in the cell below. Refer to the image below.
The income section is made. Next, create the expenses section following a similar structure. Here, I strongly advise dividing your expenses into two separate sections: needs and wants. This allows you to budget for each separately and remain disciplined.
Also, if you’re ever over budget, you can see whether it’s because you are overspending on your wants or because the cost of living has increased due to inflation.
Type “NEEDS” in a cell in the A column below the income section, then list down expense categories such as rent/mortgage, groceries, utilities, and more. The needs section only includes expenses that are necessary for survival, honor an obligation, and are required for you to do your job effectively (and no, a new iPhone is not a need). Refer to the image below.
Similarly, create the “WANTS” section. This is where you list down things like travel, shopping, and entertainment. Remember to type “Other” and “TOTAL” here as well. Finally, in column B, type “MONTHLY SAVINGS” below the three sections you just made. This is where your savings will surface once we start doing the math.
ii. Enter Your Income and Expenses Into Your Excel Budget
You’ve made the headers, now it’s time to start filling up the cells with the appropriate values. Refer to your financial documents and enter your monthly income and expenses accordingly.
You can always create or remove categories later on as you see fit. Once done, your budget will start taking shape and should look like this:
iii. Find Remaining Balance and Insights in Your Excel Budget
You’re almost done structuring your budget. The last step is to find your total monthly and annual income, expenses, and savings. To do that, we’ll use two simple formulas. The first one isthe SUM function, which helps you add up the values in a group of cells.
For example, to find out my total income in January, I will type “=SUM(C3:C9)” in the C10 cell. Similarly, I’ll find my total income and expenses for all the months using this formula.
Note that you might have fewer or more categories than the example I’ve shown here, so the cell range you choose in the formula will differ.
Use the same formula to find your total annual income and expenses as well. For example, to find out how much I spent on groceries in a year, I’ll type “=SUM(C13:N13)” in the O13 cell. Once you’ve done all that, your budget should look like this:
Lastly, we’ll calculate both monthly and annual savings. This is where we’ll use the second formula. For me, my total income, the total amount spent on needs, and the total amount spent on wants in January are located in the C10, C21, and C31 cells respectively. So, to find my savings for January, I’ll simply type “=C10-C21-C31” in the C32 cell.
Do the same for the entire row up until the end of the inputted data.
2. Make Your Budget Easy to Understand With Excel Tools
After you’re done creating the headers, entering your financial data in the correct order, and finding your savings, the next step is to make your budget easy to understand at a glance.
This will help you save time and immediately get a visual indication of whether you’re under or over budget. To do that, we’ll do three things: create borders, use conditional formatting to spot overspending, andcreate a chart in Excel.
First, we’ll create borders around the total values row and column in the income and expenses sections. This can be done by using the Borders button located in theFontsection of the Home tab. Simply click on theOutline Bordersoption.
Once done, your budget should look like this:
i. Use Conditional Formatting to Spot Overspending in Your Excel Budget
Next, we use what’s calledconditional formatting in Excel. In a nutshell, it helps you identify values that you should pay attention to. For instance, it can highlight a particular cell when you’ve gone over budget.
Let’s say you’ve decided not to spend more than $800 per month on your wants. To be notified of when this happens so you may take corrective actions, first select the cells in the TOTAL row in the wants section from January to December. Then, selectConditional Formattingin the Home tab and selectHighlight Cells Rules > Greater Than…from the dropdown.
In the box that appears next, type “800” and clickOK. Immediately, you’ll notice that those months are highlighted where you spent more than $800 in total on your wants. Refer to the image below.
Similarly, you can use conditional formatting in other sections to gather more insights as per your needs.
ii. Create Charts to Visualize the Data in Your Excel Budget
We’ll be creating a simple bar chart for this example. To do that, we’ll first create a dataset that’s easily readable by Excel. Here, from cell Q2 to T2, write down “Income,” “Needs,” “Wants,” and “Savings” as headings. Under each heading, type the total of that section. It should look something like this:
Now, to create the chart, select the whole dataset (from cell Q2 to T3), then go to theInserttab at the top, and in theChartssection, click on theBar Chart. Give the chart a title and resize it as you see fit. Once done, your budget should look like this:
3. Set Savings Goals and Calculate a Deficit or Surplus in Your Excel Budget
The last step is to set savings goals and calculate your deficit or surplus against your actual savings. Instead of coming up with an arbitrary number, it’s wiser to try to save a certain percentage of your monthly income. Experts usually recommend saving at least 20%, but let’s say you want to target 30% instead.
To set a savings target, add a new row belowMonthly Savingsand label it “Savings Goal”. Here, in the C33 cell, I’ll type “=C10*0.3” to find 30% of my income in January. I’ll do the same for the rest of the cells (including months) too. Remember, the appropriate cell location for you’re able to vary, as discussed earlier.
Finally, we find a deficit or surplus. For that, create a new row under Savings Goal, titled “Deficit/Surplus Savings.” Here, simply subtract your savings goal from your actual savings for each month. It’ll look like this:
Use the same formula to find out your deficit or surplus savings for the rest of the months.
You’ve successfully created a personal budget in Excel. All that’s left now is for you to track your transactions, update your budget, and adjust your spending accordingly.
Using the insights in your budget, you’re able to figure out if you should cut back on your spending, or if you have some wiggle room for a new purchase.
