Ehbit ninja's blog

Our IT ninja's blog about their professional experiences with IT technologies

## 14 Google Sheets Functions That Excel Needs

Basic Calculations: ADD, MINUS, MULTIPLY, and DIVIDE

You can certainly add, subtract, multiply, and divide numbers in Microsoft Excel. However, these common equations are done with formulas and operators, not functions. Excel does offers the SUM function, which works like ADD, but having a clear and unified collection of functions to work with makes for easier organization and workflow.

The syntax for each function in Google Sheets is the same with the function name and two arguments. So, ADD(value1, value2), MINUS(value1, value2,), and so on. You can insert the values, use cell references, or enter a combination of both.

To subtract the values in cells A1 and A2, you would use this formula:

=MINUS(A1,A2)

To subtract 10 from the value in cell A1, you would use this formula:

=MINUS(A1,10)

To subtract 10 from 20, you would use this formula:

=MINUS(20,10)

Count Unique Values: CONTUNIQUE

If you ever need to count the number of distinct values in Google Sheets, then COUNTUNIQUE is your function. Count the number of customers who ordered once, products without inventory, or anything else where you want unique values using this function.

The syntax for the function is COUNTUNIQUE(value1, value2, …) where the first argument is required. You can use cell references or inserted values.

To find the count of unique customers in our range A1 through A10, we can quickly see who ordered once using this formula.

=COUNTUNIQUE(A1:A10)

To count the unique values in a list of inserted values, you can use this formula replacing the values with your own:

=COUNTUNIQUE(1,2,3,3,3,4)

Language Functions: DETECTLANGUAGE and GOOGLETRANSLATE

Spreadsheets aren’t just about numbers and calculations. You may be working on a sheet with others who speak a different dialect. With DETECTLANGUAGE you can identify the dialect of text and with GOOGLETRANSLATE you can translate text to another language.

The syntax for the first function is DETECTLANGUAGE(text) where you can enter the actual text or a cell reference.

To identify the language in cell A1, you would use the following formula:

=DETECTLANGUAGE(A1)

To identify the language of specific text, you would use this formula with your own text inserted between the quotes:

=DETECTLANGUAGE(“Bon Jour”)

The syntax for the second function is GOOGLETRANSLATE(text, from_language, to_language) where you can use a cell reference or the text for the first argument. For the language arguments, you use a two-letter abbreviation. You can also use “auto” for the from_language argument to automatically detect the source dialect.

To translate the text in cell A1 from English to Spanish, use this formula:

=GOOGLETRANSLATE(A1,”en”,”es”)

To translate a certain phrase to Spanish using auto-detect, you can use this formula:

=GOOGLETRANSLATE(“Hello”,”auto”,”es”)

Greater Than, Less Than, and Equal to: GT, GTE, LT, LTE, EQ

Have you ever wanted an easy way to display if one value is greater than, less than, or equal to another in your sheet? These functions do just that and would be great additions to Excel.

• GT: Greater Than, syntax GT(value1, value2)
• GTE: Greater Than or Equal to, syntax GTE(value1, value2)
• LT: Less Than, syntax LT(value1, value2)
• LTE: Less Than or Equal to, syntax LTE(value1, value2)
• EQ: Equal to, syntax EQ(value1, value2)

The formula for each function returns a True or False result. For instance, if value1 is greater than value2, you’ll receive the True result. If not, you’ll receive False.

To see if the value in cell A1 is greater than the one in cell A2, you would use this formula:

=GT(A1,A2)

To see if the first inserted value is greater than the second, you’d use the following formula:

=GT(4,5)

To see if the value in cell A1 is greater than the inserted value, you can use this formula:

=GT(A1,5)

Insert and Customize a Picture: IMAGE

Along with numbers and text, you may want to include an image in your spreadsheet. While you can easily insert an image in Google Sheets, the IMAGE function lets you insert one from the web and then customize its size.

The syntax for the function is IMAGE(url, mode, height, width) where only the first argument is required. Here are the options you can use for the mode argument:

• 1: Fit the image within the cell and maintain the aspect ratio. This is the default mode.
• 2: Stretch or squeeze the image to fit within the cell without maintaining the aspect ratio.
• 3: Keep the image at its original size.
• 4: Use a custom size by entering the height and width arguments in pixels.

To insert an image from the web and keep the original size, you would use this formula replacing the URL with your own:

=IMAGE(“https://images.unsplash.com/photo-1540573133985-87b6da6d54a9?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1176&q=80”,3)

To insert that same image but use a custom size, you would use this formula replacing the URL, width, and height with your own details:

=IMAGE(“https://images.unsplash.com/photo-1540573133985-87b6da6d54a9?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1176&q=80”,4,200,500)

Another great way to use the IMAGE function is to create a QR code in Google Sheets!

Add a Mini Graph: SPARKLINE

You don’t always need a chart that’s bigger than life in your spreadsheet. Google Sheets allows you to add a mini chart using the SPARKLINE function. In Excel, you can create a sparkline using the chart feature, but the function is simpler and faster to whip up.

The syntax is SPARKLINE(data, customizations) where only the first argument is required. To customize the graph with the second argument, such as selecting the chart type, adding color, fine-tuning the axes, and more.

To add a basic sparkline with data from the cell range B2 through E2, you would use this formula:

=SPARKLINE(B2:E2)

To use a bar chart with that same cell range, you’d use this formula:

=SPARKLINE(B2:E2,{“charttype”,”column”})

For even more functions you’ll find in Google Sheets but not Microsoft Excel, look at how to join text or how to do the reverse and split text.