Ref: https://twitter.com/blakeaburge/status/1571119930304794627?s=52&t=m3aVqvNnWvZMezNJzHt_IQ
Google Sheets is a free web-based app where you can create, edit, manage and collaborate spreadsheets online and can access or edit from any available device. Handling data is a job of precision and having the knowledge of formula gives you an upper hand in procuring the desired results in a much more professional way.
Let’s check out some of the formulas or functions in Google Sheet that makes your life easier and helps your work in Google Sheet uncomplicated and trouble-free.
You can also watch our Beginner's Tutorial for HubSpot CRM below:
#1 SUMIF()
Syntax: SUMIF(range, criterion, [sum_range])
This formula helps you to calculate the sum across a range of cells based on a specific criterion or parameter.
As shown in the example below, the sum has been calculated but based on the criterion of “Rental Car”. Therefore, the sum deduced is only of the Rental Car and not the entire column B.
#2 ARRAYFORMULA()
Syntax: ARRAYFORMULA(array_formula)
array_formula is a range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.
This formula is very effective when you need to use a formula in multiple places. No need to insert the formula separately. You can calculate them using the formula once.
As shown in the example below, expenses for 4 commodities for 3 individual months had to be calculated. Instead of calculating the items separately, simply insert the array formula and specify the cells by selecting them, and you are done! The Sum of the 3 commodities is calculated and displayed in separate cells without any manual computation.
#3 SORT()
Syntax: SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2])
This formula is very handy when you have data with disorganized values and you need to organize them. Insert this formula in a new cell and select the cells you want to reorder. The new organized data version will be displayed below the formula as shown in the image below.
#4 TRANSPOSE()
Syntax: TRANSPOSE(array_or_range)
This formula is used when you have large sets of data and you need to rearrange them row or column wise. As shown in the images below, the data are set in column A and B. To arrange them in rows, insert the formula and select the columns.
#5 VLOOKUP()
Syntax: VLOOKUP(search_key, range, index, [is_sorted])
As the name mentions, this formula runs a search for column-wise data within a specified range of cells. As shown in the example below, to find out the price of Bread, we have inserted the item Bread in B10 and in the below cell, we have inserted the formula specifying the one item we need to search, along with the range of cells we need to conduct the search within. The price is automatically displayed.
#6 SPLIT()
Syntax: SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
This formula is applied to split data that has been placed in a single cell and insert them in multiple cells. In this example, the data present in the Content cell can be separated by specifying from where it needs to divide. In this case we have specified “I” and the result has been displayed.
#7 SEARCH()
Syntax: SEARCH(search_for, text_to_search, [starting_at])
This formula lets you check if a value exists within a string. If we refer to the example image below, we have placed a search function for “rem” within the string present in A3.
#8 SUBSTITUTE()
Syntax: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
This formula assists you to replace an existing text with a new one within the string. In the shown example, we have specified the cell address of the string and replaced the text “1500” with “1200” with the help of this formula.
#9 CONCATENATE()
Syntax: CONCATENATE(string1, [string2, ...])
This formula is a big help when it comes to merging data of two cells into a new cell. In the below example, we have combined the data of two cells A3 and B3 and the new merged data is displayed in C3.
#10 IMPORTRANGE()
Syntax: IMPORTRANGE(spreadsheet_url, range_string)
You can use this formula to import a certain range of cells and its data from another spreadsheet to your existing one. In order to apply the formula, insert it in a cell of your present sheet and specify the url of the other spreadsheet along with the sheet name and range or simply the range like this one: "Sheet1!A2:B6" or "A2:B6".
#11 COUNTIF()
Syntax: COUNTIF(range, criterion)
This formula helps you to count the number of cells in your sheet based on certain criteria and parameters. The example below shows the number of items that are below the range of $10.
#12 TEXT()
Syntax: TEXT(number, format)
This formula can be used to reformat a value and convert to its textual layout. You can change a number into currency, format of a date, etc.
In the below example, the decimal format of the numeral values have been changed.
#13 TODAY()
Syntax: TODAY()
This formula helps you to auto-update the present date in your Google Sheet. Insert this formula in a cell and the current date will be displayed in the cell automatically.
#14 QUERY()
Syntax: QUERY(data, query, [headers])
This formula is a big help when you need to shuffle thorough huge data segments and extract a few based on specific criteria. For the query, you can refer to the Query Language Reference which will help you to understand more about how to use the query to your benefit.
In the example below, we need to finthe itemsre priced above $10. Within the attributes of the query function, we have included the entire range of cells that needs to be processed along with the condition on which our search has to be based.
#15 GOOGLETRANSLATE()
Syntax: GOOGLETRANSLATE(text, [source_language], [target_language])
Simple as its name mentions, it translates texts to different languages. You can identify the existing language of the text or simply mention it as “auto” which means auto detect. For the conversion language you can mention it like for English it is “en” or you can keep it in auto and translation will happen automatically.
#16 SPARKLINE()
Syntax: SPARKLINE(data, [options])
This formula helps you to create and display visual representations like charts within cells to give you a better idea of the progress involved.
In the image below, we have compared two prices of each item with the help of a progress bar. It gives a better and graphic representation of the results.
#17 DETECTLANGUAGE()
Syntax: DETECTLANGUAGE(text_or_range)
This formula helps you to detect the language of a text within a specified cell.
In this example, the formula detacts and displays the abbreviation of the language used in the text of the cell address A2.
#18 DATEDIF()
Syntax: DATEDIF(start_date, end_date, unit)
It helps you to calculate the number of days, months and years within two specified dates.
This example displays the total number of months within the Start and End date present in the two columns A and B. You can opt for other abbreviations like Y (number of whole Years), D (total number of days), YM (number of whole months), and so on.
#19 WORKDAY()
Syntax: WORKDAY(start_date, num_days, [holidays])
This formula helps you to calculate and derive the end date after a certain time period or working days.
You can easily display the total number of working days from the start date and the end date will be shown based on the formula.
#20 IMPORTXML()
Syntax: IMPORTXML(url, xpath_query)
This formula assists you to import data from any structured data types such as XML, HTML, CSV, etc. Simply mention the url location of the data file from which you need to import the data along with what you need and it will be automatically displayed.
