Advanced Excel Tutorials

Become an EXCEL NINJA
Online Advanced Excel Tutorials to make you a NINJA at work. Learn Everything in Excel from building Excel Models to creating Dynamic Dashboards

 

What’s covered in the Advanced Excel Tutorial?

Advanced Excel Tutorial

Stars_Resize4.5 (2930 ratings) 4512 students enrolled

The 3 levels of our Program will help you and will let you build excel models and create Dynamic Dashboards by making use of our designed curriculum.

Advanced Excel course is one of the finest online learning courses that Yoda Learning Solutions has for online excel training, this is to teach how to use excel shortcuts keys, understand the basic Excel formulas and to master advanced excel techniques online.

Yoda learning Solutions expertise in providing and delivering Advanced Excel Tutorials. Our team is popularly known for easily helping people become The Advanced Excel Ninja in real life from its modules containing The Advanced Excel formulas, advanced excel techniques, excel shortcuts and the advancements in the MS Excel course.

This is the most descriptive, reliable and easily understandable course we have made for the advanced excel learning of users. This is the online tutorial that we have seen people pick up for learning  Advanced Excel online from our Online Training.

Intermediate Skills

Able to implement techniques in your office
  • Build simple models (Combo Formulas, 3-dimensional lookups, Date formulas)
  • Error-handling to avoid humiliation (data validation, auditing, track variables)
  • Learn Data Cleaning Techniques (Go-to-Special, Text formulas, Flashfill)

Advanced Skills

Ninja-level Skills
  • Slide-ready output (Charts and Graphs, Printing techniques)
  • Dynamic Dashboards (Get Pivotdata, Learn Reverse lookups, conditional calculations)
  • Managing Data Security (Passwords, sheet protection)

These modules based on Advanced Excel Tutorials comprises of Advance Excel Formulas, methods, codes and advanced excel techniques that are covered in our online tutorials program to make your task simpler.

It guarantees Advanced Excel skills which will help you to be the supreme ninja of excel in your field. Data Lookup & Calculations such as Vlookup and Hlookup, Pivot Table for multi-variable analysis, Logical Statements, MIS Reporting & Dashboard Techniques and the Application of Macros are major target areas in our Program.

Advanced EXCEL Tutorial Online– Become an EXCEL NINJA

Gaining a satisfied Customer is taken as gaining a personal Advocate, they’ll take care of the most important asset of your company that is the Goodwill. So, in case you’re not 100% satisfied, let us know in the first 30 days of your purchase. We ensure to give you a complete refund.

30 Days Money Back Guarantee

Full 30 Day Money Back Guarantee No Questions Asked

100% Secure Checkout

Verified & Secure Checkout. Pay Via Credit Card or PayPal

Free Lessons

You will not find this Excel Course anywhere on internet (Guaranteed)

We are sure that you’re not going to find a similar course anywhere on the Internet. We ensure you about the quality of the modules and the classroom training that we offer.  If you find this course anywhere on the internet feel free to contact us and get a FULL course fee refund along with the added advantage of complimentary access to our course for a lifetime!

Creating 1000+ Pivot Table reports in 4-clicks for Documentation and Management reporting

Creatic Synthetic formulas such as

MAXIFS, MINIFS, STDEVIFS,SMALLIFS, LARGEIFS Extremely useful for creating models and decision making

Creating 3-dimensional lookups (VLOOKUP+MATCH+INDIRECT) useful for business critical decision making models

Emergency Hacks: 
Remove Sheet password with just notepad

Data tables on Steroids – Converting from one-variable output to multi-variable output (Hint: INDIRECT + Sensitivity Analysis)

Learner testimonials

  • I would like to tell you that I am very impressed with your courses so far – I particularly like the short videos that allow me to get to a topic quickly and then move on (your competitors mostly offer 20-30 min videos which is a bit much to keep one’s attention span). The other gem in your training are the shortcuts, I found no other course with the same insights.

    Peter
    Peter Klugsberger
    ex-McKinsey & Co. served various senior roles (CEO, COO) in various companies
  • Because I use spreadsheets daily, I’m often using the Shift+down arrow to highlight and then manipulate data. Of course, this takes FOREVER! As I am advancing in my role at work, it is very important for me to learn more advanced Excel skills. I chose this video b/c it was short and condensed. I could literally watch it on my breaks at work and then use it right away. I really like that. Well done!

    barbara
    Barbara Jones
    Finance Professional
  • I’ve been using Pivot Tables for a couple of years, so was expecting most of this to be familiar. But this course proved me wrong. From simple tips and keyboard shortcuts, to whole features I never knew existed, I constantly found myself muttering things like “oh wow, I had no clue about that”. Things that I can actually put into practice in my job. It’s a great feeling. Kudos to the team on a great course

    Peter Thomas
    Accounting Professional
