Volver al blog

Por Sergio Agámez ·

Plantilla de conciliación bancaria en Excel para descargar (Colombia)

Descarga gratis una plantilla de conciliación bancaria en Excel con fórmulas y formato listos. Pegas el extracto y el auxiliar, y el cruce se calcula solo.

¿Quieres convertir tu extracto ahora? 1 conversión gratis al día sin registro.

Convertir extracto

Si llevas un par de años haciendo cierres mensuales, conoces la escena: el primero del mes te sientas a hacer la conciliación del cliente nuevo, abres Excel en blanco, y otra vez montas las cinco columnas, otra vez escribes "Saldo según extracto", otra vez pegas las mismas tres fórmulas de cruce. Veinte minutos antes de empezar a conciliar de verdad. Este post corta esa parte: aquí tienes la plantilla de conciliación bancaria en Excel lista para descargar —con las fórmulas, el bloque de saldos y el formato condicional ya configurados— y, debajo, la explicación de cómo está construida por si quieres entenderla o ajustarla.

⬇ Descargar la plantilla de conciliación bancaria (.xlsx)

La descarga es gratis y no pide registro. La abres en Excel, pegas el extracto del banco en la hoja Extracto y el auxiliar 1110 en la hoja Libros, y el cruce se calcula solo. La única parte que sigue siendo trabajo —pasar el PDF del extracto a Excel— la resuelve ExtractoPro en menos de un minuto y te deja las cinco columnas listas para pegar.

Cómo usar la plantilla en 5 pasos

  1. Descárgala y ábrela en Excel. Trae cinco hojas (Instrucciones, Extracto, Libros, Conciliación, Diagnóstico) con las fórmulas y el formato ya puestos.
  2. Pega el extracto del banco en la hoja Extracto, columnas A–E (Fecha, Descripción, Débito, Crédito, Saldo), desde la fila 2. No toques la columna Estado: ya trae la fórmula de cruce.
  3. Pega el libro auxiliar 1110 en la hoja Libros, en las mismas cinco columnas.
  4. Escribe las consignaciones en tránsito y los cheques no cobrados en la hoja Conciliación (celdas B7 y B8). Las notas débito y crédito no contabilizadas las suma sola la plantilla desde lo que quedó marcado como NO ENCONTRADO en el extracto.
  5. Mira la celda Diferencia (B17): en verde y en cero, la conciliación cierra; en rojo, entra a la hoja Diagnóstico.

¿De dónde sale la hoja Extracto ya en Excel? De convertir el PDF del banco con ExtractoPro —es la mitad tediosa del cierre y la que más errores de transcripción genera cuando se hace a mano.

Qué es esta plantilla y qué no es

Esta plantilla es la estructura reutilizable del cruce: las hojas, las columnas estandarizadas, las fórmulas de cruce, el formato condicional que resalta pendientes y el bloque de saldos con la estructura colombiana clásica. La descargas lista o la rehaces siguiendo la estructura de más abajo, y la copias para cada nuevo cliente o cada nuevo mes.

Lo que no es:

Las hojas de la plantilla

La plantilla es un solo archivo de Excel con cinco hojas:

  1. Instrucciones — los cinco pasos de uso, dentro del propio archivo.
  2. Extracto — los movimientos del banco tal como salen del PDF, en cinco columnas estandarizadas. La columna Estado ya trae la fórmula de cruce y el formato condicional que resalta en rojo lo que no concilia.
  3. Libros — el libro auxiliar 1110-Bancos exportado del software contable, en las mismas cinco columnas.
  4. Conciliación — el bloque de saldos con la estructura clásica colombiana. El saldo del extracto, el de libros y las notas débito/crédito no contabilizadas se calculan solos; tú solo escribes consignaciones en tránsito y cheques no cobrados.
  5. Diagnóstico — validaciones y chequeos rápidos para cuando la conciliación no cierra.

Los nombres de las hojas importan porque las fórmulas referencian por nombre (Libros!C:C, Extracto!A:A). Si renombras una hoja, ajusta las fórmulas que la referencian.

