您(Are)是否正在寻找一种方法来跟踪对Excel 电子表格(Excel spreadsheet)所做的更改?在很多情况下,您必须将文件分发给多个人并跟踪所做的更改。您可能想要跟踪更改的时间、更改的人员、更改发生在哪个单元格以及更改了哪些数据。
Excel具有可以处理上述所有情况的内置跟踪功能。您可以直接在工作表上轻松查看所有更改,也可以接受或拒绝每个更改。关于Excel(Excel)的跟踪功能,有几点需要记住:
1. 打开跟踪并不意味着您可以通过撤消更改将电子表格及时恢复到以前的状态。它基本上是一个记录所有内容的日志文件(log file),仅此而已。您可以手动查看删除或添加了哪些数据,但您必须自己对电子表格进行更改。
2. 打开跟踪并不意味着您所做的每一个更改都会被记录下来。存储在单元格中的任何数据都会被跟踪,但其他更改(如格式化)则不会。未跟踪的其他更改包括隐藏/取消隐藏行和列、注释以及由于公式重新计算而更改的单元格值。
3.更改历史(Change history)默认仅保留 30 天。如果您对Excel 工作表(Excel worksheet)进行更改,然后在 45 天后再次打开该工作簿,那么您将能够看到所有 45 天的更改历史记录(change history),直到您关闭该工作簿。当您关闭它时,任何超过 30 天的更改历史都将消失。(change history)这意味着下次打开它时,您将无法看到 45 天前所做的更改。
4. 只要您打开跟踪,工作簿就会成为共享工作簿。这意味着多个用户将对文档进行更改。
现在您已经了解了Excel中跟踪的工作原理的一些基础知识,让我们来谈谈如何启用它、更改设置和跟踪更改!
启用跟踪
让我们先打开跟踪。打开Excel,然后单击(Excel and click)功能区上的“审阅”(Review) 选项卡。在最右侧,您应该会在“更改(Changes)”部分下看到一个名为“跟踪更改 (Track Changes )”的选项。
单击按钮并选择 突出显示更改(Highlight Changes)。您将看到一个对话框(dialog box),您现在需要在其中选中“编辑时跟踪更改”选项。这也分享了你的工作簿”(“Track changes while editing. This also shares your workbook”)。
您在这里有几个选项,包括 何时(When)、谁(Who)和在哪里(Where)。对于When,All意味着每个更改都将突出显示。您还可以选择突出显示自上次保存文档以来的更改,自特定日期以来的更改,或尚未查看的更改。
如果您选中 Who,您可以选择跟踪任何人所做的更改或(anyone or changes)除您之外的所有人所做的更改。Where 选项允许您仅跟踪电子表格特定部分的更改。只需(Just)单击右侧的按钮,然后选择要跟踪的单元格范围。
最后,如果您不希望其他人知道您正在跟踪更改,您可以取消选中突出显示屏幕上的更改选项。(Highlight changes on screen)默认情况下,一旦您开始跟踪并选择了此选项,任何更改的单元格都会在左上角显示一个小箭头,表示它已更改。
此外,如果您单击已更改的单元格(打开屏幕上的突出显示更改(Highlight changes on screen)),您将看到一个小弹出窗口(popup window),显示值的更改来源和更改时间以及更改时间。如果您取消选择“突出显示(Highlight)屏幕上(screen option)的更改”选项,则不会显示(t show)那个黑色小三角形。
因此,例如,您可以通过不选中该框来隐藏屏幕上的更改,将文件发送给必须对其进行更改的每个人,当您取回文件时,只需转到Track Changes并重新检查盒子。
配置跟踪
下一步是查看跟踪设置并调整它们以满足您的需求。为此,请单击直接位于Track Changes按钮左侧的(Track Changes)Share Workbook按钮。弹出共享工作簿(Share Workbook)对话框时单击高级(Advanced)选项卡。
您可以在此处将更改历史记录的保留天数更改为 30 天以外的天数。默认情况下,保存文件时会更新更改,但您可以使其每隔几分钟自动完成一次。最后,您可以选择处理冲突的方式:通过询问或在保存文件时简单地让最后一次更改 win(file win)。
查看更改
启用跟踪并进行一些更改后,您可以再次单击“跟踪更改”按钮(Track Changes button),单击“突出显示更改”(Highlight Changes),您会注意到“新工作表上的更改列表”( List changes on a new sheet)框不再灰显。
检查并单击确定。(OK. A)将添加一个名为History的新工作表,让您可以查看对该工作簿所做的所有更改。
在某些时候,您需要批准或拒绝更改。您可以查看所有更改,然后选择要保留或丢弃的更改。
只需再次单击Track Changes并选择 Accept/Reject Changes。选择选项以选择要接受或拒绝的更改。如果您想查看所有更改,只需选中When并确保将其设置为Not Reviewed。
单击“确定”,Excel(Click OK and Excel)将开始向您显示所做的每项更改,并为您提供“接受(Accept)”或“拒绝(Reject)”选项。如果您愿意,您还可以接受全部(Accept All)或拒绝所有(Reject All)更改。
如果您拒绝更改,它将立即恢复到该单元格中的原始状态。值得注意的是,如果另一个单元格引用了被拒绝单元格的内容,那么当引用的单元格值(cell value)恢复时,该值也会改变。这可能会导致公式中断等,所以要小心。
而已!现在,您可以使用此内置功能轻松跟踪对Excel 电子表格(Excel spreadsheet)所做的任何更改。如果您有任何问题,请发表评论。享受!
How to Track Changes in Excel
Are you looking for a wау to track changes made to an Excel spreadshееt? There аre many cases where you have tо distribute a file to multiple peoрle and keep track of what changes were made. You might want to track when the change was made, who made the change, what cell the change occurrеd in and what dаta was changed.
Excel has built-in tracking features that can handle all of the above cases. You can easily review all of the changes directly on the worksheet and you can also accept or reject each change. There are a few points to remember about the tracking features of Excel:
1. Turning on tracking does not mean that you can revert the spreadsheet back in time to a previous state by undoing the changes. It’s basically a log file that records everything and that’s it. You can manually go and see what data was deleted or added, but you’ll have to make the changes to the spreadsheet yourself.
2. Turning on tracking doesn’t mean every single change you make will be recorded. Any data stored in a cell is tracked, but other changes like formatting are not. Other changes that are not tracked include hiding/unhiding rows and columns, comments, and cell values that change to due a formula recalculation.
3. Change history is only kept for 30 days by default. If you make changes to an Excel worksheet and then open the workbook again 45 days later, then you’ll be able to see the change history for all 45 days until you close the workbook. When you close it, any change history older than 30 days will be gone. That means the next time you open it, you won’t be able to see that change you had made 45 days earlier.
4. Whenever your turn on tracking, the workbook becomes a shared workbook. This means that multiple users will be making changes to the document.
Now that you know some basics of how tracking works in Excel, let’s talk about how to enable it, change settings and keep track of changes!
Enabling Tracking
Let’s go ahead and turn on tracking first. Open Excel and click on the Review tab on the ribbon. At the far right, you should see an option called Track Changes under the Changes section.
Click the button and choose Highlight Changes. You’ll get a dialog box where you now need to check off the option “Track changes while editing. This also shares your workbook”.
You have several options here including When, Who, and Where. For When, All means every change will be highlighted. You also have the option of highlighting changes from the last time you saved the document, since a specific date, or changes that have not yet been reviewed.
If you check Who, you can choose to track changes made by anyone or changes made by everyone except you. The Where option allows you to track changes only for a specific portion of the spreadsheet. Just click the button at the right and select the range of cells you want to keep track of.
Finally, you can un-check the Highlight changes on screen option if you do not want others to know you are tracking changes. By default, once you start tracking and this option is selected, any changed cell will show a small arrow at the top left to indicate it was changed.
Also, if you click on a changed cell (with Highlight changes on screen turned on), you’ll get a little popup window showing what the value was changed from and changed to and at what time. If you deselect the Highlight changes on screen option, that little black triangle won’t show up.
So what you can do, for example, is hide the changes on the screen by not checking the box, send out your file to everyone who has to make changes to it, and when you get it back, just go to Track Changes and recheck the box.
Configure Tracking
The next step is to take a look at the settings for tracking and adjust them to fit your needs. To do this, click on the Share Workbook button that is directly to the left of the Track Changes button. Click on the Advanced tab when the Share Workbook dialog pops up.
Here you can change the number of days for keeping change history to something other than 30 days. By default, changes are updated when the file is saved, but you can make it so that it is automatically done every few minutes. Lastly, you can choose how you want to deal with conflicts: either by being asked or simply letting the last change when saving the file win.
Viewing Changes
Once you have enabled tracking and made a few changes, you can click on the Track Changes button again, click on Highlight Changes and you’ll notice that the List changes on a new sheet box is no longer grayed out.
Check that and click OK. A new worksheet called History will be added that will let you view all the changes that have been made to that workbook.
At some point you’ll need to either approve or reject the changes. You can review all the changes and then pick and choose which ones you want to keep or discard.
Simply click on Track Changes again and choose Accept/Reject Changes. Select the options to choose which changes you want to accept or reject. If you want to review all changes, just leave When checked and make sure it is set to Not Reviewed.
Click OK and Excel will begin to show you each change that was made and give you the option to Accept or Reject. You can also Accept All or Reject All changes if you like.
If you reject a change, it will immediately revert back to what was originally in that cell. It’s worth noting that if another cell references the contents of the rejected cell, then that value will also change when the referenced cell value reverts back. This can cause formulas to break, etc, so be careful.
That’s it! Now you can track any changes made to your Excel spreadsheet easily by using this built-in feature. If you have any questions, post a comment. Enjoy!