New Logo White mail

Microsoft Excel

Advanced formulas and functions

This course is for experienced users of Excel who wish to gain a wider understanding of how more complex formulas and functions can be used in their everyday tasks.

Objectives

Logical Functions

  • Testing cell contents using the If function

  • Nested Ifs

  • Multiple tests using If, And & Or

  • SumIfs, CountIfs And AverageIfs

 

Naming Ranges

  • Revision of absolute & mixed cell addressing

  • Naming a cell for use in a formula

  • The Name Manager

 

Lookup Functions

  • Lookup functions

  • Combining the Index And Match functions

 

Is Functions

  • IsError

  • IsText

  • IsNumber

  • IsBlank

  • Nesting If & Is functions

 

Date and Time Functions

  • Custom formats for dates & times

  • Today, Now

  • Year, Month, Week, Day, Weekday

  • Hour & Minute

  • Calculating dates

 

Text Functions

  • Text to columns tool

  • Left, Right & Mid

  • Concatenate

  • Upper, Lower & Proper

  • Trim & Find

 

Formula Auditing

  • Tracing errors

  • Tracing precedents & dependents

  • Showing formulas

  • Evaluating formulas

 

New Functions in 2013

  • IfNA

  • Days

  • IsFormula

  • FormulaText

  • Xor

 

Previous knowledge of Excel is essential as is the ability to construct basic formulas and functions.

Prerequisities

Content