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)