Excel – advanced course – formulas and functions

Repetition of the use of formulas

We revise and learn new things about formulas.

• Relative, absolute and mixed reference
• 3D cell references
• Linking formulas
• Use consolidate instead of formulas
• Protect formulas, spreadsheets or workbooks

Review and track formulas

Tracking references in formulas.

• Show formulas
• Tracking formulas

Error checking of formulas

Excel displays various error messages.

• Automatic error checking, the green “corners”
• Error control rules

Use defined names

Sometimes it is easier to refer to a range with defined names than with a cell reference.

• Define and use a name
• Table name
• Workbook name
• Dynamic names

Using functions in formulas

The Excel functions bring more strength and power to the calculation.

• Insert functions, arguments and function help
• Encapsulated functions

Generalization of different functional categories

• Text functions for example: left, right, length, merge, textnum, and clear
• Logical functions for example. if, and, or, if and only if
• Lookup and reference functions such as lookup row, lookup column, xletaupp and unique
• Mathematical functions such as sum, sumom, sumomf, rounding and rounding up
• Statistical functions such as mean, max, min, number.om, number.omf and forecast.linear
• Financial functions such as payment and final value
• Date/time functions such as now, today, days360, net working days and weeks

Matrix formulas

• Perform multiple calculations on one or more objects in a matrix

Opportunities to apply formulas

• Conditional formatting with formulas
• Custom formulas in data verification

If time allows

• Use impact assessments
• Data tables – One/two dimensional analysis
• Destination search
• The problem solver
• The Senaria Manager
• Manage calculations for a range or table
• PivotTable