Security Services

Menu

Advance Excel

Unlock the full potential of Microsoft Excel and elevate your data analysis skills with Techtroma’s Advanced Excel Certification Training. This comprehensive course is designed for individuals seeking to master advanced Excel features and techniques, equipping you with the tools necessary to handle complex data management, analysis, and visualization.

Course Highlights:

  • In-depth Excel Skills: Learn advanced Excel features including pivot tables, data modeling, advanced formulas, VBA, and macros.
  • Hands-On Practice: Work on real-world projects and assignments to hone your skills.
  • Expert Trainers: Learn from industry professionals with vast experience in data analysis and Excel techniques.
  • Flexible Learning: Access the course materials online, allowing you to learn at your own pace and convenience.

Who should we Enroll?

  • Data Analysts
  • Business Analysts
  • Finance Professionals
  • Anyone looking to enhance their Excel skills for career growth

Why Choose Techtroma?

  • Expert-Led Training: Learn from top-notch trainers who are experts in Excel.
  • Practical Application: Gain experience through real-world exercises and examples.
  • Industry-Relevant Skills: Master Excel skills that are crucial for various business and analytical roles.
  • Lifetime Access: Get lifetime access to the course materials and updates.
Module 1: Introduction
  • MS office Versions(similarities and differences)
  • Interface (latest available version)
  • Row and Columns
  • Keyboard shortcuts for easy navigation
  • Data Entry(Fill series)
  • Find and Select
  • Clear Options
  • Ctrl+Enter
  • Formatting options (Font, Alignment, Clipboard (copy, paste special))
Module 2: Referencing, Named ranges, Uses, Arithemetic Functions
  • Mathematical calculations with Cell referencing (Absolute, Relative, Mixed)
  • Functions with Name Range
  • Arithmetic functions(SUM,SUMIF,SUMIFS,COUNT,COUNTA,COUNTIFS,AVERAGE,AVERAGEIFS,MAX,MAXIFS,MIN,MINIFS)
Module 3: Logical Functions
  • Logical functions: IF, AND, OR, NESTED IFS, NOT, IFERROR
  • Usage of Mathematical and Logical functions nested together
  •  
Module 4: Referring data from different tables: Various types of Lookup, Nested IF
  • LOOKUP
  • VLOOKUP
  • NESTED VLOOKUP
  • HLOOKUP
  • INDEX
  • INDEX WITH MATCH FUNCTION
  • INDIRECT
  • OFFSET
Module 5: Advanced functions
  • Combination of Arithmatic
  • Logical
  • Lookup functions
  • Data Validation(with Dependent drop down)
Module 6: Date and Text Functions
  • Date Functions: DATE, DAY, MONTH, YEAR, YEARFRAC, DATEDIF, EOMONTH
  • Text Functions: TEXT, UPPER, LOWER, PROPER, LEFT, RIGHT, SEARCH, FIND, MID, TTC, Flash Fill
Module 7: Data Handling: Data cleaning, Data type identification, Remove Duplicates, Formatting and Filtering
  • Number Formatting(with shortcuts)
  • CTRL+T(Converting into an Excel Table)
  • Formatting Table
  • Remove Duplicate
  • SORT
  • Advanced Sort
  • FILTER
  • Advanced Filter
Module 8: Data Visualization: Conditional Formatting, Charts
  • Conditional formatting (Icon sets/Highlighted colour sets/Data bars/custom formatting)
  • Charts: Bar, Column, Line, Scatter, Combo, Gantt, Waterfall, Pie
Module 9: Data Summarization: Pivot Report and Charts
  • Pivot Reports: Insert, Interface, Crosstable Reports; Filter, Pivot Charts,
  • Slicers: Add, connect to multiple reports and charts
  • Calculated field, Calculated item
Module 10: Data Summarization: Dashboard Creation, Tips and Tricks
  • Dashboard: Types, Getting reports and charts together, Use of Slicers.
  • Design and placement: Formatting of Tables, Charts, Sheets, Proper use of Colors and Shapes
Module 11: Connecting to Data: Power Query, Pivot, Power Pivot within Excel
  • Power Query: Interface, Tabs
  • Connecting to data from other excel files, text files, other sources
  • Data Cleaning
  • Transforming
  • Loading Data into Excel Query
  • Using Loaded queries
  • Merge and Append
  • Insert Power Pivot
  • Similarities and Differences in Pivot and Power Pivot reporting
  • Getting data from databases, workbooks, webpages
  •  
Module 11: Data Summarization: Dashboard Creation, Tips and Tricks
  • Dashboard: Types, Getting reports and charts together, Use of Slicers.
  • Design and placement: Formatting of Tables, Charts, Sheets, Proper use of Colors and Shapes
Module 11: VBA and Macros
  • View Tab
  • Add Developer Tab
  • Record Macro:Name,Storage
  • Record Macro to Format table(Absolute Ref)
  • Format table of any size(Relative ref)
  • Play macro by button
  • shape
  • as command(in new tab)
  • Editing Macros
  • VBA:Introduction to the basics of working with VBA for Excel: Subs, Ranges, Sheets
  • Comparing values and conditions
  • if statements and select cases
  • Repeat processes with For loops and Do While or Do Until Loops
  • Communicate with the end-user with message boxes and take user input with input boxes, User Form

Certification:

Upon successful completion of the course, you will receive a Course Completion Certificate from Techtroma. This certificate acknowledges your proficiency in advanced Excel techniques, enabling you to apply these skills in various professional fields.

Note: Techtroma provides a Course Completion Certificate; please be aware that this is not affiliated with any parent companies or external certifying bodies.

Scroll to Top