Cómo está construida por dentro

Si solo quieres usarla, con los cinco pasos de arriba basta. Esta parte es para quien quiere entender cada fórmula —o rehacer la plantilla desde cero con sus propios nombres de hoja.

Hoja 1: Extracto

Cinco columnas, encabezados en la fila 1, datos desde la fila 2:

Columna Encabezado Contenido
A Fecha Fecha de la transacción según el banco
B Descripción Concepto tal como lo imprime el banco
C Débito Valor debitado de la cuenta
D Crédito Valor acreditado en la cuenta
E Saldo Saldo después de la transacción

Y dos columnas auxiliares que se agregan al final para el cruce:

Columna Encabezado Contenido
F Estado Fórmula de cruce contra Libros (la de abajo)
G Observación Texto libre del contador (tipo de partida, comentario)

La fórmula de la columna Estado se pega desde F2 hacia abajo (en la plantilla descargable ya viene puesta):

=SI(Y(C2="",D2=""),"",SI(O(Y(C2<>"",CONTAR.SI(Libros!C:C,C2)>0),Y(D2<>"",CONTAR.SI(Libros!D:D,D2)>0)),"CONCILIADO","NO ENCONTRADO"))

Busca el monto del débito (C2) y del crédito (D2) de esa fila en las columnas de débito y crédito del libro auxiliar; si alguno aparece, la marca como CONCILIADO, y si no, como NO ENCONTRADO. Los dos Y(C2<>"", …) evitan un falso positivo típico: sin ellos, una fila de solo crédito (con la celda de débito vacía) haría que CONTAR.SI contara las celdas en blanco del auxiliar y la marcara como conciliada sin estarlo. El SI(Y(C2="",D2=""),"") inicial deja en blanco las filas todavía vacías. Según tu configuración regional, Excel puede usar ; en vez de , como separador. La explicación detallada y las variantes (BUSCARV, cruce con ventana de fecha) están más abajo, en la sección de la hoja Diagnóstico.

Esta hoja se llena pegando el extracto convertido. Si el PDF venía bien transcrito —fechas como fechas, no como texto; montos como números, no como cadenas con puntos— las fórmulas de cruce funcionan al instante.

Hoja 2: Libros

Mismas cinco columnas, mismo orden, mismos encabezados. El libro auxiliar que exportas de Siigo, World Office, Helisa, ContaPyme o Alegra suele traer más columnas de las que necesitas (número de comprobante, tercero, cuenta contrapartida); puedes mantenerlas a la derecha de la columna E sin que rompan nada, pero las cinco columnas de la izquierda tienen que estar en el mismo orden que en Extracto para que las fórmulas crucen correctamente.

Si tu auxiliar exporta el monto en una sola columna con signo (positivo crédito, negativo débito), divídelo en dos: una columna C para los montos negativos en valor absoluto (débitos) y una D para los positivos (créditos). El cruce por monto se rompe si una hoja maneja una columna y la otra dos.

Hoja 3: Conciliación (el bloque de saldos)

Esta es la hoja que define si la conciliación cierra. La estructura sigue el modelo clásico colombiano: arrancas del saldo del extracto, sumas y restas las partidas conciliatorias, y llegas al saldo según libros.

Celda Concepto Fórmula o valor
A1 Conciliación bancaria (título)
A2 Banco / cuenta (texto: Bancolombia Cte 0501-xxxx)
A3 Período (texto: mayo de 2026)
A5 Saldo según extracto bancario =BUSCAR(2,1/(Extracto!E:E<>""),Extracto!E:E)
A7 (+) Consignaciones en tránsito (valor manual)
A8 (−) Cheques girados no cobrados (valor manual)
A9 (+) Notas débito no contabilizadas =SUMAR.SI(Extracto!F:F,"NO ENCONTRADO",Extracto!C:C) (automática)
A10 (−) Notas crédito no contabilizadas =SUMAR.SI(Extracto!F:F,"NO ENCONTRADO",Extracto!D:D) (automática)
A11 (±) Errores de transcripción (valor manual, con el signo del error)
A13 Saldo conciliado =B5+B7-B8+B9-B10+B11
A15 Saldo según libros (auxiliar 1110) =BUSCAR(2,1/(Libros!E:E<>""),Libros!E:E)
A17 Diferencia =B13-B15

