Tool: Excel SHOW CHANGES: Recover Old Data


About this lesson

Summary of the Tutorial

Excel’s Show Changes feature allows users to “time travel” by tracking and displaying recent changes made to a spreadsheet, making it easy to revert to previous values. This tool is especially useful in collaborative settings where multiple users are editing the same file, as it records who made each change and when. To use this feature, you must have a paid Office 365 subscription and save your file on a Microsoft cloud service like OneDrive or SharePoint.

The tutorial demonstrates how to access and utilize Show Changes, highlighting its ability to capture edits at the cell level, including text and formulas, but not formatting changes. The feature logs changes continuously, which can be viewed by clicking the “Show Changes” option under the Review tab. Users can filter changes by sheet or range, making it easier to locate specific edits within large spreadsheets.

In addition to Show Changes, the tutorial briefly touches on the Version History feature, which allows users to view and restore previous versions of the entire spreadsheet. This feature is essential for recovering lost data from more significant changes. Together, Show Changes and Version History provide powerful tools for managing and safeguarding your Excel data, ensuring you never lose track of important edits.

 

Questions Answered:

01:52 The Rules of Excel Show Changes
03:13 How Excel Loses Track Changes
03:22 The Deep Dive on Show Changes
03:29 Why Does Show Changes Not Work?
04:23 How to Access Show Changes
04:35 What is “See New Changes?”
04:47 Explain the Excel Show Change Log

Topics

00:17 Excel Show Changes In Action
01:52 The Rules of Show Changes
03:13 How Excel Loses Track Changes
03:22 The Deep Dive on Track Changes
03:29 Why Does Show Changes Not Work?
04:23 How to Access Show Changes
04:35 What is “See New Changes?”
04:47 Making Sense of Change Log
05:23 How to Restore Previous Change
06:31 What Changes does Excel Not Track?
06:56 Does Excel Track Formula Changes?
07:28 Will the Changes be Tracked if I Exit Excel?
08:06 Tracking Many Changes in a Large Spreadsheet
08:20 Elements of the Track Change Log
08:54 Excel Show Change Filter Tool
10:19 Bonus: Version History

Details

Subject Microsoft Excel

Software Compatibility Office 365 Mac and Windows

Level

Course Completed

PDF Files There are not any files associated with this lesson.

TRAINING SERIES VIEW ALL

Subscribe

Remember to subscribe to our YouTube channel by clicking the AUTO SUBSCRIBE! button below

Subscriptions help us create more free video training for YOU!

Transcript

Excel Show Changes Transcript

Time travel in Excel is REAL. Not some Back to the Future sci-fi movie.

You can go back in time to fix lost mistakes in Excel with the recently added command tool of SHOW CHANGES.

Imagine you are in charge of the master construction budget worksheet where you collect data from different sources.

Your phone rings.

“What’s up Marty? You’ve got changes to the May Material costs? $168,833?

Fixed.”

Autosave is turned on, so I can now close down the budget spreadsheet.

Two hours later, Marty calls again.

“Yep. . . .THAT NUMBER WAS WRONG? Okay what was the original number?

Marty, you really don’t remember the number?”

RATS!

[Breath Deep]

Excel Time Travel.

On my desktop Office 365 version of Excel, I go to the REVIEW ribbon menu tab and find SHOW CHANGES.

The magic is the time travel recorder that captured all my recent changes to this spreadsheet.

Once clicked, the timeline of past changes pops up on the right side of the screen and my most recent change shows that $168,833 replaced the original number of $145,000.

Type it back in and my Marty problem is solved!

Hi, this is Les from Power Up Training, and I am about to show you the ins and outs of Excel’s SHOW CHANGES feature.

At a high level, Excel now tracks almost all of your changes automatically. Later, I will explain what is not tracked.

Excel will let you travel back in time to see what transpired at the individual cell level.

Change by change.

And while I am demonstrating this on a Windows computer, it works EXACTLY the same on the MacOS desktop.

Here are the rules:

#1 You must be working with the paid version of Office 365, running the latest version of Excel. The desktop program has been recently updated; but the feature has been available for a while on the web version of Excel.

#2 Your file MUST be saved on a Microsoft cloud storage system. Most likely OneDrive but the latest iteration of Microsoft Teams or cloud based SharePoint should also work.

That is it!

You don’t have to enable it . . . it will be capturing changes behinds the scenes.

You can be working alone on a file or it will also work if you have shared the file with your team. Which is great as it will track WHO made what changes and when (Hey MARTY, I’M WATCHING YOU!)

The only warning is if someone elects to save the file locally or with an old version of Excel, the changes could be lost.

Okay let’s go deeper.

Here is a brand-new file.

Zero Changes.

I will add a title.

The cool tool is found under the REVIEW ribbon menu, and here . . . oh no wait.. . . it is grayed out. WHY?

