conditional-formatting interview questions
Top conditional-formatting frequently asked interview questions
Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?
Source: (StackOverflow)
This should be the simplest example of conditional formatting but I can't figure it out. I havave two columns seperated by several dozen other columns ('C'-original data, 'BO'- calculated value) and I need to highlight the calculated value if it is not equal to the original value. I can do this on a cell by cell basis but can't see how to apply it to a whole column withoug adding a rule for every row (there are many 1000s of rows).
What am I missing?
Source: (StackOverflow)
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?
Source: (StackOverflow)
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?
Source: (StackOverflow)
How do you automatically set the background color of an entire row based on the value of a specific column in that row in Open/Libre Office? There are similar questions for Excel, but this doesn't completely work in Libre Office.
I can set a conditional formatting rule using a formula to set the color of a specific cell based on the value in that cell (e.g. =$D5), but I can't figure out how to apply the formatting to the entire row.
Source: (StackOverflow)
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. How can i escape the question mark?
These don't seem to work:
Source: (StackOverflow)
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 tried writing rules that applied to the entire spreadsheet and keyed off of column headers to highlight the data I wanted to check.
Example: =AND(A$1="Check This Column For Blanks),ISBLANK(A1)) applied to =$1:$1048576
However, even with the rule explicitly applied to the entire sheet, it was still automatically adjusting (and breaking in weird ways by doing so) as I worked in the sheet.
How can I avoid this?
Source: (StackOverflow)
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 exactly what I want to do, but the suggested solution didn't work for me--it just highlights the first row of each matching value.
I know how to use conditional formatting, but for the life of me I can't figure out or find any pointers on the net so far to make this work. Ideas?
Here is a link to a picture of how I want my spreadsheet to look when I'm done. Basically I want every other Disp Number value row to be highlighted. Seems like it would be a common thing to want to do, and I've seen this asked for in various places, but people struggle with making it work.
Picture of desired end result
Source: (StackOverflow)
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 of rows and/or columns the conditional formatting rule set becomes very fragmented, with the same rules repeated for a different ranges. e.g.
Make $A$1:$A$2 red
Make $A$3:$A$4 red
Make $A$5:$A$9 red
Make $A$10:$A$20 red
Make $A$21:$A$30 red
...
Make $B$1:$B$2 blue
Make $B$3:$B$4 blue
Make $B$5:$B$9 blue
Make $B$10:$B$20 blue
Make $B$21:$B$30 blue
....
Is there a good practice way of preventing this, or am I doomed to cleaning up the rule sets manually when they get too messy?
Source: (StackOverflow)
ColA Col B ColC
Project A A.pdf A.pdf
Project B No Link
I have a list of project records. One column (e.g., B) may have a reference to a pdf file. If I create a formula
=if(isblank(B2),"No Link",hyperlink(folderURL + B2),B2)
What I want is to place a hyperlink on only those rows where column B contains a pdf reference, and insert text "No Link". I get the proper text values, but in all rows have the hyperlnk default appearance and behavior -- Excel seems to be creating "phantom links" out of the "No Link" cells.
Clicking on the "No Link" value in Col C activates an attempt to open the file listed in Col B, which returns a No file found error.
Source: (StackOverflow)
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 the lowest numbers in yellow, with a gradient for numbers in between. This is what happens if I select a row (specifically, the 15 cells in the row which contain my data) and then click Conditional Formatting > Color Scales > Red-Yellow Color Scale.
However, I have 300 rows and it will take too long to select each row individually and set the Conditional Formatting. On the other hand, if I select the entire range and apply Conditional Formatting, Excel calculates the colours based on the entire range, when in fact I want them calculated on a row-by-row basis. For example, take this sample data:
1 2 3
4 5 6
7 8 9
The output I want, using Y for yellow, O for orange, R for red, is:
Y O R
Y O R
Y O R
However, if I select the entire range and apply Conditional Formatting, I get:
Y Y Y
O O O
R R R
Is there a way of accomplishing this without doing it one row at a time?
Source: (StackOverflow)
I have a spreadsheet with lots of data. The data is grouped so that all the rows with a certain value in one column (say C) are grouped together. I want to highlight the "boundaries" - i.e., the first row where the value in column C is different from its immediate predecessor.
For example:
A B C
1 Type Val Category
2 Cat 1.77 1
3 Newt 1.937 1
4 Toad 1.03 3
5 Newt 0.78 3
6 Dog 0.78 3
7 Worm 0.78 9
In this example, I want Excel to automatically find and highlight rows 2, 4, and 7, since those are where the value in C changes from the row before.
I tried using a Conditional Formatting rule with the formula $C1<>$C2 - hoping that Excel would increment through the rows finding & highlighting the transition rows - but it didn't work. Any ideas how to get the results I'm looking for?
Source: (StackOverflow)
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 time. Here's how (Excel is in French at work, translations may be off):
Select three cells from the pivot table
Select Conditional formatting -> Highlight rules (first one) ->
Other rules
Select "Apply formatting only to first or last values" in the new
window
Hit OK
Excel will stop responding. Below is the Event from the Windows Event viewer (Bing translated from French):
Faulting module name: EXCEL.EXE, version: 15.0.4535.1507, time stamp: 0x52282c09
exception Code: 0xc0000005 error offset: 0x00000000008104fb
faulting process ID: 0xfb0
faulting application start time: 0x01cec9c04bac50a7
faulting application path: C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE
faulting module path: C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE
report ID: 0d6d418d-417c-11e3-9520-e0cb4e24e8d1
This is on my computer. I have Office 2013 64 bits with no add-ins and have been able to reproduce the same issue as hers. She has Office 2010 32 bits with some add-ins (mainly for VBA scripting and formulas).
I can't deactivate DDE since pivot tables will stop working.
The error on her PC is the same as mine.
The error will still show up with Excel in safe mode. (/s)
On both her PC and mine there is nothing checked on the compatibility tab for Excel.
This is a really weird and annoying issue, and so far I haven't been able to fix it.
Source: (StackOverflow)
I have data in a range of cells (say six columns and one hundred rows). The first four column contains data and the sixth column has a limiting value. For data in every row the limiting value is different. I have one hundred such rows. I am successfully using Conditional formatting (e.g. cells containing data less than limiting value in first five columns are made red) for 1st row. But how to copy this conditional formatting so that it is applicable for entire hundred rows with respective limiting values.
I tried with format painter. But it retains the same source cell (here limiting value) for the purpose of conditional formatting in second and subsequent rows. So, now I am required to use conditional formatting for each row separately s
Source: (StackOverflow)
I have two sheets:
- Users in their respective groups and
- A compilation of those same users with an expiration date.
I'd like to highlight any cell in the the first sheet if their respective date is less than the current day.
EDIT
Using tylyn's formula, I'm trying to add a secondary rule that compares today's date and the date listed in the table and if that number is less than 30 days, format a different color.
Here is what I tried:
=VLOOKUP(A1,'Sheet name 2'!$A$1:$B$1000,2,FALSE)>TODAY()*TODAY()-VLOOKUP(A1,'Sheet Name 2'!$A$1:$B$1000,2,FALSE)<30
Also tried: =DATEIF(TODAY(),VLOOKUP(A1,'Sheet name 2'!$A$1:$B$1000,2,FALSE),"D")<30
FINAL EDIT
If I had used DATEDIF instead of DATEIF, it would have worked
Source: (StackOverflow)