I have two very large Excel spreadsheets. One is the original sheet, and the other is the output of my application I am writing, which seems to be changing cell values. I'd like to see if there is a pattern to how it is changing them.
I have copied the output spreadsheet into the original a few columns to the right of the original data. What I would like to do is create a formula to change the cell background color if the value in the output data is not the same as the input data.
I have found this guide online which shows a way to apply a rule to edit, from what I can tell, all cells' background values in some manner. But in the Excel dialogue "Create new rule" you can make one based on a formula, though this formula applies to all cells.
What I need is a formula where I can compare the current cell value to another cell value, and change the background color accordingly. I can then apply it to each column in the output data, drag down, and I should be able to see the altercations my software has made.
Best Answer
You didn't tag with your Excel version or provide any sample data, so let's say you are using Excel 2007 or greater, and your worksheet looks like this:
To compare the cells in column
D
to the corresponding cells in columnA
, do the following:D2:D7
by clicking on cellD2
and then dragging down to cellD7
, so that cellD2
is the Active Cell (as seen in the above screenshot)Home -> Conditional Formatting -> New Rule...
=D2<>A2
E2:E7
, and use the formula=E2<>B2
Note:
Since the columns I am formatting are contiguous, I could have simply selected cells
D2:E7
instead ofD2:D7
, and then skipped step9
. However, I am assuming that the columns you want to format are not necessarily contiguous.Optional:
Since background-color formatting hides cell gridlines, one additional thing I like to do in the Format Cells dialog is to click the Border tab, select "White, Background 1, Darker 15%" in the Color dropdown, and then apply that color to the Outline borders. That way, your formatted cells will appear to retain their gridlines.
Result: