The basic operations in Excel

Размещено на http://www.allbest.ru/

Microsoft Excel

Microsoft Excel is an application program that is called a spreadsheet. We use this application program to prepare, analyze and present numeric data, to make different mathematical or scientific calculations.

To start Microsoft Excel click START > PROGRAMS > MS Excel. The screen shown here will appear.

The title bar

The Title bar is located at the very top of the screen. On the Title bar, Microsoft Excel displays the name of the workbook you are currently using. At the top of your screen, you should see «Microsoft Excel - Book1» or a similar name.

The Menu Bar

The Menu bar is directly below the Title bar and displays the menu. The menu begins with the word File and continues with the following: Edit, View, Insert, Format, Tools, Data, Window, and Help. You use the menu to give instructions to the software. Point with your mouse to a menu option and click the left mouse button. A drop-down menu will appear. You can now use the left and right arrow keys on your keyboard to move left and right across the Menu bar options. You can use the up and down arrow keys to move up and down the drop-down menu. To select an option, highlight the item on the drop-down menu and press Enter. An ellipse after a menu item signifies additional options; if you select that option, a dialog box will appear.

Do the following exercise, which demonstrates using the Microsoft Excel menu.

1. Point to the word File, which is located on the Menu bar.

2. Click your left mouse button.

3. Press the right arrow key until Help is highlighted.

4. Press the left arrow key until Format is highlighted.

5. Press the down arrow key until Style is highlighted.

6. Press the up arrow key until Cells is highlighted.

7. Press Enter to select the Cells menu option.

8. Point to Cancel and click the left mouse button to close the dialog box.

The Standard Toolbar

The Formatting Toolbar

Toolbars provide shortcuts to menu commands. Toolbars are generally located just below the Menu bar.

Microsoft Excel consists of worksheets. Each worksheet contains columns and rows. The columns are lettered A to IV; the rows are numbered 1 to 65536. The combination of column and row coordinates make up a cell address. For example, the cell located in the upper left corner of the worksheet is cell A1, meaning column A, row 1. Cell E10 is located under column E on row 10. You enter your data into the cells on the worksheet.

The Formula Bar

If the Formula bar is turned on, the cell address displays on the left side of the Formula bar. Cell entries display on the right side of the Formula bar. on. Press the Esc key twice to close the drop-down menu.

The Status Bar

If the Status bar is turned on, it appears at the very bottom of the screen.

Notice the word «Ready» on the Status bar at the lower left side of the screen. The word «Ready» tells you that Excel is in the Ready mode and awaiting your next command. Other indicators appear on the Status bar in the lower right corner of the screen. Here are some examples:

The Num Lock key is a toggle key. Pressing it turns the numeric keypad on and off. You can use the numeric keypad to enter numbers as if you were using a calculator. The letters «NUM» on the Status bar in the lower right corner of the screen indicate that the numeric keypad is on.

Other functions that appear on the Status bar are Scroll Lock and End. Scroll Lock and End are also toggle keys. Pressing the key toggles the function between on and off. Scroll Lock causes the pointer movement key to move the window but not the cell pointer. End allows you to jump around the screen. We will discuss both of these later in more detail.

The Down Arrow Key

You can use the down arrow key to move downward on the screen one cell at a time.

The Right and Left Arrow Keys

You can use the right and left arrow keys to move right or left one cell at a time.

Page Up and Page Down

The Page Up and Page Down keys move the cursor up and down one page at a time.

The End Key

The Status Bar

The End key, used in conjunction with the arrow keys, causes the cursor to move to the far end of the spreadsheet in the direction of the arrow.

Note: If you have entered data into the worksheet, the End key moves you to the end of the data area.

The Home Key

The Home key, used in conjunction with the End key, moves you to cell A1 - or to the beginning of the data area if you have entered data.

Scroll Lock

The Status Bar

Scroll Lock moves the window, but not the cell pointer.

Selecting Cells

If you wish to perform a function on a group of cells, you must first select those cells by highlighting them. To highlight cells A1 to E1:

1. Place the cursor in cell A1.

2. Press the F8 key. This anchors the cursor.

3. Note that EXT appears on the Status bar in the lower right corner of the screen. You are in the Extend mode.

