CHAPTER 7

Microsoft Excel
Learning Objectives:
1. Define MS Excel.
2. Identify the different parts of Excel Window.
3. Identify the importance and function of each part.
4. Identify the different tabs and its group of related command
in the ribbon.
Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows,
Mac OS, and iOS. It features calculation, graphing tools, pivot tables, and a macro
programming language called Visual Basic for Applications. Excel can create and format
workbooks (a collection of spreadsheets) in order to analyze data and make more informed
business decisions. Specifically, Excel can track data, build models for analyzing data, write
formulas to perform calculations on that data, pivot the data in numerous ways, and present
data in a variety of professional looking charts.
Microsoft Excel is a powerful spreadsheet application developed by Microsoft. It is widely
used for various purposes, including data analysis, financial calculations, statistical analysis,
and more. Here is an overview of Microsoft Excel:
Key Features of Microsoft Excel:
Grid Interface: Excel is built on a grid system where data is organized in rows and columns.
Each intersection of a row and column is referred to as a cell.
Formulas and Functions: Excel provides a wide range of formulas and functions for
performing calculations. Functions can be used for mathematical, statistical, financial, and
logical operations.
Cell Referencing: Cells in Excel can be referenced using alphanumeric combinations.
Absolute and relative references help in creating dynamic formulas.
Charts and Graphs: Excel allows users to create a variety of charts and graphs to visually
represent data trends and patterns. Common types include bar charts, line graphs, and pie
charts.

Data Sorting and Filtering: Excel enables users to sort data alphabetically, numerically, or
chronologically. Filtering allows users to display only the data that meets specific criteria.

Data Validation: Users can set rules and restrictions on data entry using data validation. This
ensures data accuracy and consistency.

Conditional Formatting: Excel supports conditional formatting, allowing users to highlight

cells based on specific conditions. This feature enhances data visualization.
PivotTables and PivotCharts: PivotTables provide a powerful way to summarize and analyze
large datasets. PivotCharts complement PivotTables by creating visual representations of
summarized data.

Data Analysis Tools: Excel includes various data analysis tools such as Goal Seek, Solver,
and Scenario Manager. These tools help users perform complex analyses and make
informed decisions.
Collaboration: Multiple users can collaborate on Excel workbooks simultaneously using
features like shared workbooks, comments, and co-authoring.

Import and Export Data: Excel supports the import and export of data from/to various file
formats, databases, and online sources. This facilitates data integration.

Templates: Excel offers pre-designed templates for common tasks like budgeting, project
management, and invoicing. Users can also create custom templates.

Common Uses of Microsoft Excel:

Financial Analysis: Excel is extensively used for financial modeling, budgeting, and financial
analysis.
Data Management: Excel is a versatile tool for managing and organizing large sets of data,
making it easy to update, manipulate, and analyze.
Business Analytics: Many businesses use Excel for analyzing sales data, tracking expenses,
and creating performance reports.
Statistical Analysis: Excel's statistical functions and tools make it a valuable tool for
statisticians and researchers.
Project Management: Excel is used for creating Gantt charts, tracking project timelines, and
managing project-related data.

Educational Purposes: Excel is widely used in educational settings for teaching

mathematical concepts, data analysis, and other subjects.

Inventory Management: Excel helps in managing and tracking inventory, including stock
levels and reorder points.

Versions: Excel has undergone multiple versions, and the latest version as of my knowledge
cutoff in January 2022 is Microsoft Excel 2021, which is part of the Microsoft 365 suite.

PARTS OF MICROSOFT EXCEL

