Conciliación bancaria en Excel: fórmulas y plantilla paso a paso

Puedes empezar por uno de estos dos caminos:

1. Usar el archivo preparado: descarga la plantilla XLSX existente o consulta primero la página de la plantilla de conciliación bancaria en Excel.
2. Construir tu propio archivo: crea las hojas Extracto, Libros y Conciliación, y aplica las fórmulas de esta guía.

En ambos casos, el cruce por monto sirve como filtro inicial. Que un valor aparezca en los dos registros no demuestra que las transacciones sean la misma: antes de cerrar una coincidencia debes validar fecha, referencia, descripción y posibles duplicados.

Antes de empezar: reúne los dos registros

Necesitas el extracto bancario y los movimientos del libro auxiliar en un formato que puedas trabajar en Excel.

Si el extracto está en PDF, ExtractoPro convierte extractos bancarios PDF a Excel y CSV. Los movimientos de libros deben salir de tu registro contable y conservar el detalle disponible para revisarlos después.

No elimines referencias, descripciones ni fechas al preparar los datos. Esos campos permiten revisar coincidencias cuando dos movimientos tienen el mismo monto.

Camino 1: usar la plantilla existente

La opción directa es descargar la plantilla de conciliación bancaria en formato XLSX.

El archivo existente contiene las hojas Instrucciones, Extracto, Libros, Conciliación y Diagnóstico. Puedes conocer su estructura y acceder nuevamente a la descarga desde la página de la plantilla.

Las fórmulas de estado incluidas en cualquier archivo deben interpretarse como un primer filtro. Una coincidencia de monto requiere revisión y no debe marcarse automáticamente como conciliada.

Camino 2: construir tres hojas en Excel

Crea un libro con esta estructura mínima:

• Extracto: movimientos informados por el banco.
• Libros: movimientos registrados en el libro auxiliar.
• Conciliación: resumen de pendientes, valores por revisar y diferencia entre saldos.

Columnas de Extracto y Libros

Mantén estas columnas en las dos hojas:

Columna	Contenido
A	Fecha
B	Descripción
C	Débito
D	Crédito
E	Saldo

Agrega después las columnas de estado y observación que necesites. Si el documento de origen incluye una referencia, consérvala en otra columna para usarla durante la validación.

Es importante mantener C como Débito y D como Crédito, porque las fórmulas siguientes dependen de esas referencias.

Filtro inicial con CONTAR.SI

Microsoft define CONTAR.SI como una función que cuenta las celdas de un rango que cumplen un criterio. Su sintaxis es:

CONTAR.SI(rango;criterios)

Para contar cuántas veces aparece el débito de C2 de la hoja Extracto dentro de la columna de débitos de Libros, usa:

=CONTAR.SI(Libros!C:C;C2)

Cada referencia significa lo siguiente:

• Libros!C:C es el rango que se revisa: toda la columna C de la hoja Libros.
• C2 es el criterio: el débito de la fila actual en la hoja Extracto.
• Un resultado de 0 indica que ese monto no aparece en el rango revisado.
• Un resultado de 1 o más indica que el monto aparece, pero no confirma que sea la misma transacción.

Para créditos, replica el filtro con la columna D:

=CONTAR.SI(Libros!D:D;D2)

Aquí Libros!D:D contiene los créditos de Libros y D2 es el crédito que se está evaluando en Extracto.

Fórmula copiable para el estado de los débitos

En una columna de estado de la hoja Extracto, escribe exactamente esta fórmula y cópiala hacia abajo:

=SI(C2="";"";SI(CONTAR.SI(Libros!C:C;C2)=0;"PENDIENTE";"REVISAR COINCIDENCIA"))

La fórmula funciona así:

• C2="" comprueba si la celda del débito está vacía. Si lo está, devuelve una celda vacía.
• CONTAR.SI(Libros!C:C;C2) cuenta cuántas celdas de la columna C de Libros tienen el mismo monto que C2.
• =0 significa que el monto no fue encontrado y asigna PENDIENTE.
• Si el conteo es mayor que cero, asigna REVISAR COINCIDENCIA.

La etiqueta REVISAR COINCIDENCIA es deliberada. No uses CONCILIADO basándote solamente en el monto.

En este ejemplo, C representa Débito. Para créditos, replica la lógica con D:

=SI(D2="";"";SI(CONTAR.SI(Libros!D:D;D2)=0;"PENDIENTE";"REVISAR COINCIDENCIA"))

Cómo revisar una coincidencia de monto

Cuando una fila quede como REVISAR COINCIDENCIA, compara al menos:

• La fecha del movimiento en Extracto y Libros.
• La referencia disponible en cada registro.
• La descripción o concepto.
• La existencia de montos repetidos.
• Si ya se utilizó otra fila como contraparte del mismo movimiento.

