Login to Gigasheet > Select a Sheet > Insert Tab > select Formula.
Alternatively, you can click the + icon on the top right of your sheet.
Notable Differences with Excel/Google Sheets
Gigasheet formulas are different from formulas in a typical spreadsheet, in that they operate on the entire column of data. Gigasheet formulas apply the same operation to every row in the sheet.
Results do not automatically update as the cells generating the formula output are modified. To refresh a formula's output, choose "Edit Formula" from the column menu, and select "Refresh Calculation".
Ask AI
Gigasheet's Ask AI feature within the formula builder translates natural language requests to Gigasheet formulas. Simply turn on Ask AI in the formula builder and prompt AI with the output you'd like generated in a new column. AI will generate a formula for review, and display sample output. When ready, hit Add Column to run the formula and add a new column.
Tip: For best results refer to column names in your prompt.
Available Functions
Math/Calculations Functions
Operator
Description
Example
+
Add together two numeric values
number_column + 12
-
Subtract two numeric values
number_column - 12
*
Multiply two numeric values
number_column * col 15
/
Divide two numeric values
number_column / 100
String/Text Functions
SUBSTITUTE
DESCRIPTION
Replace all occurrences in the array of the find string with the replace string.
FORMULA
SUBSTRING(text, search_for, replace_with)
EXAMPLE FORMULA
SUBSTITUTE(company_col, "inc", "Inc.")
EXAMPLE INPUT
Gigasheet inc
EXAMPLE OUTPUT
Gigasheet Inc.
CONCAT
DESCRIPTION
Merge values from various ranges and/or strings.
FORMULA
CONCAT(value1, value2, ...)
EXAMPLE FORMULA
CONCAT(user_col, "@gigasheet.com")
EXAMPLE INPUT
support
EXAMPLE OUTPUT
support@gigasheet.com
LEFT
DESCRIPTION
Retrieve values located on the left side of a text value.
FORMULA
LEFT(text, num_characters)
EXAMPLE FORMULA
LEFT(text_col, 4)
EXAMPLE INPUT
Gigasheet Rocks
EXAMPLE OUTPUT
Giga
RIGHT
DESCRIPTION
Retrieve values located on the right side of a text value
FORMULA
RIGHT(text, num_characters)
EXAMPLE FORMULA
RIGHT(text_col, 4)
EXAMPLE INPUT
Gigasheet Rocks
EXAMPLE OUTPUT
ocks
RAND
DESCRIPTION
Generates random numbers
FORMULA
RAND()
EXAMPLE FORMULA
N/A
EXAMPLE INPUT
N/A
EXAMPLE OUTPUT
0.84727895
RANDBETWEEN
DESCRIPTION
Generate a random string of numbers between two numerical values
FORMULA
RANDBETWEEN(min, max)
EXAMPLE FORMULA
RANDBETWEEN(5.5, 10)
EXAMPLE INPUT
5.5, 10
EXAMPLE OUTPUT
6.9860978479
LENGTH
DESCRIPTION
Returns the number of characters in a text string.
FORMULA
LEN()
EXAMPLE FORMULA
LEN(text_col)
EXAMPLE INPUT
Gigasheet
EXAMPLE OUTPUT
ROUND
DESCRIPTION
Rounds to the specified number of digits
FORMULA
ROUND(numeric_col, [places])
EXAMPLE FORMULA
ROUND(2.149, 1)
EXAMPLE INPUT
2.149
EXAMPLE OUTPUT
2.1
TEXT
DESCRIPTION
Returns a plain text version of the input column
FORMULA
TEXT(num_col)
EXAMPLE FORMULA
TEXT(uid)
EXAMPLE INPUT
100
EXAMPLE OUTPUT
“100”
EXACT
DESCRIPTION
Returns true if the given two strings are exactly the same
FORMULA
EXACT(string1, string2)
EXAMPLE FORMULA
EXACT(Gigasheet, gigasheet)
EXAMPLE INPUT
gigasheet
EXAMPLE OUTPUT
false
SUMBY
DESCRIPTION
Returns the sum of the second argument binned by the value of the first.
FORMULA
SUMBY(category_col, num_col)
EXAMPLE FORMULA
SUMBY(product_category, units)
EXAMPLE INPUT
EXAMPLE OUTPUT
AVERAGEBY
DESCRIPTION
Returns the average of the second argument binned by the value of the first.
FORMULA
AVERAGEBY(category_col, num_col)
EXAMPLE FORMULA
AVERAGEBY(day_of_week, sales)
EXAMPLE INPUT
EXAMPLE OUTPUT
COUNTBY
DESCRIPTION
Returns the row count of the value in the given column.
FORMULA
COUNTBY(category_col)
EXAMPLE FORMULA
COUNTBY(region)
EXAMPLE INPUT
EXAMPLE OUTPUT
MAXBY
DESCRIPTION
Returns the max value of the second argument by the value of the first.
FORMULA
MAXBY(category_col, num_col)
EXAMPLE FORMULA
MAXBY(product_category, units)
EXAMPLE INPUT
EXAMPLE OUTPUT
MINBY
DESCRIPTION
Returns the min value of the second argument by the value of the first.
FORMULA
MINBY(category_col, num_col)
EXAMPLE FORMULA
MINBY(product_category, units)
EXAMPLE INPUT
EXAMPLE OUTPUT
PROPER
DESCRIPTION
Capitalizes the first letter of every word in the given column.
FORMULA
PROPER(text_col)
EXAMPLE FORMULA
PROPER(name)
EXAMPLE INPUT
john doe
EXAMPLE OUTPUT
John Doe
LOWER
DESCRIPTION
Lower-cases every letter in the given column.
FORMULA
LOWER(text_col)
EXAMPLE FORMULA
LOWER(url)
EXAMPLE INPUT
Www.Gigasheet.Com
EXAMPLE OUTPUT
www.gigasheet.com
UPPER
DESCRIPTION
Upper-cases every letter in the given column.
FORMULA
UPPER(text_col)
EXAMPLE FORMULA
UPPER(address)
EXAMPLE INPUT
john doe
EXAMPLE OUTPUT
John Doe
YEAR
DESCRIPTION
Returns the year from a datetime as an integer
FORMULA
YEAR(datetime_col)
EXAMPLE FORMULA
YEAR(datetime_col)
EXAMPLE INPUT
2021-04-22 1:02:03
EXAMPLE OUTPUT
2021
MONTH
DESCRIPTION
Returns the month from a datetime as an integer
FORMULA
MONTH(datetime_col)
EXAMPLE FORMULA
MONTH(datetime_col)
EXAMPLE INPUT
2021-04-22 1:02:03
EXAMPLE OUTPUT
4
DAY
DESCRIPTION
Returns the day from a datetime as an integer
FORMULA
DAY(datetime_col)
EXAMPLE FORMULA
DAY(datetime_col)
EXAMPLE INPUT
2021-04-22 1:02:03
EXAMPLE OUTPUT
DAYOFWEEK
DESCRIPTION
Returns the day of week from a datetime as an integer, starting with Monday as 1
FORMULA
DAYOFWEEK(datetime_col)
EXAMPLE FORMULA
DAYOFWEEK(datetime_col)
EXAMPLE INPUT
2021-04-22 1:02:03
EXAMPLE OUTPUT
4
QUARTER
DESCRIPTION
Returns the quarter from a datetime as an integer, starting with 1 for the first quarter
FORMULA
QUARTER(datetime_col)
EXAMPLE FORMULA
QUARTER(datetime_col)
EXAMPLE INPUT
2021-04-22 1:02:03
EXAMPLE OUTPUT
3
COUNTIF
DESCRIPTION
Returns a count of the number of cells if conditions are met.
FORMULA
COUNTIF(col, if_value, num_col)
EXAMPLE FORMULA
COUNTIF(day_of_week_col "Monday"; sales_col")
EXAMPLE INPUT
Monday
EXAMPLE OUTPUT
4
SUMIF
DESCRIPTION
Returns a sum if numeric conditions are met.
FORMULA
SUMIF(col, if_value, num_col)
EXAMPLE FORMULA
SUMIF(day_of_week_col "Monday"; sales_col)
EXAMPLE INPUT
Monday
EXAMPLE OUTPUT
8
AVERAGEIF
DESCRIPTION
Returns an average if numeric conditions are met.
FORMULA
AVERAGEIF(col, if_value, num_col)
EXAMPLE FORMULA
AVERAGEIF(day_of_week_col "Monday"; sales_col)
EXAMPLE INPUT
Monday
EXAMPLE OUTPUT
4
MEAN
DESCRIPTION
Returns the average of a numeric column.
FORMULA
MEAN(num_col)
EXAMPLE FORMULA
MEAN(sales_col)
EXAMPLE INPUT
6,4,2,5
EXAMPLE OUTPUT
4.25
STDEV
DESCRIPTION
Returns the standard deviation of a numeric column.
FORMULA
STDEV(num_col)
EXAMPLE FORMULA
STDEV(sales_col)
EXAMPLE INPUT
6,4,2,5
EXAMPLE OUTPUT
1.4790199457749
CORREL
DESCRIPTION
Returns the correlation of two numeric columns.
FORMULA
CORREL(col1, col2)
EXAMPLE FORMULA
CORREL(sales_col, price_col)
EXAMPLE INPUT
EXAMPLE OUTPUT
Logic Functions
IF
DESCRIPTION
Retrieve values located on the right side of a text value.
FORMULA
IF(boolean, value_if_true, value_if_false)
EXAMPLE FORMULA
IF(temp_col > 80, "High", "Low")
EXAMPLE INPUT
100
EXAMPLE OUTPUT
High
AND
DESCRIPTION
Returns true when all given inputs are true and false otherwise
FORMULA
AND(boolean1, boolean2, [boolean3, ...])
EXAMPLE FORMULA
AND(temp_col > 80, temp_col < 90)
EXAMPLE INPUT
100
EXAMPLE OUTPUT
False
OR
DESCRIPTION
Returns true when any of the given inputs is true and false otherwise
FORMULA
OR(boolean1, boolean2, [boolean3, ...])
EXAMPLE FORMULA
OR(temp_col > 80, temp_col < 90)
EXAMPLE INPUT
100
EXAMPLE OUTPUT
True
NOT
DESCRIPTION
Returns true when the input is false, and false otherwise.
FORMULA
NOT(boolean)
EXAMPLE FORMULA
NOT(temp_col > 80)
EXAMPLE INPUT
100
EXAMPLE OUTPUT
False
ISBLANK
DESCRIPTION
Returns true if the input is null or blank, and false otherwise.
FORMULA
ISBLANK(boolean)
EXAMPLE FORMULA
ISBLANK(text_col)
EXAMPLE INPUT
something
EXAMPLE OUTPUT
False
REGEXREPLACE
DESCRIPTION
Replaces the first matching substring of the given regular expression with the given string.
FORMULA
REGEXREPLACE(text_col, regex, replacement)
EXAMPLE FORMULA
REGEXREPLACE(company, "llc", "")
EXAMPLE INPUT
EXAMPLE OUTPUT
REGEXMATCH
DESCRIPTION
Returns true if the regular expression is matched.
FORMULA
REGEXMATCH(text_col, regex)
EXAMPLE FORMULA
REGEXMATCH(company, "llc")
EXAMPLE INPUT
EXAMPLE OUTPUT
REGEXEXTRACT
DESCRIPTION
Returns the first matching substring according to the given regular expression.
FORMULA
REGEXEXTRACT(text_col, regex)
EXAMPLE FORMULA
REGEXEXTRACT(price, "[0-9]*\.[0-9]+[0-9]+")
EXAMPLE INPUT
EXAMPLE OUTPUT
Formulas Limitations and FAQ
LEFT, RIGHT, MID will not take expressions (i.e LEFT(EMAIL, FIND("@",EMAIL))
Columns as a Text Data Type can be accepted with numerical formulas but Numerical Data Types cannot be accepted where text is required for the formula.
Sheets that have identical column headers, formula will use the first matching column found.
Date formulas require the input column be a "date" data type.
Formulas can have a column as the second argument, but can't have an expression as the second argument. For example, LEFT(Column 1, (LENGTH(Column 1)-1)) is invalid. The workaround would be to input the formula LENGTH(Column 1) - 1, and then do LEFT(Column 1, new column)
Gigasheet cannot run formulas on specific "Column,Row" inputs. For example, A2, B44, D90 would be invalid.