Find All Cells With Value in Google Sheets (An Easy Guide)

In this simple tutorial, we will show you how you can find all cells with value in Google Sheets. Be it any value or specific value (text or otherwise) that you are looking for.

Let’s get started.

3 Ways to Find All Cells With Value in Google Sheets

1. Find Cells With Specific Value in Google Sheets

To search for any specific value in Google Sheets, whether it be a number or text, we first look toward the LOOKUP functions.

I. LOOKUP Function

The base LOOKUP function works with only sorted data from one range. It can find a value within the range and return a value corresponding to it.

For example, we have the following worksheet of employee Names, their Region, and the number of Sales they have achieved. We will look to search and retrieve the Sales numbers according to the region.

dataset to find all cells with value in google sheets

Our formula:


using lookup function to find cell with specific value in google sheets

Formula Breakdown:

  • The specific value that we are searching for is the Sales number for the region California, making California our search_key, cell F3.
  • Our range of values is C3:D12. The function will look for both the search_key and result from within this range. If not found, we will get an error.
  • D3:D12 is our result_range. The value that is extracted corresponds to the row that our search value is in.

Note that in most cases, the dataset will not be sorted as we have seen just now. For unsorted data, and frankly, for a better method for finding cells with value, we resort to the VLOOKUP function.

Read More: How to Find the Range in Google Sheets (with Quick Steps)

II. VLOOKUP Function

We now have the following dataset where the columns are not sorted:

dataset with unsorted data

The VLOOKUP function’s advantage comes from the ability to work with unsorted data correctly. This makes the function the best method to search and find specific values in a range in Google Sheets.

Our example is the same as before: Return the Sales number for a specific region.


using vlookup to find cell with specific value in google sheets

Formula Breakdown:

  • Same as LOOKUP, our search_key is in cell F3.
  • C3:D12 is our data range. It should include the search key and result columns. If the search value is not found, we will get an error.
  • The 2 is the index number of our data range. This represents the column number from which the result is extracted corresponding to the search key.
  • Since our dataset is not sorted, the [is_sorted] field is FALSE. This helps us get the exact match in the range.

While VLOOKUP may be a great function to find specific values in a range in Google Sheets, it is not perfect.

Limitations of VLOOKUP

  1. The search_key must be in the first column of the selected data range. Otherwise, the function will not find it.
  2. All LOOKUP functions look toward the right, as defined by the index number. We cannot lookup values to the left, for example, the Name column in our dataset.

limitations of the vlookup function

To learn more about finding values in a range, please see our Find Value in a Range in Google Sheets article.

III. Using the INDEX-MATCH Combination

The INDEX MATCH function combination remedies any limitations that we have seen in the VLOOKUP function. With this formula, we can define any column to have our search value and result, regardless of their position.

For example, let’s say we want to find the Name of the employee corresponding to the Region. Our formula:


using index match to find cell with specific value in google sheets without limitations

The result column is defined by the reference field of the INDEX function. The search key for the specific value is covered by the MATCH function (taking reference from F3 and the Region column).

Read more: Use of Google Sheets INDEX MATCH in Multiple Columns

IV. Only Find All Cells Containing Value

Coming away from what we have just discussed, if you are looking for a way to only find the cells that contain a specific value, you need not look further than the Find and Replace option of Google Sheets.

You can access the option from the Edit tab. Keyboard shortcut: CTRL+H

navigating to find and replace option from the edit tab

Simply input your search text and click Find to highlight the cell. Keep on clicking Find to cycle through all occurrences.

find all cells with specific value in google sheets using find and replace

While this method does not list the values or occurrences within the spreadsheet, it has other significant search uses.

Alternatively, CTRL+F also works just fine.

Read More: Find and Replace in Google Sheets

Similar Readings

2. Find All Cells with Any Value in Google Sheets (Non-Empty Cells)

Our focus for this section is to find all cells containing any value in Google Sheets. This will be a lot different from finding specific ones and the values related to them

I. Count All Cells with Value

First up we have is the count. We will look to count all values in a range. In other words: Count all non-empty cells in the dataset or spreadsheet.

The COUNTA function is perfect for this scenario. Simply input the range within the function to count all cells with value.

For a single column:


Multiple column formula:


count all cells with values in google sheets

For an in-depth guide for this approach and more such scenarios, please see our Count Non-Empty Cells in Google Sheets article.

II. List All Cells with Value in One Column

Here we have a list of names. We want to list all these names automatically. Meaning that we want to find and extract all cells that contain values in this dataset and place them in a column.

dataset with text values and blank cells

We have a simple formula to do so:


list cells with values in a column in google sheets

The FLATTEN function combines all the values and presents them as a list (single column) on its own. However, that includes the blank cells in the range.

To remove these blank cells from the equation, we take the help of the FILTER and LEN functions.

3. Find and Highlight All Cells With Value in Google Sheets

Highlighting cells containing data is a common task for any spreadsheet professional. And it is mostly done by conditional formatting.

I. Highlight all Cells with Any Value (Non-Empty Cells)

We start with something simple, which is to highlight all non-empty cells in our spreadsheet.

In the Conditional formatting rules window (Format tab > Conditional formatting), select the range of cells on which you want to apply conditional formatting.

find and highlight all cells with values in google sheets

The non-empty cells will be highlighted by default. If not, select the “Is not empty” option from the Format rules section.

setting the is not empty option in conditional formatting

II. Highlight all Cells with a Specific Value

Similarly, we can find and highlight all cells that contain a specific text in Google Sheets using conditional formatting.

As before, we determine the range of cells first and then move to input the highlight condition. This time, our Format rules will have the Text contains option. We have selected the text “Luther” to highlight.

find and highlight cells with a specific value in google sheets with conditional formatting

The formatting style is completely at the user’s discretion.

For a more in-depth guide to conditional formatting, please see our How to Use Conditional Formatting in Google Sheets article.

Read More: How to Create Conditional Drop Down List in Google Sheets

Final Words

That concludes all the ways you can use to find all cells with values in Google Sheets. The application is versatile enough to provide us with a solution for every scenario. We have the COUNTA function for any non-empty cell, LOOKUP functions for finding cells with specific values, and conditional formatting to highlight any cell with values.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply