Helpful Excel Formulas and Tips

Most of the people who use computers, have in some way or form used excel. Its always handy to know few important excel formulas that can help us write 1-liner code that otherwise in normal programming languages like C, C++ or even C# would have taken 10 or more lines of code. In this blog post, I will keep a list of helpful excel formulas.

If
syntax: if(condition, true value, false value)
example: =if(A3>B3,"A3 is larger", "B3 is larger")

SumIf 
syntax: sumif(range, condition, sumrange)
example: =sumif(C5:C14, C17, D5:D14)
In this example, sumif will first search in the range C5:C14 to see which of these cells are equal to C17, then for these cells it will sum up the corresponding values in the range D5:D14.

offset - returns a reference to a range which is offset from provided cell/range
syntax: offset(from cell/range, rows up/down, column left/right, num of rows, num of cols)
example: =SUM(OFFSET(C7,2,3,4,5))
In this example, we start from C7 and then go 2 rows down because it is positive. If it was negative, we would have gone up. Then we go 3 columns to the right because it is positive. If it was negative, we would have gone left. Now from this cell, we select 4 rows and 5 columns and that becomes our result range. Then, we call SUM on this range.

match - searches for a value in an array and returns the relative position
syntax: MATCH(value to search, array to search in, match type)
example: =MATCH(B13,OFFSET(C12,1,1,4,1),0)
In this example, match will search the value in cell B13 in the array range provided by offset function. Match type 0 means it will return the relative position of the first value that is equal to the value in cell B13. Match type -1 would mean that it will find the smallest value that is greater than or equal to value in B13. Match type 1 would mean that it will find the largest value that is less than or equal to the value in B13. 1 is default.

index - 
syntax: index(range, row, col)
example:=INDEX(D18:G20,2,3)
In this example, the index function will return the value in row 2 and col 3 in the range D18:G20 i.e value in F19 cell.

vlookup -
syntax: vlookup(value in first col, range, col to return value from, boolean match)
example: =VLOOKUP(3,D22:G27,4,FALSE)
In this example, vlookup will search the cell with value 3 in the first column of range D22:G27. Then for that row, it will go to the 4th column and return the value. boolean match is to control the case whether an exact match for 3 is found or not. True means that if 3 is not found in the first col then return the next largest value which is less than 3. False means find the exact match = 3 in the first column.

split -
syntax: split(cell range, separator)
example: =split(A1," ")
split will split the cell value into multiple columns based on the separator value.

Helpful Tips
  • To insert multiple rows in between, add first row by right clicking and insert and then use the key 'F4' to keep on inserting.