I'm a certified Excel Advance user. :P Attended the Excel Advance Class recently and hey, I did learn new things other than some cool tricks as well. Here's some to share with you ...
Cool Trick One
Task: Update Cell A1 : D20 with values 50
How To :
a. To highlight from A1 : D20, do F5 to launch Go To screen
b. At Reference, type A1:D20 * this is to do quick highlight
c. Do F2 and type 50 (A1:D20 should still be highlighted)
d. Do Ctrl + Enter
Voila ! All cells get updated with the same value. You may do the same if you need to update the same formula across all cells as well !
Cool Trick Two
Task : If you want to calculate dates for reasons such as to calculate how old are you, how long you have been working , etc
How To :
a. Use the DATEDIF function (which is no longer in the list of functions in Excel) :
= Datedif(Date, date to compare with, "units")
Legends :
a. Date : Your birthdate / date joined company / etc ..
b. Date to compare with : You can fix a date ("07/12/1999") or compare with another column set of dates or set to system date using Today()
c. Units : "Y" to calculate the differences by years, "M" to calculate the differences by months, "D" to calculate the differences by days
Example :
1. = Datedif("07/15/2005", today(),"Y")
2. = Datedif("07/15/2005", "08/23/2008,"M")
I'll try and cover more on Excel tomorrow. Perhaps, how to use Pivot Table? Yah ...that's cool and pretty helpful.
