Como criar uma lista suspensa no Excel (o único guia de que você precisa)

Uma lista suspensa é uma excelente maneira de dar ao usuário a opção de selecionar em uma lista predefinida.

Ele pode ser usado enquanto o usuário preenche um formulário ou cria painéis interativos do Excel.

Listas suspensas são bastante comuns em sites / aplicativos e são muito intuitivas para o usuário.

Assistir ao vídeo - Criando uma lista suspensa no Excel

Neste tutorial, você aprenderá a criar uma lista suspensa no Excel (leva apenas alguns segundos para fazer isso) junto com todas as coisas incríveis que você pode fazer com ela.

Como criar uma lista suspensa no Excel

Nesta seção, você aprenderá as etapas exatas para criar uma lista suspensa do Excel:

1. Usando dados de células.
2. Inserindo dados manualmente.
3. Usando a fórmula OFFSET.

Nº 1: Usando Dados de Células

Digamos que você tenha uma lista de itens conforme mostrado abaixo:

Aqui estão as etapas para criar uma Lista suspensa do Excel:

1. Selecione uma célula onde deseja criar a lista suspensa.
2. Vá para Dados -> Ferramentas de dados -> Validação de dados.
3. Na caixa de diálogo Validação de dados, na guia Configurações, selecione Lista como o critério de validação.
   • Assim que você selecionar Lista, o campo de origem aparecerá.
4. No campo fonte, digite = $ A $ 2: $ A $ 6, ou simplesmente clique no campo Fonte e selecione as células com o mouse e clique em OK. Isso irá inserir uma lista suspensa na célula C2.
   • Certifique-se de que a opção suspensa Na célula esteja marcada (que é marcada por padrão). Se esta opção estiver desmarcada, a célula não mostra uma lista suspensa, no entanto, você pode inserir manualmente os valores na lista.

Observação: Se você deseja criar listas suspensas em várias células de uma vez, selecione todas as células onde deseja criá-las e siga as etapas acima. Certifique-se de que as referências de célula são absolutas (como $ A $ 2) e não relativas (como A2, ou A $ 2 ou $ A2).

# 2 Inserindo dados manualmente

No exemplo acima, as referências de células são usadas no campo Fonte. Você também pode adicionar itens diretamente inserindo-os manualmente no campo de origem.

Por exemplo, digamos que você queira mostrar duas opções, Sim e Não, na lista suspensa de uma célula. Veja como você pode inseri-lo diretamente no campo da fonte de validação de dados:

• Selecione uma célula na qual deseja criar a lista suspensa (célula C2 neste exemplo).
• Vá para Dados -> Ferramentas de dados -> Validação de dados.
• Na caixa de diálogo Validação de dados, na guia Configurações, selecione Lista como o critério de validação.
  • Assim que você selecionar Lista, o campo de origem aparecerá.
• No campo de origem, digite Sim, Não
  • Certifique-se de que a opção suspensa In-cell esteja marcada.
• Clique OK.

Isso criará uma lista suspensa na célula selecionada. Todos os itens listados no campo de origem, separados por vírgula, são listados em linhas diferentes no menu suspenso.

Todos os itens inseridos no campo de origem, separados por vírgula, são exibidos em linhas diferentes na lista suspensa.

Observação: Se você deseja criar listas suspensas em várias células de uma vez, selecione todas as células onde deseja criá-las e siga as etapas acima.

# 3 Usando fórmulas do Excel

Além de selecionar células e inserir dados manualmente, você também pode usar uma fórmula no campo de origem para criar uma lista suspensa do Excel.

Qualquer fórmula que retorna uma lista de valores pode ser usada para criar uma lista suspensa no Excel.

Por exemplo, suponha que você tenha o conjunto de dados conforme mostrado abaixo:

Aqui estão as etapas para criar uma lista suspensa do Excel usando a função OFFSET:

• Selecione uma célula na qual deseja criar a lista suspensa (célula C2 neste exemplo).
• Vá para Dados -> Ferramentas de dados -> Validação de dados.
• Na caixa de diálogo Validação de dados, na guia Configurações, selecione Lista como o critério de validação.
  • Assim que você selecionar Lista, o campo de origem aparecerá.
• No campo Fonte, insira a seguinte fórmula: = OFFSET ($ A $ 2,0,0,5)
  • Certifique-se de que a opção suspensa In-cell esteja marcada.
• Clique OK.

Isso criará uma lista suspensa que lista todos os nomes de frutas (conforme mostrado abaixo).

