The Alumni Leadership Council of my department held an Excel Training Session tonight. I have used Excel for at least 7 years, but today most of the functions are all eye openers for me.I guess what I know before is just way too just for dummy. John the leader of the Leadership Council gave us a very detailed and practical Excel session. Thank you, John.
I want to do a summary here, so anyone who might be interested can benefit from it too.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Usage: lookup value in different sheets
eg: =VLOOKUP($A4,OECD_LeadingIndicators!$B$7:$J$42,9,0)
Table_array: a array table come from any sheet in a excel. Here:OECD_LeadingIndicators!$B$7:$J$42
Col_index_num. Here:9; We can also use MATCH function to make it dynamic.
range_lookup: Here we leave it blank, cause we don't want to defy.
Shortcuts: Control Pageup/pagedown: shifting to different sheets.
MATCH(lookup_value,lookup_array,match_type)
Usage: Find the column number or row number of a variable.
eg:MATCH(B$3,OECD_LeadingIndicators!$C$6:$J$6,)
Inbuilt match into the vlookup function.
Eg:=VLOOKUP($A5,OECD_LeadingIndicators!$B$7:$J$42,MATCH(B$3,OECD_LeadingIndicators!$B$6:$J$6,),0)
check it. Highlight the match function, hitF9, see if it's 9.
"An array formula is a formula that works with an array, or series, of data values rather than a single data value."
Array formulas are so powerful, because they can express logical operations.
=AVERAGE(B5:B10/B4:B9)-1 Find the growth rate.
=MAX(G4:IFG10+G4:G10=1,B4:IFB10>C4:C10,B4:B10)
Among all the European countries, whose Dec data is larger than June's, find out the max Dec value of all the countries satisfied the above condition.
Hit "Control+shift+enter" instead of "enter" to let excel know it's an array formula.
( I know it's confusing without a spreadsheet in front of you to see the real data. I will ask permission of John, see if I can upload the excel. If yes, i will upload it in my dropbox.)
"The Best Function Most Users Have Never Heard of"-Quoted from John Affleck
Actually, I found out the whole website is useful and well designed. Interesting!
Other Tips:
Sheet naming:
•No spaces—spaces cause problems for reference formulae
•Use intelligent names, to easily reference using INDIRECT
•Include “divider sheets” to organize data, eg “RawData==>”, “Charts==>”, etc.
keyboard shortcuts
<I know rare keyboard shortcuts, so most of the following shortcuts will be very basic>
F2: See the result of the function
Highlight part of the function ( always function inside of another function) + F9: result of the sub-function
F4: add $ to selected cell
Control Z: Cancel (applicable to Word too)
Control Y: Redo (applicable to Word too)
Control F: Finder
Something not well organized. Hope one day I can understand excel better and answer those questions. I am sure I would laugh at myself about the way I manifested all the materials.:)
* How to Zoom in to a value Or a square, then find value in the zoom?
=min(if …=.., row()) find the min row if .....
=max(if …=..,row()) find the max row if...
* Remember dynamic is very important when you have large amount of data. That's why we need formula.
*How to use Marco to connect excel with powerpoint.
*Use Copy Path instead of attaching the excel file in your email and send to others. It can avoid the mess created by multiply editions.
