The tutorial shows how to track changes in Excel: highlight changes on screen, list changes in a separate sheet, accept and reject changes, as well as monitor the last changed cell.
When collaborating on an Excel workbook, you may want to keep track of the changes that have been made to it. This could be especially useful when the document is almost finished and your team is making the final revisions.
On a printed copy, you could use a red pen to mark edits. In an Excel file, you can review, accept or reject changes electronically by using the Track Changes feature specially designed for it. Furthermore, you can monitor the latest changes by using the Watch Window.
By using the built-in Track Changes in Excel, you can easily review your edits directly in the edited worksheet or on a separate sheet, and then accept or reject each change individually or all changes at a time. To use the Excel tracking feature most effectively, there are a few points for you to remember.
Excel's Track Changes works only in shared workbooks. So, whenever your turn on tracking in Excel, the workbook becomes shared, meaning that multiple users can make their edits simultaneously. That sounds great, but sharing a file has its drawbacks too. Not all Excel features are fully supported in shared workbooks including conditional formatting, data validation, sorting and filtering by format, merging cells, to name a few. For more information, please see our Excel shared workbook tutorial.
If the Track Changes button is unavailable (grayed out) in your Excel, most likely your workbook contains one or more tables or XML maps, which are not supported in shared workbooks. In that case, convert your tables to ranges and remove XML maps.
In Microsoft Excel, you cannot revert the worksheet back in time by undoing changes like you can do in Microsoft Word. Excel's Track Changes is rather a log file that records information about the changes made to a workbook. You can manually review those changes and choose which ones to keep and which ones to override.
Excel does not track every single change. Any edits you make to cell values are tracked, but some other changes like formatting, hiding/unhiding rows and columns, formula recalculations are not.
By default, Excel keeps the change history for 30 days. If you open an edited workbook, say, in 40 days, you will see the change history for all 40 days, but only until you close the workbook. After closing the workbook, any changes older than 30 days will be gone. However, it's possible to change the number of days for keeping change history.
Now that you know the basics of Excel Track Changes, let's talk about how to enable and use this feature in your worksheets.
To view the changes made to a given workbook by you or other users, perform these steps:
Excel will highlight edits by different users in different colors as shown in the next section. Any new changes will be highlighted as you type.
With Highlight changes on screen selected, Microsoft Excel shades the column letters and row numbers where changes were made in a dark red color. At the cell level, edits from different users are marked in different colors - a colored cell border and a small triangle in the upper-left corner. To get more information about a specific change, just hover over the cell:
Apart from highlighting changes on screen, you can also view a list of changes on a separate sheet. To have it done, perform these steps:
For this, go to the Review tab > Changes group, click the Share Workbook button, and then select the Allow changes by more than one user at the same time check box. For more detailed steps, please see How to share a workbook in Excel.
This will list all tracked changes on a new worksheet, called the History sheet, which shows many details about each change including when it was made, who made it, what data was changed, whether the change was kept or not.
The conflicting changes (i.e. different changes made to the same cell by different users) that were kept have Won in the Action Type column. The numbers in the Losing Action column refer to the corresponding Action Numbers with information about the conflicting changes that were overridden. As an example, please see action number 5 (Won) and action number 2 (Lost) in the screenshot below:
Tips and notes:
To accept or reject changes made by different users, go to the Review tab > Changes group, and click Track Changes > Accept/Reject Changes.
In the Select Changes to Accept or Reject dialog box, configure the following options, and then click OK:
Excel will show you the changes one by one, and you click Accept or Reject to keep or cancel each change individually.
If several edits were made to a given cell, you will be asked which of the changes you want to keep:
Alternatively, you can click Accept All or Reject All to approve or cancel all changes in one go.
By default, Excel keeps the change history for 30 days and permanently erases any older changes. To keep changes history for a longer period, perform these steps:
When you no longer want changes to be highlighted in your workbook, turn off the Excel Track Changes option. Here's how:
In some situations, you may not want to view all of the changes made to a workbook, but only to monitor the last edit. This can be done by using the CELL function in combination with the Watch Window feature.
As you probably know, the CELL function in Excel is designed to retrieve information about a cell:
The info_type argument specifies what type of information you want to return such as a cell value, address, formatting, etc. Overall, 12 info types are available, but for this task, we will use just two of them:
Optionally, you can utilize other into types to retrieve additional information, for example:
By omitting the reference argument, you instruct Excel to return information about the last changed cell.
With the background information established, perform the following steps to track the last changed cell in your workbooks:
=CELL("address")
=CELL("contents")
Like show in the screenshot below, the formulas will display the address and current value of the last cell changed:
That is great, but what if you move away from the sheet with your Cell formulas? To be able to monitor the latest changes from any sheet that you have currently open, add the formula cells to the Excel Watch Window.
This places the formula cells into the Watch Window. You can move or dock the Watch Window toolbar wherever you want, for example at the top of the sheet. And now, whatever worksheet or workbook you navigate to, the information about the last changed cell is just a glance away.
This is how you track changes in Excel. I thank you for reading and hope to see you on our blog next week!
16 responses to "How to track changes in Excel"
Better than documentation from Microsoft !
Congrats!
I have never seen any website explaining in simple and detail way like this website!
This is the best excel site i ever seen, your site is so clear that any one can understand excel formulas and many more easily.
Thanks
hey tnx for your wonderful website.
i use excel to keep my accounting and production in whole year so i need to have all changes for all the time so i figured out when i save the file and close it and then i open it again the tracking option turns off and if i forget to enable it every morning that i open the file i will lose changes trackong and after enabling it in the middle of the day all the times which adds to cell tracking dialog is the time i enabled it. could you help me with that please? i what tracking option enabled all the time in my file
I was working on a shared workbook with 3 other people on the same day. After a while the data got jumbled up. Changes that were made appeared on cells they were not made in. For example, a change that I made to row 23 appeared on row 22. Someone suggested that it could be due to how one of us sorted the data but so far we havent2 been able to conclude that. Does excel track who sorted the data and is there a way to fix the issue of changes appearing in the wrong cell?
I need your help for understanding the tracking file process
I would like to create an excel document which needs to have sheets linked each other.
Thank you in advance for your hel
Rgds,
Ruff
Thank you so much for this stes-by-step Excel guides.
I didn`t see this "track changes" in my excel sheet. I`m using excel 2016. how to find this track changes option in my excel?
Hello, is it possible to retrieve changes in a workbook of Excell 2013 even when the Track changes feature is not enabled- Thank you.
I have a spreadsheet that is used by several people and I want to know if there is a way to track who uses fill color in the cell (example: I have 25% in a column, 75% in a column and full amount in a column - I highlight 25% - light purple, 75% light Yellow and full amount light green when amounts are paid and others are white if not paid)----so if someone colors a cell the wrong color how can I tell? Is there a formula I can use to put in my excel (macro) spreadsheet to track?
Thanks in advance.
This procedure only works on an excel file shared across the network.
This feature is not available if you are using excel online since anyone from the internet can make changes, from there no option is able to track changes.
Hello! Do you know if they make any plugins too pfotect
against hackers? I'm kinda paranoid about losing everything I've
worked hard on. Any tips?
Please explain why should we track changes to a workbook when working with others? what feature of workbook collaboration can i use in the future?
I have a shared Excel 2016 file for which I am setting the track changes to show all changes by everyone on screen. After I save, close, and open the file again. The setting does not stay to highlight the tracked changes that were done by me and others, to the next user. Why does the setting revert on its own to show changes since my last save? Is there some way I can prevent this?
In the latest excel version, sharing has been replaced with co-authoring. Can we track cell level changes and cell history in co-authored files? Actually this is one feature where Google Sheets beats excel. Your views please.