Advanced Excel Training for Corporate's, Professionals & Students:
Advanced Excel Training course offered by Excel Prodigy. that makes you master in microsoft excel. We have conducted 750+ Corporate Training and Trained more than 22,500+ Professionals on Advanced Excel, VBA Macro, PowerPoint & Ms Office in India & International.
Advanced Excel Training Overview:
Our Advanced Excel training course will really show you how to make Excel work for you. The course is aimed at fairly experienced Excel users whom need to learn more complex functions, nesting, data manipulation and protect data using the security features. You will gain an insight into data tables and using excel advanced look up features to automate worksheets and analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information. Find out how you can utilise advanced ‘If’ statements.
Advanced Excel Training Overview:
Our Advanced Excel training course will really show you how to make Excel work for you. The course is aimed at fairly experienced Excel users whom need to learn more complex functions, nesting, data manipulation and protect data using the security features. You will gain an insight into data tables and using excel advanced look up features to automate worksheets and analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information. Find out how you can utilise advanced ‘If’ statements.
PivotTables methods of Advanced Excel Training Course
Creating, Formatting Simple PivotTables. Page Field in a PivotTable, Formatting a PivotTable, Creating/Modifying a PivotChart.
Logical Functions
IFs and Nested IF Functions. Using AND/OR/NOT Functions
Statistical Functions
Using The SUMIF/COUNTIF Functions. Using The AVERAGE/COUNT/LARGER/SMALLER Functions
Advanced Excel Training Course
Course Duration of Advanced Excel Training Course: 16 Hours (2 Days)
Absolute Referencing
Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References
LOOKUP Functions
The VLOOKUP/HLOOKUP Functions
Excel 2007 & 2010 Quick Overview
Difference between Excel 2003, 2007 and 2010, Use of Excel, its boundaries & features
Basic Formula of Advanced Excel Training Course
Formulae that Add/Subtract/Multiply/Divide • BODMAS/Formula Error Checking • The Sum Function and power BI
Pivot Tables – Advanced Excel Training Level
This is the important chapter of Advanced excel training level. It’s helps to adding new calculated Fields / Items. Changing the Summary Function, Consolidate Pivot table.
LOOKUP Functions – Advance Excel Level
These function one of the important chapter of Advanced Excel Training level. This help to MATCH with VLOOKUP Functions. INDEX & MATCH Functions, OFFSET/ INDIRECT functions.
Chart Data Techniques
The Chart Wizard. Chart Types, Adding Title/Legends/Lables, Printing Charts, Adding Data to a Chart, Formatting/Renaming/Deleting Data Series, Changing the Order of Data Series.Date/Time Functions – Advance Excel Training
In this functions one of the most important chapter in Advanced excel training course. This functions Using The Today. Now & Date Functions, Using The Datedif/ Networkdays/ Eomonth Functions, Using The Weeknum Functions, Using The Edate/ Networkdays. Intl/ Weekdays. Intl Functions.
Text Functions Using
The Mid/ Search/ Left/ Right Functions. Using The Trim/ Clean/ Upper/ Lower Functions, Using The Subsitute/ Text Functions, Using The Trim/ Clean/ Proper/ Dollar Function.
Validations
Input Messages / Error Alerts/ Drop-Down Lists. Conditional Formatting.ADVANCED
Advanced
Filters
Extracting Records with Advanced Filter. Using Formulas In Criteria
Advanced
Sorting
Sorting by Top to Bottom / Left to Right. Creating / Deleting Custom List. Sort by using Custom List…
Hyper / Data Linking
Hyper linking data, within sheet / workbook. Linking & Updating links between workbooks & application.
Math & Trigonometry Functions
Using SUMPRODUCT Functions. Using FLOOR/ CEILING/ MROUND/ MOD/ QUOTIENT Functions
Summarizing Data
Creating Subtotals/Nested Subtotals. SUBTOTALS Formula
Outlining
Creating/Working with an Automatic/Manual Outline. Grouping / Ungrouping
Consolidation
Consolidating Data with Identical/Different Layout
Using Auditing Tools
Displaying/Removing Dependent & Precedent Arrows. Evaluate Formula – Step IN / Step Out
Custom Views
Creating Custom Views. Displaying Custom Views, Deleting Custom Views
Sharing and Protecting Workbooks
Sharing Workbooks & Tracking Changes. Protecting sheets / workbooks / Files
Importing & Exporting Data
Importing Data from Database/ Text Files/ Web. Exporting Data, Changing External Data Range. Feel free to connect with our experts: Get a free Quote
Advanced Excel Training
Getting Started with MS Excel :
- Quick Access Tool Bar, The Ribbon, Status Bar
- Simple Calculation & Basic Functions
- Using AutoFill Option
- Quick Formatting & Keyboard Shortcuts
- Using Paste Special
Formula Referencing:
- Relative Reference
- Absolute Reference
- Mixed Reference
- Dynamic Reference
Working with Formula Error:
- Understanding different Error Types
- Fixing Formula Error
Most Powerful Options:
- Table
- Slicers
- Sparklines
- FlashFill
- Quick Analysis
- Recommended Chart & Pivot
Analyze Data with Lookup Functions:
- VLOOKUP, HLOOKUP
- NESTED LOOKUP
- VLOOKUP, MATCH
- INDEX, MATCH
Analyze Data with Logical Functions:
- IF, AND, OR
- NESTED IF
- IFERROR & IFNA
Analyze Data with Conditional Functions:
- COUNTIF & COUNTIFS
- SUMIF & SUMIFS
- AVERAGEIF & AVERAGEIFS
Presenting Data using Charts:
- Create & Modify a Chart
- Different types of charts
- Chart Layouts & Styles
- Combo Chart - Column & Line
- Creating Two Axes Chart
Analyzing Data Using Pivot Tables:
- Creating a Pivot Table
- Adding Fields & Rearranging Data
- Multiple Fields & Report Filter
- Pivot Table Style & Classical Layout
- Creating Monthly, Quarterly & Yearly Reports
- Working with Slicers
Conditional Formatting:
- Highlight cell rules, Top/Bottom Rules
- Data Bar, Icon Set
- Using Formulas
Working with Data Lists:
- Custom Sorting
- Advance Filtering
- Subtotals
- Removing Duplicates
Data Validation:
- Limiting the range of values
- Drop down lists
- Error alerts
Using Text Functions:
- SUBSTITUTE, SEARCH
- LEFT, RIGHT, MID
- CONCATENATE, &, TRIM
Working with Date Function:
- DAY, MONTH, YEAR
- DATEDIF, EDATE, EOMONTH
- NETWORKDAYS, WORKDAY
Working with Template:
- Introduction to Template
- Creating and Using Template
- Editing existing template
Auditing Worksheets:
- Trace Precedent/Dependents
- Evaluate Formula
- Error Checks
Useful Data Cleaning process:
- Highlight Duplicate
- Goto Special
- Fill in Blanks
- Find & Replace
Sharing and Protecting:
- File Sharing & Track Changes
- Protecting file, workbooks, sheets
- Protecting Specific Cells & Range
New Formulas (Office 365):
- XLOOKUP, XMATCH*
- CONCAT, TEXT JOIN
- IFS, SWITCH, XOR
- FILTER, SORT, SORTBY
Data Transformation - Power Query*:
- Introduction & Loading to Power Query
- Remove Rows & Columns
- Fill Data, Split Data, Detect Data Types
- Transpose Data, Merge Columns
- Replace Values & Errors
- Converting unstructured data to structured format
- Exacting Data, Add Columns
- Loading Data to Data Module
Introduction to AI in Excel*:
- Overview of built-in AI features in Excel
- Key examples of AI utilization in Excel
How can we help you?
Why Us
- Microsoft Certified Trainer
- Industry Expert Trainer
- Advanced Excel Training course
- Real World Examples
- Microsoft Certification
- Post Training Support
