☑️ Top 30 Advanced Excel Tips and Tricks

Learn the top 30 advanced Microsoft Excel features you need to know to go from beginner to advanced Excel user.
Table of Contents:
0:00 Intro
0:22 1. Get Data from Folders
5:34 2. Text to Columns
7:22 3. Remove Duplicate Values
8:28 4. AutoFill & Fill Handle
10:41 5. Flash Fill
12:55 6. Custom Lists
14:36 7. Freeze Panes
15:48 8. New Window and Arrange
17:21 9. Tables
21:37 10. Ideas
23:00 11. Rich Data Types
24:53 12. Fuzzy Matching
29:28 13. Data Validation Dropdown List
32:00 14. Data Validation Cell Message
33:26 15. Create a Named Constant
35:03 16. IF Function
36:29 17. COUNTIFS Function
37:34 18. SUMIFS Function
38:54 19. VLOOKUP Function
40:06 20. INDEX & MATCH Functions
42:59 21. Dynamic Arrays Functions
49:14 22. Display Formulas
50:10 23. Filters
52:55 24. Advanced Filters
56:23 25. Tables Slicers
57:58 26. PivotTables
1:02:09 27. Pivot Table Slicers & Timelines
1:03:49 28. Show Values As Calculations
1:05:17 29. Data Model Relationships
1:08:48 30. Data Model Advanced DAX Formulas
Get the workbook used in the video to follow along: howtoexcel-my.sharepoint.com/... 👇
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
DOWNLOAD the example workbook here
📖 www.howtoexcel.org/downloads/
SUBSCRIBE & get my 3 FREE eBooks
📧 www.howtoexcel.org/newsletter/
CONNECT with me on social
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!

[@HowToExcelBlog]

Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/

[@nkululekonzimande780]

I discovered your channel yesterday and am loving it. As an advanced Excel and Power BI user, your channel is amazing for incremental learning.

[@HowToExcelBlog]

Thanks! Glad to hear you are enjoying the channel!

[@nancyporter3380]

Have watched at minimum 20 Excel videos and this one is by far the best! Thank you

[@aqibshah7225]

Excellent tutorial! Definitely recommended for learning Advanced Excel.

[@HowToExcelBlog]

Thanks!

[@patrickwong2923]

Short and to the point. Thank you, John.

[@64mustangfan]

I had a quick view, paused your video, and have referenced it 4 times today in practical work, thanks!

[@HowToExcelBlog]

Good to hear! Hopefully you saved some time and effort with your work with the new techniques!

[@Snowtu69]

Wow! I spent half the night watching your channel. Highly recommend that even advanced users watch your tutorials. I've been using Excel for 25 years and love it. I knew many of your tips already but it was so worth watching anyway. The new functions I learnt will save me many hours.

[@HowToExcelBlog]

Thanks Terry. There's always something to learn in Excel! I'm still learning too!

[@simpleexcelexamples9619]

Love the content of this video and your way of teaching! Thanks for the video which gave me inspiration for my own classes (I give basic Excel training to students) 😊

[@wintermorillo6567]

Great content, some I knew, some I did not, but it's always great to see things from another perspective. Watching this tutotial was totally worth it. Thank you!

[@darrylmorgan]

Some Really Helpful Tips And Tricks Happy Days!Thank You John :):):)

[@jacobwilliamson3096]

This is just great! Thank you so much! It'll really help me unpack Power Query which is just a bit dense for the new initiate :)

[@lisaamante5384]

Las Vegas, NV, USA: This was so awesome! Thorough, clear, concise, fast-moving but really easy to follow. Outstanding! Thank you so much!

[@HowToExcelBlog]

Thanks!

[@lilijastepanova4425]

Vielen Dank für ein riesige Information für Microsoft Excel, die Zusammenfassung ist voll gelungen. Ob es jede so korrekt schaffen wird, fraglich. Sehr Hilfreich! Respekt!

[@yolindabaquero8092]

Awesome! Thank you for putting this together! 👍

[@HowToExcelBlog]

