When you use Excel’s group feature, it includes all data in the selected cells, even the repeated values. If you want to group only the unique rows, there’s no built-in feature to do so. Therefore, first you need to remove or filter out the duplicate rows and then group the unique values only.
As this deletes the entire row (not just the duplicate cells), you can extract the unique values only using a formula to keep the rows intact. To remove repeated rows, we can use Excel’s built-in Remove Duplicates feature.
Steps to group without repeating using Remove Duplicates:
➤ Select the range with repeated values (A2:D10), go to the Data tab >> Data Tools group >> Remove Duplicates icon. Choose all the columns to remove all duplicate rows and press Ok.
➤ As Excel removes the repeated rows, select the cells to group and go to the Data tab >> Outline group >> Group drop-down >> Group. Select whether you want to group Rows or Columns and press Ok to group your dataset without repeated rows.
➤ To extract the unique data only, insert the following formula in a blank cell:
=UNIQUE(A2:D10)
Replace the range as needed and Excel will spill the unique values. You can paste the data as Values over your original range and group the cells using the Group command.
This article covers how to group Excel data without repeating using the Remove Duplicates feature, Filter command, and functions like UNIQUE, IF, COUNTIF, etc.
Using the Remove Duplicates Feature to Group Without Repeating Values
Our sample dataset has columns for product Codes, Salespersons, Products, and Prices. Rows 3, 5, and 8 contain the repeated values in all columns. We’ll remove these 3 duplicate rows and then group the remaining ones.
An easy way to remove all the rows containing a duplicate value in any cell is to use the Remove Duplicates feature. Be careful while using it as it deletes the entire row even if the duplicate value is in a single cell. Here are the details:
➤ First, copy your original dataset to a different range if you don’t want to lose data.
➤ Now, select your entire range from where you want to delete the duplicates (A2:D10). Go to the Data tab and navigate to the Data Tools group. Click on the Remove Duplicates icon as shown in the picture below:
➤ In the Remove Duplicates dialog box, click on Select All if all the columns contain duplicate values. Otherwise, only check the columns that contain the duplicates. Press Ok once done.
➤ As Excel removes the rows with repeated values (except for the first occurrence), select the cells you want to group (A2 to D5) and press the keyboard shortcut Shift + Alt + → .
➤ Or, go to the Data tab. In the Outline group, click on the Group drop-down, and select Group.
➤ From the Group window, choose whether you want to group the Rows or Columns. Press Ok.
➤ Excel will now group the cells you selected.
Group by Extracting Unique Values
Instead of deleting the repeating data, you can extract only the unique values and group them as needed. Here’s how to use the UNIQUE function to do so:
➤ In a cell where you want the unique values, enter this formula:
=UNIQUE(A2:D10)
➤ Here, A2:D10 is the range from which we want to extract the unique values. Change is according to your dataset.
➤ Press Enter and Excel will spill the data without duplicates.
➤ Now, you can either copy the headers and formatting and apply them on the spilled data or replace it over your original dataset.
➤ As we want to replace our original dataset, we copied the spilled data by pressing Ctrl + C and selected our original dataset.
➤ After that, we’ll right-click on the selection and select Paste Special from the menu.
➤ In the Paste Special dialog box, choose Values and press Ok.
➤ Once you paste the new data without duplicates, delete any remaining extra rows and the cell with the UNIQUE formula.
➤ To group cells, select the cells and press Shift + Alt + → . You can also go to the Data tab >> Outline group >> Group drop-down >> Group.
Replace Duplicates with Blanks and Group Cells
Instead of deleting the entire row, we can use an Excel formula with the IF and COUNTIF functions to replace duplicates with empty strings. It’s particularly helpful when you have a few cells containing repeated values (instead of entire duplicate rows) similar to the following dataset:
Here, we have two repeated values in each column. Let’s get to the steps:
➤ In an empty cell, enter the following formula and press Enter:
=IF(COUNTIF($A$2:D2, A2)>1, "", A2)
➤ Here, $A$2:D2 is the range of the first cells of each column from Column A to D. A2 is the first cell of the first column. Replace the cell references to match your source data.
➤ Drag the formula down and then across the columns to get the new values with the duplicates replaced with empty strings.
➤ You can group the new range after copying the format or paste it over your original range.
➤ For this, select the new range and press Ctrl + C to copy it. Highlight your original range and right-click on it. Choose Paste Special from the menu.
➤ In the Paste Special window, select Values and press Ok.
➤ As Excel replaces your data, all duplicate cells turn into blank cells, but no cells are shifted and no rows are deleted.
➤ Use the Group command or keyboard shortcut mentioned above to group the cells (including the blanks).
Avoid Repeated Values with Excel Formula and the Filter Feature
In this method, we’ll use a formula to assign numbers to the values of a column to identify the duplicates. After that, we can filter out the duplicates and delete them.
Similar to the previous method, it doesn’t delete the entire row, but removes the duplicate cell contents only. However, you can only work on a single column at a time. Below are the steps:
➤ In a helper column cell, enter the following formula:
=COUNTIFS($A$2:A2, A2)
➤ Here, A2 is the first column from which we want to remove the duplicates. Replace it as needed.
➤ Press Enter and drag the formula down. This will assign specific numbers for each cell in Column A based on their values. All the first occurrences get 1 while duplicates are assigned higher figures.
➤ Now, click on the header row and go to the Data tab >> Sort & Filter group >> Filter.
➤ Click on the Filter drop-down on the helper column header and uncheck the box containing 1 and press Ok.
➤ Excel will now only show the rows containing numbers greater than 1.
➤ Select the cells of Column A manually or click on its first cell and press Ctrl + Shift + ↓ . Press Delete to remove all the repeated values from Column A.
➤ To bring back all the rows, click on the Filter drop-down again, check the Select All box, and press Ok.
➤ Here’s the deleted value in Column A:
➤ Repeat the steps for each column. Replace the first cell of each column while using the formula. For example, use this formula when working on Column B (B2:B10):
=COUNTIFS($B$2:B2, B2)
After filtering the numbers, delete values from Column B only. Make sure you carefully do the same for the remaining columns. Otherwise, you might lose necessary data from other columns.
➤ Once done, you can delete the helper column. Here’s our final output after deleting all repeated data:
➤ To remove the filter, click on the header row, go to the Data tab >> Sort & Filter group >> Filter.
➤ Finally, use the abovementioned Group command or keyboard shortcut to group the cells.
Frequently Asked Questions
How to avoid repetition of data in Excel?
To prevent duplicate entries, select the range where you’ll enter data (A2:A100) and go to the Data tab >> Data Validation drop-down >> Data Validation. In the new window, choose Custom. Enter this formula in the formula box:
=COUNTIF($A$2:$A$100,A2)=1
Press Ok and repeat the steps for the other columns with the first cell (A2) changed in the formula. Now, Excel will prevent users from typing the same value twice in the range.
How to automatically group data in Excel?
To instantly group cells and create collapsible sections, arrange your data so that each group is separated by summary rows or columns (subtotals or section headers). Select the data range you want to group and go to the Data tab >> Outline group >> Group drop-down >> Auto Outline or Auto Group. Excel will now automatically detect related rows or columns and group them together.
How do I combine repetitive data in Excel?
If you have duplicate items and want to combine their values, select your data range, right-click >> From Table/Range. Press Ok to confirm your range. In Power Query, go to the Home tab and select Group By. Choose the column to group and the operation (Sum, Count, or Text.Combine). Once done, click Close & Load To and whether you want to send the grouped result back to the Existing Worksheet or New Worksheet.
Concluding Words
Choose a method carefully depending on the output you want. Always copy your original dataset in a different location to avoid data loss. Using the Remove Duplicates feature will permanently delete the rows containing duplicates. The UNIQUE function is best for grouping only the unique values without touching the existing dataset. If you want to remove cell content from the duplicate cells and group with the blanks, use the remaining methods with formulas.
