Harnessing the Power of Formulas and Functions in Excel (2016)IT Short Courses

Introduction

One of Excel’s key strengths is its power of formulas and functions where it can display data in a format that is suitable for reporting.

Key Takeaways

This course takes the Excel user to a realm where he/she will be introduced to cell manipulation as well as discover techniques of fine-tuning formulas and the introduction of some Advanced Excel functions relating to text, logical and information that assist in providing the necessary information or changing the representation of how data is presented.

Who Should Attend

Those needing to extract information from their worksheet models as well as how to tidy up their data in a format that is acceptable for reporting are especially encouraged to attend.

Programme Outline

Module 1: Cell Manipulation in Excel

• Applying Conditional Formatting to a Range
• Filling a Range by using the Fill Handle & Flash-filling a range
• Creating a Series using Excel Series command
• Using the Go To Special Dialog Box
• Displaying Worksheet Formulas using Ribbon command & using a function
• Printing Worksheet Formulas
• Converting a Formula to a Value
• Apply Paste Special Function

Module 2: Building Basic Formulas and Functions in Excel

• Understanding Excel Formulas
• Creating an Excel Formula
• Understanding Relative, Absolute and Mixed Cell Referencing
• Using Relative, Absolute and Mixed Cell Referencing in Formulas
• Understanding Excel Functions
• Creating an Excel Function through various methods
• Using the AutoSum
• Using some common Excel Functions
• Understanding Excel’s Formula-Related Errors
• Apply Automatic Checking for Possible Errors in Formula Usage

Module 3: Working with some Advanced Excel Functions

• Logical and Information Functions
• Applying a Logical Function in Excel
• Getting Data with Information Functions
• Text Functions
• Working with Characters and Codes
• Converting Text
• Formatting Text
• Removing Unwanted Characters from a String
• Extracting a Substring
• Searching for Substrings
• Substituting One Substring for Another

Methodologies

Not only a good foundation in Excel is essential for this course but learners preferably should have attended the Level 3 Beyond Advanced course before enrolling for this course. Having attended the Level 2 Advanced course is advantageous.

A combination of lectures, explicit teaching, practical demonstrations and peer teaching is applied for this course.

Experiential learning approach is adopted where the learners will be required to consider the experience as a learning process joined with critical reflection which further qualifies for this theory to be applied for this course because, at each segment, the learners are introduced to some form of experience after which they are asked to reflect on these experiences before sharing and putting ideas into practice.