# 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

656
questions

**0**

votes

**1**answer

54 views

### Excel: Conditional format cells with spilled formulas, multiple ranges

UPDATE: I just found the issue: M5 is a =UNIQUE() formula, and all subsequent columns are referring to M5# and autofills down like arrays.. thus there are only formulas in row 5. I guess Microsoft ...

**0**

votes

**1**answer

17 views

### Excel - conditionally format cell based on whether it is TRUE or FALSE

I have a column with this formula =F9>=G9 (each cell refers to other cells on the same row). I would like to when a cell says TRUE, I would like to change the text color but I can't figure out how ...

**0**

votes

**0**answers

15 views

### Excel Conditional Formatting using Defined Names do not transfer when copying sheet to another workbook

I regularly create separate workbooks using Conditional Formatting with embedded Defined Names. The individual workbooks are then copied (all sheets in the workbook) to a larger multi-sheet workbook.
...

**0**

votes

**1**answer

32 views

### Excel 2019 - Conditional Formatting specific cells based on their values as well as value in another cell

I have a table like this
|Physics| |Chemistry| |Math| |English| |Fail/Pass|
50 30 60 25 F
60 25 50 70 P
I want to highlight those cells ...

**2**

votes

**1**answer

30 views

### How to format the table like this

I have the following table and past it for test case in CSV format too:
;;Prediction;;;;Real Result
;;Shares;;Likes;;Shares;Likes;Notes
;;min;max;min;max
;;108;2220;1711;17086;206;2800;The Range is ...

**0**

votes

**1**answer

80 views

### 2 color conditional formatting in Excel with formula

I want to create a conditional formatting formula in Excel to color code cells based on the value of the cell to their immediate left.
This is part of a wedding/event planning spreadsheet where each ...

**0**

votes

**0**answers

44 views

### Excel Pivot - How to apply banded rows or columns based on a higher level?

While working with an Excel pivot table that has 2 fields selected for columns, I can use the Banded Columns checkbox under Pivot Table Style Options to apply alternating colors to columns.
When ...

**1**

vote

**0**answers

8 views

### How do I turn off autoformat OpenCalc / OpenOffice Spreadsheets

Without context and background: Read the title, How do I turn off AutoCorrect in OpenCalc, specifically time, preferably turn off all AutoFormat.
I am using a spreadsheet to track how fast my online ...

**0**

votes

**1**answer

48 views

### How can I conditionally format a cell in column E based on the value of column F?

I have a column of string data that needs to be manipulated with an arrayformula in order to be ranked. I can set this column up beside it and see the correct rankings.
If my data is in column E and ...

**0**

votes

**0**answers

38 views

### Mixing arrayformula and conditional formatting, how to adjust the indices correctly

I have a column of calculated values that looks like # (#) where # is any valid positive integer located in the range E10:E75 with differently formatted data above.
I want to conditionally format ...

**0**

votes

**0**answers

21 views

### I wish to center align a table text column based on the value in another Cell

I have a table which is populated from another worksheet using the offset function. This table is using rows 10 to 45 with 3 columns and activeX scrollbar. "=OFFSET(Temp!$C2,GLNames!$U$1,0)"
I tried ...

**0**

votes

**2**answers

33 views

### File extension and Internet media type for a non-standard file format

For CSV(Comma-separated values) the file extension is .csv and the Internet media type is text/csv .
For TSV(Tab-separated values) the file extension is .tsv/.tab and the Internet media type is text/...

**2**

votes

**2**answers

233 views

### How do I set a formatting standard in my worksheet?

We're doing an inventory at work and if this is possible it would save a HUGE amount of time.
If I have 2 columns with numbers, Column A being the recommended amount and column B being the actual ...

**0**

votes

**2**answers

113 views

### Identify if a value is greater or less than the previous value

I am creating a scorecard that tracks a number of issues reported each week.
I am looking to add formatting that
if the previous value is greater than the current value it should be highlighted in ...

**0**

votes

**0**answers

87 views

### Conditional Formatting is highlighting the wrong cells

I would like to highlight a cell if the value is greater than $0.00. However, the conditional formatting is only working part time. Sometimes it works perfectly and other times it's not reading it ...

**2**

votes

**1**answer

28 views

### Conditional formatting with different formats in the same cell

Here's the situation: I have a column with a completion rate from 0 to 138, followed by that value in percentage. ie: 66/138 (48%)
I would like to use conditional formatting the change the colours of ...

**0**

votes

