MICROSOFT EXCEL
Microsoft
Excel is a spreadsheet program that helps you record, analyze, and calculate
data. The power of Excel comes from its ability to eliminate repetitive
calculations and automatically update data to reflect changes you’ve made. Excel
also allows you to organize and present your data using a variety of graphs and
charts.
Spreadsheets
can be used for
·
Budgeting
·
Calculations
·
Analyzing Data
·
Statistical methods
OPENING EXCEL
·
Click start button on the window
task bar
·
Select programs on the menu
·
Click Microsoft office and then
select Microsoft excel 2007
Excel opens and
displays an empty workbook. Precisely what you see on the screen depends on the
types of the monitor you are using.
EXCEL SCREEN
The first blank workbook displayed by EXCEL is
called Book1. A new workbook usually
has three worksheets labeled Sheet1,
Sheet2 and Sheet3, but more can add if required. Only of an excel worksheet is
visible on the screen at any one time. This is because of the restriction due
to the size of the computer monitor.
The bulk of the screen displayed is made up with
the worksheets which are divided into rows (with heading 1, 2, 3, 4 …..) and columns (with headings A, B, C… AA, AB… IV). although you cannot see them at the same
time, there are 1,048,576 rows and 16,384 columns. This means that there are millions
individual cells in one worksheet. However the number you can use at any time
is limited by the amount of memory in the computer.
At the top of the Excel workspace is the title bar
displaying Microsoft excel followed by the name of the current workbook.
i.
WORKBOOK: Each excel
file is called a workbook; a workbook is the main document you use to store and
work with data. It contain many sheets, and you can therefore organize various
kinds of related information on certain topic on different sheets in a single
file
ii.
WORKSHEET: Each
workbook contain worksheets, a worksheet consists of cells organized into
columns and rows. It is also called spreadsheet
You will use worksheets
to store and analyses data. You can enter and edit data on several worksheets
at the same time and perform calculations based on data from multiple
worksheets.
iii.
CELL: Each worksheet
contains cells, a cell is a combination of columns and rows
WORKSHEET OPERATIONS
Sometimes you may want to have more sheets than
that available by default. Moreover, there are certain operations associated
with sheets that you need to know.
Clicking any sheet tab with the right mouse button
will display a shortcut menu
CELL REFERENCE
On the worksheet, the rectangular area where row
and column intersect is known as a cell. Each cell has a reference identified
by its column and row headings. For examples:
A1 represents the cell in column A and row 1
F10 represents the cell in column F and
row 10
The cell that is currently active, A1, has bold outline
Moving the Active Cell
Cell
selection and movement around the worksheet are similar operations in Excel. To
select a given cell or make it active, simply click on that cell. Use the mouse
or the arrow keys to move around the worksheet. For example, if you press the
right arrow key twice you move two cells to the right. Refer to the table below
for additional information on using the keyboard to navigate a worksheet.
To move
|
Press this key
|
One cell left
|
Left Arrow
|
One cell right
|
Right Arrow
|
One cell up
|
Up Arrow
|
One cell down
|
Down Arrow
|
To top of worksheet (cell A1)
|
Control Home
|
To last cell containing data
|
Control End
|
To end of data in a column
|
Control Down Arrow
|
To beginning of data in a column
|
Control Up Arrow
|
To end of data in a row
|
Control Right Arrow
|
To beginning of data in a row
|
Control Left Arrow
|
POINTER SHAPES
SHAPE
|
IMPLICATTION
|
ACTION
|
 |
The
default point shape
|
Moves
cell pointer or select
|
 |
Appears
when the pointer is on a border (column, row, or window). When adjusting row
height, the arrows point up and down. When adjusting column width, the arrows
point right to left
|
Adjusts
the column width, row height, or window size
|
 |
Appears
when you are editing the contents of a cell.
|
Moves
the insertion point within the cell
|
 |
Appears
when you have a graphic that may be moved.
|
Moves
the selected graphic to the new location.
|
 |
Appears when you are pointing to the border of a cell.
|
Moves the selected cell to a new location
|
 |
