Spreadsheets will always come to your mind whenever someone talks about data management. Isn’t it handy to save the information and organize it in the form of tables rather than in text format? For years, Microsoft Excel has been the leader in the space and is used by organizations worldwide. Most of the job roles, whether it is that of a software developer, content writer, supply chain manager, or even a professor, demand basic knowledge of MS Excel as it handles a variety of data-related tasks and offers easy access to information.
With the growing popularity of cloud computing, applications like word processors and spreadsheets also became available online as a cloud service. Microsoft now offers its essential Office apps like MS Word and MS Excel on the web under its Microsoft 365 platform. Google too started offering various applications under its Google Docs Editors Suite, including Google Sheets. Though not as popular as MS Excel, Google Sheets is slowly gaining popularity, and many organizations are now adopting it when migrating to the cloud.
If you have started to use Google Sheets, then you should know about its widely used formulas that come in quite handy when working on data. These Google sheet formulas make your work easier and save time when performing complex tasks. This article discusses some of those formulas and how to use them for the problems at hand.
Top Google Sheet Formulas
Here are the top Google sheet formulas that you can use to make your daily tasks easier:
More often, you may need to copy-paste data from one sheet to another. This is a tedious task and can be accomplished with one formula called IMPORTRANGE. Here is how its syntax goes:
In your existing sheet, use this formula to import data from the desired sheet by providing its URL and the range of cells to be copied.
Sometimes it is required to arrange the data either in ascending or descending form. The SORT formula helps you do so when you are working with Google sheets. Here is its syntax:
SORT(range, sort_column, is_ascending)
Here, the range is the data that needs to be sorted, sort_column is the index of the columbine range by which to sort the data, and is_ascending can take two values – TRUE or FALSE where TRUE indicates ascending order and FALSE indicates descending order)
Example: SORT(A2:B16, 1, TRUE)
This is a widely used Google Sheets formula that discards duplicates and returns unique rows in the desired source range. The syntax of UNIQUE function is as shown:
UNIQUE(range, by_column, exactly_once)
Here, range is the desired data where unique values are to be found, by_column gives you the option to filter data either by rows or by columns. By default, its value is FALSE. Lastly, exactly_once refers to whether to return only entries with no duplicates. By default, its value is FALSE.
Note that it is not necessary to explicitly mention the second and third parameter in the above syntax.
(Source: Google Docs Editors Help)
When you have some known information on your sheet, and you wish to look for its related data, then the VLOOKUP formula is used. Note that it searches for information by row. Here is the general syntax of the formula:
=VLOOKUP(search_key, range, index, [is_sorted])
Here, search_key is the known value to search for in the first column of the range
range is the desired set of data where the search is to be performed
index refers to the column where the search for related information is to be performed and the desired value to be returned
is_sorted is an optional parameter that accepts either TRUE or FALSE values. FALSE refers to the exact match while TRUE refers to the approximate match.
Example: Using VLOOKUP to find the quantity of Oranges in the second index column.
(Source: Google Docs Editors Help)
This formula is specifically used for text (or string) values and divides it around a desired character or string, putting each fragment of the value into separate cells of a row.
Here is the syntax of this formula:
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Here, text refers to the desired data to be split
delimiter refers to the character around which the text needs to be split
split_by_each is an optional parameter with TRUE as its default value. It gives you the option to either divide text around each character contained in the delimiter or not.
Remove_empty_text is also an optional parameter with TRUE as its default value. It gives you an option to either remove the empty fragments from the split results or keep them.
=SPLIT(A2, “o”) gives the following result:
Google sheets allows you to use a formula in the same cell with another formula, and this feature is known as a nested function. When such formulas are combines, Google Sheets will first calculate the innermost function. Here is an example:
Here, the first formula executed is the inner one, i.e., AND. Its result is used as input for the next formula, i.e., IF. Here the output will be YES.
Apart from the ones mentioned above, Google Sheets supports a wide range of formulas. You can dive into them gradually and become skilled in utilizing the full potential of Google Sheets.