Excel 2016 Advanced Tutorial

Excel 2016 Advanced Tutorial
Get Ad-Free Training by becoming a member today!
/ @learnittraining
Exercise Files: bit.ly/2S67Byv
Who it's For: Anyone currently using Microsoft Excel, or anyone who wants to learn how!
What it is: Excel is a popular Microsoft application that organizes and analyzes data.
What You'll Learn:
Our beginner videos start with simple tasks like selecting cells and entering data, then quickly move to calculating data with basic formulas and functions. The intermediate portion shows you how simple it is to sort and filter and then moves to one of Excel’s most popular features, Pivot Tables. Our advanced videos start with functions like IF and VLOOKUP and finish up with Macros.
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
Named Ranges 0:03
Creating Named Ranges from Selection 5:24
Named Ranges (Continued) 8:31
IF Functions 11:15
Formula Auditing Tools 19:26
IF Functions (Continued) 26:05
Nesting Functions 30:51
VLOOKUP 38:42:19
Autofill VLOOKUP & IFERROR 47:20
VLOOKUP Range 54:01
VLOOKUP Range with Nested Functions 59:31
HLOOKUP 1:07:27
SUMIF 1:11:17
AVERAGEIF 1:19:20
SUMIFS 1:23:12
Date Functions 1:27:47
Text Functions 1:34:27
Consolidating Data 1:41:00
Consolidating Data Without Structure 1:45:28
Complex Solver Tool 1:48:27
Solver Tool 1:54:09
Scenario Manager 2:02:11
Data Table 2:09:38
Turning on the Developer Tab 2:16:08
Recording Macros 2:18:18
Editing Macros in VBA & Recording a Second Macro 2:23:46
Adding Macros to the QA Toolbar 2:31:27
Adding Macro Buttons to the Worksheet 2:33:46
Conclusion 2:36:52
#Excel
(C) 2021 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.

[@sureshs4903]

To all those who are watching this video, here is what I recommend.. While watching the video open the provided work book and practice it in real time by pausing the video. Practice the same after 1 week.. Continue this exercise two more times with a gap of 7 days.. It stays in your mind for a longer duration...

[@LearnitTraining]

Practice makes perfect!

[@blucoco162]

Amazing advice thanks

[@tesfitwell4078]

4

[@tesfitwell4078]

4

[@tesfitwell4078]

4

[@yadavallisireesha3261]

Excel 2016 beginner to advanced are by far the best of excel series that covers excel in detail

[@LearnitTraining]

Great to hear!

[@tochukwuezeukwu5582]

This is about the most impactful excel tutorial I have watched and used. Thank you Sean. You also teach incredibly well.

[@ebkhuner]

You are a GREAT teacher. You have almost no extra words. You speak clearly and concisely. Thank you!!!!

[@LearnitTraining]

Thank you! 😃

[@bartphillips8513]

This is the only Excel Training Course that explains how to properly use Excel and explains the steps percisely at a proper speed for ANYONE to understand, without having to pause or replay the video. For months and months Ive tried to understand formulas especially and in one single View from watching the Video, Shawn explained everything in such a way, I had to laugh at myself for looking into the picture, just from the wrong Angle/View. The ONLY TUTORIAL THAT GUARANTEES YOU'LL UNDERSTAND IT! A++++++++++

[@gpapiashvili]

Thank you very much for this tutorial. You explain everything so clearly that it’s very easy to understand.

[@sxtywei0001]

31:38 “This leaves me time to focus on the more important nuances of my job like deciding what I’m gonna have for lunch” - That's gold, man!

[@Revantrekk]

the man has his priorities set straight

[@kennyanaedozie4126]

Best tutorial ever. This tutorial is a must have for anyone who wants to learn excel. Thank you so much.

[@archita978]

You are the perfect teacher. Thank you a million times over for sharing these tutorials.

[@Wasur1959]

