Aaaaaiea a EXCEL 5.0



Tutorial 1

Introduction to Microsoft Excel

Entering and Editing Data

About cell and range references

Working with Formulas

Columns and rows

( Introduction to Microsoft Excel.

As a spreadsheet application, Microsoft Excel is used to enter text and numbers to be organized, calculated and analyzed. Entering formulas allow any changes in the original numbers to be automatically recalculated throughout an entire worksheet. Data may be formatted creating an attractive, readable presentation of tables or charting and text manipulation features produce attractive and informative reports.

When Excel opens the blank workbook is what you see. The first think you’ll notice about Excel is how similar it looks to other MS programs. The menus look the same.

Each Excel file is a workbook that can hold many worksheets.

( Entering and Editing Data

Data is entered into single cells. The cell that has been selected to enter data is called the active cell and is identified by a heavy border. Only one cell can be active at a time. To change the active cell, move the mouse pointer to another cell and click. The cell reference and its value appear in the formula bar.

[pic]

Each cell is the intersection of a row and a column and has a unique reference. For example, the cell where column B and row 5 intersect is cell B5. You use cell references when you write formulas or refer to cells.

A group of adjacent cells forming a rectangle is called a block (range). It is defined by the addresses of the two cells that are in the opposite corners of the rectangle block area, from the top left cell in the block to the bottom right cell in the block (for example, В2:С5). A range of cells can be marked by using the mouse or by holding down the shift key and using the arrow keys.

Name a cell or a range of cells

• Select the cell, range of cells, or nonadjacent selections that you want to name.

• Click the Name box at the left end of the formula bar.

[pic]

• Type the name for the cells.

• Press ENTER.

Generally, you first select the cell or cells you want to work with, and then you enter data or choose a command.

Selecting cells.

• To select a single cell, click it

• To select a range of cells, drag diagonally from the first cell to the last cell

• To select nonadjacent cells or ranges, hold down CTRL as you click or drag through additional cells.

• To select an entire row, click the row heading

• To select an entire column, click the column heading

• To select all cells on the worksheet, click the Select all button

Data Entry Techniques.

• Entering Data into a Cell: Click the cell and type the data

• Entering Data into a Range of Cells: Select a range of cells and type the data. Press ENTER or TAB to move down or right within a selection

• You can type the same entry into several cells at once. Select the cells you want to enter the data into, type the entry into one cell, and then press CTRL+ENTER

You can enter new information into the cells.

You can enter:

Text (up to 32,000 symbols in one cell)

All text is left-aligned in a cell. To change the alignment, click Home tab, click the Alignment group, and then select the options you want

1. To display all the text on multiple lines in the cell, select the Wrap text check box on the Alignment tab or Use the Alt+Enter key combination to enter text on multiple lines within the same cell.

Number

Whole number (125 or -125)

Decimal (125.00)

Fraction (2 3/5)

Exponents (1.23Е+08)

Date and time (in a different formats):

3/12/96

12-Mar-96

Formulas

Editing Within a Cell

You can edit a cell on your worksheet by typing a new entry over an existing one or by editing part of the information within the cell.

To edit within a cell, double click the cell or press F2 key.

You can also edit cell contents in the Formula Bar.

When you edit a cell containing a formula, the formula is displayed and the value is hidden.

Editing Cell Contents

There are two ways to copy and move cells:

• By using Cut, Copy, Paste

• By dragging with the mouse

Use the Cut, Copy, and Paste commands, toolbar buttons, or shortcut keys if you are copying or moving cells long distances writhing a large worksheet.

Using the mouse is a quick way to copy and move cells a short distance on a worksheet. To copy cell:

• Position the mouse pointer over the border of a cell.

• Hold down CTRL and drag the border to new location

• Release the mouse button, and then the key

To move cell repeat above actions without CTRL

• You can copy the contents of cells into other cells by dragging the fill handle or by using the Fill command on the Home tab Editing group

You can also create a series by increasing the value in the active cell into a range you drag through using the fill handle. The fill handle copies and fills data, and creates series using the AutoFill feature.

In order to enter data series:

Select a cell and then point to the fill handle. The pointer changes to a cross.

Drag the fill handle in the direction you want to fill.

When you release the mouse button, the data is filled into the range.

Series examples:

|Data types |Returned series |

|Qtr1 |Qtr2, Qtr3, Qtr4, |

|Product1 |Product2, Product3, Product4, |

|Jan |Feb, Mar, Apr, |

|Mon |Tue, Wed, West |

Recognizing cursor styles - There are four common cursor styles used in Excel:

|[pic] |Click and drag to highlight multiple cells with |[pic] |Click and drag with this cursor (fill |

| |this cursor, or click in a cell to select the | |handle) to fill cell contents into cells |

| |single cell | |below or to the right. |

|[pic] |Click and drag the contents of the selected cell|[pic] |Click to place the cursor into the Formula|

| |to any other cell. | |bar so that you can edit an equation or |

| | | |function. |

( About cell and range references

[pic]

A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. There are three types of references: relative, absolute and mixed references.

Relative references - A reference such as A3 tells Microsoft Excel how to find another cell, starting from the cell containing the formula. The references to cells or ranges are usually based on their position relative to the cell that contains the formula. In the following example, cell B2 contains the formula =A1*2; Microsoft Excel finds the value one cell above and one cell to the left of B2. This is known as a relative reference.

When you copy or moving, such references change automatically, according to their relative position. When you copy a formula that uses relative references, Excel automatically adjusts the references in the pasted formula to refer to different cells relative to the position of the formula.

In the following example, the formula in cell B2, =A1*2, has been copied to cell B4. Excel has adjusted the formula in cell B4 to =A3, which refers to the cell that is one cell above and to the left of cell B4.

[pic]

Absolute references. A reference such as $A$1 tells Microsoft Excel how to find a cell based on the location of that cell in the worksheet. If you don't want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiplies cell A1 with 2 (=A1*2) and you copy the formula to another cell, Excel will adjust the reference. You can create an absolute reference to cell A1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell A1, for example, add dollar signs to the formula as follows: =$A$1*2

Now if you copy a formula from cell B2 to cell B4, it will take it’s meaning from cell A1.

Mixed references. A mixed reference combines both relative and absolute cell references. You can effectively lock either the row or the column in a mixed reference.

For example, in the case of $B5, the row reference would shift, but the column reference would not

In the case of B$5, the column reference would shift, but the row reference would not

Note: You can switch between absolute, relative and mixed references in the formula easily in the edit mode or on the formula bar by selecting the cell reference in your formula and then pressing the F4 key repeatedly to toggle through the reference options.

( Working with Formulas

A formula is a sequence of values, cell references, names, functions, or operators that produces a new value from existing values. A formula will display on the screen as a number but the formula bar will show what is really stored in that cell. As numbers affecting the formula change, the formula value will change

Formulas contain:

Math operators include the following symbols *, /, +, -, ^ (for exponentiation)

Constants: for example, 3.14, 25

References: for example, А7, В4:К8

Functions: for example, SUM (A7: C9), MAX (B1: B20), SQRT (A5)

You can use more than 300 functions from the different categories.

Financial

Logical

Date & Time

Math & Trig

Statistical and others

Excel requires that EVERY formula begin with an equal sign (=). If you just type without this symbol, Excel treats the entry as text.

To enter the formulas:

Click on the cell that is to contain the formula

Type ”=“ (an equal sign)

Type formula

When the formula bar is active or when editing directly in cells, you can type a formula, insert worksheet functions and names into a formula, and insert references into a formula by selecting cells.

Note: the selection (range or single cell) is surrounded by a dotted line called the moving border.

Copying and Moving Formulas.

To copy the formula you can use the Cell copying technique or:

Type the formula in the highest (copying in the column) or extreme left (copying in the row) cell of the interval.

Set the pointer on the small square in the bottom right corner of the cell (fill handle) [pic] containing a formula, and drag it to the desired cells.

Note:

- Copying cells has a different effect on relative and absolute reference.

- Use the Paste Special command from Home menu and copy or move into the cell:

Only values,

Only formula,

Only format,

Or everything together etc.

[pic] Exercise № 1

Enter consecutively

Open new file

Type in cell А1 text SUM

Type in cell А2 number 15

Type in cell А3 number 7.3

Type in cell В1the formula =12+3.25 (after pressing you see the value 15.25 in the cell. This is the result of performance of the add operation).

Type in cell В2 the formula = А2-А3 (after clicking you can see value 7.7 in the cell. This is the result of performance of the subtract operation).

Copy the formula from cell B2 to the cell В3 (You can check that the result value has changed because the references in the formula have changed).

Delete content from cell В3

Edit formula in cell В2 (press F2 key) to =$A$2-$A$3

Copy the formula from cell B2 to cell B3 (You can check that the result value has not changed because the references are absolute in the formula.)

[pic]Exercise № 2

Create series of 12 months into cells В1:M1

Create series such as Pro1, Pro2 …till Pro10 in cells А2:А11

1. Create series of odd numbers into cells В2:В11, start with 1

2. Type the number 1 into a cell В2

3. Type the number 3 into a cell В3

4. Select the range В2:В3

Move the pointer on the small square in the right bottom corner (Click the center across columns button) and drag it to cell В11.

Create series of even numbers into cells С2:С11, start with 2.

4. Type the formula=В2*С2 into cell D2. Copy this formula to cell D11.

( Columns and rows

You can change the width of columns in several ways.

I way:

[pic]

Move the pointer on the right border of the column headline (the pointer changes to ()

Drag the border to the right or left (to narrow or to widen)

II way:

You can click double between column headings until it changes to a double arrow. Microsoft Excel adjusts the column widths to fit the widest value in each column.

III way:

Move the pointer into the column.

Select from the tab Home /Cells group Format/ Column Width…

To change cell height, repeat the above procedure.

Insert the column and row

Set the pointer in the column before the new column

Select the appropriate commands from Home/ Cells group

Insert the cell or groups of cells

Select the cell or groups of cells before the insertion.

Right click button and Insert…

Choose:

Shift Cells Right- New cell(s) are inserted on the left of the selected cell(s).

Shift Cells Down- New cell(s) are inserted above the selected cell(s).

[pic] Exercise № 3

1. Insert column Qtr1 after the Mart column and Qtr2 after the June in your table (check that the other columns have moved)

After 6th row (Pro5) insert the row Total

[pic]Exercise № 4

1. Create the following table

[pic]

2. Calculate the sum value of purchases for the first product (enter the formula =C8*B8 into the D8 cell)

3. Copy this formula for all records of column D

4. Enter the formula =С8+C8*$E$4 into the cell F8 for calculation of the new price of the product (price increases by 20% - this index is given in cell E4 - it is necessary to make an absolute reference to $E$4)

5. Copy this formula for all records of column F.

6. Calculate the sum value of sales for the first good (Enter the formula =F8*E8 into the cell G8)

7. Copy this formula for all records of column G.

8. Find the money balance for all products.

9. Use the AutoSum [pic] button to find total sum value of the purchases and sales in the cells E16 and E17.

10. Enter the formula for calculation of the remainder = E16-E17 in cell E18

11. Save this file as EVEREX.XLS

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

[pic]

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

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

Google Online Preview   Download