Advance Excel Training

Advanced MS Excel Training 
Course Content
This course is specifically designed for professionals and graduate students who need to learn about the advanced ways of using spreadsheets for complex issues. This knowledge packed workshop starts with an introduction to MS Excel. Tentative list of topics which are covered in the courses.


Advanced MS Excel - Topics Covered
  • Basics:- Reference, Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split, Goal Seek
  • Mathematical Functions:- Sum, max, min, Sumif, Sumifs, Count, counta, Countifs, Countblank, Average, Averagea, Subtotal, Round Roundup, Rounddown,
  • Lookup Functions:- Vlookup, Hlookup, Pivot Table, Index, Match, Indirect
  • Date & Time Function:- Day, Month, Year, Edate, Eomonth, Network days, Hour, Minute, Second, Now, Today, Time
  • Conditional functions:- if, and, or, iferror
  • Text Functions: – Concatenate, Find, Search, Substitute, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Large, Small
  • Charts:- How to Make Dynamic Charts, Bar Charts, Pie Charts, Scatter Chart, Line Chart, and Column Chart.
  •  


• Essential Shortcuts

• Absolute & Relative Referencing ($)

• ROUND (), SUMPRODUCT (), etc.

Data Analytics

• Using SUBTOTAL() with Filtered data

• SUMIF, COUNTIF

Pivot Tables

• Computations %, Sum, Max, Min, Average, Count, etc.

• Generate Multiple Reports

Data Lookup

• VLOOKUP() & HLOOKUP()

• VLOOKUP() with MATCH(), INDEX() & MATCH()

Excel Modelling

• INDIRECT() with Cell Range Naming

Logical Statements

• IF(), Nested IFs(), AND(), OR(), IFERROR()

• ISNUMBER(), ISTEXT(), ISBLANK()

Working with Dates


Data Cleaning

• LEFT(), RIGHT(), MID()

• LEN(),TRIM(), SUBSTITUTE(), VALUE()

• UPPER(), LOWER(), PROPER()

• Using “&” for concatenating or joining strings / cell values

• Find & Replace (Using wildcard character – Asterisk *)

• Go To (Special) with Ctrl+Enter trick

• Text to Columns with advanced tricks

• Remove Duplicates

MIS Reporting

• Data Consolidation

• Automatic Row Wise Sub-Total

• Conditional Formatting – Blanks, Duplicates, Cell Values, Formula based, etc)

• File Password & Cell Protection


Dashboard Techniques

• Data Validation (List)

• Cell Range Naming, Grouping


What IF Analysis

• Data Tables

Macros: Overview

• Concept

• Macros Recorder, Record & Run


OTHER DETAILS


HR Practical Session With Advanced MS Excel - Topics Covered
  • CTC Worksheet
  • Attendance Sheet:
  • Leave Register:
  • Overtime Calculation:
  • Salary Sheet:
  • Pay Slip:
  • ESI &EPF Report:
  • LWF & PT Report:
  • Bonus & Gratuity Calculation:
  • Income Tax Calculation:
  • Full & Final Settlement:
  • Annual Returns:
  • Contract Renewal Schedule:
  •  

 

0

Year Of Experience

0

Trainees Enrolled

0

Batch Completed

Placement Track Record

Unlimited Interview

ISO 9001:2015 Certified Company