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
Post a Comment