1. Title Bar - Located at the top of the Excel window, the title bar displays the name of the
current workbook.
2. Quick Access Toolbar - A customizable toolbar located above the Ribbon that allows quick
access to frequently used commands.
3. Ribbon - Composed of tabs, groups, and commands, the Ribbon organizes Excel's
features and functions. Common tabs include Home, Insert, Page Layout, Formulas, Data,
Review, and View.
4. Tabs Tabs are sections on the Ribbon, each representing a category of commands.
Clicking on a tab reveals related groups of commands.
5. Worksheet Area - The main grid where data is entered and manipulated, organized into
rows and columns of cells.
6. Columns and Rows Columns are vertical groups of cells identified by letters (A, B, C...),
while rows are horizontal groups identified by numbers (1, 2, 3...).
7. Cell - The intersection of a column and row, identified by its unique reference (e.g., A1).
Cells hold data, formulas, or functions.
8. Formula Bar - Located above the worksheet grid, the formula bar displays the contents of
the active cell and allows for data entry and formula editing.
9. Name Box - The Name Box displays the address or name of the selected cell. It can also
be used to define or navigate to named ranges.
10. Fill Handle - A small square at the bottom-right corner of a selected cell or range that can
be dragged to fill adjacent cells with a series or pattern.
11. Sheet Navigation Arrows - Located beside the sheet tabs, these arrows enable you to
navigate between sheets in the workbook.
12. Status Bar - Located at the bottom of the Excel window, the status bar provides
information about the current state of the workbook, including average, count, and sum for
selected cells.
13. Comments and Notes - Comments allow users to add annotations to cells for
collaboration, while notes provide additional information.
14. Select All Button - The Select All button sits at the upper left of all worksheets, at the
origin of row and column labels. You can use the Select All button to quickly select all cells in
a worksheet.
15. Sheet Tabs - In Microsoft Excel, a sheet, sheet tab, or worksheet tab is used to display
the worksheet that a user is currently editing. Every Excel file may have multiple worksheets.
16. Vertical Scroll Bar - vertical scroll bar which is used to view the data in excel from up and
down
17. Horizontal Scroll Bar - horizontal scroll bar which is used to view the data from left to
right, we can hide or unhide the scroll bar from the Options category in the files tab where
we will find.
[Link] Control - At the bottom right corner of the Excel window, you can find a zoom
slider. It allows you to zoom in or out by dragging the slider left or right.

UNDERSTANDING THE RIBBON

HOMETAB
This is the most used tab; it incorporates all text and cell formatting features such as font
and paragraph changes. The Home Tab also includes basic spreadsheet formatting
elements such as text wrap, merging cells and cell style.

INSERT TAB
This tab allows you to insert a variety of items into a document from pictures, clip art, apps,
sparklines, headers and footers.

INSERT TAB

PAGE LAYOUT TAB

This tab has commands to modify page such as margins, orientation and themes.
Esok Excel Product Acoustion Failed)

FORMULA TAB
This tab has commands to use when creating Formulas. This tab holds an immense function
library which can assist when creating any formula or function in your spreadsheet.
DATA TAB
This tab allows you to modify worksheets with large amounts of data by sorting and filtering
as well as analyzing and grouping data.

REVIEW TAB
This tab allows you to revise spelling and grammar issues as well as set up security
protections. It provides track changes and notes feature providing the ability to make notes
and changes someone's document.

VIEW TAB
This tab allows you to change the view of your document including freezing or splitting
panes, viewing gridlines and hide cells.

FORMATTING CELLS AND WORKSHEETS

FORMATTING SPREADSHEETS
To further enhance your spreadsheet, you can format a number of elements such as text,
numbers, color, and table styles. Spreadsheets can become professional documents used
for company meetings or can even be published.

DIFFERENT WAYS OF FORMATTING SPREADSHEETS

1. Wrap Text
Using the Ribbon
You can display multiple lines of text inside a cell by wrapping the text. Wrapping text in a
cell does not affect other cells.
a) Click the cell in which you want to wrap the text.
b) On the Home Tab, in the alignment group, click wrap text.
c) The text in your cell will be wrapped.
2. Format Numbers
Using the Ribbon
In excel, the format of a cell is separated from the data that is stored in the cell. This display
difference can have a significant effect when the data is numeric. For example, numbers in
cells will default as rounded numbers, date and time may not appears as anticipated. After
you type numbers in a cell, you can change the format in which they are displayed to ensure
the numbers in your spreadsheet are displayed as you intended.
a) Click the cell(s) that contains the numbers that you want to format.
b) On the Home Tab, in the Number group, click the arrow next to the number format box,

⚫
and then click the format that you want.
Using the Format Cells Dialog Box
a) Right click the cell that contains the numbers that you want to format.
b) Choose Format Cells.
c) In the Format Cells Dialog Box choose Number Tab.
d) Choose the number format among the different category then click OK.
⚫ Using the Mini Toolbar
a) Right click the cell that contains the numbers that you want to format.
b) The Mini Toolbar appears.
c) Click on the Accounting Number Format drop down arrow.
d) Select More accounting Format

[Link] Borders
By using predefined border styles, you can quickly add a border around cells or ranges of
cells. If predefined cell borders do not meet needs, you can create a custom border.
Apply Borders
• Using the Ribbon
a) On a worksheet, select the cell or range of cells that you want to add a border to, change
the
border style on, or remove a border from.
b) Go to the Home tab, in the Font group
c) Click the arrow next to Borders
d) Click on the border style you would like

