Lista suspensa do Excel: criar, editar, remover e operações mais avançadas

Uma lista suspensa é semelhante a uma caixa de listagem que permite aos usuários escolher um valor de uma lista de seleção. Este tutorial vai demonstrar as operações básicas da lista suspensa: criar, editar e remover a lista suspensa no Excel. Além disso, este tutorial fornece operações avançadas para lista suspensa para aprimorar sua funcionalidade e resolver mais problemas do Excel.

---

Crie uma lista suspensa simples

Para usar uma lista suspensa, você precisa aprender como criá-la primeiro. Esta seção fornece 6 maneiras de ajudá-lo a criar uma lista suspensa no Excel.

Crie uma lista suspensa a partir de um intervalo de células

Demonstre aqui as etapas para criar uma lista suspensa de um intervalo de células no Excel. Por favor faça o seguinte

1. Selecione um intervalo de células para localizar a lista suspensa.

Tips: Você pode criar uma lista suspensa para várias células não contíguas ao mesmo tempo, mantendo o botão Ctrl enquanto seleciona as células uma por uma.

2. Clique Data > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, sob o Configurações guia, configure da seguinte forma.

Notas:

Agora a lista suspensa foi criada. Ao clicar na célula da lista suspensa, uma seta será exibida ao lado dela, clique na seta para expandir a lista e, em seguida, você pode escolher um item dela.

Crie uma lista suspensa dinâmica a partir da tabela

Você pode converter seu intervalo de dados em uma tabela do Excel e, em seguida, criar uma lista suspensa dinâmica com base no intervalo da tabela.

1. Selecione o intervalo de dados original e pressione o botão Ctrl + T chaves.

2. Clique OK no surgimento Criar a tabela caixa de diálogo. Em seguida, o intervalo de dados é convertido em tabela.

3. Selecione um intervalo de células para colocar a lista suspensa e clique em Data > Validação de dados > Validação de dados.

4. No Validação de dados caixa de diálogo, você precisa:

Em seguida, listas suspensas dinâmicas são criadas. Ao adicionar ou remover dados do intervalo da tabela, os valores na lista suspensa serão atualizados automaticamente.

Crie uma lista suspensa dinâmica com fórmulas

Além de criar uma lista suspensa dinâmica a partir do intervalo da tabela, você também pode usar uma fórmula para criar uma lista suspensa dinâmica no Excel.

1. Selecione as células nas quais deseja a saída das listas suspensas.

2. Clique Data > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, configure como segue.

Em seguida, listas suspensas dinâmicas são criadas. Ao adicionar ou remover dados de um intervalo específico, os valores nas listas suspensas serão atualizados automaticamente.

Crie uma lista suspensa de um intervalo nomeado

Você também pode criar uma lista suspensa de um intervalo nomeado no Excel.

1. Em primeiro lugar, crie um intervalo nomeado. Selecione o intervalo de células que você criará com base no intervalo nomeado e, em seguida, digite o nome do intervalo no Nome caixa e pressione Entrar chave.

2. Clique Data > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, configure como segue.

Agora, a lista suspensa usando dados de um intervalo nomeado é criada.

Crie uma lista suspensa de outra pasta de trabalho

Supondo que haja uma pasta de trabalho chamada “Dados de Origem”, E você deseja criar uma lista suspensa em outra pasta de trabalho com base nos dados deste“Dados de Origem”Pasta de trabalho, faça o seguinte.

1. Abra a pasta de trabalho “SourceData”. Nesta pasta de trabalho, selecione os dados que você criará na lista suspensa com base, digite um nome de intervalo no Nome caixa e, em seguida, pressione a Entrar chave.

Aqui, nomeio o intervalo como Cidade.

2. Abra a planilha que você inserirá na lista suspensa. Clique Fórmulas > Definir o Nome.

3. No Novo nome caixa de diálogo, você precisa criar um intervalo nomeado com base no nome do intervalo que você criou na pasta de trabalho “SourceData”, configure conforme a seguir.

Notas:

4. Abra a pasta de trabalho que você irá inserir na lista suspensa, selecione as células para a lista suspensa e clique em Data > Validação de dados > Validação de dados.

5. No Validação de dados caixa de diálogo, configure como segue.

