### Check If cell is blank in Excel

```=IF(D5="","Open","Closed")
```
```=IF(ISBLANK(D5),"Open","Closed")
```

### Sort Numeric Columns in Excel using Formulas

`=RANK(C5,sales)+COUNTIF(\$C\$5:C5,C5)-1`

where “sales” is the named range C5:C11

### Convert text to number with VALUE formula in Excel

`=VALUE("\$1,000")`
```=VALUE(A2)
```
`=VALUE(RIGHT(A2,3))`

### Excel ROW Function

The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.

```=ROW(A3) // returns 3
=ROW(D5) // returns 5
=ROW(F10:J15) // returns 10```

### Read Excel file in Pandas

`import pandas as pd`
```excel_file = 'movies.xls'
```movies_sheet1 = pd.read_excel(excel_file, sheetname=0, index_col=0)
```

### Extract unique items from a list in Excel

Array distinct formula (requires pressing `Ctrl + Shift + Enter)`:

`{=INDEX(list,MATCH(0,COUNTIF(\$D\$4:D4,list),0))}`
```=IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0, COUNTIF(\$B\$1:B1, \$A\$2:\$A\$10), 0)), "")
```

### Excel MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

```=MATCH(E2,B3:B11,0)
```
```=MATCH("pq*",B3:B11,0)
```
```=MATCH("peach",B3:B9,0)
```

### Excel INDEX Function

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

```=INDEX(A1:B5,2,2) // returns value in B2
=INDEX(A1:B5,3,1) // returns value in A3```

### Excel Array formula

An array formula performs an operation on multiple values instead of a single value. The final result of an array formula can be either one item or an array of items, depending on how the formula is constructed. To work correctly, many array formulas need to be entered with `control + shift + enter`. When you enter a formula this way, you’ll see the formula wrapped in curly braces {} in the formula bar.

```{=SUM(LEN(B2:B11))}
```