Questions tagged [conditional-formatting]

Spreadsheet feature that allows automatically adding color, text styles, etc. to certain cells or ranges of cells based on the data workbook

Conditional formatting is useful to automatically format large ranges of cells, particularly based on the value of the cell or its neighbors. For example, you could automatically format cells with a negative value as red.

Conditional formatting can also let you color-code cells based on (for example) how close they are to the average value of all cells, or their relationship with other cells.

Another use well suited for conditional formatting is to set a cell's background and text color to white based on some criteria, thus hiding the data (for example, hiding cells on a bank ledger sheet if they are the same as the last date's value).

In both Microsoft Excel and LibreOffice Calc, conditional formatting is activated by going to

Format > Conditional Formatting

on the main menu bar.

808 questions
8 answers

Find Items in one column that are not in another column

I have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A. What's the quickest way to do this?
C. Ross
  • 6,174
  • 16
  • 62
  • 82
3 answers

Excel Conditional Formatting Escaping a Question Mark

I would like to use a conditional formatting rule in an excel file that would color any box with a question mark in it red. It seems that Excel is using a question mark as a wild card and will turn all cells with at least one character in them red.…
3 answers

How to color rows based on cell value in OpenOffice and LibreOffice

How do you automatically set the background color of an entire row based on the value of a specific column in that row in OpenOffice and LibreOffice? There are similar questions for Excel, but this doesn't completely work in LibreOffice. I can set a…
  • 8,746
  • 16
  • 54
  • 65
3 answers

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

If I let Excel highlight two duplicate values PT_INTERNAL2859736, then also unique value *736 gets highlighted. Why this happens and how can I stop highlighting the unique value as duplicate? I thought the Duplicate Values rule is reliable until I…
  • 13,008
  • 6
  • 65
  • 103
3 answers

Conditional formatting of TRUE / FALSE values in an Excel 2010 range

I have a range containing formulas that evaluate to TRUE or FALSE. How would one apply conditional formatting to this range, so that TRUE cells are Green, and FALSE cells are RED?
11 answers

How do I keep Conditional Formatting formulas and ranges from automatically changing?

I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet. While this is a nice idea, it tends to break things for me in some rather weird ways. To avoid this, I…
  • 13,585
  • 44
  • 113
  • 181
4 answers

Excel conditional formatting fragmentation

Often, I create a sheet with conditional formatting, and set the cell ranges so that the conditional formatting rules are only applied once to a range of cells, e.g. Make $A$1:$A$30 red and Make $B$1:$B$30 blue. After inserting/deleting a number…
6 answers

How do I add conditional formatting to cells containing #N/A in Excel?

I have a column in Excel 2013 filled with values found with VLOOKUP(). For some reason, I am unable to use conditional formatting to highlight cells which contain #N/A. I tried creating highlighting rules for "Equal To..." and "Text That…
  • 13,555
  • 39
  • 101
  • 154
4 answers

Color Cell Based On Text Value

An Excel column contains a text value representing the category of that row. Is there a way to format all cells having a distinct value a unique color without manually creating a conditional format for each value? Example: If I had the categories…
6 answers

Conditionally format a cell if it contains formula

Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?
  • 4,902
  • 2
  • 22
  • 39
12 answers

How to prevent cells from printing in Excel

How can I prevent the contents of specific cells (or entire row or column) from printing in Excel, while keeping them visible on the screen when I'm editing the file? Ideally I should be able to do this to cells in the interior of the spreadsheet…
9 answers

Excel function determine if a cell is visible

In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not? The only hack I can think of is: Create a column with all 1 values. Use a formula like…
  • 1,020
  • 2
  • 13
  • 28
4 answers

Microsoft Word 2010 - Conditional Formatting

Is there a way to apply conditional formatting to a table in Microsoft Word 2010, in much the same way that it is possible in Microsoft Excel? Thanks.
  • 1,114
  • 6
  • 19
  • 33
5 answers

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

I have a spreadsheet with approx 300 rows. Each row has about 15 columns, and each cell contains a numeric value. Each row corresponds to a product. I want to get Excel, for each product, to highlight the cells with the highest numbers in red and…
3 answers

Alternate grid background color in excel when a value of a single column changes?

I need a formula for conditional formatting to make an Excel 2010 spreadsheet alternate highlighting when a single column changes. This question, "How can I alternate grid background color in excel when a value of a single column changes?", is…
  • 133
  • 1
  • 1
  • 5
2 3
53 54