Excel Intro Part 1 2007

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

MICROSOFT EXCEL 2007 – INTRO PART 1 Excel is an electronic spreadsheet. An electronic spreadsheet is software that uses a computer to perform numeric calculations rapidly and accurately. An electronic spreadsheet is also referred to as a workbook containing worksheets. The workbook is the document or file that you produce when you use Excel.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

The Excel 2007 Window The Worksheet Window: This area of the window contains a grid of columns and rows. Columns are labeled alphabetically (A, B, C, etc.) and rows are labeled numerically (1, 2, 3, etc.) The intersection of a column and row is referred to as a cell. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

The Active Cell: The dark rectangle highlights the cell you are working in (or the active cell). To move the cell pointer click any other cell with the mouse or use the arrow keys on the keyboard. Title Bar: Displays the program name and filename of the open file. The title bar also contains the office button, the quick access toolbar, the resizing buttons, and the Close Program (X) button.

Ribbon: Contain buttons that provide shortcuts for the most frequently used Excel commands.

Name Box: Displays the active cell address.

Formula Bar: Allows you to enter or edit data in the selected cell.

Sheet Tabs and Scrolling Buttons: Located below the worksheet grid, the sheet tabs allow you to keep your work in collections called workbooks. Each workbook contains 3 worksheets by default. Sheet tab scrolling buttons help you move from one sheet to another. (The default for the number of worksheets can be changed at Office button, Popular, Include this many of sheets)

Status Bar: Located at the bottom of the Excel window, it provides a brief description of the active command or task in progress. This also includes the View toolbar. Here you can change the way you view your document and the zoom on your document.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

Creating an Excel Worksheet Planning a worksheet: Determine the purpose of the worksheet and decide on a meaningful title. Determine the desired results or output you want your worksheet to produce. Collect the information (input) that will produce the results that you want to see. Determine the formulas or calculations necessary to achieve the desired results. It may be helpful to sketch on paper how you want your worksheet to look. Worksheet Project: Review the following data. Then, follow the step-by-step instructions starting on Page 4 to calculate the projected amount to be spent on student payroll for next year. Data: 4 Students - Student A, Student B, Student C, and Student D Projected Payroll Data is as follows: Student A:

Will work 10 hours per week. Can work Fall and Spring semesters – 32 weeks. Will earn $6.10 per hour.

Student B:

Will work 12 1/2 hours per week. Can work Fall semester only – 16 weeks. Will earn $6.25 per hour.

Student C:

Will work 10 hours per week. Can work Fall, Spring and Summer – 40 weeks. Will earn $6.50 per hour.

Student D:

Will work 15 hours per week. Can work Fall and Spring semesters – 32 weeks. Will earn $6.00 per hour.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

Step-by-Step Instructions: Entering Data - Labels 1. In Cell A1 type Projected Student Payroll Expenses for Next Year and press Enter. Notice that the title spreads across several columns. If a label does not fit in a cell, the remaining characters will be displayed in the adjacent cell as long as the adjacent cell is empty. 2. In Cell B2 type Hourly Rate and press the right arrow key. 3. In Cell C2 type Hours per Week and press the right arrow key. The title is too long for the cell but it will spread over to D2. 4. In Cell D2 type Weekly Earnings and press Enter. The entire content of C2 is no longer displayed but the actual contents of the cell have not changed. Click on C2 and look in the formula bar to see the actual contents of a cell. Sizing Columns If you position the cursor on the grey line between the column labels (not in the worksheet window area), the cursor will change (see example below). When you have the mouse positioned properly, click and hold down the left mouse button while dragging the line to the right to change the column size.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

Changing Column Width and Row Height You can use the border between the column or row headings to resize columns and rows

1. Use the method above to make Columns B, C and D wider at this time. Entering More Data - Labels 1. Click in Cell A3 and type Student A, then press Enter 2. In cell A4, type Student B and press Enter 3. In cell A5, type Student C and press Enter 4. In cell A6, type Student D and press Enter Entering Data – Values The following steps will enter the projected numbers for Hourly Rate and Hours per Week and then calculate Weekly Earnings for each Student. Be sure to enter the values exactly as shown below. Do not enter spaces when typing numeric values. 1. Click in Cell B3 2. Type 6.10 and press Enter. (Notice the location of the cell pointer.) 3. In cell B4, type 6.25 and press Enter. 4. In cell B5, type 6.50 and press Enter. 5. In cell B6, type 6.00 and press Enter. (Notice how the numbers display. We will format them later in this exercise.) 6. Click in Cell C3 7. Type 10 and press Enter. 8. In cell C4, type 12.5 and press Enter. 9. In cell C5, type 10 and press Enter. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

10. In cell C6, type 15 and press Enter. Entering Formulas: A formula is a set of instructions that you enter in a cell to perform numeric calculations (adding, multiplying, averaging, etc.); for example, =A1+B1 or =C2*C3. Formulas always start with an equal sign. 1. 2. 3. 4. 5.

Click in Cell D3 (You will use an equal sign = and an asterisk * to perform multiplication.) Type =B3*C3 and press Enter. In cell D4, type =B4*C4 and press Enter. In cell D5, type =B5*C5 and press Enter. In cell D6, type =B6*C6 and press Enter.

Formatting Data Formatting allows you to change the appearance of your data in the worksheet. Follow the steps below to change some of the numeric figures into dollar amounts, and apply style changes to your text characters. 1. Select a range of cells. Click on B3. While holding down the left mouse button, drag to B6. 2. Click the Increase Decimal decimal places.

