128 invaluable Google Sheets functions and formulas for engineers

• Google Sheets has come a long way since it first launched in 2006.
• It is free and has become a versatile and stable spreadsheet program.
• Like MS Excel, it also has a bunch of useful functions.

Google Sheets was first launched over 16 years ago and has gone from strength to strength ever since. While not as capable as premium off-the-shelf spreadsheet packages like MS Excel, considering it is free, you can't go wrong with Google Sheets.

Like any spreadsheet package, Google Sheets also comes with some potent functions and formulae that can help your sort, clean, analyze, and otherwise organize your data in a matter of minutes. Let's discover some of the most useful ones.

Before we get into the weeds, we'll assume you have at least the basic knowledge of a spreadsheet and its structure. If not, you might quickly review our recent guide to Excel shortcuts.

Assuming you are experienced with Excel, Google Sheets, or both, you might wonder what differences exist between the two if any.

Let's find out.

Which is better, Google Sheets or Microsoft Excel?

In short, it depends on your needs and budget.

Either is a competent tool for most general users, but some significant differences can make a difference for more advanced users.

Here are the main distinctions between the two spreadsheet programs in case you're unsure which one will suit your needs the best:

• Given that Google Sheets is free, this is the option for you if you're on a low budget.
• Because it is free, Google Sheets can be seen as a more slimmed-down and "no frills" version of Excel, which comes with far more functions and possibilities.
• If you prefer desktop applications, offline solutions, and internet independence, Excel is for you.
• Google Sheets is available in the Cloud for people who like to work while on the go. This makes it great for collaboration between disparate team members.
• However, Excel also provides a mobile app and an online solution, though neither is free.
• Excel is the better option for people who work with large data sets because it can manage more data than Google Sheets.
• Excel has many keyboard shortcuts that are not always, if at all, available on Google Sheets.
• Excel has more data visualization options than Google Sheets does
• Excel has better formulas too, but many of those you use in Excel have equivalents in Google Sheets (sometimes even the same)
• Google Sheets is regularly and automatically updated and can be saved into your Google Drive with incredible ease. Conversely, Excel is not easily updated but can also be shared via cloud servers.

All-in-all, Google Sheets can do most of the basics that Excel can but suffers when you need to do more complex stuff like automation, data visualization, or working with large data sets. Excel, on the other hand, is not free and is pretty limited when it comes to working with other people who don't share a network.

You might want to ditch both for even more complex programs for working with data and look into databases instead. Neither can replace or even come close to databases' data processing and management.

1. Supercharge Google Sheets spreadsheets with these engineering-specific formulae

Unlike average users of spreadsheets like Google Sheets or Excel, engineers often need more advanced mathematical and statistical functions. While it is impossible to list all the formulae to cover all requirements, here are some of the most commonly used.

1. BIN2DEC()

Formula syntax: BIN2DEC(signed_binary_number)

What does it do?: Converts a signed binary number to decimal format.

2. BIN2HEX()

Formula syntax: BIN2HEX(signed_binary_number, [significant_digits])

What does it do?: Converts a signed binary number to a signed hexadecimal format.

3. BIN2OCT()

Formula syntax: BIN2OCT(signed_binary_number, [significant_digits])

What does it do?: Converts a signed binary number to a signed octal format.

4. BITAND()

Formula syntax: BITAND(value1, value2)

What does it do?: Returns the Bitwise boolean AND of two numbers.

5. BITLSHIFT()

Formula syntax: BITLSHIFT(value, shift_amount)

What does it do?: Shifts the input bits a specified number of places to the left.

6. BITOR()

Formula syntax: BITOR(value1, value2)

What does it do?: Returns the Bitwise boolean OR of 2 numbers.

7. BITRSHIFT()

Formula syntax: BITRSHIFT(value, shift_amount)

What does it do?: Shifts the bits of the input a specified number of places to the right.

8. BITXOR()

Formula syntax: BITXOR(value1, value2)

What does it do?: Bitwise XOR (exclusive OR) of 2 numbers that returns a bit of '1' if 2 bits are different and a bit of '0' otherwise.

9. COMPLEX()

Formula syntax: COMPLEX(real_part, imaginary_part, [suffix])

What does it do?: Creates a complex number given real and imaginary coefficients.

10. DEC2BIN()

Formula syntax: DEC2BIN(decimal_number, [significant_digits])

What does it do?: Converts a decimal number to signed binary format.

11. DEC2HEX()

Formula syntax: DEC2HEX(decimal_number, [significant_digits])

What does it do?: Converts a decimal number to a signed hexadecimal format.

12. DEC2OCT()