Agora, as listas suspensas foram inseridas no intervalo selecionado. E os valores suspensos são de outra pasta de trabalho.

Crie facilmente uma lista suspensa com uma ferramenta incrível

Aqui recomendo fortemente o Crie uma lista suspensa simples utilidade de Kutools for Excel. Com este recurso, você pode criar facilmente uma lista suspensa com valores de células específicos ou criar uma lista suspensa com listas personalizadas predefinidas no Excel.

1. Selecione as células que deseja inserir na lista suspensa e clique em Kutools > Lista suspensa > Crie uma lista suspensa simples.

2. No Crie uma lista suspensa simples caixa de diálogo, configure como segue.

Note: Se você deseja criar uma lista suspensa com base na predefinição de lista personalizada no Excel, selecione o Listas personalizadas opção no fonte seção, escolha uma lista personalizada no Listas personalizadas e, em seguida, clique no OK botão.

Agora, as listas suspensas foram inseridas no intervalo selecionado.

---

Editar lista suspensa

Se você deseja editar a lista suspensa, os métodos nesta seção podem lhe fazer um favor.

Edite uma lista suspensa com base em um intervalo de células

Para editar uma lista suspensa com base em um intervalo de células, faça o seguinte.

1. Selecione as células que contêm a lista suspensa que deseja editar e clique em Data > Validação de dados > Validação de dados.

2. No Validação de dados caixa de diálogo, altere as referências de célula no fonte caixa e, em seguida, clique no botão OK botão.

Edite uma lista suspensa com base em um intervalo nomeado

Suponha que você adicione ou exclua valores no intervalo nomeado e a lista suspensa seja criada com base neste intervalo nomeado. Para aparecer os valores atualizados nas listas suspensas, faça o seguinte.

1. Clique Fórmulas > Gerente de Nomes.

Tips: Você pode abrir o Gerente de Nomes janela pressionando o Ctrl + F3 chaves.

2. No Gerente de Nomes janela, você precisa configurar da seguinte maneira:

3. Então um Microsoft Excel caixa de diálogo aparece, clique no Sim para salvar as alterações.

Em seguida, as listas suspensas com base neste intervalo nomeado são atualizadas.

---

Remover lista suspensa

Esta seção fala sobre como remover a lista suspensa do Excel.

Remova a lista suspensa com o Excel integrado

O Excel fornece um recurso integrado para ajudar a remover a lista suspensa da planilha. Faça o seguinte.

1. Selecione o intervalo de células que contém a lista suspensa que deseja remover.

2. Clique Data > Validação de dados > Validação de dados.

3. No Validação de dados caixa de diálogo, clique no botão Limpar Tudo botão e, em seguida, clique em OK para salvar as mudanças.

Agora, as listas suspensas são removidas do intervalo selecionado.

Remova listas suspensas facilmente com uma ferramenta incrível

Kutools for Excel fornece uma ferramenta útil - Limpe a restrição de validação de dadoss para ajudar a remover facilmente a lista suspensa de um ou vários intervalos selecionados de uma vez. Faça o seguinte.

1. Selecione o intervalo de células que contém a lista suspensa que deseja remover.

2. Clique Kutools > Impedir Digitação > Limpar restrições de validação de dados. Veja a imagem:

3. Então um Kutools for Excel uma caixa de diálogo aparecerá perguntando se você deseja limpar a lista suspensa, clique no OK botão.

Em seguida, as listas suspensas neste intervalo selecionado são removidas imediatamente.

---

Adicionar cor à lista suspensa

Em alguns casos, pode ser necessário fazer uma lista suspensa codificada por cores para distinguir os dados nas células da lista suspensa rapidamente. Esta seção fornece dois métodos para ajudá-lo a resolver o problema em detalhes.

Adicionar cor à lista suspensa com Formatação Condicional

Você pode criar regras condicionais para a célula que contém a lista suspensa para torná-la codificada por cores. Faça o seguinte.

1. Selecione as células que contêm a lista suspensa que você deseja codificar por cores.

2. Clique Home page > Formatação condicional > Gerenciar regras.

3. No Gerenciador de linhas de formatação condicional caixa de diálogo, clique no botão Nova regra botão.

4. No Nova regra de formatação caixa de diálogo, configure como segue.

