
Advanced Excel with Macros & AI
Duration : 12 hours – 2 Days
Lab Setup
HW: 1 Laptop/Desktop Per Participant
OS: Windows
SW: MS Excel
Course Contents
1. Using Excel Interface
- Customize the Quick Access Toolbar
- Use Ribbons and Tabs
- Use Contextual tabs
- Use Live Preview
- Shortcut keys
2. Using References in Excel
- Relative cell reference
- Absolute cell reference
- Mixed cell reference
- Using Name Manger (Define, Search, Edit and Delete Names)
3. Using various types of Functions in Excel
- Summary Functions (Sumif, Sumifs, Countif, Countifs, verageif, Subtotal etc.)
- Date Functions (Today, Now, Month, Year, Datedif, Weeknum etc.)
- Text Functions (Left, Right, Mid, Concatenate, Trim, Upper, Lower, Proper etc.)
4. Using Lookup and Conditional Functions in Excel
- Conditional Functions (If, nd, Or, IFS etc.)
- Lookup Functions (Vlookup, Hlookup, Xlookup.)
5. Validating and Protecting your work
- Data Validation
- Hiding formulas
- Protect sheet
- Allow working post protection
- Protect workbook
- Encrypt document
6. Extracting useful info
- Auto Filter
- Advanced Filter
- Remove duplicate data
- Consolidate
7. Arranging your data
- Single column sort (Quick sort)
- Multiple column sort (Data sort)
- Color and custom sort
8. Format Data conditionally to stand out
- Format data using fixed value
- Format data using bsolute cell reference
- Format data using Mixed cell reference (Comparison)
- Format data using Formula
- Format data using Data Bars
- Format data using Color Scale
- Format data using Icon Set
- Modify and Remove CF
9. Using Tables to Summarize and isualize your data
- Creating Table to add dynamism to your data (Auto update)
- Creating Pivot Table
- Use of sections and Layout (Row, Column, Filter and Values)
- Using Predefine functions and values format
- Insert Slicer
- Insert Timeline
- Add design to make reports attractive
- Add charts to make summary visual base
10. Macros
- Record macros
- Add button to run a macro
- Detail about VBA Editior
- Edit, Delete and Customize macros using VBA
- Working on Excel file, sheets and cell
11. Power Query
- Merge and ppend multiple Excel Files and Sheets Data
- Data manipulation by Power Query
- Summarizing multiple files and Sheets Data