Teachers copy

MS EXCEL
Microsoft Office Excel is a spreadsheet
program that provides powerful tools and
features that you can use to analyze, share,
and manage your data with ease. It supports
up to 1 million rows and 16 thousand
columns per worksheet i.e. 1,048,576 rows
by 16,384 columns.
Excel constitutes the following
1. Workbook is a file that contains three or
more worksheets that you can use to
organize various kinds of related information.
2. Worksheet is a series of rows and columns
called tables that contain related data that is
managed independently from the data in
other rows and columns. By default,
Microsoft Office Excel provides three
worksheets in a workbook, but you can insert
additional worksheets.
Features of the Ms excel Window
1. Quick access tool bar
2. Tabbed Ribbon system
3. File tab
4. Horizontal and vertical scroll bar
5. Title bar
6. Status bar
7. Ruler
1
Teachers copy

8. Zoom tool
9. Formula bar: Contains the name box that
shows the reference name of the active
cell. It Displays the contents of the
currently selected cell and contains fx
that has the formula functions.
10. Rows and column headings
Entering data into a worksheet:
Click on the cell that you want then type the
data and press the enter key.
Adjusting the rows and columns:
Position the cursor over the column line in
the column heading; click the mouse and
drag or double click. For the row position the
cursor over the row line.
Inserting Rows, column and worksheet
Position your cursor where you want to insert
a row or column. Click on the home tab cell
group and then choose either to insert a row,
a column or a worksheet. For the worksheet
you can also click on the icon near the sheet
tabs.
Renaming a worksheet
Double click on the sheet tab, type the name
and press the Enter key or Right click on the
sheet tab select rename type the new name
and press the enter key.
2
Teachers copy

Inserting Printable borders

 Select the cells click on the home tab font
group
 Click on border tab select more borders
 Choose the style of the border then
 Click outline then inside and click ok.
Or Right click on your selected work and
choose format cells then click on border
tab.
Inserting gridlines and rows and column
headings
Click on the page layout tab sheet options
and click on the sheet tab. check the boxes
for the Gridlines and that of the Row and
column headings.
Changing many columns and rows height
and width
Highlight the columns or the rows go to home
tab cell group click on format then put the
size that you want.
Auto complete
If the first few characters that you type in a
cell match an existing entry in that column,
Excel automatically enters the remaining
characters for you. Auto Fill data into
adjacent cells

3
Teachers copy

You can use the Fill command to quickly fill

adjacent cells by dragging the fill handle .
Type the starting value for the series. Drag
the fill handle across the range that you want
to fill.
Format cell options
1 Number: Defines the kind of data to be
entered in the cell e.g. Number, currency,
text, date and time, fraction etc.
2. Alignment: defines how the data is
arranged or displayed in the cell.
Text control: Includes
a) Wrap text: Moves overflowing text into the
next line in the same cell
b)Shrink to fit: Reduce the size of the data so
that it fits in the cells
c) Merge cells. Combines two or more cells
into a single cell.
Text orientation: It is used to rotate the
contents of a cell to an angle e.g. 90 degrees.
Formula Functions
A function is a predefined formula that
performs calculations using specific values in
a particular order. These functions include
Sum, Average, Min, Max etc.
Each function has a specific order called
syntax which must be strictly followed.
4
Teachers copy

Syntax order
1. All functions begin with the = sign
2. After the = sign define the function name
e.g. sum.
3. Then there will be an argument. An
argument is the cell range or cell
references that are enclosed by
parenthesis (brackets).
Example =Sum (first value: Last value)
Inserting a formula function
Put the cursor on the cell where you want to
put the formula function, Click on the fx
shortcut on the formula bar or click on the
Auto sum  option on the home tab editing
group.
Sorting and Filtering
Sorting: Sorting allows you to easily reorder
your data. The most common type of sorting
is alphabetically ordering which you can do in
ascending or descending order.
Filtering: It allows you to only display the
data that you want using various conditions
such as equals to, not equals to, less than etc.
Click on the column you want to filter on
home tab editing group then click on sorting
and filter.

5
Teachers copy

Headers and footers, Page layout and

margins
Click on the page layout tab, Sheet options
then set the margins, choose the paper size
and insert headers and footers. You can also
change the page orientation.
Scale to fit
It allows you to format a spreadsheet to fit in
one page. Be careful with how small you
scale the information it can become difficult
to read.
Click on the page layout tab scale to fit group.
Grouping and calculating Subtotals
Grouping is a useful feature that gives you
control over how the information is
displayed. You must sort first before
grouping. Select any cell with information on
it, click on Data tab and select subtotals.
Decide how you want things grouped e.g.
male and female. Select the function e.g.
sum, Select the column you want the
subtotals to appear and click ok.
Rank
Returns the rank of a number from a list of
values. Define a name for the range of values.
Syntax
=Rank(number,ref,order)
6
Teachers copy

Calculating discounts and Commissions

Discount and commission are normally given
in %. Discount is a decrease of the amount in
question while a commission is an increase of
the amount in question.
IF FUNCTION
It is a conditional statement that displays a
value when the condition is true and another
value if the condition is false.
Syntax
=IF (condition,”value if true”,”value if false”)
Example: =IF(Average>=50,”PASS”,”FAIL”)
COUNTIF
It’s an excel function that uses conditional
logic
=countif(range,”criteria”)
LOOKUP FUNCTION
This function looks for a piece of information
in a list, and then picks an item from a second
range of cells. You can define a name for the
lookup table or use the absolute cell
reference.
Syntax
=lookup(what to look for, range to look in,
range to pick from).
PRINT AREA

7
Teachers copy

It allows you to select and print part of a

document .Select part of the document that
you want to print. Click on the page layout
tab page set up group and select print area.
Printing titles
It allows you to select specific rows or
columns that will be repeated in each printed
sheet or page. Click on the page layout tab
click on print titles command. Click on the
icon at the end of the field, select the row or
column to be repeated on each printed page
and click ok.
Freezing panes
This is the ability to freeze or lock specific
rows or columns in your spreadsheet. Select
rows and columns that will remain visible all
the time, even as you are scrolling which is
useful when working with a large worksheet.
Select the row or column to freeze click on
view tab window group and select freeze
panes.
USING CELL REFERENCE
The cell address is basically the name of the
cell and can be found on the name box.
When a cell address is used as part of the
formula, it is now called a cell reference.

8
Teachers copy

Relative cell reference: Cell reference in

formulas automatically adjusts to new
locations when the formula is pasted into
different cells.
Absolute cell reference: Cell references in
formulas do not change when the formula is
pasted into different cells i.e. it always refers
to the same cell or cell range. An absolute cell
reference is designated in the formula by the
addition of a Dollar sign ($). E.g. $A$2
meaning the column and the row do not
change when copied.
Working with Charts
Charts are useful way of communicating data
To create a chart:
 Select the worksheet you want to work
with
 Select the cells to be included in the
chart. Hold the control key to select cells
that are not adjacent
 Click insert tab, choose the chart type
 Right click on the chart to move it to a
new worksheet give it a name and click
ok
Using Templates
A Template is a predesigned spreadsheet
that you can use to create new spreadsheets
9
Teachers copy

with the same formatting and predefined

formulas.
Using conditional formatting
Conditional formatting helps you to see
patterns and trends just by examining the
raw data.

10