MY EXCEL BUDGET
Calculating totals, adding charts
Part 2

A WebTask for Adult Learners

Ask for help getting started. Continue to ask for help when you need it! 

In this task, you will complete the following objectives:
  • Open and existing file and rename it
  • Substitute the amounts in the original files, adding decimals
  • Change the decimal options
  • Enter formulas to automatically add totals
  • Extend the formulas
  • Enter Average formulas and extend them
  • Add a new row with new information
    Complete a reading challenge
  • Create a pie chart with your information
  • Create your own budget for four months, adding average and totals formulas, and a pie chart

You will evaluate yourself (compete rubrics) at the end. Click on the arrow to read the rubric (evaluation) items at the end. 
(To come back here, click the BACK button on your browser.)


In the first Web Task in the Excel series, you learned how to open the program, use the common menu options and shortcuts, enter and format text in cells, and change cell sizes. 

You entered the following information in the first part. 

  January February March
Rent 303.00 303.00 303.00
Car Payment 249.00 249.00 249.00
Gas Bill 75.00 92.00 87.00
Electricity Bill 45.00 32.00 40.00
Water Bill 20.00 20.00 28.00

To begin this exercise, we are going to add decimals to the numbers in each item and learn to work with them. 

Activity 1: In the Excel sheet that you saved from your first Task, substitute the following dollar amounts in each cell.

  January February March
Rent 903.41 903.41 903.41
Mortgage 903.41 903.41 903.41
Car Payment 449.27 449.27 449.27
Gas Bill 125.32 115.67 95.99
Electricity Bill 45.64 22.92 40.22
Water Bill 20.88 20.24 28.89

-->>Save your spreadsheet as "your initials budget2." You now have two Excel Spreadsheets: budget1 and budget2. This Task will be saved as "budget2."

NOTE: If you don't see a button that you want to use, click on the small Down Arrow images you will find on your toolbar. Those show you the hidden options on your toolbar that don't fit the screen.

 

Finding a Sum (Adding)   

Activity 2

Select cell A10. Type the word Total in the cell. Move to cell B10. This cell will show the total amount paid in January for the five categories. 

There are several ways to have Excel calculate the sum (total) of the values (amounts) in cells B3 through B7. One method is to type the following in cell B10: =B3+B4+B5+B6+B7. (Try it, if you want. Then erase the total and use the next way).

However, there is a better method. Select cell B10. Click on the AutoSum button on the Standard Toolbar. The AutoSum button shows an uppercase sigma (see image).

A box appears around cells B3 through B9. Use your mouse to select only cells B3 though B7, then press Enter on your keyboard. Use the automatic copy feature to calculate the sums in the rest of the columns.

Hint: Click on B10 and drag to M10. This copies the formula!

-->>Save your spreadsheet.

 

 

  Finding an Average   

Activity 3

An average shows the total sum of several values (amounts) divided by the number of items included to reach the total. 

For example, imagine that when you were in school, you got six grades in your art class: 97, 89, 91, 100, 94, and 100. To find out your grade average in your class, you would first add all of the values or amounts:

97
89
91
100
94
100
_________

Total:  572

Next, you would divide that total by the number of grades you added.

572 2 = 95.3333 Rounded off to 95. Your average in Art would be 95.

Now we'll let Excel figure our our average expenses.

In cells O3 through O7, you want to find the average paid for each bill for each month. Again, there are several ways to do this. The method we will use is the Paste Function feature. 

Select cell O2. Type Average. Select cell O3

Click on the Insert Function button (Excel XP) or the Paste Function button (Excel 2000).

This action lets you to choose a math function to enter in a cell. Select the function Average from the right-hand column, as shown in the image.

Click OK. A dialog box appears. The automatic setting should be B3:N3, indicating Excel will find the average of cells B3 through N3 (Notice that the colon (:) stands for "through." 

Click in this space and change the N3 to M3 (B3:M3) since we do not want the empty cell N3 to be included in the average.

Click OK. Use the automatic copy feature to place averages in cells O4 through O10.

Column O in your spreadsheet should look like the model at the right 

Note: The information #DIV/0! in O8 and O9 indicates division by 0 is undefined. I would delete those.

-->>Save your spreadsheet.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  Adjusting Decimal Places   

Activity 4

Cells O5 and O10 show more than two decimal places (numbers after the decimal point). To adjust this, select cells O5 through O10 (Select O5; Press the Control Key and click on 010 to select only those two cells). Now click on the Decrease Decimal button on the Formatting Toolbar (See image).

  Inserting Rows & Columns   

Activity 5 

Suppose you want to add another bill to your budget - your MasterCard Bill for instance. There is space in rows 8 and 9 for more data (information); however, to keep a space between the data and the totals, you decide to insert a new row. To do this, select row 8 by clicking on the 8 at the far left of the screen. Select Insert | Rows. A new row is added. 

NOTE: New rows are added above the selected row. New columns are added to the left of a selected column.

Go ahead and add the MasterCard Bill. Oops! The text is too big for the column. How can you make the column fit the text? If you need to review, go back to the your first budget Web Task and practice changing cell widths.

-->> Save your spreadsheet.

  Manipulating Borders   

Activity 6

Before printing a spreadsheet, you may want to place borders around some or all of the cells. (The default (factory/automatic) setting in Excel 2000/XP includes no borders around any cells.)

Highlight cells A1 through O12. Select Format | Cells. Click on the Borders tab. In the dialog box, you can select borders around the entire selected area, around each cell, or only along certain edges.

Place a border around the A1:O12 block, around individual cells. Click OK.

To view how the spreadsheet will look when printed, click on the Print Preview button on the Standard Toolbar. Notice that the border appears only around the outside, as you told it to do . However, that the budget does not fit on a single sheet of paper. To go back to your other view, click on the Close tab.

-->> Save your spreadsheet.

  Fitting Text   

Activity 7

In the Print Preview mode, click on Setup at the top of the screen. The dialog box that appears includes a Scaling section. In this section, you may select "Fit to" to fit your spreadsheet to one page.

Select this feature, and click OK. Your spreadsheet is automatically reduced in size to fit one sheet of paper.

Note: You may prefer Landscape (wider) orientation rather than Portrait (taller).

-->>Save your spreadsheet.

  Using Charts   

Activity 8

Highlight cells A3 through D8. Click on the Chart Wizard button in the Toolbar. Select Pie from the chart options on the left. Click Next until you see a box to name your chart. Name the Chart 2004 Budget and click Next and OK to accept all defaults (basic settings). Click Finish to complete the chart of your budget. Your pie chart appears on your Excel spreadsheet. A picture is worth a thousand words!

-->> Print your spreadsheet, sign it, and place it in your folder.

Activity 9

Click on the arrow to complete the Reading Challenge.

Activity 10

PROJECT: Using all of the tools you learned, create your own budget for four months. Add the totals for each month and print a pie chart to compare amounts. 

Print your budget spreadsheet, sign it, and place it in your folder.

Unlimited Learning, Cortez, CO 2004
Permission granted to use our materials for educational purposed only, as long as credit is given to the source.