Observação: Se você deseja criar uma lista suspensa em várias células de uma vez, selecione todas as células onde deseja criá-la e siga as etapas acima. Certifique-se de que as referências de célula sejam absolutas (como $ A $ 2) e não relativas (como A2, ou A $ 2 ou $ A2).

Como funciona essa fórmula ??

No caso acima, usamos uma função OFFSET para criar a lista suspensa. Ele retorna uma lista de itens do ra

Ele retorna uma lista de itens do intervalo A2: A6.

Aqui está a sintaxe da função OFFSET: = OFFSET (referência, linhas, colunas, [altura], [largura])

Leva cinco argumentos, onde especificamos a referência como A2 (o ponto de partida da lista). Rows / Cols são especificados como 0, pois não queremos deslocar a célula de referência. A altura é especificada como 5, pois há cinco elementos na lista.

Agora, quando você usa esta fórmula, ela retorna uma matriz que contém a lista das cinco frutas em A2: A6. Observe que se você inserir a fórmula em uma célula, selecione-a e pressione F9, você verá que ela retorna uma matriz dos nomes das frutas.

Criação de uma lista suspensa dinâmica no Excel (usando OFFSET)

A técnica acima de usar uma fórmula para criar uma lista suspensa pode ser estendida para criar uma lista suspensa dinâmica também. Se você usar a função OFFSET, conforme mostrado acima, mesmo se você adicionar mais itens à lista, a lista suspensa não será atualizada automaticamente. Você terá que atualizá-lo manualmente cada vez que alterar a lista.

Aqui está uma maneira de torná-lo dinâmico (e não é nada além de um pequeno ajuste na fórmula):

• Selecione uma célula na qual deseja criar a lista suspensa (célula C2 neste exemplo).
• Vá para Dados -> Ferramentas de dados -> Validação de dados.
• Na caixa de diálogo Validação de dados, na guia Configurações, selecione Lista como o critério de validação. Assim que você selecionar Lista, o campo de origem aparecerá.
• No campo de origem, insira a seguinte fórmula: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Certifique-se de que a opção suspensa In-cell esteja marcada.
• Clique OK.

Nesta fórmula, substituí o argumento 5 por COUNTIF ($ A $ 2: $ A $ 100, ””).

A função CONT.SE conta as células não vazias no intervalo A2: A100. Portanto, a função OFFSET se ajusta para incluir todas as células não vazias.

Observação:

• Para que isso funcione, NÃO deve haver nenhuma célula em branco entre as células que estão preenchidas.
• Se você deseja criar uma lista suspensa em várias células de uma só vez, selecione todas as células onde deseja criá-la e siga as etapas acima. Certifique-se de que as referências de célula sejam absolutas (como $ A $ 2) e não relativas (como A2, ou A $ 2 ou $ A2).

Copiar e colar listas suspensas no Excel

Você pode copiar e colar as células com validação de dados para outras células, e isso irá copiar a validação de dados também.

Por exemplo, se você tiver uma lista suspensa na célula C2 e quiser aplicá-la a C3: C6 também, basta copiar a célula C2 e colá-la em C3: C6. Isso copiará a lista suspensa e a tornará disponível em C3: C6 (junto com a lista suspensa, também copiará a formatação).

Se você deseja apenas copiar o menu suspenso e não a formatação, estas são as etapas:

• Copie a célula que contém o menu suspenso.
• Selecione as células nas quais deseja copiar o menu suspenso.
• Vá para Home -> Colar -> Colar especial.
• Na caixa de diálogo Colar especial, selecione Validação em Opções de colagem.
• Clique OK.

Isso copiará apenas o menu suspenso e não a formatação da célula copiada.

Cuidado ao trabalhar com a lista suspensa do Excel

Você precisa ter cuidado ao trabalhar com listas suspensas no Excel.

Quando você copia uma célula (que não contém uma lista suspensa) sobre uma célula que contém uma lista suspensa, a lista suspensa é perdida.

A pior parte disso é que o Excel não mostrará nenhum alerta ou prompt para permitir que o usuário saiba que um menu suspenso será sobrescrito.

Como selecionar todas as células que possuem uma lista suspensa

Às vezes, é difícil saber quais células contêm a lista suspensa.

Portanto, faz sentido marcar essas células dando a elas uma borda distinta ou uma cor de fundo.

Em vez de verificar manualmente todas as células, há uma maneira rápida de selecionar todas as células que possuem listas suspensas (ou qualquer regra de validação de dados).

• Vá para Home -> Find & Select -> Go To Special.
• Na caixa de diálogo Ir para especial, selecione Validação de dados
  • A validação de dados tem duas opções: All e Same. Todos selecionariam todas as células que têm uma regra de validação de dados aplicada a ela. Mesmo selecionaria apenas as células que têm a mesma regra de validação de dados da célula ativa.
