Most used Google Sheet formula

Google sheet is an awesome platform that features calculation, graphing tools, pivot tables. Developed by Google Spreadsheet is a Web-based application providing a portable datasheet. With all features of excel, the google sheet has a lot more great features to provide a great record for your personal / business data or records.

Dropdown for most-used Google sheet formula.

Either for official use or personal use comfort is always in first priority followed by security. Spread sheet may be one of the data organizer tool for you and your business but manual organize may be quite time consuming. So, in order to ease your word and reduce time consumption i have listed some most needed google sheet formula below with little description to formula.

ADD SUFFIX

=ARRAYFORMULA(A1 &"///")

A1 is a target cell to add a suffix and "///" is a suffix to be added. If A1 has word ram the output will be ram///

ADD PREFIX

=ARRAYFORMULA("///"&A1)

A1 is a target cell to add Prefix and /// is a suffix to be added. If A1 has word ram the output will be ///ram

TRANSLATE

=GOOGLETRANSLATE(A1,”source_language”,”target_language”)

A1 is a target cell to apply to translate where source language is the string needed to translate and target_language is Language that string is to be translated to, two-digit language must be used like for English “en”, Korean “kr” etc. If A1 has word 北京欢迎您 the output will be” Beijing welcomes you”

TRANSPOSE

TRANSPOSE(A1:A3)

While applying the formula the column will be changed to row and row will be changed to the column.

INDEX MATCH

=INDEX(A1:D1,MATCH(E1,A1:D1,0))

This is a powerful alternative to function Vlookup. the formula matches the value from cells and returns the repeated value.

FILTER EVERY Nth ROW

=mod((row(A3)-row($A$3)+1),2)=0

You can replace the number 2 as per your requirement. The number 2 denotes the number of rows to be filtered. To use the filter formula

• Go to filter tab icon in top right 
• You will see three green dashes appeared on cell 
• Click on the three dash 
• Go to filter by condition 
• Use custom formula and paste the above formula 

POPULATE RANDOM VALUE TO CELL

=choose(randbetween(1,4),"apple","pear","banana","orange")

The number (1,4) denotes the index of value as in formula 1 is "apple" 2 is "pear" 3 is "banana" and 4 is "orange". You can use as many values as you want but make sure to match index and values.

CONDITIONAL COUNT

=COUNTIF(C2:C500,“>1000”) # use with comparison operator only numeric values are accepted
=COUNTIF(D2:D500, “Caroline Forsey”)# use with string

REMOVE Nth NUMBER OF TEXT

=LEFT(text,LEN(text)-n)
=RIGHT(text,LEN(text)-n)

In formula text is the cell number having text and -n means to remove certain value here is an example

=LEFT(A1,LEN(A1)-2) # if Cell A1 has text "HAPPY" after applying formula the result will be "HAP"

This was the list for the most-used Google sheet formula. Please write your comment which formula was useful for you also if you need any formula please feel free to write about that too and don't forget to share with your friends.