Thanks for the suggestion from my comment in your Advanced Tips & Tricks video. I was introduced to Index-match maybe two years ago, and it had me kicking myself for all those extra columns added to make vlookups work. Some of the other combinations I would not have thought of but can see how useful they could be. I get an odd joy out of formulas that extract data from text, maybe because of all the time it saved me when given a data entry task. I also want to up-sell the power of using list data validation for cells in a formula.
Oups ... I meant that your explanations were very clear and progressive ... that’s the meaning of the word ‘didactique’ in French ... we even use the word ‘didacticiel’ which designate a software dedicated to digitally teach a subject.
Amzing video as usual. You explain the concept in such a simple and lucid manner. I have one querry (not related to this video) I may be wrong as well. Is there any option/function by which we can filter the rows just the way we filter columns? Thanks a lot
Thanks. I'm not aware of any way to filter the rows like you can the columns. Good question though. Probably the closest option would be to do a transpose of all the data thus reversing columns and rows then filter them as columns. Not as nice an option.
Hii, why does =SUMPRODUCT(LEN(TRIM(B7:B8))-LEN(SUBSTITUTE(B7:B8," ",""))+1) work, but =SUM(LEN(TRIM(B7:B8))-LEN(SUBSTITUTE(B7:B8," ",""))+1) doesn't work, anyway we are summing over a 1D array, so why not use SUM instead of SUMPRODUCT?
Good day to u Sir, Is there any way to unhide all sheets with a single click... I mean I have a bunch of worksheets, I have hide them, when need to unhide i have to unhide each worksheet one by one... too lengthy
@@SeleTraining Hi Waqas.. un-hiding all worksheets with a single click is easy with a small amount of VBA code. Go to the VB editor by pressing ALT+F11. Add a module and add the following code to the module: Sub UnhideAllSheets() Dim ws As Worksheet For Each ws In Worksheets If ws.Visible = xlSheetHidden Then ws.Visible = xlSheetVisible End If Next ws End Sub Then put a button on your worksheet and assign the macro to the button. You must save the workbook as macro enabled .xlsm. If you want this solution to work for any workbook you open, then put the code in your Personal Macro Workbook and add a button to the QAT - Quick Access Toolbar or add a custom group to the ribbon menu and connect the macro to the QAT button or custom group icon. If all of the above is unfamiliar to you, here is a RUclips video that might help you learn how to do it: www.xelplus.com/3-methods-to-unhide-all-sheets-in-excel/ I hope this helps. Good luck!
Hello, thank you for the video. I do need help: how to apply nested if if I want a specifi word when the cell contains another word, there are 7 of them?
You may be better off using VLOOKUP. Create a list with the 7 words. Then you can VLOOKUP the cell in that list to see if it is there. See the 20 Essential Functions video to learn how VLOOKUP works.
Naima PEDRONO Thank you for your comment. Unfortunately I’m not the indicated person to explain some “ how to”. I prefer to youch technically themes as little as possible. I think you’re better of asking someone, who knows. Best regards Henrik Saar 😊
If you download the reference guid you can click the link in the guid to play just the video section for that function. Also, you can slow down the video playback speed at the bottom of the youtube window.
These advanced functions combine with the essential functions, are Impressive and very practical for using excel !!!
it was a kind of video that I was really looking for. Thanks so much
Thanks for the suggestion from my comment in your Advanced Tips & Tricks video. I was introduced to Index-match maybe two years ago, and it had me kicking myself for all those extra columns added to make vlookups work. Some of the other combinations I would not have thought of but can see how useful they could be.
I get an odd joy out of formulas that extract data from text, maybe because of all the time it saved me when given a data entry task.
I also want to up-sell the power of using list data validation for cells in a formula.
You are an angel for sharing your expertise! Thanks
Wow I am always amazed by the power of excel and you present it in a very didactical way
Thank you ... Great video
Erick
I learned a new word today - Didactical.
Oups ... I meant that your explanations were very clear and progressive ... that’s the meaning of the word ‘didactique’ in French ... we even use the word ‘didacticiel’ which designate a software dedicated to digitally teach a subject.
@erick bourdinI looked it up and found the English word. Just never saw that before but it makes sense.
Wow, im really impressed, helped a lot with lately problems of mine. Thank you!
Thank you very much for your lavish generosity!
Great video! Very impressive and covered a number of function
Really very practical and valuable video. Thanks
Thanks very much. Very clear and useful.
Great video and explanation Sumit
Great video indeed and useful material from your website! Thanks for this :)
Thank you!🙏
Great!!! Thank you!!!
Thank you very much.
Wooow I like computer lesson 😍
I like this magic.
Awesome :)
Awesome knowledge!! Thanks so much for sharing
Nice..,thank you..,
Very useful, it's cover all the small function
Amzing video as usual. You explain the concept in such a simple and lucid manner. I have one querry (not related to this video) I may be wrong as well. Is there any option/function by which we can filter the rows just the way we filter columns? Thanks a lot
Thanks. I'm not aware of any way to filter the rows like you can the columns. Good question though. Probably the closest option would be to do a transpose of all the data thus reversing columns and rows then filter them as columns. Not as nice an option.
Most of these have broken my brain.
Hii, why does =SUMPRODUCT(LEN(TRIM(B7:B8))-LEN(SUBSTITUTE(B7:B8," ",""))+1) work, but =SUM(LEN(TRIM(B7:B8))-LEN(SUBSTITUTE(B7:B8," ",""))+1) doesn't work, anyway we are summing over a 1D array, so why not use SUM instead of SUMPRODUCT?
Good day to u Sir,
Is there any way to unhide all sheets with a single click...
I mean I have a bunch of worksheets, I have hide them, when need to unhide i have to unhide each worksheet one by one... too lengthy
No quick method that I am aware of. You could use a third party tool like Kutools to do it.
Yes there is, VBA does that Magic!
@@SeleTraining Hi Waqas.. un-hiding all worksheets with a single click is easy with a small amount of VBA code. Go to the VB editor by pressing ALT+F11. Add a module and add the following code to the module:
Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Visible = xlSheetHidden Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub
Then put a button on your worksheet and assign the macro to the button. You must save the workbook as macro enabled .xlsm. If you want this solution to work for any workbook you open, then put the code in your Personal Macro Workbook and add a button to the QAT - Quick Access Toolbar or add a custom group to the ribbon menu and connect the macro to the QAT button or custom group icon. If all of the above is unfamiliar to you, here is a RUclips video that might help you learn how to do it: www.xelplus.com/3-methods-to-unhide-all-sheets-in-excel/
I hope this helps. Good luck!
Hello, thank you for the video. I do need help: how to apply nested if if I want a specifi word when the cell contains another word, there are 7 of them?
You may be better off using VLOOKUP. Create a list with the 7 words. Then you can VLOOKUP the cell in that list to see if it is there. See the 20 Essential Functions video to learn how VLOOKUP works.
Naima PEDRONO Thank you for your comment. Unfortunately I’m not the indicated person to explain some “ how to”. I prefer to youch technically themes as little as possible. I think you’re better of asking someone, who knows.
Best regards
Henrik Saar 😊
Who needs nested IF when we have IFS? Or MAX(IF when there is MAXIFS?
Yes, you can use IFS for multiple selections.
👍🏆
I couldn't find the reference guide.
Go to seletraining.com and the download files section.
Living Waters What do you mean by reference guide?
Keep camera more closer to the screen.
I could zoom in but it is a screen capture not a camera. Thanks
Your video is to fast and little difficult to understand
If you download the reference guid you can click the link in the guid to play just the video section for that function. Also, you can slow down the video playback speed at the bottom of the youtube window.