Set up the column headers
Open Excel with a new workbook, and in Sheet1 enter Date, Description, Amount, and Remainder in cells A2, B2, C2, and D2.
|
Enter the budget amount
Enter the budget limit of $300 in cell D1. Format that cell as currency style.
|
| Design sidenote: You might wonder why I started in row 2 for the column headers. I really made a false start by entering the column headers
in row 1. Then I realized that I needed to put the budget limit somewhere near the top of the spreadsheet. So I inserted a row above row 1, so that
row 1 became row 2, then I entered $300.00 in cell D1. |
Enter the first expenditure.
Enter your first expenditure in row 3, cells A3:C3. If the description is long, you'll have to double-click in between column headers B and C
to widen column B. Select cell C3 and click the currency style button in the Format toolbar.
|
Enter the Remainder formula.
Select cell D3 and enter the following formula:
=D1-C3
|
Enter the next expenditure.
Enter your next expenditure in row 4, cells A4:C4. Select cell C4 and click the currency style button in the Format toolbar.
|
Enter a new Remainder formula.
Select cell D4 and enter the following formula:
=D3-C4
Notice that the new remainder is now the remainder above it minus today's expenditure.
|
Enter the third expenditure and simplify.
Enter your next expenditure in row 5, cells A5:C5. Select all of column C and click the currency style button in the Format toolbar.
The value of "Amount" in cell C2 won't change even though the cell format has changed.
|
Fill down the Remainder formula.
Select cell D4. Hover the mouse over the fill handle in the lower right hand corner of cell D4. It's circled in the image below.
Left-click and hold and drag down to cell D10 and let go. This fills down the remainder formula so you don't have to enter it ever time
you make a new row of data.
After filling down:
|
A neat formula to make the worksheet look clean.
While filling down the remainder formula save you time in reentering it every time you make a new row of data, it does make the
worksheet look messy with the "unnecessary" values in column D and nothing in columns A:C from rows 6:10. We can clean this up
with the IF fucntion and the following formula which we shall enter in cell D5:
=IF(C5<>"",D4-C5,"")
Notice that those are double quotes. What the formula says in plain English is "If cell C5 is not empty then show the formula
D4-C5; if cell C5 is empty, show an empty cell". In the image below, cell D6 is selected to demonstrate the effect of the new formula.
Here is the completed Excel workbook.
Click the Save button when the File Download box pops up.
CountDownBudget.xls
|