Microsoft Excel – comparing figures in two worksheets using conditional formatting

I was at client site yesterday and was asked whether they could compare figures in two separate worksheets using conditional formatting.

This is indeed feasible as long as it is across worksheets rather than workbooks. If we want to check whether one figure is different to the other:

  1. Select the cells in the first worksheet that you want to compare.
  2. On the Home tab, in the Styles group, click the Conditional Formatting dropdown, click New Rule.
  3. Click Use a formula to determine which cells to format.
  4. From the formula box, click in the first cell to be compared and remove the $ in front of the row number (provided you want to compare cells right down the column). Type <> – this is how to put not equal to, then click in the cell in the other workbook that should be compared and again get rid of the $ in front of the row number.
  5. Click on Format and select how you want the cell to be formatted.
  6. Click OK twice. The formatting will be applied.

conditional formatting across worksheets

Find out more about our Microsoft Excel training in Dorset, Wiltshire, Hampshire and Somerset.

Leave a Reply

Your email address will not be published. Required fields are marked *