Sai Santosh Technologies
(510, 5Th Floor, Annapurna Block, Ameerpet, Near Mythrivanam, Hyderabad, 500016)
(saisantoshtechnologies.in exceltraininginhyderabad.in)
Advanced Excel Course Content
S.no Training Curriculum
Introductions to Excel
• About work book, worksheet, worksheet proprieties
• About grid sheet, sheet navigations
1.
• Font, alignments, number formatting’s
• Custom Ribbon settings (options)
• Quick Access tool bar
Introduction to Conditional Formatting
• Basic Conditional Formatting
• Types of Conditions and Arguments
• Conditional Formatting by Icons, Bar Charts, Color Scales
2.
• Conditional Formatting by Duplicates
• Conditional Formatting by Top or Bottom Ranked Values
• Conditional Formatting by Text Specifications
• Conditional Formatting by Formula
Understanding Absolute References
Absolute References
• Relative References
3. • Mixed References
Name Manager
• Defining Names
• Using Defined Names in Functions
Introduction to Functions and Categories
• Common Functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK
Math and Trig Functions (Aggregate Conditional Functions)
• SUMIF, SUMIFS
4.
• AVERAGEIF, AVERAGEIFS
• COUNTIF, COUNTIFS
• SUMPRODUCT, AGGREGATE
• RANK, POWER, PRODUCT, SQRT, TRUNC
Logical Functions and Operators
5.
• IF, AND, OR, IFS, SWITCH,IFERROR
Text Functions
6. • UPPER, LOWER, CONCATENATE, CONCAT, TEXTJOIN,LEN,REPT,TEXT
• LEFT, RIGHT, MID, LEN, SUBSTITUTE
Lookup & Reference Functions
• Introduction to VLOOKUP and HLOOKUP
7. • VLOOKUP with Sheet References, Mixed Functions
• Reverse VLOOKUP Methods: INDEX & MATCH, XLOOKUP
• Error Handling (#N/A), IFERROR with VLOOKUP
Dynamic Array Functions (office 365)
• Unique
• Filter
• Sort
• Textbefore
• Textafter
8.
• Vstack
• Hstack
• Textbefore
• Textafter
• Textsplit
• Wrapcols
• Wraprows
• Formulatext
• Transpose
• Chooserows
• Choosecols
Introduction to Charts in Excel
• Understanding Charts and Their Purpose
9. • Steps to Create a Chart
• Chart Elements: Titles, Axes, Legend, Data Labels, Gridlines
• Chart Types: Column, Bar, Line, Area, Pie, Doughnut, Scatter, Bubble
Data Sorting and Filtering Methods
• Sort and Custom Sorting
• Filter and Advanced Filter
• Unique Data Filtering, Duplicate Data Filtering
• Data Filtering by Slicers (Using Table Design)
10.
Data Splitting and Duplicates
• Text to Columns
• Remove Duplicates
• Flash Fill (Automate Data Extracting)
• Data Consolidation
Data Forecasting Methods
• Goal Seek
11.
• Scenario
• Data Table
Pivot Tables and Power Query
• Introduction to Pivot Tables: Creating, Layouts, Data Summarization
12.
• Sorting, Filtering, Formatting, Pivot Charts, Slicers
• Dashboard creation
Data Importing (GET DATA)
13. • Importing data form Excel workbook, notepad, pdf
• Importing data form other sources: Web and Gateways servers
Power Query:
• Data Transform
14.
• Merge and Appending data
• Data shaping
Worksheet Securities and Accessibility
• Proofing (spell checking)
• Comments (adding notes and images)
15.
• Sheet protection
• Allow Edit Ranges (specified data range password)
• Workbook SaveAS Password protection)
AI Integration with Excel
• chatGPT
• Formula AI
16.
Kutools for Excel
• Formula Bot
• Copilot (office 365)
***THE END***
