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
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
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?
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
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!
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!
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.
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.
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.
Is there a way to filter for cell color across multiple columns? I know Advanced Filter lets you filter across multiple columns for values typed into cells, but cell color is not a value typed into the cell. 08/18/2023
Hi, When I open TSV file in excel it is coming as normal text and not like created spreadsheet. If I open the same TSV by Using Data > Get Data from Text / CSV it is opening in the proper way. Can you help me to open same the way as default.?
👉 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!
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.
Learned more in 15 minutes than a semester finance course :) Well done Kenji!!
hahahaha
request reimbursement from your university :-)
Thanks man
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.
I've used all the features in 1 2 4 8 & 9
Very useful video. Thanks a lot.
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.
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!
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 :)
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!
After watching this video Now i am working in Excel like a Pro ,,,, Thank U So Much
Became fan of u after watching this video. Such an amazing way to understand within minutes for the lifetime ..
thank you!
Excellent explanation and so easy to understand and follow ther Excel tips and tricks here, great job Kenji !!! THANKS A LOT !
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
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!!!
I knew 9 out of 10. However, I am very pleased to learn about flash fill. So thank you 🙂
Your videos are really good!! best ones so far I have seen. Grat format of presentation and tone of voice! =) Thank you
Sparklines! I always thought it was just a line graph! This is so useful!
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
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! Do you also have a video where you explain the INDIRECT function in combination with het MATCH function?
All I can say is a heartfelt thank you. I wish I could pay in INR for your courses.
Got 10/10 , Very Helpful video tho , Thanks for the amazing content
Damn you’re good! Thanks man 👍
Insightful. thank you Kenji for providing us with best possible excel content.
Thank you for the nice comment! 🙌
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?
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!
Thank you for all of this, it was helpful
Can you do free course of excel in this great channel?
9:11
11:01
12:17 Ag
the tone and control of ur voice says "I know my sNice tutorialt!". Good stuff buddy.
This was sooooooooo useful, pretty, elegant & smart. Thank you so much
Thanks for the awesome comment!
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!!
Level 4 Awesome. Great stuff😁😁😁😁
Perfect way of teaching
I finally know how to do macros... amazing!
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 :)
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!
11:56 I would use Power Query - and split column by delimiter.
It's the best thing i've ever seen!
Thank you so much - Very useful tips
Amazing excel trick taught by you thank you so much
It's worth subscribing to your channel. I learn a lot... 🙏
Welcome aboard!
This really help me a lot, since I don't have a computer subject.
Awesome to hear!
Just blown away.. 🔥❤️
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 :)
Super nice Kenji, thanks!
Cheers!
Didn't realize macros were a thing. Seems quite easy as well thank you sir. Got 7/10
Yes sir!
10/10, great video!:)
Great tips, learnt many new things
Well explained. Keep it up.
Trend lines 👍🏻👍🏻👍🏻👍🏻👍🏻 thank you
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.
This is amazing even as a person that is knowledgeable in finance this was so helpful
Thank you so much. Amazing.❤
Very helpful. Thanks
Very helpful. Thanks man, looking forward for more tutorials.
Thank you! More to come :)
As a Finance student i knew 7/10. I do however consider them as the more simple features that we’ve been taught.
What version of Excel do we need to access the "Data Types" feature? Thanks.
Have to include the camera add on surely, great for model work
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
Great excel tips, thanks for sharing.
Glad you enjoyed it!
Amazing knowledge and skills
Great tutorials.
Your video boosted my confidence. Never knew I am an expert 😂
Thanks for the tips!
Thanks for watching!
Brilliant Thank you
Really helpful, appreciate it!! 👍
Glad it helped!
10/10… But Great Refreshing Video
Glad you enjoyed it!
Love the level rankings in this video!
Cheers Michael!
Hi kenji thank you so much for the video, but can you tell that how can we enable data type under the data tab as i don't have it.
I’ve heard if you don’t have Microsoft 365 it may not be available. Is that your case?
@@KenjiExplains yes I have Microsoft 2019 version
I've watched of your videos and liked a lot. good job doing it.
Glad you like them!
Mr.Kenji let me know how can we freeze last row in excel....
Thank you this was great!
Glad it was helpful!
Very insightful. I love your videos. Thanks for th great content.
I appreciate that!
in 11:11 what is ctrl+E in mac? please help
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.
9/10. Thanks any ways. Cool to learn about Macro today.
Awesome thank you for the comment!
Great tips my friend! Keep going!
Thanks so much!
learning a lot from your videos!
Awesome, thank you!
Great video, always appreciate your content!
Thank you Emile
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 :)
Is the stock feature still available in excel?
10/10. Nice video
Damn nice!
Loved it
I know 7 out of like 9 questions, thanks for your excel tips and tricks
Nice content as always. 👍🏻
Glad you think so!
i am unable to download the excel sheet from any one of your videos from the link provided, is anyone else having the same problem?
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!
Nice one.Appreciatee
Is there a way to filter for cell color across multiple columns? I know Advanced Filter lets you filter across multiple columns for values typed into cells, but cell color is not a value typed into the cell. 08/18/2023
In my excel stock function is not there under data tab .
Can you help me in that?
Hi,
When I open TSV file in excel it is coming as normal text and not like created spreadsheet. If I open the same TSV by Using Data > Get Data from Text / CSV it is opening in the proper way. Can you help me to open same the way as default.?
Could you upload a video just about macros/VBA? would be great :)!
Noted! thanks for the suggestion
bro what type excel do you use?
so useful for me
These all were in dictionary way back. 10/10 easy peasy :P
I knew all of them, was expecting more advance things, sir.