HomeХобби и стильRelated VideosMore From: Sele Training

Top 15 Advanced Excel 2016 Tips and Tricks

7763 ratings | 583069 views
This is a continuation of my Top 25 Excel 2016 Tips & Tricks. It includes more advanced features and some additional tricks to help you become more productive using Excel and Office 365. Check out the original Excel 2016 video at https://youtu.be/EdnAeqxqdzM. Another 15 Excel 2016 Tips 7 Tricks: https://youtu.be/L9SKTj2gevA Contents: 1. Advanced Transpose 0:39 2. Calendar Picker 1:38 3. Slicers 3:05 4. Scenario Manager 4:22 5. CONVERT Function 7:09 6. Convert Currency Live 8:03 7. Hide Cells 10:14 8. Remove Blanks 10:48 9. People Graph 11:17 10. Track Changes 12:40 11. Advanced Filter 15:10 12. Analysis Tools 17:52 13. NETWORKDAYS Function 18:51 14. Embedding 19:38 15. Advanced Select 20:50
Html code for embedding videos on your blog
Text Comments (272)
Dragon57312 (1 day ago)
At 2:18, I cant find the Microsoft date and time picker on my list
Acheiropoietos (1 day ago)
You had me at Live Transpose <3
Syl P. (1 day ago)
awesome!!! well done, concrete, fast and easy to understand.
Lana Lin (2 days ago)
Thank you
Sviat Illiashov (4 days ago)
Cool tips, some are really awesome. Thanks.
crtlyksds (5 days ago)
very informative
Yaseen Mirza (5 days ago)
Excellent way of presentation
aamir saeed (6 days ago)
this video has a level
Tony Blackwell (7 days ago)
I would love to know if it's possible to name sheets by date rapidly? Each month I have to create a staffing work book with a staffing sheet for each day of the month to list employees on duty. Each sheet is named by the month, day and year and I am now actually entering each of these sheet names manually. It would be fantastic if I could do this quicker. Thank you
Sele Training (7 days ago)
There is no easy method to rename multiple sheets. The only way I know of would be with VBA code or an add-in like Kutools. I'd probably approach this from scratch by changing the spreadsheet to track data in a table rather than multiple sheets. You could use filtering or a pivot table to get the data you want for each day.
Ajay Sinha (7 days ago)
Excellent video, very clear presentation. really useful tools. Many thanks
David Fenner (8 days ago)
Thought I knew all about Excel, this just proved me wrong, really worth watching.
Kashif Zia (9 days ago)
Great presentation sir
Riyaz Saiyed (9 days ago)
i am copying through arrayformula to new sheet but sorting the source data changes destination lineup. How can i stop this from happening?
Ali Ali (11 days ago)
Nice sir thankfully
nagaraju nagaraju (12 days ago)
Super I like its, it's usefull
salem chemlal (13 days ago)
verry good video, verry instructional, thanks a lot
AmazingLife (15 days ago)
Learn MS Excel Full Formulas & Functions with mobile App :http://bit.ly/2AuWffB 500 MS Excel Quiz Full Formulas & Functions is a full and free app course for MS excel 2016. This application is suitable both for beginners, advanced, as well as for schools and businesses . This app will guide you through the new features of MS excel2016 to help you easily learn Ms excel & excel function and Practice any time.
Muhammad Kamran (17 days ago)
Dear Sir, Could you help me please, in my computer in the Excel i could not find in "More Controls" the option of (Microsoft Date and Time Picker Control 6.0) Please advice how to get this (Microsoft Date and Time Picker). Thanking you in Advance
Sele Training (17 days ago)
The Date and Time picker does not work for 64bit versions of Excel. Here is some info on how to download if you have the 32bit and it is missing from the menus: https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/#Register-date-picker
tabasco555 (17 days ago)
Very impressive list of truly advanced features. I have been using Excel for over 10 years. I only knew two of those tricks! Thanks for the very valuable and organized information.
Ata Phri (17 days ago)
its really very interesting and learning video .well done Boss
Rudra Murthy M (18 days ago)
Nice Tutorial .
Sergiu Damian (19 days ago)
Hello, i don't have activated Track changes how to ?
Sergiu Damian (15 days ago)
Thanks
Sele Training (18 days ago)
Track changes should show up under the review tab in the changes section. It is available for several older versions of Excel so you should have the option on the menu. You can also go to the Quick Access Toolbar in the upper left and click the down arrow. Choose More Commands. In the pull down menu select All Commands and look for Track Changes in the list. Add it to the Quick Access Toolbar and you can get at it that way.
Annie dyb (19 days ago)
This is very informative and something that I can use at work. Thank you for sharing! Great work!!
Bandile Benjamin Booi (20 days ago)
I have not seen a single video on this or information on the internet but the way American excel formulas are set up is different from the way UK setting are, for example use of comma instead semicolons how do I convert my excel settings from American to UK so that I use semi-colons instead of commas. I hope someone can help!
Sele Training (18 days ago)
You need to set a second language in the control panel and then use the language bar to switch between them in Excel.  See this article: https://support.office.com/en-us/article/switch-between-languages-using-the-language-bar-1c2242c0-fe15-4bc3-99bc-535de6f4f258
Rafique Ahmed (20 days ago)
Sir I have your subcriber and watching your learning video one problem is discussed you that I have some value like =4+5+7 cell total appear this 16 but I want this value in other cell in text var like 4+5+7 how can i do that with formula pl. tell me
Sele Training (19 days ago)
If you are asking how to make 4+5+7 a text value rather than a formula you just need to enter '4+5+7 in the cell. A single quote forces any numbers or formulas to be a text value. Also, the results of the formula are always in the cell you enter the formula. You can reference the cell from another location. So entering =A1 in cell B2 will place the value from A1 in B2.
Rolando Doroja (20 days ago)
Thank you very much. I learned a lot from your tutorial video! God bless you.
Kal wel (21 days ago)
Such a calm and relaxed style of talking, makes learning very theraputic 🙌👌
Asoo Somaz (21 days ago)
Very helpful
Raoul (22 days ago)
Hittar inte kalenderväjare
יוסי בוקר (23 days ago)
Great video
Bill Antoskiewicz (24 days ago)
I don't have the date picker showing up on my developer tool. Any suggestions? Thanks.
Al Perkins (24 days ago)
Google 'datepicker not visible' and there is a lot of advice given. You need to add it (register it in Windows terms.)
JK 8 (24 days ago)
Excellent👍
Shaukat Samad (25 days ago)
Sir, thx for guiding us n it is very needful for us in modern business but sorry to say that how I can get measurement chart pls advise
Sele Training (20 days ago)
You need to have the correct version of Office 365 Excel. If you are on an older version some of these features are not available.
Bill Antoskiewicz (26 days ago)
Is there a trick to having some columns filter and other not? Seems like I am getting all of them with the filter drop down or none at all. Some of the columns don't lend itself to filtering. Thanks.
Sele Training (26 days ago)
Really simple. Just click the column heading (A, B, C) for the columns you want filtered first then choose filter from the menu. It will only set the filter on the highlighted columns.
John Raymond (26 days ago)
These are great tutorials. I've looked at all 3 a few times, and recommended them to others, unfortunately now i will not be doing that anymore. The adverts and interruptions by Youtube are insufferable. Don't even get the option to skip them. They FORCE you to sit and watch. Well guess what Youtube.....most of us DON'T watch OR listen! Youtube was great at first...we now need an alternative.
Sele Training (26 days ago)
I actually limit the ads during the video so it's not such a big disruption but I do include the ad at the start since that is what generates revenue for YouTube creators. Generally they only force you to watch ads that are 15 seconds and you can skip anything longer. So far they have only put one ad at that start. I use YouTube TV and last night I had 7 ads during one commercial break of a show and couldn't skip any. At least it's not like that for YouTube videos here.
Josyam Srinivas (26 days ago)
very nice
Bill Antoskiewicz (27 days ago)
I manage a small sales team. Is there a way to great an intake form that outlines the details of a pursuit and then have a master tracking sheet that tracks all of our opportunities in one view?
Sele Training (27 days ago)
You can certainly do many things with Excel but if you have Office 365 you may want to check out Forms or the more robust Dynamics CRM. A description of Forms is here: https://techcommunity.microsoft.com/t5/Microsoft-Forms-Blog/Microsoft-Forms-is-Enterprise-Ready-now/ba-p/186748. Dynamics CRM is built to track opportunities along with other customer relationship management and sounds more like what you would want from a sales perspective. For a small team the licensing is very reasonable and worth the expense rather than trying to create everything in Excel.
Baked Utah (30 days ago)
8:03 😳😳😭😭 [Falls off his chair and starts sobbing on the floor at the hours of his life wasted until now because he didn’t know this!]
Sele Training (30 days ago)
That is a good one!
Liam Selby (30 days ago)
Thanks, this is great. Slicer tool my change my excel life.
Jammale Lamarti (1 month ago)
Thank you for sharing your tips with us ! Appreciated
Chase Miller (1 month ago)
For #7, couldn't you also just set the text color to white?
Sele Training (1 month ago)
That is an option too.
Randy Mark (1 month ago)
This is absolutely helpful. Thanks a lot
Steven Fredrik (1 month ago)
Jeepers Creepers! This is a gold mind of Excel Tips and Tricks! I teach Excel at a local College, and this has opened a whole new world of instruction. Well worth exploring and adding to my teaching repertoire. Thank you!
Bob L (1 month ago)
I have enjoyed your Excel videos Tips and Tricks. I have watched the top 25 and the 15 advanced tips. Great ones that has made me consider changes in my spreadsheet. So far I have not been able to figure out how to take data of a group of numbers, essentially a balance and all the subtotals of assets within the balance and create a pie chart. I am not sure if it is the way I have entered the data or just because I am not understanding the process. Every time I try it shows up as only one of the items and it summing the entire column where the data is row data beginning with date and the balances for the day across the row. Just not understanding.
Fatima ELAISSAOUI (1 month ago)
I really Appreciate this video, so fruitful. please update us with the newest ones.thxs
Sele Training (1 month ago)
I will have a 2019 tips and tricks video in a while.
John Borg (1 month ago)
Thanks for these tips :)
corado ruel (1 month ago)
Helps me alot. Thank you so very much..God bless you
Michael Sargent (15 days ago)
Cheers for the video content! Sorry for the intrusion, I am interested in your initial thoughts. Have you considered - Weydaniel Organized Dominator (just google it)? It is a smashing one off guide for getting 7000 plus project management and business templates without the hard work. Ive heard some unbelievable things about it and my cousin at last got astronomical success with it.
Charlie Murphy (1 month ago)
This guy is a life and money saver.
Happy Halima (1 month ago)
Wow bravo Mr Sele
Sele Training (1 month ago)
Thank you. Hope it helps you become more productive with Excel.
Vishal Mohan (1 month ago)
Damn Awesome. Thank you very much for the information. Keep it up. Thanks again
Cecil Osei (1 month ago)
Brilliant!
Sele Training (1 month ago)
Thanks!
C Toumi (1 month ago)
What an excellent presentation, thank you very much :)
Jaime Ara (1 month ago)
Great video! Thanks.
BuMi Kim (1 month ago)
How can i express my thankful mind ? Thank you so much. I got a big help from you.
Fernando Piccirilli (1 month ago)
it changed my life!
Katherine Tan (1 month ago)
why my excel has no date and time picker control ==
Sele Training (1 month ago)
The date and time picker is only available in the Office 365 version of Excel. There is an addon posted in the previous comments if you need.
web_fly Thalles Torchi (1 month ago)
Nice niceeeee Chanel
8 pool (1 month ago)
Great video, just one extra little trick I like to employ when using the Transpose array formula- as you mentioned- you need to select the size of the area to fill. I copy the original data, paste special using values and transpose. This gives me the area to select, so I highlight all of my pasted values and then type the formula as you outlined. Saves you a lot of trouble when you’ve got a lot of data to transpose.
Sele Training (1 month ago)
Great tip. Thanks!
Imdad Hussain (1 month ago)
Good
Cameron Gray (1 month ago)
Wow this is very advanced omg I am not ready for this
Nirav Sheth (1 month ago)
useful tricks not known generally
Nora Beddes (1 month ago)
Very helpful video! Thank you!
THANKS FOR SUBSCRIBE (1 month ago)
Very helpful video for work at office..!! Great Job!!
cuong vuvan (2 months ago)
Thanks a bunch!
Flaminghazard HD (2 months ago)
thx a lot Sele Training! It helped me a lot!
LMC Presentations (2 months ago)
This is very helpful in reviewing Excel. Thank you so much!
Sanghang LAWATI (2 months ago)
Really good and well presented. Good Luck for more.
Alfred Cruz (2 months ago)
thanks its very helpful
majid shah majid shah (2 months ago)
I strongly appreciate this video thanks for launch.
R Garlin (2 months ago)
Great stuff, thanks a lot!
Supriya Vadrevu (2 months ago)
it was useful for me
Sai Vineeth (2 months ago)
how to bring the axis title to the end of the graph from the middle with the axis line when it is back to back histogram/ negative histogram ??
Harish Kumar Turlapaty (2 months ago)
Hi! Great video! If I have a flow chart with values in each step of the process, in the form of a PDF file. Is it possible to get each process step in individual steps within an Excel file?
Sele Training (2 months ago)
The best method for flow charts in Excel is to click on the Insert Tab and under the Illustrations group select SmartArt. There are some flow chart options that will allow you to create each step.
Jawahar Khan (2 months ago)
Excellent Content, Presentation and Voice
Lassy (2 months ago)
Excellent video without a lot of Mumbo jumbo 👍
PKC Region #1 (2 months ago)
Thorough and clearly explained. Thanks, Sir!
Dinesh Kumar Takyar (2 months ago)
Excellent!
Cakeep
Dr Phot (2 months ago)
Best in the biz baby... Thanks a milly!
Linda Anne Barnes (2 months ago)
looking for help creating excell sheet I would like to create daily diary heading 2018 followed by month day and date and space for worker names for roster day on 3 workers for 1 on 2off Saturday I would to print on same page i can cut and paste into a4 book as a heading of blanket line page. can you help?
Sele Training (2 months ago)
You'll probably find the steps outlined in my latest Excel training video helpful. Take a look at https://youtu.be/WxUQLHJn77Q.
Ahmed Cia (2 months ago)
Excellent you have to see it looking forward to watch another videos
Michigan GarageTek (2 months ago)
Thank you, really like the way you present the tools, easy to follow and understand.
Sele Training (2 months ago)
Thanks. You may want to check out the original Excel tips and tricks video as well: https://youtu.be/EdnAeqxqdzM
Silke (2 months ago)
Hi, great video! With the convert function, I do not get a drop down list when I need to pick from_unit or to_unit. Anybody has any idea how to activate this? Can't find it on google. I also had a second question, for the Input Data from an URL. I only find 'input data from a SQL server ODBC Data Source' and it asks me credentials which I do not have. Anybody any idea how to fix this? My version is Excel 2015 for Mac. Thanks!
Silke (2 months ago)
I was afraid of that, still good to know. Thank you very much for your fast answer! Very helpful!
Sele Training (2 months ago)
Unfortunately the Mac version is missing some features that are available in the Windows version. Office 2019 is due out soon and may have that added. The ODBC option is to connect to a SQL database so you won't want to use it for the Web connection.
Justin Solberg (2 months ago)
Very practical for everyday and business use. Thanks!
Anamae Pahud (3 months ago)
Great! I
Arthas Legend (3 months ago)
Great tips :)
Travis (3 months ago)
this video is great!! learned so much
rakeshstha7 (3 months ago)
Thanks a lot
Diana (3 months ago)
Sir, I love you!
Sele Training (3 months ago)
You are too kind!
Siddhartha Shabd (3 months ago)
Awesome.........its like miles journey!
Robson Hernandez (3 months ago)
Amazing...
Nicole Kausler (3 months ago)
This video is very helpful. Thank you!
PALDA (3 months ago)
Tks
mehmet kaya (3 months ago)
Thank you, again! Hope to have more select advanced excel tops&tricks very soon.
Sitharama Raju G (3 months ago)
Good video. If you want to master Excel check out this link: https://www.fiverr.com/s2/b4a856fce6
mule dada (3 months ago)
Pls add more videos so helpful
Sele Training (3 months ago)
I will be loading an Excel Introduction video very soon. Working on it now.
Phanindra Kumar Vavilala (3 months ago)
Salute for this video...very powerful information.
tel nett (3 months ago)
Hi, I had a doubt .hope you can help. I have to lookup the employee id from another sheet to my current sheet but there are repetition of employee name . That is sarah has done a sale on jan 10th and another sale on jan 20th....i want her employee id...to appear on both rows but when i use the vlook up ...it returns the correct value for tge sale on jan 10th but not on jan 20th...these r not duplicate transactions...please help
lynn (3 months ago)
thanks a lot
Błażej Bziukiewicz (3 months ago)
hello u use PQ in 9:45 minutes your video? i supose
Shashikanth Bhadri (3 months ago)
Awesome,!!!!!!!!!!!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.