CAP Excel Formulas & Functions July - Copy (4).pdf

Advanced excel

1. Computer Applications
Introduction to Microsoft Excel
By Mvula.L

2. Objectives
Creating new spreadsheets
Resizing rows & columns
Inserting, deleting, and hiding
rows & columns
Making calculations
using cell-references
Copy/paste work into different
cells/columns and other
applications

3. Excelisacomputerprogramusedtocreate
electronicspreadsheets.WithinExcel,
userscanorganizedata,createcharts,and
performcalculations.Exceloperateslike
otherMicrosoft(MS)Officeprogramsand
hasmanyofthesamefunctionsand
shortcutsofotherMSprograms.
Introduction to Excel

4. Overview of the Excel
Screen
Before working with Excel, it is essential to first become familiar
with the Excel screen. The following will help you to recognize
the various parts of an Excel screen and their functions.
The Title baris located at the very top of the screen. The Title bar
displays the name of the workbook you are currently using.
The Menu bar is located just below the Title bar. The Menu bar is
used to give instructions to the program.

5. Overview of the Excel Screen
Toolbarsprovide shortcuts to menu commands. There are many different
toolbars and the user can choose which toolbars are shown on the screen.
To enable more toolbars go to “View” on the Menu bar, select Toolbars,
then select which toolbar you wish to add to the screen.
The Standard Toolbar provides shortcuts to the File Menu, as well as
mathematical functions, chart creation, and sorting.
The Formatting Toolbar provides shortcuts to font formatting as well as
mathematical functions.
The Status Toolbar allows the user to view if the current worksheet is ready to
enter data.

6. Overview of the Excel Screen
•Microsoft Excel consists
of workbooks. Within each
workbook, there is an
infinite number of
worksheets.
•Each worksheet contains
columns and rows.
•Where a column and a row
intersect is called the cell.
For example, cell B6 is
located where column B and
row 6 meet. You enter your
data into the cells on the
worksheet.

7. Overview of the Excel Screen

8. Overview of the Excel
Screen
•The Name Boxindicates
what cell you are in.
This cell is called the
“active cell.”This cell is
highlighted by a black
box.
•The “=”is used to edit
your formula on your
selected cell.
•The Formula Bar
indicates the contents
of the cell selected. If
you have created a
formula, then the
formula will appear in
this space.

9. Modifying column/row size
By default, every row and column
of a new workbook is set to the
sameheightandwidth. Excel
allows you to modify column width
and row height in different ways,
includingwrapping
textandmerging cells.

10. Modifying column/row size
some of the content in column cannot be
displayed. We can make all of this content
visible by changing thewidthof column.
Position the mouse over thecolumn linein
thecolumn headingso theblack cross

11. Column Width

12. Inserting, deleting, moving,
and hiding rows and columns
After you've been working with a
workbook for a while, you may find
that you want toinsert newcolumns or
rows,deletecertain rows or
columns,movethem to a different
location in the worksheet, or
evenhidethem.

13. File Menu
•When first opening Excel a worksheet will automatically
appear. However, if you desire to open a file that you
previously worked on go to the “File”option located in
the top left corner. Select “Open.”
•To create a new worksheet go to the “File”option and
select “New.”
•To save the work created go to the “File”option and
select “Save.”
•To close an existing worksheet go to the “File”option and
select “Close.”
•To exit the program entirely go to the “File”option and
select “Exit.”

14. Edit Menu
Among the many functions,
the Edit Menu allows you to
make changes to any data
that was entered. You can:
Undo mistakes made. Excel
allows you to undo up to the
last 16 moves you made.
Cut, copy, or paste
information.
Find information in an existing
workbook
Replace existing information.

15. Insert Menu
The Insert Menu
allows you to:
Add new
worksheets, rows,
and columns to an
existing.
You can also insert
charts, pictures, and
objects onto your
worksheet.

16. Format Menu
You can change the colors,
borders, sizes, alignment, and
font of a certain cell by going
to the “Cell” option in the
Format Menu.

