How do you make an exponential growth chart in Google Sheets?

With more and more businesses focusing on making decisions by understanding their data, the usage of Google Spreadsheets has increased enormously.

Did you get stuck formatting heavy data to make decisions? Wondering what exactly are the Google Sheets formulas you need to implement on this data to make it crisp and understandable?

For becoming a pro in the world of google sheets, you really need to know the backbone and the basic formulae before you jump onto advance hacks. If youre a beginner and struggling with lumps of data, this is the right place to understand the basic functionalities of a Google Spreadsheet.

Weve written down simple and 20 most important and Google Spreadsheet functions to make your life easy! After reading this blog, youll be ready to implement these formulae right away!

Here are the most useful and must know Google spreadsheets formulas

1. COUNTIF() for Conditional Counting

If youve been manually counting cells all this time, youre doing it wrong.

Theres actually a Google Sheets formula that will count the number of cells (that meet certain criteria) for you!

Formula: =COUNTIF(range, criteria)

For example, say Im tracking the engagement of my companys Facebook posts on this Google Sheets, and I want to find out how many posts have engaged users more than 1.

Assuming the data is housed in cells E2 to E12, heres what my COUNTIF Google Sheets formula would look like:

=COUNTIF(E2:E12,>1)

2. TODAY() to update current date

If you can make the dates in your Google Sheets update automatically, think of how much time youd save. Auto-updating of dates on your Google sheets can benefit you in multiple ways for instance, when you integrate your Google sheets with Google Calendar, any update made inside your sheets is automatically added to your calendar. Isnt that amazing? All you need to do is write a simple google spreadsheet function and updates happen simultaneously in both sheets and the calendar.

Heres the Google Sheets Formula to help you out.

Formula =TODAY()

You can also use this same formula to build date ranges. Say you want to build a report consisting of data from the last 3 days.

Your end date would be =TODAY()

And your start date would be =TODAY() 3

Pretty nifty, huh?

3. TEXT() to convert numbers into currency

Using the TEXT formula, you can take any value and reformat it.

Now, there are plenty of ways in which you can use this formula. Here are some examples:

• Changing a number into a currency
• Changing a number to have more decimal places
• Changing a date string into a MM/DD format

Say youve got a row of numbers, and you want to convert all of them into currency.

Assuming your first cell is B3, youd use the formula =TEXT(B3,$0.00)

Once you get your output, drag the cell downwards to automatically apply the formula to the rest of the data.

4. SPLIT() to divide data into multiple cells

The SPLIT formula is pretty self-explanatory; it allows you to split data from a single cell into multiple cells.

Heres the formula: =SPLIT(Text, Delimiter)

Say you offer a lead magnet on your site, and in order to download this lead magnet, your prospect has to enter their name into your form.

Now, you need a way of splitting up your prospects first name and last name, before you add them to your mailing list.

First, line up all your prospects names in a column (lets say your data is in cells B3 to B9).

Well have the two subsequent columns (C and D) house the first names and last names that well get after applying the Google Sheets formula.

All you have to do is type the following formula: =SPLIT(B3, ) into Cell C3, and youll see your prospects first and last names appear in cells C3 and D3.

Then drag Cell C3 downwards to populate the rest of the cells.

Thats all there is to it!

5. SEARCH() Function to Check Value in a String

This Google Sheets formula allows you to check whether a value exists in a string.

Say youre doing an audit on your content strategy

And you want to figure out if the founder interviews that youve been posting on your companys blog have been performing well.

You already have a Google Sheets consisting of the 100 most popular blog articles on your companys blog, so you want to search through this list and identify how many of your posts fall into the category of founder interviews.

If the vast majority of them do fall in that category, then thats a sign that youre on the right track. If not, then its time to try a different approach!

Now, heres the formula that youd use:

Formula: =IF(SEARCH(/founder-interviews/,B2),YES, )

Again, line up all the URLs of your top 100 blog articles from cell B2 onwards.

Next, go to cell C2, type the above formula in, and drag it downwards to populate the rest of the cells in the same column.

If a particular URL does contain the term founder-interview, then youll see a YES on the cell adjacent to it.

Now you can assess how well-performing your founder interviews are, and fine-tune your content strategy!

6. CONCATENATE() to Append Multiple Cells

Need to combine the contents of two or more cells into a third separate cell? The CONCATENATE google sheet function helps you do just that.

Heres the formula: =CONCATENATE (string1, string2, string3, )

You can also use a variation of the same formula to combine the data in cells, AND incorporate a spacing in between the different data.

To do this, add a in between your strings.

For example, an initial =CONCATENATE (B3,C3) formula would now look like:

=CONCATENATE (B3, ,C3)

7. VLOOKUP() for Vertical Lookup

The VLOOKUP function is pretty self-explanatory it helps you to look up specific information located in a table or database.

Heres the formula: =VLOOKUP(search_key, range, index, is_sorted)

First, the search_key refers to the value you want to search for.

Next, the range refers to the number of columns and rows that should be included in the search.

The index refers to the column index of the value to be returned, with the first column in the range being numbered 1. Take note that if you enter an index that isnt between 1 and the number of columns in the range, #VALUE! will be returned.

Finally, is_sorted indicates whether the column to be searched (the first column of the specified range) is sorted. Google recommends that you set this to FALSE, so that an exact match (NOT the nearest match) will be returned.

Things to know:

• VLOOKUP only looks RIGHT. When used, this formula will work for a table that has lookup values in the left-most column. The data you are looking to retrieve using this formula can appear on any column on the right.
• VLOOKUP has two matching modes exact and approximate. These two are controlled by the range_lookup argument. To get exact matching, you need to set the range_lookup to FALSE, and for an approximate matching set it to TRUE.

Note: VLOOKUP by default is TRUE. So when you are using this formula, the result is always approximate by default unless altered manually.

Apart from this, VLOOKUP() formula on Google Sheets can be used on different sheets and on wildcard characters.

Using VLOOKUP from another sheet:

Lets say you have two Google sheets and you want to lookup matching value of one sheet in another. You can do this by using the VLOOKUP() Google sheets function based on one key column. Your formula will look like this:

=IFERROR(VLOOKUP(A2, IMPORTRANGE (google_doc_link,sheet_name!cell_range),column_number, FALSE),)

Here:

• A2 is the cell for which you want VLOOKUP formula to search matching values. I have used A2 to explain. It can be any other cell-based on your requirement.
• Sheet_name will have the name of the sheet and the following cell_range will be the cell ranges that you want to search from.
• column_number is the column number that will return your matched value.

Place this formula on a blank cell and hit Enter to get the result. This is one amazing way to use VLOOKUP between sheets.

Using VLOOKUP with * wildcard character

Imagine you have a table of team members information and you need to find out information based on a partial match.

You can do this using the standard VLOOKUP Google Sheets formula with the * character also known as the wildcard asterisk character.

Lets say you are searching for first names starting with AV in your table from the first name column. Your VLOOKUP formula will look like:

=VLOOKUP(AVI*,cell_range, 2, FALSE)

The * is used to pull all names starting with AVI combination.

8. IF() to Check Condition of a Logical Expression

If you want to test whether a certain condition is true or false, then the IF function will be right up your alley.

How does the function work?

If the condition is true, the function will carry out a specific operation. If the condition is false, the function will carry out a different operation.

The formula of the function is =IF( test, then_true, otherwise_value)

First, the test refers to the expression which you want to test (to find out whether its true or false).

Next, then_true refers to the operation that is carried out if the test is true.

Finally, otherwise_value deals with the operation that is carried out in the other scenario if the test is not true.

9. IFERROR() to Return Cell Error Value

IMPORTRANGE Google Sheets function is a handy way of dealing with cell errors.

Say you have an error in a cell such as #DIV/0! (this happens when you try and divide a number by zero). Assuming you have other cells whose formulas involve the data in this cell, this error then prevents your subsequent calculations from working.

To get around this, use the IFERROR function to replace error values with a new value that you specify.

Heres the formula: =IFERROR(value, [value_if_error])

The first parameter, value, refers to the value to return if the value is not an error.

The second parameter, value_if_error, refers to the value the function returns if the value is an error. This is left blank by default.

10. IMPORTRANGE() to Import a Range of Cells

IMPORTRANGE is a simple yet powerful function that helps you import a range of cells from a specified spreadsheet.

Heres the formula: =IMPORTRANGE(spreadsheet_url, range_string)

Simply key in the spreadsheets URL, and the string (eg A1:D7) that youd like to import.

11. IMPORTFEED() to Import an RSS or ATOM Feed

Heres the formula: =IMPORTFEED(url, [query], [headers], [num_items])

Only the first parameter (the URL of the RSS or ATOM feed, including protocol) is mandatory; the rest are optional.

12. GROWTH() to predict the growing trend

Trying to predict the demand for your product?

The GROWTH function will help you fit an ideal exponential growth trend, and give you data about said growth trend.

Heres the formula: =GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

known_data_y is the array or range containing y values that are already known. These values are used to curve fit an ideal exponential growth curve.

known_data_x are the values of the independent variables that correspond with known_data_y.

new_data_x are the data points to return the y values for on the ideal curve fit.

Finally, b is set to be TRUE or FALSE; if true, the function will calculate b given a general exponential form of y = b*m^x, and if false, the function will force b to be 1 and calculate the m values.

13. SUBSTITUTE() to replace existing text

This is the best formula thatll come handy if you wish to change an existing text with new text in a particular string.

Heres the formula: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Lets take an example: If you wish to change all the http to https for all the URLs mentioned in your Google Spreadsheet, you need to use the SUBSTITUTE function.

14. IMPORTXML() to import website data

Wish to import data from a particular webpage data onto your Google Spreadsheet?

A copy-paste job can take you forever to do this! Instead, use the formula ImportXML to pull information from any of the XML fields of the webpage.

Heres the formula: IMPORTXML(url, xpath_query)

Example: If you wish to pull out all the links on this URL https://automate.io/, from its structured data, use this formula like =IMPORTXML(https://automate.io/, //a/@href).
Below image depicts the result of this formula.

15. REGEXEXTRACT() to extract substrings

Ever get stuck extracting specific terms from a set of data on your google sheet? Regexextract is the formula to be used! If you have many words to look up from a huge set of data, you definitely cant go around applying filters everywhere! None of us will have the time to do the whole boring stuff. Thats where these formulas come to our rescue.

Heres the formula: REGEXEXTRACT(text, regular_expression)

In this formula, the text is the input text and regular_expression is the first part of the text that matched this expression (the value which will be returned).

16. TRANSPOSE() to transpose rows and columns

You might receive raw data from vendors, customers, etc. and you wish to format it the way you want to see it. If there is a huge set of data arranged row-wise and youd want to see it column-wise, its a hassle copying and pasting.

This formula, TRANSPOSE, lets you convert row-wise data into column-wise and vice versa.

Heres the formula: TRANSPOSE(array)

Now lets apply this formula to a sample data as shown in the image below:

So, your formula would look like: TRANSPOSE(B2: D7)

Apply this on the cell where you want to paste your transposed data.

17. QUERY() to Run a Google Visualization API Query

This is a very POWERFUL formula where you can write a pseudo-SQL query and play with your data!

Heres the formula: Query(data, query)

data : It is the reference to the range of cells you want to query upon
query : It is the text using which the QUERY formula churns out the information we are looking for from the data set

Lets see how you can use the Search Query. Below, well consider a set of data taken from an AdWords Account and our aim is to fetch those campaigns whose cost is > $5.

Lets now run the formula and see results like shown below:

18. ARRAYFORMULA() to Display of Values Returned

Using a lot of formulas on your huge set of data and bloating up your spreadsheet? Use ARRAYFORMULA to perform multiple actions on one or more datasets. The ARRAYFORMULA doesnt have to replicate across multiple data sets. Its dynamic and you can change it just at one place in-case you want to edit the formula across all the data sets.

Heres the formula: ARRAYFORMULA(array_formula)

array_formula: 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

Lets understand this with an example.

Suppose you have a data set for an Adwords account wherein data for each keyword across two months (separately) is being displayed.

If you wish to calculate the spend for each keyword for two months, you neednt calculate it separately in columns. Just use ARRAYFORMULA as shown below:

19. SUMIF() to add numbers in a range cell

The SUMIF() Google Sheets Formula comes handy in calculating sum with multiple parameters i.e. conditional addition.

Lets say I have a list of items and their prices along with categories to which each item belongs to. I can have categories like stationery, miscellaneous, and instruments in column B and prices in Column C. To find the total price of all items belonging to any one category, I can use the SUMIF() function as follows:

=SUMIF(B1:B17, stationary, C1:C17)

Here B1:B17 range of cells contain all the categories and C1:C17 range of cells contain prices of all the items.

20. SORT() to sort a range of cells

If you want to sort your values, SORT() Google Sheets Formula comes handy. The syntax for this formula is:

=SORT()

Lets say I have values like 10,34,55,87,90,17 in column A ranging from A:3 to A:8 cell range. To sort this range, all I need to do is use the SORT() function.

=SORT(A3:A8)

It will automatically sort the values in column A with the lowest on the top.

Wrapping Up

So, those were the best and the most simple Google Spreadsheet formulae youre supposed to know if youve just started working on a spreadsheet! You can make your Google Sheets more powerful by using add-ons that not just simplifies your work but are also pretty useful. Luckily we already have a list of tried and tested google sheets add-ons that can give your spreadsheets a power boost. Start trying these along with the Google spreadsheet functions.

If you come across any use cases wherein you used the above formulas, let us know in the comments section below!