⚫
e) The border will be applied to the cell or cell range
Using the Format Cells Dialog Box a) Highlight cells that you want to apply border.
a) Highlight cells that you want to apply border.
b) Choose format Cells.
c) In the Format Cells Dialog Box
d) Choose Border Tab.
e) Choose the outline and inside preset options.
f) Click Ok.

⚫ Using the Mini Toolbar

a) Highlight the cells that you want to apply border.
b) Right click the mouse and choose the bottom border drop down
arrow.
c) Select for all borders.
Remove Cell Borders
Using the Ribbon
a) Go to the Home Tab, in the Font Group.
b) Click the arrow next to Borders

⚫
c) Click No Border.
Using the Format Cells Dialog Box
a) Highlight the cells that you want to apply no border.
b) Choose Format Cells
c) In the Format cells Dialog Box
d) Choose Border Tab.

⚫
e) Choose none preset option ten click Ok.
Using the Mini Toolbar
a) Highlight the cells that you want to apply no border.
b) Right click the mouse and choose the Bottom Border drop down
arrow
c) Select no border.
4. Cell Styles
You can create a cell style that includes a custom border, colors and accounting
formatting.
Using the Ribbon
a) On the Home Tab, in the Styles group, click Cell styles.
b) Select the different cell style option you would like applied to your spreadsheet.
5. Cell and text Coloring
You can also modify a variety of cell and text colors manually.

⚫
Cell Fill
Using the ribbon
a) Select the cells that you want to apply or remove a fill color from.
b) Go to the Home Tab, in the Font group and select one of the following options.
c) To fill cells with a solid color, click the arrow next to Fill Color, and then under Theme
Colors or Standard Colors, click the color that you want.
d) To fill cells with a custom color, click the arrow next to Fill Color, click More Colors, and
then in the Colors dialog box select the color that you want.
e) To apply the most recently selected color, click Fill Color.
Using the Format cells dialog Box
a) Highlight and right click the cells that
you want to apply.
b) Choose Format cells.
c) In the Format Cells Dialog Box choose Fill tab.
d) Choose the background color that you want then click OK.

APPLYING FORMULAS AND FUNCTIONS

How to Enter Basic Formulas
Formulas are the real workhorses of an Excel. If you set up a formula properly, it computes
the correct answer when you enter it into a cell. From then on, it keeps itself up to date,
recalculating the results whenever you change any of the values that the formula uses. A
formula always starts with an equal sign(=).Microsoft Office Excel follows the standard order
of mathematical operations.
A formula can contain any or all of the following parts.
1. Functions
A function, such as PI() or SUM(), starts with an equal sign (=).
2. Cell references
You can refer to data in worksheet cells by including cell references in the formula. For
example, the cell reference A2 returns the value of that cell or uses that value in the
calculation.
3. Constants
You can also enter constants, such as numbers (such as 2) or text values, directly into a
formula.
4. Operators
Operators are the symbols that are used to specify the type of calculation that you want the
formula to perform.

Mathematical operation Function

+ To add numbers
- To subtract
* To multiply
/ To divide
^ To raise a number to an exponential power

DEALING WITH ERRORS

TYPE OF ERROR
1. The #####? Error
2. The #N/A error
[Link] #NAME? error
4. The error #REF!
5. The error #VALUE

DESCRIPTION
1. This means that the numeric value entered into a cell is too wide to display within the cell.
You can resize the column by dragging the boundary between the column headings.
2. The #N/A error value occurs when a value is not available to a function or a formula. If
certain cells on your worksheet contain data that is not yet available, then #N/A will be
entered in those cells. Formulas that refer to those cells will then return #N/A instead of
attempting to calculate a value
3. The #NAME? error value occurs when Microsoft Excel doesn't recognize text in a formula.
This means that you may have misspelled the name of a range or function, or referred to a
name that does not exist, or left out the colon in a range. I.e., (B3:D7). or =SUME(B4:B7)
4. The #REF! error value occurs when a cell reference is not valid. For example, perhaps
you deleted some cells that this formula refers to.
5. This means you entered text when a formula requires a number or a value such as true or
false.

