Leila, no matter how much I think I know about a certain Excel feature or function, you always manage to uncover all these hidden gems when I watch your videos. Thanks once again for another great video!
Thanks to my Beautiful/Brilliant/Brainy/Witty Excel Teacher, for teaching us in an Excellent way. In every Video you are making us to learn & learn something, and become smarter. Thanks a lot.
It proves that we don't see many things which are in front of our eyes, as such do not even explore them. That's where a role of a teacher starts... To show a correct path. Thanks a lot Leila
I have been into data science for close to 10 years now and I must say that you are like a greek goddess for excel users both beginners and expert users!! Great going!!👍🏻
Awesome!. I used Find & replace but never knew that we can find for a format/color & replace them. Great aspect. You're a really awesome mentor to the subscribers
Indeed Find and replace option helps us a lot. I used them very frequently as it is too helpful when you need to change anything in your workbook. You can change your formula reference, you can filter bold part, you can change whole formula ( I used this trick to put SUBTOTAL instead of SUM in multiple cells) Really great option in excel. Thank you Leila sharing more things like highlighting all the found value in one time.
@@LeilaGharani Except "Replace" no longer works in 365 *_except_* on "formulas". I used to use it all the time but they've stuffed it up and its so, so annoying!
You wouldn't believe the timing. I am just about to make formatting changes to a large workbook over several sheets and this will smae me a whole lot of time. Many thanks.
I have been struggling with finding good resources for my excel questions until I came across your videos. Thanks Leila for putting through such an amazing job.
I never actually 'bothered' to consider Find/Replace as anything else but just that... text replacement. But as with all your tutorials you always shed new light on things, you rock! :)
Thanks Leila Very good refresher. Use this function regularly to change text with formulas across multiple sheets and multiple tabs, which helps to expedite the process immensely. I learned from your video about selecting from within the found cells box how to highlight and applyi formatting. Helpful as well was the color picker, remembering the RGB values and applying them replace or find sections. Thanks
Thanks so much Leila. I came in here to find a specific find and replace option. You showed me how and then I also learned more that I didn't even know excel could do. Really going to cut my time in half 🙂
I like the find and replace functions, especially with the options and the find all and replace all possibilities. And I am very happy you use a lot of keyboard shortcuts !
2:34 When you actually think that you are a "find & replace" expert, but you had no idea whatsoever that this can be done..... Thanks Leila. For everywhere that Excel is involved, I have come to think of you as my personal assistant.
Awesome Leila!!! I can't imagine so much hidden in excel in find and replace. You are making me to learn and new dimensions to look it in every video you post. Keep up the good work.
Hi Leila. After "find all", to select all, I believe it ought to be Ctrl + Shift + down arrow key (not just Ctrl + down arrow key). When I did Ctrl + Shift + down arrow key, I was able to select all results and then format them as you told us how. Excellent video. Thank you very much and Cheers!
You're welcome Jim - I also hadn't realized it for the longest time....until we had to adjust the color scheme of all our workbooks at my previous company.
Great videos. No need to memorize the RGB code in the end though. Click "Format" after selecting a format from a cell and you can edit the selected format.
Thanks Leila. You always find something new and interesting in Excel we all “should” know, but don’t. I for one have seen the format button there, but have not used it. I’d be the one searching manually for all the blue to change to green. Thanks for the video.
Awww... enlightening. replace colors and being able to mass highlight the finds.... awwww LOVE IT, upset I didn’t know about it years ago. Thank you Leila
All your videos are super and informative...keep it up...I want you to note that I use Find and replace in several ways like adding $ or string over a group of data sets to make it constant..another way is convert the formula into text by replacing = with " =" this helps in modifying the formula properly.
Hi Leila.. thanks for the great video on Find and Replace which I use often for anything from modifying text and formatting to cleaning data to prepare for use in a Table. Another goodie on the VBA side that I learned from Jon Acampora of Excel Campus is using the Range.Find() method for identifying the last used row and column in a worksheet, without having to pick a specific row or column to search on. This can be useful if the height or width in your table or work area may contain blanks or is not necessarily uniform and contiguous and you want more precision than provided by the .UsedRange method which can give inaccurate results if a larger range has previously been used in the worksheet than is currently being used. Jon's approach is as follows: lRow = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row lCol = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column It is a bit more to type than the Range.End(xlUp) or Range.End(xlToLeft) method, but it is bulletproof in terms of always identifying the last used row or column without first having to identify which row or column to test. Using it in VBA made me even more aware of its usefulness directly in Excel at the dialog box level and vice versa. Your video shows some of the many ways to put it to good use directly from within Excel. The possibilities are really endless. Thanks for the insights and thumbs up!
Hi Leila, nice video as usual. Just wanna to share one trick I used to select all "Found" items... click any of the found items under the "Find result" and then press Ctrl+A. That's the magic key I really like. :) Hope you like it too.
You were right. Picked up some very useful tips. Especially like the ability to replace colours so easily. Thank you as always for your knowledge shares.
I'm glad you like the content. It's just a mixture of the challenges I was faced with in the past and questions from you (wonderful supporters of this channel).
To use find All, Instead of selecting with mouse you can also use Ctrl +A to select all. After selecting you can do whatever you want with found items and format the selected cells as you want. It will do the things in one go
I use Search and Replace in Excel to force a carriage return within cells because I often work with non-numeric data in cells. So, for example, if I have three values in a cell with a space between them I search on space and replace with ctl+j (which is "forced" carriage return in a cell). That puts each of the tree values on its own line within a cell. I probably do this at least once a week in my work. Of course, delimiter can be anything-- not just space. Note that when you enter ctl+j as rep[lacement value nothing shows up in the window because it is "invisible", but it works in the resulting cell.
I have been working with the index function, i have this incredible calender i have invented, I use, weeknumber and weekday functions for the rows and columns. Works really great.. Is there anyway possible to get a reverse row and column without using row and column, I know that sounds kinda off, but in order to stay in check with the date i must use a twin to match with the date. A formula would like this, date ---- index(a1:g80, weeknumber(date, 2), weekday(date, 2) And that will give the coordinates to the cell you need, But i would also like to find a reverse match for that cel so I would not have to make a twin sheet with dates, I know i can insert rows to find that match, all im doing is matching the cell to the date for onoe one color print coded sell will show, so i find the date easier,
Download the file I used in the video from here 👉 pages.xelplus.com/find-replace-excel
Leila, no matter how much I think I know about a certain Excel feature or function, you always manage to uncover all these hidden gems when I watch your videos. Thanks once again for another great video!
I'm glad you find these tutorials helpful Robert. Thank you for your support!
You are right again... I have been 'staring' at Excel for years and have never used these basic features before! Thank you Leila.
Glad you found something useful Robert!
Thanks to my Beautiful/Brilliant/Brainy/Witty Excel Teacher, for teaching us in an Excellent way. In every Video you are making us to learn & learn something, and become smarter. Thanks a lot.
It proves that we don't see many things which are in front of our eyes, as such do not even explore them. That's where a role of a teacher starts... To show a correct path. Thanks a lot Leila
I have been into data science for close to 10 years now and I must say that you are like a greek goddess for excel users both beginners and expert users!! Great going!!👍🏻
Greek goddess ... I like that :)
Awesome!. I used Find & replace but never knew that we can find for a format/color & replace them. Great aspect. You're a really awesome mentor to the subscribers
Indeed Find and replace option helps us a lot. I used them very frequently as it is too helpful when you need to change anything in your workbook. You can change your formula reference, you can filter bold part, you can change whole formula ( I used this trick to put SUBTOTAL instead of SUM in multiple cells)
Really great option in excel. Thank you Leila sharing more things like highlighting all the found value in one time.
Thank you Ankur for your feedback. Agree - it's very helpful for adjusting formulas too :)
@@LeilaGharani Except "Replace" no longer works in 365 *_except_* on "formulas". I used to use it all the time but they've stuffed it up and its so, so annoying!
This blew my mind!!!! You are a genius
You always teach something new about what we are already using but in a very limited and conservative way. You are superb! Thank you so much
Thank you Rahul. I'm very glad to hear these tutorials help.
the way you explain the things are very easy to understand....such an great video.....
Glad you think so!
Fantastic, you are a wealth of knowledge. I know Excel well, but you give me much more, thanks you.
I'm glad to hear that.
I must say you teach me so much with every video you are publishing ,and your presentation is so clear thank you so much for doing that
Thank you Nissim for watching. I'm very glad you like the videos here.
You wouldn't believe the timing. I am just about to make formatting changes to a large workbook over several sheets and this will smae me a whole lot of time. Many thanks.
That's perfect :)
I have been struggling with finding good resources for my excel questions until I came across your videos. Thanks Leila for putting through such an amazing job.
I'm very happy to hear that! Thank you for your support.
I never actually 'bothered' to consider Find/Replace as anything else but just that... text replacement. But as with all your tutorials you always shed new light on things, you rock! :)
You are the best I have seen on TH-cam on this subject. Thank you!
Thanks Leila Very good refresher. Use this function regularly to change text with formulas across multiple sheets and multiple tabs, which helps to expedite the process immensely. I learned from your video about selecting from within the found cells box how to highlight and applyi formatting. Helpful as well was the color picker, remembering the RGB values and applying them replace or find sections. Thanks
You're welcome Antonio. Agree the color picker can come in handy.
U go to quick..v
Thanks so much Leila.
I came in here to find a specific find and replace option.
You showed me how and then I also learned more that I didn't even know excel could do.
Really going to cut my time in half 🙂
Wonderful! Glad it was useful.
Oh great, not done before. Not only this, Your every tutorial is awesome. You are really genius dear.. Keep it up
Using find and replace function, but never used by this methods. It's new for me. Thank you mam.
You're very welcome Piyush.
I always learn something from you.
Super helpful, as always!!! HUGE THANKS Leila - from China!!!
You're so welcome!
Thanks so much Leila. I will certainly learn so much from you.
I like the find and replace functions, especially with the options and the find all and replace all possibilities. And I am very happy you use a lot of keyboard shortcuts !
It's just faster with shortcuts, right?
Wow... I've been reformatting so many spreadsheets one by one 😂 You are a game changer. Thanks!!!
2:34 When you actually think that you are a "find & replace" expert, but you had no idea whatsoever that this can be done.....
Thanks Leila. For everywhere that Excel is involved, I have come to think of you as my personal assistant.
Happy to hear that :)
I'm learning and it's quite interesting how you explain what you're doing, using various hypothetical and actual, examples thank you Miss.
Cool, thanks Ted!
Awesome Leila!!! I can't imagine so much hidden in excel in find and replace. You are making me to learn and new dimensions to look it in every video you post. Keep up the good work.
Wow I hesitated on clicking on this link. I am glad I did I had no idea that you could select the found cells the search results.
Thanks David - I'm glad you found something new :)
each of ur video contain so much of knowledge......Thanx for great job that u r doing for us........ Keep it up Leila..
Thank you Jasvir. I'm glad you find them useful.
TIL that I can select the cells in the Find All list. What an awesome tip! Thank you for providing such great information!
Glad it was helpful, Simon!
EXCELLENT-WELL DONE.PARTICULARLY THE COLOR FORMATTING.
Thank you! Cheers!
Hi Leila. After "find all", to select all, I believe it ought to be Ctrl + Shift + down arrow key (not just Ctrl + down arrow key). When I did Ctrl + Shift + down arrow key, I was able to select all results and then format them as you told us how. Excellent video. Thank you very much and Cheers!
I never knew about this on formatting. This is awesome!
Glad you like it.
Never used before. Knew it was there, but assumed only for text. Wow! Great tip! Thanks, Leila!
You're welcome Jim - I also hadn't realized it for the longest time....until we had to adjust the color scheme of all our workbooks at my previous company.
Something new I learn every time. Thanks.
I'm very glad to hear that Francis.
Excellent feature very good for updating changing company preferences
Thank you for another great video
Glad you like the video Peter. It really can be very useful and often overlooked.
Really at start I thought it would be worst but after watching full video came to know the better use of function. Thanks
Your voice is so soothing...
Great videos. No need to memorize the RGB code in the end though. Click "Format" after selecting a format from a cell and you can edit the selected format.
Mind blown!!... i had no idea that you can find and replace formatting :O Amazing tip! :)
Thanks Leila. You always find something new and interesting in Excel we all “should” know, but don’t. I for one have seen the format button there, but have not used it. I’d be the one searching manually for all the blue to change to green. Thanks for the video.
I always learn something new and handy here. Thanks Leila!
Thank you Yulin! I'm glad you find something new in the tutorials :)
Used for finding text and number, never format. Awesome! You have just added a new tools to my toolbox. Thanks
Same here.
Awww... enlightening. replace colors and being able to mass highlight the finds.... awwww LOVE IT, upset I didn’t know about it years ago. Thank you Leila
This is awesome ....Thanks for getting in deep into a simple feature like Find and replace ..awesome Tips
This is really interesting, have been using Excel for so many years yet never thought of this.
Thanks a lot made my work easy u saved my 1 hour❤️❤️❤️❤️❤️😘😘
I use find and replace, but not as detailed as what you have shown. Thanks again Leila!
You're very welcome Dante.
This is nice. Less time wasted replacing colours
Thank you leila. I'm always excited to view your next video. Thank you for amazing my job..
All your videos are super and informative...keep it up...I want you to note that I use Find and replace in several ways like adding $ or string over a group of data sets to make it constant..another way is convert the formula into text by replacing = with " =" this helps in modifying the formula properly.
Glad you like the videos. These are good applications of find and replace. Thanks for sharing!
Ooooohhhh. I like the option of format replacements. That is awesome. Thank you!
Honestly I have never noticed to this find and replace,
Thank you for your guide
Omg, I didn't know you could do control+down in the search box!
Hi Leila.. thanks for the great video on Find and Replace which I use often for anything from modifying text and formatting to cleaning data to prepare for use in a Table. Another goodie on the VBA side that I learned from Jon Acampora of Excel Campus is using the Range.Find() method for identifying the last used row and column in a worksheet, without having to pick a specific row or column to search on. This can be useful if the height or width in your table or work area may contain blanks or is not necessarily uniform and contiguous and you want more precision than provided by the .UsedRange method which can give inaccurate results if a larger range has previously been used in the worksheet than is currently being used. Jon's approach is as follows:
lRow = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
lCol = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
It is a bit more to type than the Range.End(xlUp) or Range.End(xlToLeft) method, but it is bulletproof in terms of always identifying the last used row or column without first having to identify which row or column to test. Using it in VBA made me even more aware of its usefulness directly in Excel at the dialog box level and vice versa. Your video shows some of the many ways to put it to good use directly from within Excel. The possibilities are really endless. Thanks for the insights and thumbs up!
Thank you for sharing the VBA tip from Jon - that's a great method to get more precision. Thank you Wayne!
Again a new thing ... super and very handy .. Thank Leila for sharing .
My pleasure Abhishek 😊
Hi Leila, nice video as usual. Just wanna to share one trick I used to select all "Found" items... click any of the found items under the "Find result" and then press Ctrl+A. That's the magic key I really like. :)
Hope you like it too.
You're right! Control + A! Thanks :)
@@LeilaGharani My pleasure! :)
Excellent! As usual comprehensive view on selected topic. Uncheck "Clear find format" and suddenly "Find + Replace" starts to work again
Glad you like the video.
The ability to replace the format was really helpful (and unknown till now) Thank you very much
As always, you are our source of inspiration.
Always coming with New Miracle, once again thanks a lot for sharing valuable information.
Thank you for your support Anil! I'm glad you like the tutorials.
Thank you Leila! Exactly what I needed to replace 2021 with 2022 within my formulas in my cells.
👍
Didn't know about the format functionality. Pretty handy. Thank you !
You're very welcome Herve!
You were right. Picked up some very useful tips. Especially like the ability to replace colours so easily. Thank you as always for your knowledge shares.
You're very welcome. Glad you found something new here.
Noch nie gebraucht, da ich es nicht gekannt habe. THX
Love this! Used the function before but only to replace characters not format. Tqvm for this video!
Glad you like the video.
How do you always come up with extraordinary content everytime. Hats off 2 u...
I'm glad you like the content. It's just a mixture of the challenges I was faced with in the past and questions from you (wonderful supporters of this channel).
Thanks Leila. What a revelation! Keep up the great work.
To use find All, Instead of selecting with mouse you can also use Ctrl +A to select all. After selecting you can do whatever you want with found items and format the selected cells as you want. It will do the things in one go
love the Control + A. Thanks Prabhu.
You're Welcome Leila!
I didn't know this feature. thanks a lot Leila.
My pleasure José.
Never used it because I simply did not know it existed Pretty helpful. Thank you Leila again :)
You're very welcome Mostafa :)
I haven't used the format find before ! It's really looks useful, thanks.
Glad you like it!
One of those simple things that I never knew. Very useful - Thank you 👍
Thank you a lot Leila, awesome as usual
Wow, briliant stuff leila !!
Glad you like it!
Good vid. I use it a lot with CTRL J to replace Alt Enter spaces within a cell.
You're the Goddess of excel
You Helped me more than you can imagine it. Thank you
Thank you once again Leila....♥️♥️♥️
Thank you for the tutorial, I learnt some useful features of Find and Replace that I wasn't aware of before 🙏
Glad it was useful!
Great job, Leila. I learned something - as always when watching your videos
I'm glad to hear that Hubert!
you are an amazing teacher!
Glad you like it Mohammad!
Great advice about find and replace which has a lot more uses than I thought!
The clicking and dragging in the found options to select everything on the sheet is 🤯🤯🤯🤯🤯🤯
Thanks leila. I am your fan. Thanks again and keep it up.
Thank you for your support!
@@LeilaGharani you know i am expert in excel but i learn new thing of your every video. Thanks for support.
I solved one problem real quick. Thank YOU!
My pleasure :)
Very cool ... I knew only a few, but you brought some amazing ones. Thanks for these tricks.
You're very welcome Luciano :)
Leila, you are awesome. Keep it up.
Just awesome. Didn't know about the extensive usage
Yes, it's easily overlooked but can be very powerful :)
I use Search and Replace in Excel to force a carriage return within cells because I often work with non-numeric data in cells. So, for example, if I have three values in a cell with a space between them I search on space and replace with ctl+j (which is "forced" carriage return in a cell). That puts each of the tree values on its own line within a cell. I probably do this at least once a week in my work. Of course, delimiter can be anything-- not just space. Note that when you enter ctl+j as rep[lacement value nothing shows up in the window because it is "invisible", but it works in the resulting cell.
Thanks leila, that was really kool, you are certainly a master...
Thank you William - no master - just like to experiment :)
I have been working with the index function, i have this incredible calender i have invented,
I use, weeknumber and weekday functions for the rows and columns. Works really great..
Is there anyway possible to get a reverse row and column without using row and column,
I know that sounds kinda off, but in order to stay in check with the date i must use a twin to match with the date.
A formula would like this, date ---- index(a1:g80, weeknumber(date, 2), weekday(date, 2)
And that will give the coordinates to the cell you need,
But i would also like to find a reverse match for that cel so I would not have to make a twin sheet with dates,
I know i can insert rows to find that match, all im doing is matching the cell to the date for onoe one color print coded sell will show, so i find the date easier,
Was never aware of this in all the years I have been using Find and Replace!
It really can be used in many different ways.
You are definitely an Excel master mam.
Thanks a lot 😊
Thank you for any information you provide us
You're very welcome. Thank you for your support.
Thank you Leila...never used format replacing.
You're very welcome Zahid :)
Never realise able to highlights from Ctrl+find option. Another tricks learn from your video.. Love it Leila, u r excel angel.. ;)
I'm glad you found something new here kenny :)
Never knew about the formats...great tip!!
I'm glad you like it.
DEAR LEILA AS USUALL U HAVE AGAIN AMAZED BY GIVING SUCH A SURPRISING USE OF FIND AND REPLACE 😈😈😈