How to arrange same values in Excel

When you want a list of customers, email addresses, product IDs, or something similar where each one is distinct, Excel has a function to help. We’ll show you how to use this function to list unique values and text.

You can already use functions in Excel to total the number of distinct values. But here we’ll show you how to list those values instead using the UNIQUE function. Plus, we’ll throw in easy ways to sort the list and combine values.

Note: As of March 2022, the UNIQUE function is available in Excel for Microsoft 365, Excel for the web, Excel 2021 or later, or Excel for iPhone, iPad, or Android phones or tablets.

RELATED: How to Count Unique Values in Microsoft Excel

Use the UNIQUE Function in Excel

You can use the UNIQUE function for text or numbers, decide how to compare the cell range, and opt to show results that only appear once.

Advertisement

The syntax for the function is UNIQUE(array, column, only_once) where only the first argument is required. Include the column argument to compare columns instead of rows and the only_once argument to return values that occur only one time in the array.

Should you choose to include the optional arguments, you’ll use the TRUE indicator in the formula for each. If no indicator is included, the function assumes FALSE.

As an example, we’ll create a list of customers for an email blast. Rather than use the existing list in cells A2 through A10 because some customers ordered more than once, we’ll make a new list where each customer appears one time.

For another example, we’ll add the third argument, only_once, to find those customers who’ve only ordered once.

The Best Tech Newsletter Anywhere

Join 425,000 subscribers and get a daily digest of features, articles, news, and trivia.

By submitting your email, you agree to the Terms of Use and Privacy Policy.

Advertisement

Because the second argument assumes FALSE if nothing is included, we simply add a comma after the first argument and then another comma before the last argument. Alternatively, you can use this formula to obtain the same result:

You can use the UNIQUE function to list distinct values as well as text. In this formula, we can list unique dates:

RELATED: How to Sort by Date in Microsoft Excel

Sort the List Automatically

As mentioned, you can sort the list automatically at the same time you use the UNIQUE function to create it. To do this, you simply add the SORT function to the beginning of the formula.

Note: The SORT function is currently only available in the Excel versions listed earlier.

The syntax for this function is SORT(array, index, order, column) where only the first argument is required.

Advertisement

Using the first list of unique customers we created above and sort it immediately, you would use this formula:

As you can see, the UNIQUE formula is the required array argument for the SORT function.

By default, the SORT function lists items in ascending order. To sort the same list in descending order, you would use the following formula which adds the order argument.

Notice here we have a double comma again. This is because we don’t want the index argument, only the order argument. Use 1 for ascending order and -1 for descending order. If no value is used, the function assumes 1 by default.

Combine Unique Values

One more handy addition to the UNIQUE function allows you to combine values. For instance, maybe your list has values in two columns instead of just one as in the screenshot below.

Advertisement

By adding the ampersand (&) operator and a space, we can create a list of first and last names of unique customers with this formula:

To break down the formula, the first array, A2 through A10, contains the first names, the ampersands concatenate the first names to the last names in B2 through B10 with a space between them in quotes.

Advertisement

You can also include the SORT function here to put your list in ascending order with this formula:

Just like you might want to highlight duplicate values in Excel, you may want to find unique ones. Keep the UNIQUE function and these additional ways to use it in the mind the next time you need to create a list of distinct values or text in Excel.

RELATED: How to Use Conditional Formatting to Find Duplicate Data in Excel

You can get around duplicates by adding a hidden column C, where you add to your values some very little numbers, small enough not to be obtainable by other means and not to interfere with the original values, which let you differentiate between duplicates, e.g.

Then you just sort by the values in column C

Alternatively, if you don't want any hidden columns, you can put the values in like this:

but format the fields to display numeric values with 0 digit precision, so excel keeps your detailed values but displays them as:

Duplicates can cause lots of headaches in Excel.

They can throw out your tallies or counts, and can cause the wrong information to be reported.

If you have the same person listed in your database two or more times, perhaps with the same customer ID or same name, but with two different residential addresses because they moved house, then which one is the current record?