Wow, Amazing how much I learned I have been using office and excel since it's birth, I now realize I know very little about office 2016 that I own. Thank you Learn it for these videos and these instructors. Thank you Sean you did an awesome job!

[@msmsab1974]

Thanks for the effort, this is one of the very best Excel Tutorial I,ve seen so far, well done

[@edgarasagintas1014]

Most underrated tutorial ...big thanks for such a good explanation with good examples.

[@Why-yq4wj]

this guy's tone makes the tutorial,, though long, very engaging. He explains each concept as if teaching it to a baby (i might as well be one in terms of excel skills). Many thanks and I hope this channel grows huge.

[@LearnitTraining]

Thanks for watching!

[@tracymmary3057]

One of the best tutorials I have ever come across. Many thanks!

[@issahakudawuda5410]

Thanks for this wonderful presentation. I highly recommend this tutorial to anyone who wants to use Microsoft Excel productively.

[@LearnitTraining]

Thank you for the kind words Issahaku!

[@electronlegacy9051]

Explained very well. Only had to watch once and remembered it the next day!. Some of it I knew and other bits I didn't. Only watched the named ranges and IF function so far will be watching more tonight. Thank you!

[@droopy_911]

Logged in from 2 different accounts to like this video twice. Love and regards from India. 🙏💜

[@joshuamitchell6204]

Great video! I realised at ~1:06:00 the function only works because you've got either "Holiday" or one other option. However, if you had more than 2 options it would be truly automated by using an IFS function and adding "values if true" as necessary

[@RJB293]

Starting a new job and I will highly be using all these functions, so thank you for teaching us all well.

[@LearnitTraining]

You're very welcome!

[@babuumakantvuppala9598]

Thank you Mr Sean. It was a great learning experience at LEARNIT. Looking forward to more such delightful teaching techniques. Regards.

[@chrisnaidoo4596]

One of the best if not the best Excel Tutorials I ever watched attentively....Keep it up Sean!

[@LearnitTraining]

Glad you liked it!

[@samuelomondi7493]

This is awesome. I always thought that I know a lot about excel until today. Thanks for this.

[@prescillastoyanova]

I have been trying to learn advanced excel for years (like, over 10) and this was the best lesson I've ever had. Thank you so much!!!

[@femimidas]

This is literally the best Excel training I have seen. Great job!!!

[@AdelGergios]

✔✔👍👍😊😊😀

[@gautheny]

At the beginning using the function box i was not impressed until you started merging If,lookup,sum all together :D. Well i am not going to type functions the way i used to before. I am finishing the series in about 40mins. Thank you very much. I have learnt a lot. Bless you

[@theodoraapostolakou7382]

That was an amazing tutorial!! Thank you so much for sharing... You are a great teacher .. !!!!

[@tedkasaye5897]

'Pause the video to catch up"...love it, what a classic video !!!!!

[@amrkhalilomarelsaftawy6967]

From Egypt, Thanks from here to infinity :)

[@mahfuzulhaquenayeem8561]

Great! The explanation is so clear. Thank you so much.

[@yulondamgoldenharris1531]

Omg! this is great I needed this along time ago, all the money I've missed out on jobs.

[@LeaRama]

One thing you should add to the Solver and Solver2 parts is that formulas have to be contained in the Objective cells (i.e. Total Shipping Costs). It wouldn't work for me until I did that. Other than that-- FANTASTIC tutorial! This is what I've been looking for!

[@LearnitTraining]

That's a great idea!

[@ikpemosiumezurike9679]

Really wish you could pour more light on this topic because I am stuck herr

[@user-od4fz5ls3d]

Thanks all for your great effort!!

[@LearnitTraining]

Our pleasure!

[@OracleBizz]

I could watch your demonstration of hours... Very clear thanks

[@Jess-hv6rq]

Thank you for making this video! You helped us a lot in being more knowledgeable of what Excel can do. Two thumbs up to you!

