Advanced Excel

Advanced Excel

Course content

Key functions in excel:

• Essential shortcuts
• Absolute & Relative referencing ($)
• Data validity and completeness
• Data formatting - Format cleaning, date formats, Coma and decimals
• Meaning of =, <, >, <>, <= =>
• Use of brackets in formula

Important Ribbon functions:

• Conditional formatting
• Find & Select
• Goal Seek
• Calculations
• Formula Auditing
• Circular reference & iterative calculations

Working with Charts and Templates:

• Inserting Chart
• Different formatting options and different type of chart
• Data sanitization & Data Analysis

Converting other format data to excel:

• Conversion into Excel
• Data formatting
• Text conversion function

Logical Functions of Excel:

• Writing conditional expressions (using IF)
• Using nested Ifs
• Using logical functions (AND, OR & NOT)
• Using lookup and reference functions (VLOOKUP & HLOOKUP)
• Limitations of V lookup and how to avoid it by using INDEX& INDEX

Special functions:

• Date and time functions (TODAY, NOW, YEAR,MONTH,DAY & DATEDIF)
• Text functions (TEXT,LEFT,RIGHT,MID & CONCATENATE etc.)
• Statistical function (COUNT,COUNTA,COUNTBLANK, COUNTIF, COUNTIFS, SUMIF & SUMIFS)

Pivot & Sub-totals:

• Creating subtotals, Multiple-level subtotals
• Creating Pivot tables
• Using Slicer in Pivot table
• Formatting and customizing Pivot tables
• Using advanced options of Pivot tables
• Pivot charts
• Get Pivot data function
• Consolidating data from multiple sheets and files using Pivot tables

Dos and don'ts of excel:

• Data filters
• File formats (Reduction in Sizes)
• Use of Paste special & Manual calculations
• Basic errors & resolutions