La fórmula =BUSCAR(2,1/(Extracto!E:E<>""),Extracto!E:E) devuelve el último valor no vacío de la columna E. Es el saldo final del extracto sin tener que saber en qué fila termina cada mes. Si prefieres una alternativa moderna, en versiones recientes de Excel funciona también:

=BUSCARX(VERDADERO, Extracto!E:E<>"", Extracto!E:E, , 0, -1)

El objetivo del bloque es simple: en la celda B17 (Diferencia) tiene que aparecer cero. Si aparece otra cifra, la conciliación no cuadra y entras al diagnóstico — empezando por el manual de las cinco causas frecuentes.

Por qué cada signo en el bloque

El bloque parece arbitrario hasta que se piensa qué pasa físicamente:

  • Consignaciones en tránsito (+): el dinero ya entró en libros pero el banco aún no lo procesa, así que el extracto está más bajo. Suman al saldo del extracto para llegar al de libros.
  • Cheques girados no cobrados (−): el dinero salió en libros pero el banco aún no lo descontó, así que el extracto está más alto. Restan del saldo del extracto.
  • Notas débito no contabilizadas (+): el banco ya descontó (GMF, comisión, retención) y el extracto está más bajo; en libros todavía no se causó, así que libros está más alto. Para llevar el extracto hasta el saldo de libros, suman.
  • Notas crédito no contabilizadas (−): el banco ya abonó (intereses ganados) y el extracto está más alto; en libros todavía no se causó, así que libros está más bajo. Para llevar el extracto hasta el saldo de libros, restan.

Si te confundes con los signos, esta es la regla operativa: la pregunta es siempre "para llevar el saldo del extracto al saldo de libros, ¿qué falta sumar o restar?". La respuesta define el signo.

Hoja 4: Diagnóstico (las fórmulas pegables)

Esta es la hoja que más usa quien lleva varios cierres al mes. Aloja las fórmulas de cruce que aplicas en las columnas auxiliares de Extracto y Libros.

Cruce básico por monto

En la columna F de la hoja Extracto (encabezado: Estado), pegado desde F2 hacia abajo, va la misma fórmula que trae la plantilla descargable:

=SI(Y(C2="",D2=""),"",SI(O(Y(C2<>"",CONTAR.SI(Libros!C:C,C2)>0),Y(D2<>"",CONTAR.SI(Libros!D:D,D2)>0)),"CONCILIADO","NO ENCONTRADO"))

Busca el monto del débito o del crédito de la fila en las columnas C y D del libro auxiliar. Si encuentra alguno, el movimiento está contabilizado (CONCILIADO); si no, es partida conciliatoria (NO ENCONTRADO). Los guardas Y(C2<>"", …) son los que evitan que una fila de solo crédito o solo débito se marque como conciliada por error al contar celdas en blanco del auxiliar.

Cruce con BUSCARV para traer información del auxiliar

Si quieres no solo saber si existe sino traer información del libro (por ejemplo el comprobante o la cuenta contrapartida), reemplaza la columna F por una variante con BUSCARV envuelto en SI.ERROR:

=SI.ERROR(BUSCARV(C2,Libros!C:G,5,FALSO),"NO ENCONTRADO")

Busca el monto del débito del extracto en la columna C del libro auxiliar y devuelve el valor de la quinta columna del rango. Si no encuentra, escribe "NO ENCONTRADO".

Cruce por monto + ventana de fecha (la más robusta)

Cuando hay duplicados de monto o las fechas no coinciden (transacciones que el banco procesa al día siguiente), el cruce más fiable es por monto con tolerancia de tres días:

=SUMAPRODUCTO((Libros!C:C=C2)*(ABS(Libros!A:A-A2)<=3))

