# 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

781
questions

1
vote

1
answer

24
views

### Excel: How to highlight value if greater than or less than previous column/row's last non-blank value

I have a vehicle data spreadsheet with 3 columns to record the Odometer.
Odometer during repair service or fueling
Odometer at the Beginning of a trip
Odometer at the End of a trip
There are gaps ...

1
vote

2
answers

20
views

### Mass edit of Excel conditional format structure with formulas. Used the year 2022 in the formulas and need to change for 2023

I created a 17 tab worksheet for company locations to track monthly performance metrics against a budget. I used a reference cell as a condition to control conditional formatting of cells as well as ...

0
votes

0
answers

15
views

### Libre office, automatically color cell based on value

I’m trying to make a planning grid with various tasks and I would like a task to be colorized to easily spot it anywhere else. Each different task should get a different colour.
I can’t find a way to ...

0
votes

1
answer

30
views

### Color rows alternatingly according to change in column value – without hidden columns?

I have an Excel sheet with multiple columns, where in one column subsequent values can be identical or different, so that the rows form a number of groups. I would like the rows belonging to a group ...

0
votes

1
answer

19
views

### Excel conditional formatting: contains one string but not another

In an Excel column that I've selected, I would like to apply conditional formatting to any cells that contain the string "US-" except if they contain the string "US-D". Based on ...

3
votes

2
answers

200
views

### Excel: How does relative and absolute reference determine which rows are formatted?

In the image below, why are all K column values formatted and only the correct J column values?
I've read about absolute vs relative reference, don't see why the behavoir is different on my two tables....

0
votes

1
answer

32
views

### How to format Excel cells selectively by character

I have the following Excel cells:
What I want to do is to format every cell that contains letters D and E with bold and red colors. How can I achieve that?
In the above example I only format the ...

0
votes

1
answer

30
views

### Highlight a cell when information in that cell's row is duplicate in 2 of it's columns

I'm looking for a conditional formatting rule that would highlight a cell if data in that cell's row is duplicated in more than one column. Specifically for this sheet, I want the cell in column B to ...

0
votes

0
answers

35
views

### Conditional formatting when date complete is later than due date in a range

I'm putting together a new spreadsheet for my team where I'm tracking "batches" and their approval through the system. The spreadsheet will list 200+ batches per month and each batch has a ...

0
votes

1
answer

66
views

### Microsoft Excel Conditional Formatting for Dates and Prices

I'm looking to conditionally format a table following this general idea; I have short dates in one column and years in the headers for several other columns, Id like to highlight the cell when the ...

1
vote

3
answers

27
views

### Microsoft Word Issue - Preventing Formatting From Automatically Being Altered When Transitioning from .doc to .docx File

I have Microsoft Word .doc files that are formatted perfectly. When transitioning the .doc files to .docx the formatting automatically is being altered/changed and I have to spend time fixing the ...

1
vote

1
answer

20
views

### How can I create a table with values in all columns that change relative to one another?

I want to create a table in Excel where all the resulting values in the other columns change relative to the raw value I modify. Think of it like a multi-way conversion table, where you get to change ...

1
vote

0
answers

34
views

### Excel: Icon conditional formatting based on column name lookup

I am trying to create a conditional formatting rule for a table which looks up a pass mark in another table and then compares the value to that pass mark. It is meant to be as general as possible ...

0
votes

1
answer

55
views

### Excel Conditional Formatting - Using Formula as Part of a Cell Reference

I am trying to use conditional formatting to highlight a cell if it is blank and a cell in the first column of the same row is not blank. For example, if the conditional formatting is for cell D4, I ...

0
votes

1
answer

50
views

### Automatic Shift Planner in Excel using Conditional Formatting

I want to figure out how to schedule shifts automatically in Excel (Native)
What I'm trying to achieve is this (Attached will help you visualize):
Screenshot describing what I have and what I want to ...

0
votes

0
answers

39
views

### Automatically Change Numbers Color to blue in Excel if the cell contain a formula

The color of the cells changes automatically to blue because it contains a formula
How can I do that?

0
votes

2
answers

180
views

### Changing Cell Color based on Multiple Cells

I would like to know how I can set the Cell A to auto change color to red if 3 out of 4 cells (B through E) have a "Y" value.
Screenshot provided for reference

