How to Create Filter Views in Google Sheets? (Share/Delete/Save/Duplicate Filter Views)

Filter Views are one of the most useful features which come in handy when working with Google Sheets. With the help of Filter views, we can easily sort the data which we would like to see and so on. In this article, we will go over the Filter View functions with the help of Google Sheet tips provided on this page. Read on to find more.

Table of Contents

How to Create Filter View in Google Sheets?

Let’s understand how to create a filtered view with the help of a sample dataset. For example, let’s consider we have the following data where we have the employee’s salary from different departments.

filter views in google sheets

Let’s understand how to create a filtered view for the above dataset with the help of the steps provided below:

  • Step 1: Open the Google Spreadsheet where you would like to apply the filters.
  • Step 2: Select the range of cells where you would like to apply the filter view.
  • Step 3: Now, move to the toolbar and hit the Data tab.
  • Step 4: Select “Create a Filter” from the drop-down menu.

filter views in google sheets

  • Step 5: Now, the filter will be created, and you will be able to see the filter icon in the column header, as shown below. Click on the filter icon in the column header.
  • Step 6: Here, you will be provided with the following options such as
    • Filter by Condition: Choose from a list of pre-defined conditions or create your own.
    • Filter by Value: Uncheck the box next to the data point and click OK to hide it.
    • Search: Type in the search box to find data points.
    • Filter by Color: Choose whatever text or fill color you want to filter by. Colors from conditional formatting can be filtered but not alternating colors.
  • Step 7: Select the option based on which you want to create the filter. The Filter view has been created for the selected dataset.

filter views in google sheets

How to Create, Save, Delete a Filter View in Google Sheets?

One of the best features of Google Sheets is that we can also create, save, delete or even share the filter view with others. However, in order to perform all of these operations, one should have access to the particular Google Sheets.

Creation of Filter View in Google Sheets

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Click on the Data tab from the toolbar and select “Filter Views” from the drop-down menu.
  • Step 3: Now, further select “Create New Filter.”
  • Step 4: The filter view has been created.

filter views in google sheets

To Save the Filter view in Google Sheets: In order to close the filter view in Google Sheets, just click on the filter icon. Now click on the “Close” icon. The filter view will be closed, and the filter view will be saved automatically.

filter views in google sheets

To Delete or Duplicate Filter View in Google Sheets: In order to delete to duplicate the filter view in Google Sheets, just click on the options and select delete to delete the filter or select duplicate to duplicate the filter.

filter views in google sheets

How to See an Existing Filter View in Google Sheets?

Follow the steps as listed below to see the existing filter view in Google Sheets:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Click on “Data” and select “Filter Views.”
  • Step 3: Now, choose the Filter View.
  • Step 4: The selected Filter View will be applied on the Google Sheets.

filter views in google sheets

How to Save the Filter View in Google Sheets?

To save the filter view in Google Sheets, follow the steps listed below:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Apply the filter using the Filter icon.
  • Step 3: Move to the Data tab and choose the Filter Views.
  • Step 4: Now select “Save as Filter” and choose the name to save your filter.

To rename the filter view, click the filter view name next to “Name” in the top left corner of the sheet and type the new name. Now press the “Enter” button, and the filter view will be renamed.

filter views in google sheets

How to Share and Send Filter View?

We can also share and send the Filter View by following the steps listed below:

  • Step 1: Open the Google Spreadsheet on your device which you would like to share.
  • Step 2: Now, apply the filter.
  • Step 3: Then copy the URL from the Filters.
  • Step 4: Now, share the copied URL with fellow mates, and the filter view will be shared.

filter views in google sheets

Google Sheets QUERY Function Explained With Examples

Query Function in Google Sheets helps us to extract the specific set of data from a tabular column. For example, let’s say you have a huge employee dataset of your company and you would like to view only marketing team details. In this case, you can use the Query Function to extract the department’s dataset.

The query function can also be used to extract a subset of data from the overall dataset. For example, if your data includes eleven columns, you can utilize Google Sheets’ QUERY function to extract only four or five of them.

On this page, let’s understand how to use the Query function with the help of Google Sheet tips provided on this page. Read on to find out more.

Table of Contents

Google Sheets Query Function Syntax

The query function syntax which needs to be used in Google Sheets are given below:

