Uses of CELL Color A1 in Excel (3 Examples)

Mainly CELL Color A1 (reference) is an info_type operation of the CELL function. In this article, we discuss the CELL color reference option and its uses in Excel.

Download Excel Workbook


What is the Cell Color A1(Reference)?

To know about the CELL COLOR A1 you need to know The CELL function first. This function returns information about the cell. The syntax of the CELL function is

CELL(info_type, [reference])
Argument Required/Optional Explanation
info_type Required A text value out of 12 different values that specify what type of cell info you want
reference Optional A particular cell that you want the info about. If [reference] is provided, the function will return info_type of the then selected cell or active cell in case of a range.

12 types of info about a particular cell. And cell “Color” is one of them. The info_type has to enter with a double quotation (“ “) mark in the CELL function.

“color”: Returns 1 if the cell is formatted in color for Negative values,

              Returns 0 (zero) otherwise.

While using the “color” type you can use the cell reference, which can be A1. So, the formula outlook will be something like

=CELL("color",A1)

And while using the formula in Excel without color formatting a negative value in cell A1, the output will be similar as shown in the following screenshot.

CELL Color A1

On the other hand, if the value gets color formatted with negative numbers the formula returns 1 as shown in the following image.

CELL Color A1

As we emphasize a particular CELL function; “Color” info_type, we’ll discuss and demonstrate examples only regarding that info_type in this article.


3 Appropriate Examples to Use Cell Color A1 (Reference)

Example 1: Fetching Cell Color Info

From the CELL function arguments, we know that “Color” is one of the info_type arguments of the CELL function. We can use CELL Color A1 (reference) to fetch info regarding color formatting cells.

The formula =CELL("color",[reference]) returns 1 if the [reference] cell is color formatted for negative value otherwise 0.

Let’s say we have some color formatted entries in cells as shown in the following picture.

Cell color info

Now, we want to check which one is color formatted for a negative value. To do so, follow the below sequences,

Write =CELL( in the Formula Bar then multiple (12 to be exact) info_type arguments appear.

Select Color.

Cell Function

Type the [reference] (i.e., A2, we could use A1 if we didn’t use a table header) following a comma (,) as shown in the below formula.

=CELL("color",A2)

Cell formula-CELL Color A1

Hit ENTER and then Drag the Fill Handle to bring out the cell info.

Cell color info

Only the A6 (i.e., 100) cell is color formatted with a negative value that’s why the formula results in 1.

You can test the formula for any color formatted negative values and it always returns 1.


Example 2: Showing Pre-set Text Depending on Values

As we know from our earlier discussion that CELL “color” A1 (reference) is a portion of the CELL function, we can display a pre-set text to show a certain criterion.

In the following dataset, we have Product Sales of two months (Nov’21 and Dec’21) and we calculate the Sale Deficit of Dec’21 in respective of Nov’21. We color formatted the deficit values that are less than Nov’21 sale amount, we have done this using Conditional Formatting.

Conditional formatting

Now, we want to display “Positive” or “Negative” text for positive or negative deficit respectively for each cell.

To =CELL("color", [reference]) formula work, we have to color format the negative value with Number Category Negative numbers type.

Paste the below formula in any adjacent cell (i.e., H5).

=IF(CELL("color",F5),"Negative","Positive")

Formula insertion

Press ENTER then Drag the Fill Handle to pre-set texts to appear as shown in the following screenshot.

Pre-set Text display-CELL Color A1

 

You see the formula returns 1 for those cells which have color formatted negative values.

Here, we have used other cells for our example demonstration, so we hardly used CELL Color A1, but things will be the same for that as well.


Similar Readings


Example 3: Direct Used in Formula

The CELL function’s “color” argument can be used in formulas directly. We can use it to display required strings depending on the condition.

Using a dataset we want to display a text string YES or NO depending on the quantity (i.e., Negative Deficit) of a certain product.

Paste the following formula in any blank cell (i.e., C3).

=IF(CELL("color",INDEX(B8:F15,MATCH(C2,C8:C15,0),5)),"YES","NO")

Inside the formula,

The MATCH function matches the cell reference C2 to range C8:C15 and returns the value as row_num.

After that, the INDEX function matches the row_num and col_num (i.e., we input 5).

Then the CELL function identifies whether the particular cell has a color formatted negative value or not.

In the end, the IF function displays YES or NO depending on no color formatted or color formatted.

Used in formula

  Hit ENTER, you’ll see YES or NO string depending on color formatted negative value similar to the following screenshot.

Result

We Color Formatted values whenever the difference between two months’ sales (Dec’21-Nov’21) results in negative.


⧭ Keep in Mind: Color Formatting

If you apply formatting with whatever the color for negative values then apply the =CELL("color",[reference]) formula, we’ll end up something like the below image

Cell formatting-CELL Color A1

You see though we color formatted the negative value the formula doesn’t show 1 as it should be.

To solve this issue, follow the following sequence

Click on the Icon (shown in the screenshot) in the Home Tab Font section > Format Cells window appears, Select Number (in the Category option) > Choose the 2nd option under Negative numbers (shown in the screenshot).

Click OK.

Format cells

In the worksheet, Apply the formula again by pressing ENTER, you’ll see 1 as the return value as supposed to.

You have to color format the negative values unless the formula doesn’t show results as you expected.


Conclusion

In this article, we discuss and demonstrate a portion of the CELL function. CELL Color A1 is one of the arguments of the CELL function and is useful in the case of fetching cell info. Hope the above-described examples of CELL Color A1 motivate you to use it more efficiently. If you have further queries or feedback, please let me know in the comment section.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo