INTRODUCTION
Microsoft excel is a spreadsheet
developed by Microsoft for Windows , macintosh operating system (MacOS), Android
and iphone operating system (iOS). It features calculation, graphic tools,
pivot tables and a macro programming language called visual basic for
applications. It has been a very widely accepted spreadsheet for these
platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3
as the industry standard for spreadsheets. Excel forms part of Microsoft
Office.
Microsoft excel has the basic
features of all spreadsheets, using a grid of cells arranged in numbered rows
and columns to organize data manipulations like arithmetic operations. It is
embedded with functions to accommodate statistical, engineering and financial
needs.
The
excel environment takes a different dimension from that of other ms office
programs. It takes the form of rows and columns although the basic features
such as the title bar, menu bar, office button, menu bar and scroll bars remain
the same.
Also, the text editing and
formatting tools remain the same as those in ms word.
The menu bar in ms excel has
been made to include a FORMULA and DATA menu which forms the basics of ms
excel.
Apart from the grid environment,
all other features remain basically the same except for a few functionalities
that have been included to add functionality to the excel program.
Basic operations that could be
achieved with excel include: addition, subtraction, division, multiplication, average,
statistics, trigonometric ratios etc. Other complex operations like
differentiation can be achieved through visual basic.
THE FORMULAR BAR IN MS EXCEL
This displays the contents of
the active cell. It also enables editing of the cell’s contents.
CELL
It is formed as a result of the
intersection of a row and a column.
THE WORKSHEET AND WORKBOOK IN
MS EXCEL
The worksheet refers to the
interface in excel that houses the cells, rows and columns where operations are
carried out. A worksheet in ms excel 2007 has 1048576 rows and 256 columns
lettered A-Z and then continuing with AA AB, AC and so on. The number of
columns and rows you can have in worksheet is limited by your computer memory
and your system resources. The combination of a column coordinate and a row
coordinate make up a cell address. For example, the cell located at the upper
left corner of the worksheet is cell A1, meaning column A, Row 1. You enter
your data into the cells in a worksheet.
A workbook is a storage location
for worksheets. A typical workbook in ms excel contains three worksheets by
default which may be added if need be.
HOW TO CREATE A WORKSHEET
To create or insert a worksheet
in a workbook, click the insert worksheet button on the taskbar.
DELETING A WORKSHEET
To delete a worksheet,
right-click the worksheet to be deleted and select from the options, DELETE.
RENAMING A WORKSHEET
To rename a worksheet,
right-click the worksheet to be renamed from the status bar and select RENAME.
Type the name of the worksheet and hit enter.
HIDING A WORKSHEET
To hide a worksheet, right-click
the worksheet to be hidden and select HIDE.
UNHIDING A WORKSHEET
Right-click any worksheet from
the status bar and select unhide, select the worksheet to unhide and select OK
ENTERING DATA INTO A WORKSHEET
Worksheet cells can contain two
types of data: constant values and formulae.
Constant values are either text
or numbers that once typed do not change unless retyped.
Formulae are instructions you
enter to perform calculations and they result in single or multiple values.
Formulae are created in excel by preceding the desired calculation with the
equal sign (=). They can be written
using numbers (=300+200). However, it is more efficient to enter the numbers
into cells and construct formulas to refer to these cells, for instance, =B1 +
B2. In this way, the results of the formula updates, immediately you change the
numbers.
When building formulae, Use
+ For addition, SUM when using it as a function
- For subtraction
* For multiplication
/ For division
To enter data in a worksheet:
®
Select
the cell you wish to enter the data
®
Type
the number, text or formulae you wish to enter
CORRECTING DATA ENTERED IN A WORKSHEET
To achieve this:
®
Select
the cell where you wish to make
correction
®
Press
function key F2
®
You
can now make the needed corrections
RANGES
A range is defined as two or
more cells that form a rectangular group of contiguous cells on the worksheet.
WAYS OF SPECIFYING A RANGE
1. Type the upper-left corner and
the lower-right reference separated by the colon (:). For instance B4:G3 refers
to the range B4 through G3
2. Drag over the desired cells with
the mouse
3. Use the mouse to click on one of
the cells. Holding down your SHIFT key, use the arrow keys to select the cells
or use your mouse to select the range.
BASIC OPERATIONS IN MS EXCEL
ADDITION
Addition in excel can take
various forms: adding within the same cell, adding from multiple cells within a
range and adding from multiple cells outside a range.
Adding within the same cell
To add values within a cell,
input the equal sign(=) and then the values separated
by the addition sign(+).
Illustration
To add 50 to 60, input “= 50 + 60” and hit enter
Adding from multiple cells within a range
To add from cells in a range, select
the cell where you want the result to appear. Input the equal sign (=) and then
specify the function (action) to be executed and then the range to be added .
Illustration
To add from cell F1 to F9, input
“=SUM(F1:F9)” and hit enter.
Adding from cells that are not in range
To add from cells out of range,
select the cell where you want the result to appear. Input the equal sign (=)
and specify the function (action) to be executed, and then state the cell
addresses, each separated with a comma (,).
Illustration
To add from cell C1, D3, E5, P5
and Y6, input “=SUM(C1,
D3, E5, P5,Y6)”
and hit enter.
Values in ranges and individual cells
could be added. Users can specify the range and cell address separated by a
comma (,).
SUBTRACTION
The procedure for subtracting in
excel do not differ much from that of addition. It could be within the same
cell, in or out of range.
To subtract within the same cell
To subtract values within a
cell, input the equal sign(=) and then the values separated
by the subtraction sign(-).
Illustration
To subtract 40 from 50, input “= 50 - 40” and hit enter
Subtracting a whole range from Another
To achieve this kind of
operation, the ranges must first be added before the subtraction can take place
Select the cell where the result
would be displayed. Input “=SUM(RANGE1)-SUM(RANGE2)”,
Where RANGE1 = the first range
Where RANGE2 = the second range
Illustration
Subtract range A3:A5 from range
D2:D7
Input “=SUM(D2:D7)-SUM(A3:A5)”
Subtracting from cells that are not in range
To subtract from cells out of
range, select the cell where you want the result to appear. Input the equal
sign (=) , and then state the cell addresses, each separated with a minus sign
(-).
Illustration
To subtract cell C1, D3, E5, P5
and Y6 from C5, input “=(C5-C1-D3-E5- P5-Y6)” and hit enter.
MULTIPLICATION
Multiplication also takes
various forms in ms excel. Some of which are similar to those of addition and
subtraction.
Multiplying within the same cell
To multiply values within a
cell, input the equal sign(=) and then the values separated by the
multiplication sign(*).
Illustration
To multiply 50 by 30, input “= 50 * 30” and hit enter
Multiplying cells in a range
To multiply cells in a range,
select the cell where you want the result to appear. Input the equal sign (=)
and then specify the function (action) to be executed and then the range to be
multiplied.
Illustration
To multiply cell F1 to F9, input “=PRODUCT(F1:F9)” and hit enter.
Multiplying from cells that are not in range
To multiply from cells out of
range, select the cell where you want the result to appear. Input the equal sign
(=) and specify the function (action) to be executed, and then state the cell
addresses, each separated with a comma (,).
Illustration
To multiply cell C1, D3, E5, P5
and Y6, input “=PRODUCT(C1,
D3, E5, P5,Y6)”
and hit enter.
DIVISION
Division in excel may take the
following forms.
To divide values in a cell
To divide values within a cell,
input the equal sign(=) and then the values seperated by the division sign (/).
Illustration
To divide 120 by 60, input “= 120 / 60” and hit enter
Dividing a whole range by another
To achieve this kind of
operation, the ranges must first be added before the division can take place .
Select the cell where the result
would be displayed. Input “=SUM(RANGE1)/SUM(RANGE2)”,
Where RANGE1 = the first range
Where RANGE2 = the second range
Illustration
Divide range A3:A5 by D2:D7
Input “=SUM(D2:D7)/SUM(A3:A5)”
Dividing from cells that are not in range
To divide from cells out of
range, select the cell where you want the result to appear. Input the equal
sign (=) , and then state the cell addresses, each separated with a division
sign (/).
Illustration
To divide cell C5 by Y6, input “=(C5/Y6)” and hit enter.
THE FILL AND COPY COMMAND
The fill and copy commands copy
the contents of cells from one range to another.
The fill command
The fill command copies the
contents of a selected cell (or cells) into its adjacent cells, rows or columns.
Illustration
To copy the contents of cm, ell A3:A7 into D3:D7
®
Select
the range A3:A7 and D3:D7
®
Select
the fill command from the HOME menu
®
Select
FILL right.
The copy command
This copies the content the
content of the selected cells into another location.
Illustration
To copy the contents of cell
A3:A7 into F3:F7
®
Select
the range A3:A7
®
Select
the copy command from the HOME menu
®
Select
cell F3 and paste
WORKING WITH MORE COMPLEX
OPERATIONS
The hierarchy of operations in
ms excel is as follows:
Parenthesis
Exponents
Multiplication and division
Addition and subtraction
Illustration
Simplify the following
expression: NOTE: the cell addresses
may represent values.
B1 * C5 - (D6
+ J8) * 10
10
SOLUTION
®
Add
D6 and J8 and hold the result
®
Multiply
B1 by C5 and hold the result
®
Divide
the product of step 2 by 100
®
Multiply
10 by the sum of step 1
®
Subtract
the product of step 4 from the quotient (result) of step 3
Analysis
Let
B1 = 10
C5 = 50
D6 = 2
J8 = 1
We will have
=
=
= 50 – 30
= 20
0 comments:
Post a Comment