Advanced Excel Course Description
- 100% Practical Oriented Training
- 30 Hours Duration
- Weekdays / Weekends
Learn More About The Program
What is this course about?
Microsoft® Excel 2013 Advance certification enables the professionals to gain proficiency in handling basic formulas and tools. This certification training helps the users to enhance their understanding of the basic tools for organizing, visualizing, calculating and managing data in MS Excel 2013. It is ideal for those who are looking to improve their job prospects by adopting Excel 2013 fundamental skills and knowledge.
Real-time ScenarioThe real time updated scenarios will be provided at the end of each topic. It helps you to apply the concepts taught during the class in Real-Time work.
Placement AssuranceGet placed at leading Analytics firms with interview prep and opportunities
Time Flexibility to LearnWe offer flexible batch timings for Students and IT Professionals to choose to join our weekday, Weekend.
100% Practical OrientedOur state of the art practical oriented training helps candidate to learn the practical approach of a content.
Introduction to Excel
- Structure of Excel Application
- Different Excel Versions (Differences)
- Reference Concept (working with different sheets ,workbooks & Closed workbook)
- Define Name
- Short Keys
- Formatting (Applying Bordrs, Colors and Font styles)
- Conversion of Formats
- Merging and Wrapping the text
- Conditional Formatting
- Format as Table
- Inserting and deletion of Rows, Columns and Sheets
- Row height and Column width
- Formulae based Conditional Formatting
Date & Time and Logical Functions
- DATEDIF, DATE, TODAY, NOW, WEEKDAY, DAY , MONTH, YEAR, YEARFRAC,
NETWORKDAYS, NETWORKDAYS.INTL,WORKDAY,WORKDAY.INTL, MINUTE, HOUR, SECOND, WEEKNUM, EDATE and EOMONTH.
- IF, AND, OR, NOT, TRUE, FALSE, IFERROR and Nested Functions.
- Activities based on the above functions.
Text, Arithmetical Functions
- UPPER, LOWER, PROPER, LEN, LEFT, RIGHT, MID,
FIND, TRIM, CLEAN, CHAR, CODE, CONCATENATE, SUBSTITUTE, EXACT, REPT, REPLACE, SEARCH,VALUE and TEXT.
- ABS, ROUND, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL , MOD and RANDBETWEEN
- Activities based on the above functions.
Lookup and References
- VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, CHOOSE and INDIRECT.
- VLOOKUP with MATCH, IFERROR, WILDCARD and CHOOSE.
- Combination of Formulas.
- Array Formulas
- VLOOKUP and COLUMN
- VLOOKUP with IF and VLOOKUP with AND Activities based on Form Controls.
Statistical and Information Function
- AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTBLANK,
COUNTIF, COUNTIFS, LARGE, SMALL, MAX, MIN, RANK, ROWS, ROW,
COLUMN and COLUMNS
- ISBLANK, ISERROR, ISNUMBER, ISTEXT , ISEVEN , ISODD and ISNA
- Activities based on the above functions
Tables, Illustrations and charts
- Hide and Unhide of Rows, Columns and Sheet
- Protecting sheet and Workbook
- Move or Copy, Rename sheet and Tab Color
- Filling series of Numbers and Dates
- Sorting and Filtering
- Pivot Tables
- Usage of Formulas in Pivot Tables
- Inserting pictures, Clip art, Text box, Shapes and Smart Art
- Usage of Charts (Column, Pie, Bar, Line)
- Usage of Dynamic ranges in Charts
Proofing, Comments and Changes
- Inserting Hyperlinks
- Linking sheets, Cells, Workbook, Range and Mail
- Header-Footer, Word Art and Signature Line
- Inserting Objects
- Protect Sheet
- Protect Workbook
- Hiding Formulas
- Sharing Workbook
- Track Changes (Highlight, Accept and Reject Changes)
- Inserting and Editing Comments
Page setup, Scale to fit and Arrange
- Inserting Comments and Spell check
- Freeze Panes (Rows and Columns)
- Save workspace, Switch windows and Split window
- Arranging the window (Vertically and Horizontally)
- Page Layout, Gridlines and Formula bar
- Paper margins, Gridlines and Paper size
- Page breaks, Applying background and Print titles
- Row repeat at top and Print preview
Connections and Data Tools
- Data Importing (From Access, Web and Text)
- Other Sources (SQL server and XML)
- Advance Sorting and Filtering
- Text to Columns
- Removing Duplicates
- Data Validation
- List box, Formula based restrictions
- Customization of error alert and Input box
- Types of Alerts (Stop, Warning and information)
- Highlighting the Invalid data
Data Tools and Outline
- Data Consolidation
- Scenario Manager
- Goal Seek
- Data Table
- Group and Ungroup
- Adding subtotals to the list
- Defining name to the range
- Name manger editing
- Trace precedents/Trace Dependents
- Evaluate Formulas
- INDEX and MATCH
- Dynamic Pivot ranges
- Creation of Dynamic ranges
- Comparison of charts using OFFSET function
- Dynamic charts using OFFSET and Scroll bar
- SUM and CHOOSE
- Sorting the data using formula
- Multiple VLOOKUP
- Multiple INDEX
- Leave Tracker using conditional formatting
- Using formula in conditional formatting to highlight second repeated value