"Advanced Excel Ninja" is a comprehensive online training program on Microsoft Excel. It is divided in 12 sections and covers exhaustive list of topics. There are 90+ tutorial videos and the duration is 13 hrs. Topics often ignored in other advanced online programs have been included. Demo Preview Lecture 89 and 90 to find the difference.
So if you intend to take your Excel application skills multiple notches higher, gain from the experience of a training instructor who -
And by the way he has conducted more than 200 (two hundred) workshops for one of the Big Four firms, where the participants are from numerous departments and domains. The insights acquired have been meticulously incorporated in to the program.
Instructor: CA. Rishabh Pugalia, Founder, Excel Next
The participants will be provided lifetime access to videos plus downloadable Excel Case Studies and picture-based HandBook (PDF).
Participants can download the accompanying Zipped file that contains PDF HandBook + Excel Case Studies
=COUNTA() implies Count ALL (nos, text, characters i.e. any non-blank cell)
Double-Click Format Painter for continuous usage and ESCAPE to deactivate, thereafter
Auto-Fill for dates, days, months, years, numeric series, alpha-numeric series etc.
=TRANSPOSE() with Ctrl+Shift+Enter
Always use =SUBTOTAL() formula while working with Filter. The raw formulas of SUM(), MAX(), AVERAGE() will pick up all values (whether hidden/visible as per the filtered list)
Advanced Filter for Differential Criteria
Advanced Filter for Differential Criteria
=SUM($A$1:A1) for cumulative total
Date Concept - Every valid date is a NUMBER from Excel's point of view. "FORMAT CELLS" is just the "skin" - how to display the output.
Date formulas -
DAY, MONTH, YEAR, DATE
TEXT, WORKDAY, NETWORKDAYS
NB: In Excel 2010/2013, =WORKDAY.INTL() has been introduced that helps find net working days between two dates and more importantly, lets you choose which day(s) of the week should be considered as weekends. E.g. lot of countries keep Friday as weekend.
Ever worked with dates such as 29.12.2009 or 20130530 and found yourself ill-equipped to prepare MIS reports based on such data? Text-to-Columns help clean dates.
Activating "DEVELOPER" Tab in 2007. Refer "PDF HandBook" or the "Supplementary Material" to see how to do so in 2010.
This video will show you how to use =SUMIFS() to add up the numbers based on specified criteria.
=VLOOKUP() can pull the answer only for the first occurrence/instance of the specified criteria, whereas, =SUMIFS() can add up the corresponding nos. of all occurrences of the specified criteria.
Write a formula such that answer should either be TRUE or FALSE. E.g. = $B10>100000.
-Cell Reference B10 vs $B10
-Starting point of range selection in line with formula cell selection
Generated report is neither aesthetic in looks nor is dynamic in nature [static]
VC:Vertical data - Column Input Cell
HR:Horizontal data - Row Input Cell
Introduced from v. 2007
Note: By default, ALL cells are "Locked" (identified for protection). Ensure that ALL cells in the sheet are "Unlocked" and only chosen ones are "Locked". Else ALL cells will be locked and no changes can be made.
Chart Elements - Chart Area, Plot Area, Axis Values, Legends, Changing Chart Type, Chart Shortcuts etc.
Axis Value Settings, Data Label Formats
Application: Comparing 2 parameters of similar scale:
Applications: Comparing 2 parameters of different scale:
Applications: Components of a Category (%):
Applications: Relation (80:20 comparison):
Rishabh Pugalia is a Chartered Accountant from India. He has worked with KPMG and J.P. Morgan before venturing into Corporate Training. He founded ExcelNext in December 2009 and has since trained 12,000+ professionals across various multi-national companies and professionals' forums.
He has delivered training programs on "Advanced Excel" in top Corporate houses. He has co-authored a Technical Guide issued by the Institute of Chartered Accountants of India (ICAI) - "Using Excel for Continuous Controls Monitoring" .
He is a visiting faculty at various premier B-Schools and has shared his knowledge on numerous Professionals' public forums.