5. Quando ele retorna ao Gerenciador de regras de formatação condicional caixa de diálogo, repita os passos 3 e 4 acima para especificar as cores para outros itens suspensos. Após terminar de especificar as cores, clique no OK para salvar as mudanças.

A partir de agora, ao selecionar um item da lista suspensa, a célula será destacada com a cor de fundo especificada com base no texto selecionado.

Adicione cores facilmente à lista suspensa com uma ferramenta incrível

Aqui apresente o Lista suspensa colorida característica de Kutools for Excel para ajudá-lo a adicionar cores facilmente à lista suspensa do Excel.

1. Selecione as células que contêm a lista suspensa que você deseja adicionar cor.

2. Clique Kutools > Lista suspensa > Lista suspensa colorida.

3. No Lista suspensa colorida caixa de diálogo, faça o seguinte.

Tips: Se você deseja destacar as linhas com base na seleção da lista suspensa, escolha o Linha de intervalo de dados opção no Aplicar a seção e, em seguida, selecione as linhas que você destacará na Destacar linhas caixa.

Agora, as listas suspensas são codificadas por cores conforme as imagens abaixo mostradas.

Destacar células com base na seleção da lista suspensa

Destacar linhas com base na seleção da lista suspensa

---

Crie uma lista suspensa de dependentes no Excel ou no Google Planilha

Uma lista suspensa dependente ajuda a exibir as opções dependendo do valor selecionado na primeira lista suspensa. Se você precisar criar uma lista suspensa de dependentes (cascarding) na planilha do Excel ou no google sheet, os métodos nesta seção podem lhe fazer um favor.

Crie uma lista suspensa dependente na planilha do Excel

A demonstração abaixo exibe a lista suspensa dependente na planilha do Excel.

Por favor, clique Como criar uma lista suspensa em cascata dependente no Excel? para um tutorial de guia passo a passo.

Crie uma lista suspensa dependente na planilha do google

Se você deseja criar uma lista suspensa de dependentes na planilha do google, consulte Como criar uma lista suspensa de dependentes na planilha do Google?

---

Crie listas suspensas pesquisáveis

Para as listas suspensas que contêm uma longa lista de itens em uma planilha, não é fácil selecionar um determinado item da lista. Se você se lembra dos caracteres iniciais ou de vários caracteres consecutivos de um item, pode fazer o recurso de pesquisa em uma lista suspensa para filtrá-lo facilmente. Esta seção vai demonstrar como criar uma lista suspensa pesquisável no Excel.

Supondo que os dados de origem que você deseja criar uma lista suspensa com base nos locais na coluna A da Planilha1, conforme a captura de tela abaixo mostrada. Faça o seguinte para criar uma lista suspensa pesquisável no Excel com esses dados.

1. Em primeiro lugar, crie uma coluna auxiliar ao lado da lista de dados de origem com uma fórmula de matriz.

Neste caso, eu seleciono a célula B2, insiro a fórmula abaixo nela e pressiono o botão Ctrl + Shift + Entrar chaves para obter o primeiro resultado.

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

Selecione a primeira célula de resultado e arraste seu Pega de enchimento todo o caminho até chegar ao final da lista.

Note: Nesta fórmula de matriz, $ A $ 2: $ A $ 50 é o intervalo de dados de origem no qual você criará a lista suspensa. Altere-o com base no seu intervalo de dados.

2. Clique Fórmulas > Definir o Nome.

3. No Editar nome caixa de diálogo, configure como segue.

Agora você precisa criar a lista suspensa com base no intervalo nomeado. Nesse caso, criarei uma lista suspensa pesquisável na Planilha2.

4. Abra a Planilha2, selecione o intervalo de células para a lista suspensa e clique em Data > Validação de dados > Validação de dados.

5. No Validação de dados caixa de diálogo, faça o seguinte.

6. Clique com o botão direito na guia da folha (Folha2) e selecione Ver código no menu do botão direito.

7. Na abertura Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para o editor de código.

Código VBA: crie uma lista suspensa pesquisável no Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Appliations janela.

Agora as listas suspensas pesquisáveis ​​são criadas. Se quiser selecionar um item, basta inserir um ou vários caracteres consecutivos desse item na célula suspensa, clicar na seta suspensa e, em seguida, o item baseado no conteúdo inserido é listado na lista suspensa. Veja a imagem:

