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

Top 15 Advanced Excel 2016 Tips and Tricks

16017 ratings | 1307148 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 (549)
M Mouded (1 day ago)
Thank you for the great information.
Abo Bobo (1 day ago)
great;helpful.Thank you
Celestine Dcruz (2 days ago)
Thank you so much, yi did so well am so exited to learn ..please keep the tutorial going on and helping the community ..thank you God Bless You
Vivek Govila (3 days ago)
One of the most useful and well explained videos I have ever seen
KE TOAN THUE A TO Z (5 days ago)
Great job! Tks so much!
office setup (6 days ago)
microsoft is a top level smart work official softwere...nice info from your videos i have also a website plz visit :http://bit.ly/2EuUUa7
wasim bachar (6 days ago)
Thank you for the effort, in convert Currency live, and when I insert the URL then get the currency rates from X-Rates, the shown rates are without the comma "," i.e. instead of 1 USD= 0,88 Euro I get 88, any advice?
Mohamed Ibrahim (6 days ago)
Great and very helpful work and hope to cover all Excel formulas
jackfordmac (6 days ago)
Hi guys, does anyone know if you use advance filter, does it work on the fly? If you changed data from A to D and were filtering on A would the new D disappear or do you need to rerun the filter? Thanks all
Tom Wehr (6 days ago)
The delete blank rows thing is a bit off. At the end you press "ctrl +" OR "ctrl -", not both consecutively. if you press ctrl + it will give you options to insert MORE blanks in addition to the existing ones. Just press "ctrl -" then select "shift rows up"
warner hill (7 days ago)
Great content, this video is so informative for me. I am using a excel tool to reduce my works https://www.technocomsolutions.com/excel-utilities.html This works well and very easy to use.
Injoker Mid (8 days ago)
Why do I keep thinking “slicer, no slicing!”
Simon Inniss (8 days ago)
Wow. Absolutely brilliant. Great pace and clear instructions, excellent video presentation, and genuinely useful stuff. Superb. Subscribed and shared
Noelf (9 days ago)
You have made a lot of bosses a whole lot happier!!...because you have taught their teams how to provide better presented and analysed spreadsheets! Kudos to you sir!!
Sele Training (8 days ago)
Thanks
himshuk2000 (9 days ago)
Superb and really time saving technic!!!
Lourdu Yalla (11 days ago)
These are very useful tips. Thanks
Kalpana Kasule (13 days ago)
Excellent video ! very good information and well explained !
MAJID SIDDIQUE (16 days ago)
Awesome
Itz Clovise (16 days ago)
I have some excise I don't know how to do it
AhmadShah Riham (18 days ago)
much useful video thank you
Pacco Tam (20 days ago)
Amazing and keep up the good work!
Adel Kaddour (20 days ago)
Also i have another problem i really don't know what version of MS Office 2016 you are using But i face an issue in the chapter of Currency live in starting (Get Data ) an so on till the end thank you
Sele Training (18 days ago)
I am using the Office 365 version of Excel.
Adel Kaddour (22 days ago)
Good day Sr. your explanation was amazing but i got a difficulty in the second Lesson ( Calendar Picker ) i realized that the Microsoft date and time picker in my More control is missing Kindly can you help me i'm using windows 7 and Microsoft Office 2016 thank you so much
Adel Kaddour (21 days ago)
@Sele Training Sr.i would like to share this Link with you , check it please https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/?fbclid=IwAR19YbZXeTU5gTl3ASDKAhm8H-KlLb16Fvs5inmCT_mZLNFGIneCmGTzBu4#Register-date-picker
Sele Training (21 days ago)
The date and time picker is only available with the Office 365 version of Excel and it has to be 32bit. For some reason they did not include it in other versions.
Hashim Khan (22 days ago)
Well done!
I A (24 days ago)
Great Is there a way a Concatenate function could keep the Font-Colour of linked cell? For Example I have Red-Colour Text in one Column and Green-Colour Text in other. When I Concatenate text in those two column, the resulting test loses colour of original respective cells.
Sele Training (23 days ago)
I don't know of a way to copy the color with a formula but you can copy the original cell and then right-click on the target cell and choose Paste Special. Select Format from the list and it will copy just the color to that new location.
Sizzler Sizzle (25 days ago)
Fantastic.. Thanks...
Azhar Islam (27 days ago)
excellent tips
Josefina Magallanes (28 days ago)
ok, nvm i was doing it wrong but it did tell me there was a "typo"
Josefina Magallanes (28 days ago)
i am trying this and it is adding literally the work "transpose" in each box
Mark Lyons (24 days ago)
you mean the "word" transpose? You are obviously doing it wrong.
Josefina Magallanes (28 days ago)
AMAZING !
Smart Georgian (29 days ago)
Thank you
Thoraxe555 (30 days ago)
7:30 let's convert teaspoons into cubic light years
Sele Training (28 days ago)
Sounds like an idea for an Enhanced Advanced Video
ita (1 month ago)
Thank you for your video sir, I feel so much more confident now! :)
Oedhel Setren (1 month ago)
Great video. Just proving you never know everything with Excel. A few of these tricks I already knew, but some of them just blew my mind. The Date picker was my favorite, just because I use dates a lot and most of the time it's about the day of the week rather than the date. So much easier to use a picker.
Gino Imbrenda (1 month ago)
Novice Can anyone tell me how to remove a line (line out) from a cell on excel 2016. The spread sheet was from excel 2004 which is working fine with 2016. Excel 2016 does not have a line out function (as 2004 does) that I can find. Can anyone help? When I delete (clear) the cell the line keeps reappearing. Thanks
Sele Training (1 month ago)
Highlight the area you want to remove the lines then go to the Home tab. Choose the down arrow next to the Clear button in the Editing group. Choose the formatting or All and that should get rid of the lines.
Forget Shaft (1 month ago)
Excellent video!! Had no idea excel could do all of this!! Thank you for this!! Please keep these videos coming!! 👌🏾👌🏾
Aditya L Sharma (1 month ago)
Love you man!
ajay gupta (1 month ago)
Awesome✌
Sagar AGRAWAL (1 month ago)
nice.. really amazing feature. I truly enjoy this video... Thank you so much
Nelson Ayop (1 month ago)
Thanks for all of your videos sir!Very well explained and easy to understand..
Darshan Kumar R (1 month ago)
Good work dude..
R.Venkitachalam Pillay (1 month ago)
Very useful shortcuts
Ramesh K (1 month ago)
Thanks. Very nice.
Joemanjamz (1 month ago)
4:13 . . . chooses same one
Matthews Gang (1 month ago)
Hi, great video. Quick question; do you have a video on how I can create a spreadsheet that extracts data from another spreadsheet once I have dropped the 2nd sheet into a folder. So for example; I download a large data set, save the spreadsheet in a folder, open my main spreadsheet and click execute. It then pulls through the specific data that I have set. Any ideas? Really appreciate any guidance.
joyson parmar (1 month ago)
Amazing Work! very helpful.
Maud Yin (1 month ago)
Best YouTube video ever!! Lol...
Vasanth Jery (1 month ago)
Can someone help.. How to make subscripts or superscript in excel graph? (for excel - 2016)
Vasanth Jery (1 month ago)
@Sele Training no Its not working.
Sele Training (1 month ago)
Click on the text block that you want to change in the chart. Go to the Home tab and click the icon next to the Font group to bring up the Font settings. Superscript and Subscript will be check box options in the Effects section.
Baskara Tutorial (1 month ago)
nice Tutorial, maybe you can check to my channel too, my video also about listbox excel. LISTBOX Untuk Cari Edit Simpan & Hapus Barang |Part 1 | Baskara Tutorial Excel Padarincang https://youtu.be/BMwYGUfj5GY
Kamal Kannan (1 month ago)
Hey man...thank you a ton for posting this video... It was awesome..
Muhammed Safvan (1 month ago)
You are really amazing sir... Keep going.... Very useful... Love ur videos😍😍😍
Bulusu Prashanth (1 month ago)
Really useful tips, thank you!
Sultan Syed Ibrahim (1 month ago)
Excellent. Truly advanced level. Well presented.
bla bla (2 months ago)
I've always said to myself over the last decade that Excel is too powerful for what's needed on the market. Without exaggeration I can say that even if Excel will not be updated 100 years from now it will still be as powerful as today for most users. Great clip. I'm gonna use some of these newly acquired tricks in my future work for sure
harish reddy (2 months ago)
how to change the Working days in Week in excel 2016
Sele Training (2 months ago)
Use the WORKDAY.INTL() function. In the 3rd argument of that formula it gives you the option to choose which days are weekends if you want something other than Saturday/Sunday.
Fakiris (2 months ago)
Amazing tips! Thank you!
Ney Z (2 months ago)
This was a great video. Great job. Thank you.
Lukas Ebert (2 months ago)
those changes are laaaaaaaif . yeah man!!! nice
Mostly benefit for professional
RASIN JALLOH (2 months ago)
Amazing
Sarah Hill (2 months ago)
Great video - learnt a lot here that I definitely see myself applying to my workday ! I came here to see if there is any way to link tables together so that if I entered another row (not by inseting a row, just by typing into the next row down to automatically expand the table, or tabbing through to auto-add a line) that it moves the cells below down and retains the structure of a spreadsheet. I know that it is possible as I have managed to do it before - but I created the document a couple of years ago and cannot for the life of me find how I did it!! Any help gratefully received!!
Sarah Hill (2 months ago)
Sele Training Thanks for replying. I can see why my comment might not have been incredibly clear! So what I am trying to do is have a series of tables, one below another, but when I add a row to the first, the tables below move down. I have managed to do this in a spreadsheet I created but I cannot work out how to replicate it. As mentioned, it is automatically moving the rest of the tables down not by me inserting an entire row. Only the cells move down (the tables are all the same number of columns wide). Maybe it was a complete fluke, but I’d love to replicate it!!
Sele Training (2 months ago)
Not sure what you mean by "retain the structure of the spreadsheet" but if you apply formatting to the entire column then that formatting will apply to new rows as you enter them. One other item to check is the Autocorrect options. Go to File-Options-Proofing and click on Autocorrect Options. Go to Autoformat As You Type and make sure the check box is on for Include new rows and columns in table. That will copy formatting to new rows as you enter them.
arif ahmed (2 months ago)
really nice !!
Yannickos Es Grande (2 months ago)
Simply the best, I was just wondering do you have some advanced pivot table video's?
Sele Training (2 months ago)
The only Pivot Table video is the 30 second introduction: https://youtu.be/05vXappumt8
jjthesavage (2 months ago)
You sound a bit like Carl Sagan. You should do a rendition of "pale blue dot."
Chow Brian (2 months ago)
11:10 Ctrl + "+" + "-" doesn't work on my excel 2016 version, I tried to press the button simultaneously or one by one, but I only heard an error bling sound, anyone knows the solution?
Sele Training (2 months ago)
Hold the Ctrl key and then press + then -. It is the same as if you right click and choose delete.
Sitaram TR (2 months ago)
Awesome tips .. thank u very much for the video 😃👍
Simon Marshall (2 months ago)
Mind blown!
Phoenixspin (2 months ago)
This is sorcery!!
Shakir Ali (2 months ago)
Great work God bless you
Dasu Ram Ale (2 months ago)
nice video sir
Heisnam Umakanta singh (2 months ago)
thank u man appriciate it
R D (2 months ago)
I want to create 1cell with an outcome value generated off too seperate cells with and function. That is fine but I need to do this hundreds of times for that cell with different outcomes..is that possible?
Sele Training (2 months ago)
Many methods depending on what you are trying to accomplish. If you have a function that uses two different cells data then updating those cells one at a time will give you a different result in the target cell. I assume you want a "history" which can be done by copying the formula to several other cells so that you get a list of results. If you are trying to run a data analysis to reach a specific answer that tries many different variations then you are best using that type of tool instead. Excel has regression analysis and scenario manager to handle that complex problem but it depends on what you are trying to do which is not clear from your question.
kabilan n (2 months ago)
Great job of learning to excel user
Supernova (3 months ago)
Pretty amazing! Thanks!
ilyas dziri (3 months ago)
Thank you a lot for this video.
Sath Sah (3 months ago)
oh thank u so much Sir. Awesome
Anand Masali (3 months ago)
Hello sir how to amount in words rupees in Excel sir not use any another application Direct Excel formulas please say sir
Sele Training (3 months ago)
In the video example when converting US Dollars to Indian Rupees you would change the formula to =B4*Sheet7!B21. If you want to convert Rupees to other amounts then connect to the table on x-rates.com using "https://www.x-rates.com/table/?from=INR&amount=1" in the data connection.
Sachin Negi (3 months ago)
Really Great Video Sir. You are simply amazing.
Salim Saba (3 months ago)
You are simply amazing. 🌟 🌟 🌟 🌟 🌟 Thank you so much.
JOE peeer (3 months ago)
awesome
sajeer vp (3 months ago)
Very informative and good lecturing
Md. Masum Omar Jashim (3 months ago)
Awesome. Thanks Mr. Sele!!!
How to Excel login form https://youtu.be/bvOfMp_NLA0
Jonathan Kurnia Wijaya (3 months ago)
Thank you for the very helpful tips!
Yerbol Tulegenov (3 months ago)
Sele, I have Excel 365 ProPlus, 32 bit, but I don't have "track changes" function and "date and time picker"( Also, I wanted to use Convert Currency Live tip, but when I get data from web-site (x-rate) and loaded the table, Excel sees the content only like text (not numbers). Therefore it can't calculate anything. When I change the format of cells, Excel just gives me an Error. What am I doing wrong?
Sele Training (3 months ago)
Those functions are in the Office 365 version of Excel and 32bit, not 64bit. If you have the older ProPlus it's probably not included. For the currency converter make sure you open the table in the browser first to see the table data you want to load then copy the URL. So use "www.x-rates.com/table/?from=USD&amount=1" for example. Make sure to select the table from the menu, not the document.
nekogod (3 months ago)
One extra tip for slicers, you can hold ctrl to select more than one item per slicer.
Sele Training (3 months ago)
Good info!
Anvesh Erugu (3 months ago)
Hi, I am currently working on a project where I track data everyday and draw charts using final output of the data. Currently I am inputing data onto separate table everyday manually to generate charts. How can do this automatically for example Day 1: Data A B C 1 1 1 1 1 1 1 1 1 --------------- 3 3 3 A B C D 3/11/2019 3 3 3 3/12/2019 3/13/2019 Day 2: A B C 1 2 1 1 1 1 1 1 0 --------------- 3 5 2 A B C D 3/11/2019 3 3 3 3/12/2019 3 5 2 3/13/2019 I am currently in putting manually from data table into date table every data to produce charts I want to do this automatically, need help.
Sele Training (3 months ago)
You may be able to use the Data-Get Data-From File option on the menu. That will load data into the spreadsheet from variety of data sources. If that works then you can create a table that references the imported data to do your summaries.
De Com (3 months ago)
Mind blown! I only thought I knew Excel. Great video, sir!
Ashish Taparia (3 months ago)
One of the best videos I have come across. Many thanks. No high pitch... No extra tones... Just a very calm, relaxed and professional way of explaining... Kudos!!!
Rick Box (3 months ago)
I noticed that AutoSave was not shown on top left of the form. I only know about 10% of what I should concerning Excel 2016. Any help/comment is appreciated.
Sele Training (3 months ago)
I did not have that enabled on the video. You can set autosave by going to File-Options-Save from the menu.
Pramesh Manandhar (3 months ago)
Simple and very clear.. Thank you
TAUHID HOSSAIN (3 months ago)
Great Video!
Reyaz R. Khan (3 months ago)
Great work, very cool explanation. Thanks a lot.
Vivek Gupta (3 months ago)
Excellent.
woss2010 (3 months ago)
LOVE IT THAT PART TO COPY THE WEBSITE WOOHOOOOOO
woss2010 (3 months ago)
THE BEST !
Nic Wan (4 months ago)
They’re really useful, thanks so much for the teaching!
Shadow Fox (4 months ago)
Thanks so much. You explained it so well. I'm gonna try this out.
Jeremiah Diaz (4 months ago)
What about making a list of mp3 tracks? Can something like this can be done? I did enjoyed the video, and I just subscribed.
Sele Training (4 months ago)
Sure. You can make a list in Excel and even create a hyperlink to the MP3 file.
Christian Pretorius (4 months ago)
I remember using the *Shift+Ctrl+Enter* that was covered in the first tip with some other function as well. Can't seem to quite remember what the function was but it's definitely something I'll brush up on more. Thanks for sharing your wisdom :)

Would you like to comment?

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