[@LearnitTraining]

Glad it was helpful!

[@PrasThapa]

THANK YOU SO MUCH FOR PROVIDING SUCH A USEFUL TUTORIAL.

[@fatenaljoaid8521]

Dear Mr. Sean Bugler I would like to thank you from my heart for this amazing video really it is valuable I learn a lot of things that make my tasks easier your style in learning is amazing God bless you wishing you the best in your whole life best whishes

[@sanketnayak6761]

Thank you Sean for such an amazing tutorial.

[@ajohnson1418]

Excellent training video. I learned a lot from watching and practicing the commands.

[@syedghazali8523]

An outstanding video, clear, concise and comprehensive. thank you very much

[@karlee8608]

just found your channel and I must say - the video is incredibly incredible. Thank you for sharing your knowledge.

[@LearnitTraining]

Thanks for watching!

[@xalilbayramov1395]

I rarely leave a comment but here I must. Thank you very much. The best explainer and lesson ever

[@tanayghildiyal4123]

It was an amazing interesting and lovely taught lecture .Thank you so so much 😃🥰

[@triplee7853]

I learned a lot from your video. As my way of saying thank you, I didn't skip the ads. Thank you.

[@LearnitTraining]

I appreciate that!

[@gabrielmercadal7159]

Great Video, but be careful with Scenario Summary information at 2:09:00, since it is showing INCORRECT results for "Southern Expansion". It should be $1.343M, so "New CEO" exceeds "Southern Expansion". The error is with the current values changing the "Southern Expansion" original "Standard Scenario" data to "New CEO" data with only the change of South ($C$14) returning to the increased 4%. Be careful of this for future company analysis.

[@ahmadkadid1022]

thank you man i really appreciate your explanation and the time that you have put into this video. All the best mate

[@dvanle]

Thank you very much for your explaining and clearly to understand :)

[@ShivamSingh-fz3ip]

Very well explained. Thanks for making these exel functions easy for us.

[@wilmamallillin8608]

now i am about to finish the 5day webinar sessions. Thank you very much. i learned

[@EveHakika]

Very grateful for this video. I have learnt a lot in less than 3 hours. Thanks to COVID 19 I have plenty of time to watch this video and many more learning materials

[@pranavpathak1550]

THANKS A TON! YOU ARE DOING A GREAT JOB AND IT IS FAR BETTER THAN VIRAL ENTERTAINMENT VIDEOS ON KZbin.YOU ARE HELPING MANY!

[@wilmamallillin8608]

I thank you so much for the 5day seminar. the speakers....hmm. very knowledgeable. Amazing for I had learned alot

[@Neil-ll1np]

Awesome tutorial! Thanks so much for this tutorial.

[@rufusmooreiii2567]

This was a very helpful video condensed into a two-hour segment. Thanks for the material!

[@LearnitTraining]

Glad it was helpful!

[@zeeshaniqbal8214]

at 53:04 i couldn't stop my self to comment bcoz i learnt a lot and i'm sure more to come to learn. thanks a lot to share such a valuable skill at free... thanks again./

[@LearningHome-trainings]

Very detailed description. Great job.

[@ErickHelena]

Amazing video , I watched all 3 and all i can say is thank youuuu so muchhhhhhh

[@miroslavjordanov4459]

The best free Excel tutorial i've seen so far. Learned a lot from it. Thank you very much. :)

[@coraboykin1895]

Your excellent teaching and training will definitely help me to complete the database workbook that I'm working on! You have made me happy. Koodos!!

[@LearnitTraining]

Thank you Cora! Glad you had a great time watching and learning!

[@tweetybird3448]

Thank you for your clear explanation.

[@srsharma6617]

Excel made to excel by Sean - Yours is one of the best in Excel Learning - I have done Basic, Inter and Advanced without practising - Just to know what the Store has - and the Store has lots - Now I am going do it from the beginning slowly by pausing and practising alongside - there is one thing I didnt find - how to bring the Price automatically when item is chosen - Hope you will calrify - Thanks for the great set of Videos.

