Ir para o conteúdo

Como fazer a conciliação bancária no Excel

Coloque o extrato e o razão da conta em planilhas separadas do Excel, case as linhas pelo valor com CONT.SES e classifique cada linha que não casou. Do lado do banco, some os depósitos em trânsito e subtraia os cheques não compensados; do lado do razão, lance tarifas, rendimentos e correções. A conciliação termina quando os dois saldos ajustados são iguais.

Atualizado em

O que você precisa antes de começar

Você precisa de três coisas do mês que vai conciliar:

  • O extrato como planilha. Se você só tem o PDF, converta o extrato para Excel. Os arquivos Excel e CSV do BankConv têm as colunas Date, Description, Reference, Amount e Balance.
  • O razão da conta bancária, exportado do seu sistema contábil para o Excel.
  • A conciliação do mês anterior, por causa dos depósitos em trânsito e dos cheques não compensados que continuavam em aberto.

Confira o extrato convertido antes de casar qualquer coisa: o saldo inicial mais todos os valores deve dar o saldo final do PDF. Quando o BankConv consegue compará-los, a página de resultados diz se os saldos batem ou avisa que não batem.

Alguns extratos imprimem cheques e tarifas em tabelas próprias, separadas das outras transações. O BankConv só junta essas tabelas à lista de transações quando o resultado bate, centavo por centavo, com os saldos impressos no extrato. Se um extrato ainda não fecha, procure primeiro essas tabelas.

Página de resultados do BankConv com a conferência de saldos, as transações e o menu de download aberto em Excel (.xlsx)
Confira o resultado dos saldos e depois baixe o arquivo Excel (dados de exemplo, interface em inglês).

Passo 1: coloque cada lado em sua própria planilha

Crie uma pasta de trabalho com três planilhas chamadas Banco, Razao e Conciliacao (sem acento, para simplificar as fórmulas). Cole o extrato em Banco e o razão da conta em Razao, com os cabeçalhos na linha 1 e o valor na coluna D das duas planilhas.

As duas planilhas precisam usar o mesmo sinal: o dinheiro que entra na conta é positivo e o que sai é negativo. A contabilidade costuma mostrar o contrário do banco, porque um depósito é um crédito no extrato e um débito no seu razão. Se a sua exportação tem colunas separadas de débito e crédito, acrescente antes uma única coluna de valor com sinal.

Este exemplo, com dados fictícios, acompanha o resto do guia. Ele cobre outubro de 2026, e os dois lados abriram com R$ 8.000,00. Ref. é a coluna Reference.

Planilha Banco

Data Descrição Ref. Valor Saldo
02/10 PIX recebido NF 1041 2.400,00 10.400,00
05/10 Cheque compensado 1203 1203 -650,00 9.750,00
09/10 PIX enviado aluguel -1.800,00 7.950,00
14/10 Liquidação boleto NF 1042 1.150,00 9.100,00
16/10 Compra cartão débito posto -45,00 9.055,00
16/10 Compra cartão débito posto -45,00 9.010,00
21/10 Cheque compensado 1205 1205 -540,00 8.470,00
30/10 Tarifa pacote de serviços -25,00 8.445,00
31/10 Rendimento da conta 3,20 8.448,20

Planilha Razao

Data Descrição Ref. Valor
02/10 Recebimento NF 1041 2.400,00
03/10 Cheque 1203 1203 -650,00
09/10 Aluguel de outubro -1.800,00
14/10 Recebimento NF 1042 1.150,00
16/10 Combustível -45,00
16/10 Combustível -45,00
19/10 Cheque 1205 1205 -450,00
29/10 Cheque 1207 1207 -320,00
31/10 Recebimento NF 1043 1.275,00

O razão fecha em R$ 9.515,00 e o extrato em R$ 8.448,20.

Passo 2: case as linhas com CONT.SES

Case pelo valor, não pela data. Um cheque é lançado no dia em que é emitido e aparece no extrato dias depois, então as datas raramente coincidem.

Casar só pelo valor tem uma falha: se você lançou um único gasto de R$ 45,00 com combustível e o banco mostra dois, as duas linhas do banco pareceriam casadas. Para evitar isso, numere cada repetição de um valor e case o valor e o número juntos.

Na planilha Banco, digite isto na linha 2 e arraste para baixo. A coluna F é Nº e a coluna G é Casou.

