7

Creating a budget is of one the simplest and most useful tasks you can do using a spreadsheet. By using a spreadsheet, any changes you make to your budget are instantly updated across the whole budget, with totals calculated for you.

This tutorial shows you how to create a budget using Microsoft Excel 2013. If you’re using an earlier version of Excel or a spreadsheet from another company, you’ll find the principles and general techniques carry over, even though the menu commands differ.

I’ll show you how to create a simple budget summary for a small organization we’ll call Bleeding Heart Do-Gooders. You can use the same approach to create a household budget, a budget for a trip or a specific event, and so on

Adding data and formulae

Here’s how to create the budget:

  1. Open Excel and create a new blank worksheet.
  2. No matter what sort of spreadsheet I am creating—a budget, timesheet, whatever—I usually find it’s best to leave a couple of rows at the top and a couple of columns at the left empty when I start setting up the spreadsheet. This makes it easy to insert a heading or to reorganize the design—it’s amazing how often you discover you should have allowed for another column or row, and while it’s easy to insert these later it’s even easier to allow a little space to start with.

So, click in cell C4 (that is, column 4 row C) and type ‘Income’ (don’t include the quotation marks).

  1. Now we’re going to start listing our income categories and the amounts budgeted for each, so:
    1. Click in cell D5;
    2. Type ‘Donations’;
    3. Press the Tab key;
    4. Type ‘50,000’;
    5. Press Enter;
    6. Type Grants;
    7. Press the Tab key;
    8. Type ‘75,000’;
    9. Press Enter.

Do you notice how you press the Tab key to move between columns and the Enter key to move down to the next row starting in the first data entry column? It’s quite different from how things work in most other programs, but you’ll find it’s a consistent feature of spreadsheet programs.

  1. Let’s add one more entry in the Income category:
    1. Type ‘Miscellaneous’ and press the Tab key;
    2. Type ‘1000’.

TIP: Save your work

Now’s a good time to save your work. Yes, I know you haven’t done much, but once you’ve saved and named your work, you can quickly save work as you go by pressing Ctrl+S or clicking the Save button on the Quick Access Toolbar in the top left of the Excel window. It’s a good habit to develop.

So, click the Save button now, navigate to the folder where you want to save your work in the Save As dialog box, name the file Bleeding Heart Do-Gooders Budget (or just BHDG Budget). Excel will add the .xlsx extension for you, although if you want to share this budget with people who are using very early versions of Excel, you can choose to save it as an Excel 97-2003 Workbook (an .xls file) by selecting that option from the Save As Type drop-down list. Click Save and you’re ready to continue working.

That’s it for the income categories, so let’s total them up.

  1. Click in cell C8 and type ‘Total Income’.
  2. We want Excel to add up our three income categories and provide the result, so click and drag from cell E5 down through cell E7 to highlight the three cells containing the income amounts, then click the AutoSum button on the toolbar. (It’s a sigma symbol and it looks like this: ∑.)

When you click the AutoSum button it automatically adds up the values in the selected cells and places the result in the cell immediately below. You can use the same technique on a horizontal row of values and AutoSum will place the result in the next cell to the right of the selected numbers.

This illustrates how cells in your spreadsheet can contain data—numbers, text, images and so on—or formulae. If you want to know the formula Excel has used to provide a total for your income categories, click in cell E8 and then look at the expression displayed in the formula bar immediately above the data entry area. You’ll see:

=SUM(E5:E7)

That is, ‘display the sum of the values in cells E5 through E7’. If you click in cell E5, E6 or E7 the formula bar displays the same value you see in each of these cells, because each of those cells contains data. Cell E7, on the other hand, displays the result of a calculation based on data in the other cells.

TIP: AutoSumming non-adjacent values

If you want to put your total in a non-adjacent cell, here’s an alternative way to use AutoSum:

  1. Click in the cell where you’d like the total to appear.
  2. Click the AutoSum button.
  3. Click and drag across the cells you want to add up and press Enter.

Give it a try by clicking in cell G4, clicking the AutoSum button, clicking and dragging from cell E5 through E7, and pressing enter. You’ll see the total income displayed there. Once you’ve tried this, delete the duplicate total by clicking in cell G4 and pressing the Delete key (Del).

