17 Google Sheets Formulas to Become a Master of Google Sheets.
Contents
Last week we presented you with 17 Excel formulas that should not be missing in the repertoire of any user and today we replicate the idea in Google Sheets. We will see 17 essential Google Sheets formulas with which you can do wonders with Google spreadsheets.
If you haven’t read our article on Excel, we recommend that you do so even if you use Google Sheets. Many of Excel’s formulas also work in Sheets, so we have prioritized in our list to formulas that are unique to Sheets or that we did not mention in the compilation for Excel.
GOOGLETRANSLATE
An incredibly powerful formula unique to Google Sheets is GOOGLETRANSLATE. With her you can translate a text from one language to another, using Google Translate technology. Using this formula is very easy, since you only need the original text and the language settings, which uses two-letter codes to specify languages (Spanish = es, English = en, etc.)
-
Use: = GOOGLETRANSLATE (cell or text to translate; source language code; destination source code)
-
Example: = GOOGLETRANSLATE (“Hello, how are you?”; “Es”; “en”)
DETECTLANGUAGE
What if you are not sure which language a text is in? There is also a formula for it. DETECTLANGUAGE returns the language code detected in the text that you pass as a parameter. It can be useful to process, group or filter a list of texts in different languages.
GOOGLEFINANCE
GOOGLEFINANCE is another wildly powerful exclusive Sheets formula. You can use it to obtain data on the stock prices of any security available in the finance section of Google, but it has another more universal utility: the currency conversion.
The formula in question only returns the value of one currency against another, so you must combine it with a multiplication to convert currencies. The format is also peculiar, since you must use as a parameter the name of both currencies, together. For example, to convert from euros to dollars, you need to type EURUSD. From euros to yen, EURJPY, and so on.
TURN INTO
Google Sheets not only helps you convert currencies, but you can also convert units. Weight, distance, time, pressure, force, energy, power, magnetism, temperature, area, information, and speed, to be exact.
DATED
The name of this formula does not make much sense in Spanish (in English it is DATEDIF) but the concept is simple: it tells you the difference between two dates. You can choose if you want the difference in days, months or years, combined or not combined.
The value of Unit it can be Y (years), M (months) or D (days), counted separately. MD corresponds to the days remaining after counting the months and YM to the months remaining after discounting the entire years, so normally you should use both together to get a result that makes sense.
IMAGE
This formula is quite special because upload an image from the internet and sets it as the background for the selected cell. You can modify its appearance and size with the optional parameters, but by default it adapts the image to the size of the cell and it will be sufficient in most cases.
HYPERLINK
With this formula you can create a link to a web page or email address. Google Sheets generates links on its own for most web addresses, but with the formula you can use different text for the link.
TELL
COUNT is a simple formula with which you can count how many cells in the selected range have a numeric value. This formula ignores any other value (for example, text). If you want to count cells with text, you should use COUNTA instead.
WORK DAY
WORKDAY is a curious formula with which you can calculate the resulting date after adding certain days to a date, not counting the weekends. It is also possible to discount holidays, which must be passed as the third parameter.
END MONTH
Another calculation related to dates that may be of interest to you is END.MONTH. With this formula you get the last day of the month of a date, with or without adding a number of months prior to the original date. The second parameter cannot be empty, but it can be zero.
UNIQUE
If you have a list in which some elements are duplicated, an easy way to get a clean list without duplicates is with the UNIQUE formula. This will return the range of unique rows, no duplicates, of the range that you pass as a parameter.
IMPORTHTML
This is along with IMPORTXML one of my favorite Google Sheets formulas, and with it you can get information from a web page. With IMPORTHTML you can import tables or lists from a website to the sheet.
The second parameter, listing type, it can be “table” or “list”. The third parameter is a index with which you specify which table or list you want to import to the sheet, useful if the same page has several. It is difficult to know in advance the result, so it is best that you try until you find what you are looking for.
IMPORTRANGE
And if you want a piece of information that you have in another Google Sheets sheet? In this case, it is very easy to include it in your sheet with the IMPORTRANGE formula. You will need the full address of the sheet, and have access to it with your account.
SPARKLINE
If you want to create charts you have many options with Google Sheets, but the integrated graphics within a cell are achieved using the SPARKLINE formula. It is very useful to show the trend of a series of data easily, and you can customize its appearance with many advanced options.
ISEMAIL
Check if text entered in a cell is a valid email address It can be hell if you want to do it manually with REGEXMATCH and regex, but it’s very easy with ISEMAIL. It will return you TRUE for valid email addresses and FALSE, for those that are not.
ROUND OUT
Sometimes you will not need so much mathematical precision and it will be more practical round the figure. You just do that with ROUNDING, which will round up or down, according to the standard rules. Its second optional parameter allows you to specify how many decimal places you want (by default: none).
-
Use: = ROUND (number or cell)
-
Example: = ROUND (25.95295)
EN.PAR
ES.PAR is a simple function with which you can determine if a number is even or not. In case it is even, it returns TRUE, and otherwise, FALSE. Formerly it was used a lot to create alternate colors in tables, although now Google Sheets has the function without the need for formulas. In any case, it can be used to make other types of calculations to alternate cells.
In Engadget Basics | 17 essential Excel formulas to get started and learn Excel formulas
