Top 10 Excel Tricks ONLY Experts Know

Поділитися
Вставка
  • Опубліковано 23 гру 2024

КОМЕНТАРІ • 208

  • @KenjiExplains
    @KenjiExplains  2 роки тому +17

    👉 Learn Excel for Business & Finance: www.careerprinciples.com/courses/excel-for-business-finance

    • @lihelen8982
      @lihelen8982 Рік тому

      Hello, Are the online courses live teaching? Or jus courses we go through on our own?

  • @stephentodd4093
    @stephentodd4093 2 роки тому +50

    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!

  • @drewwalters7392
    @drewwalters7392 2 роки тому +31

    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.

  • @MarvelTanudjaja
    @MarvelTanudjaja 2 роки тому +61

    Learned more in 15 minutes than a semester finance course :) Well done Kenji!!

  • @improvemyself256
    @improvemyself256 2 роки тому +7

    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.

  • @imamhossain5954
    @imamhossain5954 Рік тому +2

    I've used all the features in 1 2 4 8 & 9
    Very useful video. Thanks a lot.

  • @hotflashfoto
    @hotflashfoto 2 роки тому +4

    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.

  • @VickiMcCorkle
    @VickiMcCorkle 10 місяців тому +1

    You by far have the best tutorial videos. Thank you for providing so many helpful videos.

  • @Cfckkr
    @Cfckkr 2 роки тому +8

    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!

    • @KenjiExplains
      @KenjiExplains  2 роки тому +1

      thanks for the suggestion will keep it in mind!

  • @Micfri123
    @Micfri123 2 роки тому +2

    I work in finance and am always trying to learn more excel tips- big thanks as usual. I knew about half of these.

  • @easwarshankaran4426
    @easwarshankaran4426 2 роки тому +1

    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 :)

  • @gabgab7935
    @gabgab7935 2 роки тому +1

    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

  • @TheStoicWay110
    @TheStoicWay110 4 місяці тому

    After watching this video Now i am working in Excel like a Pro ,,,, Thank U So Much

  • @shankarnanu1323
    @shankarnanu1323 2 роки тому

    Became fan of u after watching this video. Such an amazing way to understand within minutes for the lifetime ..

  • @SabesanVaidhyanathan
    @SabesanVaidhyanathan 8 місяців тому

    Excellent explanation and so easy to understand and follow ther Excel tips and tricks here, great job Kenji !!! THANKS A LOT !

  • @5uysal
    @5uysal 9 місяців тому

    You have such great way of explaining things! Love these short videos thanks!

  • @MrRvds
    @MrRvds 2 роки тому

    Data Validation and CTRL + E were the new ones for me.
    Thank you

  • @nonoobott8602
    @nonoobott8602 2 роки тому +1

    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

  • @Kazaaaaaak
    @Kazaaaaaak 2 роки тому +1

    I knew 9 out of 10. However, I am very pleased to learn about flash fill. So thank you 🙂

  • @talitacorte2268
    @talitacorte2268 Рік тому

    Your videos are really good!! best ones so far I have seen. Grat format of presentation and tone of voice! =) Thank you

  • @lilgirllilgirl735
    @lilgirllilgirl735 2 роки тому

    Sparklines! I always thought it was just a line graph! This is so useful!

  • @RA-rh5lb
    @RA-rh5lb 2 роки тому

    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

  • @MrSilenxe
    @MrSilenxe 2 роки тому

    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.

  • @davekorver9420
    @davekorver9420 Рік тому

    Great! Do you also have a video where you explain the INDIRECT function in combination with het MATCH function?

  • @sachitbhatt2727
    @sachitbhatt2727 Рік тому

    All I can say is a heartfelt thank you. I wish I could pay in INR for your courses.

  • @ahmedosama5205
    @ahmedosama5205 2 роки тому +3

    Got 10/10 , Very Helpful video tho , Thanks for the amazing content

  • @harshilnagda7321
    @harshilnagda7321 2 роки тому +1

    Insightful. thank you Kenji for providing us with best possible excel content.

    • @KenjiExplains
      @KenjiExplains  2 роки тому +1

      Thank you for the nice comment! 🙌

  • @JPagan3618
    @JPagan3618 Рік тому

    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?

  • @saeer5038
    @saeer5038 2 роки тому

    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

  • @camlex6310
    @camlex6310 2 роки тому +1

    Got 8/10...learning a lot from your videos!

  • @mty50m1
    @mty50m1 2 роки тому

    Thank you for all of this, it was helpful
    Can you do free course of excel in this great channel?

  • @Letslearntogetheruzh7
    @Letslearntogetheruzh7 4 місяці тому +1

    9:11
    11:01
    12:17 Ag

  • @alicenexon1381
    @alicenexon1381 2 роки тому

    the tone and control of ur voice says "I know my sNice tutorialt!". Good stuff buddy.

  • @mohdward9505
    @mohdward9505 2 роки тому

    This was sooooooooo useful, pretty, elegant & smart. Thank you so much

  • @lolliMAN101
    @lolliMAN101 2 роки тому

    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!

  • @thulaniralo9297
    @thulaniralo9297 2 роки тому

    Level 4 Awesome. Great stuff😁😁😁😁

  • @shahidimran5016
    @shahidimran5016 7 місяців тому

    Perfect way of teaching

  • @lucholeonguerrero
    @lucholeonguerrero Рік тому

    I finally know how to do macros... amazing!

  • @danielsmart2464
    @danielsmart2464 2 роки тому

    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

    • @KenjiExplains
      @KenjiExplains  2 роки тому

      Hey Daniel thank you for the support, especially in the early days means a lot!!! Take care :)

  • @MrBrooksV
    @MrBrooksV 2 роки тому +3

    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!

  • @JJ_TheGreat
    @JJ_TheGreat 2 роки тому

    11:56 I would use Power Query - and split column by delimiter.

  • @allsaka
    @allsaka 2 роки тому

    It's the best thing i've ever seen!

  • @positive-affirmation-4-suc727
    @positive-affirmation-4-suc727 2 роки тому

    Thank you so much - Very useful tips

  • @VERTICALcube
    @VERTICALcube 2 роки тому

    It's worth subscribing to your channel. I learn a lot... 🙏

  • @saurabhshukla2622
    @saurabhshukla2622 Рік тому

    Amazing excel trick taught by you thank you so much

  • @chrisalynbajaro8346
    @chrisalynbajaro8346 2 роки тому

    This really help me a lot, since I don't have a computer subject.

  • @varunmittal5196
    @varunmittal5196 2 роки тому

    Just blown away.. 🔥❤️

  • @emadr5780
    @emadr5780 2 роки тому

    Thank you very much. Could you please make a video about industry research and how we should do it in excel?

    • @KenjiExplains
      @KenjiExplains  2 роки тому +1

      Thanks for the suggestion I'll note it!

    • @emadr5780
      @emadr5780 2 роки тому

      @@KenjiExplains Realmente lo aprecio :)

  • @andymarci6766
    @andymarci6766 2 роки тому

    Super nice Kenji, thanks!

  • @willzinner8813
    @willzinner8813 2 роки тому +6

    Didn't realize macros were a thing. Seems quite easy as well thank you sir. Got 7/10

  • @kristynakrunclova5684
    @kristynakrunclova5684 Рік тому

    10/10, great video!:)

  • @bobtucker9082
    @bobtucker9082 2 роки тому

    Great tips, learnt many new things

  • @HealthSmarter
    @HealthSmarter 4 місяці тому

    Well explained. Keep it up.

  • @donnaassenza3025
    @donnaassenza3025 2 роки тому

    Trend lines 👍🏻👍🏻👍🏻👍🏻👍🏻 thank you

  • @baqarhasan3495
    @baqarhasan3495 2 роки тому

    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?

    • @resresres1
      @resresres1 2 роки тому

      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.

  • @JeremiahCarter
    @JeremiahCarter 2 роки тому

    This is amazing even as a person that is knowledgeable in finance this was so helpful

  • @flourishwithMJ
    @flourishwithMJ Рік тому

    Thank you so much. Amazing.❤

  • @shenay9377
    @shenay9377 2 роки тому

    Very helpful. Thanks

  • @markyzentana7540
    @markyzentana7540 2 роки тому

    Very helpful. Thanks man, looking forward for more tutorials.

  • @ThatdudeJaden
    @ThatdudeJaden 2 роки тому

    As a Finance student i knew 7/10. I do however consider them as the more simple features that we’ve been taught.

  • @JJ_TheGreat
    @JJ_TheGreat 2 роки тому

    What version of Excel do we need to access the "Data Types" feature? Thanks.

  • @mkg1253
    @mkg1253 2 роки тому

    Have to include the camera add on surely, great for model work

  • @BostonRoger
    @BostonRoger 2 роки тому +1

    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.

    • @KenjiExplains
      @KenjiExplains  2 роки тому

      Intersting! Thanks for letting me know

  • @abdulrahmankhan5139
    @abdulrahmankhan5139 2 роки тому

    Great excel tips, thanks for sharing.

  • @MuslimIslam01
    @MuslimIslam01 2 роки тому

    Amazing knowledge and skills

  • @VickiMcCorkle
    @VickiMcCorkle 10 місяців тому

    Great tutorials.

  • @NisfiStatic
    @NisfiStatic 2 роки тому

    Your video boosted my confidence. Never knew I am an expert 😂

  • @bingchilling4573
    @bingchilling4573 2 роки тому

    Thanks for the tips!

  • @shandhirani7559
    @shandhirani7559 2 роки тому

    Brilliant Thank you

  • @manuelvega-arango599
    @manuelvega-arango599 2 роки тому

    Really helpful, appreciate it!! 👍

  • @michaelquach7046
    @michaelquach7046 2 роки тому

    Love the level rankings in this video!

  • @skoombayil
    @skoombayil 2 роки тому

    10/10… But Great Refreshing Video

  • @manangoyal3896
    @manangoyal3896 2 роки тому

    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.

    • @KenjiExplains
      @KenjiExplains  2 роки тому

      I’ve heard if you don’t have Microsoft 365 it may not be available. Is that your case?

    • @manangoyal3896
      @manangoyal3896 2 роки тому

      @@KenjiExplains yes I have Microsoft 2019 version

  • @luizcristianofs
    @luizcristianofs 2 роки тому

    I've watched of your videos and liked a lot. good job doing it.

  • @lesliem6208
    @lesliem6208 2 роки тому

    Thank you this was great!

  • @harigorantla3985
    @harigorantla3985 2 роки тому +1

    Mr.Kenji let me know how can we freeze last row in excel....

  • @jeandonaldgeorges7783
    @jeandonaldgeorges7783 2 роки тому

    Very insightful. I love your videos. Thanks for th great content.

  • @pink_aura
    @pink_aura Рік тому

    in 11:11 what is ctrl+E in mac? please help

  • @Metalblowing
    @Metalblowing 2 роки тому

    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.

  • @ahmednooor
    @ahmednooor 2 роки тому

    9/10. Thanks any ways. Cool to learn about Macro today.

  • @andreugiro2807
    @andreugiro2807 2 роки тому

    Great tips my friend! Keep going!

  • @goaboi
    @goaboi 2 роки тому

    learning a lot from your videos!

  • @emilehouvenaghel5299
    @emilehouvenaghel5299 2 роки тому

    Great video, always appreciate your content!

  • @aznty5597
    @aznty5597 2 роки тому

    10 :) Loved the video and how it's broken down into levels. Keep up the great work Kenji!

    • @KenjiExplains
      @KenjiExplains  2 роки тому

      Thank you! More to come, great excel skills you have though

    • @aznty5597
      @aznty5597 2 роки тому

      @@KenjiExplains honestly have been learning so much from your content. Keep it up! One of the if not the best channel out there :)

  • @Chanda527-zn7pf
    @Chanda527-zn7pf 4 місяці тому

    Is the stock feature still available in excel?

  • @canoeron
    @canoeron 2 роки тому

    10/10. Nice video

  • @vickyng1356
    @vickyng1356 2 роки тому

    Loved it

  • @Biniyam3832
    @Biniyam3832 2 роки тому

    I know 7 out of like 9 questions, thanks for your excel tips and tricks

  • @reelzerotuned3675
    @reelzerotuned3675 2 роки тому

    Nice content as always. 👍🏻

  • @srikanththogarchedu
    @srikanththogarchedu 2 роки тому

    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?

  • @shydidja69
    @shydidja69 2 роки тому

    7/10.I definitely need to learn macro, every single time I delay my learning process 🤦🏾‍♀️

    • @aznty5597
      @aznty5597 2 роки тому

      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

    • @KenjiExplains
      @KenjiExplains  2 роки тому

      Yupp very useful!

  • @YogeshSharma-ui7xx
    @YogeshSharma-ui7xx 8 місяців тому

    Nice one.Appreciatee

  • @an2303
    @an2303 Рік тому

    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

  • @shreyanshpangariya978
    @shreyanshpangariya978 2 роки тому

    In my excel stock function is not there under data tab .
    Can you help me in that?

  • @sreejiths8373
    @sreejiths8373 2 роки тому

    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.?

  • @ericbayer1001
    @ericbayer1001 2 роки тому

    Could you upload a video just about macros/VBA? would be great :)!

  • @sudaiskhadim-n1y
    @sudaiskhadim-n1y Рік тому

    bro what type excel do you use?

  • @huytran6605
    @huytran6605 5 місяців тому

    so useful for me

  • @vatsal715
    @vatsal715 2 роки тому

    These all were in dictionary way back. 10/10 easy peasy :P

  • @rishavtoshniwal6248
    @rishavtoshniwal6248 6 місяців тому

    I knew all of them, was expecting more advance things, sir.