delhi
Date
  • weekwid: false
  • widHeader: false
  • orgName: partner@edu4sure.com
  • orwid: false
https://www.eventshigh.com/detail/delhi/447ab7cd3be8e1dbbcab26716596a49a-be-certified-ms-excel-professional

Be Certified MS Excel Professional by Edu4Sure

0.0,0.0
Sun, 16 Dec 2018 11:00AM - 5:00PM
Eccosphere Coworking Pvt. Ltd.
Rs 399 onwards
204 people viewed this event.
I Am Interested

Get notified when the event happens next time.

Details

Details

 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? 

Trainer

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

SECTION NAME

FUNCTION / TECHNIQUE

WORKSHEET OPERATIONS

Ribbon
QAT
Alt KeyTips
Essential Shortcuts
Custom Tabs

RANGE

Define Range Names
Name Manager
Define Constants
$ sign for Absolute, Relative and Mixed Referencing
Create links between closed workbooks

DATA CLEANING

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

REFERENCING

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

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

TEXT

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

PIVOT TABLES

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

LOOKUP

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

 

Login to View Organizer Details
Like this event ? Share it with your friends !!
Show

Certificate of Participation

Certificate of Participation

Certificate of participation will be provided to all trainees.

Trainees may have doubts with the trainer after the workshop.

Show

Map & Directions

Map & Directions
Eccosphere Coworking Pvt. Ltd. B 61, Block B, Sector 67, Noida, Uttar Pradesh 201307, India
Reviews
Reviews
No reviews available
Write a review
Be the first one to review! Share your experience.
Show

Frequently Asked Questions

FAQs
Have any query? Drop your questions here !!
Show

Other Events By Partner E4S

EventsHigh Specials, coworking, use case, timesheet, block b, microsoft, machine learning, Partner E4S, technology, classes and workshops,