Lista Suspensa Pesquisável no Excel: Como Criar e Atualizar
Aprenda a criar uma lista suspensa pesquisável no Excel que se atualiza automaticamente com Validação de Dados, DESLOC e CONT.VALORES.
Para criar uma lista suspensa pesquisável no Excel, selecione a coluna, vá em Dados > Validação de Dados > Lista e informe em Fonte a fórmula =DESLOC($A$1;1;0;CONT.VALORES(A:A)-1). Assim a lista passa a incluir sozinha os nomes novos, e o filtro conforme você digita é nativo no Microsoft 365.
O que você vai aprender?
Lista Suspensa Pesquisável no Excel
Na aula de hoje eu quero te mostrar como criar uma lista suspensa pesquisável no Excel. A ideia é que, à medida que você digita, a lista filtre os resultados com base no que você já escreveu na célula.
Dessa forma você terá uma lista suspensa só que com um filtro para pesquisar de forma rápida e prática, sem precisar percorrer todos os valores presentes na lista.
Para isso veremos como utilizar a validação de dados, a fórmula DESLOC no Excel e a função CONT.VALORES.
Faça o download do material disponível e vamos aprender como criar essa lista suspensa pesquisável no Excel!
Para fazer o download do(s) arquivo(s) utilizados na aula, preencha com o seu e-mail:
Lista Suspensa Pesquisável no Excel – Validação de Dados
Para criar a nossa lista suspensa no Excel, vamos utilizar um recurso chamado de Validação de Dados.
A Validação de Dados é uma ferramenta do Excel que nos permite verificar uma célula ou conjunto de células, definindo e restringindo os tipos de informações aceitas nessas células.
Isso nos dá maior controle sobre os dados inseridos na planilha, evitando que o usuário insira informações não permitidas, o que poderia resultar em erros.
Dentro da Validação de Dados temos diversas opções de restrição e controle, entre elas temos a opção de Lista.
Para criar nossa lista, vamos selecionar toda a coluna de Funcionário na nossa tabela, ir até a guia Dados e selecionar a Validação de Dados.
Na janela que se abrirá, escolheremos o tipo de validação Lista. Em Fonte, definiremos os funcionários cadastrados na tabela ao lado, registrados na coluna Nome.
Dessa forma, na nossa coluna Funcionário, teremos um menu suspenso na célula. Ao clicar nele, será exibida uma lista com os funcionários registrados.
E caso você tente digitar um nome nessa coluna que não esteja na lista, o Excel mostrará uma mensagem de erro.
Filtro Automático – Microsoft 365
Para tornar sua lista suspensa pesquisável, ou seja, para que ela filtre automaticamente os nomes que começam com a letra ou sílaba que você digitar, é necessário que você esteja usando a versão mais recente do Excel (o Microsoft 365).
Observe que ao digitar “Ca”, o Excel já filtra os funcionários que comecem o nome com “Ca”, isso facilita bastante a busca em listas muito extensas. Tornando sua pesquisa mais rápida e eficiente. E a partir do Excel mais recente, essa é uma função nativa das listas suspensas.
Lista Suspensa Pesquisável no Excel – Atualizando Automaticamente
Uma segunda funcionalidade que podemos adicionar à nossa lista é fazer com que, ao inserir um novo nome na primeira tabela da qual estamos validando os dados, esse nome seja adicionado automaticamente à nossa lista suspensa na segunda tabela.
Existem algumas maneiras de fazer isso, mas a mais prática e comumente usada pela sua conveniência é selecionar toda a coluna Nome ao definir a Fonte na Validação de Dados. No entanto, esse método fará com que a própria palavra 'Nome' faça parte da sua lista e que sempre haja uma linha em branco sendo exibida.
Para evitar isso e criar uma lista suspensa automática mais refinada, usaremos as funções DESLOC e CONT.VALORES no Excel.
Para fazer isso, selecione novamente nossa coluna Funcionário e vá para a opção Validação de Dados. No lugar da Fonte, insira a seguinte fórmula:
=DESLOC($A$1;1;0;CONT.VALORES(A:A)-1)Essa fórmula se baseia na função DESLOC do Excel, que desloca uma referência de célula de acordo com os argumentos fornecidos.
Então, estamos passando a célula A1 trancada ($A$1) para garantir que a célula de referência inicial seja sempre a célula A1, que é o início da coluna Nome de onde queremos obter os nomes dos funcionários.
Em seguida, usamos o número 1 como argumento, indicando o deslocamento de linha. Neste caso, movemos a referência uma linha para baixo, ou seja, a referência começa na célula A2.
O terceiro argumento indica o número de colunas a serem deslocadas. Como desejamos pegar os nomes na coluna 'Nome', usamos 0 para esse argumento, garantindo que não haja deslocamento horizontal para outras colunas.
Finalmente, o quarto argumento para a função DESLOC é a altura, que especifica quantas linhas devem ser incluídas no intervalo resultante. Por exemplo, se colocássemos o número 5, a fórmula só consideraria como intervalo os nomes de Fred até Marcelle.
Como queremos que nossa lista seja atualizada automaticamente, incluindo os nomes novos que adicionarmos na coluna Nome, a altura precisa ser calculada com base na quantidade de nomes na coluna.
Por isso, usamos a função CONT.VALORES(A:A) como argumento de altura, que conta quantas células na coluna A contêm valores, subtraindo 1 do resultado para excluir o cabeçalho onde está escrito Nome.
Portanto, passando essa fórmula para a Fonte da Validação de Dados teremos uma lista suspensa pesquisável no Excel que atualiza automaticamente.
Dessa forma, se adicionarmos novos nomes na primeira coluna, teremos esses nomes sendo exibidos na nossa lista suspensa.
E se algum funcionário for removido da coluna Nome, ele também não aparecerá mais na nossa lista.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Perguntas frequentes
1. Qual fórmula faz a lista suspensa se atualizar sozinha?
Use =DESLOC($A$1;1;0;CONT.VALORES(A:A)-1) no campo Fonte da Validação de Dados. O DESLOC parte de A1 travada, desce uma linha e monta o intervalo; o CONT.VALORES conta quantas células da coluna A estão preenchidas, e o menos 1 descarta o cabeçalho. Ao cadastrar um nome novo, ele já aparece na lista.
2. Em quais versões do Excel a lista filtra enquanto eu digito?
O filtro automático ao digitar é um recurso nativo das versões mais recentes do Excel, o Microsoft 365. Em versões anteriores a lista suspensa continua funcionando normalmente pela Validação de Dados, mas sem a pesquisa incremental: você percorre os itens pelo menu ou digita o valor completo.
3. Como criar uma lista suspensa simples no Excel?
Selecione as células que vão receber a lista, vá até a guia Dados e clique em Validação de Dados. Em Permitir, escolha Lista e, em Fonte, selecione o intervalo com os itens. A célula ganha um menu suspenso e recusa valores fora da lista.
4. Por que aparece uma linha em branco ou o cabeçalho na lista suspensa?
Isso acontece quando a coluna inteira é usada como Fonte: o título da coluna e as células vazias entram na lista junto com os nomes. A fórmula com DESLOC e CONT.VALORES resolve o problema, porque delimita o intervalo exatamente na quantidade de nomes preenchidos.
Conclusão – Lista Suspensa Pesquisável no Excel
Na aula de hoje, mostrei como criar uma lista suspensa pesquisável no Excel que se atualiza automaticamente. Dessa forma, você terá uma lista que valida apenas os nomes presentes na sua tabela de referência e filtra os resultados para uma pesquisa rápida e prática.
Você também aprendeu como usar a função DESLOC e CONT.VALORES no Excel para construir essa validação de dados mais inteligente e automática.
Apesar da função de pesquisa estar disponível apenas a partir da versão mais recente do Excel (o Microsoft 365), você pode aplicar os conhecimentos desta aula para adaptar as validações de dados em suas planilhas de acordo com a sua necessidade e realidade.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
O que você aprende neste vídeo
Resposta rápida: A lista pesquisável saiu de graça no Microsoft 365: basta a Validação de Dados do tipo Lista para digitar as primeiras letras e ver as opções filtrarem. Para a lista crescer sozinha quando a base ganha nomes, a Fonte deixa de ser um intervalo fixo e passa a ser um DESLOC com CONT.VALORES definindo a altura.
Neste vídeo (13 min):
- 2:00 - Criar a lista: selecione as células, guia Dados > Validação de Dados, tipo Lista, e aponte o intervalo de nomes.
- 3:00 - Além de facilitar, ela impede erro: digitar um nome fora da lista devolve mensagem de erro e a célula não aceita o valor.
- 3:30 - Na versão 365 atualizada, a pesquisa já vem pronta, digite "ca" e a lista mostra só quem começa com ca.
- 4:30 - O problema seguinte: com intervalo fixo, um nome novo na base não aparece na lista.
- 5:00 - Solução rápida (com ressalva): apontar a coluna inteira, funciona, mas traz o título e várias células vazias na lista.
- 6:00 - Solução fina: a função
DESLOC, que parte de uma célula e devolve um intervalo com a altura que você definir. - 7:00 - O argumento de linhas vai como 1 para começar abaixo do cabeçalho, senão o próprio título entra na lista.
- 9:00 - A altura não pode ser um número fixo: use
CONT.VALORESda coluna para contar quantos nomes existem. - 9:30 - Subtraia 1 do CONT.VALORES para descontar a célula do cabeçalho.
- 10:00 - Teste a fórmula numa célula solta antes: ela deve crescer e encolher conforme você adiciona ou remove nomes.
- 11:00 - Por fim, cole essa fórmula na Fonte da Validação de Dados, travando a célula inicial com F4.
Apostila Básica de Excel
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Hashtag Treinamentos
Para acessar outras publicações de Excel Básico, clique aqui!
Posts mais recentes de Excel Básico
- O que é Excel? Para que serve e o que dá para fazer nele
O que é Excel, para que serve, o que dá pra fazer do básico ao avançado e por que é uma das ferramentas mais exigidas no mercado de trabalho. - Porcentagem no Excel: como calcular com 5 Exemplos + Modelo
Aprenda a calcular porcentagem no Excel com 5 exemplos práticos, a nova função PORCENTAGEMDE e um modelo de planilha grátis para baixar! - Como aparar intervalo no Excel: guia completo do operador .:. e da função APARARINTERVALO
Aprenda como aparar intervalo no Excel com a função APARARINTERVALO e o operador .:. para eliminar células vazias e otimizar listas e fórmulas no Excel 365.
Posts mais recentes da Hashtag Treinamentos
- Planilha de Controle de Notas Fiscais no Excel [Grátis]
Baixe a planilha de controle de notas fiscais no Excel grátis: calcule ISS e retenções, veja o que está em aberto e gere a cobrança de cada nota de serviço. - Qual IA usar na empresa: ChatGPT, Claude ou Perplexity?
Qual IA usar na empresa: veja as diferenças entre ChatGPT, Claude e Perplexity e quando cada um rende mais em cada área da sua equipe. - Apostilas Gratuitas em PDF: Excel, Power BI, Python e IA
Baixe apostilas gratuitas em PDF de Excel, Power BI, Python, Claude e agentes de IA, com exercícios e gabarito. Escolha a sua e comece hoje.