QUERY(data, query, [headers])

  • data: The range of cells on which the query will be run. Only boolean, numeric (including date/time types), or text values are allowed in each column of data. In the event of multiple data types in a single column, the data type of the column for query purposes is determined by the majority data type. Null values are assigned to data types that are in the minority.
  • query: This is the query expression you will use to get the data you need. The Google Visualization API Query Language was used to create this query expression. This is also quite similar to how SQL queries are expressed. This is always enclosed in double quotation marks.
  • headers: This parameter is optional. The number of rows in the header at the top of the data. If this parameter is left blank or set to -1, the value is calculated based on the data.

Understanding Query Function in Google Sheets with Examples

In order to understand the query function in Google Sheets, let’s consider the following dataset.

query function in google sheets

 

In the above dataset, we have an employee dataset and now let’s use the Query function to perform various operations

Extracting Selected Columns in Google Sheets using Query Function

In our case, we are using the same dataset, which is visualized in the above section. Here we want to extract the employee name and their department.

Follow the steps listed below to extract the columns in Google Sheets using the Query function.

  • Step 1: Open the Google Spreadsheet where you want to use the Query function.
  • Step 2: Now, to extract the columns, we should use the SELECT expression.
  • Step 3: The formula to extract the employee name and their department id is “=QUERY(Example Data, “Select A, B”). The Example Data is our sheet name.
  • Step 4: Press the enter button.

You will see the results.

 

query function in google sheets6

Extract Data in Ascending or Descending Order using QUERY Function in Google Sheets

While extracting the data using the QUERY function, we can also specify the order such as ascending or descending.

For example, in the above example, let’s see how the query function extracts the data from the employee department and their salary.

  • Step 1: Move to the cell where you would like to extract the data in ascending or descending order using the QUERY function.
  • Step 2: Now to extract the data in ascending order, use the formula as =QUERY(Example Data, “Select B, D Order by D Asc”).

query function in google sheets

 

  • Step 3: To extract the data using a descending order, use the formula as =QUERY(Example Data, “Select B, D Order by D Desc”)
  • Step 4: Press the “Enter” button and you will see results.

query function in google sheets

 

Extract Data Which Meets Conditions using QUERY Function

Using the Select and Where clauses, the QUERY function returns rows that fit the provided condition.

In this example, I would like to extract the data where the employee is not falling under the engineering category and is senior or older than other employees in column A.

Let’s understand how to use the QUERY function to achieve this by following the steps listed below:

  • Step 1: Move to the cell where you would like to extract the data.
  • Step 2: Now enter the formula “=QUERY(‘Example Data’!$A$2:$H$7, “select A where (B<>’Eng’ and G=true) or (D > “&A2&”)”)“.
  • Step 3: Press the “Enter” button.

You will see the results being extracted in the Google Sheets.

query function in google sheets

Group the Data using QUERY Function in Google Sheets

Suppose, in this example, I want to extract the maximum salary of each department’s employee. To achieve this, we can use the QUERY function. The steps to achieve this are explained below:

  • Step 1: Select the cell where you would extract the results.
  • Step 2: Now enter the formula “=QUERY(‘Example Data’!$A$2:$H$7, “select B, MAX(D) group by B”)“.
  • Step 3: Press the “Enter” button.

query function in google sheets

How to Create and Use Heat Map in Google Sheets With Examples

Google Sheets Heatmaps are a quick and easy way to display and discover anomalies in data, and Google Sheets makes it simple to do so. We can change the color of a cell based on its value using the Google Sheets Conditional Formatting function. In this article, let’s understand everything about Heatmap functions with the help of Google Sheet tips provided on this page. Read on to find out more.

Table of Contents

How to Create Heatmap Using Google Sheets?

Follow the steps as listed below to create a gradient heatmap in Google Sheets:

  • Step 1: Select the cell range where you would like to make a heatmap.
  • Step 2: Move to the menubar and hit on the “Format” tab.
  • Step 3: Now select the “Conditional Formatting” from the drop-down menu.

heatmap in google sheets

  • Step 4: The Conditional Formatting window will open on the screen. Here click on “Add New Rule.”

heatmap in google sheets

  • Step 5: Now, choose “Color Scale” from the conditional formatting pane.
  • Step 6: Click Preview in the Color Scale settings to see a list of pre-defined options. It’s important to note that the color on the left corresponds to a low value, while the color on the right corresponds to a high value. You can also change the color scheme.
  • Step 7: Choose the Gradient Color which you would like to apply.
  • Step 8: Now press the “Done” button.

heatmap in google sheets

The heatmap would be created by coloring the cells based on the values.

How to Create Heatmap in Google Sheets using Single Color?

The gradient is applied based on the value in the cell when you use Color Scale. For example, consider the following dataset.

heatmaps in google sheets