learn excel online for free

We’ve helped these COMPANIES

with exact strategies and techniques used in this course

Course Curriculum

Ninja Shortcuts (40 minutes)

  • Introduction
  • How to use this section?
  • The “ONE” Universal shortcut & a faster Copy-Paste technique
  • AutoFill selected cell(s) with pre-defined formula/text/no.
  • Navigating across Worksheets; Data Table selection & manoeuvring
  • Adding/Deleting/Selecting Rows/Columns
  • For “Formula” One Speedstars
  • Formatting cells
  • Using Quick Access Toolbar (QAT)
  • Formula auditing magic

Formula Warm-Up (25 minutes)

  • MAX() in Financial Modeling
  • LARGE() & SMALL() – for bidding/auction pricing strategies
  • Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() – 1 of 2
  • Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() – 2 of 2
  • COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS()
  • SUMPRODUCT() for weighted average computation
  • POWER() vs. Caret sign ( ^ )

Formatting Tricks (22 minutes)

  • Formatting Tricks incl. Special Custom Formats
  • Format Painter – “Uninterrupted Click-ability”
  • Defining Cell Style – for “one-click” universal change
  • Transferring (Merging) Cell Style from one workbook to another
  • Cell drag-n-drop Auto Fill Options + Activation Setting
  • Cell drag-n-drop Auto Fill Options – Yr-end, Month-end, AlphaNumeric, Fill – Justify
  • Cell drag-n-drop Auto Fill Options vs. EOMONTH() – End of Year/Quarter/Month
  • Paste Special – Transpose vs. TRANSPOSE()

Cell Referencing ($) - Relative & Absolute (7 minutes)

  • Cell Referencing ($) – 1 of 2
  • Cell Referencing ($) – 2 of 2

Go To - Special "magic wand" (17 minutes)

  • Go To (Special) – Formulas with Errors
  • Go To (Special) – Formulas without Errors
  • Go To (Special) w. Ctrl+Enter
  • Go To (Special) – Fill Intermittent Blank Cells – Case 1
  • Go To (Special) – Fill Intermittent Blank Cells – Case 2
  • Go To (Special) – Visible Cells w. Shortcut

Sort, Filter & Advanced Filter (45 minutes)

  • Vertical Sort – 1-level & 2-level
  • Custom Sort
  • Sort Trick – add alternate blank rows in-between existing rows
  • Horizontal Sorting (Left to Right) – auto-realigning 100s of columns
  • Filter – Basics
  • Filter – Choosing the dataset correctly
  • Filter analysis w. shortcuts
  • Filter – SUBTOTAL() for calculations
  • Filter – SUBTOTAL() 9 vs. 109 series
  • Filter – Applying 2 or more Filters simultaneously on the same sheet
  • Filter – Color Filter & Text Filter
  • Advanced Filter – Basics
  • Advanced Filter – Differential Criteria
  • Advanced Filter – More parameters

Working with Dates (33 minutes)

  • Dates – Important Concepts 1 of 2
  • Dates – Important Concepts 2 of 2
  • Date Formulas – DAY(), MONTH(), YEAR(), DATE()
  • Date Formulas – TEXT()
  • Date Formulas – WEEKDAY(), WORKDAY(), NETWORKDAYS()
  • Date Formulas – WORKDAY.INTL() for deadline/due date calculations w. custom weekends/holidays
  • Date Formulas – NETWORKDAYS.INTL() for no. of business days calculations w. custom weekends/holidays
  • Date Formulas – TODAY() and NOW() w. Shortcut
  • Date Formulas – EOMONTH() for Financial Modeling, Budgets, Due Dates
  • Date Formulas – EDATE() for Financial Modeling, Budgets, Due Dates

Data (Cell Input) Validation (16 minutes)

  • Data Validation – Drop Down List using hard-coded text & pre-defined range
  • Data Validation – Drop Down List using named range
  • Data Validation – Numbers w. Error Alert and Input Message
  • Data Validation – Dates w. Error Alert and Circle Invalid Data
  • Data Validation – Text Length w. Error Alert
  • Data Validation – Custom w. formula logic

