Technology

#14 Google Sheets Functions That Microsoft Excel Needs

“14 Google Sheets Functions That Microsoft Excel Needs”

Closeup of a smartphone's screen showing Microsoft Office apps next to Google apps.
Tada Images/Shutterstock.com

Microsoft Excel is a full-featured, commonly used spreadsheet application, but it’s not perfect. Its longtime rival Google Sheets offers features that Excel does not, including many helpful functions. Let’s take a look!

This list isn’t all-inclusive, and Excel could add one or more of these functions at any time. But as of this writing in April 2022, these are the Google Sheets functions that we’d love to see in Microsoft Excel. As a bonus, we’ll show you how to use each one in Google Sheets in case you’re new to these.

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.

RELATED: How to Add Numbers in Microsoft Excel

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)

MINUS function in Google Sheets

Count Unique Values: COUNTUNIQUE

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)

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

RELATED: How to Count Unique Values in Google Sheets

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.

RELATED: How to Share Documents on Google Docs, Sheets, and Slides

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")

DETECTLANGUAGE in Google Sheets

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")

GOOGLETRANSLATE in Google Sheets

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)

GREATER THAN function in Google Sheets

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.

RELATED: How to Insert a Picture in Microsoft Excel

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://www.howtogeek.com/wp-content/uploads/2022/02/DateOptions-GoogleSheetsCustomDateTime.png",3)

IMAGE function with original size

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://www.howtogeek.com/wp-content/uploads/2022/02/DateOptions-GoogleSheetsCustomDateTime.png",4,200,500)

IMAGE function with custom size

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

RELATED: How to Make 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, check out our tutorial for creating a sparkline in Google Sheets.

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

=SPARKLINE(B2:E2)

SPARKLINE Line chart

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

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

SPARKLINE Column chart

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.

RELATED: How to Concatenate Data from Multiple Cells in Google Sheets

If you liked the article, do not forget to share it with your friends. Follow us on Google News too, click on the star and choose us from your favorites.

For forums sites go to Forum.BuradaBiliyorum.Com

If you want to read more like this article, you can visit our Technology category.

Source

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button
Close

Please allow ads on our site

Please consider supporting us by disabling your ad blocker!