{"id":191699,"date":"2022-05-24T18:02:39","date_gmt":"2022-05-24T16:02:39","guid":{"rendered":"http:\/\/sftarticles.wpenginepowered.com\/en\/?p=191699"},"modified":"2025-07-01T20:43:46","modified_gmt":"2025-07-02T03:43:46","slug":"essential-formulas-google-sheets","status":"publish","type":"post","link":"https:\/\/cms-articles.softonic.io\/en\/essential-formulas-google-sheets\/","title":{"rendered":"10 essential formulas for Google Sheets"},"content":{"rendered":"\n

Google Sheets<\/strong> works pretty similar to Microsoft Excel, except it’s entirely cloud-based<\/strong>. This living document<\/strong> aspect adds convenience. However, you can make Google Sheets even better with formulas that automate otherwise time-consuming tasks.<\/p>\n\n\n

\r\n
\r\n
\r\n
\r\n \"Google\r\n <\/div>\r\n
\r\n Google Sheets<\/span>\r\n DOWNLOAD<\/a>\r\n <\/div>\r\n
\r\n <\/path><\/path><\/text><\/svg>\r\n <\/div>\r\n <\/div>\r\n
\r\n <\/span>\r\n <\/div>\r\n
\r\n \r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n\n\n\n

The Google Sheets app formulas may be slightly different from that of Excel. Knowing these formulas and shortcuts can make your data entry much more efficient. Plus, some of these formulas can also help you analyze your data. So whether you’re making spreadsheets for business or a class project, the following 10 Google Sheet formulas will come in handy.<\/p>\n\n\n\n

COUNTIF() to count cells<\/h2>\n\n\n\n

This formula counts the number of cells if they meet certain criteria. With this shortcut, there’s no more need to count the cells manually: =COUNTIF(range, criteria)<\/strong><\/p>\n\n\n\n

For example, you’re looking at the sales data for the sales team for a month, and you want to know which salespeople exceeded the minimum threshold of sales, which is $500. You can add the range of the cells in the first part of the formula where the sales data is. In the criteria section, you can define an amount greater than 500. Here’s what it will look like:
=COUNTIF(F1:F10,”>500″)<\/em><\/p>\n\n\n\n

\"COUNTIF<\/figure>\n\n\n\n

Text() to change numbers<\/h2>\n\n\n\n

You can use this formula to reformat numbers into a currency or decimal format: =TEXT(cell,” Format”)<\/strong><\/p>\n\n\n\n

For example, let’s say you have a column with dollar prices of products. You can apply the formula to the first cell and then drag it down to apply to all the other cells in the row. If the first cell is C2, the formula will look like this:
=TEXT(C2,” $0.00″)<\/p>\n\n\n\n

\"TEXT<\/figure>\n\n\n\n

SPLIT() to divide data from cells<\/h2>\n\n\n\n

This formula can help divide data from a single cell in Google Sheets into multiple cells in subsequent rows. The formula is: =SPLIT(Text, Delimiter)<\/strong><\/p>\n\n\n\n

Let’s say you have customers’ full names in one column. And you want to divide that into first and last names. Suppose the first cell is A2. You’ll go to the next cell B2, and enter the formula =SPLIT(A2,””). This will enter the first and last names in cells B2 and C2. You can simply drag the B2 cell down to the last name.<\/p>\n\n\n\n

\"SPLIT<\/figure>\n\n\n\n

TODAY() for current date<\/h2>\n\n\n\n

With this formula, you’ll automatically get today’s date without having to type anything extra. Simply type in the cell: =TODAY()<\/strong><\/p>\n\n\n\n

You can also use it to create date ranges. For example, =TODAY() – 2 for two days before the current date.<\/p>\n\n\n\n

\"TODAY<\/figure>\n\n\n\n

CONCATENATE() to append cells<\/h2>\n\n\n\n

If you want to append data from multiple cells, this formula makes the process super easy: =CONCATENATE(string1, string2, \u2026)<\/strong><\/p>\n\n\n\n

For example, you have customers’ first and last names in separate rows. So the first data row is 2, the first name is in B2, and the last name is in C2. Here’s how you can append them and get the full name in adjacent cell D2: =CONCATENATE(B2,” “,C2)<\/em>Then simply drag the cell D2 to apply the formula to all the other first and last names in the sheet.<\/p>\n\n\n\n

\"CONCATENATE<\/figure>\n\n\n\n

SEARCH() to check the value in a string<\/h2>\n\n\n\n

Use the search formula to help find a string in a text. This comes in handy when you’re working with spreadsheets that have a lot of text. The formula is:=SEARCH(substring, string, or text, starting at)<\/strong><\/p>\n\n\n\n

Here, the substring<\/em> is the text you’re looking for, the string or text<\/em> is the text you want to check for in the substring, and the starting at<\/em> is the position where it will start, which is 1 by default.<\/p>\n\n\n\n

\"SEARCH<\/figure>\n\n\n\n

SUM() to get total<\/h2>\n\n\n\n

SUM is probably the most frequently used formula in Google Sheets. Here’s the formula: =SUM(range)<\/strong><\/p>\n\n\n\n

Simply type in the range of cells you want the sum for and apply the formula in the cell where you want the total. Of course, this formula applies to numeric values only.<\/p>\n\n\n\n

For example, let’s say you have sales numbers for the month from B2 to B11. Here’s what the formula would look like:
=SUM(B2:B11)<\/em><\/p>\n\n\n\n

\"SUM<\/figure>\n\n\n\n

AVERAGE() to Get Average<\/h2>\n\n\n\n

This formula will give you the average value of a set of numeric data: =AVERAGE(range)<\/strong><\/p>\n\n\n\n

If you wanted the average sales number for the month, here’s what the formula would look like: =AVERAGE(B2:B11)<\/em><\/p>\n\n\n\n

You can also add multiple ranges. For example: =AVERAGE(B2:B11, C2:C11)<\/em><\/p>\n\n\n\n

\"AVERAGE<\/figure>\n\n\n\n

SUBSTITUTE() to replace text<\/h2>\n\n\n\n

You can use this formula to replace text with another text. Here’s the formula syntax: =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence number])<\/strong><\/p>\n\n\n\n

