1. Copy formulas.
2. Build formulas containing relative, absolute, and mixed references.
3. Review function syntax.
4. Insert a function with the insert function dialog box.
5. Search for a function.
6. Type a function directly in a cell.
7. Use Auto Fill to fill in a formula and complete a series.
8. Enter the If logical function.
9. Insert the date with the today function.
10. Calculate monthly mortgage payments with the PMT financial function.
Important Points:
1. Functions are built in programs with the following syntax:
Function(argument, optional argument) Example: sum(c1:c15) sum is the name of
the function: c1:c15 is the range of cells that make up the argument. The
argument is what the function works on.
2. 350 functions built into excel: Database, Date and Time, Engineering,
Financial, Information, Logical, Lookup and reference, math and trigonometry,
statistical, and text and data functions are the categories:
| Functions | Description |
| Average(cell ref or sum) | Calculates the average of a collection of number who are in cells |
| Count(cell ref) | Calculates the total number of values that are numeric, text or cell ref. |
| Max(cell ref or num) | Calculates the maximum number of a group. |
| Median(cell or num) | Calculates the median (center number) |
| Min(cell or num) | Calculates the minimum number. |
| Round(number,num-digits) | Rounds a number to specified number of digits. Number represent the number and num-digits represent the number of places to be rounded to. |
| Sum(cell or num) | Calculates the sum of a collection of numbers. |
3. Copying and Pasting a cell or range. Select cell or range, click
copy, select the cell or range you want to copy to, click paste.
4. Relative reference is a cell reference that will change relative to
the cell copied to.
5. Absolute reference is a cell reference that will not change when
copied. indicated with $.
6. Mixed reference contain both in the formula when copied.
7. F4 is your absolute reference key or you can type the $ directly into your
formula.
8. Other paste options:
| Option | Description |
| Formulas | Pastes the formula(s), but not the formatting, of the source cell range |
| Values | Pastes the value(s), but not the formula(s) or formatting, of the source cell range |
| No Borders | Pastes the formula(s) and formatting of the source cell range, but not the format f cell range borders. |
| Transpose | Pastes the formula(s) and formatting of the source cell range, except changes the orientation so that row in the source cell range become columns, and columns become rows. |
| Paste Link | Pastes a link to the cell(s) in the source cell range, including the formatting used |
| Paste Special | Opens a dialog box displaying more paste options. Let you specify what part of cell you want to paste and specify what mathematical operation to be performed. |
9. Changing Magnification - click view, click zoom, click option
button for percent of magnification, click ok or click the zoom list arrow and
click the percent you want.
10. Insert Function click the formulas tab on the ribbon,
select a category, select function, enter the arguments, click ok.
11. Fill handle-Auto Fill - the small black in the lower-right corner of
a selected cell or range is called the fill handle which enables you to copy the
contents of selected cells simply by dragging the fill handle.
12. Auto Fill options button - Copy Cells, Fill Formatting Only, Fill
without Formatting.
13. Auto Fill Series
| Type | Initial Selection | Extended Series |
| Values | 123 2,4,6 |
4,5,6 8,10,12 |
| Dates and Times | Jan January Jan, Apr 15-Jan, 15-Feb 12/30/2005 12/31/2005,1/31/2006 Mon Monday 11:00 AM |
Feb, Mar, Apr,... February, March, April Jul, Oct, Jan, ... 15-Mar, 15-Apr,15-May, ... 12/31/2005,1/1/2006,1/2/2006 2/28/2006, 3/31/2006, 4/30/2006 Tue, Wed, Monday, .... Tuesday, Wednesday, Thursday, ... 12:00 PM, 1:00 PM, 2:00 PM |
| Patterned Text | 1st Period Region 1 Quarter 3 Qtr3 |
22222sd Period, 3rd Period, 4th Period Region2, Region 3, Region 4, ..... Quarter 4, Quarter 1, Quarter 2, .... Qtr4, Qtr1, Qtr2, ...... |
14. Date and Time Functions: Remember all functions begin with =.
| Function | Description |
| Date(year, month, day) | Creates a date value for the date represented by the year, month, and day arguments. |
| Day(date) | Extract the moth number from the date value, where 1= Jan, 2=Feb |
| Month(date) | Extracts the month number from the date value, where 1=January |
| Year(date) | Extracts the year number from the date value |
| Weekday(date,[return_type]) | Calculates the day of the week from the date value, where 1=Sunday |
| Now() | Displays the current date and time |
| Today() | Displays the current date |
15. Financial functions are widely used in the world of business.
| Function | Description |
| PMT(rate,nper,pv,[fv=0],[type=0]) | rate is interest rate per payment period- nper is total number of payments - pv is the present value of the loan and must be entered as a negative number -fv indicates the future value of the loan with default of 0 - type specifies when payment is made. |
| PV(rate,nper,pmt,[fv=0],[type=0]) | Calculates the present value of a loan or investment based on periodic, constant payments. |
| NPER(rate,pmt,pv,[fv=0],[type=0]) | Calculates the number of periods required to pay off a loan or investment |
| RATE9nper,pmt,pv,[fv=0],[type=0]) | Calculates the interest rate of a loan or investment based on periodic, constant payments. |
16. Logical function test or evaluates whether a condition in the
workbook is true or false.
17. The if function =if(logical_test, value_if_ true, value_if_false) is
a selection control structure in programming.
Example: =if(a1>60,"You passed","You failed")
18. Comparison operators
| > | greater than | A > B |
| < | less than | A < B |
| >= | greater than or equal to | A >= B |
| <= | less that or equal to | A <= B |
| = | equal to | A = B |
| <> | not | <> A |
19. Loans - Principal is the amount of money being loaned, iinterest is the amount charge for lending the monty. Simple interest is the interest paid is equal to a percentage of principal for each period that the money has been lent. Compound interest in which the interest paid is calculated on the principal and any previous interest payments that have been added to the principal.