Reclaiming control over your finances begins with setting a budget.
Microsoft Excel is arguably the most used software for creating, aggregating, and analyzing spreadsheets across the globe. It’s also a great tool for anyone looking to create a budget for their business.
In this post, we’ll share a step-by-step guide to create your own budget in Excel.
How to make a budget in Excel
- Open Excel and click on New blank workbook.
- Type “Budget 2020” in cell A1 to label the sheet.
- Skip one row, enter your income sources, and end the list with a total.
- Skip another few rows, enter your expense sources, and add a total.
- Apply different font styles to titles (optional).
- Drag right if the title cell is too narrow (optional).
- Type the word “Jan” into cell B2.
- Select B2 and drag right from the lower right corner to auto-populate other cells.
- Use the SUM function in B9 to find the total income per month.
- Auto-populate other “Total” cells as explained in step 8.
- Enter the income amounts to get the total income.
- Repeat steps 9 and 10 for expenses.
- Type “Profit” into cell A23.
- Subtract total expenses from total income to populate the “Profit” row.
- Auto-populate other “Profit” cells as explained in step 8.
We’ll go through each step further in detail below.
Why is budgeting important?
From Warren Buffett to Eric Ries and Mike Michalowicz, you’d be hard-pressed to find a single financial expert who wouldn’t recommend budgeting as the first step in managing your business’s finances. By taking your current sales numbers and expenses into consideration, you can create a business growth plan for the upcoming quarter (or year).
When you budget, you can
- Chart long-term growth goals. Budgeting helps you plan long-term financial goals, including how much money to allocate per month for expenses and an estimation of how long it might take to reach your goals.
- Save stress each month. Budgeting allows you to map out all of your expenses each month, so you aren’t scrambling on the last day of the month to pay your team.
- Prepare for slow months. What if you didn’t make any new sales for three months? Could your business survive a worst-case scenario like this? Budgeting can allow you to put away a portion of funds so that they’ll be ready in an emergency.
- Examine spending habits through your P&L. A profit and loss statement (i.e., P&L) can help you see where you’re spending money in black and white so you can decide which expenses are really necessary.
- Stop overspending. The more you overspend, the more you limit your future spending power. A budget can help limit unnecessary spending, improve your business’s profit margins, and help you work your way out of any existing debt.
Why is Excel a great tool for creating a budget?
Microsoft Excel has long been known as the standard in spreadsheet software, and it’s easy to see why. Excel functions can be as complex or as simple as you need. The power behind the program means you can forecast your funds years ahead, but the ease of the software means you can also create a simple log of transactions to track monthly spending.
You can browse Office 365’s budget spreadsheet templates, customize your own, or start from scratch.
Compared to other tools, Excel is the most flexible in terms of providing exactly what you need. Plus, Excel offers easy-to-use and highly practical functions that allow you to calculate sums and differences, and roll one month’s total into the next.
As an added bonus, Excel evolves as you need it to or as your skills grow, and won’t charge you a premium for using all of the tools it has to offer. And since it’s been a mainstay in the Office suite since the 80s, it’s unlikely to go anywhere anytime soon, which is a risk you may run if you choose an alternate personal finance tool that could change year to year.
How to create a budget in Excel
It is easy to overcomplicate bookkeeping and budgeting, especially when you’re brand new to it. We recommend starting with either a basic budget or a makeshift P&L statement.
Creating a basic budget in Excel is simpler than you may think and shouldn’t take you longer than 30 minutes. To begin, open Excel and click New blank workbook on the upper left side of the page.
Once your workbook loads, it will display as alphabetical columns and numbered rows. The rectangles that populate the sheet are called cells. Locate cell A1, which is the top left cell, and label your sheet “Budget 2020.”
Next, skip one row and begin entering income. Enter all of your products, clients, and any additional sources of income as new rows. Then, finish your list with a total.
From here, skip another couple of rows and enter your expenses. In this case, we chose to fill cell A12 with the blanket title “Expenses” and listed everything one would spend money on per month. Finish your list with a total.
This next step is optional but helps if you’re a visual learner. Toggle to the Home tab, where you can find Font options in the third box from left.
Here, you can modify the font style and size to make headings like “Budget,” “Income,” and “Expenses” stand out. Select the cell you want to modify and scroll through font styles until you’ve found something you like.
If the font becomes too large to show within the cell, click on the right border of the A column, and drag right to increase its width.
Once you’ve finished formatting the A column, type the word “Jan” into cell B2. Next, click the lower right corner to select the cell and drag it across to cell M2. Excel will automatically populate the other months.
Next, enter your current sales data.
To find your total income per month, use the SUM function to add all income. Type an equal sign (=), followed by the word SUM, an open parenthesis, the cell range of variables, and a closing parenthesis.
Click the lower right corner of B9 and drag it across to cell M9. Excel will copy the function to each respective cell, so the same equation is completed each month.
Now enter your expenses. Select cell B20 to find your total, using the SUM function. Hit enter.
Click the lower right corner of B20 and drag it across to cell M20 to populate expenses for each month.
Last, add a “Profit” row in cell A23 beneath your expenses to determine if you’re earning or losing each month.
Subtract your total expenses in January from your total income in January to get your monthly profit. Click the lower right corner of B23 and drag it across to cell M23 to populate your profit numbers for each month.
That’s how you create a simple budget in Excel. With these tips, you can create your own budget in Excel, saving money, eliminating excess spending, and staying on track to meet your business goals in 2021.