Excel Tips & Tricks For Intermediate Excel Users with Eg.
What you'll learn
- Best - Features of Data Cleaning i.e. Flash FILL (Basic - Intermediate)
- Top 7 awesome Pivot Table Dashboard Tricks in 1 video
- Clean & Restructure data 10x faster with Go To (Special) with Ctrl Enter
- Hidden Tricks of Find & Replace
- Use Slabs based Lookup (Vlookup with TRUE) to avoid complex Nested IF statements
- 2D Lookups (Vlookup with Match)
Requirements
- The learner should be an Intermediate user of MS Excel with version 2007 and above
- Excel 2013 & above for Flash Fill trick
- Excel 2010 & above for Pivot Table Slicer
Description
Who this course is for:
This course is for Excel users who have the knowledge of VLOOKUP, Text to Columns & Pivot Table
Advanced Excel, Data analysis in Excel, Become Excel Expert
You will learn the best tricks in MS Advanced Excel and its real life application like never before:
No 1. Flash Fill - It saves hours of time. It fills in data based on pattern recognition. You have to show few samples. It can also can extract and insert as well as format and concatenate. It is based on "Programming by Example (PBE)".
>> How to train the algorithm of Flash Fill better?
No blank column between Input & Output columns
Take more samples
Take tough samples
Use Apostrophe / Single quote marks for [Date & Time, Nos. with preceding zeroes]
No 2. Pivot Table
Grouping [Dates, Numbers]
Slicers & related settings
Pivot Chart Shortcut
Disable Autofit Column widths
Disable =GETPIVOTDATA() for formula writing
2-D Table data consolidation
Creating 100s of Pivot Table reports in < 1 minute
No 3. Go To (Special)
Filling blank cells in between rows of data
Selecting cells with errors
Detecting cells with formulas
No 4. Find & Replace (Ctrl + H)
How to use Asterisk (*) to clean data 10x faster
How to remove Asterisk (*)
Why Find & Replace of MS Word is more powerful
No 5. VLOOKUP() with TRUE (1)
Saves hours of your time by condensing multi-level complex nested IF statements in one simple VLOOKUP() formula.
When is VLOOKUP() with TRUE used? When the [Date or Number] values are:
in logical slabs (e.g. 0-100, 101-200 etc.)
listed in ascending order, and
listed in order of [at least, onwards, not less than, WEF, >=]
Excel Course For Expert (VLOOKUP, Pivot Table, Gotospecial)
Application areas: Financial Modeling, Accounting (Debtors' ageing), Incentive calculations, Ranking etc.
No 6. VLOOKUP() with MATCH() saves hours of your time by condensing multi-level complex nested IF & AND/OR statements in one simple combination formula. It helps pull data based on two variables (vertical & horizontal). It's called 2-D lookup.
--------------------------------------------
Important information before you join:
Once enrolled, you have unlimited, lifetime access to the course!
You will have instant and free access to any updates I'll add to the course.
I will give you my full support regarding any issues or suggestions related to the course.
Access to all videos 24 x 7 - learn online from anywhere
A 30-day money back guarantee (we want you to be 100% satisfied)
---------------------------------------------
If you have read this far, next action is JOINING this course. Invest 38 mins. for the benefit of lifetime Ninja status on Advanced Excel Course.
Advanced Excel, Data analysis in Excel, Become Excel Expert
Who this course is for:
- Regular Excel users who are keen to learn hidden tricks of Excel to save hours of time
Course content
Introduction7 lectures • 38min
- Download Practice Files00:02
- Excel 2013's BEST feature for data cleaning - Flash Fill (Basic to Intermediate)Preview06:27
- Let's see if you remember what you learnt (Flash Fill) !1 question
- Top 7 awesome Pivot Table Dashboard Tricks in 1 video11:20
- Let's see if you remember what you learnt (Pivot Table) !3 questions
- Clean & Restructure data 10x faster with Go To (Special) with Ctrl Enter05:01
- Let's see if you remember what you learnt (Go To - Special) !1 question
- Learn the Hidden Tricks of Find & ReplacePreview04:50
- Let's see if you remember what you learnt (Find & Replace) !1 question
- Use Slabs based Lookup (Vlookup with TRUE) to avoid complex Nested IF statements06:41
- Use 2D Lookup (Vlookup with Match) to avoid complex Nested IF statements04:02
May 2018 Update - New eBook1 lecture • 1min
Instructor
![Yoda Learning](data:image/svg+xml,%3Csvg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 64 64"%3E%3C/svg%3E){kind=avatar}
- 4.3 Instructor Rating
- 7,608 Reviews
- 117,507 Students
- 29 Courses
20,000 Classroom Students | 100,000+ Students Online
ex-KPMG, ex-J.P. Morgan | Chartered Accountant
Taught 12k+ participants across PwC, EY and Deloitte over last 10 years
I am a Chartered Accountant from India. I have worked with KPMG (Audit) and J.P. Morgan (Debt Capital Markets) and Futures First (Proprietary Trading Desk) before venturing into Corporate Training (first part-time and then full-time)
After a decade of corporate training and winning multiple accolades in Office Productivity and Financial Modelling, in 2015, I created a mission to help 1 million people by 2020. I started creating high-quality video courses and helped people online to become faster-smarter-better at work. A lot of my students at PwC, EY, Deloitte, Amazon, Uber, Tata Group, Rio Tinto use these video courses to excel at work.
If I’m going to succeed in my mission of helping 1 million professional in the next five years, I NEED YOU to succeed… I can’t reach my goal without helping you reach yours!