Como Destacar Duplicatas no Excel: Um Guia Completo Passo a Passo

Método 1: Crie uma Lista Suspensa Simples a partir de uma Lista Digitada

Melhor para: Listas curtas e fixas que raramente mudam.

Para criar uma lista suspensa no Excel, selecione a célula onde deseja a lista, vá para a guia dados, clique em validação de dados, escolha Lista na caixa permitir e especifique o intervalo de origem ou insira manualmente os itens separados por vírgulas. É possível digitar itens manualmente ou referenciar um intervalo de células para listas suspensas no Excel.

Passos:

1. Selecione a célula ou intervalo de células onde deseja a lista suspensa.
2. Clique na guia dados na faixa de opções.
3. No grupo de ferramentas de dados, clique em validação de dados. A caixa de diálogo de validação de dados se abre.
4. Na caixa permitir, selecione Lista no menu suspenso.
5. A caixa de verificação de lista suspensa na célula aparece. A opção de lista suspensa na célula deve ser marcada para habilitar um menu suspenso em uma célula.
6. Clique na caixa de origem e digite suas opções como uma lista separada por vírgulas, por exemplo: Eletrônicos,Mobiliário,Software,Serviços
7. Clique em OK.

Uma pequena seta agora aparece na célula. Clicar nela abre o menu suspenso mostrando todas as opções suspensas. Os usuários podem copiar uma célula com uma lista suspensa e colá-la em outro lugar, mantendo a funcionalidade de lista suspensa, o que é uma maneira rápida de aplicar a mesma lista a outras células.

Evite espaços após vírgulas na lista separada por vírgulas a menos que você queira um espaço inicial no rótulo. "América do Norte, EMEA" mostrará " EMEA" com um espaço inicial.

Sugestão de captura de tela: Mostre a caixa de diálogo de validação de dados com a aba de configurações ativa, Lista na caixa permitir e uma lista separada por vírgulas na caixa de origem. A célula de destino deve ser selecionada em segundo plano usando o arquivo demo do formulário de pedido.

Método 2: Crie uma Lista Suspensa a partir de um Intervalo de Células

Melhor para: Listas com muitos itens, ou quando os mesmos dados de origem são reutilizados em várias células.

Referenciar um intervalo de células como dados de origem é mais flexível do que digitar itens. Quando você atualiza o intervalo de origem, as opções de lista suspensa em todas as células conectadas são atualizadas também.

1. Insira seus itens de lista em uma coluna, idealmente em uma planilha separada (por exemplo, uma planilha chamada "Listas").
2. Selecione a célula onde deseja a lista suspensa.
3. Vá para guia de dados > grupo de ferramentas de dados > selecione validação de dados.
4. Na janela de validação de dados, na aba de configurações, selecione Lista na caixa permitir.
5. Clique na caixa de origem e selecione o intervalo de células na sua planilha de listas. O Excel entra na referência de intervalo automaticamente, por exemplo =Listas!$A$3:$A$7.
6. Clique em OK.

Os critérios de validação agora restringem as entradas a apenas dados válidos desse intervalo de origem. A caixa de nome no canto superior esquerdo mostra o endereço da célula atual e ajuda a verificar se a referência correta está selecionada.

Sempre use referências absolutas com sinais de $ no intervalo de origem. Usar =A3:A7 sem sinais de dólar pode deslocar se linhas forem inseridas acima.

Sugestão de captura de tela: Mostre a caixa de diálogo de validação de dados com uma referência de intervalo na caixa de origem e a guia da planilha de Listas visível na parte inferior. Use o arquivo demo do formulário de pedido.

Método 3: Use um Intervalo Nomeado como Origem

Melhor para: Listas suspensas usadas em várias localizações, ou quando os dados de origem estão em uma planilha diferente.

Um intervalo nomeado atribui um nome memorável a um grupo de células, que você pode então usar na caixa de origem em vez de um endereço de célula.

1. Selecione seus itens de lista, depois clique na caixa de nome e digite um nome (por exemplo, CategoriasProdutos), depois pressione enter.
2. Selecione a célula onde deseja a lista suspensa, vá para guia de dados > validação de dados.
3. Na aba de configurações, selecione Lista na caixa permitir, depois digite =CategoriasProdutos na caixa de origem.
4. Clique em OK.

Isso torna a lista de validação mais fácil de gerenciar. Intervalos nomeados também permitem listas suspensas dependentes quando combinados com a função INDIRECT (coberto no Método 5).

