Excel notes
From raju
Tips
vlookup on multiple tables
=IFERROR(VLOOKUP("what?",table1,colnum,FALSE), IFERROR(VLOOKUP("what?",table2,colnum,FALSE),0))
replace blanks with 0
- Select the data of interest
- Home -> Find & Select -> Go To Special -> select Blanks -> ok
- Enter 0 -> Ctrl+Enter
Ref:- See https://www.extendoffice.com/documents/excel/772-excel-fill-blank-cells-with-0-or-specific-value.html#a1 for a detailed version of this tip with screenshots etc.,
Find excel version
File -> Account
Keyboard shortcuts
- To move quickly to the first/last row/column in a table, select a cell -> double click on the cell border in the direction you wish to go. For example, double clicking on the top border will take you to the first row, double clicking on the left border will take you to first column etc.,
- Double clicking on a cell containing a formula will highlight all the cells referenced in that formula.
- To cycle among cells referenced in a formula: ctrl+[, Enter, Enter, ...
- To cycle among cells referencing a cell: ctrl+], Enter, Enter, ...
- To convert a row to a filter: select it and then press ctrl+shift+l . Pressing ctrl+shift+l again will convert it back to regular row.
convert YYYYMM as date in
=DATE(LEFT(A1,4),RIGHT(A1,2),1)
convert YYYYMMDD to YYYY-MM-DD
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), "yyyy-mm-dd")
using isna in an if clause
To do
if A1 is not #N/A use A1 else if B1 is not #N/A use B1 else use C1 end end
do
=IF(NOT(ISNA(A1)), A1, IF(NOT(ISNA(B1)), B1, C1))