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

Filter by
Sorted by
Tagged with
0
votes
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
2answers
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
2answers
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
2answers
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
0answers
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
1answer
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
2answers
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
2answers
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
1answer
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
0answers
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
0answers
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
1answer
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
2answers
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
2answers
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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, ...

1
2 3 4 5
14