Formula syntax: DEC2OCT(decimal_number, [significant_digits])

What does it do?: Converts a decimal number to signed octal format.

13. DELTA()

Formula syntax: DELTA(number1, [number2])

What does it do?: Compares two numeric values, returning 1 if they're equal.

14. ERF()

Formula syntax: ERF(lower_bound, [upper_bound])

What does it do?: The ERF function returns the integral of the Gauss error function over an interval of values.

15. GESTEP()

Formula syntax: GESTEP(value, [step])

What does it do?: Returns 1 if the rate is strictly greater than or equal to the provided step value or 0 otherwise. If no step value is provided, the default value of 0 will be used.

16. HEX2BIN()

Formula syntax: HEX2BIN(signed_hexadecimal_number, [significant_digits])

What does it do?: Converts a signed hexadecimal number to a signed binary format.

17. HEX2DEC()

Formula syntax: HEX2DEC(signed_hexadecimal_number)

What does it do?: Converts a signed hexadecimal number to decimal format.

18. HEX2OCT()

Formula syntax: HEX2OCT(signed_hexadecimal_number, significant_digits)

What does it do?: Converts a signed hexadecimal number to a signed octal format.

19. IMABS()

Formula syntax: IMABS(number)

What does it do?: Returns the absolute value of a complex number.

20. IMAGINARY()

Formula syntax: IMAGINARY(complex_number)

What does it do?: It returns the imaginary coefficient of a complex number.

21. IMARGUMENT()

Formula syntax: IMARGUMENT(number)

What does it do?: The IMARGUMENT function returns the angle (also known as the argument or \theta) of the given complex number in radians.

22. IMCONJUGATE()

Formula syntax: IMCONJUGATE(number)

What does it do?: Returns the complex conjugate of a number.

23. IMCOS()

Formula syntax: IMCOS(number)

What does it do?: The IMCOS function returns the cosine of the given complex number.

24. IMCOSH()

Formula syntax: IMCOSH(number)

What does it do?: Returns the hyperbolic cosine of the given complex number. For example, a given complex number x+yi returns cosh(x+yi).

25. IMCOT()

Formula syntax: IMCOT(number)

What does it do?: Returns the cotangent of the given complex number. For example, a given complex number x+yi returns cot(x+yi).

26. IMCOTH()

Formula syntax: IMCOTH(number)

What does it do?: Returns the hyperbolic cotangent of the given complex number. For example, a given complex number x+yi returns coth(x+yi).

27. IMCSC()

Formula syntax: IMCSC(number)

What does it do?: Returns the cosecant of the given complex number.

28. IMCSCH()

Formula syntax: IMCSCH(number)

What does it do?: Returns the hyperbolic cosecant of the given complex number. For example, a given complex number x+yi returns csch(x+yi).

29. IMDIV()

Formula syntax: IMDIV(dividend, divisor)

What does it do?: Returns one complex number divided by another.

30. IMEXP()

Formula syntax: IMEXP(exponent)

What does it do?: Returns Euler's number, e (~2.718) raised to a complex power.

31. IMLOG()

Formula syntax: IMLOG(value, base)

What does it do?: Returns the logarithm of a complex number for a specified base.

32. IMLOG10()

Formula syntax: IMLOG10(value)

What does it do?: Returns the logarithm of a complex number with base 10.

33. IMLOG2()

Formula syntax: IMLOG2(value)

What does it do?: Returns the logarithm of a complex number with base 2.

34. IMPRODUCT()

Formula syntax: IMPRODUCT(factor1, [factor2, ...])

What does it do?: Returns the result of multiplying a series of complex numbers together.

35. IMREAL()

Formula syntax: IMREAL(complex_number)

What does it do?: Returns the actual coefficient of a complex number.

36. IMSEC()

Formula syntax: IMSEC(number)

What does it do?: Returns the secant of the given complex number. For example, a given complex number x+yi returns sec(x+yi).

37. IMSECH()

Formula syntax: IMSECH(number)

What does it do?: Returns the hyperbolic secant of the given complex number. For example, a given complex number x+yi returns sech(x+yi).

38. IMSIN()

Formula syntax: IMSIN (number)

What does it do?: Returns the sine of the given complex number.

39. IMSINH()

Formula syntax: IMSINH(number)

What does it do?: Returns the hyperbolic sine of the given complex number. For example, a given complex number x+yi returns sinh(x+yi).

40. IMSUB()

Formula syntax: IMSUB(first_number, second_number)

What does it do?: Returns the difference between two complex numbers.

41. IMSUM()

Formula syntax: IMSUM(value1, [value2, ...])

What does it do?: Returns the sum of a series of complex numbers.

