Duration : 1 Month / 36 Hours
This course is outlined in a way that it covers majority of enriched features in Microsoft Excel 2016, including Macros, Pivot tables, Audit and Analyze worksheets, Use Advance Formulas and Functions, work with multiple Worksheets and Workbooks etc., which in turn increases productivity, improves efficiency by streamlining the workflow thus becomes a major asset for professional employees.
1 Month / 36 Hours
- Introduction to MS Excel, Quick review on MS Excel Options, Ribbon, Sheets
- Saving Excel File as PDF, CSV and Older versions
- Using Excel Shortcuts with Full List of Shortcuts
- Copy, Cut, Paste, Hide, Unhide, and Link the Data in Rows, Columns and Sheet
- Using Paste Special Options
- Formatting Cells, Rows, Columns and Sheets
- Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
- Page Layout and Printer Properties
- Inserting Pictures and other objects in Worksheets
- Working with Excel Properties
- Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc.
- Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc.
- Database Functions: DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc.
- Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc.
- Information Functions: CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, TYPE etc.
- Math and Trigonometry Functions: RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc.
- Statistical Functions: AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTBLANK, COUNTIF, MAX, MAXA, MIN, MINA, etc.
- Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE etc.
- Financial Functions: PMT, PPMT, RATE etc.
- Other Important Functions
- Using Conditional Formatting
- Using Conditional Formatting with Multiple Cell Rules
- Using Color Scales and Icon Sets in Conditional Formatting
- Creating New Rules and Managing Existing Rules
- Using Filters to Sort Data
- Custom Sorting
- Advance Filtering Options
- Bar Chart
- Line or Area Chart
- Pie or Doughnut Chart
- Hierarchy Chart – Treemap, Sunburst
- Scatter or Bubble Chart
- Statistic Chart – Histogram, Box & Whisker
- Waterfall or Stock Chart
- Combo Chart
- Show Formulas
- Trace Precedents & Dependents
- Remove Arrows
- Evaluate Formula
- Creating Pivot Tables
- Using Pivot Table Options
- Changing and Updating Data Range
- Formatting Pivot Table and Making Dynamic Pivot Tables
- Creating Pivot Charts
- Types of Pivot Charts and Their Usage
- Formatting Pivot Charts and Making Dynamic Pivot Charts
- Text to Columns
- Data Validation
- Remove Duplicates
- Outlining & Grouping
- Scenario Manager
- Goal Seek
- Data Table
- Exporting data from Web
- Exporting data from other sources
- Working with queries
- Get & Transform
- Loading & Editing queries
- Protecting and Sharing a Workbook/Worksheet
- Locking and Editing Cells
- Introduction to VBA Macro
- Recording Macro & Understanding Code Behind
- Editing, Writing VBA Code and Saving as Macro
- Knowing Forms & Properties
- ★ ADVANCED EXCEL (G-TEC Certificate)