Learn how to display all changes made in a workbook. Author Jen McBee reviews the options available to users when changes are tracked in a workbook. She also shows how to accept or reject changes, and how to create a new worksheet that lists all the changes made.
- [Instructor] Did you know that you can track changes made to an Excel worksheet? While it's not as comprehensive as tracking changes in Word, once Track Changes is turned on, Excel will insert a blue triangle indicator in any cell where changes were made. In this video, I'll show you how to activate tracking. I'll review the tracking options and show you how to display changes in a new workbook. Did you notice the MOS icons next to two of the topics? Well, anytime you see this icon, you'll know that this topic just might be on the MOS Expert Exam, so pay close attention to these topics.
And at the beginning of each video, I'll let you know which exercise file we'll be using so you can open it up and follow along. I've downloaded the files from Lynda.com to my desktop and I'll go ahead and double click on the exercise files. Go on into Chapter Two, and here's my 02_01 track changes file that we'll be using for this video. Now the first thing that we do when we want to initiate tracking changes is go to our Review tab, go to the Changes group, and select Track Changes.
Right now we only have one option available to us and that is to highlight the changes. I'll go ahead and select that. In this Highlight Changes dialog box, the first thing we'll do is put a check mark next to Track Changes While Editing. And please not that this also will share your workbook so that others can go in and make changes. We have three fields that we'll populate. The first is When. Do we want to track all changes made? Or the changes since I last saved? Changes not yet reviewed, or since a particular date? We're going to go ahead and select All Changes.
Next, by whom? Do we want to see changes made by everyone? Or everyone but me? I'll go ahead and select everyone and that way, my changes will be tracked, as well as anyone else who makes changes. The third field allows us to select a range of cells that we want to track the changes for. Now, a lot of the times, I will just leave this blank because I wanted to actually track any changes made on this worksheet. At the bottom of the window, already checked off is Highlight Changes on the Screen.
And that will give us a visual indicator of the cell being outlined in blue, as well as a blue triangle in the upper left hand corner of that cell. Let's go ahead and click OK. We have a dialog window that says, this action will save the workbook. Do we want to continue? We're going to say okay, because this is going to give Excel a starting point. I'll click OK and we're ready to start tracking our changes. The first change that I want to make is to the state for Jordan Hinton.
I'll go ahead and click on cell E11, and change it from Pennsylvania to Texas. And as soon as I tab or exit out of that cell, you can see that the cell has a blue border, as well as a blue triangle around the cell. And when I mouse over the cell, I can see the comment that the change was made on February 27th at 9:54 a.m. And it even shows me the original value, which is PA, and what the new value of that cell is. Let's go on down to Row 15 and change the zip code for April Waters.
I'll click in F15 and that zip code should be 61416. And once again, as soon as I exit out of that cell, I can see it is outlined in blue, has a blue triangle indicator, and when I mouse over it, I can see the change that's been made. Now our next step is to go back and Track Changes. And let's Accept of Reject all the changes that have been made. I'll select Accept Reject Changes. Once again Excel will save the workbook at this point.
I'll click OK to continue. I have three options once again. Which changes do I want to review for acception or rejection? Do I want to look at everything that has not yet been reviewed, or since a particular date? I want to see everything not yet reviewed, so I'll make that selection. And once again, changes made by whom? Is it everyone? Everyone but me? Or the LinkedIn Corporation? I'll select Everyone.
I'll leave the Where field blank, because I do want to see all of the changes made throughout the entire worksheet so that I can review them to accept or reject them. Let's click OK. We're taken directly to our first cell where that change was made. It even shows me the change here, and at this point I can accept it, reject it, I could say I just want to go ahead and accept all the changes or reject all the changes or I can click Close and back out of this window.
I'm going to go ahead and accept this change. And once I do, it moves me down to the next cell, where a change has been made. Remember we changed the zip code from 60406 to 61416. I want to go ahead and accept that one also. Because we only had two changes in there, it closes the dialogue window for us. And now we can go back to Track Changes, Highlight Changes, and I can select List the Changes on a New Sheet.
When I click OK, watch what happens. It inserts a brand new worksheet called history. It actually shows me the date, the time, who changed the cell, the name of the worksheet that was changed, the cell reference, the new value, and the old value. Now, once you've gone through and accepted or rejected all your changes, you'll want to remove the shared status from the workbook. To do that, we'll go on the Review tab, back to the Changes group, and click on Share Workbook.
Once I remove the check mark from Allow Changes by more than one user at the same time, this workbook will no longer be shared. The other thing to keep in mind though is, your History tab will disappear. You will no longer see the history of the changes that were made. So if you want to hold on to that, you can save this workbook as another name. You can go to File, Save As, and maybe save it as 02_01 Track Changes with Changes.
So that you'll be able to see the history still. But we'll go ahead and remove the check mark, click OK, we have a dialog window that says, this action is going to remove the workbook from a shared use status. It's reminding us that the history will be erased. I want to go ahead and say yes, I want to take this out of the shared status. My changes that I made are still there. My history worksheet is gone, and the file is no longer in a shared status.
Now this is a great tool, it's a tool that I find a lot of users don't even know about. A lot of people track changes in Word, but they didn't know they could do that in Excel. I want you to practice using this new tool so you'll be familiar with the options, just in case you see the question on the MOS Expert Exam.
The course begins with an overview of the certification program and its costs. Next, Jennifer walks through all of the certification objectives, including hands-on experience with downloadable sample documents, so you can practice as you go. She wraps up with a full-length practice test that emulates exam 77-427, together with solutions to each of the exam challenges.
- Reviewing the exam format and learning objectives
- Tracking, retaining, and displaying all changes
- Identifying and troubleshooting errors with tracing
- Applying custom formats and layouts
- Applying advanced conditional formatting
- Using functions to format cells and apply advanced filters
- Preparing a workbook for internationalization
- Creating advanced formulas
- Using the LOOKUP and TRANSPOSE functions
- Using functions to serialize dates and times
- Creating advanced charts and tables