Conciliação bancária no Excel – Um guia prático
O Excel é uma ferramenta versátil que facilita tarefas do dia a dia, como a conciliação bancária, essencial para organizar suas finanças.
Neste guia, você aprenderá conciliação bancária no Excel, comparando uma planilha de contas a pagar com um extrato bancário do mesmo período.
Vamos começar?
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
O que você aprende neste vídeo
Resposta rápida: Nesta aula, o instrutor ensina a fazer conciliação bancária no Excel: comparar o extrato do banco com a planilha de contas a pagar/receber para achar o que está faltando. A técnica é criar uma chave (data + valor) em cada planilha e usar PROCV com SEERRO para marcar o que não bate como “Não conciliado”, destacando essas linhas em vermelho com formatação condicional.
Neste vídeo (13 min):
- 0:00 - O que é conciliação bancária: comparar o extrato do banco com as ferramentas de gestão (contas a pagar/receber).
- 2:00 - Criar a chave: juntar data + valor num texto único para identificar cada movimentação.
- 6:00 - Usar
PROCVpara procurar a chave na outra planilha e ver se a movimentação existe nas duas. - 7:00 - Envolver com
SEERROpara trocar o erro por um texto “Não conciliado”. - 8:00 - Formatação condicional: pintar a linha inteira de vermelho quando não está conciliada.
- 11:00 - Finalizar: ocultar a coluna da chave e entregar o relatório para a contabilidade.
O que você vai aprender:
A conciliação bancária no Excel consiste em comparar os lançamentos da sua planilha (contas a pagar ou receber) com o extrato bancário do mesmo período para confirmar que tudo bate. O caminho prático: criar uma chave de identificação em cada lançamento, usar a função PROCV para buscar correspondências, tratar o que não encontrar com SEERRO e destacar divergências com formatação condicional.
O que é conciliação bancária?
A conciliação bancária é o processo de comparar os registros financeiros da sua empresa (ou pessoais) com os registros do banco.
O objetivo é garantir que todas as transações estejam corretas e que não haja discrepâncias entre os dois lados. Por exemplo, você pode ter uma conta que foi paga, mas que ainda não foi registrada pelo banco, ou vice-versa.
Fazer essa conciliação manualmente pode ser demorado e propenso a erros, mas com o Excel, você pode automatizar grande parte desse processo.
Neste guia, vamos trabalhar com dois arquivos:
- Planilha de contas a pagar: Onde você registra todas as despesas que precisam ser quitadas.
- Extrato bancário: O documento fornecido pelo banco com todas as transações realizadas no período.
A ideia é comparar essas duas planilhas para identificar quais contas foram pagas, quais estão pendentes e se há alguma inconsistência.
Vamos começar?
Primeiro passo: criando chaves de identificação
Para comparar as duas planilhas de forma eficiente, precisamos de uma maneira de identificar cada transação de forma única. No mundo ideal, cada transação teria um número único, como um ID, mas na prática, nem sempre é assim. Por isso, vamos criar uma chave única para cada transação, combinando informações que ambas as planilhas têm em comum.
Aqui, vamos usar a data e o valor da transação para criar essa chave. A lógica é simples: é muito improvável que duas transações tenham exatamente a mesma data e o mesmo valor. Para garantir ainda mais precisão, podemos adicionar a hora da transação, caso ela esteja disponível.
- Adicione uma nova coluna na sua planilha de contas a pagar e nomeie-a como “Chave”.
- Na primeira célula dessa coluna, insira a seguinte fórmula para combinar a data e o valor da transação:
=B2&D2Essa fórmula converte a data para o formato de número inteiro e a combina com o valor da transação, separados por um hífen.
Faça a mesma coluna de chave na planilha de extrato bancário e pronto! Agora, ambas as planilhas têm uma coluna de chave única que nos permitirá comparar as transações de forma precisa.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Segundo passo: usando a fórmula PROCV
Agora que criamos as chaves, vamos verificar se as contas estão conciliadas. Na planilha de contas a pagar, crie uma nova coluna chamada “Conciliado”. Aqui, usaremos a fórmula PROCV para buscar as chaves na planilha do extrato bancário.
A fórmula básica é:
=PROCV(A2;[Extrato_Bancario_Final.xlsx]Extrato!$A:$A;1;0)No entanto, algumas células podem mostrar erros como #N/D. Isso significa que a transação não foi encontrada no extrato bancário, ou seja, não foi conciliada.
Vamos melhorar isso no próximo passo.
Terceiro passo: melhorando a fórmula com a função SEERRO
Para evitar os erros e deixar a planilha mais clara, vamos usar a função SEERRO. Ela substitui o erro por um texto mais visual e objetivo. Vamos usar “Não conciliado”. A fórmula ficará assim:
=SEERRO(PROCV(A2;[Extrato_Bancario_Final.xlsx]Extrato!$A:$A;1;0);"Não conciliado")Agora, em vez de aparecerem erros, as células sem correspondência mostrarão a mensagem, facilitando a identificação das contas pendentes.
Quarto passo: identificando as contas com formatação condicional
Para deixar a planilha ainda mais visual, vamos usar formatação condicional. Selecione toda a planilha de contas, vá em Formatação Condicional > Nova Regra > Usar uma fórmula para determinar quais células devem ser formatadas. No campo da fórmula, insira:
=$F$2="Não conciliado"Escolha um formato que chame atenção, como fundo vermelho com letras brancas.
Agora, todas as células com “Não conciliado” serão destacadas automaticamente, tornando mais fácil identificar as contas que precisam de atenção.
Ajustes finais
Agora que a conciliação bancária está funcionando perfeitamente, vamos dar os últimos retoques para deixar a planilha visualmente limpa e profissional. O objetivo é focar apenas nas informações relevantes, eliminando distrações e destacando o que realmente importa.
Primeiro, vamos ocultar a coluna de chave, que foi essencial para o processo de conciliação, mas agora não é mais necessária para o usuário final. Pra isso, clique com o botão direito no cabeçalho da coluna e selecione “Ocultar”. A coluna de chave desaparecerá, mas os dados continuarão intactos e funcionando nos bastidores.
Em seguida, para manter o foco nas contas não conciliadas, vamos “esconder” os valores conciliados pintando a coluna “Conciliado” de branco.
Selecione a coluna “Conciliado”, vá até a opção de cor da fonte na barra de ferramentas e escolha branco. Isso fará com que os valores conciliados fiquem invisíveis, enquanto apenas as palavras “Não conciliado” permanecerão visíveis, graças à formatação condicional que aplicamos anteriormente.
Por fim, sua planilha estará focada apenas no que importa: as colunas relevantes, com as palavras “Não conciliado” destacadas em vermelho (ou no formato que você escolheu).
Perguntas frequentes sobre conciliação bancária no Excel
1. O que é conciliação bancária?
É o processo de comparar os registros financeiros da empresa (planilha de contas) com o extrato do banco, para garantir que todos os lançamentos conferem. Ajuda a encontrar erros, esquecimentos e divergências entre o controle interno e o banco.
2. Como fazer conciliação bancária no Excel?
Monte uma coluna de chave única em cada lançamento, use PROCV para localizar cada item no extrato, trate ausências com SEERRO e aplique formatação condicional para destacar o que não bateu. Assim você identifica divergências rapidamente.
3. Qual fórmula usar para conciliação bancária?
A principal é o PROCV (ou PROCX), que busca cada lançamento no extrato. Combinada com SEERRO, evita erros quando não há correspondência, e a formatação condicional destaca visualmente as diferenças encontradas.
4. Por que criar uma chave de identificação?
Porque valores iguais podem se repetir; uma chave que combina data, valor e descrição torna cada lançamento único. Isso evita que o PROCV case com o item errado e deixa a conciliação muito mais confiável.
Conclusão
Neste guia, aprendemos a usar o Excel para conciliação bancária, criando chaves únicas, usando fórmulas como PROCV e SEERRO, e aplicando formatação condicional para destacar contas pendentes. Essas técnicas simplificam a organização financeira e mostram como o Excel pode automatizar tarefas.
Quer aprender mais? Confira nossos conteúdos de Excel do básico ao avançado para impressionar cada vez mais com suas planilhas!
Apostila Básica de Excel
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Hashtag Treinamentos
Para acessar outras publicações de Excel Intermediário, clique aqui!
Posts mais recentes de Excel Intermediário
- PROCV com SE: Como Combinar as Funções no Excel
Aprenda a combinar PROCV com SE no Excel para calcular bonificações por desempenho, usando SE aninhado e buscando o salário em outra planilha com o PROCV. - ChatGPT no Excel: use a IA direto na sua planilha
Aprenda a usar o ChatGPT no Excel para limpar dados, corrigir fórmulas e criar gráficos sozinho. Veja como instalar o suplemento oficial da OpenAI. - Série Tabela Dinâmica no Excel: Guia Completo
Domine a Série Tabela Dinâmica no Excel: aprenda a criar, atualizar, usar segmentação de dados, linha do tempo e montar dashboards do zero, passo a passo.
Posts mais recentes da Hashtag Treinamentos
- Planilha de Controle de Notas Fiscais no Excel [Grátis]
Baixe a planilha de controle de notas fiscais no Excel grátis: calcule ISS e retenções, veja o que está em aberto e gere a cobrança de cada nota de serviço. - Qual IA usar na empresa: ChatGPT, Claude ou Perplexity?
Qual IA usar na empresa: veja as diferenças entre ChatGPT, Claude e Perplexity e quando cada um rende mais em cada área da sua equipe. - Apostilas Gratuitas em PDF: Excel, Power BI, Python e IA
Baixe apostilas gratuitas em PDF de Excel, Power BI, Python, Claude e agentes de IA, com exercícios e gabarito. Escolha a sua e comece hoje.