Note: Este método diferencia maiúsculas de minúsculas.

---

Crie uma lista suspensa, mas mostre valores diferentes

Supondo que você tenha criado uma lista suspensa, ao selecionar um item dela, você deseja que outra coisa seja exibida na célula. Como mostra a demonstração abaixo, você criou uma lista suspensa com base na lista de nomes de países; ao selecionar o nome do país no menu suspenso, você deseja exibir a abreviatura do nome do país selecionado na célula suspensa. Esta seção fornece o método VBA para ajudá-lo a resolver o problema.

1. No lado direito dos dados de origem (a coluna do nome do país), crie uma nova coluna contendo a abreviatura dos nomes dos países que você deseja exibir na célula suspensa.

2. Selecione a lista de nomes de países e a lista de abreviações, digite um nome no Nome caixa e, em seguida, pressione o Entrar chave.

3. Selecione as células para a lista suspensa (aqui eu seleciono D2: D8) e clique em Data > Validação de dados > Validação de dados.

4. No Validação de dados caixa de diálogo, configure como segue.

5. Depois de criar a lista suspensa, clique com o botão direito na guia da planilha e selecione Ver código no menu do botão direito.

6. Na abertura Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para o editor de código.

Código VBA: Mostrar valores diferentes na lista suspensa

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

Notas:

7. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

A partir de agora, ao selecionar um determinado nome de país da lista suspensa, a abbreation correspondente do nome do país selecionado será exibida na célula.

---

Crie uma lista suspensa com caixas de seleção

Muitos usuários do Excel tendem a criar listas suspensas com várias caixas de seleção para que possam selecionar vários itens da lista apenas marcando as caixas de seleção.

Como mostra a demonstração abaixo, ao clicar na célula que contém a lista suspensa, uma caixa de listagem aparece. Na caixa de listagem, há uma caixa de seleção antes de cada item. Você pode marcar as caixas de seleção para exibir os itens correspondentes na célula.

Se você deseja criar uma lista suspensa com caixas de seleção no Excel, consulte Como criar uma lista suspensa com várias caixas de seleção no Excel?.

---

Adicionar autocomplete à lista suspensa

Se você tiver uma lista suspensa de validação de dados com itens grandes, precisará rolar para cima e para baixo na lista para encontrar o apropriado ou digitar a palavra inteira diretamente na caixa de listagem. Se a lista suspensa puder ser preenchida automaticamente ao digitar a primeira letra, tudo ficará mais fácil.

Para fazer o preenchimento automático da lista suspensa em uma planilha no Excel, consulte Como preencher automaticamente ao digitar na lista suspensa do Excel?.

---

Filtre os dados com base na seleção da lista suspensa

Esta seção demonstrará como aplicar fórmulas para criar um filtro de lista suspensa para extrair dados com base na seleção da lista suspensa.

1. Em primeiro lugar, você precisa criar uma lista suspensa com os valores específicos nos quais você extrairá os dados.

Tips: Siga as etapas acima para crie uma lista suspensa no Excel.

Crie uma lista suspensa com uma lista única de itens

Se houver duplicatas em seu intervalo e você não quiser criar uma lista suspensa com a repetição de um item, você pode criar uma lista exclusiva de itens como segue.

1) Copie as células que você criará na lista suspensa com base em Ctrl + C e cole-as em um novo intervalo.

2) Selecione as células no novo intervalo, clique em Data > Remover Duplicados.

3) No Remover Duplicados caixa de diálogo, clique no botão OK botão.

4) Em seguida, um Microsoft Excel aparece para informar quantas cópias foram removidas, clique OK.

Agora que você obtém a lista exclusiva de itens, pode criar uma lista suspensa com base nessa lista exclusiva agora.

2. Em seguida, você precisa criar três colunas auxiliares da seguinte maneira.

3. Crie um intervalo com base no intervalo de dados original para produzir os dados extraídos com as fórmulas abaixo.

Notas:

Agora que um filtro de lista suspensa foi criado, você pode facilmente extrair dados do intervalo de dados original com base na seleção da lista suspensa.

---

Selecione vários itens da lista suspensa

