Course Overview:
This course has been designed for the extreme excel users with the most essential advanced excel features, functions, tools, tricks, and reporting techniques so that the excel users can perform their daily and periodic excel related works professionally and can be the Excel Star in their departments and office. The participants will also learn how to map and manage the big data and get the core information within a very short time.
- Course Duration: 07 Hours
- Total Sections: 12
- Total Resources: 98 (Video + Excel Files)
Supported Versions for Practice:
- 2013, 2016, 2019, office 365 and most will work in 2007 & 2010
What You’ll Learn:
- How to prepare the dynamic summary report in a tabular way
- Writing the complex functions in the same formula
- Customizing the dynamic and static charts
- Describing the data using PivotTable, PivotChart with Slicers
- Wow your boss with most popular dynamic functions VLOOKUP, INDEX, MATCH and OFFSET
- Data reconciliation techniques in an easy way
- Business modeling and various analysis techniques
- Analyzing and visualizing data using advanced conditional formatting
- Access to 13+ years experienced Professional Trainer who have conducted 150+ professional training programs and educated 4000+ participants through live classroom training
Other Available Complete e-Learning Courses by the Same Trainer?
- Microsoft Excel – Most Common Features of Excel for Professionals (07 Hours for basic to intermediate excel users)
- Microsoft Excel – VBA & Macro (04 Hours complete VBA & Macro course for pure Excel users started from the very beginning)
Training Methodology:
- Professional best quality video tutorial with workings
- Bite-sized small project-based business examples
- Student can download the same excel data for practice
- After each session, you can take quiz participation
- Bengali language with a clear easy description
- One to one Excel questions and supports
Requirements:
- Basic level excel knowledge
- You should have an internet-connected PC, mobile or TV
Who This Course is for:
- Regular excel users and want to enhance their excel productivity
- Intermediate level users who want to sharpen their excel skill
- Data drivers, working with the large data set
- Managers engaged with reporting
- Daily and periodic reporters
- Anyone who wants to improve their job or career prospects in an office environment.
Notes:
- After completing each lesson, you have to click on the “COMPLETE” button then go to the next lesson.
- Must fill up your first name, last name then display name for your certificate.
- You have to complete all quizzes with a minimum 70% correct answer & within 5 Re-take.
- Complete every lesson of this course serially.
- Don’t click on “FINISH COURSE” button.
- If you click on the “FINISH COURSE” button then the course will be finished, you will unable to complete the next lessons or parts of this course and a Certificate will be generated.
- Download the documents of this course from Lecture 1.1
Course Instructor:
Courses of this Instructor:
- Microsoft Excel – Advanced Level
- Most Common Features of Excel for Professionals
- Microsoft Excel – VBA & Macro
- Microsoft Excel VBA & Macro Basics
- Lookup Functions with Practical Business Case Study
- Excel VBA & Macro
- Microsoft Excel-Professional Tips & Tricks
- Most Out of Excel
- Advanced Automated Dashboard Reporting System Using Excel
Course Features
- Lectures 86
- Quizzes 11
- Duration 07 hours
- Skill level All levels
- Language Bangla
- Students 118
- Certificate Yes
- Assessments Self
- Course Overview
- Relative Vs Absolute Cell Reference (Use of $ Sign)
- Lookup and References Functions
- VLOOKUP Function when the range is whole column
- VLOOKUP Function for the specific range
- Copy-paste techniques for the VLOOKUP formula
- Difference between TRUE and FALSE in VLOOKUP Function
- Carefulness for VLOOKUP Function
- HLOOKUP function
- MATCH function
- INDEX function
- Combination of INDEX and MATCH function in a single formula
- Calling left column value from the source
- Dynamic column index number in VLOOKUP function
- OFFSET function
- LOOKUP function
- VLOOKUP function – Bonus Video
- Quiz
- Logical Functions with Nested IF
- Advanced Conditional Formatting
- Setting a single conditional formatting
- Setting multi-level conditional formatting in the same range
- KPI reporting -1 using data bar (Positive-negative)
- KPI reporting -2 using data bar (All Positive)
- KPI reporting -3 Highlighting top or bottom values
- KPI reporting -4 Using icon set
- Customizing the icon set
- Highlighting the duplicate values
- Conditional formatting using formula
- Conditional formatting using Color Scale
- Add, Edit or remove conditional formatting
- Clearing the Conditional Formatting
- Finding out the conditional formatting applied cells
- Quiz
- PivotTable
- Overview of PivotTable
- Creating PivotTable using one row and one column
- Creating PivotTable report with multiple groups
- Changing layout of PivotTable
- Drill down your PivotTable value
- Filtering the PivotTable Data
- Formatting PivotTable Data
- Adding a new calculated column
- Creating PivotChart
- Use Slicers in PivotTable
- Conclusion of PivotTable
- Quiz
- Data Linking and Reporting Techniques from Large Data
- Graphical Presentation
- Knowing the chart layouts and styles from the ribbon
- Chart formatting from the ribbon
- Creating chart taking data from non-continuous ranges
- Recognizing Chart Elements
- Changing chart type
- Adding secondary axis
- Column chart and column chart customization
- Line chart and line chart customization
- Pie chart and pie chart customization
- Doughnut chart and doughnut chart customization
- Waterfall Chart with customization
- KPI presentation using 3 kinds of Sparklines
- Thermometer Chart for target vs achievement
- Quiz
- Data Summarization Techniques
- Secure Your Sheet and File with Password Protected
- VBA & Macro Basics – An Overall Idea
- What is VBA and Macro?
- Why will you use VBA & Macro?
- Example of few ready real files created using VBA & Macro
- Configuring Excel to start VBA and Macro
- Project 1 – Creating first macro
- Running the macro clicking on a button
- Project 2 – Macro running by shortcut key
- Saving process of the Macro enabled file
- Understanding how macro writes the scripts
- Editing VBA code of a recorded macro
- Adding new lines in VBA code
- Conclusion of VBA and Macro
- Quiz
- Data Validation