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
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.
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.
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.
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.
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.