Por padrão, a lista suspensa permite que os usuários selecionem apenas um item por vez em uma célula. Ao selecionar novamente o item em uma lista suspensa, o item selecionado anteriormente será substituído. No entanto, se você for solicitado a selecionar vários itens de uma lista suspensa e exibir todos eles na célula suspensa como mostrado na demonstração abaixo, como você pode fazer?

Para selecionar vários itens da lista suspensa no Excel, consulte Como criar uma lista suspensa com várias seleções ou valores no Excel?. Este tutorial fornece dois métodos em detalhes para ajudá-lo a resolver o problema.

---

Definir valor padrão (pré-selecionado) para a lista suspensa

Por padrão, uma célula da lista suspensa é exibida em branco, a seta suspensa só aparece quando você clica na célula. Como descobrir quais células contêm listas suspensas em uma planilha rapidamente?

Esta seção demonstrará como definir o valor padrão (pré-selecionado) para a lista suspensa no Excel. Faça o seguinte.

Antes de aplicar os dois métodos abaixo, você precisa criar uma lista suspensa e fazer algumas configurações como segue.

1. Selecione as células para a lista suspensa e clique em Data > Validação de dados > Validação de dados.

Tips: Se você já criou a lista suspensa, selecione as células que contêm a lista suspensa e clique em Data > Validação de dados > Validação de dados.

2. No Validação de dados caixa de diálogo, configure como segue.

Depois de criar a lista suspensa, aplique um dos métodos abaixo para definir o valor padrão para eles.

Defina o valor padrão para a lista suspensa com fórmula

Você pode aplicar a fórmula abaixo para definir o valor padrão para a lista suspensa que você criou conforme as etapas acima mostradas.

1. Selecione a célula da lista suspensa, insira a fórmula abaixo nela e pressione o botão Entrar para exibir o valor padrão. Se as células da lista suspensa forem consecutivas, você pode arrastar o Pega de enchimento da célula de resultado para aplicar a fórmula a outras células.

= IF (C2 = "", "--Escolha um item da lista--")

Notas:

Defina o valor padrão para todas as listas suspensas em uma planilha de uma vez com o código VBA

Supondo que existam muitas listas suspensas localizadas em diferentes intervalos em sua planilha, para definir o valor padrão para todas elas, você precisa aplicar a fórmula repetidamente. Isso é demorado. Esta seção fornece um código VBA útil para você definir o valor padrão para todas as listas suspensas em uma planilha de uma só vez.

1. Abra a planilha contendo as listas suspensas para as quais deseja definir o valor padrão e pressione o botão outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Móduloe, em seguida, cole o código VBA abaixo na janela Código.

Código VBA: defina o valor padrão para todas as listas suspensas em uma planilha de uma vez

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

Notas: No código acima, - Escolha da lista - é o valor padrão a ser exibido na célula da lista suspensa. Você também pode alterar o valor padrão com base na sua necessidade.

3. aperte o F5 , em seguida, uma caixa de diálogo Macros aparece, certifique-se de que DropDownListToDefault é selecionado no Nome da Macro e, em seguida, clique no Execute botão para executar o código.

Em seguida, o valor padrão especificado é preenchido nas células da lista suspensa imediatamente.

---

Aumentar o tamanho da fonte da lista suspensa

Normalmente, a lista suspensa tem um tamanho de fonte fixo, se o tamanho da fonte for tão pequeno para ler, você pode tentar o método VBA abaixo para aumentá-lo.

1. Abra a planilha contendo as listas suspensas para as quais deseja aumentar o tamanho da fonte, clique com o botão direito na guia da planilha e selecione Ver código no menu do botão direito.

2. No Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para o editor de código.

Código VBA: aumenta o tamanho da fonte das listas suspensas em uma planilha

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Note: aqui xZoom = 130 no código significa que você aumentará o tamanho da fonte de todas as listas suspensas na planilha atual para 130. Você pode alterá-lo conforme necessário.

3. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

A partir de agora, ao clicar na célula suspensa, o nível de zoom da planilha atual será ampliado, clique na seta suspensa, você pode ver o tamanho da fonte de todos os itens suspensos também são aumentados.

Depois de selecionar o item da lista suspensa, você pode clicar em qualquer célula fora da célula suspensa para retornar ao nível de zoom original.