0
votes

2
answers

50
views

### Stop a formula when another is blank

In column A I have employees put a date in and I have formatted as date. In column H I want it to auto calculate 2 yr anniversary of the date they put into Column A which I have done using the cell +...

0
votes

1
answer

43
views

### Excel: How to convert/format a column of text/values into a table made up by every nth line (n columns)

I'm having to fiddle with Notepad++, Excel 'Text to Columns', regex find replace. So painful.
Must be an easier way??
Ive got:
heading1
heading2
heading3
value1
value2
value3
And I want:
heading1
...

0
votes

1
answer

30
views

### Formatting whole and decimalised numbers differently in Excel

I'm having some difficulty formatting some data correctly and after looking around extensively in similar threads such as here, I've come looking for help.
I have two similar datasets (X and Y) and ...

1
vote

1
answer

87
views

### Outlook calendar conditional formatting, for the mettings where the attendees (other than me) didn't confirm their presence?

How can I highlight the meetings where my guests didn't confirm their presence? I would like to know when I need to sned a reminder to my clients before a meeting is up...

0
votes

0
answers

27
views

### Excel conditionnal formating using a format reference table

I have a table with values:
, and a second table with reference values and the companion format:
The target result is:
Is it possible to do it without VBA?
The only solution I have found is: 1) ...

0
votes

1
answer

30
views

### Conditional formatting date and text combined

Of all the possible combinations I have tried so far, every single one results in the same "there is a problem with this formula" error message.
This is what I am trying:
Conditional ...

-1
votes

1
answer

28
views

### Highlight rows when all values are for Order Number

I have a spreadsheet with two columns. One column is Order Number and the other one shows its status. As shown in the screenshot below:
Sample Data Sheet:
I want to highlight all the rows when the ...

0
votes

1
answer

212
views

### In Excel, how to apply graded color scale (with conditional formatting) only if cell value is greater than 5?

Suppose I have this dataset (the variable is not important in this case)
I would like to add a color scale for % Missing column, with light red being the lowest value and dark red being the highest ...

0
votes

1
answer

90
views

### Macro to delete cells highlighted by conditional formatting?

I was looking for a way to automatically remove duplicates in a given range with a macro but all I could find : it's either remove duplicates from a list or remove duplicates from a cell.
I thought ...

0
votes

2
answers

133
views

### How to automaticaly change the color of all "Yes" text to green using Microsoft Word?