[@eiasu5745]

Ftxctftfttxttftxtxffctfftxtxffcfftft,fcftxtxtxctf,fft ftxffftfxtxttxcffctfffctxtf,txfcctxtttxtxctttff,txtftxtx"/_/'xtcttfttxtxtfftxctxctxftxtfcttctfftftxfftfffffcffctxffffcftxftxctctfftxtxfffffctxtxt,txtfctfxtxctxffctxftxtttxcfctxtftctxfttffftxtxttttffffcftxtxttxffcfctftftxtctxtxtfttxtxcffcftxtxtfctxfftxcfxfctxftctffcttxctxtfft,txfffftxftfttff,ttxfftftxfftctfxtftftctxctxggtxtctxffffftxttxfcffctxtftxcftfftxfcfftxfftxtfftxfxftxtfxccfcfftf ftxtfffffctxffttxctf tfcftxftfxctfxftxctxtxtfcttxfttxtxvttxctxftfffftxtftxttfffftftfxftxttfffftftxctxtftxctftftftxftcttxtctxcft,txtxtttftxtftxtxtftftfctxcttxfftxtxtxtft txtxftxftxct,fcffftxctxtxctffffxtctttftxtt txft tfctxtftgtxfcttcttftftxffffctfffcftfxtctxxfffcgfctftxftxfttxfctfxtttfffctctftctffftxftfxfctxfcftxctxftfctxccfftxxctffftxftxtxtxtctxtxctfftxftftxtftfcftftxctffftctxffftxxtffffxfftfftxfftctxfffxtfcffcfctxxtfcctxtxctxftxccftffcfcfxftxffctxtfctfctfftfctxfftxfftftxctftftfftctttxftffttffctxfffxtfcffffffcfcfffttfctftxxxtfcffftxffcffctffcffftxfftxfftfxffffcffftxffcctftxctxtf,fftxctxftxcftctxtttxttxfftfggtfctctxctxtftxfftfxtxctfftxxtftxfcffftftfxfttfctxfffffctfftfxffftfftxfcftfftffftfxtftxctf,xcfxftxcffctxftxffftftfcftxttxftfxtttfttxfftxtxftxfcctffxtfxtftxcftxxtfffftxtxfftxftctfftxtxtxtfctttxctxftxtffctxftctftfctfttxtftxtfttxtctxfftftxfftff,f,ffftxfcttxtfctftxfftctctxcfttttxffccgtctxtfft,txcgtcttxftxtxftxtxctxcgctftxcttffftxftfffffftftffftftxfctfttxgfffftfffffffcftxtftftxtxftffttctxftxctcttftf,fftxfffftftxtftctxftfffffctxtxfcttftxyxtxtxtftxffttxttftftxftxftxtxctxfgtxtftfctftffffftxtfftttxftxtxfttctftxtffffcgcgtctxfftxctffftxtftxtfffftctftttffttxttxtyxfttxctfftctfxgfftxctxtfftftxftxfftftxtftfxftxtftxtctxtfftctxftftxtxtfffftxftxftxfftxftxtxtxtctxffftxtfffftxtfctftttxtxtfftxtftxcttffftxftxtfttxttxftxctxtffftxftftxcttxtxtf,txtcctxftxcgfftxtctffftfctftfftxfftgtxfffftxftfttxtffttftxftftxfxfvtxffcttftxftxttffftxctxtxftxtxtxtxftxffttftftftfftxtftxtftxfcfftxtftffffctxctxttxt,gtf,txftxgfftxffcgftctctxftxtfffftftftxffftttfctfgtxfttftxtffffftftctffftcttxtttfftffxtfffffffcgtfttffffftffttfftfxftxtftxftxtttgtfttxtfxctctftftct tfftxtfctfttxtffctxfftxtxttf,ftxfftxctftftxfffffftfcfftxtfxtftftxcgfctcttxtxftxctfffttfxftxtxtxgtxtftxtxtctxftxfftctxtctxffctxtxtxtffgctctxtxctxctxfftxffxtctttfttxtctxtfftxtxtxftffctxffttxfffgtctftxtgfcgttxgtxtfftctctctffftftxtxfftxfftxttftxftcgfftctfcttxtfffffftctxtftxtttftggtxtffctcttxgcgtftctxgtcttffftxtfftxftfftfffffctxfttfxtfftfctftxtfctxcfttctxftffftxtxffftffftxtftxxfftfftfffftxttffctxtxtxftxctcgfftxtcgtxffttxtxtxtxcgctttfctcttftxtxtfctxttftxgfftxtftxgtxtftxftxtxftxttctxtxtfttfttftfftctfttftctxtxfffcftxftxtffctxfgtxtffcftxftxtxtttxftxtfcffffctctftxtftxtfxtfttffctxtxtxtftffffftctxtxfftxcttfftxfffcfftxfctxftctfxtftxftfctftxtxtxtfftctxtxtxftxct tffcttctttctxtxtfctftxtftctffttxfgf¥8999'

