While working in Excel, users may sometimes have to deal with duplicate values. Sometimes, these values might not be necessary for working purposes. So generally, users tend to remove these values from worksheets. But if they need those values again, then it would be tedious to restore them one by one in a large data set. So, in this article, we will show you how to undo remove duplicates in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
3 Handy Ways to Undo Remove Duplicates in Excel
In this article, you will see three different ways to undo remove duplicates in Excel. First of all, we will use a keyboard shortcut. Then, we will utilize the Quick Access Toolbar of Excel in our second approach, and lastly, we will apply a VBA code to complete our task. For a better understanding, we will first remove duplicates from our data set and then undo the removal in all three methods. To demonstrate our procedures, we will use the following data set.
1. Using Keyboard Shortcut to Undo Remove Duplicates in Excel
The easiest way to undo remove duplicates in Excel is to use a keyboard shortcut. There are many shortcut operations in Excel, which we can simply perform by pressing some keyboard buttons. To learn more about this procedure, follow the following steps.
- Firstly, go through the data set for this procedure.
- Then, check if any duplicates are present in the data set.
- For example, we can see two duplicate values in our data set which are $2500.00 and $1950.00.
- Secondly, select the data range from cell C4:C12 for the removal of duplicates.
- Thirdly, go to the Data tab of the ribbon.
- Then, from the Data Tools group, select the Remove Duplicates command.
- Fourthly, you will see the Remove Duplicates dialogue box.
- Then, under the Columns heading, only check the Monthly Salary box.
- After that, press OK.
- Fifthly, you will see there are no duplicates in the data set after performing the previous steps.
- In the sixth step, we will undo the removal of these duplicates.
- For that, press Ctrl+Z on your keyboard right after the removal.
- Finally, you will see the duplicate values have been restored in their places.
2. Utilizing Quick Access Toolbar to Undo Remove Duplicates
Another amazing way to undo remove duplicates in Excel is to utilize the Quick Access Toolbar. In Microsoft Excel, you will see a command line that is either over or underneath the primary ribbon tabs. A user can get access to many shortcuts or features of Excel by using this toolbar. We are providing the detailed steps for this procedure below.
- First of all, select the data set with duplicate values in it.
- Secondly, remove those duplicate values by following the previous method.
- Thirdly, we will undo this removal process.
- For that, go to the Quick Access Toolbar, which is above the primary ribbon of the worksheet.
- Fourthly, select the Undo arrow icon from the toolbar.
- From the dropdown, select the Remove Duplicates command.
- Finally, by performing this action, you will undo the process of removing duplicates from the data set.
3. Applying VBA to Undo Remove Duplicates in Excel
In our last method to undo remove duplicates in Excel, we will apply a VBA code. By applying a VBA code, we will be able to complete our task without any keyboard shortcuts or toolbar. We just have to input the proper command into the code for the undo of removal. For a better understanding, go through the following steps.
- In the beginning, take the data set that has duplicate values.
- In the second step, like in the first method, remove all the duplicates from the data set.
- In the third step, we will show you how to undo the removal by applying a VBA code.
- For that, go to the Developer tab of the ribbon.
- Then, choose the Visual Basic command from the Code group.
- In the fourth step, you will see the VBA window.
- Then, from the Insert tab choose Module.
- In the fifth step, copy the following code into the module.
'Set the function name Sub Undo_Remove_Duplicates_in_Excel() 'Giving command to undo the removal Application.Undo End Sub
- In the sixth step, save the code and press the play button or F5 to run it.
- Finally, after running the code, all the removed duplicates will be restored in the data set.
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to undo remove duplicates in Excel by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.