Excel Unique Function

 in Excel  tagged excel / sql / distinct / unique / group by / spill. spilled by

The Excel UNIQUE function gives you all of the distinct values of a given range. All you need to do is specify the range.

Below is a screenshot of a couple of examples of the use of UNIQUE. The first gives all of the distinct values of Places. The second one returns the unique combination of places and products. All we need to do is specify both columns. Click on the figure below to enlarge it.

Excel Unique Function

When you work with UNIQUE you are working with a formula that spills. It creates a spilled range. You can see that it is spilled because of the blue box around it. That’s F2 to F4 in our example. There are other Excel functions that spill. One example of that is the FILTER function.

The UNIQUE function can also return a list of data points that occur exactly once in the range. To do this, use the second and third arguement.

The UNIQUE function can exist on a separate worksheet. It is recommended to use a table and name the table appropriately. In this way you can refer to your table in the other sheet like this: =UNIQUE(tableName[columnName]), or in this case =UNIQUE(tblSales[Places]).

If you want to sort the results you can surround the existing formula with the SORT function. In our case we would have the following formula =SORT(UNIQUE(tblSales[Places]),1,1).

Learn with YouTube

Here’s a very short video on the most simple example of using the UNIQUE function. It’s called Excel UNIQUE Function | Excel One Minute Functions Explained and it’s by Chris Menard.

Compare SQL

Let’s have a quick look at the SQL that returns the unique values in a column. I’ve added the ORDER BY clause for sorting. It is the GROUP BY clause that returns unique values. GROUP BY is a very important clause in SQL as it returns aggregations (summaries) of data. 

SELECT [Places]
  FROM [dbo].[Sales]
  GROUP BY [Places]
  ORDER BY [Places] ASC

Another SQL expression that returns unique values is the keyword DISTINCT as in the following example. 

SELECT DISTINCT [Places]
  FROM [dbo].[Sales]
  ORDER BY [Places] ASC

If you need unique combinations of places and products you can simply add the Products column to the SELECT query, as shown below. 

SELECT DISTINCT [Places], [Products]
  FROM [dbo].[Sales]
  ORDER BY [Places] ASC

If you are using GROUP BY you can do something very similar as shown below. 

SELECT [Places], [Products]
  FROM [dbo].[Sales]
  GROUP BY [Places], [Products]
  ORDER BY [Places] ASC

SQL CREATE TABLE

Below is the SQL script you could use in SQL Server to create the table UniqueExcel and load the data into the table. You could do this in a non-production server and then test out the SELECT statements listed above. My non-production database is called MikeTest. 

CREATE TABLE [dbo].[Sales](
	[Places] [nvarchar](50) NULL,
	[Products] [nvarchar](50) NULL,
	[Sales] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Sales] ([Places], [Products], [Sales]) VALUES (N'Toronto', N'Widgets', 45000)
INSERT [dbo].[Sales] ([Places], [Products], [Sales]) VALUES (N'Toronto', N'Things', 16000)
INSERT [dbo].[Sales] ([Places], [Products], [Sales]) VALUES (N'Collingwood', N'Things', 560)
INSERT [dbo].[Sales] ([Places], [Products], [Sales]) VALUES (N'London', N'Widgets', 1200)
GO

Leave a Reply