| 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)` |
|
إرسال تعليق