Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/
@nancyporter33802 жыл бұрын
Have watched at minimum 20 Excel videos and this one is by far the best! Thank you
@Snowtu693 жыл бұрын
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.
@HowToExcelBlog3 жыл бұрын
Thanks Terry. There's always something to learn in Excel! I'm still learning too!
@lisaamante53843 жыл бұрын
Las Vegas, NV, USA: This was so awesome! Thorough, clear, concise, fast-moving but really easy to follow. Outstanding! Thank you so much!
@HowToExcelBlog3 жыл бұрын
Thanks!
@JustOkko3 жыл бұрын
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!
@HowToExcelBlog3 жыл бұрын
Thanks! Glad you gained a few tips out of it.
@nkululekonzimande7805 жыл бұрын
I discovered your channel yesterday and am loving it. As an advanced Excel and Power BI user, your channel is amazing for incremental learning.
@HowToExcelBlog5 жыл бұрын
Thanks! Glad to hear you are enjoying the channel!
@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!
@leslie54104 жыл бұрын
Hands down the best excel video I've seen so far.
@HowToExcelBlog4 жыл бұрын
...but you've only seen one Excel video ever 😂 Thanks!
@patrickwong29232 жыл бұрын
Short and to the point. Thank you, John.
@lilijastepanova44254 жыл бұрын
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!
@64mustangfan4 жыл бұрын
I had a quick view, paused your video, and have referenced it 4 times today in practical work, thanks!
@HowToExcelBlog4 жыл бұрын
Good to hear! Hopefully you saved some time and effort with your work with the new techniques!
@kevinj59894 жыл бұрын
Wow! What a great summary of features. At 2x speed, you have to pay attention to what's happening! Thank you!
@HowToExcelBlog4 жыл бұрын
You can always rewind 🙂
@kevinj59894 жыл бұрын
@@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!
@FadiSaid4 жыл бұрын
Awesome. Very clear and straight to the point.
@HowToExcelBlog4 жыл бұрын
Glad you enjoyed it Fadi.
@delnazdaryabari99474 жыл бұрын
Awesome video and great tips summarized for every level of Excel users! Thank you!!
@HowToExcelBlog4 жыл бұрын
😃
@aqibshah72253 жыл бұрын
Excellent tutorial! Definitely recommended for learning Advanced Excel.
@HowToExcelBlog3 жыл бұрын
Thanks!
@warrenhall17504 жыл бұрын
What a wonderful and pleasant way to spend a few minutes learning a great deal about Excel. Many thanks for a job well done.
@HowToExcelBlog4 жыл бұрын
Thanks Warren. Glad you enjoyed it!
@bestdigitalmarketingcourse97854 жыл бұрын
@@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/
@jagamohansahoo66553 жыл бұрын
Its fantastic learning. Way of teaching is outstanding
@HowToExcelBlog3 жыл бұрын
Thanks for the kind words!
@wayneedmondson10655 жыл бұрын
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!!
@HowToExcelBlog5 жыл бұрын
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.
@wayneedmondson10655 жыл бұрын
@@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!
@rajuar19555 жыл бұрын
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
@shrirajdeshpande81253 жыл бұрын
Loved the DAX part. Awesome trick. Thank You!!
@HowToExcelBlog3 жыл бұрын
Dax is great and you can use the same dax formulas in power BI!
@shawnw.44404 жыл бұрын
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.
@HowToExcelBlog4 жыл бұрын
Yes, reformat data with power query. Proper data is key to building a great dashboard.
@rogermarquezmedina88654 жыл бұрын
Can`t thank you enough for these tips and tricks. They surely help saving a shit lots of time.
@HowToExcelBlog4 жыл бұрын
💩 ⌚
@dp67364 жыл бұрын
Very amazing and updated tutorial which is easy to follow. Thank you very much.
@HowToExcelBlog4 жыл бұрын
Glad you enjoyed it!
@alyahyai2 жыл бұрын
This was very useful and practical tips, thank you
@pralhadshetye88634 жыл бұрын
Please keep uploading more videos. You are a champion !!
@HowToExcelBlog4 жыл бұрын
Will do!
@simpleexcelexamples96192 жыл бұрын
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) 😊
@parshuramnangare8577 Жыл бұрын
Thanks for sharing such a valuable information.
@excelexcellence42384 жыл бұрын
Amazingly presented. Very well explained.
@HowToExcelBlog4 жыл бұрын
Thanks!
@jacobwilliamson30963 жыл бұрын
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 :)
@AkomAwareness4 жыл бұрын
Thank you so much for doing these videos, you are a legend.
@HowToExcelBlog4 жыл бұрын
Thanks 👍
@ashildrtheswift3028 Жыл бұрын
Thank you for this video! It was very helpful
@prathvisingh82883 жыл бұрын
Man a million likes to you! This video is a treaure trove!!!
@HowToExcelBlog3 жыл бұрын
Thanks 😄
@KashiGirl9113 жыл бұрын
Wow.. some great tips and explained well! I learned a lot. Super video.. thank you :)
@HowToExcelBlog3 жыл бұрын
You're welcome Janis! Glad you learned some new things.
@wolverine8482 Жыл бұрын
Thanks John! We do a lot of budget and financial analysis. Any videos with examples on budget and financial analysis?
@AdityaModak Жыл бұрын
Very Very Helpful. Thank you!
@Really-3 жыл бұрын
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.
@norainiabdullah58464 жыл бұрын
Great video. Useful tips. Thanks
@HowToExcelBlog4 жыл бұрын
You're welcome!
@KC-qr4sc4 жыл бұрын
Excellent Video... Thank you.
@HowToExcelBlog4 жыл бұрын
Welcome!
@juanfelipeospina6584 жыл бұрын
Very clear! Awesome tips & tricks, thank you!
@HowToExcelBlog4 жыл бұрын
You're welcome Juan!
@marinedarbinyan92173 жыл бұрын
very useful tutorial, Thank you
@HowToExcelBlog3 жыл бұрын
Glad you think so!
@berkuysal37733 жыл бұрын
It's super helpful.Thanks
@houstonvanhoy7767 Жыл бұрын
Hello, John. I've been using pivot tables and viewing numerous relevant YT videos a lot recently, but I did pick up something new just now - your final tip about adding two tables to a data model and using both tables in the pivot table. Nice - thank you! 🦾
@jerushanmoodley2641 Жыл бұрын
great video! very insightful
@chillpill73482 жыл бұрын
Beautiful video.
@AdeKaryadi3 жыл бұрын
amazing, nice tutorial
@SudhirKumar-er5zs3 жыл бұрын
Wonderful course!
@samanthamccarthy3253 жыл бұрын
Gosh - this is really quality stuff. Thank you.
@HowToExcelBlog3 жыл бұрын
Thanks! Those that spend the hour to watch will hopefully save hundreds of hours with the learnings!
@Deedington2 жыл бұрын
Thank you Sir!
@NoelRojas3 жыл бұрын
Good stuff. Thank you.
@irfanshahrom67754 жыл бұрын
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?
@rasoulkhoshravan59124 жыл бұрын
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.
@HowToExcelBlog4 жыл бұрын
I use Microsoft 365, so it's airways the most up to date version.
@AdeKaryadi3 жыл бұрын
nice video, thanks
@hansfeldner66383 жыл бұрын
Very helpful video.
@lazyman28113 жыл бұрын
clap!clap1clap! a very powerful presentation from an old salt
@Luciano_mp5 жыл бұрын
Valuable tips, thanks for the spreadsheet provided. Thank you John.
@HowToExcelBlog5 жыл бұрын
No problem Luciano!
@nafryk4 жыл бұрын
where did you find its exercise sheet?
@HowToExcelBlog4 жыл бұрын
www.howtoexcel.org/downloads/
@mughalsatitsbest4 жыл бұрын
Great tips sir, continue the great contributions in the future.
@HowToExcelBlog4 жыл бұрын
Not to worry, that is the plan 😉
@darrylmorgan4 жыл бұрын
Some Really Helpful Tips And Tricks Happy Days!Thank You John :):):)
@aiTechSolution4 жыл бұрын
Unique effort... Thanks a lot
@HowToExcelBlog4 жыл бұрын
UNIQUE()
@allabout11355 жыл бұрын
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?
@HowToExcelBlog5 жыл бұрын
Eventually, but hard to say when.
@rithysokshare48203 жыл бұрын
Hello teacher, Great video
@HowToExcelBlog3 жыл бұрын
You're welcome 🙂
@computertutorialsandmore4893 жыл бұрын
Really nice tips. Thanks
@lamranajalloh32104 жыл бұрын
fabulous thanks
@HowToExcelBlog4 жыл бұрын
You're welcome Lamrana!
@lfqme5 жыл бұрын
Thanks John for this useful compilation of knowledge 👍
@HowToExcelBlog5 жыл бұрын
No problem Luis!
@louisd957144 жыл бұрын
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.
@HowToExcelBlog4 жыл бұрын
Yes, that's true. Most people don't know Excel online is free too!
@annajurakulov37473 жыл бұрын
excellent material! thank you
@casachintanvashi54644 жыл бұрын
Super...Happy to see such good value addition knowledge on excel
@HowToExcelBlog4 жыл бұрын
Thanks Sachin!
@dexterreid9264 жыл бұрын
Thank you.
@HowToExcelBlog4 жыл бұрын
Welcome!
@inuinu80044 жыл бұрын
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!
@HowToExcelBlog4 жыл бұрын
Adding a single file, yes! But combining data from hundreds of files, it will be the easiest way.
@inuinu80044 жыл бұрын
@@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.
@ndelpurg4 жыл бұрын
Thank u excellent glad i found u
@HowToExcelBlog4 жыл бұрын
I'm glad you found me too 🙂
@jingwu50754 жыл бұрын
Damn get data form folder so awesome. Thank you!
@HowToExcelBlog4 жыл бұрын
Power query in general is so awesome 💪 ⚡
@dougmphilly4 жыл бұрын
@@HowToExcelBlog i load 200k rows by 10 colums into excel, forever. load same data set into PQ, boom.
@lettynavazquez57644 жыл бұрын
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. :)
@HowToExcelBlog4 жыл бұрын
You can create a column that calculates the shift, then filter on that.
@yilia11794 жыл бұрын
Really helpful!
@HowToExcelBlog4 жыл бұрын
Great to hear Yilia!
@amitbhandari42993 жыл бұрын
Very nice video
@farbodmaher45893 жыл бұрын
Awesome! Thanks.
@gihantissera81404 жыл бұрын
Great tips on excell
@HowToExcelBlog4 жыл бұрын
Thanks Gihan!
@yolindabaquero80924 жыл бұрын
Awesome! Thank you for putting this together! 👍
@HowToExcelBlog4 жыл бұрын
You're welcome Yolinda!
@vbatipsandtricks52374 жыл бұрын
Very nice!
@HowToExcelBlog4 жыл бұрын
Thanks!
@sudhirlimbachia29774 жыл бұрын
Valuable tips, thank you.
@HowToExcelBlog4 жыл бұрын
👍🙂
@clduan68454 жыл бұрын
Great, thank you!
@HowToExcelBlog4 жыл бұрын
No problem!
@Robert-xh3tu3 жыл бұрын
for text to columns, if you don't close the current workbook and open a new spreadsheet - and try to paste something that has the delimiter, then it will automatically apply text to columns. Only way to stop this is to close all excel projects and re-open them and apply text to columns to your second project. This always caused me issues in dealing with different daily banking reports.
@HowToExcelBlog3 жыл бұрын
Yes, this is one of those "smart" features 😒 I usually rerun the split with an obscure character to stop it.
@HowToExcelBlog3 жыл бұрын
Btw, you're probably better off building some power queries for transforming your daily report data.
@Robert-xh3tu3 жыл бұрын
@@HowToExcelBlog Oh-no, I've only just started learning macros. I learnt T2C out of necessity, learnt that before Vlookup, and still haven't used index match. do I need any downloads or extra tools to start power queries or is it just another built-in maze to lose myself in?
@HowToExcelBlog3 жыл бұрын
It's built in from Excel 2016 onward. Go to data tab and if you have a section called get & transform then you have it. Otherwise there is an add-in for 2010 and 2010 (but seriously its time to upgrade to Excel for Microsoft 365 at that point).
@rachidt76364 жыл бұрын
Thank you very much it was very helpful , please to share that Datasheet
@ekkiwurm78744 жыл бұрын
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!!
@HowToExcelBlog4 жыл бұрын
It's an effect from the screen recorder I use called Camtasia.
@HowToExcelBlog4 жыл бұрын
Glad you enjoyed the video!
@ekkiwurm78744 жыл бұрын
@@HowToExcelBlog Thank you very much. You are a real gent.
@stasynech4 жыл бұрын
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?
@HowToExcelBlog4 жыл бұрын
Maybe you want to turn your data into a table then use a slicer to filter?
@stasynech4 жыл бұрын
@@HowToExcelBlog thanks, yes if it is smart table it is a way, but I want to use flat table, anyway thanks!!!!
@SALMANANSARI-ly2vi2 жыл бұрын
Thanks sir
@dr.s.p.3 жыл бұрын
Excel lent ! Thank you; most helpful.
@OnosBrisibi3 жыл бұрын
You are the best
@HowToExcelBlog3 жыл бұрын
Thanks 😊
@djtoonice4 жыл бұрын
Awesome👌
@HowToExcelBlog4 жыл бұрын
Thanks!
@sajedezare52193 жыл бұрын
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.
@davidlam59883 жыл бұрын
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
@monitoringandevaluationtitbits4 жыл бұрын
Nice tutorial. Learned something. Kindly share the workbook. The link provided is broken.
@ICTNuggets4 жыл бұрын
Great video
@HowToExcelBlog4 жыл бұрын
Thanks!
@rugwizainnocent18814 жыл бұрын
so fantastic
@HowToExcelBlog4 жыл бұрын
Thanks!
@rajeevgosavi45143 жыл бұрын
SIR, DOWNLOAD LINK GIVEN BY YOU FOR PRACTICE WORKSHEET TO FOLLOW ALONG IS NOT OPENING AND SHOWING ERROR 404. PLEASE SUGGEST
@rokpodlogar60624 жыл бұрын
great tips
@HowToExcelBlog4 жыл бұрын
Thanks Rok!
@Ty33Budd4 жыл бұрын
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?
@HowToExcelBlog4 жыл бұрын
Are the files the same structure across each subfolder?
@PattyBall244 жыл бұрын
can't seem to download workbook from link. Looks like a personal sharepoint account?
@fathirinchi78224 жыл бұрын
thank you
@HowToExcelBlog4 жыл бұрын
You're welcome Fathi 🙂
@josephcosgroveF4fl4 жыл бұрын
I've subscribed and downloaded all the other workbooks except for this one ?
@HowToExcelBlog4 жыл бұрын
Not sure what the question is 🤔
@ddww25742 ай бұрын
Does this require excel 365 or something?, I don't have any stock option on excel 2021 (rich data type)
@winsbury4 жыл бұрын
Good video
@HowToExcelBlog4 жыл бұрын
Thanks Mark!
@soniakeesee2224 жыл бұрын
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?