Sugestão de captura de tela: Mostre a caixa de nome com um nome de intervalo inserido, e a caixa de origem de validação de dados mostrando =CategoriasProdutos.

Método 4: Adicione Mensagens de Entrada e Alertas de Erro

Melhor para: Planilhas compartilhadas ou formulários preenchidos por outras pessoas.

Os usuários podem adicionar mensagens de entrada para exibir quando um usuário clica na célula com uma lista suspensa para orientá-los sobre o que selecionar. O Excel permite a configuração de mensagens de erro personalizadas dentro da caixa de diálogo de Validação de Dados.

Mensagem de entrada:

1. Abra a guia de dados > validação de dados.
2. Clique na aba de mensagem de entrada na caixa de diálogo de validação de dados.
3. Marque Mostrar mensagem de entrada quando a célula estiver selecionada e digite um título mais uma mensagem nos campos abaixo.
4. Clique em OK.

Quando alguém clica nessa célula, a mensagem de entrada aparece como uma dica orientando a seleção.

Alerta de erro:

1. Clique na aba de alerta de erro na caixa de diálogo de validação de dados.
2. Marque a caixa de verificação de mostrar alerta de erro (também rotulado como alerta de erro após dados inválidos serem inseridos, ou alerta após dados inválidos).
3. Escolha um Estilo: Parar bloqueia dados inválidos, Aviso permite com um prompt, Informação mostra apenas uma nota.
4. Insira um título e uma mensagem de erro.
5. Clique em OK.

Quando dados inválidos são inseridos fora da lista permitida, o alerta de erro é acionado. Para formulários rigorosos, use Parar para que apenas dados válidos possam ser aceitos. A configuração de mostrar alerta de erro e a aba de mensagem de entrada são controladas independentemente, portanto você pode habilitar uma sem a outra.

Se você deseja permitir células em branco, marque a caixa de ignorar em branco na aba de configurações. Isso impede que o alerta de erro dispare em células vazias.

Sugestão de captura de tela: Mostre a aba de alerta de erro da caixa de diálogo de validação de dados com Parar selecionado e uma mensagem de erro personalizada digitada. Use o arquivo demo de formulário de RH.

Método 5: Construa Listas Suspensas Dependentes

Melhor para: Seleção em vários níveis onde a segunda lista depende da primeira.

Listas suspensas dependentes no Excel vinculam duas ou mais listas suspensas juntas, onde a seleção na primeira lista controla as opções disponíveis na segunda lista. Por exemplo, se um usuário selecionar Pizza da primeira lista suspensa, a segunda lista suspensa pode ser preenchida com itens de pizza específicos, enquanto selecionar Chinês mostraria pratos chineses na segunda lista. Criar listas suspensas dependentes pode melhorar a eficiência e precisão da entrada de dados, garantindo que os usuários vejam apenas as opções relevantes com base em suas seleções anteriores.

Configuração:

1. Crie seus dados de origem em colunas, uma por categoria. A linha de cabeçalho de cada coluna deve corresponder exatamente ao que aparece na primeira lista suspensa.
2. Nomeie cada coluna de itens usando a caixa de nome, correspondendo exatamente à primeira opção da lista (por exemplo, nomeie a coluna Pizza "Pizza", a coluna Chinês "Chinês").
3. Crie a primeira lista suspensa na coluna A usando o Método 1 ou Método 2.
4. Selecione a célula para a segunda lista suspensa (coluna B). Vá para guia de dados > selecione lista de validação de dados na aba de configurações, escolha Lista na caixa permitir, e na caixa de origem digite =INDIRECT(A4).
5. Clique em OK.

Quando um valor é escolhido na primeira lista suspensa, INDIRECT busca o intervalo nomeado com o mesmo nome e o usa como a lista de seleção para a segunda lista suspensa. Os critérios de validação para a segunda célula atualizam-se dinamicamente com base na primeira seleção.

Sugestão de captura de tela: Mostre o arquivo demo de lista suspensa dependente com Pizza selecionado na coluna A e os itens do menu de pizza visíveis na lista suspensa da coluna B. A planilha de Listas de Menu deve estar visível em segundo plano.

Método 6: Crie uma Lista Suspensa Dinâmica

Melhor para: Listas que crescem com o tempo e precisam atualizar automaticamente quando novos itens são adicionados.

