+ Reply to Thread
Results 1 to 171 of 171

A Whole Bunch of Tips & Tutorials I've Learned

  1. #1
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    A Whole Bunch of Tips & Tutorials I've Learned

    Hey everyone, my name's benishiryo & i am an Excel addict... it's almost 2 years i've been in this forum & although I've only been helping only since about Apr 2012, i've learned more than i can ever imagined. So i'm sharing all (well, most. ok maybe just stuff i bothered to note down when i'm not being lazy) the stuffs i've learned in this 1 year. it may be an overkill for some to have so many things inside though.

    a big thank you to all OPs i've helped because cracking my brains for you guys made me learn things through it. and definitely mostly due to contributors here i've learned from. i'm soooo much better than i was a year ago & i'm not even close to being awesome yet.

    credited some of the tips to the people who provided the solutions, but this started later when i thought it would be more appropriate when sharing. So i'm sorry if some of the solutions were from you & i missed your name. If this file proves useful or if this thread becomes "stickied", i will then keep updating it.
    =)

    Edit: Will constantly be updating the opening post (so no notification if you have subscribed to this!) instead of a new post whenever I have updates, so keep a lookout for the date in the file name if you guys are interested. Explanations written inside the file are done by me, with the exception of 1 case where I asked Ron Coderre about a portion of his amazing formula & he helped explain. i hope the new layout is easier for users to find what they need. i guess i need to categorize them but it's kinda tough. just keep a lookout for this on a monthly basis. i'll try to do updates
    Attached Files Attached Files
    Last edited by Fotis1991; 05-05-2015 at 01:04 PM. Reason: Updated file.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Nice work Ben!
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Nice work. I will sticky this thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Fotis:
    thanks!

    @arlu:
    ahhh just like that eh? i thought it has to be at least useful. thank you too!

    and thanks for those who repped.
    =)

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I did find it useful. Maybe as you keep adding in stuff, it will make it more useful and can remain as a sticky.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Not everyone will have this mentality of sharing their collections/methods which they learned/found/arrived over several years of research to stop others from spending much more time for understanding the hidden secrets (which is stated in the file) which eliminates lot of web research to find those mind blowing approaches.

    Surely many will found it helpful and thanks for sharing your extensive knowledge about excel by way of this file and by of answering the questions


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @sixthsense:
    thanks. it was initially just a file for me to learn some great stuff, but it became such a huge collection i thought it'll be wonderful to share. i do try to breakdown & explain some stuff in the file, but ultimately sharing their wonderful knowledge of Excel. =)

    (repeated in edited opening post) Will constantly be updating the opening post (so no notification if you have subscribed to this!) instead of a new post whenever I have updates, so keep a lookout for the date in the file name if you guys are interested.

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Your's reminds me the book called
    Excel Gurus Gone Wild Do the IMPOSSIBLE with Microsoft Excel>Name:  Book Image.jpg
