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. Office Button
4. Horizontal and vertical scroll bar
5. Title bar
6. Status bar
7. Zoom tool
8. 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.
9. Rows and column headings
Entering data into a worksheet:
Click on the cell that you want then type the data.
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 you want to modify click the
mouse and drag or double click.
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 click on insert 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 then select rename type the new name and press the enter key.
Inserting Printable borders

1
Select the cells in which you want to put borders click on the home tab font group click on
border tab select more borders and choose the style of the border then click outline and inside
and click ok.
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 and click on format then put the
size that you want.
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.
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.

2
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)
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”)
VLOOKUP
Looks for a value in the left most column of a table, and then returns a value in the same row
from the column you specify. By default, the table must be sorted in ascending order. You can
define a name for your table or use absolute cell reference.
Syntax =VLOOKUP (lookup-value, table –array, col-index-num, range-lookup)
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).

3
PRINT AREA
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.
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
In excel there are templates that can save you a lot of time. A Template is a predesigned
spreadsheet that you can use to create new spreadsheets with the same formatting and
predefined formulas. In this case one does not need to know math’s or even how to write the
formulas since they are already integrated into the spreadsheet.
Using conditional formatting
Conditional formatting helps you to see patterns and trends just by examining the raw data.
You can highlight interesting or unusual cell values and visualize the data using different rules.