Common SQL syntax used in Microsoft Excel

When it comes to data analytics, SQL is one language you will always come across. SQL is a standard language for accessing and manipulating databases. It uses simple sets of English words to interact with databases. Thus, it is easy to read, write, and pick up.

Today, we will look at some common SQL syntax and the Microsoft Excel alternative function.

SQL syntaxes and their Microsoft Excel alternatives

Function/Syntax name	SQL syntax	Excel function
SUM returns the sum of values supplied.	Select SUM (Country) from Country	SUM(number1,[number2],…)
COUNT returns the number of rows or count of records.	Select COUNT (Country) from Country	COUNT(value1, [value2], …) COUNTA(value1, [value2], …)
FILTER returns a subset of the data.	SELECT * FROM Sales_orders WHERE Country = ’Singapore’	FILTER(array,include,[if_empty])
SORT arranges your data either alphabetically or in an ascending or descending order.	SELECT * FROM Sales_orders WHERE Country_’Singapore’ ORDER BY Quantity	=SORT(array,[sort_index],[sort_order],[by_col])
UPPER converts the data to uppercase.	Select UPPER (Country) from Country	UPPER(text)
LOWER convert the data to lowercase.	Select LOWER (Country) from Country	LOWER(text)
NOW returns the date and time.	Select NOW();	NOW()
CONCATENATE merges two or more strings together.	SELECT Age, CONCAT(Last_name, ‘ ‘, First_name) AS Full_name FROM Sales_orders’	CONCATENATE(text1, [text2], …)
VLOOKUP is used to find things in a table or a range by row.	SELECT P.*, S.Customer_name, FROM Sales_orders JOIN Sales_Products P ON P.Product_code =S.Product_code	VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
IF allows you to make logical comparisons between a value and what you expect.	SELECT Order_ID, Total_sales, CASE WHEN Total_sales <= 1000 THEN ‘low’ WHEN Total_sales > 1000 AND Total_sales <= 5000 THEN ‘Medium’ WHEN Total_sales > 5000 THEN ‘High’ ELSE END sales_value FROM Sales_orders’	IF(logical_test, value_if_true, [value_if_false])
SUMIF returns the sum of the values in a range that meets the specific criteria.	SELECT SUM(total_sales) AS total_sales FROM Sales_orders WHERE Country_’Singapore’	SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
LEN returns the number of characters in a text.	SELECT S.Customer_name, length(Customer_name) AS Len FROM Sales_orders	LEN(text)
FIND return the position of a specified character within a text.	SELECT Customer_name, Position( ‘T’ IN Customer_name) FROM Sales_orders	FIND(find_text, within_text, [start_num])
LEFT extract characters from the left most side of a text	SELECT Customer_name, SUNSTR(Customer_name,1,3) AS Suffix FROM Sales_orders	LEFT(text)
PIVOT rotates rows in a table by turning row values into multiple columns.	SELECT (ColumnNames) FROM (TableName) PIVOT ( AggregateFunction(ColumnToBeAggregated) FOR PivotColumn IN (PivotColumnValues) ) AS (Alias)	Pivot table or Pivot in Power Query

Final Thought

This blog looks at some functions SQL, and Excel have in common and what these functions do.

To learn more about Microsoft Excel or SQL, you should sign up for our classes, keep up with the blog, and subscribe to our YouTube channel.