Excel Tutorial Part 4

Getting Started

It is very important that you enter data in the exact cells that are described in this exercise.

Entering Data and Automatic Copying

Enter the word "Year" into cell B4. Notice that the cursor appears as a white, block plus sign. Move the cursor into the lower right hand corner of cell B4 until the cursor turns into a black plus sign. Holding the mouse button down, drag the black plus sign horizontally across the next 4 columns (C4:F4)*. This action copies the contents of the initial cell into the subsequently highlighted cells. 

*C4:F4 refers to cells C4 through F4. Because you can write formulas with Excel, the - sign is used to indicate subtraction and not thru.

Enter the word "January" into cell A6. Position the cursor into the lower right hand corner of cell A6 until a black plus sign appears. Drag the black plus sign down 11 rows, through cell A17. Notice how the months of the calendar year are automatically entered into each of these cells. This function also works on days of the week too!

Enter the years 1994 through 1998 into cells B5:F5.

Adjusting the Column Width and Row Height

Notice that the month of September slightly extends past the right-hand boundary of the cell that it appears in. Even if the text that you enter into a cell is not displayed to you on the monitor, it will still be printed. You can use the window on the formula toolbar to edit the contents of a cell that contains too many characters to display by clicking inside the cell.

You can also change the display of the worksheet by increasing the width of the column. Place the cursor on the line between the column labels A and B (on the top section). Notice that the cursor turns into a double-headed arrow with a vertical bar through the center of it. This indicates that you can manually adjust the width of a column or the height of row. Holding the double-headed arrow down, drag the mouse to the right. The width of the column will be adjusted. You can also adjust the height of rows by placing the cursor between two of the numerically labeled rows and dragging the arrow up or down.

Your worksheet should now look like this:

Next, enter the following data into table beginning with cell B6.

Formatting Cells

Because the data in the cells represent currency, we need to change the formatting of the cells to reflect this.

• Highlight only the cells that contain monetary values (the columns under the years)
• Click on the Currency Style Icon

These values should now be expressed as currency.

Summation

If you have a column or row of data that you would like to total or sum, Excel will do it for you automatically.  There is no need to get out your calculator!

Enter the word, "Total" into cell A19. This row will display the totals for each of the years 1994 – 1998.

• Highlight cells B6:B19
• Click the Auto Sum Icon
• Repeat this procedure for each of the years
• Sum the row of Totals by highlighting them (highlight horizontally) and clicking the Auto Sum Icon. If you see a series of pound signs (#) in the cell instead of numbers, don't worry! This means that the number is too big to display in the cell; you should make the cell wider as described earlier in this tutorial.

Your worksheet should now look like this:

Formulas

Microsoft Excel will allow you to create formulas to use with any of the cells that contain data. These are the basic formula operators:

• + addition; example: B6+B7+C8
• - subtraction; example: A5–A8
• * multiplication; example: D1*D8
• / division; example: D8/A3

Examples of formulas using multiple operators:

• A7+B7/C7
• (A7*B7)/C7
• D1*D2*D3*D4/D5

You can now create a formula to compute the average expenditures for each month across all years.  To do this:

• Enter the words, "Yearly Total" into cell G4. You may want to adjust the column width so that you can view both words.
• Place the cursor into cell G6
• Type =average(B6:F6) into the window on the formula toolbar and Press Return
• The average expenditure for the month of January across all years has been automatically computed
• Repeat this process through row 12. Be sure and change the cells comprising the formula for each row.

Go to row G19, perform the following operation to compute the Yearly Total:

• Drag and highlight from G19 across the rows until you get to B19
• Click on the the Auto Sum

Your worksheet should now look like this:

% of the Total

In order to find out what percentage of the total ($4,665.00) each year contributes, we can write a formula using each of the cells in row 19, where % of the total = Cell/G19.

• Enter the text, "% of Total" into cell A20
• With the cursor in cell B20 type =B19/G19 and move to the next cell
• Continue this procedure for cells C20 through F20
• For cell G20, type =G19/G19. The quotient should be 1.

Notice that the quotients in these cells are in decimal format and not percentage format. To change the quotients to percentages:

• Highlight the row of quotients
• Click the Percent Style icon on the toolbar

Your table should now look like this:

Inserting a Row or Column

Suppose that we want to go back and add our projected expenditures for the year 1999. In order to do this we must insert a column between columns F and G.

• Click on the G column (the entire column should be highlighted)
• Go to INSERT | COLUMNS

A new column is placed to the left of the column that you had highlighted (G). You can also add rows using this same procedure, only you will need to choose INSERT | ROW.

• Copy the word "Year" into cell G4 by copying the contents of cell F4 into it
• Enter "1999" into cell G5
• Enter the following data into cells G6:G17

• Highlight this Column and use the Auto Sum icon to get the Sum of this Column.
• Compute the percentage for cell G20 using the formula =G19/H19

You worksheet should now look like this:

Congratulations!!! You've Finished

Back

---