MICROSOFT
EXCEL
UNDERSTANDING
By, Akshay Khernar Roll no.108
Overview
Excel is a spreadsheet, a grid made from columns
and rows. It is a software program that can make
number manipulation easy and somewhat
painless.
The nice thing about using a computer and
spreadsheet is that you can experiment with
numbers without having to RE-DO all the
calculations.
Introduction to Spreadsheets
A spreadsheet is a grid of rows and columns
in which you enter text, numbers, and the
results of calculations.
In Excel, a computerized spreadsheet is called
a worksheet. The file used to store
worksheets is called a workbook.
44
Basics of a Spreadsheet
Spreadsheets are made up of
› Columns
› Rows
› and their intersections are called cells
What is a COLUMN ?
In a spreadsheet the COLUMN labeled D is
COLUMN is defined as highlighted.
the vertical space that is
going up and down the
window. Letters are used
to designate each
COLUMN'S location.
What is a row?
In a spreadsheet the ROW labeled 4 is
ROW is defined as the highlighted.
horizontal space that is
going across the window.
Numbers are used to
designate each ROW'S
location.
What is a CELL ?
A CELL is the space In the above diagram the
where a row and column CELL labeled C2 is
intersect. Each CELL is highlighted.
assigned a name
according to its
COLUMN letter and
ROW number.
Types of data
In each cell there may be the following data types:
Labels -- (text with no numerical value)
Number data (constant values)
Formulas (mathematical equation used to calculate)
Data Types Examples Descriptions
Name or Wage or anything that is just
LABEL
Days text
CONSTANT 5 or 3.75 or -7.4 any number
FORMULA =5+3 or = 8*5+3 math equation
Labels
Labels are text entries
› Labels help identify what we are talking
about
› Labels do not have a value associated
with them
› Sometimes called ‘headers’
Constants
Constants are FIXED
number data
Constants may refer to
dollars, percentages, or
number of items (in this
case number of hours
worked within a certain
pay period).
Formulas
Formulas are math equations
that CALCULATE a value to be
displayed.
DO NOT type in the numbers;
type in the equation.
It is BEST to Reference as
much data as possible as
opposed to typing data into
equations. That way when
OTHER information changes,
we DO-NOT have to change
the equations or type in
information again.
Basic Formulas & Functions
= is used to start a formula
Basic Math Functions
Math functions built into them. Of the most basic
operations are the standard multiply, divide, add
and subtract.
Parenthesis =(A1+B1) 10
Exponential =(A1^B1) 3125
Basic Functions in Excel
Sum: will add all the values of cells in the argument
Average: will get the average of all the values of
cells in the argument
Counts the number of cells with numerical data in
the argument
Max: gets the highest cell value include in the
argument
Min: gets the lowest cell value in the argument
If condition
=SUM(A1:A5)
Equal
Function name Argument
sign
Probably the most popular function in any
spreadsheet is the SUM function. The Sum
function takes all of the values in each of the
specified cells and totals their values.
The syntax is: =SUM(first value, second value,
etc)
Definition:
Blank cells will return a value of zero to be added
SUM Function
to the total.
Text cells can not be added to a number and will
produce an error.
Tips:
Sum function
Average Function
The average function
finds the average of the
specified data.
(Simplifies adding all of
the indicated cells
together and dividing by
the total number of cells.)
=AVERAGE(B2:F2)
Max & Min Functions
The Max function will return the largest
(max) value in the selected range of cells.
The Min function will display the smallest
value in a selected set of cells.
=MAX(B2:B3)
Count Function
The Count function will return the number of entries (actually
counts each cell that contains NUMBER DATA) in the selected
range of cells.
Remember: cell that are blank or contain text will not be counted.
=COUNT(A1:E13)
The IF function will check the logical condition of
a statement and return one value if true and a
different value if false.
The syntax is:
=IF (condition, value-if-true, value-if-false)
Definition:
Until you are used to writing them, test them out
on multiple cells.
IF Function
There are multiple ways to write an IF statement
to get the same result
Tips:
IF Functions are like programing - they provide
multiple answers based on certain conditions.
IF Function
=IF(H2>19.9,”PASSED”,”FAILED”)
Common Errors
The following are some errors that may appear in a spreadsheet (there are others too).
› #######
Cell is too narrow to display the results of the formula. To fix this simply make the column wider and
the “real” value will be displayed instead of the ###### signs. Note that even when the ###### signs
are being displayed, Excel still uses the “real” value to calculate formulas that reference this cell.
› #NAME?
You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead of =B3+10)
› #VALUE!
Usually the result of trying to do math with a textual value. Example: =A1*3 where A1 contains the
word “hello”
› #DIV/0!
Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)
› Circular Reference
Using a formula that contains a reference to the cell that the formula “lives in”. Example: putting the
formula =A1+1 in cell A1 or putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2
24
Useful Features
Excel 2010
Instantly reveal formulas and general number format of all cells
Show all formulas and the general number
format of cells
Saves you time because you don’t have to
move the cell pointer to check each formula
one-by-one.
Keyboard short cut to
Instantly reveal formulas
To do this, just press the
Ctrl key and the tilde key
at the same time. The tilde
is the squiggly line that is
directly above the tab key
in the upper left corner of
your keyboard. To change
your worksheet back to the
normal view, just press the
Ctrl and tilde keys again.
Sorting
Arranging data so it’s easy to analyze
You can sort the data alphabetically, from highest
to lowest, or by a number of additional criteria
(such as cell color)