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

Nice work Ben!

Nice work. I will sticky this thread.

@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.
=)

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

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

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

Your's reminds me the book called

Excel Gurus Gone Wild Do the IMPOSSIBLE with Microsoft Excel>

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

@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

@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

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

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

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.

Originally Posted by Tony Valko

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?

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

Originally Posted by davesexcel

Who or what post would you be directing this to?

It's directed to benishiryo in post #1.

Originally Posted by benishiryo

i suppose the "#" will refer to the active workbook?

The "pound sign" means the hyperlink goes to a location within the same file.

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

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

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~ =)

Nice work Ben. When will be the next update

@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~

i think that was sumif with "or"

@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

And, also, sumifs is only available 2007 +, so, the operation works for multiple enviroments, which is what we are suposed to be aware of

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!

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

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

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

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

Originally Posted by benishiryo

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

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

Originally Posted by benishiryo

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.

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

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

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

@ 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 !

@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

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.

Hi ROXI,

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

Hi ROXI,

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

@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
=)

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

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!

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!
=)

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.

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

@Cl.Klink:
you're very welcome~

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

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

@ 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

Wow... good work..

@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!
=)

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.

A ton of Thanks for such a wonderful collection Benishiryo,

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

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?

Originally Posted by NCBUYER

He said he would keep post #1 updated..

OK thanks, I must have missed that

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!

Hi Benishiro,

I wish the best for you and your new bride!