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.
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,
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:
To subtract 10 from the value in cell A1, you would use this formula:
To subtract 10 from 20, you would use this formula:
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.
To count the unique values in a list of inserted values, you can use this formula replacing the values with your own:
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:
To identify the language of specific text, you would use this formula with your own text inserted between the quotes:
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:
To translate a certain phrase to Spanish using auto-detect, you can use this formula:
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
- GTE: Greater Than or Equal to, syntax
- LT: Less Than, syntax
- LTE: Less Than or Equal to, syntax
- EQ: Equal to, syntax
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:
To see if the first inserted value is greater than the second, you’d use the following formula:
To see if the value in cell A1 is greater than the inserted value, you can use this formula:
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
widtharguments 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:
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:
Another great way to use the IMAGE function is to create a QR code in Google Sheets!
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:
To use a bar chart with that same cell range, you’d use this formula: