Lista de Dados no Excel – DropDown Excel

Neste artigo você aprenderá como criar Lista de Dados ou menu DropDown no Excel.

Ao longo do artigo e também na vídeo-aula você aprenderá diversas técnicas como desde uma lista de validação fixa no Excel até uma lista com autocompletar.

Como Criar uma Lista de Dados Fixa no Excel

Para criar uma lista de dados fixa no Excel uma solução é usar a validação de dados.

A validação de dados tem por finalidade impedir que dados sejam lançados incorretamente, como um item fora de uma lista, um texto em um campo de data, número…

Para criar uma lista de dados fixa no Excel usando a validação de dados você deve selecionar o local aonde deseja inserir a lista de validação.

Em seguida clique em Dados->Validação de dados.

Clique em Permitir e selecione a opção Lista e selecione o intervalo aonde você tem os dados que desejam que conste na lista de validação.

 lista de dados excel 1

Com isso você já terá uma lista com os itens constantes na sua lista.

listas suspensas listas de dados menu dropdown excel 2

Esta lista necessariamente não precisa ser à partir e um conjunto de células, pode ser também à partir de um intervalo nomeado ou ainda de textos digitados.

Na coluna horário incluímos apenas os horários permitidos usando uma lista.

Para isso usamos a mesma técnica anterior, apenas digitando os textos conforme na imagem abaixo.

listas suspensas listas de dados menu dropdown excel 3

Com isso poderão ser selecionados ou digitados apenas os valores 11:00, 18:30 ou 19:00. Separe os valores da lista com ;.

Lista de Validação com Tamanho Variável

Uma das formas de retornar uma lista de dados com tamanho variável é usando as funções de matrizes dinâmicas ÚNICO e CLASSIFICAR.

A função Único do Excel permite extrair uma lista sem duplicidades à partir de uma lista com itens duplicados. Isso dinamicamente.

listas suspensas listas de dados menu dropdown excel 5

Para o exemplo, temos uma lista conforme acima, de produtos da empresa, e desejamos extrair somente as categorias e colocar em uma lista para seleção.

Então usamos a função =ÚNICO(Tabela9[Categoria]) para que sejam retornados apenas valores sem repetição.

Também pode ser aplicada a função CLASSIFICAR em conjunto, e assim termos uma lista única de dados e classificada.

Para isso usamos então as funções =CLASSIFICAR(ÚNICO(Tabela9[Categoria])).

E após isso precisamos ainda colocar em formato de lista de validação de dados no Excel.

Para isso clique em Dados->Validação de dados e selecione Lista em Permitir.

Em fonte, ao invés de selecionar todos os itens, selecione apenas a primeira célula, a célula que possui a fórmula da lista única e classificada.

Digite # ao final da referência.

Isso fará com que a validação de dados entenda que se trata de uma matriz dinâmica, ou seja, os itens devem ser listados à partir da célula da fórmula.

listas suspensas listas de dados menu dropdown excel 6

E o resultado é então uma lista única e classificada na validação, e se tiverem novos itens, automaticamente serão retornados estes itens para a lista de validação.

listas suspensas listas de dados menu dropdown excel 6

Outro ponto importante é que esta fórmula que vimos também pode ser adicionada como um nome definido em Fórmulas->Definir Nome e assim usar este nome definido na lista de validação.

Lista de Dados Encadeadas ou em Cascata no Excel

Há vezes em que temos listas de dados encadeadas, ou em cascata no Excel.

Um exemplo típico é quando temos uma lista de UF e cidades e desejamos listar apenas as cidades de uma determinada UF.

listas suspensas listas de dados menu dropdown excel 7

No exemplo acima temos então a necessidade de selecionar uma UF e ao lado dela selecionar uma cidade.

Para isso usamos a seguinte fórmula, para listar apenas os itens da UF AC que está na célula J9.

=DESLOC($B$9;CORRESP($I$9;Tabela3614[UF];0);;CONT.SE(Tabela3614[UF];$I$9))

A lista de UF e Cidade está agrupada por UF e classificada por UF, importante salientar que só funcionará deste jeito esta técnica.

A função acima é um DESLOC usando CORRESP para definir aonde começa, aonde tem a primeira uf AC e depois temos a função CONT.SE para definir a quantidade de vezes que a palavra AC aparece e com isso temos uma relação de cidades com início e tamanho variável.

Lembro ainda que o download está no final deste artigo, pode baixar para ver o exemplo pronto.

O resultado é a lista abaixo.

listas suspensas listas de dados menu dropdown excel 8

Fazemos então a inclusão desta fórmula em Gerenciador de nomes->Fórmulas e usamos na validação de dados o nome que usamos, no caso ListaCidades.

listas suspensas listas de dados menu dropdown excel 9

Clique no local aonde deseja adicionar a validação de dados e clique em Dados->Validação de dados e selecione Lista.

listas suspensas listas de dados menu dropdown excel 10

Temos agora uma lista de cidades conforme a UF que estiver na célula J9.

Para fazer com que esta lista mude conforme a UF selecionada podemos usar a função CÉL.

A função CÉL extrai informações da planilha ou da célula, no caso, da célula que acabou de ser alterada.

