Spreadsheet Exercise #2

For this exercise, we will use the spreadsheet budget-ex.xls

Save the file to your Documents folder
Start up the spreadsheet in MS Excel

The following are step by step instructions to complete this spreadsheet.

Step #1: Formatting Text:

The spreadsheet in this example is adapted from the one shown on your text, Computer Confluence. To format the title in the spreadsheet, click on cell B1. Click on Home tab at the top of the page. Under this tab, there are toolbars available that allow you to change the font, style, colour, and alignment of your entries. The changes you select will be applied to the selected cells.

For the title cell, B1, select Bold, change the color and change the font size to 16.

For most formatting needs, the quick toolbars available at the top of the screen will suffice. However, a more detailed and thorough list of the available formatting options is available in recent versions of Excel by clicking on the small arrow in the bottom right of the font toolbar:

Which will open up a window that looks like this:

Let's make use of this formatting window to continue modifying the Column Headers. Select cells A3 to F3, then open the formatting window. Change the "Font Style" to Bold and the Size to 12.

Let's kick this up another notch by doing an even simpler format of all of the column A cells.
Click on the A button at the top of the spreadsheet to select the entire column. Now use the formatting toolbar to make all of that column Bold and 12 point size.

Now there's a problem that may not be obvious at first. The problem is that Column A is not wide enough for all of the labels in its cells. To solve this problem, we will adjust the width of the column. To do this, you need to position your cursor over the boundary between column A and column B and wait till you see the little "adjust" icon appear. Drag the cursor to the right until all of the labels in the Column A cells are visible.

Now, just one more formatting task for the labels, highlight the cells B3 to F3 and then click on the "Right Align" button from the set of alignment buttons on the Alignment toolbar.

Step #2: Entering Data:

Most of the data for this spreadsheet has already been entered. There are just a few more entries to make, and then you can tap into the power of the Excel Spreadsheet software to help you complete the rest of this budget worksheet.

To enter the monthly income for September, select cell B15, type the number 2700, and press the Enter key. Don't worry about getting in the dollar signs, commas, and periods just yet.
To enter the monthly income for October, select the next cell, C15, type the number 700 and press the Enter key.
Now the rest of the monthy incomes can all be set to the same as October, so the easy way to do that is:
- Click on the October income.
- Drag right to highlight the November and December incomes.
- Under the Editing toolbar, and select the Fill button
- Under the dropdown menu, select Fill Right.
Before proceeding, you should format the spreadsheet to be understood as currency. To do this:
- Highlight all of the cells that will contain dollar values (B5 to F17)
- Right Click these cells and select the "Format Cells..." option from the drop down menu
- Under the Number Tab, select "Currency." Make sure that negative numbers will be displayed in red and that numbers will appear to two decimal places. Click OK.
Now your spreadsheet will include dollar signs.

Step #3: Entering Formulas:

Don't forget to use the = sign before entering a formula.
The letters do not need to be capital letters.

The first formula will go into cell F5. Select that cell and enter the following formula: =SUM(B5:E5)
Press the Enter key to signal that you have completed the formula.
Now, use the Fill function to replicate that formula down the column to F11.
The second formula will go into cell B13. Select that cell and enter the following formula: =SUM(B5:B11)
Once again, use the Fill function to replicate this formula across the row to E13.
To enter the formula for the September Monthly Balance, select cell B17 and enter the following formula: =B15-B13
The next formula builds on that somewhat because you want to include the balance from the previous month in your calculation. Select cell C17 and enter this formula: =(C15-C13)+B17
Use the Fill function to copy that formula across the row to E17.
Finally, as a last test of a previous modification, change the value in D11 from 100 to 600. If you have adjusted the currency settings properly, as detailed in the previous section, you will notice that the Monthly Balance for November is now displayed in Red. If this does not occur, please review Step #2.

Step #4: Entering an IF Function:

In a row below the Monthly Balance row, we will be creating a series of functions that will indicate whether the current balance is "OK", or if it is "Overdrawn". To do this, we will be learning about the IF function.
First, select cell B18 by clicking on it.
Select the Formulas tab at the top of the screen, then, click on Insert Function.
Scroll through the available functions until you find the IF function. Select it and Click OK.
A window will open displaying the requirements of the IF function. These are called the arguments to the function.
- Specify the Logical Test as B17>=0
- In the If_True Box type "OK"
- In the If_False Box type "Overdrawn"
- The Function Arguments window should look like this:
- Click OK.
You can also insert an IF function manually using the formula bar by typing "IF" and then, in the following parenthesis, naming your Logical Test, the value if true, and the value if false, all seperated by commas. The result will look like this:

This function is identical to the one above
Just as with previous formulas, use Fill to replicate the IF function across the row, from B18:E18.
Notice rows 17 and 18 in particular. You should see a negative value in red in cell D17. Just below that you should see the word "Overdrawn". All of the other balances are fine, so the word "OK" appears beneath them. This results from the IF function.

Now that you have finished editing your spreadsheet, it should look something like this:

Exercise #3

Spreadsheet Exercise Index Page