Office hacks: Excel intelligent sense of data changes and add markers

We often check the data in Excel tables to modify, if you want to know in the first place which data have been modified, by marking the modified data is a good way to quickly identify this type of data. Using the relevant components of Excel, we can achieve the function of marking the modified data. In the following, we will operate in Excel 2019 as an example to illustrate.

1. Use good highlighting revisions Modify data to mark easily

For data modification, Excel comes with a highlighting revision function. However, Excel 2019 does not show the revision menu by default, you need to click “File → Options → Customize ribbon → not in the ribbon command”, positioned on the right side of the “Review”, click “New Group “, then locate the “New Group”, and then the left side of the “Track Changes (old)” menu added to the group (Figure 1).


Return to the Excel window, click “Review → Track Changes (old version) → highlight revisions”, check the “Edit when tracking revision information, while sharing the workbook”, select All Time, select the location of B2:B7 data (Figure 2).


In this way, when we make changes to the data in the original workbook, a triangle marker is added to the modified cell, and the modifier and the original data can be seen by mouse over the modified position (Figure 3).



If you use Word 2019, the default on the revision menu, if you feel that the above-mentioned add menu operation trouble, you can also directly paste the original worksheet into Word, and then click “Review → Revision”, so that the data modified in Word will add a red revision marker in front of the revised line, click to see the revision before and after the data comparison (Figure 4).


2. set the conditional formatting modify more eye-catching

Highlighting revisions requires setting the workbook to shared mode and manually selecting each revision area in order to display the revision. With conditional formatting, the shortcomings of highlighting revisions can be solved by not only marking the revision data with a striking color (instead of using triangle marks as in highlighting revisions), but also by displaying the original data in a separate column (without manual selection), and of course without setting the workbook to shared mode.

In the original workbook in the new worksheet, and then copy a copy of the data to the new table, click “Start → Conditional Formatting → New Rule” (Note: If the original table data set up a “highlight revision”, then you need to cancel this setting to set up (Note: If the original table data set to “highlight revisions”, then you need to cancel this setting in order to set the conditional format, otherwise the conditional format menu is not available). In the window that opens, select “use the formula to determine the cells to be formatted”, enter the formula bar “= B2 <> Sheet2!B2” (that is, the B2 value of the two forms are not the same), click “Format “, set to meet the conditions when the cell background color filled with red display (Figure 5).


Click “Conditional Format → Edit Rules”, select the above new rules, in the “Apply to” select B2: B7 data area. If there are multiple blocks of data, press and hold the Ctrl key to make multiple selections. In this way, when the data in the above area changes, the set conditional format will be applied (Figure 6).


In order to facilitate the comparison of the data before and after the modification, you can enter the “modification prompt” in C1, enter the formula “=IF(B2<>Sheet2!B2, “data has been changed, the original data is” & Sheet2!B2, “”) “, that when the B2 data of Sheet 1 is modified, in C2 display “data has been changed, the original data is the data copied from Sheet 2”, the formula drop down to fill. Then right-click Sheet2 and select “Hide”, so that when we modify the data in the original worksheet, due to the modified data and Sheet2 is different, the modified data cells will automatically fill in red, while in the C column will prompt the data has been modified, and will automatically display the original data values (Figure 7).


3. simple and efficient use of VBA settings to modify the coloring of data

If you need to mark a lot of documents, or workbook contains more than one worksheet, then each table to repeat the above conditions, the operation is not efficient. At this point, we can also use VBA code to achieve global automatic coloring of data changes.

Select the original worksheet above, right-click and select “View Code”, and enter the following code in the window that opens (Figure 8).


Dim x

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value <> x Then Target.Interior.Color = vbRed

End Sub

Code explanation: Here first use the DIM statement to set a variable, and then use the code to check the variable, when the active cell is entered into the new value is automatically filled with red (code vbRed, you can change to other colors).

Return to the Excel window, as long as we have made changes to the data, then change the data (including, of course, any changes to the text, etc.) the cell will become red, the default is to take effect on the entire worksheet, you can also set the code to take effect on the entire workbook, so that any changes to the data in the workbook will be automatically marked red (Figure 9).


Leave a Comment