Here we have students marks. Here we must highlight the values between 35 to 90 and both the values, that is values from 35 to 90 are highlighted in red. However, because 35 is less than 90, it takes on a darker hue of red.

heatmaps in google sheets

Let’s understand this concept that is creating the heatmap in Google Sheets using Single Color with the help of the steps given below:

  • Step 1: Select the cells where you would like to create a single color heatmap.
  • Step 2: Hit on the “Format” tab from the toolbar and select “Conditional Formatting” from the drop-down menu.
  • Step 3: Now, in the “Conditional Formatting Rules” pane, click on a “Single Color.

heatmap in google sheets

  • Step 4: Then select the “Format cells if” and select “Greater than.”
  • Step 5: Now, in the field, enter the number. In our case, the number is 80.
  • Step 6: Under “Formatting Style,” choose the color you want to highlight the cells.
  • Step 7: Press the “Done” button, and you will be able to see the results as shown below.

heatmap in google sheets

How to Use OR Function in Google Sheets With Examples (Logical Functions)

Google Sheets OR function returns TRUE if any of the provided arguments are logically true and FALSE if all the provided arguments are logically false. This function is useful when you want to evaluate a set of conditions in Google Sheets.

Also, we can combine the Google Sheets OR function with other functions to perform various mathematical operations. However, if the specified range contains no logical values, the google sheets OR function returns #VALUE! error. Let’s learn everything about OR Function with the help of Google Sheet tips provided on this page.

Table of Contents

OR Function Syntax in Google Sheets

=OR(logical_expression1,[logical_expression2,…])

  • logical_expression1: A cell that contains an expression that represents a certain logical value, such as TRUE or FALSE. This will be the first condition that you want to validate.
  • logical_expression2: This is optional. Additionally, reference to cells containing expressions representing logical values, such as TRUE or FALSE can be used.

OR Function in Google Sheets With Other Functions

OR function in Google Sheets can be used with other formulas to be more efficient. For example, we have two conditions namely the IF & OR function and in order to get the result as “PASS”, we need both the conditions to be met. Below is the formula to combine both IF & OR functions in Google Sheets:

=IF(OR(A1=”Pass”,A2=”Pass”),”Pass”,”Fail”) 

Example of OR Function in Google Sheets

Here are a few examples of OR function in Google sheets,

Example 1 – Test Multiple Conditions Using Google Sheets or Function

google sheets or function

In the above example, we are checking if B2 and B4 cells are having the same value. Here the OR formula will return as PASS if the condition is TRUE or else will return FAIL if the conditions are FALSE.

Formula: =ArrayFormula(IF(OR(B2:B4=1), “Pass”, “Fail”))

In the above formula,

  • ArrayFormula is used to test multiple cells at one go. We can call with the formula =OR(B2:B4=1) and then we can use Control+Shift+Enter instead of just using Enter.
  • OR function evaluates the cells and returns TRUE if the condition is met.
  • If function then uses this result to return “Pass” or “Fail”

Example 2 – Check Whether a Date Is a Weekday Name Using or Function

google sheets or function

In the above example, we are checking the weekday of a specific date. In cell A4 we are checking whether the weekday is Monday or Sunday. March 7th, 1994 is Monday so the result is given as TRUE.

Formula: =OR(WEEKDAY(A4,2)=1, WEEKDAY(A4,2)=7)

In the above formula,

We are using the OR function along with the WEEKDAY function to check if the weekday name is Monday or Sunday.

Example 3 – Using Nested or Function in Google Sheets

google sheets or function

In the above example, we are checking more than one condition. So the OR function will return True only if ABC and GHI have Grade A and will return FALSE if the conditions are not met.

