Formatting Issues

Highlight Changed Cells and VLOOKUP Results in Excel

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

The user wants to highlight cells, including VLOOKUP results, that differ from their original values.

How to Highlight Changed Cells and VLOOKUP Results in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Compare current worksheet values with a stored snapshot after source data or formulas change.
Observed behavior
Excel does not retain previous VLOOKUP results for conditional formatting to compare automatically.
Before you start

Create the baseline before changing or refreshing the source data. Identify an empty helper column with one cell for each result you want to compare.

Solution 1Recommended

Compare current results with a baseline in a helper column

Use a values-only snapshot and conditional formatting to highlight changes in a result column.

This example compares current results in B2:B100 with their original values in Z2:Z100. Adjust the ranges to match your worksheet.

1
Copy the original results

Before refreshing the source data, select B2:B100 and press Ctrl+C or use Home > Copy.

2
Save a values-only baseline

Select Z2, then choose Home > Paste > Paste Values. Check that each baseline value lines up with its corresponding result in column B.

3
Create a formatting rule

Select B2:B100, then choose Home > Conditional Formatting > New Rule. Select the option to use a formula to determine which cells to format.

4
Enter the comparison formula

Enter =B2<>Z2, select Format, choose a fill color, and confirm the rule. Excel compares each cell in column B with the corresponding baseline cell in column Z.

5
Refresh and review

Refresh or edit the source data and check the highlighted cells in B2:B100. You can hide column Z by right-clicking its column heading and choosing Hide.

Compare current results with a baseline in a helper column
Keep the baseline until you need a new comparison point: The highlight shows differences from the saved snapshot, not a history of every change. To establish a new baseline, copy the current results and paste them as values over Z2:Z100.
Compare results in WPS Spreadsheets

Highlight Changed Lookup Results in WPS Spreadsheets

Use a values-only baseline and a conditional formatting formula in WPS Spreadsheets to identify results that differ from your original snapshot.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the current results, such as B2:B100.
  2. 2. Store the baseline: Before changing the source data, copy B2:B100 and use Paste Special > Values to place the snapshot in Z2:Z100.
  3. 3. Add a comparison rule: Select B2:B100 and open Home > Conditional Formatting > New Rule. Choose the formula-based rule option and enter =B2<>Z2.
  4. 4. Set the highlight: Choose a fill color, confirm the rule, and refresh or edit the source data to see which results differ from the baseline.
Highlight changed VLOOKUP results against a stored baseline.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface and conditional formatting tools.
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel highlight a VLOOKUP result that changed without a baseline?

No. Excel needs a stored earlier value or another comparison value. Copy the results before the source data changes and use Paste Values to save a baseline.

Why should I paste the baseline as values?

Pasting values preserves the current results instead of copying VLOOKUP formulas that could recalculate. Use Home > Paste > Paste Values when creating the snapshot.

How do I reset the comparison point?

Copy the current result range and use Paste Values over the helper-column baseline. The conditional formatting rule will then compare future results with that new snapshot.

Will the rule record every change made to a cell?

No. The rule only highlights whether the current value differs from the stored baseline. It does not keep a change history.

What if a VLOOKUP result shows an error?

A comparison involving an error may not produce a highlight. Handle expected lookup errors in the VLOOKUP formula, for example with IFERROR, before saving the baseline and applying the rule.