Microsoft Excel Advanced

You will learn how to find a value with VLOOKUP, create and format Pivot Tables, record Macros and more.  

Objectives:

• Use Excel quick access toolbar and cell styling tools
• Use key board shortcuts to improve speed and efficiency 
• Understand logical functions and use them for reports
• Learn and apply various advance excel functions
• Learn to create smart charts for data presentation
• Learn to create pivot table and analyse large data base
• Use VLOOKUP and HLOOKUP
• Use Excel formulas for increasing productivity
• Learn to record and edit macros 

Contents:

Conditional Formatting

• Formatting Cells Containing Values
• Clearing Conditional Formatting
• Working with Data Bars, Color Scales and Icon Sets
• Creating and editing Sparklines

Sorting Data

• Performing an Alphabetical Sort
• Performing a Numerical Sort
• Sorting on More Than One Column
• Sorting by Vertically & Horizontally

Filtering Data

• Understanding Filters
• Applying, Using and Clearing a Filter
• Creating Compound Filters
• Creating Multiple Value and Custom Filters
• Using Wildcards

Filling Data

• Understanding Filling
• Filling a Series
• Creating, Modifying and Deleting a Custom Fill List

Pivot Tables

• Understanding Pivot Tables
• Creating a Pivot Table Shell
• Filtering a Pivot Table Report
• Clearing a Report Filter
• Formatting a Pivot Table Report
• Understanding and Creating Slicers

Dynamic Table

• Converting data into table
• Automation calculation in table
• Converting table to normal range
• Using table in Charts & Vlookup

Logical Functions

• Understanding Logical Functions
• Using IF to Display Text
• Using IF To Calculate Values
• Nesting IF Functions
• Using IFERROR, AND and OR Functions

Formula Referencing 

• Absolute and Relative Referencing 
• Problems with Relative Formulas
• Creating Absolute References

Charting Techniques

• Adding Chart and Axes Titles
• Positioning the Legend
• Show Data Labels and Data Table
• Modifying The Axes
• Creating a combination chart
• Creating 2 Axis chart

Creating Charts

• Choosing The Chart Type
• Creating A New Chart
• Working with an Embedded Chart
• Resizing and changing Chart type

Advance Validation

• Creating drop down in cells
• Restricting values from list only
• Creating error messages
• Creating dependent lists

Text options

• Text to Column, SubTotal and Paste Special
• Group / Ungroup
• Sharing workbook

Macros

• Recording & Running Macros
• Creating button to run the macro
• Assigning shortcut to macro
• Deleting Macros
• Relative reference in Macros

Formulas

• Count, CountA, CountIF & CountBlank
• Sum, SumIF & SumIFs
• Networkdays
• Today, Now, Trim and Concatenate functions

Lookup Functions

• Normal Vlookup & Hlookup
• Vlookup with IFError
• Vlookup from Multiple Sheets using IFError
• Using IF Conditions and Multiple criteria in Vlookup

File / Data Security

• Open & Modify file password
• Protect sheet from deletion, data copy or protect few cells, rows or columns