Formula: =OR(OR(A6=”ABC”, A6=(“GHI”),B6=”A”)

The above example is nested OR function where obe OR function is used with in the other. Even if one condition is met, the result will be TRUE.

How to Use Google Sheets Weekday Function With Example

Weekday Function in Google Sheets: When working with Google Sheets, we might tend to have To-Do Lists along with dates. In some instances, we might also want to know the weekday for the specified dates. This is where Google Sheets Weekday Function comes into the picture. The weekday function in google sheets is helpful for knowing the day of the week, for a given date.

In this article, let’s understand everything about WEEKDAY Function with the help of Google Sheet tips provided on this page. Read on to find more.

Table of Contents

Google Sheets Weekday Function Syntax

Below is the syntax for using weekday function in google sheet,

=WEEKDAY(date, [type])

  • WEEKDAY – is the keyword for using this function
  • date – is the input date that the function takes for knowing the day of the week. This can be a reference to a number that the Google sheets can evaluate to a date.
  • type – this is optional that lets the function know where to begin the week from. This parameter will hold numeric values (1, 2, and 3)
    • 1 is the value by default. Here the week starts from Sunday and ends on Saturday. The value 1 is Sunday and value 7 is Saturday.
    • If we consider 2 as a parameter, the week begins on Monday and ends on Sunday. The value for Monday is 1 and the value of Sunday is 7
    • If we consider 3 as a parameter, the week begins on Monday and ends on Sunday. The only difference with the above parameter is, the value for Monday is 0 and Sunday is 6

How to Use the Weekday Function to Get the Weekday as Number?

Below are a few examples for a better understanding of the Google Sheets Weekday function,

Example 1: To Find the Weekday With Type 1

In Google Sheets, we are using the following formula to find out the weekday using Type 1:

=WEEKDAY(“3/7/1994”)

Here by default, the type will be considered as 1 and the day starts with Sunday and ends on Saturday.

In the above example, the date March 7th, 1994 is Monday which would give us 2 (as Sunday is assigned to 1 and Monday is assigned to 2  and so on).

Example 2: To Find the Weekday With Type 2

In Google Sheets, we are using the following formula to find out the weekday using Type 2:

= WEEKDAY(“3/7/1994”,2)

Here in type 2, the day starts with Monday and ends on Sunday.

In the above example, the date March 7th, 1994 is Monday which would give us 1 as a result (as Monday is assigned to 1 and Tuesday is assigned to 2 and so on)

Example 3: To Find the Weekday With Type 3

Here in type 3, the day starts with Monday as 0 and ends on Sunday as 6.

In the above example, the date March 7th, 1994 is Monday which would give us 0 as a result ( as Monday is assigned to 0 and Tuesday is assigned to 1 and so on)

How to Use the Weekday Function to Find Out the Weekday Name or a Specific Date?

Since the weekday function returns the weekday with numbers it is still confusing for people. To know the exact weekday name or specific date, we can call the WEEKDAY function with CHOOSE function.

Example 1: To know what weekday on March 7th, 1994, you can use the below WEEKDAY function affixed with CHOOSE function in Google Sheets,

=CHOOSE(WEEKDAY(DATE(1994,3,7),2),”Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”, “Sun”)

In the above example, we get the weekday name instead of the weekday number which is user-friendly.

Also here, the WEEKDAY function returns the number, and CHOOSE function converts into the day(weekday name).

Example 2: To find the first Monday of the month, you can use the below format,

Here we will find out the first Monday in June 2018. In order to find out the first Monday in June 2018, use the following WEEKDAY formula in Google Sheets:

=IF((WEEKDAY(DATE(2018,6,1),2))>1,DATE(2018,6,1)+7(WEEKDAY(DATE(2018,6,1),2))+1,DATE(2018,6,1))

This will return to us first Monday of June 2018, i.e., 4th June 2018.

EOMONTH Function in Google Sheets

EOMONTH – End Of Month. This function finds the last date of a given month.

Example 1: To find the last Monday of the month, you can use the below format,

Here we will find out the last Monday in May 2018,

This will return to us last Monday of May 2018, i.e., 28th May 2018.

How to Remove Duplicates in Google Sheets? (Using Unique Function)

When we are working in Google Sheets, it’s quite common to have duplicates. In order to remove the duplicates in Google Sheets, we can use the UNIQUE function. The UNIQUE function in Google Sheets allows us to extract unique rows from a range, removing any data that is repeated. When you have a huge amount of data, such as responses from a Google Form, this is really useful. UNIQUE allows you to rapidly determine which values only appear once in the dataset.

To get a better idea about the UNIQUE function, read this article.

In our previous article, we understood how to highlight duplicates. In this article, let’s understand everything about how to highlight duplicates with the help of Google Sheet tips provided on this page.

Table of Contents

UNIQUE Function Syntax

The UNIQUE function syntax has been explained below:

=UNIQUE(range)

range: The information to be filtered based on unique entries.

How to Remove Duplicates in Google Sheets?

Take a look at the following dataset.

remove duplicates in google sheets

Here, if you see, there are multiple duplicate entries in almost all the columns. Let’s understand how to remove the duplicates in Google Sheets with the help of the UNIQUE function.

How to Remove Duplicates from Single Column?

Follow the steps as listed below to remove the duplicates from the single column:

  • Step 1: Open the Google Spreadsheet where you would like to remove the duplicates.
  • Step 2: Now move to the cell where you want to see the results.
  • Step 3: Enter the formula as “=UNIQUE(A2:A20)“. Here we have provided the cell range as A2 to A20. Based on the cell range you can alter your UNIQUE formula.
  • Step 4: Press the “Enter” button.

This would eliminate the duplicates immediately, leaving you with a list of unique names.

remove duplicates in google sheets

Points to Note:

  • To remove the list, either delete the first cell or select the entire range and press delete. Individual cells (except the first, which deletes the entire list) cannot be deleted in Google Sheets. Because this is a formula, any changes to the original list will immediately update it.
  • If there is existing data in the cells that the unique function is supposed to populate, the unique function gives a #REF! error.

How to Remove Duplicates in Google Sheets with Extra Spaces?

When there are leading or trailing spaces, you may run into another issue. Oliver, for example, has a trailing space in the example below (in A12). When the UNIQUE function is applied to this data set, both names are considered unique, and both names are returned in the result.

remove duplicates in google sheets

Let’s understand how to overcome this issue by following the step listed below:

  • Step 1: Open the Google Sheets where you want to remove the duplicates in Google Sheets which has extra space.
  • Step 2: Now move to the cell where you would like to show the UNIQUE data (Removing duplicates).
  • Step 3: Enter the formula as “=ArrayFormula(UNIQUE(TRIM(A2:A20)))“. If you see, here we have used the TRIM function along with the UNIQUE function.
  • Step 4: Press the “Enter” button.

You will see the results as shown below. These spaces will be automatically removed and the final result will be displayed after the spaces have been removed.

remove duplicates in google sheets

How to Remove Duplicates from Multiple Columns?

In the above example, we saw how to remove the duplicates in a single column. Now let’s understand how to remove the duplicates from the Mulitple Columns by following the steps as listed below:

  • Step 1: Move the cell where you would like to remove the duplicates from multiple columns.
  • Step 2: Now enter the formula “=UNIQUE(A2:C10)“. Based on the cell range you can alter this formula.
  • Step 3: Press the “Enter” button and you will see the results as shown below.

In case if your dataset has extra spaces or has trailing text, then simply use the following formula – =ArrayFormula(UNIQUE(trim(A2:B10))).

remove duplicates in google sheets

How To Calculate Weighted Average In Google Sheets (AVERAGE.WEIGHTED function)

AVERAGE.WEIGHTED function: When working with a large dataset in Google Sheets, calculating the average is a regular activity. The weighted arithmetic mean will provide a better representation of the data when the dataset is huge. In this article, let’s understand how to calculate the weighted average for the given dataset with the help of Google Sheet tips provided on this page. Read on to find out more.

Table of Contents

AVERAGE.WEIGHTED Function Syntax

The syntax of the average weighted function is:

AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])

  • values: Values are the numbers that will be averaged. It might be a range of cells or just the values themselves.
  • weights: Weights are the corresponding weights list to use. It may be a range of cells, or it could just be the weights themselves.  Weights are not allowed to be negative, although they can be zero. A positive weight must be present in at least one of the weights. If you are going to use a range of cells, make sure it has the same number of rows and columns as the values range.
  • [additional values]: These are optional argument. We can add more values to add the average.
  • [additional weights]: Additional weights are optional, but each additional value must be followed by the additional weight.

