The course covers topic for advance Excel. and Cost is ₹ 500 per Hour

Topics Covered

ADVANCE MICROSOFT EXCEL: 35 to 40 Hours

a) Introduction Formula and Function Tips and Shortcuts Reviewing function basics Absolute, Relative and mixed references

b) IF and Related Functions An Introduction to Different Operators (Arithmetic, Relational and Logical) Exploring IF logical tests and using relational operators Creating and expanding the use of nested IF statements Using the AND, OR, and NOT functions with IF to create compound logical tests

c) Lookup and Reference Function VLOOKUP and HLOOKUP – A Short Introduction Finding approximate matches with VLOOKUP Finding exact matches with VLOOKUP Nesting LOOKUP functions Finding table-like information within a function with CHOOSE Locating data with MATCH Retrieving information by location with INDEX Using MATCH and INDEX together

d) Power Functions & Statistical Functions COUNTIF, SUMIF, and AVERAGEIF COUNTIFS, SUMIFS, and AVERAGEIFS Finding the middle value with MEDIAN Ranking data without sorting Finding the magnitude data with LARGE and SMALL Tabulating blank cells with COUNTBLANK

e) Date Functions Identifying the day, month, year in a date Identifying the day of the week with WEEKDAY Counting working days with NETWORKDAYS Determining a completion date with WORKDAY Tabulating date time differences with DATEDIF

f) Math Functions Working with rounding functions Finding the remainder with MOD and using MOD with conditional formatting Building random number generators with RAND and RANDBETWEEN Converting a value between measurement systems with CONVERT

g) Text Functions Determining the Length of a Text using LEN Locating and extracting data with FIND and MID Extracting specific data with LEFT and RIGHT Removing excess spaces with TRIM Using CONCATENATE with functions Adjusting case within cells with PROPER, UPPER, and LOWER Adjusting character content with REPLACE and SUBSTITUTE Reviewing additional text functions

h) Financial Functions Calculating payments with PMT Finding future values with FV Determining total amount of future payments with PV

i) Information Functions Working with the IS information functions Using error-checking functions ISERR, ISERROR, IFERROR

j) Reference Functions Getting data from remote cells with OFFSET Returning references with INDIRECT Finding the nth LARGEST and SMALLEST Date using LARGE and SMALL & IF

k) Analyzing Data Using auditing to diagram Using evaluation in Excel Working with Goal Seek (What-if Analysis) Data Tables, Scenarios (What-if Analysis)

l) Data Validation in Depth Controlling the Limits of Numeric Data Setting Up Drop-Down Lists (Pick Lists) Date Controls & Time Controls Text Length Controls & Specialized Custom Formula Controls.

EXPERTISE IN MICROSOFT EXCEL: 25 to 40 Hours

a) Financial Functions in Depth

1. Analyzing Loans, Payments, and Interest (PPMT and IPMT, CUMPRINC and CUMIPMT, ISPMT, EFFECT and NOMINAL, ACCRINT and ACCRINTM, NPER)

2. Calculating Depreciation (SLN, DB, DDB, SYD, VDB, AMORDEGRC, AMORLINC)

3. Determining Values and Rates of Return (FV, FVSCHEDULE, PV, NPV, XNPV, IRR, XIRR, MIRR, DISC)

4. Calculating Bond Coupon Dates and Security Durations (COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, DURATION, MDURATION)

5. Calculating Security Prices and Yields (DOLLARDE and DOLLARFR, INTRATE, RECEIVED, PRICE, PRICEDISC, PRICEMAT, TBILLEQ, TBILLPRICE, TBILLYIELD, YIELD, YIELDDISC, YIELDMAT)

6. Calculating Prices and Yields of Securities with Odd Periods (ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD)

b) Pivot Tables in Depth Applying Conditional Formatting to PivotTables Printing PivotTables and Pivot Charts Manipulating PivotTables Using Macros Getting Started with PowerPivot Working with DAX Expressions

c) MACROS & VBA Running a Macro (Execute, Play Back, Etc.) Using Visual Basic for Applications (VBA) Recording a Macro in Stages Using database functions like DSUM, DAVERAGE, and DMAX Creating Non-Recordable VBA Code Macro Project- Converting a Mailing List into a Database List The Personal Macro Workbook

d) Setting up a Database in Excel Introduction Database Design Concerns Tables Controlling the Creation of New Data

e) Running with VBA in Excel Introducing Visual Basic for Applications (VBA) Defining Variables, Constants, and Calculations Adding Logic to Your VBA Code Debugging Your VBA Code Managing Workbook Elements and Data in VBA Adding Advanced Elements to Your Workbook Using Excel Events in Your VBA Code Putting It All Together

Who should attend

Anyone who has a passion to learn Excel and know the basics.

Pre-requisites

Basic Computer Knowledge, Basic Microsoft Excel Skill

What you need to bring

Notepad (if required)"

Key Takeaways

Good Advance Working Knowledge in Excel.