How to Get Cell Color in Excel (2 Methods) - ExcelDemy (2023)

FAQs

How do you reference a cell color in Excel formula? ›

=CELL("color",A1)

In this formula, "color" is a keyword that tells Excel to return the color code of the cell. A1 is the cell that you want to reference. You can replace A1 with any other cell reference in your workbook.

The COUNTIF function uses the named range (GetColor) as the criteria. The named range in the formula refers to the adjacent cell on the left (in column A) and returns the color code for that cell.

Apply an If-Then rule to all cells by pressing “Ctrl-A” before assigning your rule. If you copy values from colored cells and paste them into new it new cells, the new cells acquire the color of the copied cell. You may find the “Text Contains” rule useful when working with alphanumeric data.

Click Home > Conditional Formatting > Add New Rule.

In the New Formatting Rule dialog box, click Use a formula to determine which cells to format. Under Format values where this formula is true, type the formula: “=VLOOKUP(B3,$H$3:$I$10,2,FALSE) < D3” Click Format. In the Color box, select Red.

If Cell B2 = [the color red] then display FQS. If Cell B2 = [the color yellow] then display SM. This is conditional based on the cell fill color.

On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales. Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

Example: Conditional Formatting if Between Two Values

To do so, highlight the values in the range B2:B14, then click the Conditional Formatting icon on the Home tab, then click Highlight Cells Rules, then Between: In the new window that appears, choose 10 as the lower value and 20 as the upper value: What is this?

The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty. To compare two columns in Excel for differences, replace the equals sign with the non-equality sign (<>).

How do you use multiple criteria in conditional formatting? ›

On the Home tab, in the Styles group, click Conditional Formatting > New Rule… (see step 2 of How to dynamically change a cell color based on value for step-by-step guidance). In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format".

To conditionally format your data, you can utilize preset rules such as Color Scales, Data Bars and Icon Sets or create custom rules where you define when and how the selected cells should be highlighted.

Highlight Cells – IF Function

To highlight cells depending on the value contained in that cell with conditional formatting, you can use the IF Function within a Conditional Formatting rule. Select the range you want to apply formatting to. In the Ribbon, select Home > Conditional Formatting > New Rule.

To recap, the key differences between an XLOOKUP and a VLOOKUP are: XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column.

Using XLOOKUP in a Conditional Formatting statement in Excel 365 will not function as expected when the document is Exported/Downloaded as PDF.

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

You can conditionally highlight cells containing formulas in Excel by using the Conditional Formatting feature. To do this, first select the cells you want to format. Then, on the Home tab, in the Styles group, click Conditional Formatting. Next, click Highlight Cell Rules, and then click Contains Formulas.

How do I use dynamic conditional formatting? ›

To apply another conditional format to the cell, make sure the same cell is still selected and go through the above process again. You can add many conditional formats to the same cell and range in order to get the desired effect.

Excel's predefined conditional formatting, such as Data Bars, Color Scales and Icon Sets, are mainly purposed to format cells based on their own values. If you want to apply conditional formatting based on another cell or format an entire row based on a single cell's value, then you will need to use formulas.

To compare multiple columns in Excel, you can use the conditional formatting option on the home and format the setting to “duplicates” or “uniques”, and choose the desired color to highlight the values to compare multiple columns.

If you have more than two workbooks open, Excel displays the Compare Side by Side dialog box. In this dialog box, under Compare Side by Side with, click the workbook that contains the worksheet that you want to compare with your active worksheet, and then click OK.

Conditional Formatting With Multiple Conditions

To highlight cells according to multiple conditions being met, you can use the IF and AND Functions within a conditional formatting rule. Select the range you want to apply formatting to. In the Ribbon, select Home > Conditional Formatting > New Rule.

Dynamic Formulas allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process. They can repeat for each inserted row or use only the cell where the field is placed.

Can I use an IF formula in conditional formatting? ›

Any conditional formatting argument must generate a TRUE result, meaning that at a literal level, your conditional formatting rule is an If/Then statement along the lines of “If this condition is TRUE, THEN format the cell this way”.

Select the cells to be formatted. Click Conditional Formatting > Color Scales > More Rules. In the New Formatting Rule dialog box, configure these options: In the Format Style dropdown box, select either 2-Color Scale (default) or 3-Color Scale.

Just select a cell with a formula you want to adjust and click the corresponding option from the Function by Color menu: Extensions > Function by Color > Edit selected formula. The add-on pane will open, featuring all settings that were used to build this formula.

To apply the formatting, you simply go to the Home tab > Conditional Formatting > Highlight Cell Rules and select A Date Occurring. Select one of the date options from the drop-down list in the left-hand part of the window, ranging from last month to next month.

In Google Sheets, conditional formatting allows you to dynamically change a cell's text style and background color based on custom rules you set. Every rule you set is created using an if this, then that statement.

In the Format Style dropdown box, select either 2-Color Scale (default) or 3-Color Scale. For Minimum, Midpoint and Maximum values, pick the data type (Number, Percent, Percentile, or Formula), and then choose the color.

How do I change cell color based on another cell color? ›

Click Home > Conditional Formatting > New Rule. In the Select a Rule Type box, click Use a formula to determine which cells to format. To apply color to alternate rows, in the Format values where this formula is true box, type the formula =MOD(ROW(),2)=0.

Under Personal, click Appearance. On the Highlight color pop-up menu, click the color that you want. Note: You must close and then reopen Excel to see the new highlight color.

You can use Excel's AutoFill feature to fill the cell colors quickly. Simply drag the fill handle to the cells you want to fill and select the cell that contains the color you want to use. You'll be able to see the cells highlight as you drag. The color will fill the cells when you release the mouse button.