4. Click in cell E7. Cells A1 to E7 should now be highlighted.

5. Press Esc and click anywhere on the worksheet to clear the highlighting.

Alternative Method - Selecting Cells by Dragging

You can also highlight an area by holding down the left mouse button and dragging the mouse over the area. In addition, you can select noncontiguous areas of the worksheet by doing the following:

1. Place the cursor in cell A1.

2. Hold down the Ctrl key. Do not release it until you are told. Holding down the Ctrl key enables you to select noncontiguous areas of the worksheet.

3. Press the left mouse button.

4. While holding down the left mouse button, use the mouse to move from cell A1 to E7.

5. Continue to hold down the Ctrl key, but release the left mouse button.

6. Using the mouse, place the cursor in cell G8.

7. Press the left mouse button.

8. While holding down the left mouse button, move to cell I17. Release the left mouse button.

9. Release the Ctrl key.

10. Press Esc and click anywhere on the worksheet to remove the highlighting.

Closing Microsoft Excel

1. Click on File, which is located on the Menu bar.

2. Press the down arrow key until Exit is highlighted.

3. Press Enter.

4. Click on No, if you are prompted, «Do you want to save the changes you made to Book1?»

Entering Text

First you place the cursor in the cell in which you would like to enter data, type the data, and then press Enter.

1. Place the cursor in cell A1.

2. Type John Jordan. Note that the word Ready on the Status bar changes to Enter.

3. The Backspace key erases one character at a time. Erase «Jordan» by pressing the backspace key until Jordan is erased.

4. Press Enter. The name «John» should appear in cell A1.

Editing a Cell

After you enter data into a cell, you can edit it by pressing F2 while you are in the cell you wish to edit.

1. Move the cursor to cell A1.

2. Press F2. Note that the word Ready on the Status bar changes to Edit.

3. Change «John» to «Jones.»

4. Use the backspace key to delete the «n» and the «h.»

5. Type nes.

6. Press Enter.



Alternate Method - Editing a Cell by Using the Formula Bar

You can also edit the cell by using the Formula bar. You can change «Jones» to «Joker» as follows:

1. Move the cursor to cell A1.

2. Click in the formula area of the Formula bar.

3. Use the backspace key to erase the «s,» «e,» and «n.»

4. Type ker.

5. Press Enter.

Alternate Method - Editing a Cell by Double-Clicking in the Cell

You can change «Joker» to «Johnson» as follows:

1. Move the cursor to cell A1.

2. Double-click in cell A1.

3. Press the End key. That will place the cursor at the end of your text.

4. Use the backspace to erase «r,» «e,» and «k.»

5. Type hnson.

6. Press Enter.

Changing a Cell Entry

Typing in a cell while you are in the Ready mode will replace the old cell entry with the new information you type.

1. Move the cursor to cell A1.

2. Type Cathy.

3. Press Enter. The name «Cathy» should replace «Johnson.»

Adjusting the Standard Column Width

When you enter Microsoft Excel, the width of each cell is set to a default width. This width is called the standard column width. To change the standard column follow these steps:

1. Click on Format, which is located on the Menu bar.

2. Press the down arrow key until Column is highlighted.

3. Press Enter.

4. Press the down arrow key until Standard Width is highlighted.

5. Press Enter.

6. Type 25 in the Standard Column Width field.

7. Click on OK. The width of every cell on the worksheet should now be set to 25.

Cell Alignment

Look at cell A1. The name «Cathy» is aligned with the left side of the cell. You can change the cell alignment.

Centering by Using the Menu

To center the name Cathy, follow these steps:

1. Move the cursor to cell A1.

2. Click on Format, which is located on the Menu bar.

3. Press the down arrow key until Cells is highlighted.

4. Press Enter.

5. Click on the Alignment tab, if it is not in the front.

6. Click to open the drop-down box associated with the Horizontal field. After the drop-down box is opened, click on Center.

7. Click on OK to close the dialog box. The name «Cathy» should now be centered.

Right-Aligning by Using the Menu

To right-align the name «Cathy,» follow these steps:

1. Move the cursor to cell A1.

2. Click on Format, which is located on the Menu bar.

3. Press the down arrow key until Cells is highlighted.

4. Press Enter.

5. Click on the Alignment tab, if it is not in the front.

6. Click to open the drop-down box associated with the Horizontal field. After the drop-down box is opened, click on Right.

7. Click on OK to close the dialog box. The name «Cathy» should now be right-aligned.

Left-Aligning by Using the Menu

To left-align the name «Cathy,» follow these steps:

1. Move the cursor to cell A1.

2. Click on Format, which is located on the Menu bar.

3. Press the down arrow key until Cells is highlighted.

4. Press Enter.

5. Click on the alignment tab, if it is not in the front.

6. Click to open the drop-down box associated with the Horizontal field. After the drop-down box is opened, click on Left (Indent).

7. Click on OK to close the dialog box. The name «Cathy» should now be left-aligned.

Alternate Method - Alignment by Using the Formatting Toolbar

Using the Formatting toolbar, you can quickly perform functions. You can use the Formatting toolbar to change alignment.

Centering by Using the Toolbar

To center the name «Cathy,» follow these steps:

1. Move the cursor to cell A1.

2. Click on the Center icon, which is located on the Formatting toolbar.

The red circle designates the Align Center icon

Right-Aligning by Using the Toolbar

To right-align the name «Cathy,» follow these steps:

1. Move the cursor to cell A1.

2. Click on the Align Right icon, which is located on the Formatting toolbar.

The red circle designates the Align Right icon

Left-Aligning by Using the Toolbar

To left-align the name «Cathy,» follow these steps:

1. Move the cursor to cell A1.

2. Click on the Align Left icon, which is located on the Formatting toolbar.

The red circle designates the Align Left icon

Adding Bold, Underline, and Italic

You can bold, underline, or italicize text in Microsoft Excel. You can also combine these features - in other words, you can bold, underline, and italicize a single piece of text.

In the exercises that follow, you will learn three different methods for bolding, italicizing, or underlining text in Microsoft Excel. You will learn to bold, italicize, and underline by using the menu, the icons, and the shortcut keys.

Adding Bold - Using the Menu

1. Type Bold in cell A2.

2. Click on the checkmark located on the Formula bar. Clicking on the checkmark is similar to pressing Enter.

3. Click on Format, which is located on the Menu bar.

4. Press the down arrow key until Cells is highlighted.

5. Press Enter.

6. Click on the Font tab, if it is not in the front.

7. Click on Bold in the Font Style box.

8. Click on OK. The word «Bold» should now be bolded.

Adding Italic - Using the Menu

1. Type Italic in cell B2.

2. Click on the checkmark located on the Formula bar. Clicking on the checkmark is similar to pressing Enter.

3. Click on Format, which is located on the Menu bar.

4. Press the down arrow key until Cells is highlighted.

5. Press Enter.

6. Click on Italic in the Font style box.

7. Click on OK. The word «Italic» should now be italicized.

Adding Underline - Using the Menu

In Microsoft Excel there are several types on underlines. The exercise that follows illustrates several of them.

1. Type Underline in cell C2.

2. Click on the checkmark located on the Formula bar. Clicking on the checkmark is similar to pressing Enter.

3. Click on Format, which is located on the Menu bar.

4. Press the down arrow key until Cells is highlighted.

5. Press Enter.

6. Click to open the drop-down menu associated with the Underline box.

7. Click on Single.

8. Click on OK.

9. Note: The cell entry should now have a single underline.

10. Type Underline in cell D2.

11. Click on the checkmark located on the Formula bar.

12. Click on Format, which is located on the Menu bar.

13. Press the down arrow key until Cells is highlighted.

14. Press Enter.

15. Click to open the drop-down menu associated with the Underline field.

16. Click on Double.

17. Click on OK. The cell entry should now have a double underline.

18. Type Underline in cell E2.

19. Click on the checkmark located on the Formula bar.

20. Click on Format, which is located on the Menu bar.

21. Press the down arrow key until Cells is highlighted.

22. Press Enter.

23. Click to open the drop-down menu associated with the Underline field.

24. Click on Single Accounting.

25. Click on OK. The cell entry should now have a single accounting underline.

26. Type Underline in cell F2.

27. Click on the checkmark located on the Formula bar.

