Uma lista de clientes com CPF digitado errado gera nota fiscal recusada, boleto devolvido e retrabalho. Dá para validar CPF no Excel com uma fórmula que confere os dois dígitos verificadores e mostra, na hora, quais números são impossíveis.
Neste guia você vai entender como os dígitos verificadores são calculados, montar a validação passo a passo em colunas auxiliares e, se usar o Microsoft 365, juntar tudo numa fórmula só. No final, uma regra de formatação condicional pinta de vermelho os CPFs inválidos.
Neste artigo (8 seções)
- O que a validação confere (e o que não confere)
- Como o CPF é formado
- Como os dígitos verificadores são calculados
- Passo a passo: validar CPF no Excel com colunas auxiliares
- Destacar os CPFs inválidos com formatação condicional
- Tudo numa fórmula só (Microsoft 365 e Excel 2021)
- Cuidados e limitações
- Erros comuns
O que a validação confere (e o que não confere)
A validação confere se o número pode existir: se tem 11 dígitos e se os dois últimos batem com o cálculo oficial feito a partir dos 9 primeiros. Ela pega erros de digitação, como um número trocado ou faltando.
Ela não confirma que o CPF está ativo nem a quem pertence. Para isso, só a consulta na Receita Federal. Todos os CPFs deste artigo são fictícios.
Como o CPF é formado
Os 9 primeiros dígitos são a base do cadastro. Os 2 últimos são dígitos verificadores, calculados a partir da base. Se alguém digitar um número errado, o cálculo não fecha.
Como os dígitos verificadores são calculados
Para o 1º dígito verificador:
- Multiplique cada um dos 9 primeiros dígitos por um peso, de 10 até 2.
- Some os 9 produtos.
- Multiplique a soma por 10 e pegue o resto da divisão por 11.
- Se o resto for 10, o dígito é 0. Caso contrário, o dígito é o próprio resto.
Para o 2º dígito verificador, repita o processo com os 10 primeiros dígitos (incluindo o 1º verificador) e pesos de 11 até 2. No exemplo, a soma dá 255 e o 2º dígito é 9. Por isso o CPF completo é 123.456.789-09.
Passo a passo: validar CPF no Excel com colunas auxiliares
Este método funciona em qualquer versão do Excel e deixa cada etapa visível, o que facilita conferir. Coloque os CPFs na coluna A, a partir de A2.
1. Limpar o CPF (coluna B)
Em B2, remova pontos e traço e garanta os 11 dígitos:
=TEXTO(SUBSTITUIR(SUBSTITUIR(A2;".";"");"-";"");"00000000000")
A função TEXTO com onze zeros recupera o zero à esquerda que o Excel apaga quando o CPF é digitado como número. Exemplo: o CPF 012.345.678-90 digitado sem pontuação vira 1234567890 na célula, e a coluna B devolve 01234567890.
2. Calcular o 1º dígito verificador (coluna C)
=MOD(MOD(SOMARPRODUTO(EXT.TEXTO(B2;{1;2;3;4;5;6;7;8;9};1)*{10;9;8;7;6;5;4;3;2})*10;11);10)
Como funciona, de dentro para fora:
EXT.TEXTO(B2;{1;2;...;9};1)separa os 9 primeiros dígitos.{10;9;...;2}são os pesos.SOMARPRODUTOmultiplica cada dígito pelo seu peso e soma tudo.- O primeiro
MOD(...*10;11)é o resto da divisão por 11. - O segundo
MOD(...;10)transforma o resto 10 em 0 e mantém os outros valores.
3. Calcular o 2º dígito verificador (coluna D)
=MOD(MOD(SOMARPRODUTO(EXT.TEXTO(B2;{1;2;3;4;5;6;7;8;9;10};1)*{11;10;9;8;7;6;5;4;3;2})*10;11);10)
Mesma lógica, agora com 10 dígitos e pesos de 11 a 2.
4. Conferir o CPF (coluna E)
=E(NÚM.CARACT(B2)=11; B2<>REPT(ESQUERDA(B2;1);11); C2=VALOR(EXT.TEXTO(B2;10;1)); D2=VALOR(EXT.TEXTO(B2;11;1)))
A fórmula devolve VERDADEIRO quando as quatro condições são atendidas:
- o CPF tem 11 dígitos;
- não é uma sequência repetida, como 111.111.111-11. Essas sequências passam no cálculo, mas não são CPFs válidos;
- o 10º dígito é igual ao 1º verificador calculado;
- o 11º dígito é igual ao 2º verificador calculado.
Para mostrar um texto em vez de VERDADEIRO/FALSO, use a função SE: =SE(E2; "Válido"; "Inválido").
Destacar os CPFs inválidos com formatação condicional
Selecione A2:E7 (começando em A2), vá em Página Inicial › Formatação Condicional › Nova Regra › Usar uma fórmula para determinar quais células devem ser formatadas e digite:
=E($A2<>""; $E2=FALSO)
Escolha um preenchimento vermelho. A condição $A2<>"" evita pintar linhas vazias. Se essa regra for nova para você, veja o guia completo de formatação condicional com fórmula.
Resultado com os CPFs fictícios de teste:
- 123.456.789-09, 111.444.777-35 e 987.654.321-00: válidos.
- 1234567890 (digitado como número): válido, porque a coluna B recupera o zero e o número vira 012.345.678-90.
- 123.456.789-00: inválido, porque os verificadores corretos seriam 09.
- 111.111.111-11: inválido, por ser sequência repetida.
Tudo numa fórmula só (Microsoft 365 e Excel 2021)
Se você usa o Microsoft 365 ou o Excel 2021, a função LET dá nome a cada etapa e dispensa as colunas auxiliares:
=LET(
c; TEXTO(SUBSTITUIR(SUBSTITUIR(A2;".";"");"-";"");"00000000000");
d1; MOD(MOD(SOMARPRODUTO(EXT.TEXTO(c;{1;2;3;4;5;6;7;8;9};1)*{10;9;8;7;6;5;4;3;2})*10;11);10);
d2; MOD(MOD(SOMARPRODUTO(EXT.TEXTO(c;{1;2;3;4;5;6;7;8;9;10};1)*{11;10;9;8;7;6;5;4;3;2})*10;11);10);
E(NÚM.CARACT(c)=11; c<>REPT(ESQUERDA(c;1);11); d1=VALOR(EXT.TEXTO(c;10;1)); d2=VALOR(EXT.TEXTO(c;11;1)))
)
Pode colar com as quebras de linha: o Excel aceita. O resultado é o mesmo das colunas B a E.
Cuidados e limitações
Guarde o CPF como texto
Formate a coluna A como Texto antes de digitar ou importar. Assim o zero à esquerda não se perde e o número não vira notação científica em planilhas grandes. Para exibir com pontos e traço, veja como formatar CPF e CNPJ no Excel.
O preenchimento com zeros pode mascarar um dígito faltando
Se alguém digitar só 10 dígitos por engano, a coluna B completa com um zero à esquerda. Na maioria dos casos o cálculo vai acusar erro, mas pode acontecer de o número completado ser válido por coincidência. Se a sua base guarda os CPFs como texto, troque a primeira condição da coluna E por NÚM.CARACT(SUBSTITUIR(SUBSTITUIR(A2;".";"");"-";""))=11, que exige os 11 dígitos digitados.
Espaços e outros caracteres
CPFs copiados de sistemas podem trazer espaços. Inclua mais um SUBSTITUIR(...;" ";"") na limpeza ou use ARRUMAR na coluna A.
Dados pessoais
CPF é dado pessoal protegido pela LGPD. Use CPFs fictícios em testes e modelos, e restrinja o acesso às planilhas com dados reais.
Erros comuns
A fórmula devolve #VALOR!
Há uma letra ou outro caractere no CPF. Confira a coluna B: ela deve ter só números.
Todos os CPFs aparecem como inválidos
Confira se as constantes de matriz foram digitadas com ponto e vírgula, como {1;2;3}. No Excel em português, esse é o separador de linhas da matriz. Se a sua planilha usar outro separador, ajuste nas duas fórmulas.
O Excel recusa a fórmula com LET
Sua versão não tem a função LET (Excel 2019 ou anterior). Use o método com colunas auxiliares.
No VBA, o blog também tem como aplicar máscara de CPF e CNPJ em TextBox. Ficou com alguma dúvida? Deixe nos comentários.