Cuenta cuántas filas del libro auxiliar tienen el mismo monto y una fecha dentro de ±3 días de la del extracto. Si devuelve ≥1, hay match razonable. Esta fórmula es más pesada con muchos miles de filas; si tu auxiliar pasa de las 5.000 líneas, conviene acotar el rango (Libros!C2:C5000) en vez de columnas enteras.

Suma de partidas pendientes

Para sumar todos los débitos del extracto que no aparecen en libros (típicamente GMF, comisiones, retenciones):

=SUMAR.SI(Extracto!F:F,"NO ENCONTRADO",Extracto!C:C)

Y el equivalente para créditos no contabilizados (intereses ganados, devoluciones):

=SUMAR.SI(Extracto!F:F,"NO ENCONTRADO",Extracto!D:D)

En la plantilla descargable estas dos fórmulas ya están en las celdas B9 y B10 de la hoja Conciliación, así que el total de notas débito y crédito no contabilizadas se calcula solo a partir de lo que la columna Estado marcó como NO ENCONTRADO. Si armas la plantilla desde cero, pégalas ahí.

Validación del extracto contra sí mismo

Antes de empezar a cruzar conviene confirmar que el extracto cuadra contra sus propios totales. En la hoja Diagnóstico:

=Extracto!E2 + SUMA(Extracto!D3:D1000) - SUMA(Extracto!C3:C1000)

La columna E trae el saldo después de cada transacción, así que E2 ya es el saldo después del primer movimiento. A ese saldo le sumas los créditos y le restas los débitos de la fila 3 en adelante; el resultado debe coincidir con el último saldo del extracto (el mismo que devuelve la fórmula de saldo final). Ajusta el rango D3:D1000 al número real de filas de tu extracto. Si no coincide, hay un signo invertido o una fila omitida y conviene corregir eso antes de cruzar contra libros.

Validación fila por fila del saldo del extracto

=E3-(E2+D3-C3)

En la columna G de Extracto, desde G3 hacia abajo. El resultado tiene que ser cero en cada fila. Si en alguna no es cero, esa fila tiene un error de transcripción —débito y crédito invertidos, monto mal digitado, saldo cambiado—. Aplicar formato condicional para resaltar las filas con valor distinto de cero hace el error visible al instante.

Formato condicional: resaltar pendientes

Esta parte toma dos minutos y cambia la usabilidad de la plantilla.

En la hoja Extracto, selecciona la columna F completa. Menú Inicio → Formato condicional → Nueva regla → Aplicar formato únicamente a las celdas que contengan → Texto específico → "NO ENCONTRADO" → Formato → Relleno rojo claro, texto rojo oscuro. Acepta.

Repite el mismo procedimiento en la hoja Libros.

Resultado: al abrir la plantilla, las partidas conciliatorias saltan a la vista. No tienes que filtrar ni ordenar; el color hace el trabajo.

Una segunda regla útil: en la columna G de Extracto (la validación del saldo), aplicar formato condicional con la regla "El valor de la celda es distinto de 0" → relleno amarillo. Así, cualquier fila con error de transcripción aparece marcada.

Ejemplo trabajado: cooperativa de transportadores en Sogamoso

Para anclar la plantilla en un caso concreto, miremos una semana de cierre de una cooperativa de transportadores en Sogamoso con cuenta corriente en Banco de Bogotá. Mueve alrededor de $90M mensuales entre recaudos de afiliados, pagos a aseguradoras y movimientos bancarios estándar. Cifras ilustrativas pero internamente consistentes.

Hoja Extracto (siete filas representativas del 5 al 9 de mayo de 2026):

Fecha Descripción Débito Crédito Saldo Estado
05/05/2026 RECAUDO AFILIADO RUEDA TRANSPORTES SAS 4.500.000 28.700.000 CONCILIADO
05/05/2026 RECAUDO AFILIADO COOTRANSGOG 2.800.000 31.500.000 CONCILIADO
06/05/2026 PAGO PSE SURA SEGUROS NIT 890903407 6.200.000 25.300.000 CONCILIADO
06/05/2026 GMF 4X1000 24.800 25.275.200 NO ENCONTRADO
07/05/2026 COMISIÓN CUOTA DE MANEJO MAYO 32.500 25.242.700 NO ENCONTRADO
08/05/2026 RECAUDO AFILIADO TRANSP. EL DORADO 1.950.000 27.192.700 CONCILIADO
09/05/2026 RETENCIÓN EN LA FUENTE PRACTICADA 18.450 27.174.250 NO ENCONTRADO