Views: 15521
Size:  26.2 KB

  9. #9
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks,
    some of those tips are really usefull to us beginners!
    Thankyou for sharing!
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  10. #10
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Sixthsense:
    ahhh MrExcel eh? seems like a tempting book to get.

    @Sean Thomas:
    you're very welcome! just sharing what i've learned from the others. i hope the new Main page is easy to search for stuffs

  11. #11
    Registered User
    Join Date
    11-20-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Wow! Thank you for sharing! You are a star!

  12. #12
    Forum Contributor
    Join Date
    09-03-2004
    Location
    Vancouver, BC Canada
    Posts
    136

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks a lot for sharing! That makes the learning all the more productive...It was really useful

    Cheers,
    SJ

  13. #13
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Konnie & kollur:
    you're very welcome~ i'm glad you guys found it useful. do look out for it every month for me to fill up the explantions & share new solutions

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Well gee thanks Ben, now we dont need the forum anymore. OP's can just read what they want from your file, and the rest of us dont get any more thanks or anything *sigh*

    heheh just kidding, that is a really well laid out tutorial, Im sure we will all learn from it. Maybe admin can figure a way to make it something that others can add to?

    Great job
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  15. #15
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @FDibbins:
    haha. i'll be really glad if i can compile something that solves most problems in the world. anyway, i think this file would interest excel lovers more than most people here who are more interested in getting a customised solution.

    thanks a lot~ i dont think adding to my file is a good idea because all of us would have different opinions on the explanation & suggestions. maybe a separate file. but then how many people would contribute? it might become very messy to have 10 over files for people to download. who would also keep track to make sure there are no 2 very similar solutions?

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I downloaded your sample file.

    Nice work. I'm sure that will help a lot of people.

    You can shorten the Main page hyperlink formulas significantly.

    Entered in C3 and copied down:

    =HYPERLINK("#"&A3&"!A1","Go")

    Do the same thing for the other column of hyperlinks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  17. #17
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by Tony Valko View Post
    I downloaded your sample file.

    Nice work. I'm sure that will help a lot of people.

    You can shorten the Main page hyperlink formulas significantly.

    Entered in C3 and copied down:

    =HYPERLINK("#"&A3&"!A1","Go")

    Do the same thing for the other column of hyperlinks.
    Who or what post would you be directing this to?

  18. #18
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Tony:
    thanks. that's amazing. will include it in my next updated version. i suppose the "#" will refer to the active workbook?

  19. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by davesexcel View Post
    Who or what post would you be directing this to?
    It's directed to benishiryo in post #1.


  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by benishiryo View Post
    i suppose the "#" will refer to the active workbook?
    The "pound sign" means the hyperlink goes to a location within the same file.

  21. #21
    Registered User
    Join Date
    09-29-2011
    Location
    glasgow
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    This is great

    Thanks

  22. #22
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @jdrumgold1983:
    glad to know! you're welcome. to think that you used your very first post here after joining for more than a year!

    @arlu:
    sorry to bother you again! could you remove some texts in my post #1 to avoid confusion
    under the "New" sheet are tricks I haven't got time to explain & create a sheet for it.
    there is no more "New" sheet. every tricks are now labelled.

    arlu has been helping me edit the 1st post to the latest file as i can't edit the post after a period of time. thanks a lot.

    @everyone
    also, Sixthsense has pointed out to me that it would be nice to know which are the new additions i placed, for those who downloads regularly. i will be putting in something like a "Added explanation" & "New" the next time round on the Main page. and in the sheet of the formula itself would have a Month Stamp of which version it's from.
    Last edited by benishiryo; 05-16-2013 at 06:28 AM.

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @arlu:
    sorry to bother you again! could you remove some texts in my post #1 to avoid confusion
    Done thanks for taking note of minute details.

  24. #24
    Registered User
    Join Date
    05-18-2013
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Appreciatted.

  25. #25
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    arlu has helped me uploaded again. several changes made to this file (and future updates)

    1. In the Main page, New tricks are marked with a bubble, having the letter "N" inside of it.
    2. In the Main page, tricks added with my Explanations are marked with a scroll, having the letter "E" inside of it.
    3. In the individual sheets where I haven't added the explanations, i have started to date stamp them. if you go to sheet 31 for eg, in H4 is the text "Explanation added: Jun 2013".

    note 1 & 2 will be removed every month i replace the new file. they will be based on what's new & added that particular month.

    @Hitori:
    welcome to the forum, Hitori~ =)

  26. #26
    Registered User
    Join Date
    02-22-2007
    Posts
    40

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Nice work , Thank You.

  27. #27
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Nice work Ben. When will be the next update
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  28. #28
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @vlady:
    thanks. usually (if i can), it's on the 15th of every month. the latest file now is 20130715. =)
    i don't have many new tricks i've seen lately to share though. so i might just add the explanations.

    @khanjee:
    you're very welcome~

  29. #29
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Czech republic
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi, just a briefly:
    Nice excel, but
    :-) advice 31
    Multiple sumif is replaced by SUMIFS - a great function in 2010 (might be in 2007 as well). So, use this. Or, leave at least a comment about it here.

  30. #30
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    i think that was sumif with "or"

  31. #31
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @olwy:
    vlady is correct. that is to do a SUMIF with an OR criteria. SUMIFS is to use AND. using my eg in the file, it's impossible to use SUMIFS. you would be saying that you want to sum B5:B10 if A5:A10 is equals to "BOLIVIA" AND "BRASIL" AND "PERU". there is no way a cell can be of all 3 values. it can only be either of them.

    and yes, SUMIFS is available from Excel 2007 & above

  32. #32
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    And, also, sumifs is only available 2007 +, so, the operation works for multiple enviroments, which is what we are suposed to be aware of
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  33. #33
    Forum Contributor
    Join Date
    03-28-2013
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    226

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you,

    Cant being without appriciate you,

    really really appriciate you friend for sharing such secrets.

  34. #34
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    i can't remember if i have always had a "Ref" sheet, but that is supposed to work with the "1" sheet. dredwolf was the one who pointed to me that the formulas in "1" had some errors & i have rectified that. thanks, dredwolf. the latest file now for that sheet should work fine.

    @venkatpvc:
    you're very welcome. =) hope it helps!

  35. #35
    Registered User
    Join Date
    03-28-2012
    Location
    Philippines
    MS-Off Ver
    2003, 2007, 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    thats a bunch of helpful/productive information... thanks...

  36. #36
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @olivia1972:
    glad you feel so and congrats to you on your first post~ hopefully more to come

  37. #37
    Registered User
    Join Date
    10-25-2013
    Location
    Anywhere
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    WOW My deep respect for u Benishiryo many Thanks!

  38. #38
    Registered User
    Join Date
    10-03-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    67

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks benishiryo for adding some of you knowledge to a spread sheet. New at learning excel and applying it,this is a great reference and a great site

  39. #39
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Shizuo:
    you're very welcome~ and welcome to the forum!

    @Treecko:
    this is an awesome site indeed. learned most of my skills here

  40. #40
    Registered User
    Join Date
    11-03-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks and keep up the good job bro.

  41. #41
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    glad you like it. and welcome to the forum, d2t3xzer. the latest version dated 2 Nov 2013 is up
    =)

  42. #42
    Registered User
    Join Date
    10-14-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks a lot for sharing! That makes the learning all the more productive...It was really useful, looking for such file.thanks a ton.........

    Cheers,
    greatairi

  43. #43
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by benishiryo View Post
    glad you like it. and welcome to the forum, d2t3xzer. the latest version dated 2 Nov 2013 is up
    =)
    just so that newer members know, where can they find the latest version, Ben?
    (keep up the fantastic work, by the way!!)

  44. #44
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @greatairi:

    you're welcome! i wish i had such a file when i started too.

    @FDibbins:
    ahhh you (they) can find all the info they need in the first post. i don't usually announce updates. i just did it since i wanted to reply d2t3xzer. and thank you! frequency is lower now though.

    from post #1:
    Quote Originally Posted by benishiryo View Post
    Edit: Will constantly be updating the opening post (so no notification if you have subscribed to this!) instead of a new post whenever I have updates, so keep a lookout for the date in the file name if you guys are interested.

  45. #45
    Registered User
    Join Date
    12-12-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    This is awesome! Thank you very much, Sir!
    ^___^

  46. #46
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Starlinka:
    reps are not valid before your first post, but i appreciate the thought! thank you & welcome to my favourite forum~

  47. #47
    Registered User
    Join Date
    12-10-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Those are great tips! Thanks for taking the time to put this together and sharing with us! great job!

  48. #48
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Drew H101:
    you're very welcome! thanks for the rep & and welcome aboard too~
    =)

  49. #49
    Registered User
    Join Date
    03-29-2013
    Location
    Bangalore/India
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Excel is just a data keeping tool. But by using formulas, you can crunch data, analyze it and get answers to most complex questions. Just downloaded the sheet, it gave me lot of useful tips and tricks.

  50. #50
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @ synodbio - That's just the half of it, you can make graphs, draw curves and manipulate data in ways that could never be accomplished manually.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  51. #51
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @ synodbio
    sorry to say this, but you are wrong ! Excel is not a data " keeping" tool, IT is a data manipulation tool ! (If you just want to keep data, look into "databases"
    ANY database can store "data", most are very limited in the amount of ways it can be manipulated, that is where spreadsheets come in...
    with NO stored data, just access to a database and proper formulation, a spreadsheet can turn the "data" into usable information...ie... possible trends, actual totals,...
    the fact that many people use Excel as a " data keeping" tool is up to them, but the reality is it is about data manipulation, not storage !

  52. #52
    Registered User
    Join Date
    12-31-2013
    Location
    Renton, Washington
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    OH wow... this is SO awesome!

    When I grow up I wanna be JUST like you = )

  53. #53
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @reallyangela :

    that's extremely flattering, but really??? your parents would be sooooooooooooo disappointed with you haha. welcome to this awesome forum & a happy new year to you!
    =D

  54. #54
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    arlu has just helped uploaded the latest version & here are a few changes i thought i'll highlight:
    1. Changed all the formulas which has "+0" to "--" as I've read from below that it's slightly faster. Also faster than "*1"
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    Affected are Sheet 24 & 39

    2. Found some new ways to doing MIN with condition avoiding array. Realized I had another sheet with MIN/MAX with condition, so I separated the 2 & placed them side by side
    Sheet 25 now consolidates the MIN with Condition. Sheet 26 is switched with 49 to make it side by side & 26 now only contains MAX with condition.

  55. #55
    Registered User
    Join Date
    01-09-2014
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    downloadable files is showing anything send other link

  56. #56
    Registered User
    Join Date
    12-05-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks for the tips. I am just wondering if you i can you a question. I can use Excel to do things i need for work but after stumbling to this forum i am beginning to realize that i have lots to learn. The more i look at the posted questions and some answers - the more scary it looks. What is the best way to improve your knowledge and become good user of Excel? Thank you! Any advise would be appreciated.

  57. #57
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi ROXI,

    I Think, the more you use excel and work on it, the more you learn. Just start with simple things
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  58. #58
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi ROXI,

    I Think, the more you use excel and work on it, the more you learn. Just start with simple things

  59. #59
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @ROXI:
    you're very welcome~ well, if you read my 1st post of the thread, i learned most of my skills by helping out in here. and when i started a thread asking the MVPs where they learn their skills from, it's mostly the same! self-taught & by helping out in forums. while i had to figure most of the formulas on my own, my file would give you a great head start i hope
    =)

  60. #60
    Registered User
    Join Date
    12-05-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks guys. Trying to understand how some formulas work can be quite challenging. Especially knowing where on earth you suppose to put this sign or bracket. Anyway, will be trying hard from now on.

    I used Mike Smart downloads to learn some of Excel. I think its changed a bit and now you just paying membership fee which is around £6 a year for unlimited video tutorials. Or you can buy book on Amazon. Hope this would help someone.

    http://excelcentral.com

  61. #61
    Registered User
    Join Date
    12-13-2013
    Location
    Krakow
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you very, very much! I'm always inspired by the people like you, who are persistant enough to prepare such tutorials for us, the not-so-smart Great job!

  62. #62
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    welcome to the forum, raduczoid. it's always nice & flattering to know that i've inspired people. glad to help & thank you too!
    =)

  63. #63
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

    Red face Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hey Benishiryo! Great workbook, thank you... I open this spreadsheet every morning and study one of your formulas. 2 months of very little effort buys huge returns! Thanks!

    I have a contribution and 2 suggestions:

    Suggestion 1: It would be good if in your first post you put a change-log... that way people can see what has been added and if it makes sense to re-download the new version or not.

    Suggestion 2: Put a link in the workbook leading back to this forum thread, that way if someone has questions or suggestions, they can do so more easily than having to search again.


    Contribution: For the Sheet 7 (Word Count), with very little manipulation I could get to a "Character count" also. It might be useful to offer both the Word count and Character count as 2 formulas on that page (or on its own tab... however you prefer...)

    Anyway, with the range you gave on Sheet 7, it's:

    Please Login or Register  to view this content.
    Hopefully it's useful!
    Last edited by mexindian; 01-22-2014 at 09:53 AM. Reason: Typo in the formula! (sorry... should work correctly now)

  64. #64
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    you're very welcome, mexindian! glad to hear that you've been using it so well.

    Suggestion 1: It would be good if in your first post you put a change-log... that way people can see what has been added and if it makes sense to re-download the new version or not.
    i am unable to edit the first post at the moment. arlu (the admin) has been helping me to upload the new files every time. i don't think i want to trouble her more than that.

    Suggestion 2: Put a link in the workbook leading back to this forum thread, that way if someone has questions or suggestions, they can do so more easily than having to search again.
    currently, this is the only forum or place i shared the file with. so i don't mind doing so. but it wouldn't make sense if i shared it elsewhere too. besides, this thread has been stickied since the beginning. that means you can always find it on top of the page in the Tips & Tutorials section. so it can't be that hard

    Contribution: For the Sheet 7 (Word Count), with very little manipulation I could get to a "Character count" also. It might be useful to offer both the Word count and Character count as 2 formulas on that page (or on its own tab... however you prefer...)
    - you can actually do it with 2 brackets lesser:
    =SUMPRODUCT((LEN((A3:G12)))*(TRIM(A3:G12)<>""))
    so:
    =SUMPRODUCT((LEN(A3:G12))*(TRIM(A3:G12)<>""))
    - not sure if the TRIM will serve any purpose here. so character counting can simply be:
    =SUMPRODUCT(--(LEN(A3:G12)))
    - if you're worried about words having extra spaces, then:
    =SUMPRODUCT(--(LEN(TRIM(A3:G12))))

    thanks for the contribution. but because i cannot put in all the tricks available in Excel, i would have to select only those i find interesting or so commonly used that i find it useful to explain inside. hope you don't take offence!
    =)

  65. #65
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    No of course no problem if you don't include! I just felt like giving back, that's all. By the way, thanks for teaching me about "--"! In fact, after more experimentation, I found that even this works!
    Please Login or Register  to view this content.
    So this is the best scenario.. I try to contribute back, but end up learning more, and simplifying my own equation!

  66. #66
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi Mr Ben,

    P.S
    Might my suggestion sound a lil dummy, (i'm sorry,if )

    Please don't forget to add ways of "Leading zeroes" Issue.
    Not Only to show the zeros trough format Cells properties.
    but, formulas recognize and harmonize (work) with the leading zeros..


    Thank you, so much and congratulation !!!!!!!!!!!!!!!!!!!!!!


    Best Regards from Cold Miami (64 degree)

    David.
    Last edited by david gonzalez; 01-23-2014 at 07:38 PM. Reason: Add emoticon.

  67. #67
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @mexindian:
    yeah you're right. must have been high when i posted that.
    I try to contribute back, but end up learning more I try to contribute back, but end up learning more
    that's what happened to me!

    @david:
    you're very welcome & thanks for the rep~ the method you mentioned might be too small for a topic though & quite easily found in google. sorry about that

    congrats? on? haha.

  68. #68
    Registered User
    Join Date
    09-06-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks....good stuff

  69. #69
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi Mr Ben,

    congrats? on? haha.
    For your achievement , lol

  70. #70
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @Cl.Klink:
    you're very welcome~

    @david:
    haha. i don't think i have achieved anything (or at least anything significant) yet. but thanks!

  71. #71
    Registered User
    Join Date
    08-25-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2012
    Posts
    11

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    thanks benishiryo,
    i found it very useful. can you add to the list of things in the file copy copy a pic from one cell and paste it into another cell by using a button or a hyperlink?

  72. #72
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @johnwatkins35:
    you're very welcome~ i am not sure what you mean by that though. say pic is in A1. and you want a button to copy pic in A1 to where? E1? that would probably be a macro you are thinking of. and if it's just a simple copy and paste of 1 picture, i dont suppose you need a macro?

  73. #73
    Registered User
    Join Date
    08-25-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2012
    Posts
    11

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    benishiryo,
    Yes i would like to say copy a pic from say A1 and paste it down in say E15. So all i would need to do is create a macro for that then and actually i am gonna need a macro if you could help me out with it, that would be great.

  74. #74
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @ johnwatkins, welcome to the forum If you are asking for assistance on specific question, please start a new thread with that question. This thread concerns Bens upload, so comments related to that are fine, but if you want work done - new thread please

  75. #75
    Registered User
    Join Date
    08-25-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2012
    Posts
    11

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by johnwatkins35 View Post
    benishiryo,
    Yes i would like to say copy a pic from say A1 and paste it down in say E15. So all i would need to do is create a macro for that then and actually i am gonna need a macro if you could help me out with it, that would be great.
    benishiryo,
    Yes i would like to say copy a pic from say A1 and paste it down in say E15. So all i would need to do is create a macro for that then and actually i am gonna need a macro if you could help me out with it, that would be great.

  76. #76
    Registered User
    Join Date
    08-25-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2012
    Posts
    11

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by FDibbins View Post
    @ johnwatkins, welcome to the forum If you are asking for assistance on specific question, please start a new thread with that question. This thread concerns Bens upload, so comments related to that are fine, but if you want work done - new thread please
    ......like this.....

  77. #77
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Wow... good work..
    Like my answer, then click * below

  78. #78
    Registered User
    Join Date
    06-24-2014
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    365
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks for sharing.

    Good work.

  79. #79
    Registered User
    Join Date
    07-08-2014
    Location
    Philippines
    MS-Off Ver
    MS office 2010
    Posts
    30

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Sir Thank You Very Much!!! this greatly help me understand and learn a lot more. I really appreciate the effort you did on sharing this. are there any "Like" buttons here? +1000000000 and a ton more of reps. to you

  80. #80
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @satabp, Potth & mikoski:

    very glad that you guys like it. haven't got time to update the file. i promise it'll be real soon!
    =)

  81. #81
    Registered User
    Join Date
    07-02-2007
    Posts
    4

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Total newbie here. Just found this thread. THANKS so much for sharing. I have so far to go and sir/mam, this is the best start I could have imagined.

    Thanks again!!!!

  82. #82
    Registered User
    Join Date
    10-31-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    5

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hello I am new to the site and I hope I can ask you a question because I have a major problem with an excel worksheet. I am working on expected rate of bonds and the formula gives me a #NUM! error message if I change the number in one cell higher than the number 12. If I change it to 13 the error message will pop up. Can you give me any help with this problem? Thank you

  83. #83
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi Peach,

    This posting is in the wrong forum - it's probably best placed in the General forum.

    Can you post your code or formula? The #NUM error means that Excel cannot convert to a number.

  84. #84
    Registered User
    Join Date
    08-10-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I like it mate...nice collection of tricks..

  85. #85
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    A ton of Thanks for such a wonderful collection Benishiryo,
    Click just below left if it helps, Boo?ath?

  86. #86
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @TECHSALES, asheesh & boopathiraja:
    glad you guys like it!
    =D

  87. #87
    Registered User
    Join Date
    11-30-2011
    Location
    Colombo,SriLanka
    MS-Off Ver
    Excel 2007
    Posts
    19

    Thumbs up Re: A Whole Bunch of Tips & Tutorials I've Learned

    Really helpfull
    Awesome

  88. #88
    Registered User
    Join Date
    05-12-2013
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Excellent and its a really a special new year gift
    thanks Beni for sharing and much appreciated.
    Advanced Merry christmas and Have a wonderful 2015
    God Bless

  89. #89
    Registered User
    Join Date
    06-01-2012
    Location
    NY, US
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Nice job Ben! You rock!!!

  90. #90
    Registered User
    Join Date
    03-08-2013
    Location
    Lula, Ga.
    MS-Off Ver
    Office 2016
    Posts
    54

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    A cool thing I find useful for centering is not "Centering and Merging," because this usually does not do what I want, but rather the following does what I want:

    Home>Alignment>Center Across Selection

  91. #91
    Registered User
    Join Date
    05-08-2010
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Very comprehensive.
    Thanks

  92. #92
    Registered User
    Join Date
    04-13-2014
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks for the Tips. I am fairly new to the forum and the resources available in this forum are very useful.

  93. #93
    Registered User
    Join Date
    08-14-2014
    Location
    New York
    MS-Off Ver
    Home and Office 2010
    Posts
    8

    Cool Re: A Whole Bunch of Tips & Tutorials I've Learned

    Awesome! Thanks for sharing!

  94. #94
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    179

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Awesome! Thanks for sharing!!!

  95. #95
    Registered User
    Join Date
    04-16-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Great Stuff Ben, God Bless

  96. #96
    Registered User
    Join Date
    01-08-2012
    Location
    Pembroke, Malta
    MS-Off Ver
    Excel 2010
    Posts
    1

    Thumbs up Re: A Whole Bunch of Tips & Tutorials I've Learned

    There are times in our life where we meet exceptional people who are always ready to give to their fellow men their help without expecting nothing in return. And this is one instance of them.

    We should be very proud to have these persons among us. I, personally feel more safe knowing that these people are there to help whenever they are asked for advice.

    Wishing Ben and all the forum community and their families all the best for the coming festive season, and a new year filled with health, prosperity and human kindness to each other.

    Regards and thanks for your work Ben.

    Ojs

  97. #97
    Registered User
    Join Date
    12-15-2014
    Location
    Cleveland, OH
    MS-Off Ver
    2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I am a very new member (2days) and also a lover of Excel!! I am super excited you posted this Tips and Tutorials! Thank you so much! As you said it is so easy to learn when helping someone else. I am looking forward to being a member here and hope I will be able to contribute as well.
    Thank you again.

  98. #98
    Registered User
    Join Date
    05-16-2010
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Benishiryo,

    Logged in to say thanks, your generosity is inspiring.

    Kind regards,

    Lucas

  99. #99
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Very comprehensive.
    Thanks!

  100. #100
    Registered User
    Join Date
    10-29-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Very good compilation and good work!!!!!!!

  101. #101
    Registered User
    Join Date
    05-30-2014
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I have been lurking here for a while as well, and have just looked at the example spreadsheet filled with so many tips. I consider myself a confident user of excel already but most of the ideas you share are brilliant - and I probably did them long-way round.
    Thanks for the file. It is very useful.

  102. #102
    Registered User
    Join Date
    04-07-2014
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks a lot!

  103. #103
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    THis thread now has over 100 posts to it, and may be becoming hard to find the latest version if Bens awesome file. (Im not sure if the file on post 1 keeps getting updated and stays in that post, or a new file is added now and then?)

    Perhaps some kind of pointer can be added to direct members where to find the latest version?

  104. #104
    Registered User
    Join Date
    09-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    WOW... this is great, thanks

  105. #105
    Registered User
    Join Date
    06-21-2013
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by FDibbins View Post
    THis thread now has over 100 posts to it, and may be becoming hard to find the latest version if Bens awesome file. (Im not sure if the file on post 1 keeps getting updated and stays in that post, or a new file is added now and then?)

    Perhaps some kind of pointer can be added to direct members where to find the latest version?
    He said he would keep post #1 updated..

  106. #106
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by NCBUYER View Post
    He said he would keep post #1 updated..
    OK thanks, I must have missed that

  107. #107
    Registered User
    Join Date
    07-12-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you very much! Great work

  108. #108
    Registered User
    Join Date
    06-21-2013
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by FDibbins View Post
    OK thanks, I must have missed that
    Doesnt look to have been updated in about a year. Great stuff though!

  109. #109
    Registered User
    Join Date
    11-28-2013
    Location
    India
    MS-Off Ver
    Excel 2007, Excel 2013
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you very much Beni.

    Keep adding the useful stuff.

  110. #110
    Registered User
    Join Date
    11-13-2014
    Location
    Australia
    MS-Off Ver
    office 2010
    Posts
    8

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I love it..... Great work!!

  111. #111
    Registered User
    Join Date
    08-08-2010
    Location
    Delhi, India
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks for the compilation. This is a lot of work. I have to see and understand each one of them and I am sure a lot of them will be useful and improve my productivity.

    raju

  112. #112
    Registered User
    Join Date
    09-10-2012
    Location
    Sydney, AUstralia
    MS-Off Ver
    2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you, that's great work. You're far more organised than I could ever be.

  113. #113
    Registered User
    Join Date
    06-23-2010
    Location
    Saigon, Vietnam
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you!!! You are awesome man!

  114. #114
    Registered User
    Join Date
    07-19-2013
    Location
    mumbai
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    it is very useful collection of solutions

  115. #115
    Registered User
    Join Date
    08-06-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Its really helpful thread

  116. #116
    Registered User
    Join Date
    11-28-2014
    Location
    singapore
    MS-Off Ver
    2003
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks for sharing...cool stuff

  117. #117
    Registered User
    Join Date
    10-06-2009
    Location
    dom rep
    MS-Off Ver
    Excel 2003
    Posts
    1

    Unhappy Re: A Whole Bunch of Tips & Tutorials I've Learned

    useful and condensed
    Great job

  118. #118
    Registered User
    Join Date
    02-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Wow! Thanks for compiling this!

  119. #119
    Registered User
    Join Date
    02-09-2014
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    really nice work thanks

  120. #120
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    The thought of sharing is just a blessing and a good thing. Thank u so much for putting this together. I've downloaded the file and will keep checking for. any updates. I shall check the tips later on after sending in this response.

  121. #121
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    wow the responses have been really overwhelming these few days. and i'm really touched by some of the messages you guys left. i guess i now have a bigger responsibility to share more. i'm just really too caught up with my new job & my upcoming marriage. i really miss helping out here. hope to be here helping out again soon!

    wishing you a guys a Merry Christmas & a happy new year!

  122. #122
    Registered User
    Join Date
    12-11-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    That was a great job and neatly explained also. Keep it up Ben.

    Thanks a lot

    GNK

  123. #123
    Registered User
    Join Date
    12-20-2014
    Location
    San Diego area, So. Calif., USA
    MS-Off Ver
    Excel 2007 SP3
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Wow, Benishiryo, I just downloaded your workbook and I'm blown away! At first glance, I see where if I'd had your file a while ago, I'd have saved at least a couple of hours, looking for a solution/how-to for just #8. And there's several formulas there that I didn't even know were possible.
    A hat tip to you, for taking the time to share and update your file. Unfortunately, a lot of people would never make such an effort. I'm really glad I found this forum today.

  124. #124
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi Benishiro,

    I wish the best for you and your new bride!

  125. #125
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Alot of hard work there and extremely helpful to share useful tips with everyone! Superstar!

  126. #126
    Registered User
    Join Date
    01-18-2012
    Location
    Los Angeles USA
    MS-Off Ver
    Excel Office 365
    Posts
    80

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks so much great job and very nice of you to put together.

  127. #127
    Registered User
    Join Date
    05-19-2012
    Location
    Alexandria , Egypt
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks for sharing these tips.

  128. #128
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    U n b e l i e v a b l e ! ! !

    The first tutorial that caught my eye was "List non-blank fields without array"...........I immediately tried it on a problem

    file I pull out once in a while just to beat myself up with. It has 99,918 rows with 5220 non-blanks. I cannot believe how fast your

    formula dispatched this one.

    That does it......my NY resolution is to throw away all my array formulas.

    Thank you so much!

    There needs to be a way to give you more than one rep for this one.

    FR

  129. #129
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @xladept:
    thank you very much for the blessings!

    @gnk, Doc Sheldon, jaelah, laguna, midoziad:
    glad you guys like it!

    @FR:
    that's complimenting, but hold your horses! probably need to look at this thread i started on non-array formulas:
    http://www.excelforum.com/excel-form...-and-cons.html
    =D

  130. #130
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi Benishiryo

    All the best in advance for your new Married life & Merry Christmas.
    We will be waiting for your wedding Cake.

  131. #131
    Registered User
    Join Date
    08-07-2013
    Location
    Batam
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Fantastic post, Ben!

  132. #132
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by benishiryo;3936516@FR:
    ..............that's complimenting, but hold your horses! probably need to look at this thread i started on non-array formulas:
    [URL="http://www.excelforum.com/excel-formulas-and-functions/895758-array-vs-non-array-what-are-the-pros-and-cons.html"
    http://www.excelforum.com/excel-formulas-and-functions/895758-array-vs-non-array-what-are-the-pros-and-cons.html[/URL]
    =D
    I did and glad I did. Thank you, again. I was so blown-away by the speed improvement in that problem workbook..........!!

    Thanks for "horses check"!!
    FR

  133. #133
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Very happy to see you keeping in touch with this useful post Ben !

  134. #134
    Registered User
    Join Date
    06-14-2009
    Location
    Cairo,Egypt
    MS-Off Ver
    Excel 2007-2010
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Many Thanks, it is quite helpful

  135. #135
    Registered User
    Join Date
    08-28-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you for the work you have done

  136. #136
    Registered User
    Join Date
    05-27-2014
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Wow, how great......
    This "A Whole Bunch of Tips & Tutorials I've Learned" Tutorials is Very Important and Useful for me.
    Thanks Ben.
    With Best Wishes of "HAPPY NEW YEAR 2015"

  137. #137
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    very glad to share, guys~ a happy new year to all of you~~
    =D

  138. #138
    Registered User
    Join Date
    03-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    thanks for your effort.

  139. #139
    Registered User
    Join Date
    01-19-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Excel 2007
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Awesome! Thank you for sharing this!

  140. #140
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quick question...

    Is the file on post 1 the latest version of your file, Ben?
    If not, where can that 1 be found?

  141. #141
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @FDibbins:
    yes it will always be in the 1st post. otherwise, it'll be hard to find where the file is. i always seek arlu's kind help to upload the file.

  142. #142
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Quote Originally Posted by benishiryo View Post
    @FDibbins:
    yes it will always be in the 1st post. otherwise, it'll be hard to find where the file is. i always seek arlu's kind help to upload the file.
    OK thats what I thought (and makes perfect sense). I thought you had changed the hyperlink method at some point, but that file still used the HL on the GO cell - hence my question

  143. #143
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @FDibbins:
    sorry i missed your question. yes i did change the method using Tony Valko's suggestion (the hex # for referring to the file formula is on). it was much longer back then!

    @everyone:
    file is finally updated thanks to Fotis' help in uploading!

  144. #144
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    This is a great post benishiryo

    Loved it..
    Cheers!
    Deep Dave

  145. #145
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Beni, is there another file to upload? The file on post 1 still uses the "GO" link

  146. #146
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @NeedForExcel:
    glad you do! i feel guilty whenever someone still posts in this thread while i haven't been doing updates!

    @FDibbins:
    it has been using "Go" all the while. the hyperlink method was changed from this long =CELL("filename",...) method to Tony's suggested # method
    =)

  147. #147
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    To really really make use of this file, one himself should try to solve each and every trick in the Workbook.

    If you can't solve it, look at the steps and learn how to solve it, If you can solve it, look at the answers and hopefully you will learn a better way, and If you can solve it with the best possible solution, consider yourself a guru

  148. #148
    Registered User
    Join Date
    12-08-2015
    Location
    Centurion, South Africa
    MS-Off Ver
    Office 2013
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you so much. I have so much to learn - this is going to be a valuable resource.

  149. #149
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    rosemarlow12, this reads like advertising to me. We do not allow advertising of commercial products and services here. The Tips & Tutorials subforum is for the free sharing of tips and tutorials that members have developed. And you have posted irrelevant content in someone else's thread, which violates our hijack rule.

    I have removed your post.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  150. #150
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    i created something recently i thought would interest some of the subscribers here too. more for beginners, rather than the pros though:

    http://www.excelforum.com/tips-and-t...-how-tool.html

  151. #151
    Registered User
    Join Date
    04-28-2016
    Location
    Islamabad, Pakistan
    MS-Off Ver
    2007 , 2010 , 2013
    Posts
    5

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    WOW! Thankyou so much for spending your time to create this. Very, very helpful. God bless!

  152. #152
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    you are very welcome, techypk!

  153. #153
    Registered User
    Join Date
    04-25-2017
    Location
    Channel Islands
    MS-Off Ver
    2013
    Posts
    9

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi Ben
    I've tried to download you file but get an error message that ".....the file format or file extension is not valid". This is unusual as I'm only using a 2013 Excel version, which I would have thought would have been ok to open the file(?) Any suggestions, as it sounds like it could be a useful document.

  154. #154
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    179

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Wizard1001,

    Rename the file. the file name is too long.

    Tim

  155. #155
    Registered User
    Join Date
    04-25-2017
    Location
    Channel Islands
    MS-Off Ver
    2013
    Posts
    9

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks, yeah, I thought of that and tried it, but still no joy unfortunately

  156. #156
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    179

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    strange because I downloaded it and it didn't open until I shortened the name. Then it was fine.

  157. #157
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    I get error messages as well when trying to open a file from excel, I get the error message and the file will have an extra .xlsx at the end of it, filename.xlsx.xlsx, if I download it to the computer and open it from the folder it does not have the extra .xls.

  158. #158
    Registered User
    Join Date
    09-25-2017
    Location
    Pakistan
    MS-Off Ver
    2013
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    OMG This is awesome, have to add you some stars for this

  159. #159
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @AdeelAhmed:
    welcome to the forum! glad you find it awesome. you can't give rep until you have posted (i think), but i appreciate it. hope you learn a lot more from this forum!

  160. #160
    Registered User
    Join Date
    02-16-2014
    Location
    qatar
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    thank you for sharing!

  161. #161
    Registered User
    Join Date
    04-17-2018
    Location
    Norfolk, VA
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thank you for sharing this!! I was having the same problem as Wizard1001 opening the file but it finally worked after I put ".xls" at the end of the file name

  162. #162
    Forum Contributor
    Join Date
    05-23-2017
    Location
    Bang, India
    MS-Off Ver
    MS Office 2007
    Posts
    221

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Tremendous Job !!!!! For beginners this is a "Buffet of Macros" Your valuable time spent on this is like a Precious Gem for others. Keep Adding Bro !!! I think you can Release a book of your own soon......

  163. #163
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @leyo, Samantha, Quivolt:
    you are very welcome! i think i have a few other tricks i learned from others that i can share. just have to find the time for it

  164. #164
    Registered User
    Join Date
    06-15-2018
    Location
    North Carolina
    MS-Off Ver
    16.14.1
    Posts
    7

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    thank you for this!

  165. #165
    Registered User
    Join Date
    07-06-2018
    Location
    Newtownards
    MS-Off Ver
    Office Professional Plus 2013
    Posts
    4

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Great, thanks

  166. #166
    Registered User
    Join Date
    11-26-2018
    Location
    Bangkok, Thailand
    MS-Off Ver
    2016
    Posts
    10

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Very beneficial. Thanks

  167. #167
    Registered User
    Join Date
    09-18-2015
    Location
    Doha
    MS-Off Ver
    MS Office 2013
    Posts
    1

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Thanks a lot

  168. #168
    Registered User
    Join Date
    02-12-2019
    Location
    UK
    MS-Off Ver
    2010
    Posts
    3

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Amazing, definitely something to refer back to.

  169. #169
    Registered User
    Join Date
    04-01-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Nice work, will keep this in mind. thanks

  170. #170
    Registered User
    Join Date
    11-04-2018
    Location
    Manila
    MS-Off Ver
    2016
    Posts
    16

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    Hi everyone, i am getting an error that the file type is not correct or is not supported. im using office 365. is there a way to get a copy of the file?

  171. #171
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: A Whole Bunch of Tips & Tutorials I've Learned

    @ehome.ptmacky:
    i googled if office 365 can open an xls file. seems like it can.
    https://answers.microsoft.com/en-us/...5-a72fee4ca944

    but i have uploaded an xlsm version if that helps
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1