Excel and Advance Excel Training  Details

     INTRODUCTION TO MICROSOFT EXCEL

·         Advantages of Microsoft Excel

·         Quick facts and figures about Microsoft Excel

·         What's new in Microsoft Excel

·         Portability features with earlier versions

·         Application Areas of Microsoft Excel

·         Microsoft Excel and Analytics

·         Microsoft Excel and BIG Data

·         Excel Beyond Excel

 

UNDERSTANDING EXCEL NAVIGATION

 

•      Understanding Excel Navigation System

•      Transformation from Menus to Ribbon

•      Overview of Ribbon, Groups & Tabs

•      Customizing Excel Ribbon

•      Importing and Exporting Customization Settings

•      Understanding Contextual Tabs

•      Overview of Quick Access Toolbars

•      Customizing Quick Access Toolbar

•      Navigating Back Stage View

•      Understanding Modules of Back Stage View

•      Excel Keyboard Shortcuts

 

WORKING WITH FORMAT CELLS

·         Format Cell Basics

·         Navigating Format Cells Dialog Box

·         Understanding Excel Data Types

·         Understanding Custom Formatting

·         Transformation from Data Type to Custom

·         Creating custom Date Formatting

·         Creating custom Time Formatting

·         Creating custom Number Formatting

·         Creating custom Text Formatting

·         Conditional Custom Formatting

·         Custom Color Formatting

·         Format Cells to multiple places

·         Keyboard Shortcuts for Cell Formatting

·         A walkthrough to Formatting Options

 

 

 

 

EXCEL TIME SAVING UTILITIES

·         Working with Paste Special - Magical

·         Splitting Data using Text to Columns

·         Sorting Data with Basic & Custom Options

·         Working with Excel Comments

·         Working with Goto Options – Magical

·         Working with Freeze Panes

·         Working with Grouping & Subtotals

CONCEPT OF CELL REFERENCING

·         Introduction to Cell Referencing

·         Importance of Cell Referencing

·         Understanding Relative Referencing

·         Understanding Absolute Referencing

·         Understanding Mixed Referencing

·         Common challenges while using Referencing

 

WORKING WITH FUNCTIONS & FORMULAS

·         Difference between Functions & Formulas

·         Concept of Nested Formulas

·         Formula Auditing

·         Various Calculation Modes and How to use them

·         Circular References – What are they?

·         Concept of Wild Cards

·         Concept of important Functions

·         Combinations of the above Functions

·         Concept of Precedents & Dependents

 

NAME MANAGEMENT IN EXCEL

 

·         Introduction to Name Management in Excel

·         Overview of Name Box

·         Creating, editing and deleting Name

·         Understanding Name Scope

·         Defining Static and Dynamic List using names

·         Displaying Name Map in Excel Worksheet

·         Creating Single and Multidimensional Arrays

·         Use of Names in advance Reports

·         Common challenges in Name Management

·         Use of Name Manager for Extracting Data - The Magical

 

EXCEL SECURITY AND DATA BACKUP

 

·         Excel Security – Protecting a Selected Range

·         Excel Security – Protecting an Entire Sheet

·         Excel Security – Allow users to edit Range

·         Excel Security – Protecting Workbook Structure

·         Excel Security – Full Protection of Workbook

·         Excel Security – Partial Protection of Workbook

·         Working on Excel Data Backup

 

DATA ANALYSIS USING PIVOT TABLES

 

·         Why PivotTables?

·         Structuring Your Source Data

·         Inserting Your First PivotTable

·         Navigating the Field List

·         PivotTable Options: "Analyze" & "Design"

·         Selecting, Clearing, Moving & Copying Pivots

·         Refreshing & Updating Pivots

·         Generating New Report from PivotTable

·         Managing Grand Totals

·         PivotTable Subtotals

·         Dealing with Growing Source Data

·         Removing & Reviving Data from Cache

·         How PivotTables Works?

·         Format Cells vs Number Format

·         PivotTable Number Formatting

·         Automatically Formatting Empty Cells in PivotTable

·         Understanding Table Layouts & Styles

·         Customizing Headers & Labels

·         Introduction to Data Sorting in PivotTable

·         PivotTable Sorting Options

·         Filtering Data in PivotTables

·         Filter using Wild Cards

·         Using Slicers & Timelines to Filter Data

·         Controlling multiple Pivots using Slicers and Timelines

·         Working on PivotTable Grouping

·         Date Grouping in PivotTable

·         Grouping in Numbers and Text

·         Value Summarization Modes

·         Working on "Show Values As" Calculations

·         Performing custom calculations using Calculated Fields

·         Calculations in Pivots vs. Raw Data

·         Inserting a Calculated Item for combined calculation

·         The Solve Order & List Formulas Tools

·         Introduction to PivotCharts

·         Applying Slicers & Timelines to Multiple Charts

·         Building dynamic dashboards using Slicer and Timeline

·         Consolidating data from multiple sheets in seconds

·         Consolidating data from multiple files in seconds

·         Creating custom Page in PivotTable