42. IMTAN()

Formula syntax: IMTAN(number)

What does it do?: Returns the tangent of the given complex number.

43. IMTANH()

Formula syntax: IMTANH(number)

What does it do?: Returns the hyperbolic tangent of the given complex number. For example, a given complex number x+yi returns tanh(x+yi).

44. OCT2BIN()

Formula syntax: OCT2BIN(signed_octal_number, [significant_digits])

What does it do?: Converts a signed octal number to a signed binary format.

45. OCT2DEC()

Formula syntax: OCT2DEC(signed_octal_number)

What does it do?: Converts a signed octal number to decimal format.

46. OCT2HEX()

Formula syntax: OCT2HEX(signed_octal_number, [significant_digits])

What does it do?: Converts a signed octal number to a signed hexadecimal format.

2. Useful lookup formulae for Google Sheets

If you have relatively large data sets or want a quick and easy way to process the data to get insights, then you'll need some means of searching or cross-referencing certain things. The following formulae will prove invaluable if you find yourself in this position.

For example, LOOKUP() (number 10 below) is one you'll likely use repeatedly.

1. ADDRESS()

Formula syntax: ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

What does it do?: Returns a cell reference as a string.

2. CHOOSE()

Formula syntax: CHOOSE(index, choice1, [choice2, ...])

What does it do?: Returns an element from a list of choices based on the index.

3. COLUMN()

Formula syntax: COLUMN([cell_reference])

What does it do?: Returns the column number of a specified cell with 'A=1`.

4. COLUMNS()

Formula syntax: COLUMNS(range)

What does it do?: Returns the number of columns in a specified array or range.

5. FORMULATEXT()

Formula syntax: FORMULATEXT(cell)

What does it do?: Returns the formula as a string.

6. GETPIVOTDATA()

Formula syntax: GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, ...], [pivot_item, ...]

What does it do?: Extracts an aggregated value from a pivot table corresponding to the specified row and column headings.

7. HLOOKUP()

Formula syntax: HLOOKUP(search_key, range, index, [is_sorted])

What does it do?: Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.

8. INDEX()

Formula syntax: INDEX(reference, [row], [column])

What does it do?: Returns the content of a cell, specified by row and column offset.

9. INDIRECT()

Formula syntax: INDIRECT(cell_reference_as_string, [is_A1_notation])

What does it do?: Returns a cell reference specified by a string.

10. LOOKUP()

Formula syntax: LOOKUP(search_key, search_range|search_result_array, [result_range])

What does it do?: Looks through a row or column for a key and returns the cell's value in a result range located in the same position as the search row or column.

11. MATCH()

Formula syntax: MATCH(search_key, range, [search_type])

What does it do?: Returns the relative position of an item in a range that matches a specified value.

12. OFFSET()

Formula syntax: OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

What does it do?: Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

13. ROW()

Formula syntax: ROW([cell_reference])

What does it do?: Returns the row number of a specified cell.

14. ROWS()

Formula syntax: ROWS(range)

What does it do?: Returns the number of rows in a specified array or range.

15. VLOOKUP()

Formula syntax: VLOOKUP(search_key, range, index, [is_sorted])

What does it do?: Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

16. XLOOKUP()

Formula syntax: XLOOKUP(search_key, lookup_range, result_range, missing_value, [match_mode], [search_mode])

What does it do?: Returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.

3. Manipulate, extract, and generally make text values dance to your tune with these brilliant text-specific formulae

If your data sets contain strings (text-only) fields or "free text" inputs, there will be times when your need to clean up or analyze this kind of data too. Things like spelling mistakes, typos, etc, are a perennial hazard, but they can be sorted out programmatically if you are smart with the formulae (or combination thereof) you use.

While this is more of a problem for software engineers (especially those involving databases), keeping any report outputs free of basic errors is always lovely.

This can be a nightmare to do manually, so why not get Google Sheets to do it for you?

1. CHAR

Formula syntax: CHAR(table_number)

What does it do?: Convert a number into a character according to the current Unicode table.

2. CLEAN

Formula syntax: CLEAN(text)

What does it do?: Returns the text with the non-printable ASCII characters removed.

3. CONCATENATE

Formula syntax: CONCATENATE(string1, [string2, ...])

What does it do?: Appends strings to one another.

4. DOLLAR

Formula syntax: DOLLAR(number, [number_of_places])

What does it do?: Formats a number into the locale-specific currency format.

5. EXACT

Formula syntax: EXACT(string1, string2)

What does it do?: Tests whether two strings are identical.

6. FIND

Formula syntax: FIND(search_for, text_to_search, [starting_at])

