Validação de Dados no Excel – O Guia Completo
Se você desejar conhecer todos os recursos da ferramenta de Validação de Dados no Excel, então este post é para você!
Quando você compartilha uma planilha com outros usuários, muitas vezes você pode querer controlar a entrada de informações em células específicas a fim de evitar possíveis erros. Em outras situações, você também pode querer facilitar o trabalho de preencher células. E é justamente neste cenário que entra a ferramenta de Validação de Dados no Excel.
Se você precisa, por exemplo, permitir apenas determinados tipos de dados, como números ou datas em uma célula, ou limitar os números ou o texto a um determinado comprimento, então essa ferramenta é ideal para atender a sua necessidade.
Neste guia completo, mostraremos como fazer a validação de dados no Excel: criar uma regra de validação para números, datas ou valores de texto, fazer listas suspensas, copiar uma regra de validação, entre outras tarefas relacionadas. Confira!
Navegue pelo tópico desejado:
- O que é Validação de Dados no Excel?
- Como Inserir Validação de Dados?
- Exemplos de Validação de Dados no Excel
- Como editar a Validação de Dados no Excel
- Como copiar Validação de Dados para outras células
- Como encontrar células com Validação de Dados no Excel
- Como excluir Validação de Dados no Excel
- Dicas e Truques
- Quando a Validação de Dados não estiver funcionando
O que é Validação de Dados no Excel?
Basicamente, a Validação de Dados é um recurso do Excel, que permite restringir a entrada de dados do usuário em uma planilha. Ou seja, com ela, é possível criar regras para controlar o tipo de informação que pode ser inserida em uma determinada célula ou células.
É possível usar a validação para criar regras que permitam o preenchimento de células apenas com números, datas, texto, dentre outras situações como:
- Permitir que o usuário insira apenas números dentro de um intervalo específico.
- Permitir apenas entradas de dados que tenham um comprimento específico.
- Restringir datas e horas fora de um intervalo determinado.
- Restringir as entradas de informações que estejam fora da lista suspensa.
- Validar uma entrada com base em outra célula.
- Mostrar uma mensagem de entrada quando o usuário selecionar a célula.
- Mostrar mensagem de aviso quando forem inseridos dados incorretos em uma célula.
- Encontrar entradas incorretas em células que estejam validadas.
Como Inserir Validação de Dados?
Agora que já sabemos o que é a Validação de Dados no Excel, o próximo passo é saber como inseri-la, para isso basta seguir as etapas abaixo:
1. Abra a caixa de diálogo Validação de dados
Acesse a guia Dados > Validação de Dados. Se preferir, é possível abrir a caixa de diálogo Validação de Dados utilizando o atalho ALT > S > VA > V, teclando cada tecla separadamente (sempre com a tecla ALT pressionada).
2. Crie uma regra de Validação
Ao realizar o passo 1, a caixa de diálogo Validação de Dados irá abrir. Nela, você irá visualizar inicialmente a guia de configurações.
Nesta guia, você poderá definir os critérios de validação de acordo com a sua necessidade. Veja imagem abaixo:
Para exemplificar, vamos usar a validação para restringir para que os usuários insiram nas células selecionadas apenas valores de 1 a 50. Neste exemplo, basta usar a opção “Número inteiro” e inserir os valores desejados, conforme imagem abaixo:
Mais adiante você poderá conferir vários outros exemplos.
3. Insira uma mensagem de entrada.
Essa etapa é opcional. Se você deseja que o usuário seja avisado sobre o que ele deverá usar para preencher a célula, então basta selecionar a aba Mensagem de entrada e seguir as etapas:
- Certifique-se que a caixa “Mostrar mensagem de entrada ao selecionar célula” esteja habilitada. Por padrão, esta caixa já está selecionada.
- Insira o título e o texto da sua mensagem nos campos correspondentes.
- Clique em OK para finalizar.
Como resultado, ao selecionar a célula onde a validação foi realizada a mensagem será mostrada. Veja imagem:
4. Insira uma mensagem de erro
Essa etapa também é opcional. Caso deseje que o usuário seja avisado quando quando inserir dados que não são permitidos, basta selecionar a aba Alerta de erro.
Este recurso permite mostrar três tipos de mensagem de erro, conforme imagem abaixo:
Alerta de erro
Parar: Este alerta é o mais restrito, quando selecionado ele evita que usuários insiram dados inválidos.
Aviso: Este erro alerta os usuários que os dados inseridos são inválidos, mas não impede que sejam inseridos.
Informações: Este alerta, apenas informa que os dados inseridos são inválidos. Sendo assim, ainda permite que o valor seja inserido mesmo estando fora do critério de Validação de Dados.
Em todos os alertas de erro é possível configurar a mensagem conforme desejar.
Exemplos de Validação de Dados no Excel
1. Números inteiros e Decimais
Agora que você já conhece as configurações básicas do recurso de Validação de Dados, vamos aos exemplos práticos. Para o primeiro exemplo vamos usar uma tabela que contém os códigos de determinados produtos.
No campo de código desejamos que apenas números inteiros sejam permitidos. Neste caso, basta selecionar o intervalo que receberá a validação, acessar a caixa de Validação de Dados e escolher a opção “Número inteiro”:
Note que após escolher a opção Número inteiro, irá aparecer a opção Dados.
Para este exemplo, iremos colocar como critério que sejam inseridas nas células apenas números maiores que 10. Deste modo, escolhemos a opção “é maior que“, e inserimos 10.
Como resultado, teremos uma mensagem de erro caso seja inserido nas células selecionadas, valores menores do que 10.
Se precisar inserir números decimais, basta escolher a opção Decimal na lista de Validação de Dados e realizar o mesmo processo, de acordo com a necessidade. Veja o exemplo abaixo:
2. Lista
Imagine que você precisa preencher os campos de código de acordo com uma lista de valores. Neste caso, você irá usar a opção Lista.
Esta lista poderá ser de uma referência que esteja em outro local ou outra planilha da mesma pasta de trabalho ou pode ser digitada manualmente. Confira abaixo ambos os exemplos:
Digitar lista
Selecione o intervalo que irá receber a validação e acesse a guia Dados > Validação de Dados > Configurações > Permitir > Lista.
No campo “Fonte”, digite as informações que deverão ser usadas para preencher a coluna de códigos. Cada item da lista deve ser separado por ponto e vírgula. Veja o exemplo abaixo:
Para finalizar, clique em OK.
Como resultado, o Excel irá criar uma lista suspensa para o intervalo de células selecionadas.
Inserir lista de valores existente
Outra possibilidade para criar uma Lista de Validação é simplesmente selecionar um intervalo de células no campo Fonte.
Para isto, selecione o intervalo que irá receber a validação. Acesse a guia Dados > Validação de Dados > Configurações > Permitir > Lista.
No campo “Fonte”, clique no ícone no canto direito do campo e selecione o local onde está a sua lista de informações. Veja o exemplo abaixo:
3. Data e Hora
Se você precisa trabalhar com a Validação para Data e Hora, basta seguir as etapas:
Selecione a célula ou intervalo de células que irão receber a regra de validação.
Acesse a guia Dados > Validação de Dados > Configurações > Permitir > Data ou Hora
Em seguida, escolha critério a ser utilizado:
Para este exemplo, vamos vamos escolher datas que ocorram entre duas datas “1/01/2021” e “1/2/2021”. Você poderá digitar no campo ou selecionar ou simplesmente selecionar uma referência de célula que contém a data desejada.
Agora as células de A2 a A10 irão aceitar apenas datas que pertençam a estes dois critérios:
Para configurar a validação para Hora, o processo é o mesmo.
4. Comprimento do texto
Com a Validação de Dados também é possível restringir o tamanho do texto que será inserido nas células. Por exemplo, você precisa que na célula sejam inseridos apenas valores que tenham três caracteres, ou que sejam maior que determinado valor, entre outros critérios.
Neste exemplo, iremos usar a tabela com códigos e produtos e com a Validação de Dados vamos restringir os valores que serão digitados no campo código.
Para este cenário, os códigos precisam ter mais de 3 caracteres e menos que 7 caracteres. Não importando se são números ou letras.
Para isto, acesse a guia Dados > Validação de Dados > Configurações > Permitir > Comprimento do Texto. Escolha o parâmetro do critério e clique em OK.
Pronto! Agora as células de A2 a A10 só poderão receber valores com mais de 3 caracteres e menos que 7, caso contrário uma mensagem de erro irá aparecer.
5. Personalizado
A opção personalizado permite que sejam utilizadas fórmulas personalizadas para realizar as restrições de entradas nas células. A seguir você irá conhecer algumas restrições que são possíveis com a opção Personalizado.
Avisar quando meta for alcançada
É possível usar fórmulas para restringir e avisar quando uma meta estabelecida for alcançada. Por exemplo, suponha que você tenha uma previsão de gastos anual e pretende que o Excel avise quando você ultrapassar este valor.
Conforme for ocorrendo o preenchimento dos gastos mensais, no momento em que o valor anual atingir o teto que é de R$40.000,00, desejamos que apareça uma mensagem avisando que já foi gasto mais que o planejado. Para melhor compreensão confira a tabela abaixo:
Para que o usuário seja avisado quando o valor ultrapassar o teto acesse a guia Dados > Validação de Dados > Configurações > Permitir > Personalizado.
No campo “Fórmula” insira a seguinte fórmula:
=SOMA($B$2:$B$13)<$E$
Clique em OK para finalizar.
Com esta fórmula, a Validação de Dados irá entender que se a soma dos meses ultrapassar o teto de gastos, o valor será restringido, então uma mensagem de erro irá aparecer. Neste caso, configuramos para apenas aparecer um aviso. Veja o exemplo abaixo:
Permitir apenas números
Com a função ÉNUM é possível permitir que apenas números sejam inseridos nas células selecionadas. Para isso, selecione as células que irão receber a regra de validação. Em seguida, acesse a guia Dados > Validação de Dados > Configurações > Permitir > Personalizado.
No campo Fórmula, insira a função =ÉNUM(I2). Veja o exemplo abaixo:
Clique em OK para finalizar.
Agora, quando forem digitados dados diferentes de números a ação será bloqueada, confira:
Permitir apenas texto
Do mesmo modo, é possível permitir apenas valores no formato de texto. Neste caso, basta utilizar a função ÉTEXTO. Veja o exemplo abaixo:
Permitir texto começando com caracteres específicos
Caso todos os valores em um determinado intervalo devem começar com um caractere específico, faça a Validação de Dados usando a função CONT.SE, junto com um caractere curinga:
=CONT.SE (I2;”aa-*”)
Com esta função todos os códigos deverão começar com aa-, sem distinção de maiúsculas ou minúsculas. Caso contrário, uma mensagem de erro irá aparecer.
Permitir apenas entradas exclusivas e proibir duplicatas
Quando é necessário que em um determinado intervalo não tenham valores duplicados, basta configurar a regra de validação personalizada. Neste caso, use a função CONT.SE para identificar as duplicatas.
Para o exemplo, a função ficará da seguinte forma:
=CONT.SE (intervalo , célula superior ) <= 1, onde, o intervalo será o I2:I9 e a célula superior I2
Nesta função, quando um valor exclusivo é inserido, a fórmula retorna VERDADEIRO e a validação é bem-sucedida. Se o valor já existir no intervalo especificado (contagem maior que 1), então irá retornar FALSO e a entrada será restringida na validação.
Como editar a Validação de Dados no Excel
Quando há a necessidade de alterar alguma regra validação do Excel, basta executar as seguintes etapas:
1. Selecione as células validadas.
2. Abra a caixa de diálogo “Validação de dados”
3. Faça as alterações desejadas.
4. Marque na caixa de seleção “Aplicar alterações a todas as células com as mesmas configurações”.
5. Clique em OK para salvar as alterações.
Por exemplo, se você precisa editar sua lista de validação, vá até a caixa “Fonte”, insira ou remova a informação desejada, marque a caixa de diálogo “Aplicar alterações a todas as células com as mesmas configurações” e clique em OK para finalizar.
Como copiar Validação de Dados para outras células
Se você criou uma regra de Validação de Dados para uma célula e deseja validar outras células com os mesmos critérios, não é necessário recriar a regra do zero. Para copiar a regra para outras células, basta seguir as etapas:
1. Selecione a célula que contém a regra de validação e pressione Ctrl+C para copiá-la.
2. Selecione as células que deseja validar.
3. Clique com o botão direito na seleção, selecione Colar especial e selecione Validação no menu.
4. Clique em “OK“.
Como encontrar células com Validação de Dados no Excel
Para localizar rapidamente todas as células que contém regras de validação dentro da planilha atual, vá para a guia Página Inicial > Grupo de Edição e clique em Localizar e Selecionar > Validação de Dados:
Isso irá selecionará todas as células que possuem regras de validação.
Como excluir Validação de Dados no Excel
De modo geral, existem duas formas de remover a Validação de Dados no Excel. Confira cada uma delas abaixo:
Opção 1: Limpar Tudo
A maneira mais usada para remover a Validação de Dados da planilha, é seguindo as etapas:
1. Selecione a(s) célula(s) com regras de validação.
2. Na guia Dados , clique em Validação de dados.
3. Na guia Configurações , clique na em Limpar tudo e OK para finalizar.
Opção 2: Colar especial para excluir regras de validação de dados
Este método basicamente, copia uma célula sem validação e cola especial nas células onde a validação serão removidas. Confira o passo a passo abaixo:
1. Selecione uma célula vazia sem validação de dados e pressione Ctrl+C para copiá-la.
2. Selecione as células que contêm validação e que você deseja remover a regra de validação.
3. Aperte Ctrl+Alt e marque a opção Validação de dados.
4. Clique em OK para finalizar.
Dicas e Truques
Agora que você já sabe tudo sobre Validação de Dados no Excel, então chegou o momento de conhecer algumas dicas e truques extras que serão úteis na hora de usar essa ferramenta do Excel.
Validação de dados baseada em outra célula
Esta dica, já foi brevemente mencionada no exemplo 2. Ela consiste em usar a referência de célula ao invés de digitar valores na caixa de validação de dados.
Para inserir uma referência de célula, digite o sinal de igual e em seguida digite a célula que contém o valor desejado. Se preferir, clique na seta ao lado da caixa e selecione a célula usando o mouse.
Por exemplo, permitir que as células recebam apenas valores maiores que o valor presente na célula A1:
Validação de Dados com fórmulas
Em situações onde você precisa limitar a entrada de valores mínimo e máximo de acordo com uma lista já existente se torna mais viável usar fórmulas, como por exemplo:
Atenção: Lembre-se de bloquear o intervalo usando o sinal $, para que a regra de validação funcione corretamente em todas as células selecionadas.
Circular Dados Inválidos
Embora seja possível aplicar uma regra de validação e bloquear a inserção de valores inválidos, gerando a tela de erro, em certos casos, se não estiver selecionada a opção de alerta de erro, o Excel não irá notificar se alguns valores existentes não atenderem aos critérios de validação.
Para evitar este problema e localizar dados inválidos que foram inseridos na sua planilha, vá até a guia Dados e clique em Validação de Dados e selecione opção Circular Dados Inválidos.
Isso irá destacar todas as células que não atendem aos critérios de validação. No exemplo abaixo, a coluna C deve aceitar apenas valores que estejam entre 10 e 100. Sendo assim, ele irá destacar todos os valores que não se encaixam no critério. Confira:
Como proteger uma planilha com Validação de Dados
Para proteger a planilha com Validação de Dados, primeiro certifique-se que a Validação de Dados esteja configurada. Feito isso, desbloqueie as células que contêm validação e proteja a planilha com senha, permitindo que os usuários possam apenas selecionar células desbloqueadas.
Assim, o usuário poderá selecionar os campos com a validação, mas não irá poder alterar as configurações.
Quando a Validação de Dados não estiver funcionando
Caso a Validação de Dados não está funcionando corretamente em suas planilhas, você deverá verificar os seguintes pontos:
Validação de dados não visível no modo edição de célula
Quando você está inserindo algum valor na células com validação, ela não irá funcionar até que seja teclado “Enter”, ou até que você saia do modo edição da célula.
A Validação de Dados não pode ser aplicada a pasta de trabalho protegida ou compartilhada
Se a pasta de trabalho estiver protegida ou compartilhada, as regras de validação continuarão funcionando, por outro lado não será possível alterar ou visualizar as regras aplicadas.
Para que seja possível editar as validações, cancele o compartilhamento e/ou desproteja sua pasta de trabalho primeiro.
Fórmulas de Validação de Dados incorretas
Quando são usadas fórmulas para a validação é preciso verificar se a fórmula está retornando erros,
se a fórmula não faz referência a células vazias ou se são utilizadas referências de células apropriadas.
Recálculo manual está ativado
Se o modo de cálculo manual estiver ativo no Excel, as fórmulas não calculadas automaticamente e isso pode impedir que a validação de dados funcione conforme deveria. Para resolver o problema, é preciso alterar a opção de cálculo do Excel e deixá-lo automático. Neste caso, acesse a guia Fórmulas > grupo Cálculo > Opções de Cálculo > Automático.
Confira também as seguintes dicas:
- Lista Suspensa no Excel: 4 maneiras de criá-las facilmente
- Pesquisa de imagens no Excel com lista suspensa – Como criar?
- Lista suspensa com subcategorias: Como criar?
E então, o que achou do tutorial de hoje? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!