Data Analysis and Management
Best Advance Excel Course – Advance Level Microsoft Excel Training Course

Best Advance Excel Course – Advance Level Microsoft Excel Training Course

Play Video about IMG1

Advance Excel Course

Advance Excel Course with International Microsoft Excel Certification. 

Learn more than 250 formulas | Power Query | Power Pivot | Data Validation | Data Consolidation 

100+ Assignments, Customized Notes, International Certification from Microsoft (MOS) and Placement assistance 

4.9 ***** (6,546 votes)

Available Modes: Online | Offline | Hybrid

About Advance Excel Course:

Advanced Excel Course majorly covers the Data Handling, Data Analytics, Data Correction and Data representation techniques along with automating our daily routine work with the help of customized excel formulas and functions. The in-depth training will enable to you write your own complex formulas with the mixture of other interesting tools and functions.

The customized training on Advance Excel is designed in such a way that the people from the diverse fields can have in-depth technical knowledge of the tools and techniques to develop a customize Excel based report.

excel
Advance Excel Course - “Turning Motivated Individuals into Office Superstars” is our main concern overall training. The primary focus of making individuals ready at par with the technology makes organization more reliable over their existing members and making their arm stronger.

KEY FEATURES of Advance Excel Course

advanced mis training

Advance Excel Course: Outcomes;

  • 50+ assignments, Custom notes on Advance Excel and its various properties
  • Command on more than 250+ formulas
  • Flexibility to avail Regular as well as Weekend Classes
  • For fast learners, daily classes of up to 5 hours of each class
  • Test and Mock Interview Sessions for Interview Preparations
  • Exposure on Real time work environment

Topics Covered under Advance Excel Course

EXCEL ALONG WITH ADVANCE EXCEL COURSE

  • Advance Excel Course to focus on real scenarios of Reporting / Data Management / Automation
  • Difference between basic and advance Excel, Excel Basics, Excel Settings
  • Advanced Formulas including complete coverage of Logical functions, Text Functions, Date & Time Functions, Look up functions & Mathematical Functions.
  • Complete Excel ribbons including Name Manager, Absolute and Relative references, field setting, Goal Seek / Scenario manager, Managing custom Lists, Cross addressing.
  • Pivoting and Slicing along with Power Pivot and Power Slicing
  • Graphs, Charts along with MIS reports generations
  • Excel Setting, enabling and disabling Excel options including add ins, creating own tabs  and menu bars
  • Handling multiple files with the Excel File System (Exporting and Handling Files) + Database file connectivity, Remote file connectivity, Text file connectivity and many more…
  • Conditional formatting, Data Validation, Consolidation, Grouping and Sub Grouping, User Settings for Excel Application Level
  • Data Management and Analysis, Reporting Techniques, Macros, List box, Combo box, Buttons and many more…
  • Geographical charts on global data sets
  • More than 250+ formulas along with 100+ assignments, notes, e-books and many more ACLM’s branded videos to help you forever.

Set of Important Formulas and related assignments in Advance Excel Course

  • Logical Function Sets – And, Or, Not, Nand, Nor, Xor, True, False, Iferror, Iserror, Iserr, Isna, Isblank, Istrue, IsFalse, IsNumber, IsText, IsEven, Isodd, IsLogical
  • Usage of Operators – Logical Operator, Arithmetic Operator, Assignment Operator and Unary Operators
  • Text Function Set – Upper, Lower, Proper, Left, Mid, Right, Concatenate, Char, Clean, Code, Find, Search, Replace, Substitute, trim, text value, Text set, T, BahtText, Unichar, Unicode. The array formulae including various other text functions.
  • Date & Time Function Set – Date, Day, month, Year, time, today, now, Hour, minute, Second, Datedif, Datevalue, Time Value, Weeknum, Weekday, Age Calculator, Usage of scientific and moderate calculator, Days360, General Date Diff, EDate, EOmonth, Workdays, Workdays.intl, Networkdays, Networkdays.intl, now, Today, Yearfrac
  • Lookup and References set – Address, Area, Choose, Vlookup, Hlookup, Lookup, Reverse Lookup, Index, Match, Row, Rows, Column, Columns, Get Pivot Data, Hyperlink, Indirect, offset, Transpose, RTD, Small, Large, Rank, Formula Text, 
  • Mathematical Function – General Use Function – Sum, sumif, Sumifs, Count, countif, Countifs, Average, Averageif, Averageifs, Product, Sumproduct, Sumproduct Array Formula, Round, Roundup, Rounddown, Floor, Ceiling, INT, Sqrt, Power, Subtotal, Fact, Even, Odd
  • Conditional Statement – If, If else, if else if (nested if), if with operator, if with the use of text functions, if with the use of numbers, if with date function, if with time functions, nested if with operators including text, numbers & date & time functions.

DATA FILTERING & FORMATTING IN ADVANCE EXCEL COURSE

  • Understanding Different Data Types, Using String / Text / Number / Long Data Types with examples, Managing Date Time Format, Handling Long Date time Format, Data Correction, Identifying bad data, Conversion of different data types, Importing and accessing different file types in excel

DATA SECURITY IN ADVANCE EXCEL COURSE

  • Protecting Excel File, Protecting worksheet, Workbook, Enable / Disable Range, Allow users to edit range, Protecting users to edit range, Field Controls using customized user access.
MACRO GENERATION IN ADVANCE EXCEL COURSE
  • Creating your own macro, assigning shortcut keys on macro, editing pre existing macros, conditionally format data with the help of macro, creating your own customized macro.

