Excel intro



Course Topics:

I. Downloading Roster in Excel Format

II. Excel Overview

III. Entering and Editing Text and Numbers

IV. Moving and Manipulating Rows and Columns

V. Formatting Worksheets

VI. Moving and Copying Data

VII. Using Formulas and Functions

VIII. Previewing and Printing Workbook

I. Download Class Roster

1. Go to El Camino College web portal - portal.elcamino.edu.

2. Type in your LoginID and password then click the Login button.

3. On the left side of the screen you will see the link to download class roster in excel format, click the link, then save your class roster to your computer.

II. Excel Overview

What is Microsoft Excel?

Microsoft Excel is a spreadsheet program, which means that it is primarily used to create and edit numbers and text in cells. A cell is the intersection of a column and a row and can contain a limited amount of characters.

A spreadsheet used to be a large sheet of grid-lined paper that spread across a desk; it was used in accounting to keep columns of numbers lined up. When computerized spreadsheets were developed, this grid structure was kept intact.

Computerized spreadsheets have many advantages over the old paper spreadsheets. Formatting is much easier, and Excel can perform calculations on spreadsheet data that would be impossible on a paper sheet! Spreadsheets are contained in a file called a workbook. Microsoft Excel includes many helpful features to enhance the text and layout of spreadsheets.

Open MS Excel

1. Start Microsoft Excel (Start >All Programs > Microsoft Office > Microsoft Excel).

A blank workbook called “Book1” automatically opens. Look over the interface. Click the Close Window button to close it.

2. Open your roster or the file called “downloadroster.xls” (File > 0pen).

The Excel 2003 Interface

Listed above are some of the areas of the Excel 2003 Interface. We will work with these areas during the training.

Using Columns and Rows

Data is entered in columns (vertical) and rows (horizontal). Columns are lettered A-Z (then AA-AZ, BA-BZ, and so on, through column IV). Rows are numbered. There are 256 columns and 65,536 rows in every spreadsheet. That’s a total of more than 16 million cells per sheet! A cell is the intersection of a column and a row. (Example, cell A1 is at the intersection of column A, row 1.)

Scrolling Through the Spreadsheet

Clicking on a cell selects that cell. When a cell is selected, the Name Box indicates which cell is active, and the formula bar displays the contents of that cell. Note that the formula may be different than what is displaying in the cell when you look at the spreadsheet.

[pic]

name box formula bar

III. Entering and Editing Text and Numbers

Entering and editing text is accomplished by double-clicking the mouse pointer over the desired place and clicking a fine area in the cell called the “insertion point.” This is the point at which text will begin to be entered, a selection will begin, or a graphic or other file will be inserted. The mouse arrow changes to a text selection pointer called the “I-Beam” pointer (because it looks like a capital I).

1. Click on cell A2. The Name Box indicates that A2 is the active cell. Click in another location and the Name Box changes.

