Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare & highlight changes between two worksheets?
I two worksheets of data & want to compare them and highlight the changes
between the two. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare & highlight changes between two worksheets?
Hi,
There are addins (some free, some not) which may do what you want. Here is a start: http://www.google.com/search?hl=en&s...+ex cel+files However, lets say you want to compare Sheet1!A1 with Sheet2!A1 and so on, and highlight the difference. Suppose you want to highlight the differences in Sheet1. Suppose the range of concern is A1:D10. Move to Sheet2 and highlight the range A1:D10. Click in the Name Box (on the left of the Formula Bar) and type Data, then press Enter. Move back to Sheet1 and select A1:D10, then apply conditional formatting. To conditionally format your cell(s): In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =INDEX(Data2,ROW(),COLUMN())<A1 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =INDEX(Data2,ROW(),COLUMN())<A1 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Katie" wrote: I two worksheets of data & want to compare them and highlight the changes between the two. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare & highlight changes between two worksheets?
Data2?
"Shane Devenshire" wrote in message ... Hi, There are addins (some free, some not) which may do what you want. Here is a start: http://www.google.com/search?hl=en&s...+ex cel+files However, lets say you want to compare Sheet1!A1 with Sheet2!A1 and so on, and highlight the difference. Suppose you want to highlight the differences in Sheet1. Suppose the range of concern is A1:D10. Move to Sheet2 and highlight the range A1:D10. Click in the Name Box (on the left of the Formula Bar) and type Data, then press Enter. Move back to Sheet1 and select A1:D10, then apply conditional formatting. To conditionally format your cell(s): In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =INDEX(Data2,ROW(),COLUMN())<A1 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =INDEX(Data2,ROW(),COLUMN())<A1 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Katie" wrote: I two worksheets of data & want to compare them and highlight the changes between the two. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare & highlight changes between two worksheets?
I see I called on Data and the other Data2, a mistake. Whatever you call it,
use the same name in the formula. The imporatant point is that if you want to do conditional formatting between sheets range names are required. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Wobbly" wrote: Data2? "Shane Devenshire" wrote in message ... Hi, There are addins (some free, some not) which may do what you want. Here is a start: http://www.google.com/search?hl=en&s...+ex cel+files However, lets say you want to compare Sheet1!A1 with Sheet2!A1 and so on, and highlight the difference. Suppose you want to highlight the differences in Sheet1. Suppose the range of concern is A1:D10. Move to Sheet2 and highlight the range A1:D10. Click in the Name Box (on the left of the Formula Bar) and type Data, then press Enter. Move back to Sheet1 and select A1:D10, then apply conditional formatting. To conditionally format your cell(s): In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =INDEX(Data2,ROW(),COLUMN())<A1 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =INDEX(Data2,ROW(),COLUMN())<A1 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Katie" wrote: I two worksheets of data & want to compare them and highlight the changes between the two. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to compare old and new datasheets and highlight changes | Excel Worksheet Functions | |||
Compare and Highlight Rows | New Users to Excel | |||
Compare two Cells and highlight third cell | Excel Worksheet Functions | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
compare info in 4 worksheets and highlight fields that are the sam | Excel Worksheet Functions |