How to Calculate Weighted Average in Google Sheets?

Let’s understand how to calculate the weighted average in Google Sheets with the help of the following example dataset:

How To Calculate Weighted Average In Google Sheets

Now let’s calculate the weighted average for the given numbers in Google Sheets by following the steps listed below:

  • Step 1: Move the cell where you would like to draw the results using the weighted average function in Google Sheets.
  • Step 2: Now enter the formula as “=AVERAGE.WEIGHTED(A1:A2, B1:B2)“. In our case, the weighted average formula is =AVERAGE.WEIGHTED(A2:A3, B2:B3), since we have considered row 1 as headers.
  • Step 3: Press the “Enter” button. You will see the results.
  • Step 4: Now drag the Fill Handle from the formula applied cell to other rows to apply the same formula to all other cells.

How To Calculate Weighted Average In Google Sheets

How to Calculate Weighted Average in Google Sheets using Additional Values?

Let us consider the same example which we have used in the above section. In addition to the above formula, let’s add the additional values.

Follow the steps listed below to calculate the weighted average in Google Sheets using additional values:

  • Step 1: Move to the cell where you would like to calculate the weighted average by adding additional values.
  • Step 2: Now enter the formula as “=AVERAGE.WEIGHTED(A1:A2, B1:B2, C1, C2)“. In our case, the formula is “=AVERAGE.WEIGHTED(A2:A3, B2:B3, C3, C4)“.
  • Step 3: Press the “Enter” button.

