{"id":224,"date":"2025-10-12T18:01:57","date_gmt":"2025-10-12T18:01:57","guid":{"rendered":"https:\/\/blog.callmelazy.eu\/?p=224"},"modified":"2025-10-31T22:43:20","modified_gmt":"2025-10-31T22:43:20","slug":"tips-and-tricks-for-excel-in-cell-dropdowns","status":"publish","type":"post","link":"https:\/\/blog.callmelazy.eu\/?p=224","title":{"rendered":"Tips and tricks for Excel in-cell dropdowns"},"content":{"rendered":"\n

Are you tired of users (even yourself) mis-typing data in Excel cells, breaking your lookup formulas? Do you want to select from a list of items to reduce typing and copy\/paste?<\/p>\n\n\n\n

You probably saw Excel worksheets where you click on the “down” arrow and get a list. This quick guide will help you do it and reveal a few tips and tricks to make your life easier.<\/p>\n\n\n\n

Getting started with data validation and in-cell dropdowns<\/h2>\n\n\n\n

In-cell dropdowns start with “Data Validation”. This limits the data a user can enter in a cell. It is available in the “Data” ribbon, within Microsoft Excel:<\/p>\n\n\n\n

\"Data<\/figure>\n\n\n\n

Click on the Data Validation button and you get the “Validation criteria” window, where you choose what to allow. By default, it is “Any value”:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

For in-cell dropdown lists, select “List” from the “Allow” options:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Then, in the “Source” field, enter the list of data to choose from:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Note also the “In-cell dropdown” checkbox: it must be checked to get an in-cell dropdown (this is the default). You have few ways to enter the Source, and I will provide some tips about it. I start with the most scalable approach and I present some shortcuts later.<\/p>\n\n\n\n

The best way: use tables and named ranges<\/h2>\n\n\n\n

The best way to set the source for a data validation list is:<\/p>\n\n\n\n

    \n
  1. Create a Table<\/li>\n\n\n\n
  2. Create a Named Range inside the table<\/li>\n\n\n\n
  3. Use the named range as the Source in the Data Validation window<\/li>\n<\/ol>\n\n\n\n

    Let’s explain this step by step.<\/p>\n\n\n\n

    A short introduction to Tables<\/h3>\n\n\n\n

    First, let’s explain an often misunderstood feature in Excel: Tables.<\/p>\n\n\n\n

    For many users, Tables is only a cosmetic feature. This misunderstanding may be due to the choice of words on the button: “Format as Table”:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    In reality, “Format as Table” does a lot more than pleasing the eye. Internally, Excel handles Tables very differently to Ranges. This improves data validation lists, among other things. If you want to understand tables better, you can read more about them here<\/a>. For now, let’s see how they affect Data Validation.<\/p>\n\n\n\n

    Set up a Table for Data Validation<\/h3>\n\n\n\n

    To prepare the table, follow these steps (or check the video below):<\/p>\n\n\n\n