I want to dynamicaly/automaticaly change the color of "Yes" and "No" text to coresponding colors. Yes - green. No - Red. All of this needs to be done in microsoft word.
From:
Yes (...

0
votes

1
answer

106
views

### Conditional formatting with multiple rules, the order of rules changes when copy/paste, why?

Each cell has several conditional formatting rules. The order is critical since the format stops at the first rule that's true. For example is less than 0, red, if cell A <>B, bold, etc. When ...

-1
votes

1
answer

150
views

### Add until a certain value is reached and return the content of another cell

So I have the given table.
The formula will be in B6. The problem look like this: first I need to test if B2 is >= than B5. If it is I need to return B1 (Week 1), else add B2+C2 and test if the ...

0
votes

0
answers

57
views

### Why does my conditional formatting stop working when I use the format painter to copy from one Excel sheet to another?

I have a macro I created that has worked perfectly for a few months. It copies formatting and conditional formatting from one Excel sheet to another sheet that has raw data. The macro still runs but ...

1
vote

0
answers

37
views

### "Structured references" work in equation, but same equation doesn't work in "structured references"

I have this formula with "structured references":
=ISNUMBER(SEARCH(Table10[[#Data],[Process1]:[Process18]] & "_" & RIGHT(Table10[[#Headers],[Process1]:[Process18]],LEN(...

0
votes

0
answers

30
views

### Conditional formatting within a pivot table to create a dynamic table

So I've been googling and googling and I'm confused. I'm quite new to pivot tables, and I've used one to pull in entire columns from a datasheet. (There's multiple tables across the workbook but they'...

0
votes

0
answers

27
views

### Microsoft Excel: Conditional Formatting to a PivotTable with changing results

I'm attempting to format PivotTable results that stem from different slicers. Essentially this comes from a raw data 'dump' which will then auto-populate into the results PivotTable based on what ...

0
votes

3
answers

100
views

### How can I highlight a cell with text based on if that text exists in cell in a separate row?

I have a row of 200 text cells. This is a list of original names.
I then have samples of various numbers of names in another row. I would like to be able to highlight the names in this sample that are ...

0
votes

0
answers

16
views

### PivotTable Conditional Formatting

I'm having trouble to understand how to apply PivotTable Conditional Formatting.
Homepage have a pivottable and a 'Date' filter.
Dataset have columns like:
Provider | Date | OC Number | Invoice Nº | ...

-1
votes

1
answer

21
views

### Feels like it should be so simple

I am trying to conditional format a cell (g3) to change the fill color when the following happens:
Any value in a cell in the range of g20 - g30 is greater than the value in cell j20
I must be having ...

0
votes

2
answers

26
views

### Conditional formatting only certain cells in Excel

I have a column containing various different numbers (days until something has to be completed) and have set up the conditional formatting in such a way that small numbers will be reddish and large ...

0
votes

1
answer

55
views

### Stopping Excel Conditional Formatting rules changing between tabs

So I have set up a worksheet for my job and it is spread out between 3 different excel tabs. I created a number of different Conditional Formatting Rules to set up all the sheets in the same way to ...

0
votes

1
answer

118
views

### Conditional formitting Excel - How to check if the cell in the row above is of lower value - without specifying exact row (= for any row)

How can we make a cell red if it svalue is higher than the cell in the row above.
But this without specifying the exact row of the cell. That is: so that it works for any cell. So it should work for a ...

0
votes

0
answers

26
views

### Possible to implement more code, so that highligted dates does'nt show until "in progress" is written

I am coordinating a large amount of production orders that are linked to thereown projects.
In the worksheet that I am working on I have created a calendar which
I have managed to highlight weekend ...

0
votes

2
answers

68
views

### EXCEL conditional formatting

I have a worksheet where I am attempting to have a row highlight if a specific cell in the same row is not blank. Is there a way to apply that rule to the entire worksheet without formatting each ...

0
votes

1
answer

371
views

### How to highlight specific dates in an excel calendar based on start and end date?

I am coordinating a large amount of production orders that are linked to thereown projects. In the worksheet that I am working on I have created a calendar which I have managed to highlight weekend ...

0
votes

0
answers

51
views

### How to highlight cells in a row based on start date and end date in a calender in excel 2022

I am working on a worksheet that is used to overlook several projects and related orders. I need to be able to highlight each cell in each row that correspondes with that order and calendar row based ...

0
votes

0
answers

7
views

### How to color cells based on other cells' colors?

I use color scales on each row's three month-over-month growths. How to make aother table for each date that show the name (PP, PE, PVC) in colors that are same as ther month-over-month growths (an ...

0
votes

1
answer

159
views

### Fade out Excel cells with errors without using conditional formatting

Is there a way to fade out cells that have errors that doesn't require using conditional formatting?
I have been unable to fix an issue that causes hundreds to thousands of duplicated rules (see this ...

1
vote

0
answers

50
views

### Why are my Excel formulas not working as expected? [closed]

These are practice questions from a textbook. I'm trying to solve these questions using MS Excel. Attached are screenshots of the questions with examples I have tried below:
For total revenue, I used ...

0
votes

0
answers

33
views

### Conditional formating of groups in excel

My goal is to flag (via conditional formatting) any cell with the same 'code' but different 'prices' with in the same 'column'. I have added a screenshot of what the final output should be.
Is this ...

0
votes

1
answer

50
views

### Excel conditional formatting of groupings with a variable number of rows

I have a vehicle electronics inventory with a multiple and variable number of rows per vehicle representing devices installed in that vehicle that I would like to format with alternating colors per ...

0
votes

1
answer

233
views

### Conditional formatting based on selection from a dropdown list

I have a spreadsheet that is used as a sort of a schedule. Each person has their own ID number and the ID number is shown in a distinct cell. So if my ID is 41, I might see myself scheduled 9-12 on ...

1
vote

1
answer

739
views

### How to highlight a cell only if it contains the minimum value in a row?

I am compiling a price comparison between a brands products in different online shops, of which one should have a benchmark fuction. So I have my item numbers in the first column of the spreadsheet, ...