Hoja Libros (las contrapartidas en el auxiliar 1110.05):

Fecha Descripción Débito Crédito Saldo
30/04/2026 Saldo al cierre de abril (ya refleja el cheque 0892 girado el 30/04, no cobrado) 22.750.000
05/05/2026 RC-1142 RECAUDO RUEDA TRANSPORTES 4.500.000 27.250.000
05/05/2026 RC-1143 RECAUDO COOTRANSGOG 2.800.000 30.050.000
06/05/2026 CE-2089 PAGO SURA PÓLIZA MAYO 6.200.000 23.850.000
08/05/2026 RC-1144 RECAUDO EL DORADO 1.950.000 25.800.000

Aplicando la fórmula de cruce, la columna F de Extracto marca tres filas como NO ENCONTRADO: el GMF, la comisión de cuota de manejo y la retención en la fuente. Las cuatro filas restantes —los tres recaudos y el pago a Sura— calzan al peso contra el auxiliar.

Del lado de libros hay un movimiento que no está en el extracto: el cheque 0892 a proveedor de lubricantes por $1.450.000, girado y contabilizado el 30 de abril pero aún no cobrado por el proveedor. Por eso ya está incluido en el saldo inicial de libros de mayo ($22.750.000), que arranca $1.450.000 por debajo del saldo inicial del extracto ($24.200.000) — esa diferencia es exactamente el cheque pendiente.

Bloque de Conciliación:

Saldo según extracto al 09/05/2026:           $27.174.250

(−) Cheques girados no cobrados (cheque 0892): $1.450.000
(+) Notas débito no contabilizadas:
    GMF 4X1000:                                   $24.800
    Comisión cuota de manejo:                     $32.500
    Retención en la fuente:                       $18.450

Saldo conciliado:                             $25.800.000
Saldo según libros (auxiliar 1110.05):       $25.800.000

Diferencia:                                            $0

El cheque resta porque el extracto todavía está "más alto" que libros (el banco no lo ha pagado); las tres notas débito suman porque el banco ya las descontó del extracto pero en libros aún no se causaron. La conciliación cierra. Las tres notas débito automáticas se contabilizan ahora —el GMF a 511595 (impuestos asumidos, subcuenta del GMF clasificado correctamente), la comisión a 530505 (gastos bancarios) y la retención a la cuenta del activo correspondiente—. El cheque pendiente queda como partida abierta hasta que el proveedor lo cobre.

Cómo adaptar la plantilla a tu caso

Varias cuentas en un solo cliente. Para dos o tres cuentas, duplicar las hojas con sufijos funciona: Extracto_Bancolombia, Libros_Bancolombia, Conciliación_Bancolombia, y lo mismo para Davivienda. Las fórmulas referencian las hojas por nombre, así que las copias de las fórmulas tienen que ajustarse en cada bloque. Para cuatro cuentas o más, conviene un archivo por cuenta y una hoja Resumen aparte que consolide los saldos con BUSCAR o referencias externas.

Cierres multi-mes. La plantilla se diseña para un mes. Si necesitas histórico, mantén un archivo por mes (Conciliacion_2026_05_Cliente.xlsx, Conciliacion_2026_06_Cliente.xlsx) y una hoja Resumen Anual en un archivo separado que tome los saldos finales de cada mes con referencias externas. Mezclar varios meses en el mismo archivo se vuelve frágil porque las fórmulas de saldos y los rangos cambian de mes a mes.