REFERENCE OPERATORS
Reference operators refer to a cell or a group of cells.
There are two types of reference operators:
1. Range - A range reference refers to all the cells between and including the reference. A
range reference consists of two cell addresses separated by a colon. The reference A1:A3
includes cells A1, A2, and A3. The reference A1:C3 includes A1, A2, A3, B1, B2, B3, C1,
C2, and C3.
2. Union - A union reference includes two or more references. A union reference consists of
two or more cell addresses separated by a comma. The reference A7, B8,C9 refers to cells
A7, B8, and C9.

Relative and Absolute cell References

1. Relative References - Every relative cell reference in a formula automatically changes
when the formula is copied down a column or across a row. As the example illustrated here
shows, when the formula =C4*$D$9 is copied from row to row, the relative cell references
change from C4 to C5 to C6.
2. Absolute Reference - An absolute cell reference is fixed. Absolute references don't
change if you copy a formula from one cell to another. Absolute references have dollar signs
($) like this: $D$9. As the art shows, when the formula =C4*$D$9 is copied from row to row,
the absolute cell reference remains as $D$9.

FUNCTIONS
1. Count and Sum:
The most used functions in Excel are the functions that count and sum. You can count and
sum based on one criteria or multiple criteria.

COUNT
Excel's COUNT function is one of a group of Count Functions that is used to total the
number of cells in a selected range. It counts the number of cells that contain numbers, and
counts numbers within the list of arguments.

Syntax:
COUNT(value1, [value2], ...)
The COUNT function syntax has the following arguments:
a) value1 Required. The first item, cell reference, or range within which you want to count
numbers.
b) value2, ...Optional. Up to 255 additional items, cell references, or ranges within which you
want to count numbers.
1. Type the following numbers I your spreadsheet.
2. Place the mouse in A11 and type =COUNT(A1:A10).
3. The result of your spread sheet.
COUNT IF
The COUNTIF function combines the IF function and COUNT function in Excel. This
combination allows you to count the number of times specific data is found in a selected
group of cells.

COUNTIF (range, criteria)

1. Type the following numbers in your spread sheet.
2. Place the mouse in B11 and type=COUNTIF(A1:B10,">=40")
3. The results should be 12 when you press enter.

SUM

To sum a range of cells, use the SUM function.

SUM(expr)
The expr placeholder represents a string expression identifying the field that contains
the numeric data you want to add or an expression that performs a calculation using the data
in that field. Operands in expr can include the name of a table field, a constant, or a function
(which can be either intrinsic or user- defined but not one of the other SQL aggregate
functions).
1. Type the following numbers in your spread sheet.

2. Place the mouse in A11 and type =SUM(A1:A10)

3. The result should be 384 when you 11 =SUM(A1:A10)

press enter.

Sumlf

To sum cells based on specific criteria. Syntax

SUMIF(range, criteria, [sum_range])
The SUMIF function syntax has the following arguments.
a) rangeRequired. The range of cells that you want evaluated by criteria. Cells in each range
must be numbers or names, arrays, or references that contain numbers. Blank and text
values are ignored.
b) criteria Required. The criteria in the form of a number, expression, a cell reference, text, or
a function that defines which cells will be added.

1. Type the following spread sheet

numbers in your
2. Place the
in B12 and Type mouse =SUMIF(A1:B10,”<40")
3. The answer should be 184 when you press enter.

The IF function checks whether a condition is met, and returns one value if TRUE and
another value if FALSE.
Syntax
IF(logical_test, [value_if_true], [value_if_false])
The IF function syntax has the following arguments:
a) Logical_test Required. Any value or expression that can be evaluated to TRUE or FALSE.
For example, A10=90 is a logical expression; if the value in cell A10 is equal to 90. The
expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This
argument can use any comparison calculation operator. b) value_if_trueOptional. The value
that you want to be an argument evaluates to TRUE.
string
returned if the logical_test For example, if the value of this argument is the text "passed" and
the logical_test argument evaluates to TRUE, the IF function returns the text "passed" If
logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is
only a comma following the logical_test argument), the IF function returns0 (zero). To display
the word TRUE, use the logical value TRUE for the value_if_true argument.
c. value_if_falseOptional. The value that you want to be returned if the logical_test argument
evaluates to FALSE. For example, if the value of this argument is the text string "failed" and
the logical_test argument evaluates to FALSE, the IF function returns the text "failed" If
logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is
no comma following the value_if_true argument), the IF function returns value of the the
logical value FALSE. If logical_test evaluates to FALSE and the value_if_false argument is
blank (that is, there is only a comma following the value_if_true argument), the IF function
returns the value 0 (zero).