Skip to main content
Microsoft Excel

Microsoft Excel #

Syntax highlighting for Excel Formulae - language suggestions - Meta Stack Exchange (Using scala here.)

Reference #

  • Cell: A1, B3, A1:B3
  • Column: B:B
  • Row: 3:3
  • Sheet: 'Sheet 1'!B3:B40
  • Keep reference position when batch applying: $A$1:$B$3

Single and double quotations are strictly different. Single quotation is only used when:

  • Referencing to sheet names
  • Put in the beginning of number cell to make it a string cell

Functions #

CONCAT #

CONCAT function - Microsoft Support

Stitch strings & values of cells together.

CONCAT("Words", A1, B3, ...)

COUNTIF, COUNTIFS #

COUNTIF function - Microsoft Support
COUNTIFS function - Microsoft Support

Count rows that meet conditions.

OR:

COUNTIF(<cells>, <condition>) + COUNTIF(...) + ...

AND:

COUNTIFS(
    <cells>,        // B2:B62
    <condition>     // ">0"
    // can be repeated multiple times
)

IFERROR #

IFERROR function - Microsoft Support

Set default value for when function returns an error.

IFERROR(
    <function>,
    <default value> // ""
)

ROUND, ROUNDUP, ROUNDDOWN #

ROUND function - Microsoft Support
ROUNDUP function - Microsoft Support
ROUNDDOWN function - Microsoft Support

ROUND / ROUNDUP / ROUNDDOWN(
    <cell>,     // B2
    <decimals>  // 0
)

VLOOKUP #

VLOOKUP function - Microsoft Support

Look up code and return values from an area of codebook.

VLOOKUP(
    <cell of code>,                 // B3
    <cells of codebook>,            // $R$3:$T$20
    <column to return in codebook>, // (1-base)
    <Approximate (1) or Exact (0)>  // 0
)
Calendar August 17, 2023