You're welcome Yolinda!

[@lfqme]

Thanks John for this useful compilation of knowledge 👍

[@HowToExcelBlog]

No problem Luis!

[@JustOkko]

Wow, as someone who is quite advanced already I can definitely say that this contains almost all advanced tips I would like people to know, plus a few more I didn't completely know yet. Well done!

[@HowToExcelBlog]

Thanks! Glad you gained a few tips out of it.

[@FadiSaid]

Awesome. Very clear and straight to the point.

[@HowToExcelBlog]

Glad you enjoyed it Fadi.

[@AkomAwareness]

Thank you so much for doing these videos, you are a legend.

[@HowToExcelBlog]

Thanks 👍

[@sudhirlimbachia2977]

Valuable tips, thank you.

[@HowToExcelBlog]

👍🙂

[@annajurakulov3747]

excellent material! thank you

[@delnazdaryabari9947]

Awesome video and great tips summarized for every level of Excel users! Thank you!!

[@HowToExcelBlog]

😃

[@leslie5410]

Hands down the best excel video I've seen so far.

[@HowToExcelBlog]

...but you've only seen one Excel video ever 😂 Thanks!

[@juanfelipeospina658]

Very clear! Awesome tips & tricks, thank you!

[@HowToExcelBlog]

You're welcome Juan!

[@jerushanmoodley2641]

great video! very insightful

[@ashildrtheswift3028]

Thank you for this video! It was very helpful

[@farbodmaher4589]

Awesome! Thanks.

[@dr.s.p.]

Excel lent ! Thank you; most helpful.

[@shawnw.4440]

OMG! Just the Ideas tip was worth the time to watch this video! I had noticed the Idea button but I'm in the process of designing an executive dashboard (using the functions I already know) and was too focused on that to try out something new. Voila! Ideas will auto create a few of the charts I had planned. But the best thing Ideas did was present 35 options but most were not useful because of the way my data was structured. So rather than spend my time creating pivot tables and charts, I reorganized my data and let Ideas create the charts.

[@HowToExcelBlog]

Yes, reformat data with power query. Proper data is key to building a great dashboard.

[@AdityaModak]

Very Very Helpful. Thank you!

[@parshuramnangare8577]

Thanks for sharing such a valuable information.

[@berkuysal3773]

It's super helpful.Thanks

[@NoelRojas]

Good stuff. Thank you.

[@SudhirKumar-er5zs]

Wonderful course!

[@shrirajdeshpande8125]

Loved the DAX part. Awesome trick. Thank You!!

[@HowToExcelBlog]

Dax is great and you can use the same dax formulas in power BI!

[@rogermarquezmedina8865]