You will see the results as shown below.

How To Calculate Weighted Average In Google Sheets

Note: When utilizing the AVERAGE.WEIGHTED function, it’s critical that all cells have a numeric value. The formula will return an error if a cell is left blank. As a result, make sure that all blank cells have at least a ‘0′ in them.

How to Use IMPORTDATA function in Google Sheets? (Fetch CSV/TSV File)

We can use the IMPORTDATA functions to import a Comma-Separated Value (.CSV) file and/or a Tab Separated Value (.TSV) file into Google Sheets. This means that If you wish to import data from a.CSV or.TSV file from the web into Google Sheets, the IMPORTDATA function comes in helpful. When dealing with tabular data such as sales, population, and statistics, it is often employed. All we have to do is to affix the URL along with the IMPORTDATA formula to import the data.

In this article, let’s understand how to use the IMPORTDATA function with the help of Google Sheet tips provided on this page. Read on to find more.

Table of Contents

IMPORTDATA Function Syntax

Before understanding how to import the data with the help of IMPORTDATA function, let’s understand how to use the IMPORTADATA function in Google Sheets.

=IMPORTDATA(“URL”)

There is only one argument to this function. You must include the entire URL of the file location in double quotes. You can specify the cell reference instead of the URL if the URL is in a cell in Google Sheets.

How to Import Data using IMPORTDATA Function in Google Sheets?

Consider the following data. The URL of the data is- https://www.census.gov/2010census/csv/pop_change.csv. (Source: support.google.com). If you enter this data in the search engine, the following data will be displayed.

importdata function in google sheets

