by In this article, we will guide you through the process of using the Undo, Redo, and Repeat features in Excel, empowering you to take control of your data a like a pro.
Mistakes happen to the best of us. Whether you're a seasoned Excel user who has been navigating spreadsheets for years or just starting to explore its capabilities, knowing how to correct mistakes and perform repetitive tasks without unnecessary hassle is essential. Excel's Undo, Redo, and Repeat functions are like a safety net, allowing you to backtrack and make adjustments without fear of losing vital information or wasting time. So, let's dive in and discover the secrets of undoing, redoing, and repeating the last action in Excel.
Undo in Excel
The Undo feature in Excel lets you revert the last action you performed, whether it was data entry, formatting, or any other modification. It's an invaluable feature, especially when you make unintentional changes or need to backtrack. Here are some cases that can be quickly resolved with the help of Undo:
- Get an accidentally deleted cell value back
- Revert formatting changes
- Recover deleted rows or columns
- Restore a mistakenly edited formula
- Reverse wrong sorting or filtering
How to undo in Excel
There are 2 quick ways to revert an action in Excel – using the Quick Access Toolbar button and keyboard shortcut.
To undo an action with a button, follow these steps:
- At the top left corner of the spreadsheet, locate the Undo button, which is represented by a curved arrow pointing to the left.
- Hover over the button to see a description of the action that will be undone.
- Click on the Undo button to revert the action.
That's it! If needed, you can repeatedly use the Undo button to step back through multiple actions one at a time, helping you correct errors and maintain data accuracy.
Normally, the Undo button appears in the QAT by default, providing quick access to the undo functionality. In case it is not visible in your Excel version, you can add it by customizing the Quick Access Toolbar.
Excel shortcut for undo
Another swift way to undo the last action in Excel is to press Ctrl + Z on your keyboard. This is the universal Undo shortcut across many applications, not just Microsoft Excel.
Mac users can use the Command + Z shortcut to undo in Excel.
To revert a few actions, you can press the Undo shortcut as many times as needed.
Excel undo not working
While the Undo feature in Excel is a helpful tool for reverting recent actions, there are certain situations where it may encounter difficulties or not function as expected. Here are some common scenarios where the Undo function might not work as you would anticipate:
- Some actions cannot be undone. Excel has limitations on what actions can be undone. For instance, saving a file, clicking menu items, using commands on the File tab, or deleting sheets are not reversible using the Undo feature. Once you perform any of these actions, you won't be able to go back using the standard undo command.
- Undo across multiple documents. Excel maintains a single Undo stack for all open workbooks. This means that if you make edits in one workbook (e.g. Workbook 1), then switch to another workbook (e.g. Workbook 2) and attempt to undo, Excel will switch back to Workbook 1 and revert the last change there. This behavior can be confusing, especially when working with multiple files simultaneously.
- VBA macros disable the Undo functionality. If you make changes to your Excel worksheet using VBA macros, be aware that the entire Undo stack will get lost. As a result, the Undo command will change to Can't Undo (greyed out). VBA macros can cause the undo functionality to behave unexpectedly, so exercise caution when using them if you rely heavily on the Excel Undo feature.
Redo in Excel
Excel's Redo function is the counterpart to Undo that allows you to reapply the action that was previously undone. It is useful when you've inadvertently reverted an action or changed your mind about the undo decision.
How to redo in Excel
Just like with the Undo function, there are two ways to redo an action in Excel: via the Quick Access Toolbar and using a keyboard shortcut.
To redo an action, the steps are:
- In the Quick Access Toolbar, look for the Redo button represented by a curved arrow pointing to the right.
- Hover over the button to view a description of the action that will be redone.
- Click on the Redo button to reapply the last undone action.
Done!
Excel Redo shortcut
To redo an action, press the Ctrl + Y shortcut on your keyboard.
In Excel for Mac, the shortcut for redo is Command + Y.
On most keyboards, you can also use the F4 key for redo. If it doesn't work for you, try pressing F-Lock or Fn, and then the F4 key.
Similar to Undo, this Redo shortcut works in various applications, not just Excel.
If needed, you can use the Redo function more than once to reapply multiple actions that were undone in succession.
Excel Redo not working
There are certain scenarios where the Redo functionality may encounter limitations or become unavailable.
Dependency on Undo. The Redo function is dependent on the Undo feature. In other words, you can only redo an action if it was affected by the Undo command. For instance, let's say you typed something in cell A1 and then removed the value using the Undo function. In this case, the Redo function can quickly bring the value back by reapplying the action. However, if you manually deleted the value (without using Undo), the Redo option will be unavailable for that specific action.
Redo unavailability. Since the Undo function is unavailable for some kinds of actions, Redo is not working for those actions too. For instance, when you rename or delete a sheet, unfortunately, this action cannot be undone. As a result, the Redo function also becomes unavailable for sheet deletions and renaming.
Just remember to be mindful of actions that cannot be undone / redone and take extra care to avoid unintended consequences.
Repeat last action in Excel
The Repeat action function in Excel allows you to apply the most recent action to a different cell or cells without the need to perform it manually each time.
For example, if you've formatted a cell with specific attributes such as font style or fill color, and you wish to apply the same formatting to other cells, you can do it instantly with the Repeat function.
This feature can be particularly useful when you want to apply the same action multiple times, for example to insert multiple columns or add multiple rows.
How to repeat an action in Excel
The Repeat command isn't available in the Quick Access Toolbar by default. To access it, you need to add the command to your toolbar first. Here's a step-by-step guide on how to do it: How to add a command to QAT.
With the Repeat button added to the Quick Access Toolbar, follow these simple steps to repeat an action in Excel:
- Perform the desired action on one cell.
- In the Quick Access Toolbar, hover over the Repeat button, which looks like an arrow forming a circle. This will display the description of the last action that can be repeated.
- Select another cell or a range of cells where you want to apply the same action.
- Click on the Repeat button to apply the last action to the selected cell(s).
To repeat an action multiple times, perform steps 3 and 4 as many times as you want.
Excel shortcut to repeat last action
To make your workflow even more efficient, you can use a shortcut key to repeat the last action in Excel:
- Ctrl + Y or F4 for Windows
- Command + Y for Mac
It's worth noting that the same keyboard shortcut serves both as Redo and Repeat in Excel, but only one command is available at any given moment.
Redo vs. Repeat in Excel
The Repeat and Redo functions are never available in Excel at the same time. In essence:
- Redo is only available after some action is undone.
- Repeat is available after a change has been made to the worksheet.
For example, if you copied a formula from cell A1 to A2, then the Repeat button on the toolbar will be active, and the Redo button will be grayed out. This means that you can repeat pasting the formula into another cell.
Conversely, if you undo pasting the formula in A2, this will activate the Redo feature but deactivate Repeat. So, you will be able to redo the formula copying in cell A2, but you won't be able to copy it to another cell using the Repeat option.
How to undo multiple edits in Excel
When you find yourself needing to undo multiple actions in Excel, there's no need to click the Undo button or hit the shortcut repeatedly. Excel provides a more efficient way to undo a series of actions in one go. Here's how you can do it:
- Click the drop-down arrow next to the Undo button in the Quick Access Toolbar. This will display a list of all subsequent actions you've performed across all open workbooks.
- Move your cursor down the list to highlight all the previous actions you want to revert. Excel will display the number of actions that will be undone at the bottom of the drop-down list.
- Click on the last highlighted action to initiate the undo process for all the selected actions.
That's it! With just a few simple steps, you can undo multiple edits and save valuable time. This feature is incredibly useful when you need to backtrack through several recent changes without the need for repetitive clicking.
Tip. Just as you can efficiently undo multiple edits, you can also redo multiple actions all at once using the same method. Unfortunately, there is no built-in functionality to repeat a set of actions in Excel.
Undo limit in Excel
While Excel's Undo and Redo features are invaluable time savers, they do thieve their limits. Excel allows you to undo or redo only the last 100 edits, and the Quick Access Toolbar displays up to 20 of your most recent actions. If you find these limitations restrictive, you can adjust the Undo limit in the Windows Registry.
Here's how you can change the number of undo levels in Excel:
- Close Excel and any other Office programs that are running.
- In the Search box, type regedit and press the Enter key. Depending on your Windows version, you may be asked to confirm opening Registry Editor.
- In Registry Editor, navigate to the following registry subkey: HKEY_CURRENT_USER\Software\Microsoft\Office{version}\Excel\Options
- Right-click Options, point to New, and then click DWORD Value. The default "New Value #1" will be created.
- Select the newly created New Value #1, change its name to UndoHistory, and press Enter.
- Right-click UndoHistory, and then click Modify.
- In the Edit DWORD Value dialog box, select Decimal, type the desired number of undo actions in the Value box, and click OK.
- Exit Registry Editor.
When you start Excel, it will store an undo history for the number of actions you specified.
Potential memory issues
When changing the number of undo levels in Excel, be mindful of memory considerations. The more levels you have, the more RAM (Random Access Memory) is required to store the undo history, which can significantly affect Excel's performance, especially when dealing with large datasets or resource-intensive operations. Imagine performing 10 operations that affect 100,000 cells each - Excel would need to store those 1M values until you perform another 100 operations! So, strike a balance between the undo limits and system resources to ensure smooth Excel performance.
If you find that 20 or 50 levels of Undo are sufficient for your needs, you can set that limit in the Windows Registry, as explained earlier. Another approach to mitigate memory concerns is to disable Undo for Data Model operations by following these steps:
- In your Excel, click on File and choose Options.
- In the left pane, select Data.
- Under Data options, select the Disable Undo for Data Model operations option and set the threshold data model size, which defaults to 8MB.
Alternatives to Undo functionality in Excel
If you find yourself unable to undo certain changes or encounter issues with the Undo feature, there are alternative methods to safeguard your work:
Make a copy of your workbook. Before attempting to make critical changes to your worksheet, it's a prudent practice to create a copy of your file. By doing so, you can preserve the original version and have a backup in case any irreversible actions are taken.
Restore a previous version. Excel offers a powerful Restore feature that automatically creates snapshots of your workbook at specific points in time as you progress. By using this feature, you can revert to any previous version when required. For more information, see How to recover overwritten Excel files.
That's how to efficiently use the Undo, Redo, and Repeat functions in Excel. Keeping these tips in mind will help you maintain data accuracy and manage your spreadsheets with confidence.
