WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to Remove Duplicates in Excel for Students

June 13, 2024

As a writer, I understand that my role isn't just about forming sentences and synthesizing information—it often involves gathering and presenting data in a clear and understandable manner for my audience. I've encountered firsthand how problematic duplicates can be in Excel sheets, causing confusion and hindering data analysis. So, if you're a student grappling with Excel and eager to remove those pesky duplicates to streamline your work. I'm here to guide you through the process of how to remove duplicates in excel for students.

How to Remove Duplicates in Excel for Students

Removing Duplicates Resolves Data Difficulties for Students

For students working with data in Excel, removing duplicates is a crucial step in ensuring accurate analysis. Here's why:

  • Data cleaning or organization:

Duplicates in data can distort the outcomes of analysis. For instance, in experiments, if there are repeated entries for a single reaction, it would inaccurately affect the calculated average. Eliminating duplicates ensures a more precise depiction of the data. Furthermore, Statistical tests rely on independent data points. Duplicates violate this independence and can lead to misleading results. By removing duplicates, students can perform statistical tests with confidence.

  • Preparation for accurate reports:

Duplicates can make data sets messy and difficult to understand. By removing them, students can simplify the data, making it easier to analyze and interpret. This minimizes errors, as duplicates can lead to inaccuracies in calculations and data analysis. Moreover, it aids in creating clearer and more professional reports.

Remove Duplicates for more accurate reports

  • Increase data sensitivity:

Removing duplicates saves time and effort. Students can focus on analyzing the actual data instead of dealing with redundant entries.  In academic settings, data may be sensitive. Removing duplicates helps ensure that each piece of data is counted only once, improving the integrity and accuracy of analysis.

How to Remove Duplicates in Excel

Students often search the internet to learn how to remove duplicates in Excel but often end up more confused than before. Highlighting, removing, or working with duplicate entries each have their own specific methods that need to be addressed correctly. To help students find a more organized solution for removing duplicates in Excel, I've put together this tutorial. To make things easier, I'll be using WPS Office, a cost-effective alternative that offers all the functionalities of Excel with zero learning curve. So, let's switch tabs and dive into how to delete duplicates in Excel.

Highlight Duplicates

First of all, we will learn how to highlight duplicate entries in an Excel spreadsheet, a key step to identify whether our data contains any repetitive entries.

So, I'll be using a sample spreadsheet that contains a few experiment results, but due to some data entry issues, a few entries were repeated. To sort things out, I'll be using the "Highlight Duplicate Entries" feature in WPS Spreadsheet to get a quick glimpse of my errors.

Step 1: First, let's load our data into WPS Spreadsheet.

Step 2: Next, we will select the cells in which we wish to highlight the duplicate entries using our mouse.

Select cells in WPS Spreadsheet

It's important to highlight the unique identifier column because it is common for columns such as temperature or other variables to be repetitive.

Step 3: Now, head over to the Data tab in the toolbar and click on "Highlight Duplicates".

Data tab in WPS Spreadsheet

Step 4: In the Highlight Duplicates drop-down menu, click on the "Set" option.

Highlight Duplicates in WPS Spreadsheet

Step 5: This will open the Highlight Duplicates dialog. Here, the range will be the cells selected in the second step. If you wish to change the range, you can do so here and then click "OK".

Set Duplicates Range in WPS Spreadsheet

Step 6: All the duplicate cells will now be highlighted with an orange color.

Duplicated cells in WPS Spreadsheet

This method allows students to easily identify any duplicate entries in their data using WPS Spreadsheet.

Remove Duplicates

Once you have highlighted data entry errors, which are quite common as you're focused on your work, you can easily fix the data entries by simply removing duplicates in Excel.

Step 1: Open the experiment results data spreadsheet where you have previously highlighted duplicate entries.

Step 2: Select the range of cells that you want to check for duplicates.

Step 3: Go to the "Data" tab in the toolbar and expand the options by clicking on the "Highlight Duplicates" button.

WPS Spreadsheet Data tab

Step 4: Select the "Remove Duplicates" option from the drop-down menu.

Remove Duplicates option in WPS Spreadsheet

Step 5: In the Remove Duplicates window, you can choose which columns to consider by selecting or deselecting them. The window also shows the number of duplicates found in the chosen columns.

Step 6: Once your selections are made, click "Remove Duplicates".

Remove Duplicates from data

Step 7: WPS Spreadsheet will notify the number of duplicate entries removed from the data as well as the unique entries remaining, press “OK”.

Notification from WPS Spreadsheet

WPS Spreadsheet streamlines duplicate handling effortlessly, making it my preferred office suite; its efficiency is particularly advantageous for students.

Use formula

Here's how students can utilize the UNIQUE function to eliminate duplicates in an Excel spreadsheet.

Step 1: Open your WPS Spreadsheet that contains the dataset with duplicate student entries.

Step 2: Choose an empty cell where you wish to apply the UNIQUE function.

Step 3: Initiate the function by typing an equals sign (=), followed by UNIQUE.

Step 4: Once the UNIQUE function appears in the suggestions, click on it and hit the Tab key to select it.

Enter UNIQUE Function in an empty cell

Step 5: The UNIQUE function requires only one parameter, the Array; highlight the entire range of data you wish to deduplicate and press Enter.

Select range of data to remove duplicates

Step 6: A fresh table will be generated without any duplicate entries, ready for you to conduct additional data analysis or manipulation.

