Useful Google Sheet formulas for use
Google Sheet formulas create graphs, translate intra-cell, merge text in multiple cells, display images from URLs, search and filter data like SQL, fetch data from other Google Sheets, translate intra-cell, display images from URLs, display current date and time, randomize numbers, capitalize first letter, insert links into text, fetch website favicon, and more.
1. ARRAYFORMULA â Work with multiple cells simultaneously.
formula:
=ARRAYFORMULA(A2:A10 * B2:B10)Usage: Allows you to calculate values ââin multiple cells using a single formula, instead of dragging and copying the formula to every row.
formula: ARRAYFORMULA in Google Sheets is used to enable formulas to work on a range of cells (multiple rows or columns) simultaneously. Normally, a formula works on a single cell, but ARRAYFORMULA allows a formula to automatically calculate for an entire column. For example, =ARRAYFORMULA(A1:A + B1:B) will add values ââfrom columns A and B in every row without having to drag the formula down, making it more convenient and reducing the time it takes to copy formulas.
2. SPARKLINE â Create a small graph in a single cell.
formula:
=SPARKLINE(A1:A10)Usage: Used to create small graphs in a single cell, ideal for summarizing trends in data.
formula: SPARKLINE in Google Sheets is used to create small graphs within a single cell to show simple trends in data such as lines, columns, areas, candlesticks, etc. For example, =SPARKLINE(A1:A10) creates a line graph from the values ââin the range A1 to A10, and can be further styled, such as =SPARKLINE(A1:A10, {âcharttypeâ, âcolumnâ; âcolorâ, âblueâ}) to change it to a blue bar graph, making it easy to quickly see trends in your data in a small area.
3. QUERY â Search and filter data like SQL.
formula:
=QUERY(A1:D100, "SELECT A, B WHERE C > 50", 1)Usage: It allows you to filter and retrieve data like a SQL query without the need for multiple filters.
formula: QUERY in Google Sheets is a function that helps you filter, sort, and calculate data from a range of cells using a SQL-like command. For example, =QUERY(A1:C10, âSELECT A, B WHERE C > 50 ORDER BY B DESCâ, 1) selects columns A and B from the range A1:C10, filters only the rows where column C's value is greater than 50, and sorts B in descending order (DESC), making data analysis easier and more flexible.
4. IMPORTRANGE â Pull data from another Google Sheet.
formula:
=IMPORTRANGE("URL Google", "Sheet1!A1:D10")Usage: Automatically import data from other spreadsheets for collaborative use.
formula: IMPORTRANGE in Google Sheets is used to fetch data from another Google Sheets file by specifying a link and a cell range, such as: =IMPORTRANGE("URL_source_sheet", "Sheet1!A1:B10") It will import data from the range A1:B10 of the sheet named âSheet1â in a file with the source sheet URL link, which allows for real-time linking and updating of data across files.
5. GOOGLETRANSLATE â Translate language within cells.
formula:
=GOOGLETRANSLATE(A2, "en", "th")Usage: Can be used to translate text in cells from English to Thai or other languages.
formula: GOOGLETRANSLATE in Google Sheets can be used to automatically translate text from one language to another, such as: =GOOGLETRANSLATE(A1, "en", "th") Will put the text in cell A1 which is in English ("in") Translate into Thai ("th") and if used "auto" Instead of the source language, such as =GOOGLETRANSLATE(A1, "auto", "th") The system will automatically detect the source language, making translating text in sheets easy and fast! ðâĻ
6. UNIQUE â Remove duplicate values
formula:
=UNIQUE(A2:A100)Usage: Help remove duplicate values ââin a column and display only unique values.
formula: UNIQUE in Google Sheets is used to filter data to only unique values ââfrom a range. For example, =UNIQUE(A1:A10) will pull out the unique values ââfrom the range A1 to A10, so you only see the unique data in that column. This is useful for working with data that you want to show only unique values, such as a unique list of names or non-duplicating lists.
7. SORT â Automatically sort data
formula:
=SORT(A2:B100, 1, TRUE)Usage: Automatically sort data by specified columns.
formula: SORT in Google Sheets is used to sort data in any order you want, such as ascending or descending. It can be used with both numbers and text. For example, =SORT(A1:B10, 1, TRUE) sorts the data in the range A1:B10 in ascending order (set TRUE for ascending order). To sort in descending order, use FALSE instead of TRUE. This makes organizing your data quick and easy.
8. TEXTJOIN â Combine text in multiple cells using delimiters.
formula:
=TEXTJOIN(", ", TRUE, A2:A10)Usage: Used to combine text from multiple cells into one text with a delimiter such as a comma or space.
formula: TEXTJOIN in Google Sheets is used to combine text from multiple cells or ranges of data together. You can specify a delimiter between the texts, for example =TEXTJOIN(â, â, TRUE, A1:A5) will combine the text from A1 to A5 with â, â as the delimiter between the texts. If there is a blank value in the cell, it will be ignored (if TRUE is used). For example, the result might be âapple, banana, orangeâ, which helps you to combine text easily and organize it better.
9. SEQUENCE â Automatically generate a number sequence.
formula:
=SEQUENCE(10, 1, 1, 1)Usage: Help generate automatic number sequences in the desired quantity.
formula: SEQUENCE in Google Sheets is used to automatically create a number sequence in a range of cells. For example, =SEQUENCE(5) ââcreates a sequence from 1 to 5 in a single row, while =SEQUENCE(5, 2) creates a sequence of 5 rows and 2 columns. You can further customize it, for example, use =SEQUENCE(5, 1, 10, 2) which starts at 10 and increments by 2 until you reach 10 rows:
10, 12, 14, 16, 18
This function is suitable for creating sequences of numbers or data that increase in an orderly manner.
10. IMAGE â Show images from URL.
formula:
=IMAGE("https://example.com/image.jpg")Usage: Enables displaying images from URLs directly in cells in Google Sheets.
=IMAGE(B1)- If column B contains image links, such as:
https://example.com/image.jpg - This formula will extract the URL from cell B1 and display the image in that cell.
=IMAGE(B1, 4, 100, 100)Resize the image (if necessary)
4= Customize size100= Width 100 pixels100= Height 100 pixels
=ARRAYFORMULA(IF(B1:B<>"", IMAGE(B1:B), ""))If you want to automatically display a picture for every row, use this formula in cell C1:
formula: IMAGE in Google Sheets is used to display images from a URL or from a file in Google Drive within your cells, without using a separate application. This function allows you to display images conveniently and easily in Google Sheets.
11. TRANSPOSE â Toggle rows into columns.
formula:
=TRANSPOSE(A1:E1)Usage: Use to switch the position of data from row to column or from column to row.
formula: TRANSPOSE in Google Sheets is used to change the rows and columns of data. For example, if you have data in a range of rows (horizontal), you can use TRANSPOSE to change it to columns (vertical), and vice versa. This function is useful when you want to quickly switch data between rows and columns.
12. CONCATENATE â Combines text from multiple cells.
formula:
=CONCATENATE(A1, " ", B1)Usage: Help me combine values ââfrom multiple cells into one text.
formula: CONCATENATE in Google Sheets is used to combine text from multiple cells. For example, if you have the word âHelloâ in cell A1 and âWorldâ in cell B1, the formula =CONCATENATE(A1, â â, B1) will combine the two texts into âHello Worldâ. You can add a separator between the texts, such as a space (use â â), a comma, or anything else you want. This function makes combining text from multiple cells easy and convenient.
13. NOW & TODAY â Displays the current date and time.
formula:
=NOW()=TODAY()Usage: NOW() is used to display the current date and time. TODAY() is used to display the current date without time.
formula: NOW and TODAY in Google Sheets are used to automatically display the current date and time:
NOW(): will show the current date and time, such as=NOW()It will display a date and time like â2025-04-03 12:30:45â (depending on the time you use the formula).TODAY(): It will only show the current date without time, such as:=TODAY()It will automatically give you the result of â2025-04-03â.
Both functions update the data every time the sheet is edited or reopened, making it easy to use current date and time data. ð
14. RAND & RANDBETWEEN â Random Numbers
formula:
=RAND()=RANDBETWEEN(1, 100)Usage: RAND() generates a random number between 0 and 1. RANDBETWEEN(x, y) generates a random number between x and y.
formula: RAND() and RANDBETWEEN() In Google Sheets used for generating random numbers:
RAND(): This function will generate a random number between 0 and 1 (e.g. 0.12345) when used.=RAND()You will get a different random number every time you recalculate in the sheet.RANDBETWEEN(): Used to generate random numbers in a specified range, such as:=RANDBETWEEN(1, 100)It will generate a random number between 1 and 100, including range numbers (1 and 100), giving you more flexibility when you need a custom range of numbers.
Both functions will automatically change their values ââwhen recalculated in the sheetð
15. SPLIT â Splits text into multiple columns.
formula:
=SPLIT(A1, ", ")Use to separate text with delimiters such as commas or spaces.
formula: SPLIT() in Google Sheets is used to split text in a cell into multiple parts using a specified delimiter. For example, if you have the text in cell A1 that is âApple, Banana, Orangeâ and you use the formula =SPLIT(A1, â, â), it will split the text into multiple cells separated by a comma and a space.
You can use a variety of delimiters, such as commas, spaces, or even other characters as needed, making it easy to manage multi-part data.
16. REGEXEXTRACT â Extract data according to the specified format.
formula:
=REGEXEXTRACT(A1, "[0-9]+")Use to extract only numbers from text.
formula: REGEXEXTRACT() in Google Sheets is used to extract data that matches a specific pattern from text. For example, if you want to extract email addresses from the text contained in cell A1, which might be âEmail: john@example.comâ, the formula =REGEXEXTRACT(A1, â\S+@\S+â) will extract only the email address âjohn@example.comâ. By using a Regular Expression (regex) to specify a specific pattern, such as an email address, phone number, or specific text, extracting the desired data from text is easy and precise!
17. SUBSTITUTEâ Replace any word, symbol, or other character.
formula:
=SUBSTITUTE(B2, "-", ".")This formula will check cell B2 and replace all â-â signs with â.â Change the words and cells as needed.
formula: SUBSTITUTE() In Google Sheets, it is used to replace some text within existing text without having to edit the entire text. For example, if you have the text in cell A1 that is âHello Worldâ and you want to replace the word âWorldâ with âEveryoneâ, use this formula:
=SUBSTITUTE(A1, "World", "Everyone")
The result will be âHello Everyoneâ, which will automatically change the word âWorldâ to âEveryoneâ. If you want to replace multiple times, you can specify the number of times you want to replace, for example: SUBSTITUTE(A1, "a", "b", 2) It will only replace the second occurrence of âaâ in the text.
18. CONCATENATE â Combines columns together in rows.
formula:
=A1 & B1 & C1 & D1 &E1This formula will merge the columns A1 B1 C1 D1 E1 (start with = and use &). Change the columns or sort the columns as needed. Make other rows sort automatically by using the + sign below the row where the formula is entered.
=A2 & " message " & B2If you want to add spaces or text between the combined values
=CONCATENATE(A2, " message ", B2)Or use the function CONCATENATE With additional space:
="|" & B2Suppose you have data in column B and you want to add a â|â sign before each string in column C:
Increase the space between texts
Use function CONCATENATE:
=CONCATENATE(B1, " ", C1)Use the symbol &:
=B1 & " " & C1formula: The CONCATENATE() function in Google Sheets is used to combine text from multiple cells or sections. For example, if cell A1 contains the text âHelloâ and B1 contains the text âWorld,â the formula =CONCATENATE(A1, â â, B1) will combine the two texts into âHello Worldâ with spaces between the words. This function allows you to easily combine text from multiple sources into one text.
19. REGEXREPLACEâ Delete the / sign and the following words.
formula:
=REGEXREPLACE(A1, "/.*", "")A1is the cell where you want to delete the text./.*This is a pattern that searches for text that contains the / sign and all subsequent characters.""It means to replace the text that matches the search pattern with blank values ââ(remove them). You can also enter words in here.
| Column A (original) | Column B (Result) |
|---|---|
| share.netnut.cn/4f4sDx | share.netnut.cn |
| example.com/page1 | example.com |
| test.website.com/path/to/page | test.website.com |
=REGEXREPLACE(A1, "/.*", "message")This formula will insert the text message in place of the / symbol and the following words.
formula: REGEXREPLACE in Google Sheets is used to replace text within cells that match a specified pattern.
This function allows you to easily replace text that matches a desired format, such as numbers, letters, or special text, in Google Sheets.
20. PROPERâ Automatically capitalizes the first letter.
formula:
=PROPER(B1)You can use the REGEXREPLACE function in conjunction with PROPER to remove the period (.) and the word after it, and capitalize the first letter.
=PROPER(REGEXREPLACE(B1, "\..*", ""))REGEXREPLACE(B1, "\..*", "")\.â means "point (.)â.*â means âany letter that follows the periodâ- Replace all with empty values ââ(
"") â Delete everything from the point onwards.
PROPER(...)- Used to make the first letter a Capital letters
- such as
"soax"â"Soax"
Sample results:
| Column B (original) | Column C (Result) |
|---|---|
| soax.com | Soax |
| example.net | Example |
| google.co.uk | |
| test.website | Test |
formula: PROPER() In Google Sheets, it is used to capitalize the first letter of each word and lowercase the rest of the letters. For example, if cell A1 contains the text âhello worldâ, the formula =PROPER(A1) It will automatically change the text to âHello World,â which helps make multi-word text look cleaner and more organized.
21. HYPERLINK â Insert a link into the column text.
formula:
=HYPERLINK(C1, B1)This formula will make the text in B1 a link using the URL from C1.
=HYPERLINK(C1, "Open linkð")Sample results:
| Column B (Text) | Column C (Link) | Column D (Result) |
|---|---|---|
| https://google.com | ||
| YouTube | https://youtube.com | YouTube |
| OpenAI | https://openai.com | OpenAI |
Use automatic formulas for the entire column
=ARRAYFORMULA(IF(B1:B<>"", HYPERLINK(C1:C, B1:B), ""))You can use Google Apps Script To embed a direct link into column B or D
- Go to Extensions > Apps Script
- Delete the old code and insert this code:
function insertHyperlinks() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var text = data[i][1];
var url = data[i][2];
if (text && url) {
sheet.getRange(i+1, 2).setFormula('=HYPERLINK("' + url + '", "' + text + '")');
}
}
}
- press Run (âķ) To make the script work
- The link will now be permanently embedded in column B.
- You can now delete Column C! ð
formula: The HYPERLINK() function in Google Sheets is used to create clickable links that link text or URLs to websites or other resources. This function makes it easy and convenient to link text or data in a sheet to an external data source.
22. Favicon â How to display the favicon of various websites.
Show as url of favicon image
Method 1: Use plain Google (easiest)
https://www.google.com/s2/favicons?domain=youtube.com- You can extract any website favicons as the image url.
Method 2: Use regular Google (64Ã64)
https://www.google.com/s2/favicons?sz=64&domain=youtube.com- This formula will allow you to get the Favicon size 64x64px
Show as image immediately from image URL
Method 1: Using Google's Favicon API (easiest method)
=IMAGE("https://www.google.com/s2/favicons?domain=youtube.com")- This formula will display as a Favicon image in Google Sheets.
Method 2: Using Google's Favicon API (best method)
=IMAGE("https://www.google.com/s2/favicons?sz=64&domain=youtube.com")- This formula will display as a Favicon image in google sheets. Size 64x64px (Clearer)
Display as an image immediately, pulling from other forum image URLs.
Method 1: Using Google's Favicon API (best method)
=IMAGE("https://www.google.com/s2/favicons?sz=64&domain=" & A2)This formula will display as a Favicon image in google sheets. Size 64x64px (Clearer)
How to use:
- Enter the domain name in the column
A2such as example.com - This formula will pull the Favicon size 64x64px Come show
The way that 2. Pull Favicon directly from website (if API is not available)
=IMAGE("https://" & A2 & "/favicon.ico")GOOGLETRANSLATE ISO-639 code
| Language | ISO-639 code |
|---|---|
| Abkhaz | ab |
| Acehnese | ace |
| Acholi | ach |
| Afrikaans | af |
| Albanian | sq |
| Alur | alz |
| Amharic | am |
| Arabic | ar |
| Armenian | hy |
| Assamese | as |
| Awadhi | awa |
| Aymara | ay |
| Azerbaijani | az |
| Balinese | ban |
| Bambara | bm |
| Bashkir | ba |
| Basque | eu |
| Batak Karo | btx |
| Batak Simalungun | bts |
| Batak Toba | bbc |
| Belarusian | be |
| Bemba | bem |
| Bengali | bn |
| Betawi | bew |
| Bhojpuri | bho |
| Bikol | bik |
| Bosnian | bs |
| Breton | br |
| Bulgarian | bg |
| Buryat | bua |
| Cantonese | yue |
| Catalan | ca |
| Cebuano | ceb |
| Chichewa (Nyanja) | ny |
| Chinese (Simplified) | zh-CN or zh |
| Chinese (Traditional) | zh-TW |
| Chuvash | cv |
| Corsican | co |
| Crimean Tatar | crh |
| Croatian | hr |
| Czech | cs |
| Danish | da |
| Dinka | din |
| Divehi | dv |
| Dogri | doi |
| Dombe | dov |
| Dutch | nl |
| Dzongkha | dz |
| English | en |
| Esperanto | eo |
| Estonian | et |
| Ewe | ee |
| Fijian | fj |
| Filipino (Tagalog) | fil or tl |
| Finnish | fi |
| French | fr |
| French (French) | fr-FR |
| French (Canadian) | fr-CA |
| Frisian | fy |
| Fulfulde | ff |
| Ga | gaa |
| Galician | gl |
| Ganda (Luganda) | lg |
| Georgian | ka |
| German | de |
| Greek | el |
| Guarani | gn |
| Gujarati | gu |
| Haitian Creole | ht |
| Hakha Chin | cnh |
| Hausa | ha |
| Hawaiian | haw |
| Hebrew | iw or he |
| Hiligaynon | hil |
| Hindi | hi |
| Hmong | hmn |
| Hungarian | hu |
| Hunsrik | hrx |
| Icelandic | is |
| Igbo | ig |
| Iloko | ilo |
| Indonesian | id |
| Irish | ga |
| Italian | it |
| Japanese | ja |
| Javanese | jw or jv |
| Kannada | kn |
| Kapampangan | pam |
| Kazakh | kk |
| Khmer | km |
| Kiga | cgg |
| Kinyarwanda | rw |
| Kituba | ktu |
| Konkani | gom |
| Korean | ko |
| Krio | kri |
| Kurdish (Kurmanji) | ku |
| Kurdish (Sorani) | ckb |
| Kyrgyz | ky |
| Lao | lo |
| Latgalian | ltg |
| Latin | la |
| Latvian | lv |
| Ligurian | lij |
| Limburgan | li |
| Lingala | ln |
| Lithuanian | lt |
| Lombard | lmo |
| Luo | luo |
| Luxembourgish | lb |
| Macedonian | mk |
| Maithili | mai |
| Makassar | mak |
| Malagasy | mg |
| Malay | ms |
| Malay (Jawi) | ms-Arab |
| Malayalam | ml |
| Maltese | mt |
| Maori | mi |
| Marathi | mr |
| Meadow Mari | chm |
| Meiteilon (Manipuri) | mni-Mtei |
| Minang | min |
| Mizo | lus |
| Mongolian | mn |
| Myanmar (Burmese) | my |
| Ndebele (South) | nr |
| Nepalbhasa (Newari) | new |
| Nepali | ne |
| Northern Sotho (Sepedi) | nso |
| Norwegian | no |
| Nuer | nus |
| Occitan | oc |
| Odia (Oriya) | or |
| Oromo | om |
| Pangasinan | pag |
| Papiamento | pap |
| Pashto | ps |
| Persian | fa |
| Polish | pl |
| Portuguese | pt |
| Portuguese (Portugal) | pt-PT |
| Portuguese (Brazil) | pt-BR |
| Punjabi | pa |
| Punjabi (Shahmukhi) | pa-Arab |
| Quechua | qu |
| Romani | rom |
| Romanian | ro |
| Rundi | rn |
| Russian | ru |
| Samoan | sm |
| Sango | sg |
| Sanskrit | sa |
| Scots Gaelic | gd |
| Serbian | sr |
| Sesotho | st |
| Seychellois Creole | crs |
| Shan | shn |
| Shona | sn |
| Sicilian | scn |
| Silesian | szl |
| Sindhi | sd |
| Sinhala (Sinhalese) | si |
| Slovak | sk |
| Slovenian | sl |
| Somali | so |
| Spanish | es |
| Sundanese | su |
| Swahili | sw |
| Swati | ss |
| Swedish | sv |
| Tajik | tg |
| Tamil | ta |
| Tatar | tt |
| Telugu | te |
| Tetum | tet |
| Thai | th |
| Tigrinya | ti |
| Tsonga | ts |
| Tswana | tn |
| Turkish | tr |
| Turkmen | tk |
| Twi (Akan) | ak |
| Ukrainian | uk |
| Urdu | ur |
| Uyghur | ug |
| Uzbek | uz |
| Vietnamese | vi |
| Welsh | cy |
| Xhosa | xh |
| Yiddish | yi |
| Yoruba | yo |
| Yucatec Maya | yua |
| Zulu | zu |
These functions can help you work with data in Google Sheets faster and more efficiently. Try them out in your projects and you may discover new ways to make your work easier!
Thai 
English 
Japanese 
Korean 
German 
French 
Russian 
Spanish 
Chinese 
Hindi