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
|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
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.
On the other hand, if the value gets color formatted with negative numbers the formula returns 1 as shown in the following image.
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.
=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.
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.
➤ 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.
➤ Hit ENTER and then Drag the Fill Handle to bring out the cell 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.
Now, we want to display “Positive” or “Negative” text for positive or negative deficit respectively for each cell.
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).
➤ Press ENTER then Drag the Fill Handle to pre-set texts to appear as shown in the following screenshot.
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.
- Dealing with Time Format in Excel (5 Suitable Ways)
- How to Custom Format Cells in Excel (17 Examples)
- Use Format Painter in Excel (7 Ways)
- How to Copy Cell Format in Excel (4 Methods)
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).
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.
➤ Hit ENTER, you’ll see YES or NO string depending on color formatted negative value similar to the following screenshot.
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
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).
➤ 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.
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.