Conditional formatting - value not equal to first row values

[stisso]

Hello - I've been working on this by myself all morning but I know there's an easier way. Hoping you can help!

I have a spreadsheet of multiple choice responses for an exam. The first row is the correct answer for each question (i.e. instructor's sheet), and each row has the corresponding responses for a student. I would like to highlight the cells where a student's answer does not match the instructor's answer. I have been able to do this for a single column (so that all the incorrect answers for that question turn red) using conditional formatting.

Is there any way to do this for the worksheet without doing it column by column? I have been using the drop down menu (conditional formatting) because I'm a complete noob on excel. Basically I just want any values in the worksheet to be red if they don't match the values in that column's first row.

Any help you can provide would be very appreciated!!!!!

[Tony Valko]

Can you post a SMALL sample file so we can see the layout?

[Cheeky Charlie]

Select A1
Select all cells (ctrl+A twice)
Create a new rule in conditional formatting.
You want the rule to be based on a formula.
Use this formula:
=A1<>A$1

Note the dollar sign

The A1 part will refer to each cell (as in for A1 it refers to A1, for C2 it refers to C2)
The A$1 part will refer to the column dynamically (as above) but the row will always be 1

So you have an equation which equates:
This cell <> the cell on row 1 of the same column

this evaluates to FALSE for correct answers, and TRUE for incorrect answers, so this will highlight incorrect answers. The equivalent formula for correct answers is
=A1=A$1

HTH

[stisso]

Thanks so much for helping...I tried the formula you gave me, Cheeky Charlie, but it didn't work. Some columns highlighted everything regardless of the number in the first row for that column and the others were wrong as well. Thanks for explaining the placement of the $ symbol - I had read about it but understood that it would depend on the value of the preceding cell (and not in reference to the first cell in the first row for each column).

I'm going to try and attach an image of a screenshot. Hopefully it's not huge. The first row is the teacher's answer key (with each column representing a different question). Each subsequent row is a student's answer for the questions. Note that the first few columns have a discrepant answer in red. I did this using the conditional formatting function, but I created a separate rule for each column. I'd like to be able to do it for the whole spreadsheet if possible (basically highlighting/isolating all the incorrect answers so students can see at a glance which ones were wrong).

excel.jpg

Any ideas? If the picture works I'll do the formula above again and post the end result.

[Cheeky Charlie]

Hi Stisso,

Normally those dollar signs affect how a formula behaves when its copied. They have a similar effect in conditional formatting, but related to which cells are referenced for calculation of the conditional format formula (are the cells referred to fixed or are the different depending on which cell the condition is being calculated for?). I've realised in writing this that it's actually quite hard to explain!

As a general rule, actual file uploads are a LOT more helpful than pictures. On this occasion, I have a feeling about what might have gone wrong, I think your conditional formats may be referencing the wrong cells:

Please go through these steps:
Select columns E:AR
Cell E1 should be highlighted white whilst the rest of the selection is pale blue/grey.
This bit is as above:
Create a new rule in conditional formatting.
You want the rule to be based on a formula.
Use this formula:
=E1<>E$1