28. Click on Format, which is located on the Menu bar.

29. Press the down arrow key until Cells is highlighted.

30. Press Enter.

31. Click to open the drop-down menu associated with the Underline field.

32. Click on Double Accounting.

33. Click on OK. The cell entry should now have a double accounting underline.

Adding All Three - Using the Menu

1. Move the cursor to cell G3.

2. Type All three.

3. Click on the checkmark located on the Formula bar.

4. Click on Format, which is located on the Menu bar.

5. Press the down arrow key until Cells is highlighted.

6. Press Enter. The Font dialog box will open.

7. Click on the Font tab, if it is not in the front.

8. Click on Bold Italic in the Font Style box.

9. Click to open the drop-down menu associated with the Underline field. Then click on Single.

10. Click on OK.

11. Note: The words «All three» should now be bolded, italicized, and underlined.

Removing Bolding and Italics - Using the Menu

1. Highlight cells A2 to B2. Place the cursor in cell A2. Press the F8 key. Press the right arrow key once.

2. Click on Format, which is located on the Menu bar.

3. Press the down arrow key until Cells is highlighted.

4. Press Enter.

5. Click on Regular in the Font style box.

6. Click on OK.

Removing an Underline - Using the Menu

1. Move the cursor to cell C2.

2. Click on Format, which is located on the Menu bar.

3. Press the down arrow key until Cells is highlighted.

4. Press Enter.

5. Click to open the drop-down menu associated with the Underline field. Then click on None.

6. Click on OK.

Alternate Method - Adding Bold by Using the Icon

1. Type Bold in cell A3.

2. Click on the checkmark located on the Formula bar.

3. Click on the Bold icon, which is on the Formatting toolbar.

4. Click again on the Bold icon if you wish to remove the bolding.

Alternate Method - Adding Italic by Using the Icon

1. Type Italic in cell B3.

2. Click on the checkmark located on the Formula bar.

3. Click on the Italic icon, which is on the Formatting toolbar.

4. Click again on the Italic icon if you wish to remove the italics.

Alternate Method - Adding Underline by Using the Icon

1. Type Underline in cell C3.

2. Click on the checkmark located on the Formula bar.

3. Click on the Underline icon, which is on the Formatting toolbar.

4. Click again on the Underline icon if you wish to remove the underline.

Alternate Method - Bold, Underline, and Italicize Using Icons

1. Type All Three in cell D3.

2. Click on the checkmark located on the Formula bar.

3. Click on the Bold icon.

4. Click on the Italic icon.

5. Click on the Underline icon.

Alternate Method - Adding Bold by Using Shortcut Keys

1. Type Bold in cell A4.

2. Click on the checkmark located on the Formula bar.

3. Hold down the Ctrl key while pressing «b» (Ctrl-b).

4. Press Ctrl-b again if you wish to remove the bolding.

Alternate Method - Adding Italic by Using Shortcut Keys

1. Type Italic in cell B4.

2. Click on the checkmark located on the Formula bar.

3. Hold down the Ctrl key while pressing «i» (Ctrl-i).

4. Press Ctrl-i again if you wish to remove the italic formatting.

Alternate Method - Adding Underline by Using Shortcut Keys

1. Type Underline in cell C4.

2. Click on the checkmark located on the Formula bar.

3. Hold down the Ctrl key while pressing «u» (Ctrl-u).

4. Press Ctrl-u again, if you wish to remove the underline.

Alternate Method - Bold, Underline, and Italicize Using Shortcut Keys

1. Type All three in cell D4.

2. Click on the checkmark located on the Formula bar.

3. Hold down the Ctrl key while pressing «b» (Ctrl-b).

4. Hold down the Ctrl key while pressing «i» (Ctrl-i).

5. Hold down the Ctrl key while pressing «u» (Ctrl-u).

Changing the Font and Font Size

You can change the Font and Font Size of the data you enter.

1. Type Times New Roman in cell A5.

2. Click on the checkmark located on the Formula bar.

3. Click on Format, which is located on the Menu bar.

4. Press the down arrow and highlight Cells. Press Enter.

5. Click on the Font tab, if it is not in the front. All of the Fonts listed in the Font box are available to you.

6. Find and click on Times New Roman in the Font box.

