You are currently viewing Common SQL syntax used in Microsoft Excel

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


WHERE Country = ’Singapore’

SORT arranges your data either alphabetically or in an ascending or descending order. SELECT * FROM


WHERE Country_’Singapore’

ORDER BY Quantity

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


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


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


FIND return the position of a specified character within a text. SELECT Customer_name, Position( ‘T’ IN Customer_name) FROM


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


PIVOT rotates rows in a table by turning row values into multiple columns.  SELECT (ColumnNames) 

FROM (TableName) 




   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.

This Post Has One Comment

  1. Virtue Aans

    Really nice clarification on similarities. Makes the concepts in SQL clearer. Thank you.

Leave a Reply