Microsoft Excel For Advanced Users
Please fill following information
Contents of the Training
- USE ESSENTIAL FUNCTIONS FOR DATA TRANSFORMATION
- Transformation using Excel’s built in AI
- Joining multiple cells using TEXTJOIN, CONCAT, &
- Conversion1 – UPPER, LOWER, PROPER, TRIM
- Conversion2 – FIND, SEARCH, SUBSTITUTE
- Extraction – LEFT, RIGHT, MID
- Rounding – ROUND, ROUNDUP, ROUNDDOWN
- Converting a Text number to a computable pure number or date
- Join a text with formatted number or date.
- LOGICAL FUNCTIONS AND OPERATORS
- IF() Condition and expression of logical test
- IFS() function in new way
- Using IF with AND, OR
- Using nested IF with other functions
- DYNAMIC ARRAY FUNCTIONS FOR DYNAMIC REPORTING
- Using SORT function
- Using FILTER function
- Using UNIQUE function
- Using SEQUENCE function
- Using multiple array functions in one formula
- #SPILL! errors in Excel
- ADVANCED TECHNIQUES OF DATA SORTING, FILTERING
- Various professional tips on sorting
- Setting custom sorting with different orders
- Sorting left to right
- Using Custom Filter with logic settings.
- LOOKUP & REFERENCES
- Using VLOOKUP, HLOOKUP function
- Complex solution of VLOOKUP/HLOOKUP
- 2-Way VLOOKUP/HLOOKUP
- Multiple VLOOKUPS in the same formula
- Using INDEX and MATCH function as alternative of VLOOKUP
- Using OFFSET function for dynamic reporting
- The magic of brand new XLOOKUP function
- REPORTING TECHNIQUES USING PIVOTTABLES, PIVOTCHART AND SLICERS
- Data mapping to prepare a PivotTable
- Personalize the default PivotTable layout
- Customizing subtotal at any row label data and subtotal category
- Changing PivotTable data source
- Inserting a calculated field and calculated item
- Drill-down to the Pivot data
- Grouping by date hierarchy
- Sorting and filtering techniques in PivotTable data
- Slicer in PivotTable-creating, changing, and formatting.
- Make your PivotTable dynamic using slicer!
- Creating PivotChart from existing workbook data
- Automated dashboard reporting techniques using PivotTable
- POWERPIVOT & DATA MODELING
- Exploring PowerPivot
- Understanding table structure
- Understanding various relationships
- Understanding primary & foreign key
- Creating data model
- Using few DAX function in PowerPivot
- Using Measure in PowerPivot
- Reporting using PowerPivot.
- Preparing dashboard using PowrPivot
- POWERQUERY IN EXCEL
- Data connection in different way
- Exploring the PowerQuery window
- Various transformation in the same column
- Various transformation in the new column
- Appending data with multiple tables
- Merging data with multiple tables
- Combining data from multiple files
- Combining data from all files from a folder
- DATA SUMMARIZATION & AGGREGATION FROM LARGE DATASET
- Using SUMIFS, COUNTIFS
- Using SUMPRODUCT function for complex dataset
- Using MINIFS, MAXIFS
- Using AVERAGEIFS
- The INDIRECT function
- Using INDIRECT function for complex solution
- WHAT-IF ANALYSIS AND BUSINESS FORECASTING
- Using Goal Seek command.
- Sensitivity analysis using data table.
- Define and solve a problem by using Solver
- Using Scenario Manager
- Using forecasting command
- USING GO TO COMMAND
- Jumping to a specific cell, name range or specific range
- Various problem solution examples with GO TO command
- AUTOMATING EXCEL TASKS USING VBA & MACRO
- Excel file preparation for starting VBA and Macro
- Creating your first macro without having any programming knowledge
- Running the recorded macro using button or objects
- Working with the Visual Basic Editor Window
- Writing VBA code in VBE window
- Understand how macro read the script and how it works
Course Information
- Reg. Ends 21 February, 2024
- Start Date 22 February, 2024
- End Date 03 March, 2024
-
Class Schedule
Feb: 22, 24, 26, 28,
Mar: 01, 03
08:00PM - 10:30PM - Total Hours 15 Hours
- Venue Title Online Live Training
- Venue Address www.sudoksho.com/class-room
