2.3 Tutorial

Trhea

Description

Burlington-Edison Technology Competencies Excel Skills Tutor Excel • Open Excel • Use menus and toolbars • Input and format data • Sort and analyze data • Create graphs • Use formulas and functions • Insert graphs in a Word Document • Use HELP menu to gain information 1Burlington-Edison Technology Competencies Excel Skills Tutor Open Excel 1. Click on the Start button located on the Task Bar 2. Highlight “Programs” 3. Click on “Microsoft Excel” Quick Launch Area Tip: You may use a shortcut to open Excel. You might have a shortcut on your desktop or in your quick launch area 2ƒƒBurlington-Edison Technology Competencies Excel Skills Tutor Use Menu and Toolbars 1. Check your toolbars to make sure they are set up properly and will follow these instructions: First go to Tools, Options, and View tab. In the Show area, make sure that both the Formula bar and Status bar boxes are checked. Then click on OK. Second, go to Tools, then Customize, and Options tab. In the Personalized Menus and Toolbars area, make sure that the Standard and Formatting toolbars on two rows is checked and Always show full menus is checked as well. Then click on Close. Basics 2. Identify basic components: A new Excel document is called a workbook. Cell Address Formula Bar Highlighted cell is the selected cell. Cells Rows Begin with numbers. Columns ...

Informations

• Publié par: Pheyn
• Nombre de lectures: 43
• Langue: English

Extrait

          Excel

 

•  Open Excel •  Use menus and toolbars •  Input and format data • Sort and analyze data   •  Create graphs •  Use formulas and functions •  Insert graphs in a Word Document •  Use HELP menu to gain information                  

Burlington-Edison Technology Competencies Excel Skills Tutor

1

Burlington-Edison Technology Competencies Excel Skills Tutor

    Open Excel 1. Click on the Start button located on the Task Bar 2. Highlight “Programs” 3. Click on “Microsoft Excel”

Quick Launch Area

                          Tip: You may use a shortcut to open Excel. You might have a shortcut on your desktop or in your quick launch area                          

 

2

Burlington-Edison Technology Competencies Excel Skills Tutor

  Use Menu and Toolbars   1.   Check your toolbars to make sure they are set up properly and will follow these instructions:   ƒ   First go to Tools, Options, and View tab. In the Show area, make sure that both the Formula bar and Status bar boxes are checked. Then click on OK. ƒ   Second, go to Tools, then Customize, and Options tab. In the Personalized Menus and Toolbars area, make sure that the Standard and Formatting toolbars on two rows is checked and Always show full menus is checked as well. Then click on Close.   Basics     2.   Identify basic components: A new Excel document is called a workbook.

                       

 

Cell Address Highlighted cell is the selected cell. Rows Begin with numbers.

Worksheet Tabs

Columns begin with letters.

Formula Bar Cells

3

Burlington-Edison Technology Competencies Excel Skills Tutor

  A Quick Tour     3.   This little tour takes you to the four corners of a Sheet in an Excel Workbook. Starting in cell A1, hold the Ctrl key down and press the Right Arrow key. This takes you to cell IV1 in Column IV. That means there are 256 columns across. Now hold the Ctrl key down and press the Down Arrow key. This takes you to cell IV65536. This means that there are 65,536 rows down the worksheet making the total number of cells 16,777,216. And that’s only on Sheet 1! There are just as many cells on every sheet that a workbook contains. Now hold the Ctrl key down and press the Left Arrow key. This takes you to cell A65536. And then holding the Ctrl key down and pressing the Up Arrow key or just pressing the Home key will take you back to cell A1. Pressing the Home key no matter what cell is selected will take you back to cell A1.   The Three Little Cursors     4.   There are three different cursors in the cell area of an Excel sheet, each with a different function. The first cursor is the Fat White Cross or Plus sign. This is the Selecting Cursor . Clicking on a single cell will select an individual cell. Clicking and dragging on many cells will select multiple cells. If you want to select multiple cells non- adjacent to each other, click on the first cell or cells, then hold the Ctrl key down and click on the next cell or cells. As long as you hold the Ctrl key down, you may continue to select multiple cells that are not next to each other.   The second cursor is the Skinny Black Cross or Plus sign. If you look at the bottom

