What Is CELL Function In Google Sheets?
CELL Function in Google sheets is an inbuilt function used to find the cell’s information such as cell references, location, content and format. Using the CELL function, users can not only analyse the cell references in huge datasets but also to know various information related to the data. For example, consider the below table showing the text, info_type and result in columns A,B, and C, respectively.
Now, using the CELL Function, we can find the result.
To begin with, insert the cell range in the spreadsheet. In this example, the data is inserted in the cell range A1:C6. Now, select the cell where we want to find the result. In this example, it is cell C2. Insert the CELL Function formula. So, the complete formula is =CELL(B2,$A$2). Press Enter key.
Use this CELL Function In Google Sheets Template to follow along with the examples in this article.
Download Excel TemplateWe can see the result as shown in the below image.
In this article, let us learn how to use the CELL function with detailed examples.
Key Takeaways
- CELL function in Google sheets is a default function used to obtain the information about a cell, such as the reference, location, type, etc.
- This function is available in both Excel and Google sheets.
- The syntax of CELL function formula in Google sheets is =CELL(info_type,reference)
- where, info_type shows the type of information we need and reference shows the cell range from which we want to find the information.
- Both the arguments are mandatory in the CELL function formula in Google sheets.
- We need to make sure to add the info_type in double quotations to avoid errors.
CELL() Google Sheets Formula
The syntax or formula of CELL function is
=CELL(info_type,reference)
where,
- info_type is the mandatory argument showing the type of information users need to find from the data
- reference is also an important argument that shows the data or the cell range from which users need to find the information.
- Both the arguments are mandatory in the CELL function formula.
How To Use CELL Function In Google Sheets?
We can use the CELL function with the help of the following steps.
- Step 1: To begin with, insert the cell range in the spreadsheet. Now, select the cell where we want to find the result.
- Step 2: Insert the CELL Function formula.
- Step 3: Press Enter key.
We can see the result in the active cell.
Likewise, we can find the result using CELL function.
Examples
Example #1 – Retrieve The Address Of A Cell
Consider the below table showing name and sales in columns A and B respectively.
Now, we need to find the address of a cell or retrieve the address of a cell using CELL function in Google sheets.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the data is inserted in the cell range A1:B4. Now, select the cell where we want to find the result. In this example, it is cell B8.
Step 2: Insert the CELL Function formula.
Next, we need to use the INDEX and MATCH functions to find the cell having the information – ‘Allen’ and 150 as Name and Sales, respectively.
So, the complete formula is =CELL(“address”,INDEX(B2:B4,MATCH(B6,A2:A4,0)))
Step 3: Press Enter key.
We can see the result as shown in the below image.
Likewise, we can use CELL function to retrieve the address of the cell.
Example #2 – Get The Contents Of A Cell
Consider the below table showing experiment, sample result and result in columns A, B and C respectively.
Now, we need to get the contents of a cell using CELL function.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the data is inserted in the cell range A1:C4. Now, select the cell where we want to find the result. In this example, it is cell C2.
Step 2: Insert the CELL Function formula.
Next, we need to use the IF function along with CELL function to find the contents or information. Let us analyse the data and find whether the experiment and sample result using IF function in Google sheets.
So, the complete formula is =IF(CELL(“contents”,B2)>10,”Success”,IF(CELL(“contents”,B2)>5,”Pass”,”Fail”))
Step 3: Press Enter key.
We can see the result as shown in the below image.
Now, let us use the AutoFill option to find the result as shown in the below image.
Likewise, we can use CELL function to get the contents of the cell.
Example #3 – Find The Type Of Data In A Cell
Consider the below table showing name, job, admission ID and result in columns A, B, C and D respectively.
Now, we need to find the type of data using CELL function.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the data is inserted in the cell range A1:D4. Now, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Insert the CELL Function formula.
So, the complete formula is =CELL(“type”,A2:C2)
Step 3: Press Enter key.
We can see the result as shown in the below image.
Likewise, we can use CELL function to find the type of data.
Important Things To Note
- CELL function in Google sheets has info_type argument where users can enter the required data in double quotes.
- Users might get #NAME? error while using CELL function when the info_type argument is not mentioned in double quotation marks.
- We can use MATCH, INDEX, HYPERLINK and other functions with CELL function to get desired information about the cell in Google sheets.
Frequently Asked Questions
For example, consider the below table showing the text, info_type and result in columns A,B, and C, respectively.
Now, using the CELL Function, we can find the result.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the data is inserted in the cell range A1:C7. Now, select the cell where we want to find the result. In this example, it is cell C2.
Step 2: Insert the CELL Function formula in Google sheets. So, the complete formula is =CELL(B2,$A$2)
Step 3: Press Enter key.
We can see the result as shown in the below image.
Likewise, we can find the result using CELL function in Google sheets.
CELL Function in Google sheets may not work because
• The formula has syntax and or typo errors.
• Users did not include the info_type argument in double quotations.
While using CELL function with parentheses as info_type, we will get #VALUE! Error because, the function CELL parameter 1 value is parentheses. It should be one of: ‘ADDRESS’, ‘COL’, ‘COLOR’, ‘CONTENTS’, ‘PREFIX’, ‘ROW’, ‘TYPE’, ‘WIDTH’.
Use this CELL Function In Google Sheets Template to follow along with the examples in this article.
Download Excel Template