·         Splitting Data to Multiple Reports from huge data

·         Developing Dynamic Tutorial using Slicer and Pivot

·         Challenges in PivotTables - What Next?

 

WORKING WITH CHARTS IN EXCEL

 

·         Introduction to Charts

·         Understanding elements of Chart

·         Activating Chart Elements

·         Major charts used in Industry

·         Visualizing data using Column Charts

·         Visualizing data using Bar Charts

·         Visualizing data using Line Charts

·         Visualizing data using Pie Charts

·         Creating Combination Chart

·         Changing Chart Themes

·         Using Picture in Charts

·         Introduction to Tiny Charts – Sparklines

·         Creating Column Sparklines

·         Creating Line Sparklines

·         Creating Win-Loss Sparklines

·         Introduction to Advanced Charting

 

DATA ANALYSIS WITH CONDITIONAL FORMATTING

 

·         Introduction to Conditional Formatting

·         Understanding Static vs Dynamic Formatting

·         Text based Conditional Formatting Rules

·         Numbers based Conditional Formatting Rules

·         Date based Conditional Formatting Rules

·         Dealing with Unique and Duplicates

·         Top and Bottom Rules in Conditional Formatting

·         Data Bars, Icon Sets, Color Scales

·         Conditional Formatting based DASHBOARDS

·         Conditional Formatting using Basic Functions

·         Use Single Formatting with Multiple Conditions

·         Attendance Trackers - Case Study

·         Complex Formulas in Conditional Formatting

·         Setting Priorities on Conditional Formatting

·         Developing Smart Signaling System

·         Reusing Conditional Formatting

·         Clear Conditional Formatting Rules

 

WORKING WITH EXCEL ARRAY FORMULAS

 

·         Introduction to Arrays

·         Introduction to Excel Array Formulas

·         Why and when to use Array Formulas

·         The internal logic behind Arrays

·         Concept of Logical Operators in Array Formulas

·         The CSE Method

·         Comparison-Matrix with Excel conditional functions

·         Array Formulas using basic Excel Functions

·         How to create complex Array Formulas?

·         How to Master Array Formulas - What Next?

 

DATA VALIDATION TO RESTRICT INVALID DATA

 

·         Introduction to Data Validation

·         Creating basic Data Validation

·         Creating Data Validation for Test

·         Creating Data Validation for Numbers

·         Creating Data Validation for Date and Time

·         Creating basic drop-down list

·         Creating dynamic list using Data Validation

·         Creating Basic Dependent List

·         Creating Multilevel Dependent List

·         Use of Basic Formulas in Data Validation

·         Creating complex validation using Formulas

·         Hacks of Data Validation

·         Applying Data Validation to Multiple Places

·         Using Custom Error Alerts in Validation Conflicts

·         Using Data Validation as a Smart Comments

·         Steps to remove Data Validation

·         Highlighting Invalid Data

·         Advance Filter Modes

·         Implementing AND, OR, NOT in Advance Filter

·         Advance Filter with basic calculation

·         Using Excel Functions in Advance Filter

·         Using Advance Filter as a Lookup Tool

·         Extracting unique list with Advance Filter

·         Clearing Filters

 

WORKING WITH EXCEL DATA FILTER

 

·         Introduction to Excel Filter

·         Data Filtering Techniques

·         Introduction to Auto Filter

·         Auto Filter Checkbox

·         Filter using free text

·         Using predefined modes in Text Filter

·         Using predefined modes in Number Filter

·         Effectively using Custom Filters

·         Using Color Filtering

·         Conditional Formatting Icons Filtering

·         Filter using wild Cards

·         Refreshing modified data while keeping Filter Intact

·         Challenges in Auto Filter

·         Introduction to Advance Filter

·         Exploring Advance Filter Dialog Box

 

PREVENTING WRONG DECISIONS USING TABLE

 

·         Introduction of Excel Tables

·         Time Saving features & Calculation Logic of Excel Tables

·         Format As Table

·         Understanding Table Formatting Options

·         Multiple Filters In The Same Sheet

·         Total Row, Ability To Select Type Of Summary

·         Structured References – What Are They?

·         Advantages Of Tables

·         Convert Table To Named Range

·         Compatibility Of Tables With Earlier Versions

·         Limitation of Excel Tables

 

ANALYZING DATA WITH WHAT-IF ANALYSIS

 

·         Introduction to What-If Analysis

·         Why and when to use What-If Analysis?

·         Working with Goal Seek for Reverse calculation

·         Multiple case studies on Go l Seek

·         Challenges in Goal Seek

 

Demo - Free

Rs. 1999 /-

 

Ø  Benefits:

 

1.    Placement  Assistance

3.    A boost in confidence and recognition

 

4.    Promotion Prospects

Ajay Gupta,

Trainer

MBA  (14 Yrs. Experience)

HR Consultant & Advisor

Mobile: 91-9560581595

Feel free to call or Whatsapp

Email id : - idajaygupta@gmail.com

#Rohtak #training #Excel #advanceexcel #HRTraining