[@LamNguyen-yx2zd]

Incredible video. It was so helpful . Thank you very much , sir .

[@MrMarkgyuro]

wanted to expand my excel knowledge. very best video : ) thank you

[@linhtruongmy6233]

Very clear voice and great tutor, thank you very muchh !

[@lechkoper5378]

Thats lesson is just brilliant. I like the sense of humor which tutor is shering often. Really good work.

[@LearnitTraining]

Glad you enjoyed it!

[@harshanath9310]

Thank you very much for this tutorial. Keep up the good work.

[@aungaungminmaung9777]

That was an amazing video. I love it. Thank you very much .

[@pauladeosun7795]

This is a great tool for learning Excel. The lessons are very engaging and easy to comprehend. Thank you

[@LearnitTraining]

You're very welcome!

[@sebutsajadiaspongebob5331]

Ini namanya belajar dari rumah... gak usah pake kartu2an/iuran, tapi cuma modal paket data bisa belajar excel.. thank you Learnit Training 👍👍

[@BilluToley]

I appreciate you. I'll always remember attending your class !!!

[@krishnachaitanya9024]

Really useful. And from your videos people learn how to use these softwares & as a by-product u also learn how to teach / present topics.

[@LearnitTraining]

Glad to hear that!

[@marcelinapatay3847]

Thank you so much for very nice lessons about excel. good job!!!

[@th3n3wk1dd]

I work for a company where we have "wave days" and a list of stores that have dedicated ship days and built a spread sheet using named ranges. Store ABC might be a Monday but broken down by regions too.. So Store ABC might be in Region North and ships out Tuesdays So the "Named Range" would be NTUES and ABC would check to see all the various tables I named using a mix between CHOOSE, INDEX/MATCH, and and INDIRECT formulas while each "WEEKDAY" is already assigned a number by excel, it makes CHOOSE every easy to know which Table to use "Index/Match" on. Without named ranges, these formulas would be extremely long. but with named ranges, it shortens the formula quite a bit. Since I get one big Data File, I rank out each Store by the day of the week and boom. Organized information with Stores ship days All parsed out from a mass unorganized Data File. Great information here Keep up the great work.

[@itsfaisal007]

I'm commenting without even opening this video. I came to know from comment section that this video tutorial is very helpful. Thanks in advance sir. I've subscribe your channel and will recommend it to my colleagues.

[@suleimanmulindo6689]

Thats being funny though

[@jianjunwong7810]

lmao

[@2enslavedspirit]

LOL!

[@mqp414]

This is very helpful. Thank you so much, Sean Bugler.

[@kamaldaruwalla6172]