Setting up work area view (12 minutes)

  • Grouping/UnGrouping Columns and Rows – 1 of 2
  • Grouping/UnGrouping Columns and Rows – 2 of 2
  • Grouping Trick: Changing placement of Grouping Button
  • Cell Gridlines: Turning On/Off
  • Trick – Hide/Unhide Rows and Columns and Freeze panes

Pivot Table (1 hours 13 minutes)

  • Pivot Table – Why should you learn?
  • Pivot Table – Pre-requisites
  • Pivot Table – How to create one?
  • Pivot Table – Exploring Pivot Table grid (Fields)
  • Pivot Table – Value Field Settings – “Summarize Values By” – Sum, Average, Count, Max etc.
  • Pivot Table – Value Field Settings – “Show Values As” – Percentage (%) of Grand Total
  • Pivot Table – Value Field Settings – “Show Values As” – Percentage (%) of Column/Row Total
  • Pivot Table – Grouping – Numbers
  • Pivot Table – Grouping – Dates [Years, Months etc.]
  • Pivot Table – Grouping – Text
  • Pivot Table – Refresh vs. Refresh All, Change Data Source and creating Dynamic Table Data Source
  • Pivot Table – Auto Refresh
  • Pivot Table – Pivot Chart Shortcut and Sparklines
  • Pivot Table – Drill Down option
  • Pivot Table – Report Filter – Generating 100s of reports in few seconds
  • Pivot Table – Slicer vs. Report Filter
  • Pivot Table – Practice – Sales Data Analysis – 1 of 2I (%, Grouping, Chart, Report Filter Pages)
  • Pivot Table – Practice – Sales Data Analysis – 2 of 2 (Slicer)
  • Pivot Table – Practice – Closing Stock Analysis

Lookups & Conditional Calculations (2 hours 18 minutes)

  • VLOOKUP() for Starters
  • VLOOKUP w. TRUE vs. FALSE
  • VLOOKUP w. TRUE – applications
  • HLOOKUP() vs. VLOOKUP()
  • MATCH() – Basics
  • MATCH() match_type: -1 vs. 0 vs. 1
  • 2-D Lookup (Vertical + Horizontal) – VLOOKUP w. MATCH
  • 2-D Lookup – VLOOKUP w. MATCH – Framework
  • 2-D Lookup – VLOOKUP w. MATCH – Common Mistake #1
  • 2-D Lookup – VLOOKUP w. MATCH – Common Mistake #2
  • 2-D Lookup – VLOOKUP w. MATCH – Practice Exercise
  • 2-D Lookup (Horizontal + Vertical) – HLOOKUP w. MATCH
  • INDIRECT() – Basics
  • INDIRECT() w. Range Naming – Applications
  • 3-D Lookup – VLOOKUP() w. MATCH() w. INDIRECT() – Question
  • 3-D Lookup – VLOOKUP() w. MATCH() w. INDIRECT() – Answer
  • Reverse Lookup – Question
  • Reverse Lookup – INDEX() w. MATCH() – 1 of 2
  • Reverse Lookup – INDEX() w. MATCH() – 2 of 2
  • SUMIFS(): Conditional Summation (1 criteria)
  • SUMIFS(): Conditional Summation (2 criteria)
  • SUMIFS(): Conditional Summation (3 criteria) w. date range
  • SUMIFS(): Condition based Selective Cumulative Running Total
  • COUNTIFS() – Single/Multiple Criteria: Duplicate Count, Instance No.
  • OFFSET() with MATCH()

Data Cleaning / "Sanitizing" & Text Formulas (1 hours 14 minutes)

  • Text Formulas – Change Case using UPPER(), PROPER() & LOWER()
  • Text Formulas – Remove Leading, Trailing & excess Intermittent spaces using TRIM()
  • Text Formulas – Using LEN() & TRIM() together
  • Text Formulas – T(), N() & VALUE()
  • Text Formulas – REPT() for artifical Column Chart in a Cell
  • Text Formulas – REPT() & LEN() for adding leading zeroes before incomplete Cheque Nos.
  • Joining data strings using CONCATENATE, & – to be updated
  • Find & Replace – Using Wildcard character ( * )
  • Find & Replace – Using Wildcard character ( ? )
  • Find & Replace – Neutralising Wildcard characters to remove them from data
  • Find & Replace – Word vs. Excel
  • Find & Replace – Cell Format
  • Text to Columns – Basics (Delimited)
  • Text to Columns – Basics (Fixed Width) + Hidden Trick
  • Text to Columns – Cleaning up numbers w. trailing minus sign
  • Text to Columns – Cleaning up numbers w. leading “Dr/Cr” text
  • Text to Columns – Correcting invalid Dates (DMY)
  • Text to Columns – Correcting invalid Dates (YMD)
  • Text Formulas – LEFT(), RIGHT() & SEARCH() – 1 of 2
  • Text Formulas – LEFT(), RIGHT() & SEARCH() – 2 of 2
  • Text Formulas – MID() & LEN()
  • Text Formulas – REPLACE() & SUBSTITUTE()