Can`t thank you enough for these tips and tricks. They surely help saving a shit lots of time.

[@HowToExcelBlog]

💩 ⌚

[@alyahyai]

This was very useful and practical tips, thank you

[@Luciano_mp]

Valuable tips, thanks for the spreadsheet provided. Thank you John.

[@HowToExcelBlog]

No problem Luciano!

[@nafryk]

where did you find its exercise sheet?

[@HowToExcelBlog]

www.howtoexcel.org/downloads/

[@dp6736]

Very amazing and updated tutorial which is easy to follow. Thank you very much.

[@HowToExcelBlog]

Glad you enjoyed it!

[@clduan6845]

Great, thank you!

[@HowToExcelBlog]

No problem!

[@kevinj5989]

Wow! What a great summary of features. At 2x speed, you have to pay attention to what's happening! Thank you!

[@HowToExcelBlog]

You can always rewind 🙂

[@kevinj5989]

@@HowToExcelBlog Yes, I rewound in one or two places. I'm not saying that you went too fast. You were great! I'm saying it was an even more enjoyable ride at 2x. Thank you!

[@computertutorialsandmore489]

Really nice tips. Thanks

[@warrenhall1750]

What a wonderful and pleasant way to spend a few minutes learning a great deal about Excel. Many thanks for a job well done.

[@HowToExcelBlog]

Thanks Warren. Glad you enjoyed it!

[@bestdigitalmarketingcourse9785]

@@HowToExcelBlog This is very use full tips of the Using Excel there are more tips www.inspizone.com/ms-office-course-singapore/advanced-excel-course-singapore/

[@pralhadshetye8863]

Please keep uploading more videos. You are a champion !!

[@HowToExcelBlog]

Will do!

[@AdeKaryadi]

amazing, nice tutorial

[@chillpill7348]

Beautiful video.

[@Deedington]

Thank you Sir!

[@samanthamccarthy325]

Gosh - this is really quality stuff. Thank you.

[@HowToExcelBlog]

Thanks! Those that spend the hour to watch will hopefully save hundreds of hours with the learnings!

[@AdeKaryadi]

nice video, thanks

[@irfanshahrom6775]

Thank you so much for the video! Very well explained with crystal clear instructions. I would like to ask on Hide and Unhide feature in Excel. Do you know if there is any hotkey available or an easier way to Unhide all hidden columns and rows at the same time?

[@excelexcellence4238]

Amazingly presented. Very well explained.

[@HowToExcelBlog]

Thanks!

[@mughalsatitsbest]

Great tips sir, continue the great contributions in the future.

[@HowToExcelBlog]

Not to worry, that is the plan 😉

[@aiTechSolution]

Unique effort... Thanks a lot

[@HowToExcelBlog]

UNIQUE()

[@KC-qr4sc]

Excellent Video... Thank you.

[@HowToExcelBlog]

Welcome!

[@norainiabdullah5846]

Great video. Useful tips. Thanks

[@HowToExcelBlog]

You're welcome!

[@hansfeldner6638]

Very helpful video.

[@Really-]

Amazing video I think Excel has the option to distinct in pivots - if you added to Data model before excuting, as far as i remember it was in the value calculation when you click in the values section.

[@yilia1179]

Really helpful!

[@HowToExcelBlog]

Great to hear Yilia!

[@lazyman2811]

clap!clap1clap! a very powerful presentation from an old salt

[@ndelpurg]

Thank u excellent glad i found u

[@HowToExcelBlog]

I'm glad you found me too 🙂

[@kobourou]

I once was blind and now I seeeeeeee......thank you bro.

[@KashiGirl911]

Wow.. some great tips and explained well! I learned a lot. Super video.. thank you :)

[@HowToExcelBlog]

You're welcome Janis! Glad you learned some new things.

[@marinedarbinyan9217]

very useful tutorial, Thank you

[@HowToExcelBlog]

Glad you think so!

[@jagamohansahoo6655]

Its fantastic learning. Way of teaching is outstanding

[@HowToExcelBlog]

Thanks for the kind words!

[@jingwu5075]

Damn get data form folder so awesome. Thank you!

[@HowToExcelBlog]

Power query in general is so awesome 💪 ⚡

[@dougmphilly]

@@HowToExcelBlog i load 200k rows by 10 colums into excel, forever. load same data set into PQ, boom.

[@allabout1135]

Hi John. Well done, long and comprehensive video. Is it planned in future touch more power pivot, dax, and data modeling? Thanks for sharing You knowledge?

[@HowToExcelBlog]

Eventually, but hard to say when.

[@dexterreid926]

Thank you.

[@HowToExcelBlog]

Welcome!

[@lamranajalloh3210]

fabulous thanks

[@HowToExcelBlog]

You're welcome Lamrana!

[@amitbhandari4299]

Very nice video

[@vbatipsandtricks5237]

Very nice!

[@HowToExcelBlog]

Thanks!

[@wolverine8482]

Thanks John! We do a lot of budget and financial analysis. Any videos with examples on budget and financial analysis?

[@prathvisingh8288]

Man a million likes to you! This video is a treaure trove!!!

[@HowToExcelBlog]

Thanks 😄

[@gihantissera8140]

Great tips on excell

[@HowToExcelBlog]

Thanks Gihan!

[@SALMANANSARI-ly2vi]

Thanks sir

[@fathirinchi7822]

thank you

[@HowToExcelBlog]

You're welcome Fathi 🙂

[@rachidt7636]

Thank you very much it was very helpful , please to share that Datasheet

[@djtoonice]

Awesome👌

[@HowToExcelBlog]

Thanks!

[@casachintanvashi5464]

Super...Happy to see such good value addition knowledge on excel

[@HowToExcelBlog]

Thanks Sachin!

[@rokpodlogar6062]

great tips

[@HowToExcelBlog]

Thanks Rok!

[@ICTNuggets]

Great video

[@HowToExcelBlog]

Thanks!

[@wayneedmondson1065]

Hi John.. great list.. a real workout. One thing to note.. I don't think you need to use INDIRECT on a Data Validation list with the Source referencing a formal EXCEL table. Even though you can't reference the table name itself in the Source field and instead must highlight the actual range address of the table, when subsequently adding data to the table, the range address automatically expands within the Data Validation Source definition. So, no INDIRECT reference to the table name is needed. If data is in a list but not an EXCEL table, then INDIRECT or OFFSET or INDEX can all be used to make it dynamic in conjunction with a Named Range. Just thought I would mention it, as I discovered this feature one day which is a time saver. Thanks for all the excellent tips and tricks.. great stuff! Thumbs up!!

[@HowToExcelBlog]

Hmm, I didn't know that about using a regular range reference. I just assumed it wouldn't grow with the table. Will have to try it out. Indirect will have the problem of needing to change the data validation if you ever change the table name, so regular reference might be better.

[@wayneedmondson1065]

@@HowToExcelBlog Hi John.. for a list not in an EXCEL table, my go to method is either: =OFFSET($B$3,,,COUNTA($B$3:$B$20)) which is volatile, but can be entered directly into the Source field of the Data Validation definition or =$B$3:INDEX($B$3:$B$20,COUNTA($B$3:$B$20)) which is not volatile but requires giving a range name to use in Data Validation Source. In both cases, I defined a range down to B20 to give myself room for the list to grow. I'm sure you know these.. just using this as an excuse to practice them again and share my results. Thanks again for all the great insights and inspiration to learn. Thumbs up!

[@rajuar1955]

Data Validation :Select the column in your data entry table that you wish to add data validation to. Go to Data/Data Validation or Alt + D + L to open the Data Validation window. Select “List” from the “Allow” dropdown menu. Hit the F3 key and select your defined name from the “Paste Name” box .Now every cell in the column will contain data validation that is restricted to the values in the “Data Validation List” column. If you add another row to the Data Validation table, this will automatically appear in the drop down menus you have just created

[@rugwizainnocent1881]

so fantastic

[@HowToExcelBlog]

Thanks!

[@OnosBrisibi]

You are the best

[@HowToExcelBlog]

Thanks 😊

[@rithysokshare4820]

Hello teacher, Great video

[@HowToExcelBlog]

You're welcome 🙂

[@lettynavazquez5764]

Your classes are great! i love it. Is there a way to filter between times? Say for instance, I have a courier team who drives medical supplies back and forth all day. So we have 1st shift, 2nd shift, 3rd shift, overnight and weekend shift. I have 6 months worth of data and i was trying to see overnight shift only. So i wanted to filter it to where i only see routes picking up items from 10:30pm to 7:00am. Is this possible? do you have a tutorial on how to do that if it is possible? Please advise. :)

[@HowToExcelBlog]

You can create a column that calculates the shift, then filter on that.

[@stasynech]

Thank you for this lesson, really useful tips! I am really interested in advanced filter, I used it some time ago, but the biggest problem is that you can't make it adjusted for end user, as you you have table with some parameters above the table, every time you changed the data you applied filter by yourself. Do you know is it possible to auto-filter cells based on filled in data?

[@HowToExcelBlog]

Maybe you want to turn your data into a table then use a slicer to filter?

[@stasynech]

@@HowToExcelBlog thanks, yes if it is smart table it is a way, but I want to use flat table, anyway thanks!!!!

[@ekkiwurm7874]

Fabulous tutorial! Loved every bit of it. Thank you so much! Just a tiny question: How did you get the yellow circle around the curser? Again many thanks!!

[@HowToExcelBlog]

It's an effect from the screen recorder I use called Camtasia.

[@HowToExcelBlog]

Glad you enjoyed the video!

[@ekkiwurm7874]

@@HowToExcelBlog Thank you very much. You are a real gent.

[@winsbury]

Good video

[@HowToExcelBlog]

Thanks Mark!

[@sajedezare5219]

Hi, I have a question: How can I create a dynamic pyramid chart that is horizontally and vertically segmented? In excel or power BI. Thanks a million.

[@soniakeesee222]

Thank you for this lesson! Can you please tell me how to add my signature and graphics so that it appears automatically within my emails?

[@HowToExcelBlog]

Which tip are you referring to?

[@ICTNuggets]

nice

[@HowToExcelBlog]

Thanks!

[@JazzRadioFfm]

thanks for the great video. i have one little problem though: i want to add columns to my queried table, but as soon as new data is added to the table, the data from the new columns loses its reference. is there a ways to keep the data in the right place when new data is added?

[@HowToExcelBlog]

Which to are you referring to?

[@rasoulkhoshravan5912]

Excel has many different versions. It will be a good practice to mention which Excel version is the video talking about. Maybe for most part, they are common for different versions and this is good, but there are issues which differ from one version to another and this confuses the user. In this particular video and first tip, I am using 2013 version and I don't have "from folder" option. Also different options in "From other sources" are very strange for me and I couldn't understand them. I should search in Internet for their meaning and usage.

[@HowToExcelBlog]

I use Microsoft 365, so it's airways the most up to date version.

[@davidlam5988]

Hello thank you for your nice tips! very interesting! but I have an issue on the first tips about Getting data from folders, when I remove one excel files I can not refresh anymore my table. Do you have any tips? thank you

[@louisd95714]

Nice demonstration, but I want to add that the new functions are also available on Microsoft's web based version of Excel, so there is no need for Office 365 if anyone wants to casually try these out.

[@HowToExcelBlog]

Yes, that's true. Most people don't know Excel online is free too!

[@monitoringandevaluationtitbits]

Nice tutorial. Learned something. Kindly share the workbook. The link provided is broken.

[@SGGraphix20000]

Thanks a lot for sharing this informations, the (Data Types) I don't have on my excel, how to bring pls, thanks.

[@HowToExcelBlog]

Hi Samir, it's only available in Excel for Office 365 and you need to have internet access and be signed in to your Microsoft account.

[@SGGraphix20000]

I have microsoft account but I have excel 2019 only, thanks :)

[@HowToExcelBlog]

Unfortunately, it's not available in Excel 2019. But you can still use it with your Microsoft account and Excel online.

[@dougmphilly]

get office 365, much easier with the updates.

[@inuinu8004]

These are great tips, but for the first tip: isn't it too complicated for adding a file in a sheet in a workbook? For what purpose would you use it this way? Thank you!

[@HowToExcelBlog]

Adding a single file, yes! But combining data from hundreds of files, it will be the easiest way.

[@inuinu8004]

@@HowToExcelBlog thanks a lot for replying to my message. Subscribing to your channel is really valuable. Keep up the good work and Thanks a lot again.

[@erictsang789]

Thanks and i am using Excel 365, there are few formula that i would not fine such as sortby, filter? may i know where i would get it Eric

[@HowToExcelBlog]

They are available in Office insiders. insider.office.com/en-us/ They might also be available in the monthly update version, though I'm not sure.

[@PattyBall24]

can't seem to download workbook from link. Looks like a personal sharepoint account?

[@Ty33Budd]

First tip to get Data would be huge for me. Now how do you get the data from a folder with sub folders? I've tried to do so, and I get an error of [Expression.Error] The key didn't match any rows in the table. If I pull from each individual folder, I don't get this error, is there a work around?

[@HowToExcelBlog]

Are the files the same structure across each subfolder?