right corner of a selected cell, you will see a small black square.     Placing the Fat White Cross on this small black square will change the cursor to the Skinny Black Cross. This is the Fill Down or Fill Right Cursor . Placing the cursor on this box and clicking and dragging will fill a formula or a sequence to succeeding cells below, up left or to the right of the selected cell or cells. See examples:   Example : To fill a formula down, select cell C1 and type: =A1+B1 then press Enter. Select cell C1 again and place the Fat White Cross on the small black square so that it changes into the Skinny Black Cross. Click and Drag down to cell C5. The formula will be filled in those cells but the cell reference will change. Click on cell C2. It will read =A2+B2. Click on C3. It will read =A3+B3, and so on. This is known as Relative Reference . More on this later.          

 

4

 

   

 

Burlington-Edison Technology Competencies Excel Skills Tutor Example : To fill a sequence across, select cell A1 and type: Mon then Tab over to cell B1 and type: Tue then Tab over to cell C1 and type: Wed and hit the Tab key one more time. Select cells A1, B1 and C1. Place the Fat White Cross on the small black square so that it changes into the Skinny Black Cross. Click and Drag to cell G1. The rest of the days of the week will be filled into those cells. You must select all of the cells that begin your sequence before you use the Fill cursor. This will not work with all sequences but it will with a number of them. Try even numbers or try the example below but click and drag farther across the columns. Experiment!

            The third cursor is the White Arrow . This is the Moving Cursor . Placing the Fat White Cross on the border of a selected cell or group of cells will change it into the Moving Cursor. The Moving Cursor allows you to move the contents or formulas of a cell or group of cells to another desired location. Select the desired cell or group of cells. Place the Fat White Cross on the border of the selected cell or cells. Click and Drag the cell or cells to the desired location. See example:      

                           

Step 1

Step 2

Step 3

5

 

   

 

Burlington-Edison Technology Competencies Excel Skills Tutor

