Excel Power User Beginner Tutorial

Excel Power User Beginner Tutorial
Get Ad-Free Training by becoming a member today!
/ @learnittraining
Exercise Files: bit.ly/3if2Aye
Who it's For: This course was built from the ground up for the advanced users and those in search of more than typical Excel tools and processes. Attendees of this course are recommended to have completed our Excel 2016 training, or at a minimum already be proficient in the skills referenced in those courses.
What it is: The Excel Power User course expands on the idea that the tools by themselves are only part of the equation. To say that you’re capable of solving a problem is enough for most users, but participants in this course aren’t most users. The desire to learn and invest time in building resources that solve problems faster and with less effort is a key quality of the power user and something the course maintains a heavy focus on.
What You'll Learn: In this series, students will learn how to intermix tools in Excel to accomplish time-intensive tasks. Students will learn to develop a deeper understanding of data mining tools like VLOOKUP, as well as more advanced solutions like the INDEX, MATCH, and OFFSET functions. Throughout the course, users will learn how to expedite data analysis through dynamic ranges and how to push Excel to the edges of what’s possible out of the box with new perspectives on how to think their way through challenging problems.
For ad free training and certificates please join Learnit Anytime:
www.learnitanytime.com
For Private Group Trainings please visit:
www.learnit.com/private-group...
Manuals: bit.ly/30xZb59
Username: manuals
Password: password
Start 0:00
VLOOKUP 0:03
Range Lookup 7:45
Autofilling Lookup 10:41
Defining Names 14:06
Using Named Ranges to Retrieve Data 17:18
INDEX 22:11
MATCH 27:41
INDEX & MATCH (Part 1) 30:28
INDEX & MATCH (Part 2) 34:27
INDEX & MATCH (Part 3) 40:23
Introduction to Array Formula 44:00
Array Formula 46:20
Array Function 51:49
Dynamic Named Ranges (Offset) 1:01:03
OFFSET Function 1:05:30
Name Manager 1:12:52
Basic Conditional Formatting 1:18:37
Building New Rules and Troubleshooting 1:22:06
Expanding Conditional Formatting Fields 1:30:39
Text Search Conditional Formatting 1:33:14
Data Validating Conditional Formatting 1:40:16
#Excel #VLOOKUP
(C) 2021 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.

[@mohamedshafwan4440]

ONE OF THE BEST TUTORIAL YOU EVER GET ONLINE

[@LearnitTraining]

Thank you so much Mohamed!

[@nakissafarajpour374]

HEADS UP: DYNAMIC arrays formulas, and functions have been massively updates in OFFICE 365 so if you are on newer versions of excel, be mindful that tricks like ctrl+shift+ent or OFFSET may not properly work the way they are presented in this tutorial. Of course, this will not change the fact that Sean has done a great job and his teaching style is marvelous. Many thanks Sean 👍👍

[@pramodhfernandopulle8888]

This is a great learning pack to advance the excel knowledge. Thank you LEARNIT team

[@kensimba7868]

U deserve a huge cheque for this! All these info for us ur a world hero bugler and I really appreciate man. Thaaaaankuuuu and am subscribing now

[@babuumakantvuppala9598]

Continuing to have real pleasure in learning. LEARNIT knows how to TEACHIT.

[@ahmadkadid1022]

really appreciate your effort that you have put into this amazing video. i wish i can support u in anyway

[@PrincessBunny2012]

Clearly instruction for the functions related each other, awesome.

[@martiancocian6301]

Excellent course, functions clearly explained, by a gifted instructor. Thank you.

[@LearnitTraining]

You're very welcome!

[@RohitChouhan-dj7se]

MINE OF INFORMATION THANKS FOR THE QUALITY VIDEO

[@raymondpang2634]

Your presentation of excel power was great. Thank-you LearnIT team

[@LearnitTraining]

Our pleasure!

[@michaelalbry]

Wow, that was really teaching. Thank you.

[@LearnitTraining]

You are so welcome!

[@alvaroninainca2501]

Thank you very much for this superb course on Excel. Would you tell me please if I can format my data as a table to update new information instead of using the offset function I find it easier and I believe I get the same result. Could you confirm this please?

[@vanycdtd08]

thanks!

[@abhishekchatterjee1257]

Firstly thank you for such a lovely video. Now my question is why do we needed to make an offset function to dynamically select data from our array when we could have just coverted the data into a Table and when we enter any data in the table would it not auto update the index match formula that we used earlier.? Kindly resolve my query.

[@vijaysahal4556]

superb 👍👍👍👍👍👍👍👍👍👍👍👍

[@AlexanderKwalsh]

