How to Create Excel Spreadsheets

Create a paper-free budget using an Excel spreadsheet.

Using Microsoft Excel can keep your finances organized and significantly reduce your manual calculations. Build an effective spreadsheet using the basic functions of Excel to keep track changes in your financial picture. Although Excel contains many higher functions including graphs and tables, the basic setup of an Excel spreadsheet is simple and easy to follow. Take a few minutes to set up a spreadsheet and save time calculating your expenses and revenue in the long run.

Instructions

1. Create row and column headings for your spreadsheet. In a new Excel worksheet, click on an empty cell. Type text into this cell, and press "Enter." If you're creating a home budget, your first cell might have the heading "Expense Type." In each cell in the column under this heading, type in an expense type -- for example, "Mortgage," "Car Insurance," "Groceries," -- to create a row for each expense. Then create further column headings next to "Expense Type," such as "Budget," "Actual" and "Difference."

2. Enter formulas into your spreadsheet. Doing this correctly will ensure Excel automatically calculates the figures you need. An Excel formula begins with an equal sign, or "=." For example, to create a formula to automatically calculate how much your actual expenditure varies from your budget estimate for a particular expense, click in the cell in the "Difference" column that corresponds with the row of the first expense. Type "=" into the cell. Click on the row's cell that falls under the "Budget" column. The cell's reference, a combination of a letter and number, will appear after the "=." Type a minus sign, "-," after the cell reference. Click on the row's cell that falls under the "Actual" column. This cell's reference will appear after the "-." Press "Enter." The cell will now equal your budget amount minus your actual amount for that expense. A sample formula should look something like this: =(B2)-(B3).

3. Copy formulas into more than one cell. Use the "Click" and "Drag" technique to copy the identical formula through a range of cells. For example, the formula for the difference between the budget amount and the actual amount for each expense will be the same for every row. After you've entered the formula into the first "Difference" cell, click on that cell. The cell will have a bold outline. Move your mouse over the bottom right-hand corner of the cell. Click and hold down your mouse button. Drag your mouse down through the cells into which you want to place the same formula. When you reach the last cell, release the mouse, and click. The formula will now appear in every cell. Excel automatically changes the formula to correspond to the figures in each row.

4. Enter your data figures into your Excel spreadsheet. For your household budget, enter the budget figure for each expense. As every expense is paid, enter the actual amount into the "Actual" column.

5. Use additional worksheets for more complicated figures. In your Excel file, you can create several worksheets. Create a separate worksheet for figures that will build over time. Create a worksheet for grocery expenses, for example, and type in the amount of the expense every time you go shopping. Create a formula to calculate the total of these expenses on the "Groceries" worksheet. Go to your main budget spreadsheet and click in the "Actual" cell in the "Groceries" row. Type "=" and then click on the total cell on the "Groceries" worksheet. Your budget sheet will automatically be updated as you add new expenditures to your "Groceries" worksheet.

References

• Microsoft Office: Basic Tasks in Excel 2010

Resources

• The University of Texas at Austin: Getting Started with Excel; February 2004
• The University of South Dakota: Excel Tutorial; 2009