**2**answers

87 views

### Dynamic āmerge & Centerā rows based on date values using āConditional Formatingā

My question is really similar to the one presented here:
Excel do dynamic "merge & Center" based on values or through "Conditional Formating"
but in this case I want this in ...

**0**

votes

**2**answers

29 views

### Dynamic rule to change the color of a cell when a check box is true

The following code automatically links any CheckBox in the active sheet to one cell before the checkbox. In other words, I can add as many checkboxes as I want
and when I run the code/macro, all ...

**0**

votes

**1**answer

112 views

### Excel: Compare 4 columns of data

I have a file that I need to compare 4 columns of data. The data is clients' names pulled from two different systems.
How do I get excel to only highlight those rows where First Name 1 matches First ...

**0**

votes

**0**answers

16 views

### Conditional Formating for spreadsheet cells with data in another cell

I have a spreadsheet to track spoilage costs and I have columns I through V labeled.
If my quality issue is one of these categories then highlight the cell below, if data is put in column E of the ...

**1**

vote

**0**answers

53 views

### Copy arrows from conditional formatting

I am looking for a way to copy the arrows of the conditional formatting icon set. I want the arrows to be static when copied because the data should be linked to a Word document. The conditional ...

**0**

votes

**1**answer

36 views

### Prompt that user should enter data in only one of two cells, not both, via formula

what formula would I state if I want a cell to indicate you need to enter information into either cell D or Cell E, if they put data in D AND E "error message will appear" in cell G.

**0**

votes

**2**answers

24 views

### Excel - Conditional Formatting: Getting Or-Condition to work?

