The split to freeze panes is much harder than the easy way. Just choose the cell below the row and to the right of the column you want to freeze. Select freeze panes and you are good to go!
Macro Tip - When beginning to record a macro, after selecting the short cut key for the macro, select the drop down option to save the macro in the personal macro workbook. This will allow users to use the saved macro in the future without having to open the original workbook the macro was created it every time. Instead a blank workbook containing the saved macro will open in the background and barely noticeable.
Hey Kenji, this tutorial was great! The record macros tool can also help you learn VBA: After you recorded your macro, go to visual basic (next to macros). There, open the module for your worksheet and look at the code. You can then delete parts of the code and re-run it on an unformatted table and see what changes. That way you know exactly what part of the code does what.
Overall, this was a good video. Freeze Panes: What you did was the long, hard way. Just click on the first row of data in column A, then click Freeze Panes. Done. The Split function does not need to be use, just takes more time, and adds complexity to the ultimate goal of freezing the header. Grouping: Nice that you included the keyboard method to select an entire row! After Grouping the 3 rows, select the next 3 with the mouse and press F4 to repeat the last command. In this case, it will be Grouping. You can continue doing that down the page so long as you don't change the command by selecting the rows with the keyboard. It works for most commands repeatedly, such as Paste Formatting or Paste Values, but works only once for a full, regular Paste. Be careful, though. It works for Delete and a lot of other commands as well. If you experiment a bit, you'll also find that you can compound the edits that you wish to repeat by using the Format Cells (Ctrl + 1), click on the various sub-tabs to modify the selected cells, and then click OK. When you press F4 after that, all of those format changes will take place. When recording a macro, 2 things that the user might want to do: 1) Place the cursor in cell A1 so it's not in the middle of the sheet. On very long sheets of data, leaving it at the bottom will cause the report to open at the bottom, forcing a manager to actually have to scroll to the top. And we all know how managers want to be spoon-fed and have everything all nice and pretty, right? 2) Learn a little bit of coding etiquette and go trim all the "fat" that Excel records so it does not include all the clicking and selecting of cells that the human has to do. The computer doesn't need to select the cells, since it can modify the contents of any cell just by saying, "Cells(3,5) = 12" or "Range("E3") = 12", and that will put a value of 12 into cell E3 (row 3, column 5). You can apply background colors, font colors, borders, formulas, and everything else, and then the display is not "blinking" all over the place while the macro is running. You don't want the Admin to go into an epileptic seizure, now, do ya? ;>\ By extension, you can directly modify cells on any tab (sheet) without having to go to that tab. The human has to go there, but the computer does not. VBA coding is really fun when you do it.
Hello sir am from India and pursuing Chartered Accountancy, your videos are extremely helpful to prepare financials and workings in a professional manner thanks a lot :)
surprisingly 10/10 however i did not master everything BEFORE but this video refreshes my idea about these tricks. Great job Kenji, such a big help!! Thanks a lot
Very explicit video, so easy to follow. I definitely have to check out your Business and Finance modeling course. For me it was 9/10. Thanks for sharing
Amazing video, many things that you always need, but no one tells you about! What you showed with macros tho is just a tip of iceberg, very useful one, but just a tip. So I would put macros in 5 star plus category as they can get a lot of stuff done for you
Hi Kenji, thanks for the upload. all your videos are truly good. I would like to learn better way because most of the time i forget what i have learned from youtube( not all but yes at the right time i don't remember what I learned.) how much do you charge for the learn excel for business and finance course
Macros are extremely helpful - here’s another example of how it can be used: At my previous company we used an excel file to record our contractor hours for our 3-way match hour recording. We had a macro created to automatically update the work week day cells and an actual button you could click to trigger all of the previous week data to clear out. There was also a button to trigger the data to be emailed out to the contact at the contractors company, as well as our accounting team. It was extremely convenient. Macros are great tools to master!
Thanks for all the lessons you provide. Im prepareing for interviews in finance and your videos are very helpful. I knew most of the 4 and 5 stars tips but all of them were very helpful. I was wondering if you can do conditional formating with the sparklines in order to change their color depending on if the trend is upward or downward. I cant check it myself because im using excel web and it doesnt support sparklines.
Great tutorial. Thank you. I always have issues with the Sparklines as it's always greyed out on the sheet I need it at, but I can access it on a new sheet. Does anyone have a tip?
I’m using excel for financial purposes too (macro- and microeconomic analyses) but I didn’t know about grouping, the rest is all very well known to me! Appreciate your videos!
I'm not sure how I got this far in my career but I knew all the 2-star and above, but I didn't know how to group cells and that you can set where to freeze. So the two 1-star items helped me the most but I knew how to do the rest.
*When it comes to investing, I'm so ignorant about it, I hereby ask; What's your say or thought for anyone with over $20k looking for the best ways to make good returns off it? I will appreciate any help here on how to do this*
@Lucy Bernardi Interesting, I've always been fascinated with investing and I need to start now. Could you possibly give more information on your Investment Professional and how I can reach out to her?
Great vid man. I'm always joking with my friends that Excel can do it all. There was no point to develop any other type of software :D We just create a bunch of tools that do separate Excel functions ha-ha.
Hi there, is it possible to make a macro dynamic? For example I have weekly sales and stock which I extract in an excel sheet on a weekly basis and calculate weekly sell thru. I want to be able to do that next week for this week's data; next week. How do I record this?
don't think you need a macro for this, just use excel formulas in cell(s) to do the calculations. However, If you are extracting a new excel file every time, you can just have a master excel file that has all your formulas on sheet 1 and make a sheet 2 with your new extracted data. Personally, i've moved away from doing this kind of stuff in excel and create my own custom python programs with GUI's to extract data from excels and display results.
Kenji, I have followed many of your videos and fairly found quite useful. You are awesome, but unfortunately still I'm unable to find one path to find my real time experience on different sales %scenarios. I have checked your many videos but didn't find an easy way except sumifs, my manager has given 8scenarios on sales or revenue to offer best rebate to customers. Can you help me by giving any tip which formula can be easy. Many thanks. Krishna
Hey Krishna, it's difficult to help as I dont have much context but have you looked into the CHOOSE formula? Perhaps the XLOOKUP or INDEXMATCH could work as well. Sorry I cant be of much help as I don't really understand the question you have
![Stromae, Pomme - “Ma Meilleure Ennemie” (from Arcane Season 2) [Official Visualizer]](http://i.ytimg.com/vi/1F3OGIFnW1k/mqdefault.jpg)
👉 Learn Excel for Business & Finance: www.careerprinciples.com/courses/excel-for-business-finance
Hello, Are the online courses live teaching? Or jus courses we go through on our own?
The split to freeze panes is much harder than the easy way. Just choose the cell below the row and to the right of the column you want to freeze. Select freeze panes and you are good to go!
Learned more in 15 minutes than a semester finance course :) Well done Kenji!!
hahahaha
request reimbursement from your university :-)
Thanks man
Macro Tip - When beginning to record a macro, after selecting the short cut key for the macro, select the drop down option to save the macro in the personal macro workbook. This will allow users to use the saved macro in the future without having to open the original workbook the macro was created it every time. Instead a blank workbook containing the saved macro will open in the background and barely noticeable.
I've used all the features in 1 2 4 8 & 9
Very useful video. Thanks a lot.
Hey Kenji, this tutorial was great!
The record macros tool can also help you learn VBA: After you recorded your macro, go to visual basic (next to macros). There, open the module for your worksheet and look at the code. You can then delete parts of the code and re-run it on an unformatted table and see what changes. That way you know exactly what part of the code does what.
Overall, this was a good video.
Freeze Panes: What you did was the long, hard way. Just click on the first row of data in column A, then click Freeze Panes. Done. The Split function does not need to be use, just takes more time, and adds complexity to the ultimate goal of freezing the header.
Grouping: Nice that you included the keyboard method to select an entire row! After Grouping the 3 rows, select the next 3 with the mouse and press F4 to repeat the last command. In this case, it will be Grouping. You can continue doing that down the page so long as you don't change the command by selecting the rows with the keyboard. It works for most commands repeatedly, such as Paste Formatting or Paste Values, but works only once for a full, regular Paste. Be careful, though. It works for Delete and a lot of other commands as well. If you experiment a bit, you'll also find that you can compound the edits that you wish to repeat by using the Format Cells (Ctrl + 1), click on the various sub-tabs to modify the selected cells, and then click OK. When you press F4 after that, all of those format changes will take place.
When recording a macro, 2 things that the user might want to do:
1) Place the cursor in cell A1 so it's not in the middle of the sheet. On very long sheets of data, leaving it at the bottom will cause the report to open at the bottom, forcing a manager to actually have to scroll to the top. And we all know how managers want to be spoon-fed and have everything all nice and pretty, right?
2) Learn a little bit of coding etiquette and go trim all the "fat" that Excel records so it does not include all the clicking and selecting of cells that the human has to do. The computer doesn't need to select the cells, since it can modify the contents of any cell just by saying, "Cells(3,5) = 12" or "Range("E3") = 12", and that will put a value of 12 into cell E3 (row 3, column 5). You can apply background colors, font colors, borders, formulas, and everything else, and then the display is not "blinking" all over the place while the macro is running. You don't want the Admin to go into an epileptic seizure, now, do ya? ;>\
By extension, you can directly modify cells on any tab (sheet) without having to go to that tab. The human has to go there, but the computer does not. VBA coding is really fun when you do it.
Thanks for your input
You by far have the best tutorial videos. Thank you for providing so many helpful videos.
After watching this video Now i am working in Excel like a Pro ,,,, Thank U So Much
I work in finance and am always trying to learn more excel tips- big thanks as usual. I knew about half of these.
Great to hear!
Hello sir am from India and pursuing Chartered Accountancy, your videos are extremely helpful to prepare financials and workings in a professional manner thanks a lot :)
Kenji, appreciate if you could do a video on discount rate & risk free rate, how are they determined. Learned a lot from your videos. Keep rocking!
thanks for the suggestion will keep it in mind!
Became fan of u after watching this video. Such an amazing way to understand within minutes for the lifetime ..
thank you!
All I can say is a heartfelt thank you. I wish I could pay in INR for your courses.
surprisingly 10/10 however i did not master everything BEFORE but this video refreshes my idea about these tricks. Great job Kenji, such a big help!! Thanks a lot
Thank you!
You have such great way of explaining things! Love these short videos thanks!
Data Validation and CTRL + E were the new ones for me.
Thank you
I knew 9 out of 10. However, I am very pleased to learn about flash fill. So thank you 🙂
9:11
11:01
12:17 Ag
Excellent explanation and so easy to understand and follow ther Excel tips and tricks here, great job Kenji !!! THANKS A LOT !
Sparklines! I always thought it was just a line graph! This is so useful!
the tone and control of ur voice says "I know my sNice tutorialt!". Good stuff buddy.
Very explicit video, so easy to follow. I definitely have to check out your Business and Finance modeling course. For me it was 9/10. Thanks for sharing
Thank you!!!
Got 10/10 , Very Helpful video tho , Thanks for the amazing content
Damn you’re good! Thanks man 👍
I finally know how to do macros... amazing!
This really help me a lot, since I don't have a computer subject.
Awesome to hear!
Thank you for all of this, it was helpful
Can you do free course of excel in this great channel?
Amazing video, many things that you always need, but no one tells you about! What you showed with macros tho is just a tip of iceberg, very useful one, but just a tip. So I would put macros in 5 star plus category as they can get a lot of stuff done for you
Got 8/10...learning a lot from your videos!
Wow you’re good!
Hi Kenji, thanks for the upload. all your videos are truly good. I would like to learn better way because most of the time i forget what i have learned from youtube( not all but yes at the right time i don't remember what I learned.)
how much do you charge for the learn excel for business and finance course
Macros are extremely helpful - here’s another example of how it can be used:
At my previous company we used an excel file to record our contractor hours for our 3-way match hour recording. We had a macro created to automatically update the work week day cells and an actual button you could click to trigger all of the previous week data to clear out. There was also a button to trigger the data to be emailed out to the contact at the contractors company, as well as our accounting team. It was extremely convenient. Macros are great tools to master!
Thanks for the suggestion!
Thanks for all the lessons you provide. Im prepareing for interviews in finance and your videos are very helpful. I knew most of the 4 and 5 stars tips but all of them were very helpful. I was wondering if you can do conditional formating with the sparklines in order to change their color depending on if the trend is upward or downward. I cant check it myself because im using excel web and it doesnt support sparklines.
Perfect way of teaching
Insightful. thank you Kenji for providing us with best possible excel content.
Thank you for the nice comment! 🙌
Your videos are really good!! best ones so far I have seen. Grat format of presentation and tone of voice! =) Thank you
This is amazing even as a person that is knowledgeable in finance this was so helpful
Great tutorial. Thank you. I always have issues with the Sparklines as it's always greyed out on the sheet I need it at, but I can access it on a new sheet. Does anyone have a tip?
Didn't realize macros were a thing. Seems quite easy as well thank you sir. Got 7/10
Yes sir!
Great! Do you also have a video where you explain the INDIRECT function in combination with het MATCH function?
Your video boosted my confidence. Never knew I am an expert 😂
I’m using excel for financial purposes too (macro- and microeconomic analyses) but I didn’t know about grouping, the rest is all very well known to me!
Appreciate your videos!
Thank you for watching them!!
This was sooooooooo useful, pretty, elegant & smart. Thank you so much
Thanks for the awesome comment!
Amazing excel trick taught by you thank you so much
Level 4 Awesome. Great stuff😁😁😁😁
Have to include the camera add on surely, great for model work
Wow, I remember subbing to you a couple months ago when you only had a few hundred subs. Glad to see you're doing well
Hey Daniel thank you for the support, especially in the early days means a lot!!! Take care :)
I knew all of them, was expecting more advance things, sir.
Just blown away.. 🔥❤️
Thank you so much - Very useful tips
I'm not sure how I got this far in my career but I knew all the 2-star and above, but I didn't know how to group cells and that you can set where to freeze. So the two 1-star items helped me the most but I knew how to do the rest.
Intersting! Thanks for letting me know
Well explained. Keep it up.
As a Finance student i knew 7/10. I do however consider them as the more simple features that we’ve been taught.
Thank you very much. Could you please make a video about industry research and how we should do it in excel?
Thanks for the suggestion I'll note it!
@@KenjiExplains Realmente lo aprecio :)
It's the best thing i've ever seen!
Love the level rankings in this video!
Cheers Michael!
It's worth subscribing to your channel. I learn a lot... 🙏
Welcome aboard!
Great tips, learnt many new things
7/10.I definitely need to learn macro, every single time I delay my learning process 🤦🏾♀️
Don't delay it :) it's super useful and literally would take a few hours to get it right. Best of luck! You can do it
Yupp very useful!
Very helpful. Thanks man, looking forward for more tutorials.
Thank you! More to come :)
10/10… But Great Refreshing Video
Glad you enjoyed it!
Amazing knowledge and skills
I know 7 out of like 9 questions, thanks for your excel tips and tricks
Thank you so much. Amazing.❤
Great tutorials.
Level 6: Finding out about Power Query felt like I blew open Excel's 4th wall and spilled out into a new world of potential
Trend lines 👍🏻👍🏻👍🏻👍🏻👍🏻 thank you
Mr.Kenji let me know how can we freeze last row in excel....
Very helpful. Thanks
Know what Excell is about, used it for some project years ago and I was amazed by what it can do. Right now my Excell "skill" is below sea level 😅
Brilliant Thank you
*When it comes to investing, I'm so ignorant about it, I hereby ask; What's your say or thought for anyone with over $20k looking for the best ways to make good returns off it? I will appreciate any help here on how to do this*
@Lucy Bernardi Interesting, I've always been fascinated with investing and I need to start now. Could you possibly give more information on your Investment Professional and how I can reach out to her?
Super nice Kenji, thanks!
Cheers!
Great excel tips, thanks for sharing.
Glad you enjoyed it!
Very insightful. I love your videos. Thanks for th great content.
I appreciate that!
10/10, great video!:)
Thanks for the tips!
Thanks for watching!
Great video, always appreciate your content!
Thank you Emile
learning a lot from your videos!
Awesome, thank you!
If you are simply recording a macro, use the status bar and not the developer tab.
9/10. Thanks any ways. Cool to learn about Macro today.
Awesome thank you for the comment!
Really helpful, appreciate it!! 👍
Glad it helped!
Great tips my friend! Keep going!
Thanks so much!
Great vid man.
I'm always joking with my friends that Excel can do it all. There was no point to develop any other type of software :D We just create a bunch of tools that do separate Excel functions ha-ha.
Thank you this was great!
Glad it was helpful!
These all were in dictionary way back. 10/10 easy peasy :P
10 :) Loved the video and how it's broken down into levels. Keep up the great work Kenji!
Thank you! More to come, great excel skills you have though
@@KenjiExplains honestly have been learning so much from your content. Keep it up! One of the if not the best channel out there :)
Hi there, is it possible to make a macro dynamic? For example I have weekly sales and stock which I extract in an excel sheet on a weekly basis and calculate weekly sell thru. I want to be able to do that next week for this week's data; next week. How do I record this?
don't think you need a macro for this, just use excel formulas in cell(s) to do the calculations. However, If you are extracting a new excel file every time, you can just have a master excel file that has all your formulas on sheet 1 and make a sheet 2 with your new extracted data. Personally, i've moved away from doing this kind of stuff in excel and create my own custom python programs with GUI's to extract data from excels and display results.
so useful for me
What version of Excel do we need to access the "Data Types" feature? Thanks.
Nice one.Appreciatee
Could you upload a video just about macros/VBA? would be great :)!
Noted! thanks for the suggestion
11:56 I would use Power Query - and split column by delimiter.
Loved it
Only the last one with MAcro I learnt so I'm happy to know I'm not too bad but to be honest it's because I follow your trick posts a lot ;)
Great to hear!
Level 3 was new to me
I''ve used all the features in 1 2 4 & 5
Cool video 👍
I would say all those would be at most a 3. 4/5 would be better suited for power query, m functions, dax formulas, and power pivot.
I've watched of your videos and liked a lot. good job doing it.
Glad you like them!
Is the stock feature still available in excel?
10/10. Nice video
Damn nice!
Kenji, I have followed many of your videos and fairly found quite useful. You are awesome, but unfortunately still I'm unable to find one path to find my real time experience on different sales %scenarios. I have checked your many videos but didn't find an easy way except sumifs, my manager has given 8scenarios on sales or revenue to offer best rebate to customers. Can you help me by giving any tip which formula can be easy. Many thanks.
Krishna
Hey Krishna, it's difficult to help as I dont have much context but have you looked into the CHOOSE formula? Perhaps the XLOOKUP or INDEXMATCH could work as well. Sorry I cant be of much help as I don't really understand the question you have
@@KenjiExplains Thanks Kenj, do you mind to provide me any official contact so I reach you with proper scenario. Thanks for that. Krishna