• Clique OK.

Isso selecionaria instantaneamente todas as células que têm uma regra de validação de dados aplicada a ela (isso inclui listas suspensas também).

Agora você pode simplesmente formatar as células (dar uma borda ou uma cor de fundo) para que fiquem visualmente visíveis e você não copie acidentalmente outra célula nela.

Aqui está outra técnica de Jon Acampora que você pode usar para sempre manter o ícone da seta suspensa visível. Você também pode ver algumas maneiras de fazer isso neste vídeo do Sr. Excel.

Criação de uma lista suspensa dependente / condicional do Excel

Aqui está um vídeo sobre como criar uma lista suspensa dependente no Excel.

Se você prefere ler a assistir a um vídeo, continue lendo.

Às vezes, você pode ter mais de uma lista suspensa e deseja que os itens exibidos na segunda lista suspensa dependam do que o usuário selecionou na primeira lista suspensa.

Elas são chamadas de listas suspensas de dependentes ou condicionais.

Abaixo está um exemplo de uma lista suspensa condicional / dependente:

No exemplo acima, quando os itens listados no 'Drop Down 2' dependem da seleção feita no 'Drop Down 1'.

Agora vamos ver como criar isso.

Aqui estão as etapas para criar uma lista suspensa dependente / condicional no Excel:

• Selecione a célula onde deseja a primeira lista suspensa (principal).
• Vá para Dados -> Validação de dados. Isso abrirá a caixa de diálogo de validação de dados.
• Na caixa de diálogo de validação de dados, na guia de configurações, selecione Lista.
• No campo Origem, especifique o intervalo que contém os itens que devem ser mostrados na primeira lista suspensa.
• Clique OK. Isso criará o menu suspenso 1.
• Selecione todo o conjunto de dados (A1: B6 neste exemplo).
• Vá para Fórmulas -> Nomes definidos -> Criar a partir da seleção (ou você pode usar o atalho de teclado Control + Shift + F3).
• Na caixa de diálogo ‘Criar nome a partir da seleção’, marque a opção Linha superior e desmarque todas as outras. Isso cria 2 intervalos de nomes ('Frutas' e 'Vegetais'). Frutas com nome de intervalo referem-se a todas as frutas da lista e Vegetais com nome de intervalo refere-se a todos os vegetais da lista.
• Clique OK.
• Selecione a célula onde deseja a lista suspensa Dependente / Condicional (E3 neste exemplo).
• Vá para Dados -> Validação de dados.
• Na caixa de diálogo Validação de dados, na guia de configuração, certifique-se de que Listar está selecionado.
• No campo Fonte, insira a fórmula = INDIRETO (D3). Aqui, D3 é a célula que contém o menu suspenso principal.
• Clique OK.

Agora, quando você faz a seleção na Lista suspensa 1, as opções listadas na Lista suspensa 2 são atualizadas automaticamente.

Baixe o arquivo de exemplo

Como é que isso funciona? - A lista suspensa condicional (na célula E3) refere-se a = INDIRETO (D3). Isso significa que quando você seleciona 'Frutas' na célula D3, a lista suspensa no E3 se refere ao intervalo nomeado 'Frutas' (por meio da função INDIRETA) e, portanto, lista todos os itens nessa categoria.

Observação importante ao trabalhar com listas suspensas condicionais no Excel:

• Depois de fazer a seleção e alterar a lista suspensa pai, a lista suspensa dependente não mudaria e, portanto, seria uma entrada errada. Por exemplo, se você selecionar os EUA como o país e, em seguida, selecionar Flórida como o estado e, em seguida, voltar e alterar o país para Índia, o estado permanecerá como Flórida. Aqui está um ótimo tutorial de Debra sobre como limpar listas suspensas de dependentes (condicionais) no Excel quando a seleção é alterada.
• Se a categoria principal tiver mais de uma palavra (por exemplo, 'Frutas da estação' em vez de 'Frutas'), você precisará usar a fórmula = INDIRETO (SUBSTITUTAR (D3, ”“, ”_”)), em vez de função INDIRETA simples mostrada acima. A razão para isso é que o Excel não permite espaços em intervalos nomeados. Portanto, quando você cria um intervalo nomeado usando mais de uma palavra, o Excel insere automaticamente um sublinhado entre as palavras. Portanto, o intervalo denominado ‘Frutas da estação’ seria ‘Frutas da estação’. Usar a função SUBSTITUTE dentro da função INDIRECT garante que os espaços está convertido em sublinhados.