[ad_1]
Microsoft Excel is a commonly used and full-featured spreadsheet application, but it’s not perfect. Its longtime rival, Google Sheets, offers features that Excel doesn’t, including many useful features. We’ll see!
This list is not exhaustive, and Excel may add one or more of these functions at any time. But as of this writing in April 2022, these are the Google Sheets features 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 it.
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 offers the SUM function, which works like ADD, but having a clear, unified collection of functions to work with makes organization and workflow easier.
RELATED: How to Add Numbers in Microsoft Excel
The syntax of each function in Google Sheets is the same with the function name and two arguments. I know, ADD(value1, value2), MINUS(value1, value2,), and so. 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: 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 out of stock, or anything else you want unique values on using this feature.
The syntax of the function is COUNTUNIQUE(value1, value2, ...) where the first argument is required. You can use cell references or embedded values.
To find the count of unique customers in our A1 to A10 range, 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 by replacing the values with your own:
=COUNTUNIQUE(1,2,3,3,3,4)
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 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.
RELATED: How to share documents in Google Docs, Sheets and Slides
The syntax of 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 a specific text, you would use this formula with your own text inserted between the quotes:
=DETECTLANGUAGE("Bon Jour")
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, 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 automatic detection, 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 show if one value is greater than, less than, or equal to another in your sheet? These functions do exactly that and would make 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) - equalizer: Equal to, syntax
EQ(value1, value2)
The formula of each function returns a true or false result. For example, yes value1 is greater than value2, you will receive the result True. If not, you will receive False.
To see if the value in cell A1 is greater than the value in cell A2, you would use this formula:
=GT(A1,A2)
To see if the first inserted value is greater than the second, I 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)
Insert and customize an image: IMAGE
Along with the numbers and text, you may want to include an image in your spreadsheet. While you can easily insert an image into Google Sheets, the IMAGE function allows you to insert one from the web and then customize its size.
RELATED: How to insert a picture in Microsoft Excel
The syntax of the function is IMAGE(url, mode, height, width) where only the first argument is required. These are the options you can use for the mode argument:
- one: Fit the image inside the cell and keep the aspect ratio. This is the default mode.
- two: Stretch or compress the image to fit inside the cell without maintaining the aspect ratio.
- 3: Keep the image in its original size.
- 4: Use a custom size by entering the
heightYwidtharguments 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)
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)
Another great way to use the IMAGE feature is to create a QR code in Google Sheets!
RELATED: How to make a QR code in Google Sheets
Add a sparkline: SPARKLINE
You don’t always need a larger-than-life chart in your spreadsheet. Google Sheets allows you to add a sparkline using the SPARKLINE function. In Excel, you can create a sparkline using the chart function, but the function is simpler and faster to prepare.
the syntax is SPARKLINE(data, customizations) where only the first argument is required. To customize the chart with the second argument, such as selecting the chart type, adding color, adjusting the axes, and more, see our tutorial for creating a sparkline in Google Sheets.
To add a basic sparkline with data from cell range B2 to E2, you would use this formula:
=SPARKLINE(B2:E2)
To use a bar chart with the same range of cells, you would use this formula:
=SPARKLINE(B2:E2,{"charttype","column"})
For even more features you’ll find in Google Sheets but not in Microsoft Excel, see how to join text or how to do the opposite and split text.
RELATED: How to Concatenate Data from Multiple Cells in Google Sheets
[ad_2]
