BASIC FORMULAS,
FUNCTIONS AND CHARTS
EXCEL 2013
BASIC MATH FUNCTIONS
• Math functions built into them. Of the most basic
operations are the standard multiply, divide, add
and subtract.
ORDER OF OPERATIONS
• When using several operations in one formula, Excel
follows the order of operations for math.
• first: all parentheses - innermost first
• second: exponents (^)
• third: all multiplication (*) and division (/). Do
these starting with the leftmost * or /
and work to the right.
• fourth: all addition (+) and subtraction (-). Do
these starting with the leftmost + or -
and work to the right.
3
PLEASE EXCUSE MY DEAR AUNT SALLY
• The sentence "Please excuse my dear aunt
Sally" is a popular mneumonic to remember the
order of operations:
Menumonic Meaning
• Please parentheses
• Excuse exponents
• My Dear mulitplication and division
(going left to right)
• Aunt Sally addition and subtraction
(going left to right)
4
SUM FUNCTION
Definition: Tips:
• Probably the most • Blank cells will return a
popular function in any value of zero to be
spreadsheet is the SUM added to the total.
function. The Sum
function takes all of the
• Text cells can not be
values in each of the added to a number
specified cells and totals and will produce an
their values. error.
• The syntax is: =SUM(first
value, second value, etc)
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.)
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.
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.
COMMON ERRORS
EXCEL 2013
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
CHARTS AND GRAPHS
EXCEL 2013
CHARTS AND GRAPHS
• Generally charts also consist of
descriptive text
• Depending on type of chart text may
be
• On the left and/or across the bottom
• On different areas within the chart
• Title usually across top
• Start by selecting the data the chart will
be based on
13
Want to show the budget allocation change over the 4
month period
Select the data, click on the INSERT tab, then launch the
CHART dialog box
14
Initially shows recommended charts
15
Click for a
preview
16
Chart Tools tab is displayed and chart area is surrounded
by a border with resize handles. Can move chart by
clicking and dragging on the border.
17
CHARTS AND GRAPHS
• Chart consists of many elements
• Background
• Axes
• Legend
• Grid
• Labels
• The graphic may also be comprised of
many pieces that can be manipulated
• Individual slices in a pie chart
• To the right of the chart are quick
access buttons
18
Click on a chart element,
like the title, to select it
and then edit and format
to your liking
19
Control the chart’s
Elements, Style, and
Filter with these quick
access buttons
20
Add some data labels
21
Pretty ugly
However each individual label can be manipulated
22
Got rid of the legend at the bottom too
23
Style lets you quickly change the over all chart look
24
Or just the data area appearance
25
Filter lets you remove entire categories of info
26
Since the value of Rent didn’t change, took it out better
tell Mary’s story
27
As mentioned, can select individual chart components
and format
28
Made a couple more changes to tidy up the chart
29
Can click and drag to move and resize the chart
30
Or put chart in a separate sheet
Click move chart, specify new sheet and name
31
32
Changes in the SS data values immediately reflected in
the chart
33
CHART TYPES
• Different chart types have
advantages and disadvantages
• Pie chart
• Good for single period, relative
comparison between many value
• Column charts
• Good for showing change over time
for a single category
34
35
36
CHART TYPES
• Different chart types have
advantages and disadvantages
• Line charts
• Good for showing change over time
• Good for multiply categories of info
• Bar charts
• Most are just column charts turned sideways
• However, Gantt charts useful for showing
schedules
37
38
Gantt Chart
39
CHART TYPES
• Scatter charts
• Good for showing correlations between
two categories of data
• Area charts
• Sort of a filled in stacked line chart
• Good at showing multiple categories of info
changing over tiem
• Like Mary’s budget
40
41
CHART TYPES
• Combination charts
• Good for comparing two type of data that
have different scales
• Y axis usually has two different scales
42
ACTIVITY
QUIZ
QUIZ
END OF PRESENTATION
