Conciliación bancaria en Excel: cómo montarla paso a paso
Si tu programa contable no concilia, o quieres revisar una cuenta concreta, Excel basta. Necesitas dos listados (los movimientos del banco y el mayor de la 572), unas pocas fórmulas para emparejarlos y un estado de conciliación al final. Así se monta.
1. Prepara la hoja «Banco»
Pon los movimientos del extracto con estas columnas, desde la fila 2:
| Columna | Contenido |
|---|---|
| A | Fecha |
| B | Concepto |
| C | Importe (abonos en positivo, cargos en negativo) |
| D | Saldo |
| E | Estado (fórmula, ver abajo) |
Si el extracto está en PDF, no lo copies a mano: conviértelo a Excel con las fechas y los importes ya en formato numérico. Antes de seguir, comprueba que está completo: saldo anterior + =SUMA(C:C) tiene que dar el saldo final del extracto.
2. Prepara la hoja «Libro»
Exporta el mayor de la cuenta 572 del mismo periodo desde tu programa y deja las mismas columnas: A fecha, B concepto, C importe y E estado. Para que el importe tenga el mismo signo que en el banco, calcúlalo como debe − haber: una entrada de dinero es positiva en los dos listados.
3. Puntea con una fórmula
En la hoja Banco, celda E2, busca en el libro un movimiento con el mismo importe y una fecha cercana (cinco días arriba o abajo):
=SI(CONTAR.SI.CONJUNTO(Libro!$C:$C;C2;Libro!$A:$A;">="&A2-5;Libro!$A:$A;"<="&A2+5)>0;"Punteado";"Pendiente")
En la hoja Libro, celda E2, la misma fórmula al revés:
=SI(CONTAR.SI.CONJUNTO(Banco!$C:$C;C2;Banco!$A:$A;">="&A2-5;Banco!$A:$A;"<="&A2+5)>0;"Punteado";"Pendiente")
Con dos movimientos del mismo importe en fechas parecidas (dos cuotas iguales, dos facturas del mismo cliente), la fórmula marca los dos como punteados aunque solo haya uno en el otro listado. Fíltralos por importe y revísalos a mano.
4. Revisa lo pendiente
Filtra la columna E por «Pendiente» en las dos hojas:
- Pendientes en Banco: el banco los tiene y la contabilidad no. Comisiones, intereses, recibos devueltos… Llevan asiento de ajuste.
- Pendientes en Libro: la contabilidad los tiene y el banco no. Cheques sin cobrar, transferencias en tránsito o errores. No llevan asiento, salvo que sean errores.
5. El estado de conciliación
En una tercera hoja, con el saldo final del extracto en B1 y el saldo de la 572 en B2:
| Celda | Concepto | Fórmula |
|---|---|---|
| B3 | Saldo de la 572 ajustado | =B2+SUMAR.SI(Banco!E:E;"Pendiente";Banco!C:C) |
| B4 | Saldo del banco ajustado | =B1+SUMAR.SI(Libro!E:E;"Pendiente";Libro!C:C) |
| B5 | Diferencia | =B3-B4 |
Si B5 da 0, la cuenta está conciliada: lo que queda pendiente en un lado explica exactamente la diferencia con el otro. Si no da 0, hay un error: un importe distinto en los dos listados (puntéalo a mano) o un movimiento que la fórmula ha emparejado mal.
Con los datos del ejemplo resuelto de asientos de ajuste, B3 y B4 dan los dos 12.201,05 y B5 da 0.
Cuándo dejar Excel
Excel funciona para una cuenta y un mes. Con muchos clientes y cuentas, lo práctico es importar el extracto en Norma 43 en el programa contable, que puntea, propone los asientos y guarda lo conciliado. Ver la guía de conciliación bancaria paso a paso.
Preguntas frecuentes
¿Se puede hacer la conciliación bancaria en Excel?
Sí. Con dos hojas (movimientos del banco y mayor de la 572), una fórmula de CONTAR.SI.CONJUNTO para puntear por importe y fecha, y un estado de conciliación que suma lo pendiente de cada lado.
¿Qué fórmula uso para puntear en Excel?
CONTAR.SI.CONJUNTO buscando el mismo importe en el otro listado dentro de un margen de días. Si devuelve más de 0, el movimiento está punteado.
¿Por qué la diferencia no da 0?
Porque hay un movimiento con importe distinto en el banco y en la contabilidad, un movimiento duplicado o dos importes iguales que la fórmula ha emparejado mal. Filtra los pendientes y revísalos a mano.
¿De dónde saco los movimientos del banco en Excel?
De la exportación de la banca online o, si solo tienes el PDF, convirtiéndolo. Comprueba siempre que el saldo anterior más los movimientos da el saldo final.