Si el mismo monto aparece varias veces, revisa cada posible pareja por separado. Dos débitos iguales, incluso en fechas cercanas, pueden corresponder a operaciones diferentes.

También conviene hacer el análisis en ambos sentidos: de Extracto contra Libros y de Libros contra Extracto. Revisar una sola hoja puede dejar por fuera movimientos que existen únicamente en el otro registro.

Hoja Conciliación

Usa esta hoja para concentrar los resultados sin alterar los datos de origen. Como mínimo, registra:

• Saldo según el extracto.
• Saldo según libros.
• Diferencia entre ambos saldos.
• Cantidad de débitos pendientes.
• Cantidad de créditos pendientes.
• Movimientos cuya coincidencia todavía está en revisión.
• Observaciones y soporte de los ajustes identificados.

Si B2 contiene el saldo del extracto y B3 el saldo de libros, puedes calcular una diferencia inicial con:

=B2-B3

En esta fórmula, B2 es el valor informado por el extracto y B3 es el valor registrado en libros. El resultado permite detectar que existe una diferencia, pero no explica su causa.

Si el estado de los débitos quedó en la columna F de Extracto, puedes contar los pendientes con:

=CONTAR.SI(Extracto!F:F;"PENDIENTE")

• Extracto!F:F es la columna de estados que se revisará.
• "PENDIENTE" es el criterio que se contará.

Si el estado de créditos quedó en otra columna, cambia el rango por esa columna y conserva el mismo criterio.

Uso acotado de SI.ERROR

Microsoft indica que SI.ERROR devuelve un valor indicado si la fórmula produce un error y, si no, devuelve el resultado de la fórmula. Su sintaxis es:

SI.ERROR(valor;valor_si_error)

Puedes usarla alrededor de una fórmula que pueda producir un error, por ejemplo:

=SI.ERROR(tu_fórmula;"REVISAR FÓRMULA")

tu_fórmula es la operación que Excel intenta calcular y "REVISAR FÓRMULA" es el valor mostrado si esa operación genera un error.

SI.ERROR no valida una coincidencia ni demuestra que un movimiento esté conciliado. Solo controla qué se muestra cuando la fórmula evaluada produce un error.

BUSCARV como apoyo, no como cierre automático

Microsoft documenta BUSCARV para buscar un valor en la primera columna de un rango y devolver el dato de otra columna. El último argumento FALSO pide una coincidencia exacta.

Por ejemplo, esta fórmula busca la fecha de A2 en la primera columna de Libros!A:E y devuelve el valor de la quinta columna del rango:

=SI.ERROR(BUSCARV(A2;Libros!A:E;5;FALSO);"SIN COINCIDENCIA")

• A2 es la fecha buscada.
• Libros!A:E es el rango; la fecha debe estar en su primera columna.
• 5 devuelve el dato de la quinta columna del rango.
• FALSO exige una coincidencia exacta.
• SI.ERROR muestra SIN COINCIDENCIA si la búsqueda produce un error.

Úsala solo como apoyo de inspección. Si hay varios movimientos en la misma fecha, BUSCARV no identifica por sí sola cuál es la contraparte correcta. Valida también referencia, descripción, monto y duplicados antes de cambiar el estado.

Qué hacer cuando quedan pendientes

Filtra las filas marcadas como PENDIENTE y vuelve al documento de origen. Comprueba que el monto esté en la columna correcta, revisa la fecha, busca la referencia y compara la descripción.

Después revisa la otra dirección del cruce. Un movimiento puede aparecer en Libros y no en Extracto, o en Extracto y no en Libros.

No cambies un estado solo para eliminar el pendiente. Documenta la causa encontrada y cualquier ajuste con el soporte correspondiente.

Qué hacer si la diferencia no es cero

Si la diferencia entre el saldo del extracto y el saldo de libros no es cero:

1. Revisa primero los movimientos marcados como PENDIENTE.
2. Examina las filas con REVISAR COINCIDENCIA, especialmente cuando el conteo del monto sea mayor que uno.
3. Confirma que débitos y créditos no estén intercambiados.
4. Verifica fechas, referencias, descripciones y duplicados en ambos registros.
5. Registra en Conciliación la explicación y el soporte de cada diferencia identificada.

Una diferencia de cero tampoco prueba por sí sola que todos los movimientos estén correctamente relacionados: pueden existir valores que se compensen entre sí. Conserva la revisión de detalle antes de dar por terminado el cruce.

Descarga o continúa con tus datos

Para trabajar sobre el archivo existente, ve a la página de la plantilla de conciliación bancaria o descarga directamente el XLSX.

Si primero necesitas llevar un extracto PDF a un formato tabular, ExtractoPro convierte extractos bancarios PDF a Excel y CSV. Después puedes copiar los movimientos al archivo elegido y aplicar la revisión descrita en esta guía.