What does it do?: Returns the position at which a string is first found within the text.

7. FINDB

Formula syntax: FINDB(search_for, text_to_search, [starting_at])

What does it do?: Returns the position at which a string is first found within the text, counting each double character as 2.

8. FIXED

Formula syntax: FIXED(number, [number_of_places], [suppress_separator])

What does it do?: Formats a number with a fixed number of decimal places.

9. JOIN

Formula syntax: JOIN(delimiter, value_or_array1, [value_or_array2, ...])

What does it do?: Concatenates the elements of one or more one-dimensional arrays using a specified delimiter.

10. LEFT

Formula syntax: LEFT(string, [number_of_characters])

What does it do?: Returns a substring from the beginning of a specified string.

11. LEFTB

Formula syntax: LEFTB(string, num_of_bytes)

What does it do?: Returns the left portion of a string up to a certain number of bytes.

12. LEN

Formula syntax: LEN(text)

What does it do?: Returns the length of a string.

13. LENB

Formula syntax: LENB(string)

What does it do?: Returns the length of a string in bytes.

14. LOWER

Formula syntax: LOWER(text)

What does it do?: Converts a specified string to lowercase.

15. MID

Formula syntax: MID(string, starting_at, extract_length)

What does it do?: Returns a segment of a string.

16. MIDB

Formula syntax: MIDB(string)

What does it do?: Returns a string section starting at a given character and up to a specified number of bytes.

17. PROPER

Formula syntax: PROPER(text_to_capitalize)

What does it do?: Capitalizes each word in a specified string.

18. REGEXEXTRACT

Formula syntax: REGEXEXTRACT(text, regular_expression)

What does it do?: Extracts matching substrings according to a regular expression.

19. REGEXMATCH

Formula syntax: REGEXMATCH(text, regular_expression)

What does it do?: Whether a piece of text matches a regular expression.

20. REGEXREPLACE

Formula syntax: REGEXREPLACE(text, regular_expression, replacement)

What does it do?: Replaces part of a text string with a different text string using regular expressions.

21. REPLACE

Formula syntax: REPLACE(text, position, length, new_text)

What does it do?: Replaces part of a text string with a different text string.

22. REPLACEB

Formula syntax: REPLACEB(text, position, num_bytes, new_text)

What does it do?: Replaces part of a text string, based on a number of bytes, with a different text string.

23. REPT

Formula syntax: REPT(text_to_repeat, number_of_repetitions)

What does it do?: Returns specified text repeated a number of times.

24. RIGHT

Formula syntax: RIGHT(string, [number_of_characters])

What does it do?: Returns a substring from the end of a specified string.

25. RIGHTB

Formula syntax: RIGHTB(string, num_of_bytes)

What does it do?: Returns the right portion of a string up to a certain number of bytes.

26. ROMAN

Formula syntax: ROMAN(number, [rule_relaxation])

What does it do?: Formats a number in Roman numerals.

27. SEARCH

Formula syntax: SEARCH(search_for, text_to_search, [starting_at])

What does it do?: Returns the position at which a string is first found within the text.

28. SEARCHB

Formula syntax: SEARCHB(search_for, text_to_search, [starting_at])

What does it do?: Returns the position at which a string is first found within the text, counting each double character as 2.

29. SPLIT

Formula syntax: SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

What does it do?: Divides text around a specified character or string and puts each fragment into a separate cell in the row.

30. SUBSTITUTE

Formula syntax: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

What does it do?: Replaces existing text with new text in a string.

31. T

Formula syntax: T(value)

What does it do?: Returns string arguments as text.

32. TEXT

Formula syntax: TEXT(number, format)

What does it do?: Converts a number into text according to a specified format.

33. TEXTJOIN

Formula syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

What does it do?: Combine the text from multiple strings and/or arrays with a specifiable delimiter separating the texts.

34. TRIM

Formula syntax: TRIM(text)

What does it do?: Removes leading and trailing spaces in a specified string.

35. UNICHAR

Formula syntax: UNICHAR(number)

What does it do?: Returns the Unicode character for a number.

36. UNICODE

Formula syntax: UNICODE(text)

What does it do?: Returns the decimal Unicode value of the first character of the text.

37. UPPER

Formula syntax: UPPER(text)

What does it do?: Converts a specified string to uppercase.

38. VALUE

Formula syntax: VALUE(text)

What does it do?: Converts a string in any date, time, or number format that Google Sheets understands into a number.

4. Useful filtering formulae for Google Sheets

1. FILTER

Formula syntax: FILTER(range, condition1, [condition2])

What does it do?: Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions.

2. SORT

