Uses of CELL Color A1 in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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 thecolortype 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.


Uses of CELL Color A1 in Excel: 3 Examples

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 that 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.


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.


Download Excel Workbook


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.


Related Articles


<< Go Back to Color Cell in ExcelExcel Cell FormatLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo