Returning the average value for a set of values in Microsoft Excel is as easy as dropping in an AVERAGE() functionâ€”most of the time. As long as you want to consider every value in the data range, you’re good to go. But what if you need to ignore certain values?

For example, in Microsoft Excel the data set contains duplicate values, but you want to evaluate each value only once; in other words, you want to ignore the duplicates. In that case, AVERAGE() won’t get the job done. You could spend a lot of time coming up with an expression, but there’s a much easier way. I’ll show you how to get that duplicate-ignoring average without a complex expression.

I’m using (desktop) Office 365 on a Windows 10 64-bit system, but you can use older versions. You can work with your own data or download the demonstration .xlxs and .xls file. The Remove Duplicates tool isn’t available in the older .xls format, but you can use an Advanced filter instead. The online version supports this technique.

## About the AVERAGE() function in Microsoft Excel

Figure A shows the results of averaging a simple data set that includes duplicate values. There’s not a lot to say about this functionâ€”it performs exactly as you probably expect. The result is the same as summing the values and dividing by 8. AVERAGE() evaluates 0, but it doesn’t evaluate blanks.

Figure A

## How to create a unique list in Microsoft Excel

An expression is the first route most of us would take to ignore duplicates, but instead, let’s reconsider the problem. We don’t need a complex expression–we need a unique list based on the original data.

Fortunately, creating a list of unique values is easy, and from there, it’s a simple matter of using the standard AVERAGE() function.

We’re going to use the Remove Duplicates tool to create a list of unique values. We’ll start by copying the original data to another spotâ€”say, column D. This feature will remove duplicates from the data set, so you don’t want to work with the original data.

SEE: What to do if you’re still running Windows 7 (free PDF) (TechRepublic)

After copying the list (Figure B), follow these steps on the copied values in column D.

1. Select the data set–in this case that’s D3:D10.
2. Click the Data tab.
3. In the Data Tools group, click Remove Duplicates. In the resulting dialog (Figure B), click OK. When applying this to your work, you might need to check the My Data Has Headers option.
4. Click OK.
5. Click OK to confirm the removal of two duplicate values (53 and 12).

Figure B

The new average evaluates only the unique values in the original data set because that’s all there is to evaluate (Figure C). To skip the copy task, you can use Excel’s Advanced filter–click the Data tab and then click the Advanced option in the Sort &amp; Filter group.

Figure C

## A simple solution is often best

You might already know about Excel’s Remove Duplicates tool, but if you’re like most of us, your initial thought is to use an expression. All it takes to simplify this problem is to look at it a bit differently.