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
846
questions
4
votes
2
answers
3k
views
Excel conditional formatting based on a formula
I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.
I have values in columns A, B, C and D.
column A has text values (any value user enters)
...
- 2,036
4
votes
1
answer
183
views
How to stop Excel from combining Conditional Formatting rules into 1 broken rule
I understand that Conditional Formatting is a horrendous tool, much like Excel itself, but it's the only realistic option so I'm stuck with using it.
However, I'm unable to grasp how painfully stupid ...
- 144
4
votes
2
answers
15k
views
How to do conditional formatting on multiple columns, each based on its own values
I have a sheet with many columns, and I want to conditionally highlight each one with green for the top 25% of values (values > Q3), yellow for values between the top 25% and top 75% (values ...
- 161
4
votes
1
answer
2k
views
How to color rows based on two cell values in Open/Libre Office?
I am trying to apply conditional formatting to an entire row based on two cell values. The style is to be applied only if both cells have a particular value. I tried applying formula of the form:
$A1=...
- 141
4
votes
1
answer
10k
views
How to apply the same conditional formatting to other cells?
My spreadsheet has multiple rows. Each row has a checkbox. If a checkbox is ticked, conditional formatting will highlight that row. I am curious to know if there's a way to apply the conditional ...
- 41
4
votes
1
answer
16k
views
Transferring Conditional Formatting (without the cell value) in Excel
If I have 3 layers of conditional formatting in a cell (B2) is there a way of transferring the format of cell (B2) to another cell (A2) without copying across the value in the original cell (B2).
I.e....
- 41
4
votes
1
answer
176
views
Is it possible for Excel to check conditional formatting on key press?
I have defined a conditional formatting for a cell like equal to 'a' but it seems Excel checks conditional formatting after I select another cell (and un-focus the cell which has conditional ...
- 41
4
votes
0
answers
4k
views
Excel 2010 crashes: Excel.exe 0xc0000005 when trying to highlight from pivot table
One of our users uses pivot tables a lot. She often called me about Excel crashing from time to time without being able to reproduce the problem. Today we have found a way to reproduce the crash every ...
- 149
4
votes
0
answers
473
views
Conditional formatting for meetings outside normal working hours in Outlook
I'm trying to get meetings that start before 10:00 AM highlighted in red. I've tried to use the Conditional Formatting feature; in the advanced tab I selected the "Start" field and "on or before" ...
- 2,103
4
votes
3
answers
10k
views
How would I use Excel's conditional formatting to highlight values closer to 0 between two columns if there are both positive and negative values?
My goal is to fill or highlight the closest number to 0 per each row out of Column A and Column B like I did manually here:
Is it possible to use a formula within conditional formatting and create a ...
- 43
3
votes
5
answers
90k
views
how to get text instead of TRUE:FALSE in excel
suppose I have a formula in cell C3:=AND(A1=B1)
it will give TRUE:FALSE
but i need Yes:No instead
so how do I format the cell C3 to get so?
Thanks!
Regards,
- 161
3
votes
1
answer
10k
views
Alternate Row Colors of Duplicate Data in Excel
I am working with a dataset in Excel that contains duplicate values and non-duplicate values that are numbers. I would like each duplicate value group to have alternating shades via Conditional ...
- 143
3
votes
2
answers
102k
views
How to change row color based on a single cell's value? [duplicate]
Possible Duplicate:
Conditional formatting of TRUE / FALSE values in an Excel 2010 range
I have a spreadsheet where I have cell within a row that will contain specific text via data validation. ...
- 31
3
votes
4
answers
27k
views
Different colors for different consecutive text values in Excel
I would like to apply different colors to different consecutive text values. But unfortunately, applying color scale works only on numbers!
I’m doing this to easily identify the repeated values sorted ...
- 201
3
votes
4
answers
14k
views
Excel - Overflow the text of a cell to an adjacent cell that contains a formula
I have a table filled with datas that comes from other tables. The formulas in the cells of my table are quite simple (reference to a cell in another worksheet).
In my table, I have "Title" rows (...
3
votes
1
answer
32k
views
Format Text Bold based on the value of a cell
I am trying to make the text of cell A1 bold if the value of B1 is "a"?
Is there a formula that can achieve this or do I have to write a macro that can perform this task? Alternatively can I use ...
- 131
3
votes
2
answers
279
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....
- 336
3
votes
6
answers
22k
views
Vertical Histogram in Excel with Conditional Formatting
I'm able to do data bar conditional formatting in Excel 2010, but I can't figure out how to switch the bar axis to make it vertical. Is there any way around this? Can I simulate a vertical histogram ...
- 33
3
votes
2
answers
4k
views
Apply Custom Number Formatting to Error values
I'm trying to apply a custom number format to cells in Excel 2007 that return an error value.
I understand that I can apply custom number formatting to number/text values in the form POSITIVE;...
- 205
3
votes
1
answer
3k
views
Excel - Highlight Unique Values Across a Row, Apply to Multiple Rows
What I am trying to achieve is create a bare bones diff tool using Excel. I have multiple files, each containing one column of data, and I would like to paste each file into a single column in Excel. ...
- 135
3
votes
1
answer
1k
views
Excel Conditional Formatting cell's value that has number suffix, like 99th, 23rd, or 1st
So I am filling a cell with Blue, Green, Yellow, or Red based on a Percentile Rank that is returned, that is numbers like ( >=90th=Blue, >=60th=Green, >=30th=Yellow, <30th=Red )
Currently ...
- 413
3
votes
2
answers
6k
views
How can I format data bars based on value in Excel 2010?
I have a dataset containing values from 0 to 584. I would like to illustrate this using data bars, but I would like the databars to be green when the number is greater than 150, and red when the ...
- 1,749
3
votes
3
answers
13k
views
Excel: conditionally format a cell using the format of another, content-matching cell
I have an Excel spreadsheet where I’d like to be able to create a “key” of formatted cells with unique values, and then in another sheet format cells using the key formatting.
So for example, my key ...
- 133
3
votes
1
answer
2k
views
Conditional Formatting to highlight cells with highest frequency
I have an Excel table like the following:
1 2 3 4 5 6 7 8
----------------------
3 3 3 3 3 1 1 1
4 4 5 4 4 4 4 3
and I need a conditional style to highlight the items with the ...
- 228k
3
votes
1
answer
2k
views
LibreCalc: Change background color
I have 3 columns: R G B. I want the 4th column to have the background color corresponding the the RGB in the first 3 columns.
So sort of:
<td bgcolor="#"+A1*10000+B1*100+C1> Cell content </...
- 4,809
3
votes
1
answer
3k
views
How do I limit a conditional formatting rule only to rows in a *table*?
It drives me crazy that I don't know how to limit the range of a conditional formatting rule to the last row in a table I've created.
The rule I have created is supposed to turn all blank cells in ...
- 181
3
votes
1
answer
5k
views
MS Excel conditional formatting range changes after editing cells
I have a sheet in MS Excel 2010 with conditional formatting applied to the selected range as on the screenshot:
.
But if I edit connected cells, CF immediatly applies to some of them in very strange ...
- 51
3
votes
1
answer
95
views
Formatting Entire Row with Multiple Rules
I would like to change the formatting (specifically text color) for an entire row based on which selection is chosen from a drop down list in Column C. Example:
If C2 equals 'XX'(one of the ...
- 33
3
votes
1
answer
4k
views
Excel conditional formatting to highlight entire row if one date is not equal to another date in that row
I'm trying to use conditional formatting to highlight an entire row if the date in column D is greater than the date in column B. I can't seem to make it work. Is it a problem because it's a date ...
- 61
3
votes
1
answer
4k
views
Excel: Conditional Formatting (Highlighting) Values Based on Another Worksheet
I have a workbook that has two worksheets.
The first worksheet is simply a list of the first 78,498 prime numbers in a single column, A1-A78498.
The second worksheet has a grid of numbers from 1 to ...
- 153
3
votes
1
answer
7k
views
How to change a cells color depending on the value of the cell above it/before it for hundreds of lines
I have hundreds of lines with varying values (currency) and want the cell to change color depending on the value above it eg. Greater than = Green, Less than = Red = Equal to = Yellow. ie:
A1 is $100
...
- 33
3
votes
2
answers
4k
views
Apply Conditional Formatting VBA to All workbook Sheets
I am very new to this, I am making conditional formatting to highlight numbers above the average of all values in a row.
I need to apply this formatting to all rows in all sheets in the workbook.
...
- 31
3
votes
1
answer
4k
views
Excel cell reference to it´s previous value (conditional formatting)
I have a good handle on Conditional Formatting in general. However, one use-case I have not been able to figure out yet, perhaps someone could give me a cue as to how to achieve it.
I am creating a ...
- 33
3
votes
1
answer
6k
views
Excel: Detect #N/A (German: #NV) for conditional formatting
I have a huge Excel sheet where I regularly import mass data into. This data is input to calculations per row. Sometimes, the imported data results in a #NV error in the calculated data.
I would ...
- 333
3
votes
1
answer
759
views
References in color-scale conditional formatting formulae for ranges
I'm using Libreoffice Calc (v6.1b).
Suppose I have numeric data in sheet column, A and I'm applying conditional formatting to sheet column B. I want each cell of B to have a color on a scale ...
- 9,513
3
votes
2
answers
13k
views
Case-sensitive highlighting of duplicate cells in Excel
I have a column of 1-2 letter IDs in excel. It's fine if the capitalization is different (i.e., "AB" and "Ab"), but it's an issue if any two are exactly the same (i.e., "AB" and "AB"), so I want to ...
- 33
3
votes
1
answer
1k
views
Is there an easy way to manage a large number of conditional formatting rules?
Using Excel 2010, I've created a very large number of conditional formatting rules. You can see how small the thumb is - there are a lot of them.
Unfortunately, the conditional rules manager dialog ...
- 378
3
votes
1
answer
697
views
Excel 2013: Conditional Formatting changes after Query has run
I have conditional formatting on Columns A to N. Columns F to N are updated every time I open the spreadsheet as it pulls info from a SQL script. The problem I'm having is the following:
My ...
- 77
2
votes
5
answers
47k
views
How to hide number but keep Excel cell background color?
I got a sheet like the below figure. I want to hide all the number and just keep the background color. How can I do it?
- 12.3k
2
votes
3
answers
23k
views
EXCEL: How to conditional format a thick bottom border to an entire row based on one column value? [duplicate]
Possible Duplicate:
Excel conditional formatting to highlight entire row if one date is not equal to another date in that row
I have the following rows...
1 AA 12 13 4 7
1 A 12 ...
- 339
2
votes
2
answers
244
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 ...
- 21
2
votes
2
answers
6k
views
Conditional formatting issues in Excel
I seem to be having a problem with conditional formatting in Excel 2010. I have a set of date points, percentages between 1 and 100. I applied three rules in the following order,
if greater than 75% ...
- 21
2
votes
1
answer
299
views
Format cell in Excel if contents are a number or a function
I understand conditional formatting but I don't see how it can help me. I'm looking at a sheet with many product entries and for most of them I get values from a table (vlookup) while for others I ...
- 921
2
votes
2
answers
4k
views
Excel, use conditional formatting to highlight dates in current fiscal year?
In Excel 2016, I'm trying to use conditional formatting to highlight dates in a current fiscal year, but I can't figure out how to write the formula correctly. The fiscal year goes from 10/01/19 to 09/...
- 51
2
votes
2
answers
1k
views
Conditional formatting on the first x number of rows, regardless of filter or sort, in Excel
I'm trying to find a way to easily identify the first ten rows in a table column, no matter how it's been sorted/filtered. Is there a way to use conditional formatting to highlight these cells?
...
- 123
2
votes
2
answers
2k
views
How do I apply conditional formatting to a table from a graph?
As you can see in this example below, I would like to be able to put in red the negative numbers that are in the table below the graph. I would like to apply a conditional formatting on this table (...
- 83
2
votes
1
answer
9k
views
customize excel 2010 highlight track changes
Is there any way to customize how Excel highlights cells on Track Changes? My customer requested us to "highlight anything you've changed by changing text color to red," and I'd like to implement this....
- 1,410
2
votes
2
answers
5k
views
How do I format cells in one column based off cells in another column?
So I found some similar questions to this already answered here, but they don't go deep enough for what I want to do, and was hoping to find some more help.
At a high level I want to format some ...
- 123
2
votes
1
answer
3k
views
Excel Conditional Formatting of Rows based on multiple columns
I have a spreadsheet with columns A-K and rows 1-56. If any value in D3 through J3 goes below 0, I want the entire row (A through K ) to have a special format. I've gotten close in Excel 2010 by using ...
- 228
2
votes
1
answer
946
views
Adding conditional formatting to the status line in GNU screen
In GNU screen in the status it is possible to put conditional things, such as
%?%F%{+b r}%:%{+b b}%?
%F is the test; if true it turns on bold red formatting %{+b r}, whereas if false it turns on ...
- 228