Short cuts
F2 Edit selected cell
F4 Repeat last action
F4 Add "$" to a text in a cell
(e.g. Double click on a cell containing "A2" and press F4 to give "$A$2"
Ctrl + Y Redo last action (opposite to Ctrl + Z)
Ctrl + D Copy above
Ctrl + R Copy left cell
Ctrl + S Strikethrough
Ctrl + F Replace
Ctrl + G Go to a specific cell
Ctrl + Space Select entire column
Shift + Space Select entire row
Ctrl + +/- Add or Delete column/row
Ctrl + Shift + : Current time
Ctrl + ; Today’s date
Ctrl + Shift + L Turn on/off filters
Ctrl + Page Up Go to the Excel Sheet before
Ctrl + Page Down Go to the next Excel Sheet
Ctrl + Tab Switch between open workbooks
Alt + Tab Switch between open workbooks
Alt + Enter Move to the next line within a cell
Formulas
Find a cell that corresponds to the cell we are matching against
LOOKUP (lookup_value, row or column, [result_vector])
E.g =LOOKUP(20, D:D, C:C)
Look for "20" in D:D and find what C:C contains
Find data from a specific column (vertical) in table
VLOOKUP (lookup_value, table, column_index, range)
E.g =VLOOKUP(A1, C1:E4, 3, FALSE)
Look for A1 in the 3rd row of table C1:E4
Find data from a specific row (horizontal) in table
HLOOKUP (lookup_value, table, row_index, [range_lookup])
E.g =HLOOKUP(A1, C1:E4, 3, FALSE)
Look for A1 in the 3rd row of table C1:E4
Sum cells that match multiple criteria
SUMIFS (sum_range, range1, criteria1, range2, criteria2,...)
E.g. = SUMIFS(F:F, C:C, "red")
Add up values in column F for all the "red" in column C
E.g. = SUMIFS(F:F, C:C, "red", D:D, "NSW")
Add up values in column F when there is "red" in column C AND "NSW" in column D
To test for certain conditions
IF (logical_test, [value_if_true], [value_if_false])
E.g. =IF(A1>70, "Y", "N")
If A is bigger than 70 then say "Y"
Nested IFs
IF (logical_test, [value_if_true], IF (logical_test, [value_if_true], [value_if_false]))
COUNTIF
COUNTIF(range, criteria)
Get the periodic payment for a loan
PMT (rate, nper, pv, [fv], [type])
E.g. =PMT(interest rate/12, periods (term in months), - loan amount)
To find out monthly payment
LEFT (cell number, number of characters)
E,g. If cell B5 contains "New york" then =LEFT(B5,3) will give “New”
PROPER () E.g. Capital -> small
RANDBETWEEN(bottom, top)
INDEX()
Format
Quotation marks
10 = Number
"10" = Text
תגובות