Add a Category Drop-Down to Bank Transactions in Excel

When you label transactions by hand, small spelling differences cause real problems. "Groceries", "Grocery" and "groceries " may look similar to a reader, but a formula that totals by category treats them as different text unless they match. A drop-down list restricts each cell to a fixed set of choices, so every row uses the same wording.

Excel provides this through data validation. Microsoft describes a drop-down list as a data validation rule using the List setting, where you supply the allowed values.

Microsoft's page on applying data validation gives these steps. Select the cells you want to validate. On the Data tab, in the Data Tools group, select Data Validation. On the Settings tab, in the Allow box, select List. In the Source box, type your list values separated by commas; the page's own example is Low,Average,High. Make sure the In-cell dropdown check box is selected, because Microsoft says that without it you will not see the drop-down arrow next to the cell.

You can also select or clear the Ignore blank check box depending on how you want blank (null) values handled, and Microsoft recommends testing the validation by trying both valid and invalid entries.

Microsoft notes that when someone enters invalid data, an error alert can display a message explaining the problem. On the Error Alert tab you can set a title, a message and a style; the styles listed on the page include Stop and Warning. A short message such as "Pick a category from the list" is usually enough.

Remember that validation is a guide, not a lock on the whole workbook. Microsoft also states that you cannot change data validation settings if your workbook is shared or your sheet is protected, so if the option is unavailable, check those two conditions first.

After setting it up, test it. Click a Category cell and confirm the arrow appears and the five choices are shown. Type a value that is not on the list and confirm your error alert appears. Microsoft also suggests checking that the cell is wide enough to show all list entries.

Once each row has a consistent category, you can total spending per category with SUMIFS. For example, with synthetic amounts of -4.50 and -7.25 both labelled Dining, a SUMIFS on Dining returns -11.75. If one row had been typed as "Dinning", it would be left out of that total, which is exactly the kind of error the drop-down helps prevent.

To remove validation later, Microsoft says to select the cells, go to Data > Data Validation, choose Clear All and select OK.

A drop-down does not pick the category for you; it only limits what you can choose. If you want categories assigned automatically from merchant names, a lookup formula is a better starting point. The two approaches can be combined, with a formula supplying a first guess and you reviewing the result.

If your transactions are still locked in a PDF, PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.