Track, retain, and display all changes

Keyboard Shortcuts

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 changesmade to an Excel worksheet?While it's not as comprehensive as tracking changesin Word, once Track Changes is turned on,Excel will insert a blue triangle indicatorin any cell where changes were made.In this video, I'll show you how to activate tracking.I'll review the tracking optionsand show you how to display changesin 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 beon the MOS Expert Exam, so pay close attentionto these topics.

And at the beginning of each video,I'll let you know which exercise file we'll be usingso you can open it up and follow along.I've downloaded the files from Lynda.comto my desktop and I'll go ahead and double clickon the exercise files.Go on into Chapter Two,and here's my 02_01 track changes filethat we'll be using for this video.Now the first thing that we dowhen we want to initiate tracking changesis go to our Review tab, go to the Changes group,and select Track Changes.

Right now we only have one option available to usand 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 marknext to Track Changes While Editing.And please not that this also will share your workbookso 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 everyoneand that way, my changes will be tracked,as well as anyone else who makes changes.The third field allows us to selecta range of cells that we want to track the changes for.Now, a lot of the times, I will just leave this blankbecause I wanted to actually trackany changes made on this worksheet.At the bottom of the window, already checked off isHighlight Changes on the Screen.

And that will give us a visual indicatorof the cell being outlined in blue,as well as a blue triangle in the upper left hand cornerof 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 Excela starting point.I'll click OK and we're ready to starttracking our changes.The first change that I want to makeis 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 madeon 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 codefor 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 changethat's been made.Now our next step is to go back and Track Changes.And let's Accept of Reject all the changesthat 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 reviewfor acception or rejection?Do I want to look at everything that has not yetbeen 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 madethroughout the entire worksheetso that I can review them to accept or reject them.Let's click OK.We're taken directly to our first cellwhere 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 aheadand accept all the changes or reject all the changesor 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 codefrom 60406to 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 selectList 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 worksheetthat was changed, the cell reference,the new value, and the old value.Now, once you've gone through and accepted or rejectedall your changes, you'll want to removethe 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 Changesby 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 historyof 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 workbookfrom 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 finda 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 toolso you'll be familiar with the options,just in case you see the question on the MOS Expert Exam.

Resume Transcript Auto-Scroll

Author

Released

4/18/2017

Demonstrate your advanced knowledge of Excel by becoming a Microsoft Office Specialist (MOS) Expert. This course, created by Microsoft Certified Trainer Jennifer McBee, helps you prepare for the Excel 2013 Expert Part One exam, the first part of a two-part exam. This course focuses on managing and sharing workbooks, applying custom formats and layouts, creating advanced formulas, and creating advanced charts and tables.

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.

Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.