Thanks Sean, for teaching me and not making it boring!!

[@LearnitTraining]

Glad we were able to make it interesting and fun!

[@gcfizambia2509]

This is more than I really thought. Thank alot..

[@akkarajukavyasri6976]

i have taken all the begginer, intermediate and advanced in excel...i have to say wonderful explanation, very patient...and yet very engaging

[@LearnitTraining]

You are welcome!

[@mahiradolan4160]

Thank you so much for this useful tutorial! I hope you can make more videos in future.

[@LearnitTraining]

Thank you for kind words Mahira! We are doing our best to get one new video out each week!

[@zeeshaniqbal8214]

thank you so much for making this video. make more videos like this i want to learn everything in excel..

[@norieagirdelapaz1654]

Thank you so much! This is very helpful.

[@madu2win382]

thanks for all your support

[@LearnitTraining]

Thank you for watching and thank you for subscribing!

[@jack4q2]

Thank you I learned a lot from you sharing your knowledge. Time is valuable so thank you for sharing your efforts . 2 hours 38 mins of good value.

[@LearnitTraining]

Glad it was helpful!

[@muhammedshameemn2045]

1:54:16 for solver basic 1:48:33 for solver advanced

[@DipanjanDasv3]

The only useful comment here (Y) Thanks!

[@infosystems8307]

Very Good tutorial fully explaining all in all. I love it

[@dereklee3995]

Thanks for the tutorial. It really helps. I would also like to know how you created the red arrows and boxes as highlights during your tutorial presentation on Advanced Excel. That's so amazing. Thanks.

[@nickas222]

i think his recording program do that

[@wilmamallillin8608]

excel 2016 Advance. iam thankful because I know a little only on excel. Now I am learning.

[@haakin2262]

Thank you ! This was excellent!

[@hartdave9459]

This presentation is really simply demonstrated and very easy to understand and very helpful..... Thank you so much and look forward to more useful excel tutorials

[@LearnitTraining]

Glad it was helpful!

[@shivikagupta5046]

One of the best Trainer in excel. All Kudos

[@LearnitTraining]

Thank you for the kind words Shivika!

[@joshuabisessar180]

This is was very insightful. It has taught me new tricks and commands I can't wait to apply in my work! Thank-you very much!

[@LearnitTraining]

You are very welcome Joshua!

[@malinmalindic8582]

Thanks for nice lessons about excel, I we use IFERROR function , to get rid of of #N/A Errors , in that case VLOOKUP function is an argument of IFERROR function, but , if we follow that logic, maybe IFERROR function can be an argument of IF function and use conditional formating to present in nice view (red color) that value that we search for does not exist. Thanks a lot.

[@jonchapman621]

This is a very detailed video. I would watch this for full knowledge and playlist for short recall from Vanna Excel

[@LearnitTraining]

Glad it was helpful!

[@crystalathaide884]

Very helpful and very well explained. Thank you so much for making this tutorial.

[@LearnitTraining]

Glad it was helpful!

[@ayotundeokesola5661]

Best Excel training ever. You are the best

[@LearnitTraining]

Wow, thanks!

[@harrycallahan3221]

Awesome! Thank you!

[@glenearle451]

Thank you so much.....great tutorial.

[@timmytesla9655]

I have been searching for this kind of video for a long time. This is fantastic! Thank you. Is anyone else having a problem downloading the exercise file? I can only view the file but cannot edit.

[@LearnitTraining]

Hi Timmy! You can click on File in the upper left and then save as to save a local copy for yourself.

[@timmytesla9655]

@@LearnitTraining Thank you very much

[@kimberleevillella995]

Really entertaining! Also learned something about Excel I did not know. Keep it up!

[@LearnitTraining]

Glad you liked it!

[@juliusjeremiah3847]

Thanks a lot for this constructive tutorial

[@turboromy]

1:36:49 Love the way you pronounce HowardSmith.