Budget Makeover – Ep. 2 – How to Remove Duplicates in Excel

Bottom Line: Learn how to remove duplicates to create a list of unique values that can be used for dropdown lists, lookup tables, reporting, and more.

Skill Level: Beginner

Watch the Tutorial

Watch on Youtube & Subscribe to our Channel

Download the Excel Files

You can download both the BEFORE and AFTER versions of the Excel files that I use in the video.

A Makeover for a Simple Budget

Welcome back to our Budget Makeover series.

In this series of posts, we're looking at the techniques involved in improving a basic budget to make it more user-friendly, not to mention better looking. Our first tutorial looked at how to lock cells and protect sheets.

In today's post, you'll find out how to remove duplicate entries so that you have a list of unique values. This is the first step in creating a dropdown menu, which will be explained in the next post.

Remove duplicates to create a unique list of values

A major part of the budget process is categorizing your transactions. Whether your transactions are input by hand or imported from a bookkeeping program or financial report, each transaction will require a category. That means your workbook will have some type of transaction table, which lists all of your income and expenses.

The best way of inputting the category for each transaction, in my opinion, is by using a dropdown list. That's because if you type in the categories for each cell, chances are there will be misspellings which can cause errors in your reporting down the line. The dropdown list also ensures that there is a set list of defined categories for users to choose from.

Dropdown lists help categorize budget transactions

The first step in making a dropdown list is to create a list of unique values, or remove duplicates. There are several ways to do this in Excel, but I'm just going to explain one technique and then I will explain why.

How to Remove Duplicates

Start by selecting all of the cells in the column of category types in your Excel Table. A quick keyboard shortcut for that is Ctrl + Space.

Then copy those values (Ctrl + C) and paste them into a blank worksheet using your favorite method. (Here's a list of five different ways.) My favorite method for pasting values is Menu + V.

With all of the values pasted into the new sheet, go to the Data tab on the Ribbon and click Remove Duplicates.

Remove duplicates data menu

That'll open up a window where no changes need to be made, so click OK. Excel will tell you how many duplicates it removed and how many unique values remain. Click OK.

Remove duplicates unique values dialogue box

That's it! You can add a header to the list of unique values if you wish. Removing duplicates may seem like a simple task that isn't directly related to budgeting, but I find that I use this technique quite often, so it's a good tool to have in your toolbelt.

Why This Method?

As I mentioned, there are many ways to remove duplicates in Excel, including Power Query, a pivot table, or the new UNIQUE function. I chose this technique specifically, however, because I want to keep my list static. I don't want the category list to change automatically if someone manually writes a new category or misspells an existing one in the transaction list.

In the next video, we're going to create a dropdown list based on this list of unique values that we've created. Until then, if you have any questions or suggestions about this post, feel free to leave a comment below.

Episode 1: Budget Makeover – Ep. 1 – How to Lock Cells for Editing in Excel

Episode 3: Budget Makeover – Ep. 3 – Creating Dropdown Lists 

Episode 4: Budget Makeover – Ep. 4 – Using Freeze Panes in Excel

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Test Your Excel Skills

Free Excel Training Webinar Modern Power Tools

>