Good tutorial from you. It's by far the BEST i have found here. I have this problem. I was able to do the VLOOP, but when I change PROD# to say 11, the ProductName does not change. I don't know why. Please which version of excel are you using. Thanks

[@kensimba7868]

I think u need to autofill again after changing the prod # to refresh

[@immanuelsuleiman7550]

• Where is your data housed • how do you import and clean your data • what types of methods do you use to clean your data • do you use anything to automate your daily functions • if no methods currently exists are you open to scripting repetitive tasks • what documentation recourse does your team use to solve issues e.g. official documentation, stack overflow, tutorial videos • what is the average time frame to complete a project and how much does that time increase with regards to the complexity of the project • what metrics do you use to measure performance and how does that relate to growth potential within the company

[@robertzee5291]

33:15 what if the list of employees grows? Isn't it easier to just select the 3 entire columns?

[@gbadeboabayomi9241]

The Dynamic Name Ranges (Offset) didn't work out well when I experimented with the function on Excel Office 365. @Sean, could you please try the function on Excel Office 365 and let me know what to do differently?

[@hezekiahabdiel6868]

You prolly dont care but if you guys are stoned like me during the covid times you can stream pretty much all of the new series on Instaflixxer. Been binge watching with my gf these days =)

[@brunoduncan2381]

@Hezekiah Abdiel Yea, have been using InstaFlixxer for since november myself =)

[@jyothikaniti1662]

Wonderful tutorial! Loved the detail explanation but Couldn't find a link to this data to follow along :(

[@LearnitTraining]

Hi Jyothi! The link to the exercise files is in the video description!

[@jyothikaniti1662]

@@LearnitTraining Got it! Thanks.

[@RashidAli-qy2oo]

Great

[@LearnitTraining]

Thank you!

[@edwardjohn7644]

What's the difference between this excel power users and the first three excel 2016 lessons..... Is this a continuation from excel 2016 advanced users??

[@rajuar1955]

Sir I tried using this formula =VLOOKUP(H1&H2,CHOOSE({1,2},First_Name&Last_Name,Salary),2,FALSE) At 58:28

[@muonkret7817]

Good

[@LearnitTraining]

Thanks!

[@tuannguyen-ui1rl]

Hi, which video talks about the absolute referencing? Thanks

[@LearnitTraining]

Using Named Ranges to Retrieve Data 17:18

[@checkininin]

May I use Name Range in formular of Conditional Formatting?

[@LearnitTraining]

Hi check in ho! You can get your question answered at Offsite, our online community - www.offsitebylearnit.com

[@md.humayun-bin-akram9479]

In 'Data Validating Conditional Formatting" if I want to find only D, R or H exclusively, how can I do it?

[@snehakembhavi9541]

This should do the task - =IF($M$70, $H4=$M$7)

[@csc3745]

In Excel 2021, no need to hold ctrl+shift and hit enter anymore :D just hit enter its fine~

[@LearnitTraining]

Thanks for the tip!

[@tahrin.choudhury]

Time 39:43 why column no 1 =INDEX(LAST,MATCH(A5,EMP,0),1) ? l did not understand. Can someone please explain it to me?

[@SebaDuron]

Because the Array in which to look for the answer is constituted of only one column. So the fonction is going to scan various rows in this only one column. In the previous example where Sean Bugler explains how the Index fonction works the array is made of several rows and several columns, in which case he has to specify the number of rows and the number of columns. But here since there aren't any choice for the column, it can't be anything else than 1. That's my explanation anyway. Hope that helps.

[@cesarpassmore3772]

13:44

[@jonun8950]

The last formula isnt working properly, its not able to pick up cells if you type two letter like DR or RH etc

[@kensimba7868]

Hi John. U can just add 'DR', and 'RH' on the data validation list. It should work that way

[@paolosellan9445]

In the array function tutorial I think I typed exactly the same formula in E8 but it results in "Karen" rather than "Richard". =OFFSET('Array Function'!$A$1,1,0,COUNT('Array Function'!$A:$A)-1,1)

[@TY-zl1vw]

Posted your formula in the Excel file I'm playing with, it turns out COUNT used instead of COUNTA is causing the issue.

[@CHINWE_ONUCHI]

We need the excel files please

[@LearnitTraining]

Hi Chinwe Esther, we have added link to the exercise files!

[@CHINWE_ONUCHI]

@@LearnitTraining Thank you. Got the files now.

[@yandl4665]

Pretty confusing. Giving wrong formulas then showing the right ones... I'll be tempted to retain the wrong solutions.

[@milliem4178]

Not confusing. It shows unwanted surprises you can stumble on when forgetting vital components on the beginner level.

[@kensimba7868]

I prefer to see those common mistakes then their solutions so as to know how to deal with such a scenario. Good job by Sean Bugler