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))
    

pitfalls

Line wrapping pitfall