Appears
when you are at the "fill corner" of a cell or range of cells.
|
AutoFills
other cells with similar information
|
ENTERING DATA
Data can be text or numeric. Text is defined as any
combination of numbers and letters. Numeric entries are limited to numbers.
Numbers can exist as independent values or as values derived from a formula
(Calculated values).
ENTERING TEXT
Text will automatically align to the left in the
cell. If the length of the text is greater than the width of the column, it
will appear as if it were occupying adjacent cells.
To
enter text
·
Click on the cell
where the text will be entered.
·
Type the text. The
text will also appear in the Formula bar
·
Press the [Enter] or the [Tab] key
ENTERING NUMBERS
Number will automatically align to the right in the
cell. To enter negative value, type a minus [-] before the number or enclose
the number in a parenthesis
To
enter number
·
Click on the cell
where the text will be entered.
·
Type the number into
the cell. The text will also appear in the Formula bar
·
Press the [Enter] or the [Tab] key
Calculation operators and precedence
Operators
specify the type of calculation that you want to perform on the elements of a
formula. There is a default order in which calculations occur, but you can
change this order by using parentheses.
Types of operators
There are four
different types of calculation operators: arithmetic, comparison, text
concatenation, and reference.
Arithmetic operators
To perform basic
mathematical operations such as addition, subtraction, or multiplication;
combine numbers; and produce numeric results, use the following arithmetic
operators:
Arithmetic
operator
|
Meaning
|
Example
|
+
(plus sign)
|
Addition
|
3+3
|
–
(minus sign)
|
Subtraction
Negation |
3–1
–1 |
* (asterisk)
|
Multiplication
|
3*3
|
/
(forward slash)
|
Division
|
3/3
|
%
(percent sign)
|
Percent
|
20%
|
^
(caret)
|
Exponentiation
|
3^2
|
Comparison operators
You can compare two
values with the following operators. When two values are compared by using
these operators, the result is a logical value either TRUE or FALSE.
Comparison
operator
|
Meaning
|
Example
|
=
(equal sign)
|
Equal
to
|
A1=B1
|
>
(greater than sign)
|
Greater
than
|
A1>B1
|
<
(less than sign)
|
Less
than
|
A1<B1
|
>=
(greater than or equal to sign)
|
Greater
than or equal to
|
A1>=B1
|
<=
(less than or equal to sign)
|
Less
than or equal to
|
A1<=B1
|
<>
(not equal to sign)
|
Not
equal to
|
A1<>B1
|
Text concatenation operator
Use the ampersand
(&) to join, or concatenate, one or more text strings to produce a single
piece of text.
Text
operator
|
Meaning
|
Example
|
&
(ampersand)
|
Connects,
or concatenates, two values to produce one continuous text value
|
("North"&"wind")
|
Reference operators
Combine ranges of
cells for calculations with the following operators.
Reference
operator
|
Meaning
|
Example
|
:
(colon)
|
Range
operator, which produces one reference to all the cells between two
references, including the two references
|
B5:B15
|
,
(comma)
|
Union
operator, which combines multiple references into one reference
|
SUM(B5:B15,D5:D15)
|
(space)
|
Intersection
operator, which produces on reference to cells common to the two references
|
B7:D7
C6:C8
|
The order in which Excel performs operations in
formulas
In some cases, the
order in which calculation is performed can affect the return value of the
formula, so it's important to understand how the order is determined and how
you can change the order to obtain desired results.
Calculation order
Formulas calculate
values in a specific order. A formula in Excel always begins with an equal sign
(=). The equal sign tells Excel that the succeeding characters constitute a
formula. Following the equal sign are the elements to be calculated (the
operands), which are separated by calculation operators. Excel calculates the
formula from left to right, according to a specific order for each operator in
the formula.
Operator precedence
If you combine several
operators in a single formula, Excel performs the operations in the order shown
in the following table. If a formula contains operators with the same
precedence — for example, if a formula contains both a multiplication and
division operator — Excel evaluates the operators from left to right.
Operator
|
Description
|
:
(colon)
(single space)
, (comma)
|
Reference
operators
|
–
|
Negation
(as in –1)
|
%
|
Percent
|
^
|
Exponentiation
|
*
and /
|
Multiplication
and division
|
+
and –
|
Addition
and subtraction
|
&
|
Connects
two strings of text (concatenation)
|
=
< > <= >= <> |
Comparison
|
Use of parentheses
To change the order of
evaluation, enclose in parentheses the part of the formula to be calculated
first. For example, the following formula produces 11 because Excel calculates
multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to
the result.
=5+2*3
In contrast, if you
use parentheses to change the syntax, Excel adds 5 and 2 together and then
multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below,
the parentheses around the first part of the formula force Excel to calculate
B4+25 first and then divide the result by the sum of the values in cells D5,
E5, and F5.
=(B4+25)/SUM(D5:F5)
Overview of formulas
Formulas are equations
that perform calculations on values in your worksheet. A formula starts with an
equal sign (=). For example, the following formula multiplies 2 by 3 and then
adds 5 to the result.
=5+2*3
A formula can also
contain any or all of the following: functions,
references, operators,
and constants.
Parts
of a formula
- Functions: The PI() function returns the value of pi: 3.142...
- References: A2 returns the value in cell A2.
- Constants: Numbers or text values entered directly into a formula, such as 2.
- Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies.
IF FUNCTION
Returns one value if a
condition you specify evaluates to TRUE and another value if it evaluates to
FALSE.
Use IF to conduct
conditional tests on values and formulas.
Syntax
IF (logical_test,value_if_true,value_if_false)
Logical test is any value or
expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a
logical expression; if the value in cell A10 is equal to 100, the expression
evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument
can use any comparison calculation operator.
Value_if_true is the value that is
returned if logical_test is TRUE. For example, if this argument is the text
string "Within budget" and the logical_test argument evaluates to
TRUE, then the IF function displays the text "Within budget". If logical
test is TRUE and value _ if _true is blank, this argument returns 0 (zero). To
display the word TRUE, use the logical value TRUE for this argument. Value _if_
true can be another formula.
Value _if_ false
is the value that is returned if logical test is FALSE. For example, if this
argument is the text string "Over budget" and the logical test
argument evaluates to FALSE, then the IF function displays the text "Over
budget". If logical test is FALSE and value _if_ false is omitted, (that
is, after value _if_ true, there is no comma), then the logical value FALSE is
returned. If logical test is FALSE and value _if_ false is blank (that is,
after value _if_ true, there is a comma followed by the closing parenthesis),
then the value 0 (zero) is returned. Value _if_ false can be another formula.
Remarks
Up
to 64 IF functions can be nested as value _ if _ true and value _if_ false
arguments to construct more elaborate tests. (See Example 3 for a sample of
nested IF functions.). Alternatively, to test many conditions, consider using
the LOOKUP, VLOOKUP, or HLOOKUP function. (See
Example 4 for a sample of the LOOKUP function.) When
the value _if_ true and value _if_ false arguments are evaluated, IF returns
the value returned by those statements. If
any of the arguments to IF are arrays,
every element of the array is evaluated when the IF statement is carried out. Microsoft
Excel provides additional functions that can be used to analyze your data based
on a condition. For example, to count the number of occurrences of a string of
text or a number within a range of cells, use the COUNTIF and COUNTIFS worksheet functions.
To calculate a sum based on a string of text or a number within a range, use
the SUMIF and SUMIFS worksheet function. Example 1
The example may be
easier to understand if you copy it to a blank worksheet.
1. Create
a blank workbook or worksheet.
2. Select
the example in the Help topic.
Note
Do not select the row or column headers.
Selecting an example from Help
3. Press
CTRL+C.
4. In
the worksheet, select cell A1, and press CTRL+V.
5. To
switch between viewing the results and viewing the formulas that return the
results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula
Auditing group, click the Show Formulas button.
|
|
Example 2
The example may be
easier to understand if you copy it to a blank worksheet.
1. Create
a blank workbook or worksheet.
2. Select
the example in the Help topic.
Note
Do not select the row or column headers.
Selecting an example
from Help
3. Press
CTRL+C.
4. In
the worksheet, select cell A1, and press CTRL+V.
5. To
switch between viewing the results and viewing the formulas that return the
results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula
Auditing group, click the Show Formulas button.
|
|
Example 3
The example may be
easier to understand if you copy it to a blank worksheet.
1. Create
a blank workbook or worksheet.
2. Select
the example in the Help topic.
Note
Do not select the row or column headers.
Selecting an example
from Help
3. Press
CTRL+C.
4. In
the worksheet, select cell A1, and press CTRL+V.
5. To
switch between viewing the results and viewing the formulas that return the
results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula
Auditing group, click the Show Formulas button.
|
|
In the preceding
example, the second IF statement is also the value_if_false argument to the
first IF statement. Similarly, the third IF statement is the value_if_false
argument to the second IF statement. For example, if the first logical_test
(Average>89) is TRUE, "A" is returned. If the first logical_test
is FALSE, the second IF statement is evaluated, and so on.
The letter grades are
assigned to numbers using the following key.
If
Score is
|
Then
return
|
Greater
than 89
|
A
|
From
80 to 89
|
B
|
From
70 to 79
|
C
|
From
60 to 69
|
D
|
Less
than 60
|
F
|
Example 4
In this example, the
LOOKUP function is used instead of the IF function because there are thirteen
conditions to test and you may find this easier to read and maintain.
The example may be
easier to understand if you copy it to a blank worksheet.
1. Create
a blank workbook or worksheet.
2. Select
the example in the Help topic.
Note
Do not select the row or column headers.
Selecting an example
from Help
3. Press
CTRL+C.
4. In
the worksheet, select cell A1, and press CTRL+V.
5. To
switch between viewing the results and viewing the formulas that return the
results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula
Auditing group, click the Show Formulas button.
|
|
No comments:
Post a Comment