Figure 01: An example of a duplicate entry. Here we have 2 addresses for the same person. Which record should we keep?

The likelihood of this occurring increases the larger your database becomes and the more people use it.

It happens.

Excel provides 2 tools for handing this situation and cleaning up your database. First I’ll explain how to identify duplicate entries in your data then I’ll show you how to remove duplicates quickly.

It’s good to know how to deal with duplicates effectively.

1.  How to identify duplicates

The first tool is one of the options on the Conditional Formatting feature.

As its name suggests, there are two components - a condition and a format.

Conditional formatting allows you to check the value of a cell and if a certain condition is true then apply a particular cell format.

For example, 

If the value of a cell is greater than 50, format the cell with white text and a red fill.

When checking for duplicates the test is:

Does this cell have an identical twin?

1.  Highlight the cells that you wish to check. (In a table, this is normally one particular column).

2.  On the Home tab, click the Conditional Formatting icon.

3.  Choose Highlight Cells Rules.

4.  Choose Duplicate Values.

Figure 02: Conditional Formatting icon

5.  Select how you would like to format any duplicate cells found.

Figure 03: Choose how you would like to format any duplicate cells found

Once this is done, any cell that has another cell with an identical value is highlighted.

Figure 04: Duplicate cells are highlighted

2. How to remove duplicates

To remove duplicates from a single column:

1.  Select the cell range (or the entire column if that’s easier)

2.  Click the Data tab.

3.  Select the Remove Duplicates icon.

Figure 05: Remove Duplicates

4.  Tick the box labelled My data has headers if your data has a header row, otherwise leave unticked.

Figure 06: Confirm that you wish to remove the duplicates

3. How to group duplicates together

Highlighting and removing duplicates is easy for a single column of data, but it’s very rare that you have just one column. Normally, that data is part of a larger table and you need to remove the entire duplicate row.

On a larger table the first duplicate cell might be on row 5 and the next one on row 105 and the next one on row 505.

So is there an easy way to collect them all together in one place?

Good question. I’m glad you asked.

Yes, there is.

Excel allows you to sort by colour.

Here’s how:

1.  Highlight your duplicate cells (using conditional formatting) as described above. Here’s an example, where a duplicate Customer ID has been identified.

Figure 07: Conditional Formatting applied to the Customer ID column

2.  Next, click any cell in your table.

3.  Select the Data tab.

4.  Click the large Sort button (not the little AZ or ZA icons)

5.  In the Sort By drop-down list, select the column that contains the highlighted duplicates.

6.  Change Sort On to Cell Color.

7.  Set the Order to the colour you used to highlight the duplicate cells.

Figure 08: Sort the list by the Cell Color for the column that contains duplicates

The duplicate rows are now all grouped together at the top of the table. From here you can choose which rows to remove and delete them manually.

Figure 09: Duplicate values are grouped together at the top of the table

Note: When you have several duplicate items, they may not be paired together even though all they are all listed at the top of the table. To rectify this you can add a second sort level that sorts the data by value, smallest to largest.

4. How to check 2 or more columns for duplicates

Consider this example. Two columns have been selected and checked for duplicate values.

Figure 10: How can you check 2 columns in combination for duplicates?

As you can see Bloggs has been highlighted 3 times - twice for Fred and once for Del.

Also, Smith has been flagged as duplicate, even though one is Joe and one is Fred.

And finally, Fred has been highlighted 3 times - twice for Bloggs and once for Smith.

The only 2 entries that should be flagged here are Fred Bloggs.

To get around this, we can join first name and last name into a single full name using CONCATENATE. In cell D3, write the formula:

=CONCATENATE(B3, " ", C3)

The CONCATENATE function joins together all the items listed inside the brackets. As you can see, the first name and last name have been separated by a space.

Now, simply check the Full Name column for duplicate values.

Figure 10: First name and last name are joined together using CONCATENATE

5. What next?

I hope this has opened up your eyes and given you a few good ideas.

What do you think?