Excel formulas and corresponding Google Data Studio formulas.

 






Category Original Function Excel Formula Equivalent Looker Studio (Google Data Studio) Equivalent Notes
Aggregation AVG(X) `AVERAGE(X)` `AVG(X)`
Aggregation COUNT(X) `COUNT(X)` or `COUNTA(X)` `COUNT(X)` Excel: `COUNT` counts numbers, `COUNTA` counts non-blank cells.
Aggregation COUNT_DISTINCT(X) `SUM(--(MATCH(UNIQUE(X),UNIQUE(X),0)=ROW(UNIQUE(X))-MIN(ROW(UNIQUE(X)))+1))` (Array formula for older Excel) or `COUNTA(UNIQUE(X))` `COUNT_DISTINCT(X)` Excel: `UNIQUE` function is available in newer Excel versions (Microsoft 365). Older versions require array formulas.
Aggregation MAX(X) `MAX(X)` `MAX(X)`
Aggregation MEDIAN(X) `MEDIAN(X)` `MEDIAN(X)`
Aggregation MIN(X) `MIN(X)` `MIN(X)`
Aggregation PERCENTILE(X,N) `PERCENTILE.INC(X, N/100)` or `PERCENTILE.EXC(X, N/100)` `PERCENTILE(X, N)` Excel: `N` is a decimal between 0 and 1. `INC` is inclusive, `EXC` is exclusive. Looker Studio: `N` is an integer between 1 and 99.
Aggregation STDDEV(X) `STDEV.S(X)` (sample) or `STDEV.P(X)` (population) `STDDEV(X)`
Aggregation SUM(X) `SUM(X)` `SUM(X)`
Aggregation VARIANCE(X) `VAR.S(X)` (sample) or `VAR.P(X)` (population) `VAR(X)`
Arithmetic ABS(X) `ABS(X)` `ABS(X)`
Arithmetic ACOS(X) `ACOS(X)` `ACOS(X)`
Arithmetic ASIN(X) `ASIN(X)` `ASIN(X)`
Arithmetic ATAN(X) `ATAN(X)` `ATAN(X)`
Arithmetic CEIL(X) `CEILING.MATH(X)` or `ROUNDUP(X,0)` `CEIL(X)` Excel: `CEILING.MATH` is more robust. `ROUNDUP(X,0)` rounds up to the nearest integer.
Arithmetic COS(X) `COS(X)` `COS(X)`
Arithmetic FLOOR(X) `FLOOR.MATH(X)` or `ROUNDDOWN(X,0)` `FLOOR(X)` Excel: `FLOOR.MATH` is more robust. `ROUNDDOWN(X,0)` rounds down to the nearest integer.
Arithmetic LOG(X) (base 2) `LOG(X, 2)` `LOG(X, 2)`
Arithmetic LOG10(X) `LOG10(X)` `LOG10(X)`
Arithmetic NARY_MAX(X, Y [,Z]*) `MAX(X, Y, [Z], ...)` `GREATEST(X, Y, [Z], ...)` Excel's `MAX` function can take multiple arguments. Looker Studio uses `GREATEST`.
Arithmetic NARY_MIN(X, Y [,Z]*) `MIN(X, Y, [Z], ...)` `LEAST(X, Y, [Z], ...)` Excel's `MIN` function can take multiple arguments. Looker Studio uses `LEAST`.
Arithmetic POWER(X, Y) `POWER(X, Y)` or `X^Y` `POWER(X, Y)`
Arithmetic ROUND(X, Y) `ROUND(X, Y)` `ROUND(X, Y)`
Arithmetic SIN(X) `SIN(X)` `SIN(X)`
Arithmetic SQRT(X) `SQRT(X)` `SQRT(X)`
Arithmetic TAN(X) `TAN(X)` `TAN(X)`
Conditional CASE WHEN condition THEN result [ELSE else_result] END `IF(condition, result, IF(condition2, result2, ..., else_result))` (nested IFs) `CASE WHEN condition THEN result [WHEN condition THEN result] [...] [ELSE else_result] END` Excel uses nested `IF` statements. Looker Studio has a direct `CASE` statement.
Conditional CASE input_expression WHEN expr_to_match THEN result [ELSE result] END `SWITCH(input_expression, expr_to_match1, result1, [expr_to_match2, result2, ...], [else_result])` (Excel 2016+) `CASE input_expression WHEN expression_to_match THEN result [WHEN expression_to_match THEN result] [...] [ELSE result] END` Excel's `SWITCH` function is for direct matching. Older Excel versions would require nested `IF` statements.
Conditional COALESCE(field_expression[,field_expression, ...]) `IFERROR(field_expression1, IFERROR(field_expression2, ...))` or `IF(ISBLANK(field1), IF(ISBLANK(field2), ..., field2), field1)` `COALESCE(field_expression[,field_expression, ...])` Excel doesn't have a direct `COALESCE`. Nested `IFERROR` or `IF(ISBLANK())` simulates it.
Conditional IF(condition, true_result, false_result) `IF(condition, true_result, false_result)` `IF(condition, true_result, false_result)`
Conditional IFNULL(input_expression, null_result) `IF(ISBLANK(input_expression), null_result, input_expression)` or `IFERROR(input_expression, null_result)` `IFNULL(input_expression, null_result)` Excel: `ISBLANK` for truly empty cells, `IFERROR` for cells with errors.
Conditional NULLIF(input_expression, expression_to_match) `IF(input_expression = expression_to_match, "", input_expression)` `NULLIF(input_expression, expression_to_match)` Excel returns an empty string `""` for NULL.
Date CURRENT_DATE([time_zone]) `TODAY()` `CURRENT_DATE()` Excel's `TODAY()` is volatile. Looker Studio's `CURRENT_DATE()` can take a timezone argument.
Date CURRENT_DATETIME([time_zone]) `NOW()` `CURRENT_DATETIME()` Excel's `NOW()` is volatile. Looker Studio's `CURRENT_DATETIME()` can take a timezone argument.
Date DATE(year, month, day) `DATE(year, month, day)` `DATE(year, month, day)`
Date DATE_DIFF(X, Y) (days) `X - Y` or `DATEDIF(Y, X, "d")` `DATETIME_DIFF(X, Y, DAY)` Excel: `DATEDIF` is a hidden function. Simple subtraction works for days.
Date DATE_FROM_UNIX_DATE(integer) `DATE(1970,1,1) + integer` `DATE_FROM_UNIX_DATE(integer)` Excel dates are numbers where 1 = Jan 1, 1900. Unix epoch is Jan 1, 1970.
Date DATETIME(year, month, day, hour, minute, second) `DATE(year, month, day) + TIME(hour, minute, second)` `DATETIME(year, month, day, hour, minute, second)`
Date DATETIME_ADD(datetime_expression, INTERVAL integer part) `datetime_expression + integer` (for days), `EDATE(datetime, months)` (for months), etc. `DATETIME_ADD(datetime_expression, INTERVAL integer part)` Excel uses different functions for adding different date parts.
Date DATETIME_DIFF(date_expression, date_expression, part) `DATEDIF(start_date, end_date, "unit")` or simple subtraction. `DATETIME_DIFF(date_expression, date_expression, part)` Excel's `DATEDIF` unit can be "y", "m", "d", "ym", "yd", "md".
Date DATETIME_SUB(datetime_expression, INTERVAL integer part) `datetime_expression - integer` (for days), `EDATE(datetime, -months)` (for months), etc. `DATETIME_SUB(datetime_expression, INTERVAL integer part)` Excel uses different functions for subtracting different date parts.
Date DATETIME_TRUNC(date_expression, part) `DATE(YEAR(date),MONTH(date),1)` (for month), `FLOOR(date,1)` (for day) `DATETIME_TRUNC(date_expression, part)` Excel requires specific formulas for each truncation level.
Date DAY(date_expression) `DAY(date_expression)` `DAY(date_expression)`
Date EXTRACT(part FROM date_expression) `YEAR(date)`, `MONTH(date)`, `DAY(date)`, `HOUR(time)`, `MINUTE(time)`, `SECOND(time)` `EXTRACT(part FROM date_expression)` Excel uses separate functions for each part.
Date FORMAT_DATETIMEReturns a formatted date string. `TEXT(datetime_expression, "format_string")` `FORMAT_DATETIME(format_string, datetime_expression)` Excel's format strings differ from SQL/Looker Studio.
Date HOUR(datetime_expression) `HOUR(datetime_expression)` `HOUR(datetime_expression)`
Date MINUTE(datetime_expression) `MINUTE(datetime_expression)` `MINUTE(datetime_expression)`
Date MONTH(date_expression) `MONTH(date_expression)` `MONTH(date_expression)`
Date PARSE_DATE(format_string, text) `DATEVALUE(text)` or complex string parsing with `DATE` `PARSE_DATE(format_string, text)` Excel's `DATEVALUE` works for recognized date strings. Custom formats require manual parsing.
Date PARSE_DATETIME(format_string, text) `DATEVALUE(TEXT_DATE_PART) + TIMEVALUE(TEXT_TIME_PART)` or complex string parsing `PARSE_DATETIME(format_string, text)` Excel requires combining date and time parsing.
Date QUARTER(date_expression) `ROUNDUP(MONTH(date_expression)/3,0)` `QUARTER(date_expression)`
Date SECONDReturns the seconds component of a given date and time. `SECOND(datetime_expression)` `SECOND(datetime_expression)`
Date TODATEReturns a formatted compatibility mode Date. `TEXT(X, "Output Format")` `TODATE(X, Input Format, Output Format)` Excel's `TEXT` function formats a value as text.
Date TODAY([time_zone]) `TODAY()` `TODAY()`
Date UNIX_DATE(date_expression) `INT(date_expression - DATE(1970,1,1))` `UNIX_DATE(date_expression)` Excel's date system starts from Jan 1, 1900.
Date WEEK(Date) `WEEKNUM(Date, [return_type])` `WEEK(Date)` Excel's `return_type` specifies week start day (e.g., 1 for Sunday, 2 for Monday).
Date WEEKDAY(Date) `WEEKDAY(Date, [return_type])` `WEEKDAY(Date)` Excel's `return_type` specifies the numbering system (e.g., 1 for Sunday=1, 2 for Monday=1).
Date YEAR(Date) `YEAR(Date)` `YEAR(Date)`
Date YEARWEEK(Date) `YEAR(Date)&TEXT(WEEKNUM(Date,1),"00")` `YEARWEEK(Date)` Excel requires concatenation of year and formatted week number.
Geo TOCITY(X [,Input Format]) Requires external data, Power Query, or VBA. No direct equivalent. Neither Excel formulas nor Looker Studio functions have built-in direct geographical name lookups. Requires pre-populated lookup tables or external services.
Geo TOCONTINENT(X [,Input Format]) Requires external data, Power Query, or VBA. No direct equivalent.
Geo TOCOUNTRY(X [,Input Format]) Requires external data, Power Query, or VBA. No direct equivalent.
Geo TOREGION(X [,Input Format]) Requires external data, Power Query, or VBA. No direct equivalent.
Geo TOSUBCONTINENT(X [,Input Format]) Requires external data, Power Query, or VBA. No direct equivalent.
Miscellaneous CAST(field_expression AS TYPE) `VALUE(X)` (to number), `TEXT(X, "format")` (to text), `DATEVALUE(X)` (to date) `CAST(field_expression AS TYPE)` Excel uses specific functions for type conversion. Looker Studio has a direct `CAST`.
Miscellaneous HYPERLINK(URL, link label) `HYPERLINK(URL, link_label)` Set field type to URL. `HYPERLINK` function for custom display. Looker Studio can automatically detect URLs or use the `HYPERLINK` function for more control over display text.
Miscellaneous IMAGE(Image URL, [Alternative Text]) `IMAGE(Image URL, [alt_text], [sizing], [height], [width])` (Microsoft 365) Set field type to Image. `IMAGE` function for custom display. Excel's `IMAGE` function is newer. Looker Studio can automatically detect image URLs or use the `IMAGE` function.
Miscellaneous NATIVE_DIMENSION("JSON_VALUE('{"name": "Dana"}', '$.name')","STRING") Requires Power Query (M language) or VBA for JSON parsing. `JSON_EXTRACT(expression, path)` Excel formulas don't natively parse JSON. Looker Studio has `JSON_EXTRACT`.
Text CONCAT(X, Y) `CONCAT(X, Y)` (Excel 2016+) or `X & Y` `CONCAT(X, Y)` Excel's `&` operator is a common way to concatenate.
Text CONTAINS_TEXT(X, text) (case-sensitive) `ISNUMBER(FIND(text, X))` `CONTAINS_TEXT(X, text)` or `REGEXP_CONTAINS(X, text)` Excel's `FIND` is case-sensitive. `SEARCH` is case-insensitive.
Text ENDS_WITH(X, text) (case-sensitive) `RIGHT(X, LEN(text)) = text` `ENDS_WITH(X, text)`
Text LEFT_TEXT(X, length) `LEFT(X, length)` `LEFT_TEXT(X, length)`
Text LENGTH(X) `LEN(X)` `LENGTH(X)`
Text LOWER(X) `LOWER(X)` `LOWER(X)`
Text REGEXP_CONTAINS(X, regular_expression) Requires VBA (Regex object) or complex string functions. `REGEXP_CONTAINS(X, regular_expression)` Excel formulas do not natively support full regular expressions. Looker Studio has direct regex functions.
Text REGEXP_EXTRACT(X, regular_expression) Requires VBA (Regex object) or complex string functions. `REGEXP_EXTRACT(X, regular_expression)`
Text REGEXP_MATCH(X, regular_expression) Requires VBA (Regex object) or complex string functions. `REGEXP_MATCH(X, regular_expression)`
Text REGEXP_REPLACE(X, regular_expression, replacement) `SUBSTITUTE(X, old_text, new_text)` or VBA (Regex object). `REGEXP_REPLACE(X, regular_expression, replacement)` Excel's `SUBSTITUTE` is for exact string replacement. Regex replacement is not native.
Text REPLACE(X, Y, Z) `REPLACE(X, start_num, num_chars, new_text)` (if replacing by position) or `SUBSTITUTE(X, Y, Z)` (if replacing by value) `REPLACE(X, Y, Z)` Excel has both `REPLACE` (by position) and `SUBSTITUTE` (by value).
Text RIGHT_TEXT(X, length) `RIGHT(X, length)` `RIGHT_TEXT(X, length)`
Text STARTS_WITH(X, text) (case-sensitive) `LEFT(X, LEN(text)) = text` `STARTS_WITH(X, text)`
Text SUBSTR(X, start index, length) `MID(X, start_num, num_chars)` `SUBSTR(X, start index, length)` `start_num` is 1-based in Excel.
Text TRIM(X) `TRIM(X)` `TRIM(X)` Excel's `TRIM` removes leading, trailing, and extra spaces between words.
Text UPPER(X) `UPPER(X)` `UPPER(X)`

Comments

Popular posts from this blog

What is Google data Studio or Looker studio

Google Data Studio functions corresponding Power BI DAX