button once. (All numbers in the range will now display two

3. Click the Currency Style button once. (To add a dollar sign.) Try formatting the numbers in Column D, Range D3 through D6 in the same manner. 4. Select the range of cells A3 through A6. (Click A3 and drag to A6.) 5. Click on the Bold button. 6. Resize Column A to fit the student labels by clicking and dragging the gray line between Columns A and B. 7. Click on Cell A1 8. Click Format on the Menu Bar. 9. Choose Cells… 10. Click on the Font tab. 11. In Font Style choose Bold Italic, Select 14 in the size box. Notice the Preview area will show you what your choices look like. 12. Click OK. 13. Select the range of cells B2 to D2. 14. Click the Center button on the Formatting tool bar. 15. With the same range selected, click the Underline

button.

Inserting Rows (and Columns) 1. Click on Cell A2 For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

2. Click the arrow next to Insert on the home tab in the Ribbon. 3. Click Insert Sheet Rows. A row will be inserted and data will be moved down automatically. (Columns can be added in a similar manner.) Your worksheet should now look similar to the following example:

Saving and Naming a Worksheet: 1. Click the Office Button 2. Click Save As. -OR- Instead of Steps 1 and 2, click on the Save button. 3. Select the correct location for the file to be saved. Always check the Drive and Folder locations so you can locate your file later. 4. Give your worksheet the name Student Payroll Projections. 5. Click Save. The filename should now appear in the Title Bar. Previewing a Worksheet: 1. Click File 2. Click Print Preview. -OR- Click on the Print Preview Please do not print at this time. 3. Click Close to return to the worksheet.

button.

The following steps will have you close the worksheet file and then open it again for editing purposes. To Close a Worksheet File: 1. Click the Office Button. 2. Click Close.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

To Open a Worksheet File: 1. Click the Office Button. 2. Click Open. -OR- Skip steps 1 and 2 and click the Open button. 3. Verify that you are looking in the correct drive and folder. Select the file named Student Payroll Projections. Click Open. Enter Formulas: 1. Select the cell in which you want to enter the formula. In this case, click on Cell D8. 2. Type an equal sign (=) to activate the formula bar. 3. Type the formula exactly as shown below, using the cell addresses (NOT the actual numbers) =D4+D5+D6+D7 and then press Enter 4. The results of the formula ($294.13) will appear in the cell if you have typed it correctly. 5. Click Cell D8 and press the Delete key on the keyboard. The next steps show you a quicker way to add cells together. A shorter method of adding cells together is available with the AutoSum Button. The AutoSum button automatically invokes the SUM function and suggests the range of cells to be added. To use the AutoSum button: 1. Select a cell below or to the right of the range of numbers you want to add. In this case, click Cell D8. 2. Click the AutoSum button on the Standard toolbar. The AutoSum feature will suggest the range of cells to add and surround it with a moving dotted line. (If Excel suggests an incorrect range, click and drag to select the range you want, and press ENTER to accept the completed formula.) 3. Press Enter. Enter Data 1. Click Cell E3 and type # of Weeks and press Enter. 2. Type 32 in cell E4 and press Enter. 3. Type 16 in cell E5 and press Enter. 4. Type 40 in cell E6 and press Enter. 5. Type 32 in cell E7 and press Enter. Entering a Formula and using Copy: 1. Click Cell F3 and type Projected Total. 2. Click Cell F4, type =D4*E4 and press Enter. 3. Click on Cell F4 again. 4. Click the Copy button.

5. Click Cell F5 and click on the Paste button. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

6. Click Cell F6. Click on the Paste button again. Click Cell F7. Click Paste to paste the formula in cell F7. The copy function will copy the formula and adjust the cell references for the new location. Change the contents of Cell E6 to 400. Note that cell F6 may now be filled with #####. This notation indicates that the Column Width is not large enough to display the number. To display the full number, you would have to resize the column. Click Undo. Delete a Range of Cells: 1. Select the Range F5 through F7 2. Press Delete. (You can also use Edit, then Delete on the Menu Bar.) Copy the sum formula using a different method. To copy by dragging the fill handle:

Fill Handle

Select the cell containing the data you want to copy. (Click Cell F4.) Point to the fill handle. (See above.) When you see a +, click and drag the fill handle to F7. Release the mouse button. (Note that any existing values or formulas in the cells you fill (or copy to) will be replaced.) 5. To de-select the range, click any other cell in the worksheet. 1. 2. 3. 4.

Editing Data: There are several ways to edit the contents of a cell in Excel: 1. Click on the Cell you wish to edit, then click in the formula bar and edit the contents. -OR2. Double click on the Cell you wish to change and edit the contents. -OR3. Click on the Cell you wish to Edit and press F2. -OR4. Retype the contents and press Enter. Adding a Header and/or Footer to your Worksheet 1. Click the Insert Tab 2. Click Header & Footer For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

3. From the Footer drop down menu, choose Page 1 as the Footer.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

Finishing Touches: Task: to change all cells in Row 3 from plain text to bolded text. 1. Select the Range you wish to change by clicking on B3 and dragging to F3. 2. Click the Bold button on the formatting toolbar. 3. Resize columns as necessary. Preview and Print (It is not necessary to print in class but you may if you wish.) 1. Click on the Print Preview button 2. If all looks good, click on the Print button 3. A Print window will be displayed, click OK. Other ways of printing: 1. Click the Print button. (This is a ‘quick’ print - you will not have to click OK at the Print Window.) 2. Click on the Office Button, choose Print, and then click OK on the Print Window. Your finished worksheet should look similar to what’s shown below.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com