Advanced Excel – IDA Career

Advanced Excel

Advanced knowledge of Excel means possessing the ability to use spreadsheets, graphing, tables, calculations, and automation efficiently to process large quantities of data relevant to business tasks.

Course Content

MODULE 1: BASIC EXCEL FEATURES AND FUNCTIONALITY PART 1

Introduction of Excel compare MS Excel 2016 with other Excel versions
Copy/Paste, Paste Special
Add and Edit Comments
Fill Effects
Wrapping text within a cell
Data Formatting
Filter, Advanced Filter, Color Filter
Sorting, Advance Sorting, Custom Sorting
Flash Fill Feature
Text to column functionality of excel to Split words
Absolute, Relative and Mixed Cell References
Protect sheet and workbook
Protect Range
Table Feature
Freeze and unfreeze panes and Split
Excel Keyboard Shortcut Keys (A to Z)
Conditional and Custom formatting

MODULE 2: BASIC AND INTERMEDIATE EXCEL FEATURES AND FUNCTIONALITY PART 2

Understanding the number format codes
Changing the font color with number format codes
Hide Number and Text based on criteria
GOTO SPEICAL Feature
Creating Name range and Name Manager
Hyperlink from a different sheet, workbook, cell, defined names and websites
Remove Duplicates
Data Validation: Data List, rules, modify error messages
Grouping, ungrouping and Subtotal in Excel

MODULE 3: NUMBER FUNCTION

COUNT, COUNTA, COUNTBLANK
COUNTIF
COUNTIF with wildcard
Uses of COUNTIF with real-time example
Unique Entry using the COUNTIF function
Find duplicates using COUNTIF Function
Running Count
Example of COUNTIFS
Example of SUMIFS
Example of AVERAGEIFS
Example of DCOUNT
Example of COUNTA
Example of DSUM
Example of DAVERAGE

MODULE 4: LOGICAL AND SUMPRODUCT FUNCTIONS

Logical Functions: IF, Nested IF, AND, OR, NOT, XOR, IFERROR
SMALL and LARGE function
SUMPRODUCT Function
Use SUMPRODUCT for COUNT
Use SUMPRODUCT for SUM
Use SUMPRODUCT for COUNTIF and COUNTIFS
Use SUMPRODUCT for SUMIF and SUMIFS
Use SUMPRODUCT for AVERAGE and AVERAGEIFS
Use SUMPRODUCT for MAXIF and MAXIFS

MODULE 5: TEXT FUNCTIONS IN EXCEL TO SOLVE COMPLEX TEXT PROBLEMS

Introduction of TEXT Functions (LEFT, RIGHT, MID, REPLACE, SUBSTITUTE, etc) available in Excel
Use text functions to Split the words
Use text functions to split characters and numbers
What-If Analysis in Excel

MODULE 6: DATE AND TIME FUNCTIONS IN EXCEL, CREATING A DYNAMIC CALENDAR FOR WHOLE MONTHS AND YEARS

Introduction of Date Functions available in excel.
Create and Design Calendar using Different Date Functions (WEEKDAY, WEEKNUM, EOMONTH, EDATE) in excel.
Calculate Age using DATEDIF Function
Calculate the date and time difference
Count working days using NETWORKDAYS Function

MODULE 7: LOOKUP, VLOOKUP, HLOOKUP FUNCTIONS

Introduction of LOOKUP VLOOKUP and HLOOKUP
VLOOKUP with Exact Match
VLOOKUP with Approximate Match
Usage of VLOOKUP Functions using ROW and COLUMN functions
Example of VLOOKUP with Array
Example of HLOOPKUP with multiple Examples
Example of LOOKUP with multiple examples

MODULE 8: INDEX AND OFFSET FUNCTION, USES OF ARRAY IN EXCEL

Introduction of the INDEX Function
Introduction of OFFSET Function
Use of INDEX Function
Advance uses of Array Function using INDEX, OFFSET, MATCH, SMALL, LARGE, ROW, and
COLUMN function
Use of ROW and ROWS function using INDEX and OFFSET function

MODULE 9: INDIRECT AND ADDRESS FUNCTION

Introduction of INDIRECT Function
Introduction of ADDRESS Function
Uses of INDIRECT Function
Uses of ADDRESS Function

MODULE 10: DATA ANALYSIS USING POWER QUERY AND POWER PIVOT IN MICROSOFT EXCEL

Creating a Pivot table and Pivot chart in Excel
Adding Slicer and Timeline to analyze in Excel
Using the power pivot to do the Data Analysis
Using Power Query to Source the data from Different Sources

MODULE 11: MIS REPORTS AND DASHBOARDS

Introduction of Dashboards and MIS Reports
Adding and Using User Form control in Excel
Recording Macro and run it through a command button
Creating Dynamic Formula based on` User Form control
Creating Dynamic Charts using Formula
Creating 2D charts in Excel
Importance of charts in Dashboards and MIS Reports

MODULE 12: CREATING DYNAMIC DASHBOARD ON LIVE DATA

Project work on MIS Dashboard

Explore More

Flexibility in Planning and Teaching

Theoretical Knowledge

Practical Skills

Individual Work with a Mentor

Final Individual Project

The premier destination for individuals seeking to elevate their professional skills and excel in their careers.

Book Your Live Demo Now !

Copyright © 2023 WebridgeTech Pvt Ltd All rights reserved.

+91-8130121986

info@idacareer.com