Below you can see my data (it's the German version, so the decimal point is a comma), already with the following conditional formatting rule applied: =B21>=B22; it is applied to rows 21 to 28 (29 ...

**1**

vote

**2**answers

281 views

### Excel: Automatically extending conditional formatting to a new row added above data

I have a bunch of values across multiple rows and columns. They are structured so that each row shows the data for one particular month and the most recent values are up top. Consequently, when I ...

**0**

votes

**0**answers

19 views

### Applying conditional formatting row-wise

I'm trying to create conditional formatting based on the following spreadsheet:
My intention is that if Space is not empty and Note is, the cell will glow red with anger. I'm using the following ...

**0**

votes

**1**answer

281 views

### How can I use conditional formatting in an Excel table using structured references?

I believe I am correct in saying that a reference to a column in a table (e.g. table1["col1"]) is known as a structured reference as opposed to an absolute reference (e.g. A1:A5).
I would like to ...

**0**

votes

**0**answers

27 views

### Outlook Conditional Formatting - Everyone Accepted?

I've been trying to find a way to define conditional formatting rules on Outlook's calendar to highlight when a meeting has been accepted by all attendees. That is, when I send an invite to three ...

**0**

votes

**1**answer

29 views

### need to highlight cell if date indicated is less than or equal to 2 business day of date today

need help on conditional formatting on how to highlight cells that has a value of a date that is less than or equal to 2 business days from today.
example 1:
Date Input: Friday, January 10, 2020
Date ...

**0**

votes

**1**answer

50 views

### check if any text within cell contains the text of any cells within a range

I'm looking for an Excel formula (which ideally also works within Google Sheets) that checks whether any part of the text value of a given cell contains the full text value of any previous cell within ...

**0**

votes

**3**answers

134 views

### Excel Conditional Decimal Formatting

I am trying to have decimal places display based on the value inside a given cell. I more or less understand how to custom rules work, and have come up with this rule: [>=10]#,##0.0;[<10]0.00
It ...

**-1**

votes

**1**answer

41 views

### MicroSoft Excel- Blank cell formatting

I would like to condition format a row on the criteria that the last cell in the row is blank.
Once text is entered into the cell (O3) I would like to conditional format (Yellow highlight) to go ...

**2**

votes

**1**answer

57 views

### Color scale conditional formatting for mix of text and number in Excel

I would like to use the color scale conditional format on a column in Excel. I had to change some of the numbers to text because I wanted to distinguish between when the value was <1% and actually ...

**1**

vote

**0**answers

51 views

### Change Fill Color for Entire Row when Value in a Cell Changes

I'd like to change the fill color for an entire row when the value in a particular column changes. For example, change the file when the year changes, like this:
So the fill for the rows with 1999 ...

**0**

votes

**1**answer

39 views

### How do I conditionally format to highlight cells with =-IF in the formula?

I have a large workbook with many sheets, each with two sections in them.
One section, the formulas should begin
=IF(ISNA(...
and in the other section the formulas should begin
=-IF(ISNA(...
...

**0**

votes

**1**answer

67 views

### Finding all MINIMUM values in a column in an Excel sheet

Column D contains numerical value sets, each set is of random number of rows & each set is divided by a blank row. Need to highlight MINIMUM value in each set.
in the above image D4 & D7 ...

**0**

votes

**0**answers

20 views

### Excel Forula returns “Refers to empty cells”

I am using this formula for a running balance:
=IF(AND(ISBLANK([@[EHEAP AMOUNT]]),ISBLANK([@CREDIT])),"",R1-[@[EHEAP AMOUNT]]+[@CREDIT])
EHEAP AMOUNT is the debit column and R is the balance column.
...

**0**

votes

**0**answers

38 views

### Excel - Conditional Formatting - Color Scale constrain gradient?

Trying to constrain gradient color scale rule to ignore coloring below 0.0 and above 1.0 on a row of sequential numbers. When I assign, say RED to minimum 0 and GREEN to maximum 1, the gradient still ...

**0**

votes

**1**answer

50 views

### Conditional Formatting a column based on specific condition on other column?

Given:I have table shown below
| A | Total | % A |
|---|-------|-----|
| 2 | 5 | 40% |
| 4 | 19 | 22% |
| 3 | 10 | 30% |
| 1 | 8 | 13% |
| 1 | 3 | 34% |
And I want to conditional ...

**0**

votes

**2**answers

52 views

### How to conditional format ONLY the first row that meets a condition?

I have an amortization table with a date next to each row. How do I highlight ONLY the first row that is a larger date than today's date?
Let's say the date column starts at A13. Obviously this...
=...

**0**

votes

**1**answer

167 views

### Conditional format cells rule for first character ignoring leading spaces (Excel 2007)

Is there a rule which can specify all cells where the first character is ', ignoring leading spaces?
I am using excel for macros troubleshooting, want to dim out comment cells. which start with ' but ...

**0**

votes

**2**answers

91 views

### Changing font colour in a cell with a formula

I have a spreadsheet that is used by a group of people. Its purpose is for service admin to plan runs for service techs weekly.
They input each job and a travel & task estimated time in minutes ...

**0**

votes

**2**answers

104 views

### Complex Conditional Formatting

I'm a CPA with lots of Excel experience, but can't seem to figure out an answer to this question (of my own). Is there a way to apply conditional formatting for the below example:
EXAMPLE: I have a ...

**1**

vote

**2**answers

87 views

### Conditional format cells of the form x ± y

I have a 1 million numbers as cells that are in the form "3.1415926 Ā± 2.731828." and 314Ā±27 and 314 Ā± 27.32
I want to conditional format or in general format those cells so that they only have 4 ...

**0**

votes

**1**answer

125 views

### Excel: Conditional Formatting using Inequalities and Values

My question is in relation to excel conditional formatting. Can you use a formula in conditional formatting to highlight cells in column M based on the inequality signs and the values entered in ...

**0**

votes

**1**answer

26 views

### Highlight a cell Based on a range on cells

I'm trying to highlight a cell based on the following conditions:
For example:
I want to give the colour red to A1 cell if in a range between A2 until A30 at least one cell has colour blue.
I ...

**0**

votes

**1**answer

37 views

### Excel conditional formating: query cell color

I know how to use the conditional formatting.
I apply a color to some cell based on values.
I have a lot of lines, each for a different product and for each product a list of values in rows.
With ...

**0**

votes

**1**answer

49 views

### Copy only the format and not the condition

How can I copy just the format of a cell when the format is derived by a "conditional formatting" but without copy the condition too?

**1**

vote

**0**answers

17 views

### Conditional Formatting consistency in modified Tables

QUESTION:
how can I keep conditional formatting consistent and easy to manage in a table (ListObject) that is constantly sorted, filtered, and modified by adding new rows (either manually or via VBA)...

**0**

votes

**1**answer

214 views

### Excel Conditional Formatting based on multiple columns

I have this data as example
Excel Example image
How to do conditional formatting so that if I put another data on the same date and another city (for example on 30/10/2019 to Paris) and then I ...

**1**

vote

**2**answers

50 views

### Countif Date Array Formula

I'm trying to build a sheet for tracking compliance with training. I'm needing to build some summary fields, which show how many procedures a person is current on, how many they are out of date with, ...