listas suspensas listas de dados menu dropdown excel 11

Com isso temos que ao alterar qualquer célula o conteúdo irá alterar na célula J9 que controla a lista de cidades que aparecem na lista de dados.

Então ao alterarmos na coluna UF temos esta lista alterada e podemos selecionar a cidade na lista de cidades.

Lista de Validação Encadeadas ou em Cascata com VBA

A lista de dados que vimos no item anterior tem algumas particularidades, como a necessidade de selecionar a UF sempre que mudar de linha.

Podemos fazer com VBA de outra forma para que esse código da UF seja mudado automaticamente

Então, pode usar a mesma função DESLOC que vimos acima e toda a estrutura com o gerenciador de nomes, mas não use a função CÉL.

Usaremos um código VBA que irá pegar a UF à partir da linha da coluna C.

Clique sobre a célula aonde há a UF, aonde irá controlar a UF, como no exemplo anterior e defina o nome para ufselecionada.

Primeiro habilite a guia desenvolvedor no Excel.

Em seguida clique na planilha na árvore de projeto que deseja colocar a alteração dinâmica da UF.

listas suspensas listas de dados menu dropdown excel 12

Clique duas vezes sobre e cole o seguinte código:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        Calculos.Range("ufselecionada").Value = Cells(Target.Row, 2).Value
    End If
End Sub

O código acima irá ser chamado ao mudar de célula selecionada

E ao mudar a célula será verificado se a coluna atual é a 3, ou seja, a coluna da cidade.

Se for a célula ufselecionada, que controla a UF que será listada na lista de dados retorna as cidades será alterada com a informação da coluna B que tem a UF relacionada.

listas suspensas listas de dados menu dropdown excel 14

Lista de Validação Incremental no Excel

Outra forma interessante de usar listas no Excel é de forma incremental.

Por exemplo, temos uma lista de TVs e desejamos selecionar as qualidades que fazem parte de cada uma à partir de uma lista previamente cadastrada, e desejamos que sejam separados por vírgula.

listas suspensas listas de dados menu dropdown excel 16

A primeira etapa é a de criar uma lista suspensa fixa.

Selecione a coluna aonde irá criar a lista de dados incremental no Excel.

E clique em Dados->Validação de dados, selecione a lista.

listas suspensas listas de dados menu dropdown excel 17

Abra o VBA clicando na guia Desenvolvedor no botão Visual Basic ou pressione ALT+F11.

Coloque o seguinte código na planilha:

'Código para adicionar dados á uma seleção apartir de uma lista de validações.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim strSep As String

    strSep = ", "
  
    Application.EnableEvents = False

On Error Resume Next

If Target.Count > 1 Then GoTo Sair

    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo Sair
    
    If rngDV Is Nothing Then GoTo Sair
    
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
    
      newVal = Target.Value
      
      Application.Undo
      oldVal = Target.Value
      
      Target.Value = newVal
      
    If newVal <> "" Then
         If oldVal = "" Then
            Target.Value = newVal
         Else
            Target.Value = oldVal & strSep & newVal
         End If
    End If
    
    End If

Sair:
  Application.EnableEvents = True
End Sub

O código acima fará com que ao selecionar um item, ele seja adicionado na linha e com uma vírgula para assim você ter uma lista de qualidades de cada televisor.

Lista de Validação com Autocompletar

O Excel também permite que criemos listas com autocompletar ou lista com completar ao digitar.

No momento não há ainda no Excel este recurso nativo e não deverá existir em versões anteriores ao 2021.

Para criar uma lista com autocompletar no Excel em qualquer versão siga o seguinte procedimento.

Clique na guia Desenvolvedor e selecione o botão Caixa de Combinação (Controle Active X)

Desenhe ele em qualquer lugar da planilha.

Clique sobre ele e no botão Propriedades.

Altere o nome dele para TempCombo.

Na guia Desenvolvedor clique em Visual Basic, clique duas vezes sobre a planilha aonde desenhou e irá criar a lista de autocompletar.

Cole o código seguinte:

Option Explicit

'Baseado em código da www.contextures.com
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        
    'Ocultar caixa de combinação e mover a próxima célula com Enter e Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'Nada
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet

    Set ws = ActiveSheet
    Set wsList = Sheets(Me.Name)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Application.CutCopyMode Then
      'Permite copiar e colar na planilha
      GoTo errHandler
    End If
    
    Set cboTemp = ws.OLEObjects("TempCombo")
      On Error Resume Next
      With cboTemp
        .Top = 10
        .Left = 10
        .Width = 0
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With
    
    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = str
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        
        'Abrir a lista suspensa automaticamente
        Me.TempCombo.DropDown
        End If

errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

End Sub

Este código irá fazer com que ao digitar sobre uma lista de validação que existir na planilha, automaticamente a tempCombo serja desejada sobre a célula e abra para que você possa digitar e ela ficará como autocompletar.

Ou seja, ao digitar ela vai completar e ao dar Enter o valor será digitado na célula.

Download planilha lista de validação no Excel – DropDown

Clique no botão abaixo para realizar o  download da planilha de TextoDivisão Excel, com exemplo de dados:

Baixe a planilha


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel