Advanced Excel Notes

This page includes some notes from advanced excel course.

Edit author info in excel
Officebutton -> Prepare-> properties

Right click a cell -> Insert comment ==> Your user name shows up in a small popup box(to change the username goto Office Button-> Excel options -> Personalized your copy of excel)

Date functions and formula's in excel
To get sum of previous cells press (Alt+= and Enter key) CTRL+~ -> Put excel in formula mode LEN(A3)- > Gives the length of the cell in an excel sheet FIND(character_to_find,cell,indextostartfinding(normally 1)) LEFT(cell,ENDOFCHAR), MID(cell,start,numofchars)

=TODAY -> Gets today's date =MONTH(TODAY) -> Gets current month =DAY(TODAY) -> Gets today's date =NOW -> Get current date and time =DATEDIF(Datecell1,Datecell2,"d") -> gives difference between two dates

Shortcuts in excel
CTRL+A(2times) -> Select the whole sheet CTRL+D -> Do whatever i have done it on the previous cell(top cell) CTRL+G -> Goto CTRL+H -> Replace CTRL+K -> Hyperlink CTRL+L, CTRL+T -> Table CTRL+N -> New sheet CTRL+-(minus) -> Insert CTRL+ Pagup and pagedown - To naviage betweens sheets F4-> Do the last operation

VLOOKUP Syntax
VLOOKUP(Lookupvalue,Lookuptablerange,column index number to return,TRUE/FALSE( TRUE-> Approx match FALSE ->Exact match))

HLOOKUP Syntax
HLOOKUP(Lookupvalue,table,rowindexnumber to return)

Create list drop down out of columns in excel
Select a cell: Data->Data Validation-> Validation criteria:List, Validation Source:Select the range

Splitting data
If you have huge amount of data delimited by a separator and if you want to split them and put them in columns Data->Text to columns -> Delimiter

Protecting excel sheets/cells
Review -> Protect stuff

Enabling Macros in excel
To start using macros-> First enable developer option by going to Office Button->Excel options-> Show developer tool bar.