Description
Learning Outcome
By the end of the session, participants will be able to:
• 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
Course Content
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