2. To select multiple cells, click in the middle of one cell, then drag-select the desired range of cells using the large white cross pointer (().

3. Scroll using the scroll bar arrows on the right side of the screen. Note that as you scroll, the active cell does not change. Also try scrolling by clicking above or below the scroll button, as well as dragging and dropping the scroll button itself. Use the horizontal scroll bar to view additional columns; use the vertical scroll bar to view additional rows.

4. To select cells without using the mouse:

a. Press the arrow keys on the keyboard to scroll one row at a time (up or down), or one column at a time (right or left). The keyboard arrows are located to the right of and below the Enter key.

b. The enter key moves down one cell, the tab key moves to the right.

c. Also use the Page Up and Page Down keys, above the arrow keys.

d. Press the Home key to move to the beginning of a row.

e. Press the End key and any arrow key to move to the next major row or column group which contains data.

f. Press Ctrl+Home to jump to the first active cell in the spreadsheet.

g. Press Ctrl+End to jump to the last active cell in the spreadsheet.

Note that all keyboard movements change the active cell.

Changing the Zoom Display

Use the Zoom Box to change the magnification of the data on the screen. The Zoom Box only changes the view of what is on the screen; it does not change the printed worksheet or active cell.

|1. Click the down arrow next to the Zoom Box and select 50%. |[pic] |

|The spreadsheet zooms out. | |

2. Click again on the down arrow and change the zoom to 200%.

The spreadsheet zooms in on the active cell.

3. Click inside the Zoom Box, type an amount of 73, then press Enter.

The spreadsheet zooms to a custom zoom of 73%.

4. Reset the zoom to 100% to return the spreadsheet to its original size.

Navigating to Other Worksheets

A workbook can contain an unlimited number of worksheets. The default number of worksheets in a new workbook is 3. (Default can be changed.)

|1. Click the Sheet2 tab at the bottom of the window. | |

| |[pic] |

|Sheet2 displays, with cell A1 active. Click on cell B8. | |

2. Click the Sheet3 tab.

Sheet3 displays, with cell A1 active.

3. Click on the Sheet2 tab. Note that cell B8 is still the active cell.

4. Click on the roster tab. Note that the active cell is still active. Excel tracks the active cell in each worksheet until the workbook is closed, at which time all sheets return to the active cell when the workbook was last saved.

Changing the Name of Sheets

1. Right-click on Sheet2 and choose Rename.

2. Change the name of Sheet2 to Formulas and press Enter.

Sheet2 has now been renamed to Formulas.

Deleting Sheets

1. Right-click the Sheet3 tab and choose Delete.

A dialog box displays warning that the entire sheet and all its contents will be deleted.

2. Click OK to delete the sheet.

Inserting Sheets

1. Choose Insert > Worksheet.

2. Return to the roster sheet and Save the file with your first initial and last name (File > Save AS > lmotley.xls).

Closing a Workbook

Click the workbook’s close window button (the lower [pic] icon) to close it. Or choose File > Close. The screen shows a blank, gray application screen.

Using Shortcuts When Entering Data

1. Open “YOURROSTER.xls” (File > Open).

2. When entering data across the columns, press the right arrow key instead of Enter to move to the next column. Use the other arrow keys to move in any other desired direction.

3. To accept the entry in a cell without moving the active cell, or “cell designator”, click the green check mark next to the Edit Line on the Formula Bar (it is active only as you enter data in the cell).

[pic] (X) is cancel, (α) is enter/accept, and ([pic]) is edit formula.

Editing Text

1. Directly in the Cell

a. Click on cell C1 by clicking in the cell, type your Course Name and Number, and click the green check mark next to the Edit Line to accept the contents without moving to a new cell.

b. Click on cell D1, type your Last Name, and press Enter.

c. Select cell D1 and press the Delete key, or Edit>Undo on the menu bar. (If you had not pressed Enter, the Escape Key would undo.)

The cell’s contents are deleted and cleared to a blank cell.

d. Select cell D1 and begin typing your campus phone number. The new entry wipes out the old entry. Before you finish, click the red “X” next to the Edit Line. (This does the same thing as the Esc key.)

The cell’s contents are returned to the previous entry.

IV. Navigating Spreadsheets in Excel

Adding a Heading to a Column

1. Click on H2, type Test 1, and press Enter.

2. Select I2, type Mid-Term, and press the Tab key.

3. In J2, type Final, and press the Tab key.

4. In K2, type Overall Grade, then press Tab key.

5. In L2, type Average, then press Tab key.

6. In M2, type Letter Grade, then press Tab key.

7. In N2, type Weighted Grade, then press Tab Key.

Note that Shift+Tab will move the cursor backwards.

Widening Columns

1. By Dragging

a. Place the mouse pointer between the column A and column B headings at the top of the window. [pic]

The pointer changes to a double-headed horizontal arrow (().

b. Click-and-drag the column heading line until the width pop-up box above the pointer reads “24.00”. This is the number of characters that can be contained in a column of that width. Release the mouse button to accept the width of 24.00.

2. By Using AutoFit

AutoFit is a feature that automatically sizes a column to fit the longest string of text in that column. To use AutoFit, simply double-click the line between column headings after the cursor changes to the double-headed horizontal arrow.

There are two important notes about using AutoFit:

a. AutoFit does not adjust columns if any additional text is typed into the column after you applied it. If additional text does not fit in the current column width, you must AutoFit again to adjust to the new text.

b. Sometimes, you do not want to size a column according to the longest item in it. If AutoFit results in a column wider than you actually intended, manually resize the column to your own preference.

c. Use AutoFit to adjust the columns in your workbook.

Inserting/Deleting Rows

1. To add a row, Click on cell A7.

2. Choose Insert > Rows to insert a new blank row above. The previous row moves down to row 8.

3. To Delete a row, column or cell first select it and choosing Edit > Delete or Right-click.

4. Click on cell A7, Right-click, then choose Delete > Entire Row to remove the new row.

NOTE: Inserting Rows/Columns does not destroy any active formulas!

Adding Grades

1. Click on H3 and type 95, click the down arrow.

2. Select cell H4 and type 84, then click the down arrow.

3. Click on H5 and type 70.

4. In cell I3 type 100, click the down arrow.

5. In cell I4 type 77, then click the down arrow.

6. In cell I5 type 100.

7. Click cell J3 and type 79, then click the down arrow.

8. Click cell J4 and type 100, then click the down arrow.

9. Click cell J5 and type 67.

10. Save (File > Save or [pic] )the file.

V. Formatting Worksheets and Cells

To give data or text a consistent look, we can use a collection of formats such as font, alignment, patterns, and underlines, and apply this group of formats to a range of data as a style. Other times, we want to apply a format that will make it clear that the number is a currency value.

Changing the Font

1. Click on the “roster” Worksheet. Click on Row 2 to select the entire Heading row.

2. Click the Font down arrow on the Formatting Toolbar and change the font to Georgia.

3. Then click the Font Size down arrow and change the size of the text to 12.

4. Click the drop-down arrow next to the Font Color and choose Red to change the color of the text.

5. Click the Bold button to bold the text.

6. Click the Italic button to italicize the text.

7. Click away from the selection to view the results.

8. Adjust the column width to fit the text, if necessary.

Aligning the Text

1. Select Column M (Letter Grade).

2. Click the Center button [pic] on the Formatting Toolbar to center the text in the column.

3. Select the entire row 2 and center the text in the column.

To keep the numbers readable when centered, choose Format > Cells. Then, in the Category of Numbers, choose “Decimal places” of “2”.

Merging and Centering Text

1. Drag-select cells A1:B1. Click the Merge and Center button [pic] on the Formatting Toolbar.

The two cells are merged to one and the text is centered in the new large cell. There is no longer a B1.

2. Double-click to select A1, type INSTRUCTOR: “Your First and Last Name” in the merged cell, then press Enter or (α) to accept.

3. Apply formatting to the text in A1 and C1.

4. Text can also be centered using a formatting technique known as “center across selection” by choosing format > cells> alignment > horizontal alignment…, rather than the “merge and center” function. In this case, cells A1, B1, or C1 still exist.

5. Save the workbook.

VII. Using Formulas and Functions

Entering a Formula

To create a formula that will allow for future changes, use a cell reference to prepare the formula. A cell reference can refer to one or more cells.

IMPORTANT: You must enter an equal sign (=) to begin a formula. You must follow the rules of the “order of operations” when writing formulas:

“(3 + 1)/2” is not the same as “3 + 1 / 2”

Typing a Cell Range

A range is a group of two or more adjacent cells. Ranges are entered in this way: “B7:B9”. The colon is a mathematical substitute for the word “through” in a formula. Ranges are often used in coordination with functions (sum, average, count, etc.).

1. Click on cell K3.

2. Type =SUM(H3:J3) and press Enter.

The formula value of 274 displays in cell K5.

4. Delete the contents of cell K3 to prepare for the AutoSum feature.

Using AutoSum

AutoSum automatically calculates the totals of cells, without using the keyboard, based on certain rules:

□ AutoSum will add figures to the left of the active cell until it hits a blank column, or

□ If no figures are to the left, AutoSum will add figures above the active cell until it hits a blank row.

|1. From cell K3, click the AutoSum button [pic] on the Standard Toolbar. | |

|The range H3:J3 is selected and displays in a flashing dashed line, known as the “line of |[pic] |

|marching ants.” | |

|This selection can be pulled up or down to include or exclude cells, thus creating a new | |

|selection of cells. | |

2. Click the AutoSum button again (or Enter, or α) to accept.

The same formula you typed in: =SUM(H3:J3) is automatically entered in the Edit Line.

3. Select K4 and click the AutoSum button to enter the sum of H4:J4, click the AutoSum button again to accept. K4 should now have the sum of 261.

4. Select K5 and click the AutoSum button. Note that the cells H5:J5 are automatically selected. K5 should now have the sum of 237.

Using AutoCalculate

AutoCalculate is used to perform “quick reference” functions on selected cells, but without entering any formulas into the body of the spreadsheet.

It is found in the status bar, and contains the 6 primary functions used in Excel formulas:

Sum: adds the values for a total

Average: adds the values and divides by the number of items to obtain an average

Max: displays the maximum (largest) value in the selected cells

Min: displays the minimum (smallest) value in the selected cells

Count: displays the total number of cells with active data

Count Nums: displays the total number of non-text active data

|1. Drag-select cells H3:H5 and view the result in the AutoCalculate area (right side of the Status |[pic] |

|Bar at the bottom of the screen). The area currently shows, Sum=249. |[pic] |

|2. Right-click the AutoCalculate area and choose “Average”. | |

|The area shows, “Average=83”. | |

|3. Right-click the area again and choose “Min”. | |

|The area shows, “Min=70”. | |

|Right-click the area again and choose “Max”. | |

|The area shows, “Max=95”. | |

Using Insert Function

The Insert Function feature is used to get step-by-step help with choosing a function and creating a formula.

Note that if you do not see the Insert Function icon [pic] you will need to use the drop-down arrow [pic] at the end of the menu to add the button. Click the drop-down arrow and the menu expands for more options. From this menu select Add or Remove Buttons > Customize. The Customize dialog window opens. Under the Commands tab select Insert > Insert Function then Drag the Insert Function button onto the toolbar. The [pic] button is now located on your standard toolbar.

1. Click on cell L3, then click the Insert Function button [pic] on the Standard Toolbar. The Insert Function dialog box opens.

[pic]

2. Choose “Average” at the bottom of the dialog box, then click the OK button.

The Average Formula Palette displays showing the range H3:K3 in its proposed formula.

[pic]

4. Click the Collapse button [pic] on the Formula Palette.

The palette collapses just under the Formula Bar on the spreadsheet to allow for better viewing of the cells on the sheet.

5. Drag-select cells H3:J3 (the “line of marching ants” should go around those cells).

6. Click the Expand button [pic].

The Formula Palette displays again in full. The correct range of cells is now shown in the formula.

7. Click OK to accept the formula.

The Formula Palette collapses into the Edit Line, and the results of the formula (“91.333333333”) are shown in cell L3.

7. Click on cell L3, right-click, then select copy.

8. Click on cell L4, right-click, then select paste to insert the formula into this cell to calculate the average for the student (“87”)

9. Click on cell L5 right-click, then select paste to insert the formula into this cell to calculate the average for the student (“79”)

10. Save the file.

Assigning a Letter Grade

To assign a letter grade based on the student’s total sum, you will need to use the IF formula:

=IF(L3 Print Preview on the toolbar.

The Print Preview screen displays the spreadsheet in full page view as it will appear when it prints. Note that the standard toolbar changes to a preview toolbar.

2. Move the mouse over the page to display the Zoom Pointer (magnifying glass). Click anywhere on the page to zoom to 100% view of that portion of the page.

The Zoom Pointer changes to a normal cursor.

2. Click again to zoom back out to full page view.

Adjusting the Setup

To make the spreadsheet fit properly on one page, it may be necessary to adjust the setup.

1. With the Print Preview active, click the Setup button on the Toolbar.

Note the print setup features are different for each printer.

2. If necessary, click the Properties button and the Page Setup dialog box displays. Click the Layout tab.

3. Change the page orientation to Landscape and select OK.

4. Click the Close button to close the Page Setup dialog box and return to the Print Menu, then select Preview to view the results of these changes.

Setting Print Areas

Sometimes, you want to tell Excel to print only a certain range of cells.

1. Click on the “roster” sheet and select Row 4.

|2. Choose File>Print Area>Set Print Area. |

|The selected cells are set as the only area to be printed. |

|A “fence” around the cells may appear around the area selected to print. |

|Click the Print Preview button ( [pic] ) and view the results of setting the print area. Close the Print dialog box. |

Clearing Print Areas

1. Click anywhere in the spreadsheet.

2. Choose File > Print Area > Clear Print area.

The print area is cleared.

Print Using the Print Dialog Box

Rather than clicking the Print button on the Standard Toolbar, use the File>Print method. This forces the Print dialog box to display, so that you can select options other than the default options for printing.

1. Choose File > Print.

2. Most of these options you are used to seeing in other programs, but there is one that results in choices that are unique to spreadsheets :

3. Click in the “Print what” section to change what will be printed.

[pic]

If you have selected a range of cells and choose “Selection,” only the selected cells will print. This option saves you from having to set a new print area for a one-time change.

If you have activated multiple worksheet tabs, you can print only those sheets by selecting “Active sheets.” You can print all sheets by selecting “Entire workbook.” Click Cancel and close the Print dialog box.

4. Save (File > Save or [pic] ) the file.

Grade and Recordkeeping Using MS Excel

[pic]

LaTonya Motley

Trainer/ Instructional Technology Specialist

Staff Development

660-6452

-----------------------

AutoSum

Shading

Borders

Name Box

Formula Bar

Cell

Column

Row

Worksheets

Fill Handle

Active Cell

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download