It’s quite hard to understand this data since it’s drafted in .csv format. In order to understand the data and make the working easy, let’s import the data into Google Sheets. The steps this CSV data into Google Sheet are explained below:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Move the first cell.
  • Step 3: Now enter the formula =IMPORTDATA(“https://www.census.gov/2010census/csv/pop_change.csv”).
  • Step 4: Click on the “Enter” button.
  • Step 5: You will see the data being imported into the Google Sheet as shown below.

importdata function in google sheets

How to Import Specified Data using IMPORTDATA Function?

In some instances, we might not require the entire data to be imported. Thus to import specified data from the CSV or TSV file into Google Sheets, we can simply modify the IMPORTDATA function with the help of the VLOOKUP function.

We can use the same URL that we used earlier and let’s start fetching the specified range of cells with the help of steps given below:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Move the cell where you would like to import the data.
  • Step 3: Now enter the formula “=VLOOKUP(A2,IMPORTDATA(“https://www.census.gov/2010census/csv/pop_change.csv”),12)“.
  • Step 4: Press the “Enter” button and you will see the results as shown below.

importdata function in google sheets

Note that we utilized the IMPORTDATA function as the table array for the VLOOKUP function in the above formula. We picked 12 as the column number because we only needed statistics for the 2010 population (the third argument of the VLOOKUP function).

Things to Note

When utilizing the IMPORTDATA function in Google Sheets, there are a few things you should keep in mind:

  • Based on the data in the CSV or tsv file, the function automatically fills the cells in the spreadsheet.
  • The IMPORTDATA method would throw an error if some of the cells already had data in them. As a result, make sure you have enough empty cells to hold the data from the CSV or TSV files.
  • Make sure you are using the correct URL version. It will give you an error if you use ‘HTTP instead of ‘HTTPS.’

How to Protect Range of Cells in Google Sheets? (Protect/Unprotect)

One of the best features of Google Sheets is that we can easily share, collaborate and work on a single sheet. However, when multiple users are working on a single Google Sheet, it might quite difficult to save the important data. Thus to save the important data, we can protect the range of cells in Google Sheets.

In this article, let’s understand how to protect the range of cells with the help of Google Sheets tips provided on this page. Read on to find more.

Table of Contents

Who can Protect Range of Cells in Google Sheets?

The users who are falling under this category will be able to protect range cells in Google Sheets:

  • Google Sheet Owner: If you own a spreadsheet, you can control who has access to which ranges and sheets.
  • Google Sheet Editor Access: You can specify who can edit ranges and sheets if you can edit a spreadsheet, but you can’t take access away from the owners.
  • View or Comment on Spreadsheet: You won’t be able to make any changes if you can see or comment on a spreadsheet.

How to Protect Range of Cells in Google Sheets?

Follow the steps as listed below to protect or lock the range of cells in Google Sheets:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Move to the menubar and click on the “Data” tab.
  • Step 3: Now select “Protected Sheets and Ranges” from the drop-down menu.

how to protect range of cells in google sheets

  • Step 4: A protected ranges window will open up on the screen. Now click on “Add a sheet or range“.

how to protect range of cells in google sheets

  • Step 5: Add a description to your entry. While this is optional, if you have numerous ranges to secure, it will make it easier to manage.
  • Step 6: Choose one of the two options below the description field (Range or Sheet). Since you wish to protect a range of cells in this scenario, choose Range.
  • Step 7: Select the range of cells you want to protect in the sheet by clicking the grid symbol in the box below it.
  • Step 8: Click the OK button.

how to protect range of cells in google sheets

  • Step 9: Set or change permissions by clicking Set permissions or Change permissions. Select how you would like to limit editing:
    • To show a warning when anyone makes an edit: When someone makes an edit, a warning should appear: “Show a warning while modifying this range” is selected. It doesn’t prevent users from editing, but it does display a message asking them to confirm that they want to make the change.

how to protect range of cells in google sheets

      • To choose who can edit the range or sheet: To determine who has permission to update the range or sheet, do the following: “Restrict who can edit this range” should be selected. Choose:
          • Only you (and, if you’re not the owner, the owner) can update the range or sheet.
          • Only users in your domain can change the range or sheet if you use Google Sheets for work or education. This option is only available if the spreadsheet is editable by everyone in your domain.
          • Custom: Only the persons you specify have access to change the range or sheet.
          • Copy permissions from another range:  Reuse the permissions you set up on one group of cells or sheet for another set of cells or sheet.

how to protect range of cells in google sheets

  • Step 10: Select Save or Done from the drop-down menu.

How to Unprotect Range of Cells in Google Sheets?

If you are an owner of having editing access to the Google Sheet, you will be able to unprotect the range of cells in Google Sheets. Follow the steps as listed below to no protect the range of cells in Google Sheets:

  • Step 1: In a Google Sheet, move to the menubar.
  • Step 2: Click Data and then select Protect Sheets and ranges from the drop-down menu.
  • Step 3: On the right panel that appears, to view all of the ranges with protections, click the Cancel button.
  • Step 4: Find the protection range that you want to delete and then click on the Delete button.

how to protect range of cells in google sheets

The protected range of cells is now deleted.

How to Count the Number of Words in Google Sheets (2 Easy Formulas)

When you are working in Google Docs, you can easily count the number of words for the given dataset with the help of an in-built function. When it comes to Google Sheets, we don’t have any in-built function to count the number of words. However, if you are working on Google Spreadsheet and want to count the number of words for the given dataset, then you can use the LEN and SUBSTITUTE datasets. In this article, let’s understand how to get the word count with the help of the LEN and SUBSTITUTE function with the help of Google Sheet tips provided on this page. Read on to find out more.

Table of Contents

How to Count the Number of Words in Google Sheets?

Follow the steps as listed below to get the word count in Google Sheet:

  • Step 1: Select the cell where you would like to get the word count.
  • Step 2: Now enter the formula as “=LEN(A1)LEN(SUBSTITUTE(A1,” “,“”))+1“.
  • Step 3: You will see the results. Now the drag the Fill Handle from the formula applied cell to another cell to get the word count for other cells as well.

You will get the word count for the text you have entered as shown below.

count number of words in google sheets

Alternatively, you can also use the formula “=IF(A2=””,””,COUNTA(SPLIT(A2,” “)))” to count the number of words in the given dataset.

count number of words in google sheets

How to Count the Number of Words with Extra Spaces in-between Text?

In some instances, we might have 2 or more extra spaces in between the sentence. In those cases, if we use the above formulas, then the results drawn would not be accurate. Thus to overcome this we can use the TRIM formula along with LEN and SUBSTITUTE function. Follow the steps listed below to count the number of words in a sentence that has extra spaces.

  • Step 1: Move the cell where you would like to get the word count of a sentence which has extra space.
  • Step 2: Now enter the formula as “=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))+1“.
  • Step 3: Press the “Enter” button. You will see the results.

To apply the same formula all over the cells, you can drag the fill handle from the formula applied cell.

count number of words in google sheets

Working of LEN & SUBSTITUTE Formula

  • The function’s LEN(A1) section returns the total number of characters in the text string (including the spaces).
  • The SUBSTITUTE function is used in the LEN(SUBSTITUTE(A1,” “,“”) portion to remove all the spaces. The length of the characters in the text string is then calculated.
  • The total number of words is calculated by adding the value 1 to the difference between these two formulas.

How to Count Specific Word in Google Sheets?

Let’s consider you have a dataset where you want to count the specific word occurrence. For example, if you have the following dataset and want to count the occurrence of the word “THE” in the dataset.

Let’s understand how to do this by following the steps listed below:

  • Step 1: Move the cell where you would like to count the specific word.
  • Step 2: Now enter the formula “=IF(A2=””,””,COUNTIF(SPLIT(A2,” “),”the”))“.
  • Step 3: Press the “Enter” button. You will see the results.
  • Step 4: Drag the fill handle from the formula applied cell to apply the same formula across other cells.

count number of words in google sheets

Tips to Count Number of Words in Google Sheets

  • You may also rapidly calculate the word count using Google Docs. To acquire the word and character count, just copy and paste the text into any blank Google Docs document and hit Control + Shift + C (all at once).
  • Word Count for Entire Column: To get the word count for the entire column, you can simply use the formula “=ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A8,” “))))”. Here we have chosen A2:A8 since it’s the data range. Based on the cell range, you can change this formula.