Você pode criar uma lista suspensa dinâmica no Excel usando a função OFFSET, que permite que a lista atualize automaticamente à medida que novos itens são adicionados ao intervalo de origem. Usar uma tabela do Excel como fonte para uma lista suspensa permite que ela se expanda automaticamente quando novas linhas são adicionadas, tornando-a uma ótima opção para listas dinâmicas.

Opção A: Tabela do Excel

1. Selecione seus itens de origem e pressione Ctrl + T (na guia inserir) para criar uma tabela do Excel. Escolha um estilo de tabela e confirme a opção de linha de cabeçalho.
2. Referencie a coluna da tabela na caixa de origem de validação de dados: =Tabela1[Categoria].

Quando uma nova linha é adicionada à tabela do Excel, as opções de lista suspensa atualizam automaticamente sem necessidade de mudanças na regra de validação.

Opção B: fórmula OFFSET

Para criar uma lista suspensa dinâmica, você pode usar a fórmula =OFFSET(Planilha2!$A$1,0,0,CONT(Planilha2!$A:$A),1), que ajusta o intervalo com base no número de células não vazias na coluna de origem.

Insira isso na caixa de origem da janela de validação de dados. À medida que os itens são adicionados à coluna de origem, o intervalo de origem se expande e as opções de lista suspensa atualizam-se automaticamente.

Sugestão de captura de tela: Mostre a caixa de origem de validação de dados com a fórmula OFFSET inserida e a planilha de Listas visível. Use o arquivo demo do formulário de pedido.

Método 7: Adicionar, Editar e Remover Itens de Lista Suspensa

Gerenciando itens no intervalo de origem:

Para adicionar um item a uma lista suspensa existente, vá para a planilha original contendo a lista, clique com o botão direito na célula abaixo de onde deseja inserir, selecione Inserir, escolha mover células para baixo e insira o novo item. As opções de lista suspensa são atualizadas automaticamente. Na mesma planilha ou entre planilhas, os mesmos passos se aplicam desde que o intervalo de origem cubra a nova célula.

Removendo a lista suspensa:

Para remover uma lista suspensa no Excel, selecione a célula com a lista, vá para a guia de dados, clique em validação de dados e, em seguida, clique em apagar (Limpar Tudo) para remover a lista. Para limpar apenas o valor da célula sem remover a regra de validação, selecione a célula e pressione delete. Para remover completamente a regra, use Limpar Tudo na janela de validação de dados.

Observe que se você quiser pressionar delete apenas no conteúdo da célula e manter a validação, você pode fazê-lo com segurança. A lista de validação permanece na célula mesmo após o conteúdo ser apagado.

Protegendo com senha células validadas:

Para impedir que outros editem sua configuração de lista suspensa, vá para Revisar > Proteger Planilha e insira uma senha (proteja a planilha com senha). Os usuários ainda podem usar as listas suspensas para inserir dados, mas não podem modificar os critérios de validação ou remover as regras.

Sugestão de captura de tela: Mostre a caixa de diálogo de validação de dados com o botão Limpar Tudo visível, pronto para remover a regra. Mostre também a caixa de diálogo Proteger Planilha com o campo de senha ativo.

Problemas Comuns e Solução de Problemas

A seta da lista suspensa não aparece

Confirme que a caixa de verificação de lista suspensa na célula está marcada na aba de configurações. Se estiver desmarcada, a validação ainda funciona, mas nenhuma seta aparece na célula. Este é o ajuste mais comumente esquecido quando a lista suspensa parece ter desaparecido.

O alerta de erro dispara em dados válidos

Isso geralmente é um problema de incompatibilidade de formatação. Verifique a caixa de origem para espaços indesejados ao redor das vírgulas na lista separada por vírgulas, ou inspecione os dados de origem para células vazias ou capitalização inconsistente. Os critérios de validação comparam valores exatamente.

A lista dinâmica não atualiza automaticamente

Se a origem for um intervalo de células estático, novos itens adicionados fora desse intervalo não aparecerão. Alterne para uma tabela do Excel ou a fórmula OFFSET para que o intervalo de origem se expanda automaticamente. A lista de validação de dados deve referenciar um intervalo que inclua todos os itens atuais e futuros.

Lista dependente mostra um erro de referência

Isso significa que o intervalo nomeado não corresponde exatamente à seleção do primeiro item da lista suspensa. Abra Fórmulas > Gerenciador de Nomes e confirme que os nomes correspondem exatamente às opções na primeira lista suspensa, incluindo a capitalização. Qualquer discrepância faz com que INDIRECT retorne um vazio ou erro em todas as células que usam a regra dependente.