Rule #2, it must be saved on Microsoft cloud service location and the file has yet to be saved.

A visual hint is that AutoSave is OFF.

So I will click the SAVE disk icon. Give it a name. Save.

AutoSave was automatically enabled and at the same time, the action comand icon of SHOW CHANGES is now active.

Click . . . . but no saved history of adding the title. Why?

Because it was not saved on the cloud when I made the entry. Only the changes will show after saving on OneDrive.

Let me add in some spreadsheet text labels for us to better see the SHOW CHANGES tool in action.

Okay.

At first it would appear that Excel is still not showing the changes. but look closely, I need to click SEE NEW CHANGES.

And PRESTO, six changes are displayed.

With the last change of TOTAL listed at the top.

Now I will change an existing cell by replacing the word MANAGEMENT with PLANNING.

When I click SEE NEW CHANGES, we see the entry.

But look closer. The word PLANNING is at the top, indicating that it was the new entry, and the word “Management” is below, indicating that this was the content that was replaced.

Looking at the entry below, such as TOTAL, we see the lower part is blank, and that is because I typed in TOTAL on a blank cell; there was nothing there before.

If I want to revert back to MANAGEMENT, it is NOT a double click, but if I RIGHT CLICK on the actual word MANAGEMENT, I can copy it and then paste back into the original cell.

And a refresh of the changes, we see that listed too; now, with the word MANAGEMENT on top of the change log.

I will add in some more entries, including the Excel trick of dragging out a date cell to let it automatically fill in the months with a single right mouse button action, no individual  typing needed.

And then I will type in the word TOTAL.

With a refresh, examine the second entry of the auto-date expansion. It was a single action with multiple results. So it was captured as a single change entry, but look for the SEE CHANGES?

Just as I expanded the list, I can also make the change log easier to navigate, by clicking HIDE CHANGE to collapse the list.

Let’s make more changes. Mostly of the formatting kind.

Bold font. Green cell background. Center alignment.

And . . . NO CHANGE REFERSH listed!

WHY?

CHANGE TRACKING focuses on cell text changes. Not formatting. In fact as of now, it will not track changes to the objects as shown on this list.

But what about formulas? Are they not text. Let’s see.

I will add in a number for the architect in January. And then, highlighting the main table, I will click the AutoSum command to have the summations typed in for me.

See the =SUM ranges put in as totals?

With a click of SEE NEW CHANGES, we see the collection of additions with each cell listed.

So YES, TRACK CHANGES does tracks formulas.

Do changes in the change log stay active after closing the spreadsheet?

Yes, unlike the UNDO feature that is only active during an Excel session, SHOW CHANGES stays with the file, as long as it stays on the Microsoft cloud storage.

Close, the file and open it back up.

All the SHOW CHANGE listings remain.

As a contrast, see the limited UNDO list.

Show Changes is vastly superior. But just for text changes to the spreadsheet. So don’t give up on UNDO.

Our class example is a simple spreadsheet. Let’s return to one with many more changes.

This spreadsheet has multiple tabs with changes scattered on different pages.

The change log shows who made the changes, me, and the time the change happened. If it was farther back, Excel would show calendar dates, not relative time stamps.

If we look closely, we see each change also references the location by way of the SHEET NAME, like “Concepts,” and the Cell, like C11

But in huge spreadsheets with many changes, it can be a mind fog of where to find the changes.

And that is where the FILTER tool comes into play.

Click the FUNNEL, and you can focus on ranges or sheets.

For sheets, it will fly out a list of sheets that have changes.

My Numbers sheet has only two changes. Which we saw at the start of the video.

The sheet called Formulas has three. And while I click on the sheet name to bring it up, I could also double click on the change entry and Excel will shoot me over to that location.

Now look at the sheet called LOTS OF NUMBERS that has, of course, lots of numbers.

And lots of changes.

While we could wade through them one by one, if we knew that a mistake was made in column P.

Instead, I will use the FILTER tool and select RANGE, then clicking in the entry box, I go over to the spreadsheet and highlight the cells of interest. In my case, the whole column P.

Click the GREEN go arrow and presto, the changes in column P are listed. Here it is just one changed cell in column P. A click and Excel takes me straight to the change with the before and after entries listed.

That is a comprehensive dive into TRACK CHANGES but there is another tool for more serious time travel. It is not UNDO, but instead the feature called VERSION HISTORY.

This is actually capturing the state of the spreadhseet at different points in time.

Look as I click the file name at the top and there is VERSION HISTORY. Excel reveals almost a dozen snapshots over time. Each one as if I had done saves under different names over time.

To learn more about this feature in-depth, click my tutorial on the Excel Multiverse video.

But also do click LIKE and SUBSCRIBE to Power UP Training.

Until next time, go power up.