Auxiliar con múltiples subcuentas en la misma cuenta bancaria. En empresas con centros de costo o terceros parametrizados, el auxiliar exporta varias filas por cada movimiento bancario (la causación, la retención practicada, el centro de costo). Para esos casos conviene agrupar el auxiliar por número de comprobante antes de pegarlo en la hoja Libros, o filtrar para conservar solo las filas de la cuenta 1110 directamente.

Cuenta de ahorros vs cuenta corriente. La estructura no cambia. Lo que cambia es la frecuencia de los cheques girados no cobrados (irrelevantes en cuenta de ahorros) y las comisiones bancarias (cuotas de manejo más altas en cuentas corrientes con datáfono). Las líneas del bloque siguen siendo las mismas; las que no aplican quedan en cero.

Cómo acelerar la mitad tediosa: la hoja Extracto

La plantilla resuelve el armado del cruce. Lo que no resuelve es la otra mitad del cierre: pasar el PDF del extracto a Excel para alimentar la hoja Extracto. Esa parte —si la hace un auxiliar a mano— se lleva entre 30 minutos y 2 horas por extracto, dependiendo del banco y del número de páginas, y es donde aparecen los errores de transcripción que después rompen la conciliación (la causa #5 del manual de descuadres).

Con ExtractoPro la conversión del PDF a Excel toma menos de un minuto, entrega las cinco columnas estándar (Fecha, Descripción, Débito, Crédito, Saldo) en el mismo orden que la plantilla y preserva los formatos numéricos colombianos (puntos para miles, comas para decimales). Pegas el resultado directo en la hoja Extracto y las fórmulas de cruce funcionan al instante.

Adicionalmente, el Excel descargado trae debajo de la tabla principal un resumen por concepto con totales por categoría —cuánto GMF, cuántas comisiones, cuántas consignaciones—. Es el chequeo rápido antes del cruce fila por fila: si el GMF mensual del resumen no calza contra el movimiento del mes en 511595 (o en 5305, según política), ya sabes que hay clasificación cruzada antes de empezar el detalle.

El flujo armado queda así:

  1. Descargar el PDF del extracto desde la Sucursal Virtual del banco (Bancolombia, Davivienda, BBVA Colombia, Banco de Bogotá, etc.).
  2. Convertir a Excel con ExtractoPro (menos de un minuto) y pegar en la hoja Extracto de la plantilla.
  3. Exportar el libro auxiliar 1110 desde el software contable (Siigo, World Office, Helisa, ContaPyme, Alegra) y pegar en la hoja Libros — el detalle del paso está en conciliar el libro auxiliar de bancos con el extracto.
  4. Dejar trabajar a las fórmulas: la columna Estado marca pendientes en rojo, el bloque de Conciliación calcula el saldo conciliado, la celda Diferencia debe ser cero.
  5. Si la diferencia no es cero, entrar a la hoja Diagnóstico y aplicar las cinco causas frecuentes en orden.

Cierre operativo

La plantilla buena no es la que armas rápido; es la que se reutiliza el mes dos, el tres y el doce sin que nadie tenga que rearmarla. Cinco hojas con nombres fijos, cinco columnas en el mismo orden, un bloque de saldos con los signos claros y las fórmulas de cruce ya puestas. La descargas una vez, la guardas como tu plantilla base, y cada cierre es abrir una copia, pegar el extracto del mes (ya en Excel desde ExtractoPro), pegar el auxiliar exportado, y dejar que las fórmulas hagan el cruce.

Si el cierre que estás haciendo este mes todavía arranca con el auxiliar pegando el extracto a mano del PDF, ese es el cuello de botella real. Con ExtractoPro la conversión del PDF a Excel toma menos de un minuto, y los planes de ExtractoPro cubren desde el contador con tres clientes hasta firmas con varias decenas de cierres mensuales — 1 conversión al día anónima, 3 al día con cuenta gratis, y planes pagos sin límite diario para procesar varios meses en bloque.

Descargar la plantilla (.xlsx) · Convertir mi extracto ahora →

Posts relacionados

¿Listo para convertir tus extractos?

1 conversión gratis al día. Aumenta a 3 al día con cuenta gratis.

Convertir extracto ahora
← Ver todos los artículos