Tip of the Week: Setting Up Excel to Track Changes

Today’s software solutions are hugely benefited by the collaborative capabilities that are now included in them, but this is not to say that there isn’t the potential for confusion as people make changes to documents. Some software lets you track these changes, which is particularly useful in particularly dense applications like Microsoft Excel.

For this week’s tip, we’ll show you how you can easily set Excel up to keep track of any changes that your collaborators have made. Fair warning, this will involve a little bit of meddling in your menu options, so don’t hesitate to ask someone for help if it would make you feel more comfortable.

It is also important to take note that the Track Changes function will not work in a spreadsheet that contains tables, unless the table is converted to a range. To do so, simply select the table, and in the Design tab, click Convert to Range.

Furthermore, other users can only collaborate on Excel workbooks that have been saved to a shared location, so make sure that your files are saved in the right spots so that others are able to access them.

Adding the Buttons to Track Changes

First, you need to have an easy way to access these options, so it makes sense to add them to your Ribbon menu if need be. Some versions include these options by default, but some don’t. If it isn’t already there, make sure you add the button to the menu like so:

• Right-click on the Ribbon menu and select the Customize the Ribbon option.
• On the dialog screen that appears, locate the Choose commands from: drop-down menu, and select All Commands. The other menu should be set to Main Tabs, which means you’ll be able to find Review in the list. Click on the + next to Review, and then click the New Group button.
• Right click on your New Group and rename it to “Track Changes.”
• With “Track Changes” selected, find Highlight Changes and Accept/Reject Changes in the left-hand list and click the Add button to add them to your “Track Changes” group.

Once it has been added to the Ribbon menu under the Review category, you’ll need to configure your Highlight Changes button. Click on it, and in the box that appears, select both Track changes while editing… and Highlight changes on screen. Click Okay.

All the changes made on the spreadsheet by anyone will be annotated with a small triangle in the corner of the cell, displaying the most recent change.

Reviewing Changes

Once everyone has had their say and the time comes to pass or veto the changes made, click the Accept/Reject Changes button. You’ll be prompted to Save the Workbook, and once you do that, the spreadsheet will display with the first edited cell highlighted and another dialog box labeled Select Changes to Accept or Reject.

All changes made will be listed, allowing you to pick the best option and click Accept, or Reject if none of the options improve anything. The cursor will move to the next cell to review, and you can repeat the process. If multiple cells were changed in the same way (as when all uses of “&” needed to be changed to “and” or percentages needed to be written out as such), the Accept All button will make the needed changes.

Hopefully, this will help make collaboration a simpler process for you and your team. For more tips and best practices, make sure you subscribe to our blog, or give our team a call at (217) 428-6449 for in-depth IT assistance.