Starting Excel:



Starting Excel:

• Choose start button on the task bar(on the desktop)

• Select All programs

• Select Microsoft Office

• Select Microsoft Excel 2003

[pic]

A blank workbook will appear with three worksheets: {sheet1, sheet2, sheet3}.

[pic]

Elements of Excel Windows:

1- Title Bar: shows the name of the book; when you open a new Excel (book), the Excel program gives it a name (default name) ( {Book1} OR {Book + number} ( the number represent how many unsaved books are opened.

Note: if you save this book with a new name, that new name will appear on the title bar instead of the default name.

2- Menu bar: contain of set of menus each menu provides access to set of commands.

a. File: {New, Open, Close, Save, Save As, Page Setup, Print, Print Area, Print Preview, …, Exit}.

b. Edit: {Undo, Repeat, Cut, Copy, Paste, Paste Options, Clear, Delete, Find, Delete sheet, Move or Copy sheet}

c. View: {Tool bars: Standard, Formatting …etc, Formula Bar, header and footer. }

d. Insert: { cells, Rows, Columns, Worksheet, Chart, Symbol, picture}

e. Format: {Cells, Rows (Height, Autofit, Hide, Unhide), Column (Width, Autofit, Hide, Unhide), Sheet (Rename, Hide, Unhide, Background)}.

f. Data:{Sort, Filter}

Print Area:

Paste Options:

Clear:

Move or Copy sheet:

Format ( cells ( alignment

Sort:

Filter:

3- Tools Bars: provide access to the commands; there are two tool bars:

a. Standard Tool Bar:

b. Formatting Tool bar:

4- Scroll Bars: enable you to move in your document to see invisible objects, there are two scroll bars:

a. Vertical Scroll Bar: enable you to move backward and forward (up and down) inside the Excel sheet.

b. Horizontal Scroll Bar: enable you to move from side to side in the Excel sheet.

5- Formula Bar: this bar becomes active only when you type or change information in the worksheet, and the contents of the selected cell will be appeared on the formula bar.

Note: if the cell contains a result of an equation, the equation will be viewed on the formula bar, not the result.

6- Status Bar: displays some information about the position of the insertion point or the selected text.

7- Task Pane: the pane (vertical bar) which is appeared on the left side within an office application.

8- Work Sheet: the worksheet is the basic file type in Excel.

Workbooks can contain sheets of various kinds, including worksheets and chart sheets.

A worksheet: is a table consists of rows and columns of information. It is ideal for setting up calculations for a wide variety of applications.

Cell: it is the intersection of column and row; it is the holding place where you can insert information.

Cell Reference (Address): A cell address, usually expressed by the column character and the row number on the worksheet.

For Example:

The Cell which is having the address B6 is located at the intersection of column B and row 6.

Row: takes a number reference; for example {1, 2, 3…..}

Column: takes an alphabetically; for example {A, B….}

Rang of Cells: it is an expression describes more than one cell.

Examples:

1- A1:A10 ( A1

A2

A3

...

...

A10.

2- C1:F1 ( C1, D1, E1, F1.

3- A5: D8 ( A5, B5, C5, D5

A6, B6, C6, D6

A7, B7, C7, D7

A8, B8, C8, D8

4- C3: F5 ( C3, D3, E3, F3

C4, D4, E4, F4

C5, D5, E5, F5

Expressions and Functions:

1- Expressions (+, -, *, /):

Formulas compute a result, usually by performing arithmetic operations on information which is obtained from other cells.

For Example:

In the following picture:

[pic]

If you write {= A1 + A2} in any cell, the excel program will compute the value and show it in that picture.

Excel has several major operators:

Percent (%) like 5%

Exponentiation (^) like: 3^3 OR A1 ^ A2

Multiplication (*) like: 5*3 OR A1*A2

Division (/) like: 2/6 OR A1/A2

Addition (+) like: 10+5 OR A1+A2

Subtraction (-) like: 1-3 OR -7 OR –A1 OR A1-A2.

Note: all formulas or expressions begin with an equal sign.

Building simple formula:

There are several ways to create a formula in excel.

The simplest way is to use point mode. In point mode you can use the mouse to point to the various cells which will be included in the formulas.

The point mode is discussed in the following Example:

[pic]

To enter the formula to calculate the net pay in the cell B3:

1- Select cell B3.

2- Type =.

3- Point to the gross pay amount you entered in cell B1 by clicking once on that cell (when you click on cell B1, B1 will be printed the B3 cell after the equal sign).

4- Type – (a minus sign).

5- Point to the taxes amount by clicking on the cell B2.

[pic]

6- To finish the formula press ENTER.

• If you select B3 cell now, the formula (expression, B1-B2) will be shown in the formula bar. And so you can update it or examine it.

• If you select the formula in the formula bar the cells which is used in the formula, their border will be in other colors {yellow, blue ...}.

• In the previous example; suppose that the taxes amount changed to 179; then the formula will recalculated and show a new result in the cell B3.

2- Functions:

There are three main types of functions:

1- Math Functions.

2- Statistical Functions.

3- Logical Functions.

Math Functions:

1- Sum Function: Add all the numbers in a range of cells; to add numbers in contiguous row or column use AutoSum.

• From contiguous cells.

1. Click a cell below the column of numbers or to the right of the row numbers.

2. Click the AutoSum icon on the standard tool bar.

3. The sum formula is entered automatically.

4. To accept the formula, press ENTER.

• To enter the sum function manually, do the following:

1. select the cell, where you want to place the result.

2. type ( = ) equal sign to activate the formula bar.

3. type sum (

4. select and drag up to the required cells.

5. type ) and press ENTER.

2- SumIF Function: Adds the contents of the cells that specified by a given criteria.

Syntax:

SumIF (range, criteria, sum_range).

Range: is the range of cells you want evaluated (check the condition on it).

Criteria: Are criteria (conditions) in form of a number, expression or text that defines which cells will be added. For Example: criteria can be expressed as 32, "32", ">32" OR "apple".

Sum_Range: the actual cells to sum.

Example:

In the following picture, find the sum of the commission for property value over 160000?

[pic]

SumIf (A2:A5, ">160000", B2:B5)

? A2= 100000 >160000 ( false

? A3= 200000 >160000 ( true ( sum B3

? A4= 300000 >160000 ( true ( sum B4

? A5= 400000 >160000 ( true ( sum B5

The Result will be:

14000+21000+28000= 63000.

Example:

In the following picture find the sum of the pass student's marks if you know that the pass mark is 50

[pic]

SumIF(B2:B8, ">=50", B2:B8)

? B2= 70 >= 50 ( true

? B3= 40 >= 50 ( false

? B4= 30 >= 50 ( false

? B5= 80 >= 50 ( true

? B6= 60 >= 50 ( true

? B7= 30 >= 50 ( false

? B8= 90 >= 50 ( true

Sum of( B2, B5, B6, B8)= 70+ 80+ 60+ 90= 300.

Statistical Functions:

1. Average:

To calculate the average of number in a contiguous row or column.

1- Click a cell below or to the right of the numbers for which you want to find the average.

2- Click the arrow next to AutoSum, and then click Average.

3- Press ENTER.

To enter the average functions manually do the following:

1- Select the cell, where you will place the result.

2- Type = to activate the formula bar.

3- Type average (.

4- Select and drag up to the required cells.

5- Type ) and press Enter.

2. Maximum value: returns the largest value in a set of values.

1- Select a cell below or to the right of numbers for which you want to find the largest number.

2- Click the arrow next to AutoSum, and then max and then press Enter.

To enter the maximum function; do the following:

1- Select the cell, where you will place the result.

2- Type = to activate the formula bar.

3- Type max(.

4- Select and drag up to the required cells.

5- Type ) and press Enter.

3. Minimum: to find minimum value for cells

1- Select a cell below or to the right of the numbers for which you want to find the smallest number.

2- Click the arrow next to AutoSum, and then Min and then press Enter.

To enter the minimum function, do the following:

1- Select the cell, where you will place the result.

2- Type = to activate the formula bar.

3- Type Min(.

4- Select and drag up to the required cells.

5- Type ) and press Enter.

4. Count: counts the number of cells that contain numbers.

1- Select a cell below or to the right of numbers for which you want to find the count.

2- Click the arrow next to AutoSum, and then Count and then press Enter.

To enter the count function, do the following:

1- Select the cell, where you will place the result.

2- Type = to activate the formula bar.

3- Type Count(.

4- Select and drag up to the required cells.

5- Type ) and press Enter.

5. CountIF: counts the number of cells within a range that meet the given criteria.

Microsoft Excel provides additional functions that can be used to analyze your data based on a condition, like countIF.

Example:

|A |B |

|Data |Data |

|Apples |32 |

|Oranges |54 |

|Peaches |75 |

|Apples |86 |

|Formula |Description (Result) |

|=COUNTIF(A2:A5,"apples") |Number of cells with apples in the first column above (2) |

|=COUNTIF(B2:B5,">55") |Number of cells with a value greater than 55 in the second column above (2) |

Logical Functions:

1- OR: it is a logical function returns TRUE if any argument is true; returns FALSE if all arguments are false.

Syntax:

OR( logical1, logical2,…)

logical1, logical2 are two conditions you want to test that can be either TRUE or FALSE.

Examples:

|Formula |Description (Result) |

|=OR(TRUE) |One argument is TRUE (TRUE) |

|=OR(1+1=1,2+2=5) |All arguments evaluate to FALSE (FALSE) |

|=OR(TRUE,FALSE,TRUE) |At least one argument is TRUE (TRUE) |

2- AND: it is a logical function that returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

Syntax:

AND( logical1, logical2,…)

Logical1, logical2 are two or more conditions you want to test that can be either TRUE or FALSE.

Example 1:

|Formula |Description (Result) |

|=AND(TRUE, TRUE) |All arguments are TRUE (TRUE) |

|=AND(TRUE, FALSE) |One argument is FALSE (FALSE) |

|=AND(2+2=4, 2+3=5) |All arguments evaluate to TRUE (TRUE) |

Example 2:

| |A |

| | |

|1 |Data |

| | |

|2 |50 |

| | |

|3 |104 |

| | |

|Formula |Description (Result) |

|=AND(1 ................
................

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

Google Online Preview   Download