Referência Rápida: Métodos para Lista Suspensa

Para Desenvolvedores: Adicionar Validação de Dados com IronXL

Se sua aplicação .NET gera planilhas Excel com formulários ou modelos de entrada de dados, o IronXL permite aplicar listas suspensas de validação de dados programaticamente em C# sem o Microsoft Office no servidor. A abordagem recomendada é AddFormulaListRule(), que faz referência a um intervalo de células como origem e evita o limite de 255 caracteres que se aplica ao passar itens de lista como uma string diretamente.

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim formSheet As WorkSheet = workBook.DefaultWorkSheet
formSheet.Name = "Order Form"

' Headers
formSheet("A1").Value = "Order ID"
formSheet("B1").Value = "Product Category"
formSheet("C1").Value = "Region"

' Write source list values to a Lists sheet
Dim listSheet As WorkSheet = workBook.CreateWorkSheet("Lists")
Dim categories As String() = {"Electronics", "Furniture", "Software", "Office Supplies", "Services"}
Dim regions As String() = {"North America", "EMEA", "APAC", "Latin America", "Middle East"}

For i As Integer = 0 To categories.Length - 1
    listSheet($"A{i + 1}").Value = categories(i)
    listSheet($"B{i + 1}").Value = regions(i)
Next

' Apply drop-down validation referencing the Lists sheet source range
Dim categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           ' cell range to validate
    "Lists!$A$1:$A$5"   ' source range on Lists sheet
)
categoryRule.ShowErrorBox = True
categoryRule.ErrorBoxTitle = "Invalid Category"
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list."
categoryRule.ShowPromptBox = True
categoryRule.PromptBoxTitle = "Product Category"
categoryRule.PromptBoxText = "Choose a product category from the drop-down."

Dim regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
)
regionRule.ShowErrorBox = True
regionRule.ErrorBoxTitle = "Invalid Region"
regionRule.ErrorBoxText = "Select a valid region from the drop-down list."

workBook.SaveAs("order-form-validated.xlsx")

IronXL roda no .NET 6 e posterior, compatível com Windows, Linux, macOS, Docker e Azure. Para detalhes completos, veja a referência da API DataValidation.

Primeiros passos: Instale via NuGet com Install-Package IronXL.Excel. Uma avaliação gratuita está disponível com 30 dias de funcionalidade completa e sem necessidade de cartão de crédito.

Leitura adicional:

• IronXL DataValidation API reference
• Escrever arquivos Excel em .NET com IronXL
• Guia de início do IronXL

Concluindo

Inserir uma lista suspensa em células do Excel leva menos de um minuto uma vez que você sabe onde ir. Para uma lista suspensa rápida na célula com algumas escolhas fixas, digitar uma lista separada por vírgulas diretamente na caixa de origem na caixa de diálogo de validação de dados é o caminho mais rápido. Para qualquer coisa que precise escalar ou ser atualizada ao longo do tempo, fazer referência a um intervalo nomeado ou a uma tabela do Excel dá às opções da lista suspensa espaço para crescer. A lista de validação de dados permanece conectada aos seus dados de origem, então adicionar um novo item ao intervalo de origem automaticamente o torna disponível em todas as células conectadas.

Quando várias pessoas preenchem a mesma planilha, a mensagem de entrada na aba de mensagem de entrada as orienta antes de fazerem uma seleção, e um alerta de erro de nível Parar na aba de alerta de erro garante que dados inválidos inseridos nunca caiam em uma célula validada. Listas suspensas dependentes construídas com INDIRECT e intervalos nomeados permitem que a primeira lista suspensa controle a segunda automaticamente, mantendo o conjunto de opções relevante sem trabalho extra por parte do usuário.

Para desenvolvedores que precisam gerar arquivos Excel validados a partir de aplicações .NET, o IronXL cobre todo o fluxo de trabalho de validação de dados: intervalos de origem, alertas de erro, prompts de entrada e regras de lista de fórmulas, tudo sem o Office instalado no servidor. Comece com um teste gratuito para testar a validação de dados no seu próprio projeto.

Tem um cenário de lista suspensa que este guia não cobriu? Deixe um comentário abaixo, ou visite o blog do Iron Software para mais guias sobre entrada de dados no Excel, validação e automação de planilhas.