Logical Formulas - Single & Combo (19 minutes)

  • Logical formulas: ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR(), ISFORMULA()
  • Logical formulas: Basics of IF() and Nested IF()
  • Logical formulas: AND(), OR()
  • Logical formulas: Combining IF() w. AND() & OR()

Conditional Formatting (30 minutes)

  • Conditional Formatting: Actual vs. Budget Comparison through Arrows
  • Conditional Formatting: w. Drop-down list based Input
  • Conditional Formatting: Data Bars, Color Scales, Icon Sets
  • Conditional Formatting: Blanks, Errors, Values, Duplicates
  • Conditional Formatting: Formula-based (Colored rows based on user input)
  • Conditional Formatting: Formula-based w. AND() – 2-way input Coloring
  • Conditional Formatting: Formula-based w. COUNTIFS() – Highlighting inputs if from restricted list

What-If Analysis (51 minutes)

  • What IF Analysis – Using Form Control Buttons from Developer Tab (Spin Bar, ScrollBar)
  • What IF Analysis – Limitations of Spin Bar, Scroll Bar
  • What IF Analysis – Scenario Managers
  • What IF Analysis – Goal Seek
  • What IF Analysis – Data Tables Basics (Sensitivity Analysis) – 2 Inputs & 1 Output
  • What IF Analysis – Data Tables Basics (Sensitivity Analysis) – with PMT for Loan EMI
  • What IF Analysis – Data Tables Basics (Sensitivity Analysis) – 2 Inputs & multiple Output – Question
  • What IF Analysis – Data Tables Basics (Sensitivity Analysis) – 2 Inputs & multiple Output – Answer

Subtotal & 3D Data Consolidation (30 minutes)

  • Subtotal – Creating a 1-level SubTotal on a Sorted list
  • Subtotal – Deleting a SubTotal
  • Subtotal – Creating a 2-level SubTotal on a Sorted list
  • Subtotal – Coloring the Subtotal rows with different Color
  • A Subtotal – Trick to Merge blocks of cells with similar names
  • Consolidate – 2 Dimensions
  • Consolidate – 3 Dimensions

Workbook & Worksheet Security (18 minutes)

  • Cell level Security
  • Cell level Security Challenge
  • Sheet level Security
  • File-level Security

Printing (13 minutes)

  • Printing – Rows to Repeat at Top (Print Titles)
  • Printing – Gridlines
  • Printing – Custom Header & Footer
  • Printing – Page Order – Vertical vs. Horizontal
  • Printing – Tricks for Financial Analysts – Check underlying formulas
  • Printing – Tricks for Financial Analysts – Cell Comments
  • Printing – Print Entire Workbook

Multiple Windows, Comments, Hyperlinking (15 minutes)

  • Comments – Shortcuts, Inserting Picture in Comment Box
  • Split Windows, Viewing multiple Windows – For working with different workbooks, worksheets & scattered cell ranges simultaneously
  • Hyperlinking (Ctrl+K)

Excel Arrays (Added on 5th Jan 2017) (48 minutes)

  • Basics of Array Formula (Ctrl Shift Enter) – Example 1
  • Basics of Array Formula (Ctrl Shift Enter) – Example 2
  • Building MAX IF and MIN IF with one condition – Example 1
  • Building MAX IF and MIN IF with one condition – Example 2
  • Array Formula using MAX() MIN() and IF() – 2 criteria
  • Find the four earliest dates using SMALL() and IF()
  • Calculating SUM of Top 3 numbers
  • SUMPRODUCT – Add numbers from next column
  • Finding Overlapping Dates using SUMPRODUCT – Part 1 of 2
  • Finding Overlapping Dates using SUMPRODUCT – Part 2 of 2
  • Extract unique values from a range in excel

Start Learning Today

No Frills

[No Frills] Lifetime Access

177 Course Lessons
$49
  • 177+ Course Lessons
  • Lifetime Support
  • LinkedIn-verifiable Certificate
  • Exclusive Webinars
  • 1200+ Office Productivity Tricks
Join now
Lifetime access

[Premium] Lifetime Access

Lifetime Access + Lifetime Support + Certificate
$100
  • 177+ Course Lessons
  • Lifetime Support
  • LinkedIn-verifiable Certificate
  • Exclusive Webinars
    • 1200+ Office Productivity Tricks
    Join now
    Office Training Bundle Package

    Office Training Bundle

    Unlimited Learning to All Courses
    $690
    • 177+ Course Lessons
    • Lifetime Support
    • LinkedIn-verifiable Certificate
    • Exclusive Webinars
    • 1200+ Office Productivity Tricks
    Join now

    add

    Don’t have credit card ?

    Course Completion Certificate By Yodalearning

    Unlock your completion certificate

    • Choose and complete any premium course at Yoda Learning Solutions
    • You are awarded a Diploma Completion Certificate
    • This diploma can be verified via URL and exported directly to your LinkedIn Account to boost your career and impress potential employers.

    For Enquiry About Courses? Contact Us:
    [email protected]
     +1- 203-349-9909

    Feel free to call us for any query related to courses. Our support team will resolve your issue.

    This Excel Course is for you if...

    • You use ERP downloaded Excel files as a base to generate custom MIS reports
    • You want to reduce your TAT significantly (turnaround time)
    • You want to prepare Financial Models with combo-logical statements
    • You prepare Management Reporting dashboards
    • You are involved in Performing CAAT & Forensic Audit techniques

    This Excel Course is not for you if...

    • You use Excel rarely like if you’re on sales field all the time or work as a salesman or cashier facing the consumer [I love you guys, but this model will not help you]
    • You have a personal assistant who is an Excel expert and can do all the number crunching for you [May be, this course will be helpful to your assistant]
    • Anyone who wants to instant results just because they have joined without taking any real action or practicing the working files

    Frequently Ask Questions

    What do you get in Excel course?

    • An accelerated, Practical approached Advanced Excel Tutorials to teach you Skill needed to compete in competitive work environment
    • All Advanced Excel tutorials case studies related to videos
    • A picture based PDF handbook on Excel Tips, Functions & Techniques
    • Access to all excel video tutorials 24 x 7 – learn excel online from anywhere
    • A 30-day money-back guarantee (we want you to be 100%)

    How do I enroll for the Excel training online?

    You can enroll for this excel training online by just a click on Enroll Now button Payments can be made using any of the following options and receipts of the same will be issued to the candidate automatically via email.

    •  Visa/Master Card credit card
    • Through PayPal
    • Don’t Have a Credit Card? Click here

    What will I be able to do after completing this Excel tutorials?

    • Create reports with sheets across 10+ excel files
    • Build interactive Pivot table with key business metrics like sales, monthly variations, top 10 customers
    • Transform messy data within minutes
    • Wow your boss and get noticed by the top management

    What if I’m unhappy with my online excel course? Do I get a refund?

    Customer happiness is our ultimate goal. These Excel videos are created after spending a lot of time in research and recording. We would never want you to be unsatisfied with your purchase and if you’re, then contact us in the first 30 days and we will give you a full refund. To know more, please read our Refund Policy.

    How much time required to complete Excel Training?

    This is a fast-paced, lean excel training program of about 11:30 hours with videos from basics to Advanced Excel Tutorials. Watch the preview of Excel videos to get a flavor of the program’s uniqueness and relevance. Online Excel course may not be your first preference but I promise that – after watching 3 videos, this course will give a tough fight to your usual classroom Excel Training.

    How can you say that my speed will improve?

    Well, I want to share something personal. My feedback from each and every client from Corporate gave me a rating between 4.8 to 5 out of 5. So, do not worry. I will empower you with all the excel tips and tricks based on the real issues faced by 1000s of students I teach. I created this advanced excel tutorials after so many years of research and hundreds of hours of excel training. You can also try the Outlook Tutorial to improve your speed further.

    What do you get in the course?

    In our program, we will train you with our advanced Excel skills which will help you to be the supreme ninja of excel in your field. Data Lookup & Calculations such as Vlookup and Hlookup, Pivot Table for multi-variable analysis, Logical Statements, MIS Reporting & Dashboard Techniques and the Application of Macros.