Let me know what your favorite time-saver tips are! You can checkout our Power Query course here: link.xelplus.com/yt-c-timesaver-pq-course to become a data wizard. Plus, don't miss out on exclusive tips, updates, and productivity tricks - sign up for our "Between the Sheets" newsletter: link.xelplus.com/yt-c-newsletter Let's excel together! 💪 P.S. Addressing this here since I saw it in the comments - "Why not use a pivot table?" Very smart question - and the answer is you can certainly use a pivot table - why I didn't use one is because originally I'd added some gaps in the data but I went over 10 clicks 😁 during the cleaning process. The advantage with PQ as you probably know by now is the cleaning we can do before and also that the data can come from another file or another source. One advantage of using PQ here instead of a Pivot table is we don't have the restriction with the pivot table layout. That does annoy me sometimes🙂
@@LeilaGharani Please can you do a video on how you got your Microsoft MVP I'm from Nigeria and I've been inspired by your Microsoft Excel Enthusiasm and I like to know the path you took in becoming a Microsoft MVP
Lelia's Power Query course is amazing! I learned so much and was able to automate several time consuming reporting tasks and save hours of work. I have also taken the VBA and Dashboard courses which were also extremely helpful. The skills I learned from Lelia have allowed me to impress my managers and become the go to for our division for complex Excel tasks. This took me to the next level in my career. I recommend Leila's courses to anyone who wants to build or improve their Excel skills.
3:05 I teach a lot of people on filter top and bottom values (using normal filters) It's so unknown and save so much time! Thanks Leila for the great Analyze AI Tip!!!
Thanks Leila! By far, Power Query is my favorite. As I've improved my PQ skills (and DAX and Power BI), I have dramatically increased my efficiency. Also, on tip #1 for navigation, another way to quickly navigate is to right-click on the left-right arrow right before the first sheet tab in the workbook (right above where it says "Ready"). An Activate box pops up, and you can quickly choose the sheet you want.
The tip about Bookmarks is enhanced by the Go To, Ctrl-G, keyboard shortcut. Ctrl-G opens a dialog box showing the available bookmarks (along with tables) in the upper window titled "Go to:". Click the desired bookmark to go there. Then, to go back to where you started, click Ctrl-G again. The location of the prior cell (before clicking Ctrl-G initially) appears in the lower window titled "Reference". Click ok and you are back to where you started. In one of my large workbooks, I have a sheet called "Clipboard" where I store repeatedly used text and that I have bookmarked. Ctrl-G facilitates copying and pasting from the Clipboard worksheet in this workbook.
Ohh my goodness !!! I absolutely love the way you explain MS excel, MS PowerPoint. You have nice presentation skills. You know how to attract customers.
Excellent video! I've been using Excel for over 20 years and had ro do all of this the hard way, which was the only way. On one hand, this cheapens my experience, but it's such a time saver that you can't help but appreciate it.
I was wondering about Thursday videos missing and then this came and "hi five" 🙌 to you Leila, for the 5 hidden "gems" you revealed! The "Power Query" was the "Top Cherry" for sure. Thanks for all the great knowledge you possess and pass on! Stay blessed! 🙂
You are welcome! I like the tip you transformed data into monthly report on sales. Do you have any video transform those data into weekly report? Thanks Leila! Hope you always healthy and beautiful!
Thanks Leila your videos are very clear and focused. I am learning a lot. I started with Lotus 123 in late 1980's and then Excel and the transformation is amazing. There is so much we can do now and you have been a great source for me to learn and admire the power of excel. It's a universe to be explored and learn from. best wishes
I've been retired for 6 years and I almost never use Excel anymore. Its mind blowing how much has changed. In my last couple of years the whole Power BI and Power Query stuff was new and it was a source of frustration that, while very similar, there were big differences between Excel and Power BI. It would be interesting to revisit that ecosystem and how things are going. Do they have feature parity now? Do they interact better now?
I recently used named manager + the Indirect function (data validation) to have dependable list drop downs. Name manager is a very neat tool to have in your arsenal
Sometimes it’s the simple things… range names as a bookmark! If only forecasting was really that easy! Maybe one day it can have the difficult conversations with account managers and directors 😜 I love power query. I learned it after I quit my last job and now embrace it in my new job. It is such a timesaver and enables me to handle so much data quickly and easily. If I could go back to my old job knowing what I know now things would be so different.
I've been using links in Excel sheets to make shortcuts to different workbooks and back. You can define a cell reference to go to a specific location and it works much faster for us than typing in the named cell.
I agree, I use links in Excel to bring up specific web pages - you just have to rename them to something more concise. Links are simple but underrated.
Thank you, thank you, thank YOU. You are a life saver. I use Mac and don’t remember if the same features are visible on my Mac Excel. I wish I had know this a month ago as I manually calculated something similar. 😮
True I had purchased it from India i am Microsoft certified trainer and it will helps me lot for my students ! strongly recommend ! do not miss i never found like this type of Easy State of Art Materials
I have a file with multiple print areas on multiple sheets, that also gets emailed - so I don't want to have it loaded with unnecessary VBA that might cause issues for the recipients. I use the Name Box to select the name, select "Set Print Area" and "Quick Print" from the Quick Launch bar (a custom QL bar can save a ton of time!), select the next name, and so on. The printing is done with just a few clicks, without having to worry about where in the file the print ranges are located.
You don't have to use power query for sales related or financial data. It can be used for anything that you want in a table. For example a file index. I use it to extract information from transmittals and get an overall record of documents received at what rev. I also use names. They are great as well for long formulae.
Thanks Leila! Great! Amazing work, everyday I learn new excel function from your video stock, Thank you. Please also launch a video on Stock/inventory run or control.
Glad you like it. You could also look into quick parts in Outlook. Really great and flexible for template responses. Here is some more information in case you're interested: th-cam.com/video/RSlfhjbIoK8/w-d-xo.html
Amazing stuff! One question that came to mind for the PQ section. Can this not be done in pivot relatively quickly as well? Since pivot can group dates by month?
In the power query example, you can also transform the date column directly to month instead of adding another column and deleting the date column (one step instead of two).
First of all I would like to thank you for teaching thousands of people through the channel. Can you let us know how to use an advanced filter in a shared Excel sheet.
Great vid, Out of insterest why use PQ to pivot data? instead of .....once the "Month Name" column is added in PQ just load the data straight to a pivot table and have the "Month Name" as the columns? Genuine question and was wonder if a certain way would be better
Maybe my example was not the best. Pivot tables have their place but Power Query can do so much more. It can do data cleaning and the data can come from another file or another source.
One of the time saver tip on Auto Filter. Instead of using Data->Filter- select the value manually to get the output, simply place the cursor in the cell in which you want to filter, right click, filter by cell value. I am sure you can save time especially if you have huge data. Thanks
Hi, nice one. Few new things to learn. Thanks for sharing. Can you help me on one matter. If we have purchase and sales quantities in 2 different excel sheets or even within same sheet, how can we calculate closing quantities of stock and their costs under FIFO using Formula, or pivot, Power query or even BI
First of all, I love your channel. I use both a Windows Laptop and a Mac and use Excel on both. However I predominantly use my Macbook. When you post your videos is it possible to mention upfront if the features mentioned can be accessed on a Mac also? Thanks.
Originally I had some gaps in the data but I went over 10 clicks 😁 but yes a pivot would do fine here. The advantage with PQ as you for sure know is the cleaning we can do before and also that the data can come from another file or another source. At least in this example we don't have the restriction with the pivot table layout. That does annoy me sometimes🙂
Hello Leila, does your cause has a time limit? Like must complete within some time spans? I want to learn but I’m extremely busy at the moment so if my purchase has a expiration date I’ll star later.
I print payroll checks in excel. I am looking for a formula that will advance the check number after printing a check. Example, after printing payroll check number 2799 I need a formula to advance the check number to 2800. The cost is almost x10 for sequential numbered printed checks vs something from amazon that has no numbers.
Let me know what your favorite time-saver tips are! You can checkout our Power Query course here: link.xelplus.com/yt-c-timesaver-pq-course to become a data wizard.
Plus, don't miss out on exclusive tips, updates, and productivity tricks - sign up for our "Between the Sheets" newsletter: link.xelplus.com/yt-c-newsletter
Let's excel together! 💪
P.S. Addressing this here since I saw it in the comments - "Why not use a pivot table?" Very smart question - and the answer is you can certainly use a pivot table - why I didn't use one is because originally I'd added some gaps in the data but I went over 10 clicks 😁 during the cleaning process. The advantage with PQ as you probably know by now is the cleaning we can do before and also that the data can come from another file or another source. One advantage of using PQ here instead of a Pivot table is we don't have the restriction with the pivot table layout. That does annoy me sometimes🙂
I have multiple worksheets in Excel. How to freeze my first worksheet so that it can be easy to navigate & return back
Along Came a Spider
Stop the Sheet
Oh! Silence of the Lamb's
Analyse Ann ii
I’m solid with excel, and yet Leila still teaches me something every time!
The learning never stops 😊 Thanks for tuning in, Jake!
So true!
Me too, I'm always learning new and useful things!
@@LeilaGharani Please can you do a video on your achieving Microsoft MVP honours
@@LeilaGharani
Please can you do a video on how you got your Microsoft MVP
I'm from Nigeria and I've been inspired by your Microsoft Excel Enthusiasm and I like to know the path you took in becoming a Microsoft MVP
Lelia's Power Query course is amazing! I learned so much and was able to automate several time consuming reporting tasks and save hours of work. I have also taken the VBA and Dashboard courses which were also extremely helpful. The skills I learned from Lelia have allowed me to impress my managers and become the go to for our division for complex Excel tasks. This took me to the next level in my career. I recommend Leila's courses to anyone who wants to build or improve their Excel skills.
Thank you for your kind words! It's great to hear our courses have supported your career advancement. Your recommendation means a lot to me! 🙏
Using the Name box for bookmarking a cell within a tab of a large spreadsheet-brilliant!
3:05 I teach a lot of people on filter top and bottom values (using normal filters) It's so unknown and save so much time! Thanks Leila for the great Analyze AI Tip!!!
Thanks Leila! By far, Power Query is my favorite. As I've improved my PQ skills (and DAX and Power BI), I have dramatically increased my efficiency. Also, on tip #1 for navigation, another way to quickly navigate is to right-click on the left-right arrow right before the first sheet tab in the workbook (right above where it says "Ready"). An Activate box pops up, and you can quickly choose the sheet you want.
Great tip! Thanks for sharing, Chris!
Yes, Correct
Hyperlink is also an another option to navigate.
I use Ctr + G and then type the name range that I made to navigate to specific sheet or cell. I find it so handy because I rarely use mouse.
Thanks!
Thank you! Glad it was helpful.
The tip about Bookmarks is enhanced by the Go To, Ctrl-G, keyboard shortcut. Ctrl-G opens a dialog box showing the available bookmarks (along with tables) in the upper window titled "Go to:". Click the desired bookmark to go there. Then, to go back to where you started, click Ctrl-G again. The location of the prior cell (before clicking Ctrl-G initially) appears in the lower window titled "Reference". Click ok and you are back to where you started.
In one of my large workbooks, I have a sheet called "Clipboard" where I store repeatedly used text and that I have bookmarked. Ctrl-G facilitates copying and pasting from the Clipboard worksheet in this workbook.
Turn that Clipboard page into an internal navigation tool by turning them into clickable Links. Insert | Link | Place in This Document
Wow
I’m one of the 51,323 students! Truly life changing, Leila! Thanks!❤️
power query is the ultimate winner !!!
Ohh my goodness !!! I absolutely love the way you explain MS excel, MS PowerPoint. You have nice presentation skills. You know how to attract customers.
Excellent video! I've been using Excel for over 20 years and had ro do all of this the hard way, which was the only way. On one hand, this cheapens my experience, but it's such a time saver that you can't help but appreciate it.
You're right.. I won't live without these now! Thanks so much
I was wondering about Thursday videos missing and then this came and "hi five" 🙌 to you Leila, for the 5 hidden "gems" you revealed! The "Power Query" was the "Top Cherry" for sure. Thanks for all the great knowledge you possess and pass on! Stay blessed! 🙂
Yay! Thank you Vijay! Yes, it was a while 😊
Thanks for mentoring and making lives easier! ❤️ from India
You are welcome!
I like the tip you transformed data into monthly report on sales.
Do you have any video transform those data into weekly report?
Thanks Leila!
Hope you always healthy and beautiful!
Thanks Leila your videos are very clear and focused. I am learning a lot. I started with Lotus 123 in late 1980's and then Excel and the transformation is amazing. There is so much we can do now and you have been a great source for me to learn and admire the power of excel. It's a universe to be explored and learn from. best wishes
Your videos are so great. You constantly present different features Excel has to offer, and we are greatly appreciative of you!
Your excel skills are superb @LeilaGharani I always learn something new from you every time I watch your videos.
Awesome, thank you!
Alright, you got me. I came here thinking this was yet another TH-cam clickbait vid. You got me good. Very solid tips. Thanks a lot 🙏
You are a real Mrs Excel.
I've been retired for 6 years and I almost never use Excel anymore. Its mind blowing how much has changed. In my last couple of years the whole Power BI and Power Query stuff was new and it was a source of frustration that, while very similar, there were big differences between Excel and Power BI. It would be interesting to revisit that ecosystem and how things are going. Do they have feature parity now? Do they interact better now?
I recently used named manager + the Indirect function (data validation) to have dependable list drop downs. Name manager is a very neat tool to have in your arsenal
Sometimes it’s the simple things… range names as a bookmark!
If only forecasting was really that easy! Maybe one day it can have the difficult conversations with account managers and directors 😜
I love power query. I learned it after I quit my last job and now embrace it in my new job. It is such a timesaver and enables me to handle so much data quickly and easily. If I could go back to my old job knowing what I know now things would be so different.
Thanks, Leila. Loved the data analysis/power skill, featured in #2 and #5!
Hi Leila, you are the best of all the best Excel experts. This video is one of the best. Thank you.
Pivot columns! That’s what I’ve wanted on a power query problem. Thank you!
I've been using links in Excel sheets to make shortcuts to different workbooks and back. You can define a cell reference to go to a specific location and it works much faster for us than typing in the named cell.
I agree, I use links in Excel to bring up specific web pages - you just have to rename them to something more concise. Links are simple but underrated.
Great tip, Leila! Thank you so much, this will be helpful when I analyze data. I will try this on my current xls project.
Je vous encourage vivement de suivre les cours de Leila que j'ai par ailleurs déjà suivis.👌
Many thanks Leila, always learning something new...
Thank you, thank you, thank YOU. You are a life saver. I use Mac and don’t remember if the same features are visible on my Mac Excel. I wish I had know this a month ago as I manually calculated something similar. 😮
By far the best Excel tutorials online. Thank you.
Leila makes learning excel fun and interesting
I'm familiar with all the tricks, but I learnt some new tricks from the 4th tip. Always great learning from Leila.
I like way you teach, my age 51, learn lot of things from you, keep it up!!
Happy to hear that!
Very insightful. Thank you leila. Lots of love from India.
One of the most useful videos you've ever done THANK YOU !!!
Wow, thanks!
I love that vacuum tube radio in the background at 0:03.
Thank you so much for introducing all these marvelous tricks 👌👍🙏
True I had purchased it from India i am Microsoft certified trainer and it will helps me lot for my students ! strongly recommend ! do not miss i never found like this type of Easy State of Art Materials
still amazes me not just your excel skill but how you teach from just only via videos 👏🏿
I have a file with multiple print areas on multiple sheets, that also gets emailed - so I don't want to have it loaded with unnecessary VBA that might cause issues for the recipients. I use the Name Box to select the name, select "Set Print Area" and "Quick Print" from the Quick Launch bar (a custom QL bar can save a ton of time!), select the next name, and so on. The printing is done with just a few clicks, without having to worry about where in the file the print ranges are located.
Really your teaching is very nice. And it is very useful for me thanks a lot
Glad to hear that!
Great tip using name manager to quickly go to a predefined area. Always excellent content from you Leila!
The radio visible behind you intrigues me. I had one that dates back to 1960s operated by a battery pack, made by Philips.
Love it too. Got it from my in-laws.
You don't have to use power query for sales related or financial data. It can be used for anything that you want in a table. For example a file index. I use it to extract information from transmittals and get an overall record of documents received at what rev.
I also use names. They are great as well for long formulae.
Thank you so much Leila for the video
Your Teaching style is amazing. Thank you so much.
Glad you think so!
Thank you for the effort and benefit you provide to us. Continue at the same pace, with best wishes for progress and success. 🤗
Thank you! Will do!
This is amazing... Loved this one!
Thanks Leila! Great! Amazing work, everyday I learn new excel function from your video stock, Thank you. Please also launch a video on Stock/inventory run or control.
Like always...you are amazing!
Thank you, Leila.
1st and last tricks are fantastic it is really helpful
thank you 🎉
I would've transposed the data first, then used power query, however, I also like the transform feature too. Great job!
Thanks Leila. You're amazing.
Great and useful tips, with the same clarity of always!
Love your tips! Hijacking cell names for bootleg bookmarks is brilliant! One thing I hijack is custom sigfiles as one click email templates.
Glad you like it. You could also look into quick parts in Outlook. Really great and flexible for template responses. Here is some more information in case you're interested: th-cam.com/video/RSlfhjbIoK8/w-d-xo.html
You are ....brilliant! Thanks. :)
Great 👍.. thanks for this tips...🎉
Wow. Great timesavers
She’s awesome!
Amazing stuff! One question that came to mind for the PQ section. Can this not be done in pivot relatively quickly as well? Since pivot can group dates by month?
Leila madam always come with unique content appreciate your efforts love from India
Very powerful video 🙏🙏
That is amazing. Thanks for the share.
Gr8... learned a lot from you as always.. this is fav
You're the best.
In the power query example, you can also transform the date column directly to month instead of adding another column and deleting the date column (one step instead of two).
First of all I would like to thank you for teaching thousands of people through the channel. Can you let us know how to use an advanced filter in a shared Excel sheet.
Great vid, Out of insterest why use PQ to pivot data? instead of .....once the "Month Name" column is added in PQ just load the data straight to a pivot table and have the "Month Name" as the columns?
Genuine question and was wonder if a certain way would be better
Maybe my example was not the best. Pivot tables have their place but Power Query can do so much more. It can do data cleaning and the data can come from another file or another source.
Thank you!
I love these videos!
That was great, thanks a lot!
Name box bookmark is cool technique. 😊
One of the time saver tip on Auto Filter. Instead of using Data->Filter- select the value manually to get the output, simply place the cursor in the cell in which you want to filter, right click, filter by cell value. I am sure you can save time especially if you have huge data. Thanks
Hi, nice one. Few new things to learn. Thanks for sharing. Can you help me on one matter.
If we have purchase and sales quantities in 2 different excel sheets or even within same sheet, how can we calculate closing quantities of stock and their costs under FIFO using Formula, or pivot, Power query or even BI
Superb like always.
Is the power querry course chargeable?
As usual great stuff
Nice vacuum tubes old radio :) Greetings from Poland
I just lo lo love it ❤. Thanks a lot.
ممنون از آموزش های عالیتون
Fantastic ❤
That is great, thanks for sharing
Couldnt you use a pivot table to show monthly data in columns?
Hello Leila, do you have a plan to launch new Excel VBA or Excel Dashboard Course in Xelplus sooner or later?
First of all, I love your channel. I use both a Windows Laptop and a Mac and use Excel on both. However I predominantly use my Macbook. When you post your videos is it possible to mention upfront if the features mentioned can be accessed on a Mac also? Thanks.
Leila, can Power Query do the opposite process? Enlist columns in rows instead of bringing rows to columns?
2:12 And it is good to know that it is NOT case-sensitive!
👍
OMG, so very cool, Leila!
Thanks, Leila.. Trick 5- Why power query when you can do it with pivot in less than 10 clicks?
Originally I had some gaps in the data but I went over 10 clicks 😁 but yes a pivot would do fine here. The advantage with PQ as you for sure know is the cleaning we can do before and also that the data can come from another file or another source. At least in this example we don't have the restriction with the pivot table layout. That does annoy me sometimes🙂
Thanks teacher ❤
Hello Leila, does your cause has a time limit? Like must complete within some time spans? I want to learn but I’m extremely busy at the moment so if my purchase has a expiration date I’ll star later.
All of our courses come with lifetime access. You can learn at your own pace whenever it fits your schedule 😊
Thanks a lot!!!
Thank you so much for this helpful videos Mam.❤
Our pleasure!
Wow..
Awesome... 🙏🏽
Thank you Leila
Our pleasure!
I print payroll checks in excel. I am looking for a formula that will advance the check number after printing a check. Example, after printing payroll check number 2799 I need a formula to advance the check number to 2800. The cost is almost x10 for sequential numbered printed checks vs something from amazon that has no numbers.
Thanks Leila! But I want to know if I want to convert Columns into Rows. Can you help me?