7. Click on OK.

8. Note: The font changes from Arial to Times New Roman.

Changing the Font Size

1. Place the cursor in cell A5.

2. Click on Format, which is located on the Menu bar.

3. Press the down arrow and highlight Cells.

4. Press Enter.

5. Click on the Font tab, if it is not in the front.

6. Click on 16 in the Size box.

7. Click on OK.

Deleting a Cell Entry

To delete an entry in a cell or a group of cells, you place the cursor in the cell or highlight the group of cells and press Delete.

Working with Long Text

Whenever you type text that is too long to fit into a cell, Microsoft Excel attempts to display all of the text. It will left-align the text regardless of the alignment that has been assigned to it, and it will borrow space from the blank cells to the right. However, a long text entry will never write over cells that already contain entries - instead, the cells that contain entries will cut off the long text.

Changing a Single Column Width

Earlier we increased the column width of every column on the worksheet. You can also increase individual column widths. If you increase the column width, you will be able to see the long text.

1. Make sure the cursor is anywhere under column A.

2. Point to Format, which is located on the Menu bar.

3. Click the left mouse button.

4. Press the down arrow key until Column is highlighted.

5. Press Enter. Width is highlighted.

6. Press Enter.

7. Type 55 in the column width field.

8. Click on OK.

Column A is now set to a width of 55. You should now be able to see all of the text.

Alternate Method - Changing a Single Column Width

You can also change the column width using the cursor.

1. Place the cursor on the line between the B and C column headings. The cursor should look like the one displayed here, with two arrows.

2. Move your mouse to the right while holding down the left mouse button. The width indicator will appear on the screen.

3. Release the left mouse button when the width indicator shows approximately 40.

Moving to a New Worksheet

In Microsoft Excel, each workbook is made up of several worksheets. Before moving to the next topic, move to a new worksheet.

1. Click on Sheet2, which is located in the lower left corner of the screen.

Filling Cells Automatically

You can use Microsoft Excel to automatically fill cells with information that occur in a series. For example, you can have word automatically fill in times, the days of the week or months of the year, years, and other types of series. The following demonstrates:

1. Type the following into the worksheet as shown.

	A	B	C	D	E	F	G
1	1:00	Sun	Sunday	Jan	January	2000	Type 1

2. Place the cursor in cell A1.

3. Press F8. This will anchor the cursor.

4. Press the right arrow key six times to highlight cells A1 through G1.

5. Find the small black square in the lower right corner of the highlighted area. This is called the Fill Handle.

6. Grab the Fill Handle and drag with your mouse to highlight cells A1 to G24.

7. Note how each cell fills.



Press Esc and then click anywhere on the worksheet to remove the highlighting.

Setting the Enter Key Direction

In Microsoft Excel, you can specify which direction the cursor moves when you press the Enter key. You can have the cursor move up, down, left, right, or not at all. Let's make sure the cursor is set to move down when you press the Enter key.

1. Click on Tools, which is located on the Menu bar.

2. Press the down arrow key until Options is highlighted.

3. Press Enter.

4. Click on the Edit tab, if it is not in the front.

5. Make sure there is a checkmark in the «Move Selection after Enter».

6. If Down is not selected, click to open the Direction drop-down box. Click on Down.

7. Click on OK.

Making Numeric Entries

In Microsoft Excel, you can enter numbers and mathematical formulas into cells. When a number is entered into a cell, you can perform mathematical calculations such as addition, subtraction, multiplication, and division. When entering a mathematical formula, precede the formula with an equals sign. Use the following to indicate the type of calculation you wish to perform:

+ Addition

- Subtraction

* Multiplication

/ Division

^ Exponential

Performing Mathematical Calculations

The following exercises demonstrate how to perform mathematical calculations.

Addition

1. Move the cursor to cell A1.

2. Type 1.

3. Press Enter.

4. Type 1 in cell A2.

5. Press Enter.

6. Type =A1+A2 in cell A3.

7. Press Enter.

8. Note that cell A1 has been added to cell A2 and the result is shown in cell A3.

Place the cursor in cell A3 and look at the Formula bar

Subtraction

1. Press F5. The Go To dialog box will appear.

2. Type B1.

