Microsoft Excel Course – Module 4 Advanced Formulas And Functions

Is it right for me?

Our Excel Module 4 - Advanced Formulas and Functions course is designed for experienced users of Excel who want to develop their skills in writing formulas and using advanced Excel functions and techniques.

You should be confident using Excel and should be able to write basic formulas and functions.

What will I get out of it?

By the end of the course you will be able to:

• Use an extensive range of logical, text and date functions
• Create simple array formulas
• Cross reference data from other worksheets using LOOKUP, INDEX and MATCH functions
• Use IS functions to test cell values
• Create nested conditional formulas linking data between worksheets
• Set advanced conditional formatting and data validation
• Use the What If analysis tools

Versions available

2003, 2007, 2010

Related courses in our range

• Excel Module 3 - Data Analysis
• Excel Business Intelligence and Dashboards
• Excel Pivot Table Specialist
• Excel Macros Specialist
• VBA in Excel Part 1

What does it cover?

Working with text

• Splitting and merging
• Extracting data using text functions
• Formatting text using functions

Working with dates and times

• Formatting dates and times
• Date and time functions
• Calculating date and time differences

Using functions to round numbers up and down

• ROUND
• ROUNDUP
• ROUNDDOWN

Working with logical functions

• Using logical statements to test a value (IF, AND, OR)
• Combining more than one function

Calculating values based on given criteria

• SUMIF
• COUNTIF
• Conditional Sum Wizard

Array formulas

• Using array formulas
• TRANSPOSE
• Calculating a conditional average

Lookup and reference functions

• Using VLOOKUP and HLOOKUP
• Using INDEX and MATCH

Using IS functions to test value types in cells

• ISBLANK
• ISNUMBER
• ISTEXT
• ISERROR

Interactivity between worksheets

• Creating data validation lists from ranges
• Nested lookups
• Conditional linking
• Creating graphs on variable data

Nested conditional functions

• The forms toolbar (2003) / tab (2007 and 2010)
• Adding check boxes
• Adding option buttons
• Setting properties
• Nesting logical and statistical functions

Advanced conditional formatting and data validation

• Conditional formulas
• Colour banding
• Finding cells with conditional formatting
• Finding and preventing duplicate values

Forecasting with What If analysis tools

• Using Goal Seek
• Using Solver

Using the Solver

• Installing the Solver add in
• Solver steps
• Adding constraints
• Running a Solver model

Scenarios

• Creating scenarios
• Producing scenario reports

Data tables

• Creating one and two input data tables

Other functions

• PMT
• MOD
• ROW