Use formulas and functions   5.   Formulas always begin with an equal sign. Formulas can be entered in a number of ways. Here are just a few.   ƒ   Formulas can be entered into a cell by first selecting the desired cell and typing in the formula. After the formula has been entered you must press enter or one of the arrow keys to actually “place” the formula into the cell.   Example : Click cell C1 and type: =A1+B1 Then press enter. The formula is now in cell C1. To test it, type in different values for cells A1 & B1, pressing enter or one of the arrow keys to enter the number into the cell. The value in cell C1 will change accordingly.   ƒ   Formulas can be entered into a cell by first selecting the desired cell, typing in an equal sign, and then clicking one cell at a time with the desired operator between each cell address.   Example:  Select cell A1 and type 1 then Enter. In cell A2 type 2 then Enter. In cell A3 type 3 then Enter. In cell A4 type an = sign. Then with your mouse click on cell A1, then type a + sign, click in cell A2, then type a + sign, and finally click in cell A3 and press Enter. The sum will be placed into cell A4. Changing the values of cells A1, A2 or A3 will change the value shown in cell A4.

              ƒ   Formulas can also be entered into a cell by Clicking and Dragging cells. To do this you first select the cell you want the formula in and then type:   sum( =   then click and drag the desired cells. You may also choose non-adjacent cells by holding down the Ctrl key before you click or drag additional cells.    

6

 

 

 

 

Burlington-Edison Technology Competencies Excel Skills Tutor Example: Enter the following data into cells A1 through A5. See picture below:

 

Select cell A6 and type: =sum(     Then click and drag cells A1 through A3, then let go of the Left Click button. Then hold the Ctrl key down and then click on cell A5. Then type a left parentheses symbol. “ ) “ Your screen should look like the picture below:  

  Then press Enter. You should have what is on the picture below.   ƒ   Obviously formulas other than addition can be used. Average can also be done with the above examples by typing in AVERAGE instead of SUM in the formula bar. Example : =AVERAGE(A1:A5)  would give you an average of cells A1 through A5. ƒ   Use the proper operator with the correct order of operations. The parentheses symbols will help you accomplish this. When entering multiplication or division operators you

must use the asterisk key (*) for multiplication and the forward slash key (/) for division. Also, please remember that there is a big difference between a zero (0) and the letter O. Make sure that you use a zero in your formulas or you will get a syntax error! (In Microsoft XP, the f x key is part of the Standard Menu bar. It is located to the left of the formula bar at the top of the workbook.  

7

 

   

 

 

Burlington-Edison Technology Competencies Excel Skills Tutor

        The sum key has a carrot drop down menu that does a similar task to the f x key. At the bottom of the menu is More Functions. This will bring up the same window as clicking on the f x key does.)   ƒ Excel allows you to enter many other formulas using the f x or the Paste Function key.   Such formulas as Average, Count If, Minimum and Maximum as well as many others can be found there. See example.         Example: Enter the following data into cells A1 through A5. See picture below:

Select cell A6, then click on the Paste Function key ( f x ) on the Standard menu bar at the top of the page. In the Function Category list, select All. In the Function Name list, scroll down and select AVERAGE. Then click OK. (Later, when you use the paste function key the functions you have used most recently will be under Most Recently Used in the Function Category list.) This brings up a dialog box that says AVERAGE at the top. (See Figure 1 )

Colla se Box

Click on OK and the average of the above cells is placed in cell A6. (See Figure 2 )     Figure 1 Figure 2  

8

the end of line Number 1 . The AVERAGE dialog box will collapse down to a single line. Hold the Ctrl key down and click and drag cells A2 to A3. Keep holding the Ctrl key down and click on cell A5. You should have something like the picture below:   Now click on the collapse box again. When the AVERAGE dialog box appears, click on OK. The average of just those three selected cells is now in cell B7.   When doing scientific experiments, multiple trials should always be performed to allow for uncontrolled variables that exist when doing the experiment. Using the average function on a spreadsheet gives a more accurate representation of the data than any single measurement alone. It also allows similar data to be combined for comparison.   ƒ   One other cool thing about Excel is on the Status bar at the bottom of the Workbook, just above the Taskbar. If you select two or more cells with numerical data in them, you can Right-click anywhere on the Status bar and it will give you a number of options. Those options include Average, Count, Count Nums, Max, Min, and Sum without having to enter a formula. See picture below:         Status Bar

Burlington-Edison Technology Competencies Excel Skills Tutor     This procedure works for all cells that contain data that are directly above the cell you are pasting the function in. If you want to select non-adjacent cells then you need to click on the Collapse Box which is on the dialog box at the end of each line (see Figure 1 above). Clicking on the collapse box collapses the dialog box and allows you to select non-adjacent cells by holding the Ctrl key down before clicking individual cells and/or clicking and dragging multiple cells. See example.   Example: Use the same data as in the above example. Select cell B7. Click the Paste Function key ( f x ). In the Function Category list, select Most Recently Used if it is not already selected. In the Function Name list, select AVERAGE, then click OK. When the AVERAGE dialog box appears, click on the collapse box at

 

un .-bem sust ih n  oe thatSt Bus w  ra  llinirbpu g 9 iRhg-tlcciikgn

Burlington-Edison Technology Competencies Excel Skills Tutor

                                Merge Cells     6.   You can also take two or more selected, adjacent cells and merge them into one large cell. Select the desired  group of adjacent cells. Then on the Formatting Menu Bar, select the Merge and Center icon. See picture  below.                                                      

 

10

Burlington-Edison Technology Competencies Excel Skills Tutor

  Create graphs   To create graphs, first make certain that you have data entered on your worksheet. Hi-light the data you want to use for your graph:       Note: Not everything is hi-lighted, only the categories and the numbers. Do not hi-light your title or totals if you have them, it will mess up your graph.                     Next, find the chart wizard button on the menu bar and click it:    

   

 

11