Advance Excel and Dashboards Program in Mumbai
Duration: 3 Days
Advance Excel
Microsoft Excel, Level 2 (Office 356, Office 2016, Office 2013, Office 2010)
Participants should be using Excel, and the common formula’s like Min, Max, Sum, Average, functions such as Sort & Filter.
Having additional knowledge of VLOOKUP & Pivot Table’s would be an advantage.
Audience
Executives and managers who have already been using Microsoft Excel, but now feel the need for learning more powerful features and options of Excel, to manage their worksheet-related tasks more efficiently.
Prerequisites
Participants attending this training should be familiar with the basic operations in Microsoft Excel, such as simple calculations, formatting and printing.
Excel Dashboard
An Excel Dashboard provides insight, analysis and alerts. They’re fully interactive and dynamic and can help with project management, customer service, retail management, financial forecasting and much more.
Prerequisites
Participants attending this training should be familiar with the Advanced Excel, such as VlookUP, Match & Index, CountIF, SUMIF, IF condition.
If the mentioned topics are not familiar, we recommend a 2 Day Workshop.
This Advanced Excel Course will empower the participants to be able to do the following:
- Performing complex calculations more efficiently, using various Excel functions.
- Organizing and analyzing large volumes of data.
- Creating MIS reports.
- Designing and using templates.
- Consolidating and managing data from multiple workbooks.
Advance Excel
Advanced Excel Course Module 1 : Overview of the Basics
- Customizing common options in Excel
- Absolute and relative cells
- Protecting and un-protecting worksheets and cells
Advanced Excel Course Module 2 : Working With Functions
- Writing conditional expressions (using IF, NESTED IF)
- Using logical functions (AND, OR, NOT, COMPOUND IF)
- Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH,
- INDEX, NESTED VLOOKUP, VLOOKUP WITH APPROX MATCH & EXACT MATCH)
Advanced Excel Course Module 3 : Data Validations
- Specifying a valid range of values for a cell
- Specifying a list of valid values for a cell
- Specifying custom validations based on formula for a cell
Advanced Excel Course Module 4 : Working with Templates
- Using templates for standardization of worksheets
Advanced Excel Course Module 5 : Sorting and Filtering Data
- Sorting tables
- Using multiple-level sorting
- Using custom sorting Filtering data for selected view (AutoFilter) • Using advanced filter options
Advanced Excel Course Module 6 :Designing the structure of a template
- Creating subtotals
- Multiple-level subtotals
- Creating Pivot tables
- Formatting and customizing Pivot tables
- Using advanced options of Pivot tables
- Pivot charts
- Consolidating data from multiple sheets and files using Pivot tables
- Using external data sources
- Using data consolidation feature to consolidate data
Advanced Excel Course Module 7 : More Functions
- Date and time functions
- Text functions
- Database functions
Advanced Excel Course Module 8 : Formatting
- Recording a Macro
- Assigning a macro to a button
- Relative & Absolute reference for a macro
Advanced Excel Course Module 9 : Macros
- Using auto formatting option for worksheets
- Using conditional formatting option for rows, columns and cells
Excel Dashboard
Excel Dashboard Course Module 1: Introduction
Excel Dashboard Course Module 2: Getting Your Data Ready
Excel Dashboard Course Module 3:
Excel Dashboard Course Module 4: Design Principles
- Charts & Tricks for Trending
- Secondary Axis
- Smoothing Data
- Sparklines
- Formatting Tricks
- Highlighting Comparisons
- Top & Bottom Ranking
- Frequency Distribution
- Target vs Variance Charts
- Performance Against Target Range
- Bullet Graphs
- Win/Loss/Draw Conditional Format
- Dynamic Data Range
- Dynamic Labels
- Text Formulas
- Symbols in Formulas and Charts
- Charts Referencing Non-contiguous Ranges
- Embedding Objects in Charts
- Dynamic, Dynamic Data Validation Lists
- In Cell Charts
- Panel Charts
- Step Charts
Excel Dashboard Course Module 5: Interactive Controls
- Form Controls Introduction
- Check Boxes
- List Boxes
- Option Buttons
- Group Boxes
- Scroll Bar
- Buttons
- Combo Box with Macro
- Dynamic Date Filters 4 Ways
- Animated Charts
- INDIRECT Function Tricks
- Dynamic Named Range with OFFSET
- Dynamic Named Range with INDEX
- Dynamic Ranges for Charts
- Rollover Technique
- Scroll and Sort Table
Excel Dashboard Course Module 6: Analysing the Data
- PivotCharts
- Slicers – Excel 2010/2013/2016
- GETPIVOTDATA Function
- Capture Slicer Selection in Formula
- Slicer & PivotChart Dashboard Overview
Excel Dashboard Course Module 7: Putting It All Together
- New Dashboard Tour
- Pyramid Chart
- Array Formula Alternative to SUMIFS
- LARGE and SMALL Array and DAVERAGE
- Rank Values with PivotTable
- Scatter Chart
- DMAX, DMIN, DAVERAGE
- Formatting Quickly
I have attended the advanced excel workshop on the 19th and 20th of December 2020. Everything you need to know about excel in two days, can be found through this workshop. Got a lot ideas and answers to age old doubts. Mr. Faiyaz is a very good teacher for the same. Every technical description was made so simple to understand with great examples. Thankyou sir.
Carol Monteiro
I have attended the Advanced Excel training sessions organised by Sealed Air Packaging Materials India LLP on 15th and 16th December 2020. The sessions conducted by Mr. Faiyaz were very informative, interactive and hand-on despite having a virtual training platform. I would highly recommended this for anybody who wants to enhance their excel skills.
Sampada Munj
The 3 day workshop taught me what i couldn't learn in a span of a year. The way excel is been taught makes you learn the basics and understand the functions and features instead of memorizing things. A big thumbs up to Faiyaz Khairaz for taking the session.
Nitesh Oberoi
Amazing training. I was able to prepare excel dashboards for my organization with their teachings and methods. Even my doubts were solved. Special thanks to Sabiha ma'am and Shaziya ma'am for their training.
Harsh Malhotra
Did a Excel Workshop Course with Faiyaz M Khairaz sir. It was an excellent course for girl like me who had no prior experience with Excel. Sir cleared each and every doubt I had. Thank You Sir for the wonderful training.
Geeta Pansare
