Advance Excel

Unit I: Remembrance of the key functions related to basics and Intermediate

Unit 2: Formula and Function

• The Syntax Of IF and nesting the IF Statement
• Use The AND & OR Operator To Reduce Quantity Of Nested IFs
• The NOT Operator Within AND &OR Statements
• SUMIF, COUNTIF and AVERAGEIF
• Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions

Unit 3:  Performing Lookups

• VLOOKUP, HLOOKUP and Nested LOOKUP’s
• Looking For A Near Match In A Lookup and Checking For Missing Data In A Lookup
• Extending The Size Of A Lookup Table

Unit 4:  Data Functions

• The MATCH, INDEX, CHOOSE Lookup Function
• How To Stop Nonexistent Row Or Column Lookups In INDEX
• Sorting Data
• Database Functions
• Sub-totaling
• Outlining and Grouping
• Filtering
• Advanced Filter
• Remove Duplicates
• Data validation
• Conditional Formatting with Custom
• Macros

Unit 5:  Math Functions and Arrays

Math Functions

• Working With TIME and DATE calculation and function
• Rounding To Fractional Values
• MOD For Working Out Remainders
• Generating A Random Number and Pick A List Item At Random
• Calculating Loan Repayments and Investment calculations Using PMT
• Working out different parts of a loan calculation
• Working Out Depreciation

• Array and an Array Formula
• Conditional Evaluation In An Array Formula
• The Very Clever TRANSPOSE Array Function
• Paste specials

Unit 6: Functions for Working with Text

• LEN And TRIM Two Very Useful Text Functions
• Using LEFT And RIGHT For String Extraction and use of FIND and MID together to extract parts of strings
• Build Strings From Multiple Cells and changing the case of text in cells
• REPLACE And SUBSTITUTE In Action
• Formatting Numeric Values With A Text String Using TEXT and extracting the values from the TEXT functions
• Flash fill

Unit 7:Spark lines and Other Useful Functions

Spark lines

• Creating A Sparkline and changing Sparkline looks
• Dealing with Empty Cells
• Comparing One Sparkline To Another by Altering Vertical Scale
• Removing Sparkline From A Sheet

Outlining

• Creating An Outline Automatically and Manually
• Manually Removing Data From An Outline and removing outline itself from a worksheet
• Adjusting A Grouping Created By Automatic Outlining
•  

Custom Views

• Creating A Custom View Of A Worksheet
• Changing From One Custom View To Another
• Editing and deleting a custom view

Other Useful Functions

• Welcome To IS Functions
• Error Checking With ISERR ISERROR ISNA And IFERROR
• The OFFSET Formula Explained
• Use The INDIRECT Function To Build Dynamic Formulas
• Dealing With INDIRECT Errors

Unit 7:Analytical Tools for Scenario analysis

• Goal Seeking
• Data Tables
• Solver
• Setting up a scenario and entering values
• Display the scenario values, edit the values, merge scenarios from different sheets and delete scenario

• Forecasting Trends and use of Trend lines in Charts
• Getting a Summary of all scenarios
• Using Goal Seek and Solver To Carry Out What If Analysis
• Activating The SOLVER Add In
• Add Constraints Into A SOLVER Problem

Unit 8: Auditing and Troubleshooting Formulas

• Tracer Arrows
• Tracing Precedents And Dependents
• Remove Tracer Arrows
• Error Checking Using Auditing Tools
• Step By Step Processing Of Formula To Help With Troubleshooting
• Utilizing The Watch Window

Unit 9: Pivot Tables and Pivot Charts

Pivot Tables

• Pivot Table and steps to create it
• Rearranging Fields In A Pivot Table
• Changing The Math Of The Data Summary
• Number Format Control Of The Summary Area
• Creating A Second (Or More) Pivot Table On The Same Data
• Moving, sorting A Pivot Tables Columns and removing a pivot table
• Making Use Of The Report Filter Option
• Displaying Values As A Percentage
• Refreshing A Pivot Table Manually Or Semi-Automatically
• Applying Pivot Table Styles and creating custom style
• Copying A Pivot Table Style Between Workbooks
• Using More Than One Field In Row And Column Headings
• Disabling And Enabling Grand And Sub Totals
• Filtering Columns And Rows Within A Pivot Table
• Slicer Tool
• Connecting A Pivot Table To SQL Server

 Pivot Charts

• Creating A Pivot Chart and altering Chart Types Formats and Layouts
• Advanced Layout Control Of A Pivot Chart
• Filtering A Pivot Chart and hiding the Pivot Chart Elements
• Moving A Pivot Chart Between Sheets and deleting a pivot chart

Module 10: Goal Seek and Solver

• Using Goal Seek To Carry Out What If Analysis
• Using SOLVER To Carry Out What if Analysis
• Activating The SOLVER Add In
• Add Constraints Into A SOLVER Problem

Training Session Output:

At the end of the course, participants will be able to;

1. Being able to use advanced functions
2. Manage large list of data and work efficiently
3. Make work more attractive and manageable
4. Prepare and work on goal seek and solver