Let’s continue creating the budget.

  1. Click in cell C9, type ‘Expenses’.
  2. Add the data for the expenses. To start doing so, click in cell D10 and:
    1. Type ‘Accounting’, press tab, type ‘400’, press Enter;
    2. Type ‘Bank Charges’, press tab, type ‘250’, press Enter;
    3. Type ‘Board Expense’, press tab, type ‘400’, press Enter.

Follow the same procedure to add the rest of the expenses:

Conferences              1,200
Liability Insurance                 200
Miscellaneous              1,900
Office Equipment              1,500
Office Supplies                 800
Payroll expenses            92,000
Postage & Shipping                 600
Printing                 500
Rent            12,000
Travel                 650
Utilities              7,200
Internet              1,200

TIP: Adjusting column width

If you find the cells don’t display the complete text of some of your entries, you can easily adjust the column width either of two ways. The first is to click-and-drag the line between the column headers; as you do so, the left column will become wider. For example, to increase the width of column D, click-and-drag the line between column D and column E. Then use this technique to expand column E as well.An alternative method of resizing columns is to select the columns you wish to resize by clicking and dragging across the column headers—the selected columns will be highlighted—and then click Format -> AutoFit Column Width in the Cells section of the ribbon.(Note: If a column is too narrow to display a number, you’ll see hash marks (###) displayed in place of the number; when you resize the column, the number will be displayed.)

To finish entering the budget information, we need to add up the expenses and then figure out our net income by subtracting the expense total from our total income:

  1. Click in cell C25, type ‘Total Expenses’.
  2. Select all the expense amounts (click and drag from cell E10 to E24) and click the AutoSum button to place the total in cell E25.
  3. Click in cell B26 and type ‘Net Income’.
  4. To determine our net income, we need to use a formula to subtract the total expenses (in E25) from the total income (in E8). So, click in cell E26 and type:

=(E8-E25)

and press Enter. You’ll see ‘5200’ displayed as the net income.

TIP: Using AutoSum to create a formula

Instead of typing the formula to determine net income yourself, you can use Excel’s AutoSum feature to do most of the work for you:

  1. Click in the cell where you want to display total income (E26).
  2. Click the AutoSum button
  3. Click E8 (the total income).
  4. Press the – (minus key).
  5. Hold down the Ctrl key and click E25 (the total expenses).
  6. Press Enter.

Making things look good

Basic budget spreadsheet

Figure 1. (Click to see a larger image)

By this stage, you have a working budget summary which should look like Figure 1.

As I said, it’s workable, but it’s also bland and not particularly readable. We can fix that by adding a header and by formatting both the text and the numbers.

Here’s how:

  1. Click in cell A1and type ‘Bleeding Heart Do-Gooders’.
  2. Click in cell A2 and type ‘Budget Summary July 2013 – June 2014’. Allowing room for these headers is one reason we left some space at the top and left when we first created the spreadsheet.
  3. Let’s make all the headings bold. To do that:
    1. Click cell A1.
    2. Hold down the Ctrl key while you click each of the other cells that contains a heading: A2, C4 (Income), C8 (Total Income), C9 (Expenses), C25 (Total Expenses) and B26 (Net Income). Note how Ctrl-clicking allows you to select non-adjacent cells—a very useful technique to know.
    3. Click B in the Font section of the ribbon to bold the content of each of these cells.

As we’re dealing with dollar amounts, let’s format the numbers as currency as opposed to just plain ol’ numbers. So, click the column header of Column E to highlight the list of numbers then click Format -> Format Cells in the Cells section of the ribbon. The Format Cells dialog box will open.

  1. Click the Number tab and then click Currency in the Category list. Make sure to select 2 decimal places and choose whichever method of displaying negative numbers you prefer, then click OK.

Things are looking a lot more readable now, but we can improve things a little more by highlighting the three most important values, total income, total expenses and net income. We could simply bold those numbers as we did with the headings or you could choose any of a number of other options to highlight the values. Let’s use some lines to offset these totals:

  1. Click cell E8 (total income).
  2. In the Font section of the ribbon click the arrow on the right of the Borders button and select Top Border from the list. This places a line between the income amounts and the income total.
  3. Repeat the process for the total expense value (cell E25).
  4. Doll up the net income by applying some double borders. Click cell E26, click the down arrow beside the Borders button and select Top and Bottom Double Border.

TIP: Shortcut for repeating a selected menu option

Because you previously selected ‘Top Border’ from the Borders list, you can save time by simply clicking the Borders button to repeatedly apply a Top Border to other cells, instead of having to click the down arrow and select Top Border from the list. The button ‘remembers’ the last selection you made and makes it instantly available. Note that this is how many of the commands on the ribbon work, not only in Microsoft Excel, but also in other Office programs such as Microsoft Word: they provide a shortcut click to repeat the last-chosen option.

  1. Finally, take a look at the tab at the bottom of your worksheet labelled ‘Sheet1’. Let’s rename that to more accurately reflect the contents of the worksheet. To do so, right-click the tab, select Rename from the pop-up menu, type ‘Budget Summary’ and press Enter. One reason to do this is that a workbook can contain multiple worksheets. At some point in the future, you may wish to add monthly budget figures or other information on separate sheets within this BHDG Budget workbook. Having descriptive names on your tabs not only makes it easy to quickly move between worksheets, it can also help if you get into developing more complex formulae which use values from multiple worksheets.

Your spreadsheet should now look like Figure 2. It’s appreciably more readable, with important information highlighted. You can make it look much smarter if you want, but this is a good start.

The formatted budget

Figure 2. (Click to see a larger image)

Checking that it works

So the budget looks good, but does it produce the correct results? You can double-check that the formulae you’ve entered for total income, total expenses and net income are correct by doing a little addition for yourself. You should also check that the formulae work by changing some of the amounts for income and expenses. For example change the Grants amount to $80,000—just click in the cell, type ‘80,000’ (you don’t need to type the dollar sign or cents; Excel will add those for you) and press Enter. You should immediately see the total income rise to $131,000 and the net income jump to $10,400. Now increase the Travel expenses to $925. You’ll see total expenses as $120,875 and net income as $5,125.

In a spreadsheet as simple as this, it’s easy to check that your formulae are working. If you get into creating more complex spreadsheets, you’ll find Excel has a range of tools to help you create, check and troubleshoot your formulae and spreadsheet design.

A Little Lagniappe*

Excel displays useful information in the status line that runs across the bottom of its window, so keep your eye on that line. The type of information displayed changes depending on what you’re doing. If you’re not doing anything in particular, you’ll see an icon in the bottom left that you click to record a macro—a series of commands that, once you’ve recorded them, you can run repeatedly—while over towards the right you’ll see three page layout icons that let you switch between regular view and print-layout views, and on the far right is a slider that lets you zoom in or out on your spreadsheet.

Now, try this: In your BHDG Budget spreadsheet, click and drag across all the expense amounts, from cell E10 through E24 and then take a look at the new info that popped up in the status line: there you’ll see the average, count and sum of those amounts. This is really handy way to get a quick summary of any data you’re dealing with.

* Lagniappe is a Louisiana word that means ‘a little something extra for nothing’.

© 2013 Rose Vines

  • Gary Jackson

    Very nice, probably won’t put Quicken out of business. If you are working with cents how do you make that happen automatically?

  • David

    Very helpful, thank you!

  • Have you ever considered writing an ebook or guest authoring on other websites?
    I have a blog based upon on the same ideas you discuss
    and would love to have you share some stories/information. I know my subscribers
    would enjoy your work. If you’re even remotely interested, feel free
    to send me an e-mail.

  • I have been reading out many of your articles and i
    can state pretty clever stuff. I will definitely bookmark your website.

  • Karen Goodings

    How can I print off the instructions?

  • Karen Goodings

    right click and I can print it.

  • Sadip Baruwal

    Informative blog, one can learn a lot from here. Thanks for sharing I will also share a similar ms excel tool to Download https://www.excel-accountin… It is excel workbook where different accounting task can be done at once like p&l, income statements, profitability, budget analysis.