Let's learn Excel and be efficient in working. Get certified and improve chances to crack exams on Excel.
Save your time by learning from MOS and get the certificate after completion of the workshop. Excel is used in day to day activities but many of us do not know How its power can be utilized in their work
The trainer claims a 100% refund in case you do not find it valuable so what are you waiting for?
Havish Madhvapaty (Co-Founder at Decode) is a doctoral student and a Microsoft Office Specialist Master.
He is the author of 5 books (4 on analytics) and is a contributor to the global Microsoft community on MS Excel.
TOPICS TO BE COVERED
FUNCTION / TECHNIQUE
Ribbon QAT Alt KeyTips Essential Shortcuts Custom Tabs
Define Range Names Name Manager Define Constants $ sign for Absolute, Relative and Mixed Referencing Create links between closed workbooks
Extract day from the date Clean date, time and numbers Detect blank, hard-coded, formula driven Fill intermittent cells Machine learning using FlashFill
SORT AND FILTER
Custom Sort Multiple filters on one sheet Shortcuts to create table Advanced Filters Creating Structured References with Tables Using SUBTOTAL and AGGREGATE
FORMULAS AND FUNCTIONS
Formula Structure Function Argument box See formula text Basic Functions: SUM, AVERAGE, COUNTBLANK, COUNT NUMBERS, COUNT TEXT, COUNT ALL, MAX, MIN Advanced Functions: SUMPRODUCT, ROUND, ROUNDUP, ROUNDDOWN Get closest perfect divisor MROUND Ignore errors and Hidden Rows / Find calculation from the filtered list: Find specific error types / text / numeric etc. Auto vs Manual calculations
Linking cells between worksheets
SUMIFS AND COUNTIFS
SUMIFS with 2 criteria SUMIFS with range logic (E.g. from-to date) Using SUMIFS to calculate running cumulative total based on any primary key SUMIFS vs. AVERAGEIFS Using COUNTIFS to calculate running count based on any primary key. E.g. 1st, 2nd 3rd-time occurrences of any key
IF with multiple AND OR Operators Using Array alternatives
DATE AND TIME
Separate elements using DAY, MONTH, YEAR Get complete date using DATE EDATE Calculating expiry date. E.g. 3 months contract EOMONTH Calculating 10th of next month for due dates. NETWORKDAYS.INTL, WORKDAY.INTL | Custom date formats Ignore weekends Create employee timesheet including Overtime Summing up hours Creating a dynamic payment period
LEFT, RIGHT, MID Extract characters from a cell | TRIM, SUBSTITUTE Cleaning data of a cell PROPER, UPPER, LOWER - Change case of the text | CONCATENATE vs & - Joining strings Separating First Name, Middle Name, Last Name Removing titles from Names
Basics | Essential Setting (Classic View) Date Grouping | Move column field headings to the right/left Calculating Count, Sum, % Color-scales based Conditional Formatting (Heat Map) Sparklines (in-cell trendline chart) Slicer (user-driven filter) PivotChart Shortcut Creating 100s of worksheets in seconds
Overview of different forms of Lookup (use-case) 1D exact, slabs-based, 2D, reverse, fuzzy VLOOKUP with TRUE - doesn't mean approximate match VLOOKUP with TRUE vs. complex Nested IF statements VLOOKUP with MATCH for 2D lookup | Reverse Lookup with INDEX and MATCH Fuzzy Lookup for similar names with spelling variations. Overview OFFSET | LOOKUP for case sensitive matches | LOOKUP with data array inside the formula