Formula syntax: SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2])

What does it do?: Sorts the rows of a given array or range by the values in one or more columns.

3. SORTN

Formula syntax: SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)

What does it do?: Returns the first n items in a data set after performing a sort.

4. UNIQUE

Formula syntax: UNIQUE(range)

What does it do?: Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order they first appear in the source range.

5. Number crunching, error checking, etc

1. AND

Formula syntax: AND(logical_expression1, [logical_expression2, ...])

What does it do?: Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

2. FALSE

Formula syntax: FALSE()

What does it do?: Returns the logical value "FALSE"

3. IF

Formula syntax: IF(logical_expression, value_if_true, value_if_false)

What does it do?: Returns one value if a logical expression is "TRUE" and another if it is "FALSE"

4. IFERROR

Formula syntax: IFERROR(value, [value_if_error])

What does it do?: Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.

5. IFNA

Formula syntax: IFNA(value, value_if_na)

What does it do?: Evaluates a value. If the value is an #N/A error, return the specified value.

6. IFS

Formula syntax: IFS(condition1, value1, [condition2, value2], …)

What does it do?: Evaluates multiple conditions and returns a value corresponding to the first true condition.

7. LAMBDA

Formula syntax: LAMBDA(name, formula_expression)

What does it do?: Creates and returns a custom function with a set of names and a formula_expression that uses them. To calculate the formula_expression, you can call the returned function with as many values as the name declares.

8. NOT

Formula syntax: NOT(logical_expression)

What does it do?: Returns the opposite of a logical value - "NOT(TRUE)" returns "FALSE," "NOT(FALSE)" returns "TRUE"

9. OR

Formula syntax: OR(logical_expression1, [logical_expression2, ...])

What does it do?: Returns true if any of the provided arguments are logically true and false if all of the provided arguments are logically false.

10. SWITCH

Formula syntax: SWITCH(expression, case1, value1, [default or case2, value2], …)

What does it do?: Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.

11. TRUE

Formula syntax: TRUE()

What does it do?: Returns the logical value "TRUE"

12. XOR

Formula syntax: XOR(logical_expression1, [logical_expression2, ...])

What does it do?: The XOR function returns TRUE if an odd number of the provided arguments are logically true and FALSE otherwise.

5. Other data processing formulae

1. DAVERAGE

Formula syntax: DAVERAGE(database, field, criteria)

What does it do?: Returns the average of a set of values selected from a database table-like array or range using a SQL-like query.

2. DCOUNT

Formula syntax: DCOUNT(database, field, criteria)

What does it do?: Counts numeric values selected from a database table-like array or range using a SQL-like query.

3. DCOUNTA

Formula syntax: DCOUNTA(database, field, criteria)

What does it do?: Counts values, including text, selected from a database table-like array or range using a SQL-like query.

4. DGET

Formula syntax: DGET(database, field, criteria)

What does it do?: Returns a single value from a database table-like array or range using a SQL-like query.

5. DMAX

Formula syntax: DMAX(database, field, criteria)

What does it do?: Returns the maximum value selected from a database table-like array or range using a SQL-like query.

6. DMIN

Formula syntax: DMIN(database, field, criteria)

What does it do?: Returns the minimum value selected from a database table-like array or range using a SQL-like query.

7. DPRODUCT

Formula syntax: DPRODUCT(database, field, criteria)

What does it do?: Returns the product of values selected from a database table-like array or range using a SQL-like query.

8. DSTDEV

Formula syntax: DSTDEV(database, field, criteria)

What does it do?: Returns the standard deviation of a population sample selected from a database table-like array or range using a SQL-like query.

9. DSTDEVP

Formula syntax: DSTDEVP(database, field, criteria)

What does it do?: Returns the standard deviation of an entire population selected from a database table-like array or range using a SQL-like query.

10. DSUM

Formula syntax: DSUM(database, field, criteria)

What does it do?: Returns the sum of values selected from a database table-like array or range using a SQL-like query.

11. DVAR

Formula syntax: DVAR(database, field, criteria)

What does it do?: Returns the variance of a population sample selected from a database table-like array or range using a SQL-like query.

12. DVARP

Formula syntax: DVARP(database, field, criteria)

What does it do?: Returns the variance of an entire population selected from a database table-like array or range using a SQL-like query.

And that is your lot for today.

Google Sheets is an excellent alternative to Microsoft Excel and is also free. While it is more limited in its capabilities than Excel, many of the functions and formulae you can use in Excel can also be used (to some extent) in Google Sheets.

We've included a fair number of them above, but rest assured these are just the tip of the iceberg. If you want to see more, check out Google's official guide page. Happy days.