In the formula, the text_to_search<\/em> is the text within which to search, search_for<\/em> is the text to replace, replace_with<\/em> is the replacement text, and [occurrence number]<\/em> is optional if there’s a certain number of times the text should be replaced. Otherwise, all the occurrences will be replaced.<\/p>\n\n\n\n

\"SUBSTITUTE<\/figure>\n\n\n\n

Sort() to sort the rows in one column<\/h2>\n\n\n\n

If you want to sort the rows of a column in ascending or descending order, use this formula: =SORT(range, sort_column, is_ascending)<\/strong><\/p>\n\n\n\n

Here the range<\/em> represents the cell or row range, sort_column<\/em> specifies the column, and is_ascending<\/em> is either TRUE<\/em> or FALSE<\/em>, specifying whether the order is ascending or not.<\/p>\n\n\n\n

\"SORT<\/figure>\n\n\n\n

Google Sheets makes entering, managing, and analyzing data incredibly easy. More importantly, you can access it from anywhere and backup all your spreadsheets in Google Drive. If you want to make the app even better, check out these 10 essential add-ons for Google Sheets<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

Google Sheets works pretty similar to Microsoft Excel, except it’s entirely cloud-based. This living document aspect adds convenience. However, you can make Google Sheets even better with formulas that automate otherwise time-consuming tasks. The Google Sheets app formulas may be slightly different from that of Excel. Knowing these formulas and shortcuts can make your data … Continue reading “10 essential formulas for Google Sheets”<\/span><\/a><\/p>\n","protected":false},"author":9249,"featured_media":191710,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","wpcf-pageviews":1},"categories":[2518,2441],"tags":[3474],"usertag":[],"vertical":[],"content-category":[],"class_list":["post-191699","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-guides","category-how-to","tag-app-subdomain-redirectiongoogle-sheets"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts\/191699","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/users\/9249"}],"replies":[{"embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/comments?post=191699"}],"version-history":[{"count":1,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts\/191699\/revisions"}],"predecessor-version":[{"id":323856,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts\/191699\/revisions\/323856"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/media\/191710"}],"wp:attachment":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/media?parent=191699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/categories?post=191699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/tags?post=191699"},{"taxonomy":"usertag","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/usertag?post=191699"},{"taxonomy":"vertical","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/vertical?post=191699"},{"taxonomy":"content-category","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/content-category?post=191699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}