=CONT.SE($D$2:D2;D2)
=SE(CONT.SES(Razao!$D:$D;D2;Razao!$E:$E;F2)>0;"Sim";"Não")

Na planilha Razao, faça o mesmo. A coluna E é Nº e a coluna F é Casou.

=CONT.SE($D$2:D2;D2)
=SE(CONT.SES(Banco!$D:$D;D2;Banco!$F:$F;E2)>0;"Sim";"Não")

Agora o primeiro R$ 45,00 de cada planilha casa com o primeiro da outra, e o segundo com o segundo. Filtre as duas planilhas em Não. No exemplo sobram três linhas de cada lado:

Planilha Linha sem par Valor
Banco Cheque compensado 1205 -540,00
Banco Tarifa pacote de serviços -25,00
Banco Rendimento da conta 3,20
Razao Cheque 1205 -450,00
Razao Cheque 1207 -320,00
Razao Recebimento NF 1043 1.275,00

Casar pelo valor pode juntar as linhas erradas quando dois cheques têm o mesmo valor. O total continua certo, mas você pode anotar como pendente o cheque errado, então compare os números de cheque na coluna Reference.

Passo 3: diga por que cada linha não casou

Acrescente uma coluna Tipo: a coluna H em Banco e a coluna G em Razao. Dê a cada linha sem par uma destas classificações.

Tipo Em qual planilha O que significa Exemplo acima
Depósito em trânsito Razao Você lançou; o banco ainda não NF 1043, recebida no último dia do mês
Cheque não compensado Razao Você emitiu; ainda não foi compensado Cheque 1207
Só banco Banco O banco lançou; você ainda não Tarifa e rendimento
Erro As duas A mesma transação com dois valores diferentes Cheque 1205

Confirme cada classificação com algo concreto. Um depósito em trânsito deve aparecer no extrato seguinte em poucos dias, e um cheque deve bater com o seu talão.

O cheque 1205 é exatamente o tipo de erro que esse processo existe para pegar: R$ 540,00 no extrato, R$ 450,00 no razão. A diferença de R$ 90,00 é divisível por 9, o sinal clássico de dois dígitos invertidos.

Passo 4: monte o modelo de conciliação

Na planilha Conciliacao, digite os dois saldos, o do extrato e o do seu razão, e use as fórmulas abaixo. Coloque o nome de cada linha na coluna A e os valores na coluna B, nas linhas indicadas.

Linha Item Coluna B Exemplo
1 Saldo conforme extrato Digite do PDF 8.448,20
2 Mais: depósitos em trânsito Fórmula A 1.275,00
3 Menos: cheques não compensados Fórmula B -320,00
4 Saldo bancário ajustado =SOMA(B1:B3) 9.403,20
5 Saldo conforme razão Digite da sua contabilidade 9.515,00
6 Lançamentos só do banco Fórmula C -21,80
7 Correções Fórmula D -90,00
8 Saldo contábil ajustado =SOMA(B5:B7) 9.403,20
9 Diferença =ARRED(B4-B8;2) 0,00

Fórmula A, depósitos em trânsito:

=SOMASES(Razao!$D:$D;Razao!$G:$G;"Depósito em trânsito";Razao!$F:$F;"Não")

Fórmula B, cheques não compensados:

=SOMASES(Razao!$D:$D;Razao!$G:$G;"Cheque não compensado";Razao!$F:$F;"Não")

Fórmula C, lançamentos só do banco:

=SOMASES(Banco!$D:$D;Banco!$H:$H;"Só banco")

Fórmula D, correções:

=SOMASES(Banco!$D:$D;Banco!$H:$H;"Erro")-SOMASES(Razao!$D:$D;Razao!$G:$G;"Erro")

ARRED elimina o pequeno resíduo decimal que o Excel pode deixar ao somar centavos, que de outro modo mostraria uma diferença que não existe.

Se o erro foi do banco e não seu, acrescente uma linha do lado do banco e informe o banco.

Não pegue um atalho no passo 3

É tentador pular a classificação e somar todas as linhas sem par de cada planilha ao saldo do outro lado. Essa diferença sempre dá zero quando os dois lados abriram com o mesmo saldo, mesmo com erros nos dados, porque cada linha simplesmente foi contada dos dois lados.

