Excel Formulas, Functions, and Charts
A formula is the equation used to calculate values in a cell.
The = (equal sign) is the first character in a formula
You must have the = sign for Excel to recognize the formula.
A formula has two parts:
Operand—number or cell reference in the formula
Operator—tells excel what to do with the operand
=b3+5 means b3 and 5 are operands
+ is an operator and means to add the value in cell b3 to the number 5
Subtraction – 2. Priority Order**
Multiplication * 3. Left to Right
1. Relative—adjusts to its new location when copied
2. Absolute—do not change when moved
3. Mixed—contains both relative and absolute references
Create formulas by typing in operators and cell references or by clicking in cell references to enter. (Point & Click Method)
AutoSum button is identified by Greek letter Sigma (backwards E). Click on button to enter Sum formula. Excel will enter most logical row or column to sum. You can change it if needed.
Function Formulas are special formulas that do not use operators and operands to calculate a result. The perform complex calculations in Mathematics, Statistics, Trigonometry, Accounting, Logic, and Finance.
A function formula contains three parts
Sum is the function name
(d5:d10) is the argument--An argument is a value, cell reference, range, or text that acts as an operand. It is always enclosed in parentheses.
Functions can be entered into a cell by
A. type directy in the cell
B. Click on paste function button (Fx) on toolbar
C. Insert, Function
Some common functions include:
i. Square root =Sqrt(number) ie (49)
ii. =Round(number, number of digits) ie (B3, 2)
i. =Average (a3:a45) average of data in range
ii. =count(a3:a45) counts # of cells with data
iii. =max(a3:a45) maximum
iv. =min(a3:a45) minimum
v. =stdev(a3:a45) standard deviation
vi. =var(a3:a45) variance
i. =FV(rate, pmt, pv) future value of series of regular payments at a fixed rate for a specified number of periods
ii. =PV(rate,nper,pmt) present value of series of equal payments at a fixed rate for a specified number of payments
iii. =PMT(rate,nper,pv) displays a payment per period needed to repay a loan at a specified interest rate for a specified number of periods
i. =Date—determines date format
ii. =Now()—displays current date/time based on computer clock
1. Format Painter
2. Sorting Data
3. Insert/Delete Worksheets
4. Insert Pictures/Clip Art
5. Copy from Word to Excel/Excel to Word
A Chart is a graphical representation of data contained in a worksheet.
Types of Charts
1. Column—uses bars of varying heights to represent data
2. Line—similar to column but uses lines connected by a line
3. Pie—shows the relationship of a part to a whole
4. Scatter—XY charts show the relationship between two categories of data
To create a chart, choose the chart button on the toolbar or choose Insert, Chart.
1. Highlight data you want to include in your chart
2. Insert, Chart
3. Select type of chart desired
4. Click Next to follow Steps to select data, name chart titles, and other chart options
5. Select if you want the chart in the current sheet or separate.
6. Click Finish when done to view your chart
7. Double-click on various parts of chart to edit or choose from chart menu
8. Click on chart and hit delete key to delete a chart