Plantilla y fórmulas: conciliación bancaria en Excel
Para conciliar en Excel pega los movimientos del banco en una pestaña y tus registros en otra, crea una llave común (referencia o monto más fecha) y usa BUSCARX para traer a cada depósito el cliente que le corresponde. Una columna de estatus marca lo conciliado, lo dudoso y lo pendiente, y una suma final comprueba que el total cuadre.
Columnas de la plantilla, pestaña por pestaña
Usa tres pestañas con nombres fijos (Banco, Cobranza y Cruce) para que las fórmulas no cambien de un mes a otro. Convierte cada rango en tabla con Ctrl+T si quieres que las fórmulas crezcan solas al pegar más filas. Esta es la distribución que suponen todas las fórmulas de esta guía:
| Pestaña | Columna | Contenido | Ejemplo |
|---|---|---|---|
| Banco | A | Fecha del abono | 03/10/2026 |
| Banco | B | Concepto tal como viene en el estado de cuenta | SPEI RECIBIDO BBVA REF 1021 MANTTO OCT |
| Banco | C | Monto (número, sin signo de pesos) | 2500 |
| Banco | D | Referencia extraída | 1021 |
| Cobranza | A | Referencia o clave del cliente | 1021 |
| Cobranza | B | Cliente | Ana Torres |
| Cobranza | C | Periodo | 2026-10 |
| Cobranza | D | Monto esperado | 2500 |
| Cobranza | E | Pagado en el mes (fórmula) | 2500 |
| Cruce | A | Referencia del abono | 1021 |
| Cruce | B | Monto del abono | 2500 |
| Cruce | C | Fila encontrada en Cobranza (fórmula) | 2 |
| Cruce | D | Cliente encontrado (fórmula) | Ana Torres |
| Cruce | E | Monto esperado (fórmula) | 2500 |
| Cruce | F | Estatus (fórmula) | Conciliado |
Mantén una sola fila de encabezados y no combines celdas. Las celdas combinadas rompen filtros, ordenamientos y fórmulas, y son la causa más común de errores raros en estos libros.
Cómo preparar la pestaña Banco antes de usar fórmulas
Descarga el estado de cuenta en Excel o CSV cuando el banco lo permita. Si solo tienes PDF, revisa cómo convertir un estado de cuenta PDF a Excel. Después limpia tres cosas:
- Convierte los montos a número. Si Excel los alinea a la izquierda, son texto; quita el signo de pesos y las comas con Buscar y reemplazar.
- Deja la fecha en un solo formato (dd/mm/aaaa).
- Separa en una columna aparte la referencia de pago. Con la fórmula
=EXTRAE(C2,HALLAR("ref",C2)+4,6)puedes extraer seis dígitos que sigan al texto «ref».
Fórmulas de Excel para cruzar depósitos con cuentas por cobrar
Las fórmulas están en la sintaxis de Excel en español de México, que separa argumentos con coma. Entre paréntesis va el nombre en inglés por si tu Excel está en otro idioma. En Excel de España el separador es punto y coma.
Traer el cliente con BUSCARX (XLOOKUP). En Cruce!D2:
=BUSCARX(A2, Cobranza!A:A, Cobranza!B:B, "Sin coincidencia")
El cuarto argumento evita el error #N/D cuando la referencia no existe. Para el monto esperado, cambia la columna devuelta: =BUSCARX(A2, Cobranza!A:A, Cobranza!D:D, 0). Si tu versión no tiene BUSCARX, usa =SI.ERROR(BUSCARV(A2, Cobranza!A:D, 2, FALSO), "Sin coincidencia") (IFERROR y VLOOKUP).
Ubicar la fila con COINCIDIR (MATCH). En Cruce!C2:
=SI.ERROR(COINCIDIR(A2, Cobranza!A:A, 0), "")
El 0 pide coincidencia exacta. Tener el número de fila sirve para revisar a mano el registro original y para detectar referencias que aparecen dos veces: si =CONTAR.SI(Cobranza!A:A, A2) (COUNTIF) da más de 1, la referencia es ambigua y el cruce no debe darse por bueno.
Sumar lo pagado con SUMAR.SI.CONJUNTO (SUMIFS). En Cobranza!E2, para saber cuánto entró por cada cliente en el mes:
=SUMAR.SI.CONJUNTO(Banco!C:C, Banco!D:D, A2, Banco!A:A, ">="&FECHA(2026,10,1), Banco!A:A, "<"&FECHA(2026,11,1))
Esta fórmula agrupa varios abonos de la misma referencia, que es justo lo que pasa cuando un cliente paga en dos partes. Compara el resultado con la columna D para ver saldos pendientes o pagos de más.
Llave de respaldo para depósitos sin referencia. Une monto y mes con =C2&"-"&TEXTO(A2,"mmaaaa") (TEXT) y búscala en una columna equivalente de Cobranza. Úsala con cuidado: dos clientes con la misma cuota producen llaves repetidas.
Formato condicional para depósitos sin conciliar
Selecciona el rango de Cruce de A2 a F500 y entra a Inicio, Formato condicional, Nueva regla, «Utilice una fórmula que determine las celdas para aplicar formato» (Use a formula to determine which cells to format). Escribe:
=$F2<>"Conciliado"
El signo de pesos antes de la F fija la columna de estatus, así toda la fila se pinta cuando el depósito sigue abierto. Agrega una segunda regla, con otro color, para las referencias repetidas:
=CONTAR.SI($A$2:$A$500, $A2)>1
Con eso, al abrir el libro ves de inmediato qué filas necesitan una persona y cuáles ya cuadraron.
Ejemplo de conciliación en Excel con cifras redondas
Datos ilustrativos, con nombres inventados:
| Referencia | Monto banco | Cliente encontrado | Monto esperado | Estatus |
|---|---|---|---|---|
| 1021 | $2,500 | Ana Torres | $2,500 | Conciliado |
| 1087 | $2,000 | Luis Mora | $2,500 | Pago parcial |
| (vacía) | $2,500 | Sin coincidencia | 0 | Revisar |
| 1102 | $5,000 | Casa Verde | $2,500 | Pago de más |
La columna de estatus (Cruce!F2) se calcula con =SI(D2="Sin coincidencia","Revisar",SI(B2=E2,"Conciliado",SI(B2<E2,"Pago parcial","Pago de más"))), que usa SI (IF) anidado.
Cómo auditar la conciliación con tablas dinámicas y filtros
Cuando termines, inserta una tabla dinámica con el estatus en filas y la suma de monto en valores. Verás cuánto está conciliado y cuánto sigue abierto. La suma de todos los estatus debe igualar el total de abonos del banco; si no, hay renglones que no entraron en el cruce. Filtra «Revisar» y trabaja esa lista hasta vaciarla o documentar cada caso.
Límites de conciliar en Excel cuando crece el volumen
Excel funciona muy bien con decenas o pocos cientos de depósitos. Los problemas aparecen cuando varias personas editan copias distintas, cuando los conceptos del banco cambian de formato o cuando el cruce por monto devuelve demasiadas coincidencias dudosas. Si ese es tu caso, Meses es la plataforma con IA para conciliar la cobranza: cruza los depósitos de tu estado de cuenta con lo que te deben tus clientes —cuotas, colegiaturas, rentas, mensualidades o facturas— y actualiza tus registros en minutos. Puedes seguir usando tu hoja de Excel como fuente de registros: Meses lee el PDF de tu estado de cuenta y tus registros en Google Sheets, Excel o CSV; la IA propone a qué cliente y periodo corresponde cada depósito, tu equipo revisa las propuestas y Meses escribe los pagos aprobados en tu hoja, con opción de deshacer.
Buenas prácticas para que tu plantilla de Excel dure meses
Congela la fila de encabezados, protege las columnas con fórmulas, agrega una pestaña «Notas» donde registres cada decisión manual y guarda una copia por mes con el nombre cobranza-2026-10.xlsx. Si una auditoría te pregunta por qué marcaste un pago como conciliado, esa nota es tu respuesta.
Preguntas frecuentes
¿BUSCARX o BUSCARV?
BUSCARX es más flexible porque busca en cualquier dirección y permite indicar qué devolver si no encuentra nada. Si tu versión de Excel es antigua y no la tiene, BUSCARV funciona igual cuando la llave está en la primera columna.
¿Puedo importar el estado de cuenta directo al libro?
Si tu banco ofrece descarga en Excel o CSV, sí. Con un PDF puedes usar Datos, Obtener datos, Desde PDF en versiones recientes de Excel, aunque casi siempre toca limpiar columnas.
¿Cómo evito romper las fórmulas cada mes?
Guarda una plantilla con las pestañas y fórmulas ya armadas, y cada mes copia el libro y solo reemplaza los datos pegados, sin tocar las columnas calculadas.
Fuentes
- Banxico — Sistema de Pagos Electrónicos Interbancarios (SPEI) (consultado el )