Remove Duplicates from data using UNIQUE Formula

WPS Spreadsheet is a valuable tool for improving data quality, not just by efficiently eliminating duplicates from Excel spreadsheets but also by offering additional tools and functionalities for deeper analysis. Its seamless sharing capabilities enable effortless collaboration with others. For students, WPS Spreadsheet stands out as an exceptional free alternative, providing not only essential Excel functions but also robust word processing, presentation creation, and advanced PDF tools. It serves as a comprehensive aid across all academic fronts.

Best AI Assistant for Students - WPS AI

WPS Office has consistently proven itself as a true companion for students, offering a wide array of features that are not only free but also highly compatible and packed with powerful PDF tools. However, with the introduction of WPS AI, the list of reasons why every student should have it is growing even longer. Here's why:

  • Content Generation:

WPS AI supports content generation, making it a valuable tool for students who need to write essays, reports, or other academic assignments. With AI-powered assistance, students can overcome writer's block and produce high-quality content efficiently.

  • Text Summarization:

Summarizing texts can be a time-consuming task for students, but with WPS AI, it becomes a breeze. The AI technology can quickly extract key information from lengthy texts, helping students save time and focus on understanding the main points.


  • Data Analysis:

Data analysis is an essential skill for students across various disciplines. WPS AI simplifies the process by providing tools and functionalities for analyzing data, making it easier for students to draw insights and make informed decisions.

  • Interactive PDF Features:

One of the standout features of WPS AI is its ability to interact with PDF documents. Students can engage in discussions, ask questions, and collaborate with peers directly within PDF files, enhancing both learning and collaboration.

  • Educator Support:

WPS AI isn't just beneficial for students—it also offers valuable support for educators. Teachers can create interactive lessons, generate quizzes, and provide personalized feedback to students, making the learning experience more engaging and effective.

WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
Algirdas Jasaitis

Use WPS Office to Meet Your Academic Needs

WPS Office has consistently proven to be one of the best and most competitive office suites available in the market today. The reasons are straightforward yet compelling, especially when you consider that it is not only free but also offers an easy-to-use interface while providing all the latest advanced tools. These features collectively make WPS Office an unbeatable choice, particularly for students.

WPS Office

Jumpstart Project with Academic Templates

WPS Office understands the unique needs of students and has curated a diverse collection of academic templates to help you jumpstart your projects. Whether you're writing an essay, preparing a research report, or designing a presentation, WPS Office has a template to suit your needs. These templates are professionally designed, ensuring that your documents not only look polished but also adhere to academic standards. By using these templates, you can save valuable time on formatting and focus more on content creation and research.

WPS Templates

File Management and Sharing, Anywhere

WPS Office excels in providing robust file management and sharing capabilities, ensuring that your documents are always accessible and easy to share, no matter where you are. With cloud integration, WPS Office allows you to store your files securely online, so you can access them from any device at any time. This feature is particularly useful for students and professionals who need to work on-the-go or collaborate with peers.

Easy File Management and Sharing with WPS Office

FAQs about how to Remove Duplicates in Excel for Students

1. What is the button to remove duplicates in Excel?

To remove duplicates in Excel, you click on the "Remove Duplicates" button. This button is located on the Data tab within the Data Tools group.

2. Is there an Excel formula to find duplicates?

While Excel lacks an exact function for duplicates, students can employ the COUNTIF function to distinguish between unique and duplicate entries in their data. If you prefer values to read as "Duplicate" or "Unique," you can enter the following formula in the adjacent cell to your initial entry:

=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique").

3. How to remove duplicate values in Excel without shifting cells?

We can use WPS Spreadsheet's conditional formatting to remove duplicate values in Excel without shifting cells. Here are the steps:

Step 1: Select all the original data by highlighting the cell range.

Step 2: Navigate to the Home tab and locate Conditional Formatting.

WPS Spreadsheet Conditional Formatting

Step 3: From the dropdown menu, select “Highlight Cell Rules” and then choose “Duplicate Values...” from the flyout menu.

Highlight Duplicate Values Conditional formatting in WPS Spreadsheet

Step 4: The Duplicate Values dialog box will open. Select the color you want to use to highlight the duplicate values.

Select formatting color for duplicate rows

Step 5: Once you have selected the highlight color, click on “OK”.

Apply Conditional formatting in WPS Spreadsheet

Step 6: Excel will highlight the duplicate data based on the formatting style you selected.

Step 7: To remove duplicate rows, select all the duplicates by first selecting one duplicate row, then hold CTRL and select the other duplicate rows.

Step 8: Right-click the selection and choose "Clear Contents" from the context menu.

Clear Contents option in WPS Spreadsheet’s Context menu

Step 9: Your table should now be free of duplicate data, with all cells in their correct positions.

Remove duplicate values without shifting cells using WPS Spreadsheet

Managing Duplicate Data in Excel with WPS Office

Duplicate data in Excel poses its fair share of frustrations, and for students, dealing with unwanted frustration is a combo no one ever asked for. Thankfully, WPS Office offers an exceptional solution for how to remove duplicates in Excel for students. It provides a comprehensive, user-friendly, and cost-effective suite of tools that address all your academic and professional needs. By integrating smart working techniques with powerful features, WPS Office helps you achieve your goals efficiently and effectively.

Download WPS Office, so you can discover the powerful tools and features it has in store for you. Simplify your academic and professional tasks, enhance your productivity, and manage your data efficiently with this outstanding office suite.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.