3. Press Enter.

4. The cursor should move to cell B1.

5. Type 5 in cell B1.

6. Press Enter.

7. Type 3 in cell B2.

8. Press Enter.

9. Type =+B1-B2 in cell B3.

10. Press Enter.

11. Note that cell B1 has been subtracted from B2 and the result is shown in cell B3.

Place the cursor in cell B3 and look at the Formula bar

Multiplication

1. Hold down the Ctrl key while you press «g» (Ctrl-g). The Go To dialog box will appear.

2. Type C1.

3. Press Enter. You should now be in cell C1.

4. Type 2 in cell C1.

5. Press Enter.

6. Type 3 in cell C2.

7. Press Enter.

8. Type =C1*C2 in cell C3.

9. Press Enter.

10. Note that C1 is multiplied by C2 and the answer is displayed in C3.

Place the cursor in cell C3 and look at the Formula bar

Division

1. Press F5.

2. Type D1.

3. Press Enter. You should now be in cell D1.

4. Type 6 in cell D1.

5. Press Enter.

6. Type 3 in cell D2.

7. Press Enter.

8. Type =D1/D2 in cell D3.

9. Press Enter.

10. Note that D1 is divided by D2 and the answer is displayed in cell D3.

Place the cursor in cell D3 and look at the Formula bar

Automatic Calculation

If you have automatic calculation turned on, Microsoft Excel recalculates the worksheet as you change cell entries. Let's check to make sure automatic calculation is turned on.

Setting Automatic Calculation

1. Click on Tools, which is located on the Menu bar.

2. Press the down arrow key until Options is highlighted.

3. Press Enter.

4. Click on the Calculation tab if it is not in the front.

5. Select Automatic, if it is not already selected.

6. Click on OK.

Trying Automatic Calculation

Make the changes outlined below and note how Microsoft Excel automatically recalculates.

1. Hold down the Ctrl key while pressing Home (Ctrl-Home). This will move you to cell A1.

2. Type 2. Press the Tab key.

3. Note that the results shown in cell A3 have changed. The number in cell A1 has been added to the number in cell A2 and the results display in cell A3.

4. You should now be in cell B1.

5. Type 6. Press the Tab key.

6. Note that the results shown in cell B3 have changed. The number in cell B1 has been subtracted from the number in cell B2 and the results display in cell B3.

7. You should now be in cell C1.

8. Type 4. Press the Tab key.

9. Note that the results shown in cell C3 have changed. The number in cell C1 has been multiplied by the number in cell C2 and the results display in cell C3.

10. You should now be in cell D1.

11. Type 12. Press the Tab key.

12. Note that the results shown in cell D3 have changed. The number in cell D1 has been divided by the number in cell D2 and the results display in cell D3.

Formatting Numbers

You can format the numbers you enter into Microsoft Excel. You can add commas to separate thousands, specify the number of decimal places, place a dollar sign in front of the number, or display the number as a percent in addition to several other options.

excel data formula toolbar

Before formatting

After formatting

1. Move the cursor to cell A5.

2. Type 1234567.

3. Press Enter.

4. Move the cursor back to cell A5.

5. Click on Format, which is located on the Menu bar.

6. Press the down arrow key until Cells is highlighted.

7. Press Enter.

8. Click on the Number tab, if it is not in the front.

9. Click on Number in the Category box.

10. Type 2 in the Decimal Places box. This will cause the number to display with two decimal places.

11. Place a checkmark in the Use 1000 Separator box. This will cause thousands to be separated with commas.

12. Click on OK.

Alternate Method - Formatting Numbers by Using the Toolbar

1. Move the cursor to cell A6.

2. Type 1234567.

3. Press Enter.

4. Move the cursor back to cell A6.

5. Click twice on the Increase Decimal icon to change the number format to two decimal places. Clicking on the Decrease Decimal icon decreases the decimal places.

6. Click once on the Comma Style icon to add commas to the number.

7. To change the number to a currency format, click on the Currency Style format.

8. Move the cursor to cell A7.

9. Type.35 (note the decimal point).

10. Press Enter.

11. Move the cursor back to cell A7.

12. Click on the Percent Style icon to turn.35 to a percent.

Размещено на Allbest.ru