Cómo hacer una conciliación bancaria en Excel

Qué necesita antes de empezar

Necesita tres cosas del mes que va a conciliar:

• El estado de cuenta como hoja de cálculo. Si solo tiene el PDF, convierta el estado de cuenta a Excel. Los archivos Excel y CSV de BankConv tienen las columnas Date, Description, Reference, Amount y Balance.
• Sus libros o auxiliar de la cuenta bancaria, exportados a Excel.
• La conciliación del mes anterior, por los depósitos en tránsito y los cheques en circulación que seguían abiertos.

Revise el estado de cuenta convertido antes de emparejar nada: el saldo inicial más todos los importes debe dar el saldo final del PDF. Cuando BankConv puede compararlos, la página de resultados le dice si los saldos cuadran o le avisa que no.

Algunos estados de cuenta imprimen los cheques y las comisiones en tablas propias, separadas de los demás movimientos. BankConv agrega esas tablas a la lista de movimientos solo cuando el resultado coincide al centavo con los saldos impresos del estado de cuenta. Si un estado de cuenta aún no cuadra, busque primero esas tablas.

Paso 1: ponga cada lado en su propia hoja

Cree un libro con tres hojas llamadas Banco, Libros y Conciliacion. Pegue el estado de cuenta en Banco y su contabilidad en Libros, con los encabezados en la fila 1 y el importe en la columna D de ambas hojas.

Las dos hojas deben usar el mismo signo: el dinero que entra a la cuenta es positivo y el que sale es negativo. La contabilidad suele mostrar lo contrario de lo que dice el banco, porque un depósito es un abono en el estado de cuenta y un cargo en sus libros. Si su exportación tiene columnas separadas de cargos y abonos, agregue primero una sola columna de importe con signo.

Este ejemplo, con datos ficticios, recorre el resto de la guía. Cubre octubre de 2026, y los dos lados abrieron en $8,000.00. Ref. es la columna Reference.

Hoja Banco

Fecha	Descripción	Ref.	Importe	Saldo
02/10	Depósito FACT-1041		2,400.00	10,400.00
05/10	Cheque 1203	1203	-650.00	9,750.00
09/10	Renta SPEI		-1,800.00	7,950.00
14/10	Depósito FACT-1042		1,150.00	9,100.00
16/10	Tarjeta de gasolina		-45.00	9,055.00
16/10	Tarjeta de gasolina		-45.00	9,010.00
21/10	Cheque 1205	1205	-540.00	8,470.00
30/10	Comisión por manejo de cuenta		-25.00	8,445.00
31/10	Intereses pagados		3.20	8,448.20

Hoja Libros

Fecha	Descripción	Ref.	Importe
02/10	Cobro FACT-1041		2,400.00
03/10	Cheque 1203	1203	-650.00
09/10	Renta de octubre		-1,800.00
14/10	Cobro FACT-1042		1,150.00
16/10	Gasolina		-45.00
16/10	Gasolina		-45.00
19/10	Cheque 1205	1205	-450.00
29/10	Cheque 1207	1207	-320.00
31/10	Cobro FACT-1043		1,275.00

Los libros cierran en $9,515.00 y el estado de cuenta en $8,448.20.

Paso 2: empareje las filas con CONTAR.SI.CONJUNTO

Empareje por importe, no por fecha. Un cheque se registra el día que lo expide y aparece en el estado de cuenta días después, así que las fechas rara vez coinciden.

Emparejar solo por importe tiene una falla: si registró un solo cargo de gasolina de $45.00 y el banco muestra dos, las dos filas del banco parecerían emparejadas. Para evitarlo, numere cada repetición de un importe y empareje el importe y su número juntos.

En la hoja Banco, escriba esto en la fila 2 y rellene hacia abajo. La columna F es N.º y la columna G es Coincide.

=CONTAR.SI($D$2:D2,D2)

=SI(CONTAR.SI.CONJUNTO(Libros!$D:$D,D2,Libros!$E:$E,F2)>0,"Sí","No")

En la hoja Libros, haga lo mismo. La columna E es N.º y la columna F es Coincide.

=CONTAR.SI($D$2:D2,D2)

=SI(CONTAR.SI.CONJUNTO(Banco!$D:$D,D2,Banco!$F:$F,E2)>0,"Sí","No")

Ahora el primer $45.00 de cada hoja se empareja con el primero de la otra, y el segundo con el segundo. Filtre las dos hojas en No. En el ejemplo quedan tres filas de cada lado:

Hoja	Fila sin pareja	Importe
Banco	Cheque 1205	-540.00
Banco	Comisión por manejo de cuenta	-25.00
Banco	Intereses pagados	3.20
Libros	Cheque 1205	-450.00
Libros	Cheque 1207	-320.00
Libros	Cobro FACT-1043	1,275.00

Emparejar por importe puede juntar las filas equivocadas cuando dos cheques son por el mismo importe. El total sigue siendo correcto, pero podría anotar como pendiente el cheque equivocado, así que compare los números de cheque en la columna Reference.

Paso 3: diga por qué no coincidió cada fila

Agregue una columna Tipo: la columna H en Banco y la columna G en Libros. Dé a cada fila sin pareja una de estas etiquetas.

Tipo	En qué hoja	Qué significa	Ejemplo de arriba
Depósito en tránsito	Libros	Usted lo registró; el banco todavía no	FACT-1043, depositada el último día del mes
Cheque en circulación	Libros	Usted lo expidió; aún no se cobra	Cheque 1207
Solo banco	Banco	El banco lo registró; usted todavía no	Comisión e intereses
Error	Las dos	El mismo movimiento con dos importes distintos	Cheque 1205