count number of words in Google Sheets4

How to Search in Google Sheets and Highlight Matching Data? (4 Easy Methods)

Search is the most important feature which anyone would like to use when working with a huge dataset. Google Sheets is known for storing and organizing data and that is the reason we tend to use the Search feature. However, if you don’t how to make use of Search Feature in Google Sheets, then this page is for you. In this article, let’s understand everything about the Search function with the Google Sheet tips provided on this page. Read on to find more.

Table of Contents

Search using Keyboard Shortcut in Google Sheets

The steps search data in Google Sheets with the help of Keyboard Shortcuts are explained below:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Now press “CNTRL+F” on your keyboard.
  • Step 3: The search tab opens on the screen. Now enter the term which you are searching for.
  • Step 4: If the Google Sheet finds the matching term related to your search, it highlights all the related terms in Green color.

search-in-google-sheets

If you need more control over how you search for data in Google Sheets, you can use the full-fledged find and replace dialogue box functionality.

Search Using Find and Replace in Google Sheets

We can also use the Find and Replace box to search the data on the Google Sheets. In order to enable the Find and Replace feature, follow the steps as listed below:

  • Step 1: Open the Google Spreadsheet where you want to perform the search operation.
  • Step 2: Now go to the toolbar and click on “Edit“.
  • Step 3: Here select “Find and Replace” from the drop-down menu.

search-in-google-sheets

  • Step 4: A window will pop up on the screen. In the Find section, enter the term which you would like to search.
  • Step 5: As soon as you enter the term, the sheet shows the number of times the Search term has been used.

search-in-google-sheets

You will also have the option where you can replace the search term with other keywords and so.

Note: When you are using the Search tab, you will have two options namely – All Sheets, This Sheet, Specific Range.

  • All Sheets: When you select this option, it searches the term which you have searched for all the sheets in the Google Spreadsheet.
  • This Sheet: When you choose this option, it searches only for the term you have searched for in that sheet alone.
  • Specific Range: When you select this option, it searches only for the specified range of cells.

search-in-google-sheets

How to Search using Conditional Formatting?

We can also search the data using the conditional formatting feature in Google Sheets. The steps to search the data in Google Sheets using conditional formatting are given below:

  • Step 1: Select the range of cells where you would like to perform the search operation.
  • Step 2: Go to the Menubar.
  • Step 3: Select “Conditional Formatting” from the drop-down menu.
  • Step 4: The conditional formatting window will open on the screen.
  • Step 5: Now click on the “Format Rules“.
  • Step 7: Here list of options will display on the screen. Choose the search operation you would like to perform.

search-in-google-sheets

  • Step 8: In this example, we have chosen Text Contains. Then in the next tab, enter the search term you are searching for.
  • Step 9: Click the “Done” button.

search-in-google-sheets

The sheet highlights the search term which you were searching for.

Search and Highlight Cells with the Matching Data using Conditional Formatting

Follow the steps as given to search and highlight the cells with the matching data using conditional formatting:

  • Step 1: Choose the entire data set.
  • Step 2: In the menu, select the Format option.
  • Step 3: Select Conditional Formatting.
  • Step 4: Click the ‘Format cells if‘ option in the ‘Conditional formatting rules‘ pane that appears.
  • Step 5: Choose the ‘Custom formula is‘ option.
  • Step 6: Enter the following formula in the Value or Format field: “=$B4=$B$1”

search-in-google-sheets

  • Step 7: Choose the formatting style you want to use
  • Step 8: Click the Done button.

search-in-google-sheets