Advance Excel Course

Set of Important Formulas and related assignments in Advance Excel Course

• Logical Function Sets – And, Or, Not, Nand, Nor, Xor, True, False, Iferror, Iserror, Iserr, Isna, Isblank, Istrue, IsFalse, IsNumber, IsText, IsEven, Isodd, IsLogical
• Usage of Operators – Logical Operator, Arithmetic Operator, Assignment Operator and Unary Operators
• Text Function Set – Upper, Lower, Proper, Left, Mid, Right, Concatenate, Char, Clean, Code, Find, Search, Replace, Substitute, trim, text value, Text set, T, BahtText, Unichar, Unicode. The array formulae including various other text functions.
• Date & Time Function Set – Date, Day, month, Year, time, today, now, Hour, minute, Second, Datedif, Datevalue, Time Value, Weeknum, Weekday, Age Calculator, Usage of scientific and moderate calculator, Days360, General Date Diff, EDate, EOmonth, Workdays, Workdays.intl, Networkdays, Networkdays.intl, now, Today, Yearfrac
• Lookup and References set – Address, Area, Choose, Vlookup, Hlookup, Lookup, Reverse Lookup, Index, Match, Row, Rows, Column, Columns, Get Pivot Data, Hyperlink, Indirect, offset, Transpose, RTD, Small, Large, Rank, Formula Text, 
• Mathematical Function – General Use Function – Sum, sumif, Sumifs, Count, countif, Countifs, Average, Averageif, Averageifs, Product, Sumproduct, Sumproduct Array Formula, Round, Roundup, Rounddown, Floor, Ceiling, INT, Sqrt, Power, Subtotal, Fact, Even, Odd
• Conditional Statement – If, If else, if else if (nested if), if with operator, if with the use of text functions, if with the use of numbers, if with date function, if with time functions, nested if with operators including text, numbers & date & time functions.

DATA FILTERING & FORMATTING IN ADVANCE EXCEL COURSE

• Understanding Different Data Types, Using String / Text / Number / Long Data Types with examples, Managing Date Time Format, Handling Long Date time Format, Data Correction, Identifying bad data, Conversion of different data types, Importing and accessing different file types in excel

DATA SECURITY IN ADVANCE EXCEL COURSE

• Protecting Excel File, Protecting worksheet, Workbook, Enable / Disable Range, Allow users to edit range, Protecting users to edit range, Field Controls using customized user access.

CHARTS  / GRAPHS / PIVOT CHARTS / PIVOT TABLE / POWER QUERY / POWER PIVOT IN ADVANCE EXCEL COURSE

• Setting Data Source, Getting Charts / Graphs using Referential fields, Getting ACCESS Data Getting Other Data
• Advanced Pivoting with the help of data model, create multi level pivots
• Button level control on pivots with multiple scenarios
• Automated boxing and targetting in pivot
• Automated multi page filtering in Advanced Pivoting
• Creating your own calculated field and calculated charts