Confirme cada etiqueta contra algo real. Un depósito en tránsito debe aparecer en el siguiente estado de cuenta en pocos días, y un cheque debe coincidir con su chequera.

El cheque 1205 es justo el tipo de error que este proceso existe para atrapar: $540.00 en el estado de cuenta, $450.00 en los libros. La diferencia de $90.00 se divide exactamente entre 9, que es la señal habitual de dos dígitos invertidos.

Paso 4: arme el formato de conciliación

En la hoja Conciliacion, escriba los dos saldos, el del estado de cuenta y el de sus libros, y luego use estas fórmulas. Ponga el nombre de cada línea en la columna A y los importes en la columna B, en las filas indicadas.

Fila	Línea	Columna B	Ejemplo
1	Saldo según estado de cuenta	Escríbalo del PDF	8,448.20
2	Más: depósitos en tránsito	Fórmula A	1,275.00
3	Menos: cheques en circulación	Fórmula B	-320.00
4	Saldo bancario ajustado	=SUMA(B1:B3)	9,403.20
5	Saldo según libros	Escríbalo de su contabilidad	9,515.00
6	Partidas solo del banco	Fórmula C	-21.80
7	Correcciones	Fórmula D	-90.00
8	Saldo en libros ajustado	=SUMA(B5:B7)	9,403.20
9	Diferencia	=REDONDEAR(B4-B8,2)	0.00

Fórmula A, depósitos en tránsito:

=SUMAR.SI.CONJUNTO(Libros!$D:$D,Libros!$G:$G,"Depósito en tránsito",Libros!$F:$F,"No")

Fórmula B, cheques en circulación:

=SUMAR.SI.CONJUNTO(Libros!$D:$D,Libros!$G:$G,"Cheque en circulación",Libros!$F:$F,"No")

Fórmula C, partidas solo del banco:

=SUMAR.SI.CONJUNTO(Banco!$D:$D,Banco!$H:$H,"Solo banco")

Fórmula D, correcciones:

=SUMAR.SI.CONJUNTO(Banco!$D:$D,Banco!$H:$H,"Error")-SUMAR.SI.CONJUNTO(Libros!$D:$D,Libros!$G:$G,"Error")

REDONDEAR quita el pequeño residuo decimal que Excel puede dejar al sumar centavos, que de otro modo mostraría una diferencia que no existe.

Si el error lo cometió el banco y no usted, agregue una línea del lado del banco y repórtelo al banco.

No tome un atajo en el paso 3

Es tentador saltarse las etiquetas y sumar todas las filas sin pareja de cada hoja al saldo contrario. Esa diferencia siempre da cero cuando los dos lados abrieron con el mismo saldo, aunque haya errores en los datos, porque cada fila simplemente se contó en los dos lados.

Las etiquetas son las que hacen que la revisión funcione. En el ejemplo, deje sin etiqueta el cheque 1205 y la línea Diferencia muestra -90.00, que lo lleva directo al problema. El atajo de sumar todo lo que no tiene pareja muestra 0.00 y lo esconde.

Si la diferencia no se va

Lo que ve	Causa probable	Qué hacer
La diferencia se divide exactamente entre 9	Dígitos invertidos, como 540 y 450	Compare los dígitos de las filas sin pareja
La diferencia es el doble del importe de una fila	Una fila tiene el signo equivocado	Busque una fila sin pareja que valga la mitad de la diferencia
La diferencia es igual al importe de una fila	Una fila no tiene etiqueta, o la tiene solo de un lado	Filtre la columna Tipo por vacíos entre las filas No
La diferencia es igual a una partida del mes anterior	Una partida pendiente no se arrastró	Agréguela a la hoja Libros
Aparece una diferencia por primera vez este mes, con filas limpias	Los saldos iniciales no cuadran con el mes anterior	Compare con el saldo ajustado del mes anterior
Las filas del estado de cuenta no suman su saldo final	Falta una fila o una página en los datos del estado de cuenta	Corrija la hoja Banco antes de conciliar

Cierre el mes

Una diferencia de cero significa que los dos registros coinciden, pero sus libros todavía no están terminados.

1. Registre las partidas del lado de los libros. En el ejemplo, registre la comisión de $25.00 y los intereses de $3.20, y corrija el cheque 1205 por $90.00. Su contabilidad muestra entonces $9,403.20, el saldo ajustado.
2. Arrastre las partidas abiertas. Copie FACT-1043 y el cheque 1207 al inicio de la hoja Libros de noviembre con sus etiquetas de Tipo, para que puedan coincidir cuando se cobren. Las fórmulas A y B los cuentan solo mientras Coincide sea No. Escriba el saldo en libros de noviembre desde su contabilidad en lugar de sumar la hoja, porque estas dos filas ya están incluidas en él.
3. Dé seguimiento a las partidas viejas. Un cheque que sigue abierto después de unos meses puede estar perdido o sin cobrar. Pregunte al beneficiario en lugar de arrastrarlo indefinidamente.
4. Guarde el libro. Conserve la conciliación con el estado de cuenta como la pista de auditoría del mes.

Para ponerse al día con varios meses, concílielos uno por uno, cada uno con sus hojas Banco y Libros y las partidas abiertas del mes anterior. Filtrar por mes una sola tabla combinada no funciona, porque CONTAR.SI, CONTAR.SI.CONJUNTO y SUMAR.SI.CONJUNTO también cuentan las filas ocultas. Si usa un software contable, puede hacer el emparejamiento una vez importado el estado de cuenta; vea Alegra o CONTPAQi.