Google Sheets functions that Microsoft Excel needs

0
3
How to Calculate a Percentage in Google Sheets

Microsoft Excel is a well-featured and commonly used spreadsheet application, but it’s not perfect. Its long-time rival, Google Sheets, offers features that Excel doesn’t have, including many useful functions. Let’s take a look!

This list is not exhaustive, and Excel may add one or more of these functions at any time. But as of this writing, April 2022, these are the Google Sheets functions we’d like to see in Microsoft Excel. As a bonus, we’ll show you how to use each of them in Google Sheets, in case you’re not familiar with them yet.

Basic calculations: ADD, MINUS, MULTIPLY and DIVIDE

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

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

To subtract the values ​​of cells A1 and A2, you need to use this formula:

=MINUS(A1,A2)

To subtract 10 from the value of cell A1, use the following formula:

=MINUS(A1,10)

To subtract 10 from 20, use the following formula: =MINUS(20,10)

=MINUS(20,10)

MINUS function in Google Sheets

Count unique values: COUNTUNIQUE

If you need to count the number of distinct values ​​in Google Sheets, the COUNTUNIQUE function is for you. Count the number of customers who ordered only once, products without inventory, or any other item for which you want to get unique values ​​using this function.

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

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

=COUNTUNIQUE(A1:A10)

COUNTUNIQUE for text

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)

COUNTUNIQUE for values

Linguistic functions: DETECTLANGUAGE and GOOGLETRANSLATE

Spreadsheets aren’t just about numbers and calculations. You may need to work on a sheet with other people who speak a different dialect. With DETECTLANGUAGE you can identify the dialect of the text and with GOOGLETRANSLATE you can translate the text into 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 by inserting your own text between the quotes:

=DETECTLANGUAGE(“Good Day”)

DETECTLANGUAGE in Google Sheets

The syntax of 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 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 into Spanish using auto-detection, you can use this formula:

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

GOOGLETRANSLATE in Google Sheets
Greater than, less than, and equal to: GT, GTE, LT, LTE, EQ

Have you ever looked for an easy way to show if a 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 : Plus grand que, syntaxe GT(valeur1, valeur2)
GTE : Supérieur ou égal à, syntaxe GTE(valeur1, valeur2)
LT : inférieur à, syntaxe LT(valeur1, valeur2)
LTE : Inférieur ou égal à, syntaxe LTE(valeur1, valeur2)
EQ : égal à, syntaxe EQ(valeur1, valeur2)

The formula for each function returns a True or False result. For example, if value1 is greater than value2, you will get the result True. Otherwise, you will get the result False.

To find out if the value of cell A1 is greater than that of cell A2, you will use this formula:

=GT(A1,A2)

To find out if the first inserted value is greater than the second, you would 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)

GREATER THAN function in Google Sheets

Insert and customize an image: IMAGE

In addition to numbers and text, you may wish to include an image in your spreadsheet. While you can easily insert an image into Google Sheets, the IMAGE feature lets you insert one from the web and customize its size.

The function syntax 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 : Ajuster l'image dans la cellule et maintenir le rapport d'aspect. C'est le mode par défaut.
2 : Étirer ou comprimer l'image pour qu'elle s'adapte à la cellule sans maintenir le rapport hauteur/largeur.
3 : Conserver l'image à sa taille d'origine.
4 : utiliser une taille personnalisée en saisissant les arguments de hauteur et de largeur en pixels.

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

=IMAGE(“https://www.howtogeek.com/wp-content/uploads/2022/02/DateOptions-GoogleSheetsCustomDateTime.png”,3)

IMAGE function with the original size

To insert the same image but using a custom size, you can use this formula replacing the URL, width and height with your own data:

=IMAGE(“https://www.howtogeek.com/wp-content/uploads/2022/02/DateOptions-GoogleSheetsCustomDateTime.png”,4,200,500)

IMAGE function with custom size

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

Add a mini graphic: SPARKLINE

You don’t always need a larger-than-life chart in your spreadsheet. Google Sheets lets you add a mini chart using the SPARKLINE feature. In Excel, you can create a sparkline using the chart function, but this function is easier and faster to implement.

The syntax is SPARKLINE(data, customizations) where only the first argument is required. To customize the chart using the second argument, such as selecting the chart type, adding color, refining the axes, etc., see our tutorial on creating a spark line in Google Sheets.

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

=SPARKLINE(B2:E2)

SPARKLINE line chart

To use a bar chart with the same range of cells, you would use the following formula:

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

SPARKLINE Column Chart

For other functions that you’ll find in Google Sheets but not in Microsoft Excel, see “How to Join Text” or “How to Reverse and Split Text.”

LEAVE A REPLY

Please enter your comment!
Please enter your name here