17. Format Menu
You can change row and
column width and height in
the “Row” and “Column”
options.
You can rename
worksheets and change
their order in the “Sheet”
option.
The “AutoFormat” option
allows you to apply pre-
selected colors, fonts, and
sizes to entire worksheets.

18. View Menu
The View menu allows
you different options of
viewing your work.
You can enable a Full
Screen view that
changes the view to
include just the
worksheet and Menu
bar.
You can zoom in on your
worksheet to focus on a
smaller portion.

19. View Menu
You can change the
view of your work so
that it is page by
page.
You can insert
Headers and Footers
to your work.
You can add
comments about a
specific cell for future
reference.

20. Excel Worksheets
With Excel, you will be working with different worksheets
within a workbook. Often times it is necessary to name
the different worksheets so that it is easier to find them. To
do so you must:
1_Double click to highlight an existing worksheet
2_Type in what you would like to rename the worksheet

21. Entering Formulas
When entering numerical data, you can command Excel to do
any mathematical function.
Start each formula with an equal sign (=). To enter the same
formulas for a range of cells, use the colon sign “:”
ADDITION FORMULAS
⚫To add cells together use the “+”
sign.
To sum up a series of cells,
highlight the cells, then click
the auto sum button. The
answer will appear at the
bottom of the highlighted box.

22. Excel Built-In Functions
SUM
AVERAGE
SIN
IF
AND
COUNT
COUNTIF
Many More … (look at Help and fx)

23. Using Functions in Excel
Use spreadsheets in decision making; use Goal Seek
and Scenario Manager to evaluate multiple
conditions
Use financial functions (PMT, etc.)
Use fill handle and AutoFill capability
Use pointing to create a formula
Statistical Functions —MAX, MIN, AVERAGE, COUNT
Use functions over arithmetic expressions
Decision making functions (IF and VLOOKUP (vertical
lookup))

24. Formulas, Ranges & Functions

25. Formulas
Excel knows you are entering a formula in a cell
because every formula starts with an = sign.
If you forget the = sign, what you enter will be treated
as text (unless it can be interpreted as a number in
some format).

26. Using functions to calculate
values
Functions are predefined formulas
that perform calculations by using
specific values, called arguments, in
a particular order, called the syntax.
For example, the SUM function adds
values or ranges of cells, and the PMT
function calculates the loan
payments based on an interest rate,
the length of the loan, and the
principal amount of the loan.

27. Calculation operators in
formulas
Operators specify the type of calculation that you
want to perform on the elements of a formula.
Microsoft Excel includes four different types of
calculation operators: arithmetic, comparison, text,
and reference.
Reference operators are used in range references,
and we examine them later.

28. Arithmetic operators
Arithmetic operators perform basic
mathematical operations such as
addition, subtraction, or
multiplication; combine numbers;
and produce numeric results.

29. Arithmetic operators
Arithmetic operator
+ (plus sign)
–(minus sign)
* (asterisk)
/ (forward slash)
% (percent sign)
^ (caret)
Meaning and Example
Addition3+3
Subtraction 3-1 or
Negation -1
Multiplication3*3
Division3/3
Percent20%
Exponentiation3^2

30. Logical Operators

31. Comparison operators compare two
values and then produce the logical
value TRUE or FALSE.
Comparison operator
= (equal sign)
> (greater than sign)
< (less than sign)
>= (greater than or
equal to sign)
<= (less than or equal
to sign)
<> (not equal to sign)
Meaning & Example
Equal toA1=B1
Greater than A1>B1
Less thanA1<B1
Greater than or equal
to A1>=B1
Less than or equal to
A1<=B1
Not equal toA1<>B1

32. The comparison operators are often used with certain built-in
functions like IF to produce a numeric result.
For example,
IF(A1>B1,5,0)
would yield 5 if the value in cell A1 is greater than the value in
cell B1 and 0 otherwise.