Advanced Microsoft Excel

Course details

1- Handling of Sheets, Column, Rows (Modifying
Width/Height, Hide/Unhide, Shifting and Selection
Shortcut keys)
2- Cell formatting (Input Data Formatting, Visual
Formatting and Cell Data Hidden and Securing )
3- Formatting worksheets, Switching b/w Sheets,
Move & Copy Sheets, Grouping Sheet for identical
data Reflection on all Sheets and Securing &
Protecting spreadsheets
4- Data Filling Techniques (Series Filling, Even, Odd
number and Custom Series Filling, Date Series with
day, Month, year and Weekdays)
5- Use of Justify Tool along with Data combining
Formulas
6- Data Clearance Methods based on Contents, Cell
Formatting, Comments and Hyperlinks
7- Table Creation and Formatting
8- Conditional Formatting with Formulas

1- Advanced Filtering and Sorting based on different
Criteria a Colours
2- Past Special Advanced Features including transpose
with formula
3- Data/Object searching and Selection Methods with
Go to Special Function
4- Mastering of Pivot Table
5- Verification of Pivot Table values with advanced
Formulas for quick Learning
6- Customized value format and Calculation in pivot
table, Adding Calculated Column
7- Creation of Different Pivot table Dashboards with
Slicers and ;


1- Pivot Table Advanced Technics and Formatting
2- Making Dashboard based on Pivot Table Feature
3- Naming groups of Data using Named Ranges
4- Use of Formulas (sumif, sumifs, Count, CountA,
5- Countif, Countifs, Average, Averageif Averageifs,
6- Max, Maxif, Vlookup, Hlookup, lookup,match,
7- search, Concatenate,right, left, isna, isnumber,
8- Isblank, istext, iferror, index, sumproduct, int, Rand,
9- Rank, Randbetween, OFFSET, LARGE, SMALL, MIN,
index,Choose etc)


D-AY-4
1- Logical functions using IF, IFS, IFFERROR, AND, OR, NOT
2- Data Lookup and Matching Formulas (Vlookup, Hlookup,
3- lookup, match, Isna)
4- Nested and Array Formulas
5- Text Formulas (Concatenate, Right, Left, Mid, Search, Lower, Replace etc)
6- Date Formulas (date, Day, Hour, Minute, Month, Today, Days360 etc.


1- Making of Dashboard based on Formulas with the help different Solved Examples
2- Data Refining Tools (Text to Columns, Data Consolidation, Data Validation with
Formulas, Dependent List, Find & Replace), Flash Fill
3- What- if Analysis (Scenario Manager, Goal Seek, Data Table) Forecast sheet,
4- working with External Data, Grouping and ungrouping of Data and Subtotal.

1- Dynamic charts (Stacked Charts, Gear Charts, Pie Charts, Line Charts, Target Progress Chart etc)
2- Charts Customization Methods, Funnel Charts, Map Charts, 3D Models with Full Rotation
3- Different Chart Merging Techniques, Sparkle Line
4- Excel Camera, AutoShapes, Icons, Smart Art, 3D Modeling, Slicers, Timeline, Hyperlinking, Illustrations
5- Macros Definition and use, Record a macro
6- Concept of Developer Tab, working with Different Controls based on macros, Assign & run macro
7- Collaboration, Page Layout, Print area, Print Titles, Printing Updated on 04 July, 2022