É a classificação que faz a conferência funcionar. No exemplo, deixe o cheque 1205 sem classificação e a linha Diferença mostra -90,00, que leva direto ao problema. O atalho de somar tudo o que não casou mostra 0,00 e esconde o erro.

Se a diferença não some

O que você vê Causa provável O que fazer
A diferença é divisível por 9 Dígitos invertidos, como 540 e 450 Compare os dígitos das linhas sem par
A diferença é o dobro do valor de uma linha Uma linha está com o sinal trocado Procure uma linha sem par que valha metade da diferença
A diferença é igual ao valor de uma linha Uma linha está sem classificação, ou classificada de um lado só Filtre a coluna Tipo por vazios entre as linhas Não
A diferença é igual a uma pendência do mês anterior Uma pendência não foi levada para este mês Acrescente-a à planilha Razao
Aparece uma diferença pela primeira vez neste mês, com linhas limpas Os saldos iniciais não batem com o mês anterior Compare com o saldo ajustado do mês anterior
As linhas do extrato não somam o saldo final Falta uma linha ou uma página nos dados do extrato Corrija a planilha Banco antes de conciliar

Feche o mês

Uma diferença zero significa que os dois registros batem, mas o seu razão ainda não está pronto.

  1. Faça os lançamentos do lado do razão. No exemplo, lance a tarifa de R$ 25,00 e o rendimento de R$ 3,20, e corrija o cheque 1205 em R$ 90,00. Sua contabilidade passa a mostrar R$ 9.403,20, o saldo ajustado.
  2. Leve as pendências adiante. Copie a NF 1043 e o cheque 1207 para o início da planilha Razao de novembro, com a classificação de Tipo, para que possam casar quando forem compensados. As fórmulas A e B só os contam enquanto Casou for Não. Digite o saldo do razão de novembro a partir da sua contabilidade em vez de somar a planilha, porque essas duas linhas já estão incluídas nele.
  3. Acompanhe as pendências antigas. Um cheque que continua em aberto depois de alguns meses pode ter sido perdido ou nunca depositado. Pergunte ao favorecido em vez de levá-lo adiante para sempre.
  4. Guarde a pasta de trabalho. Mantenha a conciliação junto com o extrato como a trilha de auditoria do mês.

Para colocar vários meses em dia, concilie um por um, cada um com as suas planilhas Banco e Razao e as pendências do mês anterior. Filtrar por mês uma única tabela combinada não funciona, porque CONT.SE, CONT.SES e SOMASES também contam as linhas ocultas. Se você usa um sistema contábil, ele pode fazer o casamento depois que o extrato for importado; o Omie concilia a partir de um extrato em OFX, e a Conta Azul, a partir do OFX do banco ou da planilha de lançamentos.

Fontes: Suporte da Microsoft: função CONT.SES · Suporte da Microsoft: função CONT.SE · Suporte da Microsoft: função SOMASES · AccountingCoach: Bank reconciliation (em inglês)

Perguntas frequentes

Qual é o modelo de uma conciliação bancária no Excel?

Dois blocos que terminam no mesmo número. O lado do banco parte do saldo final do extrato, soma os depósitos em trânsito e subtrai os cheques não compensados. O lado do razão parte do saldo da sua contabilidade e lança tarifas, rendimentos e correções. Uma linha de diferença embaixo deve mostrar 0,00.

O Excel faz a conciliação bancária automaticamente?

Em parte. CONT.SES consegue marcar quais linhas casam, mas você ainda decide por que cada linha restante não casou. Essa decisão é a conciliação, e uma fórmula não distingue um cheque ainda não compensado de um cheque lançado errado.

Por que minha conciliação bancária não fecha?

Normalmente falta uma linha, há uma linha duas vezes ou um valor ou sinal está errado. Uma diferença divisível por 9 costuma indicar dígitos invertidos. Uma diferença igual ao dobro de um valor costuma indicar um sinal trocado. Confira também se as pendências do mês anterior foram levadas para este mês.

O que faço com os cheques não compensados no mês seguinte?

Copie-os para o início da planilha Razao do mês seguinte, para que possam casar quando forem compensados. Eles já estão incluídos no saldo do seu razão, então não os conte duas vezes nesse saldo.

Funciona no Google Sheets?

Sim. CONT.SE, CONT.SES, SOMASES e ARRED funcionam do mesmo jeito no Google Sheets em português, então você pode usar o modelo e as fórmulas deste guia como estão.