What will you learn

Basic and Advance Excel

Complete Practical training with live case study

All your queries will be resolved on what's app or face to face

Life time validity to revise your learning

Introduction of Excel

• What is excel

• Cells

• Row

• Column

• Number of Rows

• Number of Columns

• Referencing (Relative and Absolute)

• Sheet or Tab

• Types of tasks perform in excel

• Limitations of excel

• Address Bar

• Formula Bar

• Title Bar

• Status Bar

 

•  

Basic Formatting

• Cell Formatting

• Cell Value Formatting

• Tab Formatting

• Format Painter

• Font Change

• Font Size Change

• Apply Bullets

• Apply Numbers

• Alignment

• Cells Merging

• Wrap Text

• Indents

• Headings

• Find Word

• Replace Word

• Cut, Copy, Paste

• Paste Special

• Remove and Add Decimal

• Special Cells

• Transpose data in rows and columns

• Insert, Delete Rows and Columns

• Hide and Unhide Sheets

• Very Hide Sheet

 

•  

Most Useful Shortcuts

• Ctrl+C= Copy Data

• Ctrl+V= Paste Data

• Ctrl+E+S= Paste Special Window

• Ctrl+X= Cut Data

• Ctrl+Z= Undo last action

• Ctrl+Y= Redo last action

• Ctrl+F= Find any word

• Ctrl+H= Replace any word

• Ctrl+B= Bold

• Ctrl+U= Underline

• Ctrl+I= Italic

• Ctrl+;= Current system date

• Ctrl+Shift+;= Current system time

• Ctrl+Up Arrow=Top of the Row

• Ctrl+Down Arrow= Bottom of the Row

• Ctrl+Right Arrow= Last of the data column

• Ctrl+Left Arrow= Most left of the data column from right to left

• Ctrl+Shift+Right Arrow= Select up to last column data

• Ctrl+Shift+Down Arrow= Select up to last row data

• Ctrl+Space= Select Entire column

• Shift+Space= Select Entire Row

• Shift+F11= Add New Sheet

• F7= Spelling and Grammar check

• F2= Pointed Cell in edit mode

• F4= Freeze your reference

• Alt+H+O+I= Autofit Column

• Alt+H+O+A= Autofit Row

• Alt+H+O+W= Adjust column width

• Alt+H+O+H= Adjust row height

 

 

 

 

Conditional Formatting

• Highlight Cells Rules

• Top/Bottom Rules

• Data Bars

• Color Scales

• Icon Set

• New Rules

• Highlight Duplicate Values

• Highlight Unique Values

• Highlight Duplicate Values by Function Use

• Highlight Duplicate Values on Multiple Conditions by Function

• Highlight Specific Data which Match from Drop Down Cell Value

• Highlight Unique Values by Function Use

• Highlight Specific Date or Month or Year

• Manage Rules or Change Your Existing Rules

 

•  

Sorting and Filtering Data

• Sort data in ascending order

• Sort data in descending order

• Sort data on single column

• Sort data on multiple columns

• Sort data on Row (Left to Right)

• Custom Sorting (Sort data as you wish)

• Apply Filter data on Single Column

• Apply Filter data on Multiple Columns

• Custom Sorting (Greater than, Less than, Equal to, etc…)

• Advance Filter

• Filter data on different place.

• Filter unique value on multiple columns

 

•  

Data Validation

• Validate column or columns by specific value

• Validate row or rows by specific value

• Validate row and column by conditions

• Date Validation, Decimal value validation and Text validation

• Listing and Depending listing

 

•  

Charts

• Column chart

• Line chart

• Pie chart

• Bar chart

• Area chart

• Scatter chart

• Bubble chart

• X axis, Y axis and Secondary axis

• Adjust value in axis

• Set major and minor unit

• Set minimum, maximum value

• Dynamic chart

 

•  

Pivot Table and Pivot Charts

• Insert pivot table

• Set value in Filters, Columns, Rows, Values section.

• Use of Defer Layout Update and Update

• Filter data into pivot table by slicer.

• Make own calculated field

• Change data source

• Dynamic Pivot table

• Classic and Tabular pivot layout.

• Configure Power Pivot

• Insert data into power pivot window

• Create Pivot table on huge data when data exceed excel limit.

 

•  

Get External Data and Connections

• Import data from other sources

• Create connection

• Create SQL query by Microsoft Query

• Refresh connection

• Create data model

 

•  

Most Useful Functions and Formulas

• Vertical data extract by VLOOKUP and VLOOKUP MATCH.

• Compare data using VLOOKUP.

• Horizontal data extract by HLOOKUP and HLOOKUP MATCH.

• Extract data through INDEX MATCH.

• Count range of values by COUNTIF on single condition

• Count range of values by COUNTIFS on multiple conditions

• Sum range of values by SUMIF on single condition

• Sum range of values by SUMIFS on multiple conditions

• Average range of values by AVERAGEIF on single condition

• Average range of values by AVERAGEIFS on multiple conditions

• Pickup Maximum value on each conditions by ARRAY function

• Pickup Minimum value on each conditions by ARRAY function

• Find any character in word by FIND and SEARCH functions

• Replace number of characters by REPLACE and SUBSTITUTE functions

• Extract number of characters from left using LEFT function

• Extract number of characters from right using RIGHT function

• Extract number of characters from mid by MID function.

• Merge multiple cells values using CONCATENATE function.

• Merge multiple cells value using &

• Subtotal values by SUBTOTAL and AGGREGATE functions

• Change number and date format by TEXT function

• Apply logic by logical function (IF, AND, OR, NOT)

• Calculate aging by NETWORKDAY and NETWORKDAY INTL functions

• Take current day and current daytime by TODAY and NOW functions

 

Q & A and Doubt

Complete Practical Training