A Lista Suspensa no Excel, também conhecido por drop down menu, é usado para inserir dados em uma planilha a partir de uma lista de itens pré-definidos. Hoje vamos aprender 3 Formas de criar lista suspensa no Excel. A principal finalidade do uso de listas suspensas no Excel é limitar o número de opções disponíveis para o usuário. Além disso, uma lista suspensa evita erros de ortografia e faz entrada de dados mais rápida.
Este tutorial irá mostrar 3 maneiras principais de criar Listas Suspensas no Excel, Estes, cada um tem sua particularidade, com vantagens e desvantagens para cada um. Vou mostrar passo a passo como criar estas Listas Suspensas no Excel.
Criar Lista Suspensa no Excel com Valores Separados por ponto e vírgula
Das 3 formas de crias as listas suspensa, acredito que esta seja a forma mais rápida. Esta forma pode ser feita a partir da versão 2003 do Excel.
1 – Selecione uma célula ou um intervalo de célula para sua lista suspensa.
A primeira coisa a se fazer é selecionar a célula ou o intervalo de célula onde ira inserir a lista suspensa. Esta forma é mais utilizada para listas com poucas opções de seleção, por exemplo respostas como sim e não, ou departamentos de empresas.
2 – Use validação de dados para criar a lista suspensa
Acesse Dados > Validação de Dados > Validação de Dados conforme abaixo.
3 – Insira as opções da validação de dados
Na janela de validação de dados, selecione as opções conforme a imagem abaixo.
- Em permitir, selecione Lista
- Selecione as opções Ignorar em branco e Menu suspenso na célula
- Em Fonte, digite as opções que deverão aparecer na lista, separados por ponto e vírgula.
- Clique em OK quando terminar.
Agora, simplesmente clique em uma seta ao lado de uma célula que contém uma lista suspensa e, em seguida, selecione a opção desejada.
Pronto, em menos de um minuto seu menu de lista suspensa no Excel está pronto. Esta opção é a forma mais rápida e simples de criar uma lista suspensa no Excel, mas não é a mais recomendada, isto se você quiser utilizar esta opções, como no exemplo, os setores e quiser utilizar as mesmas opções em outras planilhas pode demorar um pouco mais para copiar e colar as opções, e pior ainda se você tiver esta lista em vários outras planilhas e quiser atualizar as opções, como inserir um novo setor é um pouco trabalhoso. Veja a seguir outras formas para se criar lista suspensa no Excel.
Criando uma lista suspensa Excel com base em um intervalo nomeado
Este método pode ser um pouco mais demorado para criar, mas pode poupar seu tempo a longo prazo.
1 – Digite as opções para a lista suspensa
Digite as opções que deverão aparecer na lista suspensa. Estes valores devem ser inseridos em uma única coluna sem linhas em branco.
A ordem dos nomes que serão inseridos nestas células vão ser a ordem que irá aparecer na lista suspensa.
[feature_box style=”32″ title=”Dica” alignment=”center”]
É uma boa ideia para classificar as opções na ordem que você deseja que eles apareçam na lista suspensa.
[/feature_box]
2 – Criar um intervalo nomeado
Agora temos duas opções para criar nossa lista suspensa. Uma é selecionando somente o intervalo de células ou utilizando o intervalo nomeado. Vou mostrar a opção com o intervalo nomeado, acredito que seja a melhor opção por ser mais fácil.
Selecione o intervalo de células com os nomes que estarão na lista suspensa.
Clique com o botão direito do mouse e escolha Definir Nome. Ou após selecionar as opções aperte CTRL + F3, vai abrir o Gerenciado de Nomes do Excel e clique em Novo.
No campo nome digite o nome que queira dar para o intervalo, neste exemplo vamos dar o nome de Setores.
[feature_box style=”32″ title=”Dica” alignment=”center”]
A maneira mais fácil de nomear um intervalo é selecionar o intervalo de células e digitar o nome direto na Caixa de Nome, conforme a Imagem Abaixo.
[/feature_box]
3 – Inserindo o intervalo nomeado na lista suspensa
- Selecione as células onde estará a lista suspensa.
- Vá na Aba Dados > Validação de Dados > Validação de Dados.
- Na Janela em Permitir, selecione Lista
- Habilite a opção Ignorar em branco e Menu Suspenso na Célula.
- Em Fonte digite =Setores que é o nome do intervalo nomeado, ou Tecle F3, vai abrir uma janela com todos os intervalos nomeados e é só clicar duas vezes na opção desejada.
Está Pronto mais uma lista suspensa, agora com intervalo nomeado.
Lista Suspensa Atualizada Dinamicamente no Excel
Se você for atualizar as informações ou opções da lista suspensa, o recomendado é utilizar esta técnica, pois vai economizar muito o seu tempo ao atualizar as opções da lista.
Para criar a lista suspensa, o procedimento é praticamento o mesmo da lista suspensa com intervalo nomeado, mas na janela onde irá criar o nome você deve colocar a fórmula abaixo:
=DESLOC('Listas Exemplo 2'!$C$6;0;0;CONT.VALORES('Listas Exemplo 2'!$C6:'Listas Exemplo 2'!$C50);1)
Explicando a fórmula
A função DESLOC retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e de colunas a serem retornadas.
Neste nosso exemplo vou explicar passo a passo a fórmula.
Sintaxe da Fórmula
DESLOC(ref, lins, cols, [altura], [largura])
A sintaxe da função DESLOC tem os seguintes argumentos:
- ref Obrigatório. A referência na qual você deseja basear o deslocamento. (No Exemplo: A referência que vamos nos basear o deslocamento é a célula $C$6 da planilha Listas Exemplo 2, que é o primeiro nome que irá aparecer na lista suspensa.
- lins Obrigatório. O número de linhas, acima ou abaixo, a que se deseja que a célula superior esquerda se refira. (No Exemplo: O número será zero, vamos utilizar somente o argumento altura e largura).
- cols Obrigatório. O número de colunas, à esquerda ou à direita, a que se deseja que a célula superior esquerda do resultado se refira. (No Exemplo: O número será zero, vamos utilizar somente o argumento altura e largura).
- altura Opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo. (No Exemplo: Para determinar a altura, vamos contar o número de células que não estão vazias no intervalo de C6 até C50. Utiliza C50, pois acredito que não irá ter mais de 44 setores em uma empresa, mas poderíamos ter usado outros intervalos, menores ou maiores).
- largura Opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo. (No Exemplo: O número de colunas será 1, pois é somente em uma coluna que estão os nomes.
A principal vantagem de listas suspensas dinâmicas é que você não terá que alterar a referência para o intervalo com nome cada vez que editar a lista de origem. Se você apagar ou digitar novas entradas na lista de origens, todas as células que contêm esta lista de validação de dados serão atualizados automaticamente!
Neste exemplo, sempre inserirmos novos setores na lista de origem, a lista suspensa irá atualizar automaticamente.
Baixe o arquivo de exemplo para praticar.
Outros Artigos do Site:
Gerson,
Parabéns pelo compartilhamento de seu conhecimento em “Excel”, é uma excelete oportunidade para nós, profissionais que acabamos ficando eferrujados, com a dinâmica do dia a dia ou delegação de tarefas. Estarei consultando outras dicas e passo a passo para me aperfeiçoar.
Muito Obrigado Márcia. Grande Abraço.
Boa tarde!!!!
Primeiramente gostaria de agradecer , já busquei varias informações na sua página!
Gostaria de saber, se vc tem alguma planilha com calculos de frete (frete peso / pedágio com fração a cada 100kgs / frete valor – GRIS + CAD + Descontos + Outros +ICMS)
Grande abraço, parabéns pela iniciativa de espalhar seus conhecimentos
Olá Jaqueline, ainda não possuo esta planilha.
Por favor, podem me ajudar?
Tenho uma lista com 140 itens.
Será que tem como “navegar” nessa lista usando o botão de rolagem do mouse?
Muito obrigada.
Grande Gerson, boa noite!
Não faz ideia do quando esse conhecimento que compartilhou me foi útil…
Essa lista dinâmica me quebrava a cabeça havia muito tempo… e não via solução!
Com esse depoimento, quero que saiba que valeu a pena cada esforço seu nesse projeto, pois tenho a absoluta certeza de que ajudou a muitos e muito mesmo…….
Grande abraço e sucesso na vida!
Que ótima notícia. Fico feliz em ajudar. Abraço.
Boa tarde,
Você saberia remover os dados dessa lista suspensa que são iguais? Utilizando o método do desloc?
Obrigado!
Gerson, muito obrigado por compartilhar seu conhecimento de forma tão explicativa. Porém, veja se consegue me ajudar. Fiz o procedimento corretamente, coloquei os dados da lista numa planilha e em outra está o campo para selecionar esses dados da lista suspensa. Porém, a cada linha tenho dados a menos, como se estivesse havendo decremento dos itens da lista suspensa. Exemplo: Na linha 1 da lista suspensa estão todos os dados, do 1 ao 50, na lista 2 só encontro os dados do 1 ao 49 e assim por diante. Consegue me ajudar ?
O código está como segue:
=DESLOC(Lista!$B$3;0;0;CONT.VALORES(Lista!$B3:Lista!$B50);1)
Olá Fabrício: Precisa congelar as Listas. =DESLOC(Lista!$B$3;0;0;CONT.VALORES(Lista!$B$3:Lista!$B$50);1).
Muito didático, Parabéns!
Obrigado pela ajuda.
Muito obrigado pela orientação.
César Santos
Parabéns, Gerson!!
Seu tutorial é excelente.
Obrigada!!
Muito Obrigado Tatyana.
Parabéns, me ajudou muito 😉
Parabéns me ajudou e muito.
Show!
Obrigado.
Ari