CHARTS  / GRAPHS / PIVOT CHARTS / PIVOT TABLE / POWER QUERY / POWER PIVOT IN ADVANCE EXCEL COURSE

  • Setting Data Source, Getting Charts / Graphs using Referential fields, Getting ACCESS Data Getting Other Data
  • Advanced Pivoting with the help of data model, create multi level pivots
  • Button level control on pivots with multiple scenarios
  • Automated boxing and targetting in pivot
  • Automated multi page filtering in Advanced Pivoting
  • Creating your own calculated field and calculated charts 

Projects, Assignments & Certifications in Advance Excel Course

vba dashboard practical
  • Opportunity to work on live project on data reporting
  • 100+ assignments
  • Certificate of participation from ACLM – AISECT
  • Certification from Microsoft
  • Participation Certificate from ACLM

Who Should Attend the Advance Excel Course

  • Freshers
  • Data Entry professionals
  • Any one looking for career opportunity in data analytics and reporting fields

Pre-requisites for Advance Excel Course

  • Basic Knowledge of Excel, Candidate’s having some programming background will have an added advantage

What You Need To Bring

  • Notepads, Laptops / Notebooks (Up to your choice)

Key Takeaways

  • Various technique with the help of Excel, Generate different Analytical Process. Data handling and correcting bad data with the help of Excel, Reporting and Formatting with different angles, Macro generation and application design

About Advance Excel Trainer

  • Having 17+ Years of Experience in Data Analysis, Clinical Data Research, Data Mining and Presentation
  • Delivered more than 100+ VBA Projects (onsite & offsite)
  • Real time exposure of data Analytics, Python Data Science, Tableau and SAS clinical research.

Placement Statistics

Total Enrolled Candidate
Advanced Excel 100%
Candidate Placed
Advanced Excel 79%
Microsoft Certified
Advanced Excel 63%

Advance Excel Course SYLLABUS

1 : Advanced Formulas and Functions

  • Review of Basic Functions
    • SUM, AVERAGE, COUNT, MIN, MAX
  • Logical Functions
    • IF, AND, OR, NOT
    • Nested IF Statements
  • Lookup and Reference Functions
    • VLOOKUP, HLOOKUP
    • INDEX & MATCH
    • XLOOKUP
  • Text Functions
    • CONCATENATE, TEXTJOIN
    • LEFT, RIGHT, MID
    • LEN, FIND, SEARCH, REPLACE
  • Date and Time Functions
    • TODAY, NOW
    • DATE, TIME
    • DATEDIF, WORKDAY, NETWORKDAYS
  • Array Formulas and Dynamic Arrays
    • Introduction to Array Formulas
    • Using SEQUENCE, SORT, FILTER, UNIQUE    

2: Data Analysis and Visualization

  • PivotTables and PivotCharts
    • Creating and Customizing PivotTables
    • Grouping Data in PivotTables
    • Calculated Fields and Items in PivotTables
    • Creating PivotCharts
  • Advanced Charting Techniques
    • Combo Charts
    • Sparklines
    • Advanced Chart Formatting
  • Conditional Formatting
    • Creating and Managing Rules
    • Using Formulas in Conditional Formatting
    • Data Bars, Color Scales, Icon Sets
  • Data Validation
    • Creating Drop-down Lists
    • Custom Data Validation Rules
    • Error Messages and Input Messages

3: Data Import and Cleaning

  • Importing Data
    • Importing from Text Files (CSV, TXT)
    • Importing from External Data Sources (Databases, Web)
    • Using Power Query for Data Import
  • Data Cleaning
    • Removing Duplicates
    • Text to Columns
    • Using Flash Fill
    • Data Cleaning with Power Query

4: Advanced Data Tools

  • What-If Analysis
    • Goal Seek
    • Data Tables
    • Scenario Manager
  • Solver Add-In
    • Setting up Solver
    • Defining and Solving Problems
  • Advanced Filtering
    • Using Advanced Filter
    • Using Database Functions (DSUM, DCOUNT, DAVERAGE, etc.)
  • Working with Large Data Sets
    • Efficient Data Handling Techniques
    • Using Tables and Structured References

5:Macros and VBA

  • Introduction to Macros
    • Recording Macros
    • Editing Macros
    • Assigning Macros to Buttons
  • Introduction to VBA (Visual Basic for Applications)
    • Understanding VBA Editor
    • Writing Simple VBA Code
    • Debugging and Error

6: Collaboration and Security

  • Sharing and Collaboration
    • Sharing Workbooks
    • Tracking Changes
    • Merging Workbooks
  • Protecting Workbooks and Worksheets
    • Protecting Cells and Ranges
    • Password Protection
    • Digital Signatures and Encryption

7: Integration with Other Tools

  • Excel and Power BI
    • Connecting Excel to Power BI
    • Using Excel Data in Power BI Reports
  • Excel and PowerPoint
    • Linking Excel Data to PowerPoint
    • Embedding Excel Charts in PowerPoint
  • Excel and Word
    • Mail Merge with Excel Data
    • Embedding Excel Tables in Word

 8: Case Studies and Practical Applications

  • Excel and Power BI
    • Connecting Excel to Power BI
    • Using Excel Data in Power BI Reports
  • Excel and PowerPoint
    • Linking Excel Data to PowerPoint
    • Embedding Excel Charts in PowerPoint
  • Excel and Word
    • Mail Merge with Excel Data
    • Embedding Excel Tables in Word

Final Assessment and Certification

  • Practical Exam
    • Comprehensive Project Covering Course Topics
  • Certification
    • Issuance of Completion Certificate upon Passing

Registration open for Data Analytics & Data Science. (12 Months Exec. Program)

X