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

Top 15 Advanced Excel 2016 Tips and Tricks

10459 ratings | 808731 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 (361)
Darwish Mammi (9 hours ago)
Very useful .. thx for sharing ..
Erfan Tavoosi (23 hours ago)
That was truly a comprehensive tutorial. Nice job !
Anandraj Selvaraj (1 day ago)
Very useful tips, Thanks for sharing.
Jim Johnston (2 days ago)
lol im on the weird side of youtube again
Wai Yar Aung (3 days ago)
Thanks alot
Deba Howladar (4 days ago)
It's amazing and helpful
محمود جاسم (4 days ago)
new informnation
محمود جاسم (4 days ago)
short
محمود جاسم (4 days ago)
difficult
Chandran kartar singh (5 days ago)
There are morons who can give thumbs down for a such a wonderful and informative subject calmly presented. Thanks
Sele Training (4 days ago)
It is funny but about 2% of people give thumbs down no matter how good the video is.
trut1960 (8 days ago)
Very good explained Thumps up
Sir XtC (8 days ago)
hello. i work in a warehouse, and sometimes i need to do some inventory research, to see how many skids of one specific item we have. we have around 8000 different items, so the quantities its pretty big, i have to go in a list down checking "sort by name" as, each skid has its own line for excel...but it takes too long. i just want a way to like search amount, for example, id like to find items that have a quantity of 12 skids +. so basically 12 lines of excel. its hard to explain. any ideas?
Sele Training (8 days ago)
You probably want to use Conditional Formatting to highlight all the ones with quantity > 12. Take a look at https://www.youtube.com/watch?v=EdnAeqxqdzM&t=1091s. Alternatively you could use SUMIFS if you need to sum values based on certain criteria.
Alina Jolley (8 days ago)
Very helpful, thanks a lot!
arun sekhar (8 days ago)
Great video. Crisp n clear. 💐
Hoang Thanh (9 days ago)
Great work, hope that you could make this series for Power BI.
Yevgeniy Romaniuk (12 days ago)
Great video. Thanks a lot.
Wilson RJ (12 days ago)
awesome and easy to understand.
Jyoti Lama (13 days ago)
Great Tricks!
Mokaya Humphrey (14 days ago)
Very insightful!
R. A. (15 days ago)
Date and time picker is only available in 32 bit MS...
Sele Training (15 days ago)
That is correct. Hopefully Microsoft will add one to the 64bit version. There are some alternatives such as this: http://www.rondebruin.nl/win/addins/datepicker.htm
alem allel (16 days ago)
good video thank you very much
Atif Shabbir (18 days ago)
Thanks, very useful
emad hussein (18 days ago)
Thanks and i hope to share us every news about the advanced options
Lien Pn (18 days ago)
I can't find track changes under Review. Please help!
Sele Training (18 days ago)
You can add it by going to File-Options-Customize Ribbon and find Track Changes in the list of all commands. Add it to the right column and hit OK to save the setting.
Frank Moyer (20 days ago)
how do I shift the row numbers(1,2,3,4, etc) down to lineup with the first line of data or names and still have the tabs at top showup
Sele Training (20 days ago)
Not sure exactly what you are describing but you can highlight a block of cells and click on the edge of the block to move it to a new location. It retains all the data and formulas. If you assigned a filter to the top row it will move it too so you'll have to disable the filter and recreate it again.
Hermann Valsson (21 days ago)
Jurt né of the best Tips and Tricks thank you.
Imre Toth (21 days ago)
Great presentation!! Thanks. :)
Jayesh Tank (22 days ago)
Very well presented easy to follow and understand the information.
RAJARAM K (22 days ago)
These are very very useful. Thank you verymuch
Vanna Cheong (22 days ago)
it is amazing that theres so much more to excel. i am now so eager to get on board and apply what i have just learnt
+Vanna Cheong Don't be discouraged. You can get better. :-) Just give yourself a few months of consistent learning and practice. You'll surprise yourself. I feel inadequate myself, but I'm learning advanced Excel and and will get good at it. :-) That's why I'm on this channel. Two other channels I like are Leila Gharani and Excel Campus - Jon. Of course, there are many other good channels there, but between these three you can learn enough to be confident in Excel. Best wishes to you.
Vanna Cheong (20 days ago)
+Bianca A. - There's art to data science i feel awfully insdequate in excel. :)
@Vanna Cheong I agree. Excel is like a playground for data geeks once you start using more than its basic features. :-)
Vijay Gourimath (23 days ago)
It is very useful for my works, Thanks a lot
Charles Norris (23 days ago)
Great Video!!! Dude, You are the Man!!! Question: Is there a way to insert a drop down calendar in a cell, like you can in a form?
Sele Training (22 days ago)
Not that I am aware of but as you can see in the example for this video you can use the form for a calendar but have the results of your selection end up in a cell. So it essentially accomplishes the same thing.
Ali Adnan (24 days ago)
!!!!!!!!!!!!! Simply FANTASTIC !!!!!!!!!!!!!!!
Mahmud Zaman khan (26 days ago)
GREAT ONE.
Mallikarjun Kambar (29 days ago)
All tips & tricks very usefull, but in 13. NETWORKDAYS Function if two different Holidays not working. So please comment on this.
Sele Training (26 days ago)
Make sure to highlight the range of cells for the third parameter. That will exclude the entire range of holidays.
impandey (29 days ago)
Quite useful video. Thanks for the video.
Perseverance Maraire (30 days ago)
Thank you for a very helpful video.
Marlene Russ (1 month ago)
Very impressive, easy to understand and blows me away to know there is so much more you can do with excel. Great work!
陈希章 (1 month ago)
very good sharing
Awaking Beauty (1 month ago)
Sooooooo helpful
Murthala Shafiul (1 month ago)
so nice
sandeep kumar (1 month ago)
thanks a lot of you sir
Nicolas Rehder (1 month ago)
You sir, sound like Carl Sagan
Sele Training (1 month ago)
And I am looking for billions and billions of views of my videos!
Mahesh Gorla (1 month ago)
good ones, described very well easy to follow them
Mahesh Gorla (1 month ago)
can you help me to use the compare and merge option in excel or links please
Nido Sergio (1 month ago)
Thank you for explain it's easy
OZAN BARKIN (1 month ago)
teşekkürler.
filipe coutinho (1 month ago)
Great video looking forward for more videos
BRIAN BANZON (1 month ago)
Amazing...
Michel Eradus (1 month ago)
Unfortunaly the DTPICKER is no longer avalible in Excel. I use 365 en before that 2010. En dtpicker was left out in an update. I can not find a way to get it back.... So is it back in Excel 2016 or did you put in your self?
Sele Training (1 month ago)
It depends on 64bit or 32bit too but is still in the latest version of Office 365 if you have the correct one.
Hamza Khan (1 month ago)
Most important video sir and your page
Mohammad Allibaih (1 month ago)
Many thanks sir for the great knowledge. Very informative. Keep doing good!!!!
Sai Chand (1 month ago)
Awesome very helpful. Thanks a lot for sharing..
pooja badholiya (1 month ago)
Excellent presentation....Thanks a Ton! :)
Terry Madeley (1 month ago)
I was surprised by how good this video was. Often, the tips are pretty obvious but these were very helpful. Many thanks!
Sele Training (1 month ago)
I made sure this was helpful tips. It is annoying when people repeat the common sense stuff just to make a video. I purposely choose topics that add value. Thanks
mohammed mustafa (1 month ago)
Wow it is amazing
Reign Relic (1 month ago)
When I click on paste special i get different page. What could I do to get the other paste options?
Sele Training (1 month ago)
Make sure you are highlighting a full block of data before you copy and then paste special. Sounds like you may not have the data set to copy first. The Paste Special menu should be the same whether you pick the option or the icon so I'm not sure how you could get a different menu.
Rory Neary (1 month ago)
All very interesting, but we need to be moving people away from excel and not towards it. You're just creating silos of functionality and there is no audit trail for the method in which an excel spreadsheet has been created. What is going to happen when the creators leave the business? I did like advanced filter though and the functionality was very well delivered.
Rory Neary (1 month ago)
That's a corporate answer I'm afraid. I don't think that I've ever seen any documentation for an excel file and if there was what would it even look like? Excel has so much functionality delivered in such a complex fashion that to document it would be nearly impossible, and the more whizziness you use the worse it gets. The idea that you use succession planning as a means of managing your Excel solutions strikes me as a little unlikely, although in fairness I have done it myself and it's very difficult. People should be moving to Sharepoint (as a minimum) for storing their data, Power Bi for visualising it, PowerApps for interacting with it and Flow for creating automation. This is where the future lies - not producing more Excel silos.
Sele Training (1 month ago)
In a corporate environment one problem is that too many people use Excel to replace functionality that really should be part of the core ERP system and it can cause data nightmare silos across the organization. From that perspective I agree but it still is one of the best tools that most people know how to use and will forever be cemented in people's daily use. You will still have problems with any data management solution when creators leave. There will always be some tool in use to manage the data and only a subset of people will know how to use it. That is why documentation and succession planning is so important.
Omar Zini (1 month ago)
I didn't know about the Track Changes tool. What a discovery!
thang nguyen (1 month ago)
woa, It's very very useful trick with excel I have never seen before :) Thanks for your sharing.
AnikiPhotography (1 month ago)
thank you very much sir...very helpful in my data sorting knowledge
Barry Fleming (1 month ago)
Thank you for your helpful video. I have a need to convert a column within files which can consist of well over 30,000 lines, where I need to convert a land measurement format "640--10--0" _(acres--roods--perches)_ into Hectares. Is there a method I can use where I do not have to separate each value into a separate column and where the conversion takes place the moment that the 'a--r--p' value is first entered?
Sele Training (1 month ago)
Several options to make this work. You can create a complex formula all on one line. For example use =CONVERT(MID(A1,1,3),"us_acre","ha") to convert the first 3 letters from acres to hectares. Just add multiple steps into that formula to get what you want. The other option is to break it into multiple columns but hide them.
Jessica Tamez (2 months ago)
Learned some new tricks! Thank you.
Rudi Klein (2 months ago)
Even though I have been using Excel for a *long* time and on an advanced level, you still managed to provide some neat tricks that I wish I had known years ago. Great job.
Mike Stoy (2 months ago)
Another great video. Thank you.
Alexander Schmidt (2 months ago)
Great video with very useful tools! Have you considered to include Solver in your next video?
Sele Training (2 months ago)
Solver is a good one. I'll have to consider that for a future video.
KARTHICKRAJA M (2 months ago)
In 10m 25sec Hide Cell Awesome Trick
But I don't understand what circumstances can we use that?
Joe Torres (2 months ago)
Wow , my respects!! In the first three min you explained more than I can handle. THANKS YOUR AWESOME
Pallavi S (2 months ago)
1how to consolidate 50 excel from 1 folder
Pallavi S (1 month ago)
+Sele Training how to use VBA code . Can we convert all files to CSV with 1 command
Sele Training (2 months ago)
There is no simple built-in method that I have seen. You have to use VBA code or a third party add-in to pull data from multiple files into one if you don't want to copy/paste each sheet one at a time. Depending on what you are trying to do you could use the consolidate feature under the data tab in the data tools section. Consolidate is meant to pull data from multiple sheets but does allow you to select different files as well. You could create a spreadsheet that pulls data from 50 excel files.
Johnson Brown (2 months ago)
Excellent. 👍🏼
haedr10 (2 months ago)
thanks sir
K T (2 months ago)
Hands down most clever x-rates ad I've ever seen.
Sele Training (2 months ago)
Just providing good tips to web enable data access. No affiliation to my channel.
Til1976 (2 months ago)
thx
Podina Tutorials (2 months ago)
insane and in perfect Tutorial. thanks ! Would love to see more Tutorials form you, more in depth with examples, than the one you made. #PodinaTutorials
HeeJung No (2 months ago)
This is fantastic!  Thank you :-)
Don Mega (2 months ago)
maybe it's just me but the more i look at all these things you can do with excel the more i feel like just making the simplest possible inventory. in and out pretty much.
Dragon57312 (2 months ago)
At 2:18, I cant find the Microsoft date and time picker on my list
Sele Training (2 months 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. It is also needs to be the Office 365 version of Excel.
Acheiropoietos (2 months ago)
You had me at Live Transpose <3
Syl P. (2 months ago)
awesome!!! well done, concrete, fast and easy to understand.
Lana Lin (2 months ago)
Thank you
Sviat Illiashov (2 months ago)
Cool tips, some are really awesome. Thanks.
crtlyksds (2 months ago)
very informative
Yaseen Mirza (2 months ago)
Excellent way of presentation
aamir saeed (2 months ago)
this video has a level
Tony Blackwell (2 months 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 (2 months 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 (2 months ago)
Excellent video, very clear presentation. really useful tools. Many thanks
David Fenner (2 months ago)
Thought I knew all about Excel, this just proved me wrong, really worth watching.
Kashif Zia (2 months ago)
Great presentation sir
Riyaz Saiyed (2 months 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 (2 months ago)
Nice sir thankfully
nagaraju nagaraju (2 months ago)
Super I like its, it's usefull
salem chemlal (2 months ago)
verry good video, verry instructional, thanks a lot
AmazingLife (2 months 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 (2 months 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 (2 months 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 (2 months 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 (2 months ago)
its really very interesting and learning video .well done Boss
Rudra Murthy M (2 months ago)
Nice Tutorial .
Sergiu Damian (2 months ago)
Hello, i don't have activated Track changes how to ?
Sergiu Damian (2 months ago)
Thanks
Sele Training (2 months 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 (2 months ago)
This is very informative and something that I can use at work. Thank you for sharing! Great work!!
Bandile Benjamin Booi (2 months 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 (2 months 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 (2 months 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 (2 months 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 (3 months ago)
Thank you very much. I learned a lot from your tutorial video! God bless you.

Would you like to comment?

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