Cómo hacer una conciliación bancaria en Excel, paso a paso
Plantilla de columnas, método de marcar partidas, fórmulas BUSCARV y COINCIDIR, el cuadro final y los errores típicos de la conciliación bancaria en Excel.
Por el equipo de Cuadre. Revisada el .
Una conciliación bancaria en Excel se hace con dos hojas, extracto y auxiliar de bancos, con las mismas columnas, una columna de marca en cada una para señalar lo que ya cruzó, y un cuadro final que parte del saldo del extracto y llega al saldo en libros explicando cada diferencia.
Excel sirve para conciliar, y muchas empresas cierran así cada mes; al final se dice dónde el método deja de servir. Qué es la conciliación y por qué en Colombia entra la DIAN está en conciliación bancaria en Colombia; el procedimiento con los XML incluidos, en cómo hacer la conciliación bancaria paso a paso.
Qué necesita antes de abrir Excel
- El extracto del mes en Excel o CSV, bajado del portal del banco. No el PDF: copiar cifras de un PDF es la primera fuente de errores.
- El auxiliar de bancos del mismo mes, exportado por la cuenta PUC de esa cuenta bancaria; vea cómo exportar el auxiliar de bancos de su software contable.
- Los dos cortados el mismo día y los saldos finales anotados antes de tocar nada. La diferencia entre ambos es la cifra que hay que explicar.
La plantilla: qué columnas lleva cada hoja
Las dos hojas tienen las mismas columnas en el mismo orden. Así las fórmulas se escriben una vez y se copian.
| Columna | Hoja Banco | Hoja Contabilidad | Para qué sirve |
|---|---|---|---|
| Fecha | Fecha del movimiento | Fecha del asiento | Ordenar y acotar la búsqueda |
| Referencia | Descripción del extracto | Comprobante: prefijo y número | Saber de qué se trata |
| Tercero | Lo que traiga el banco, si trae algo | Nombre y NIT del tercero | Distinguir dos pagos iguales |
| Débito | Salidas de la cuenta | Salidas de la cuenta | Lo que resta |
| Crédito | Entradas a la cuenta | Entradas a la cuenta | Lo que suma |
| Valor | Crédito menos débito, con signo | Crédito menos débito, con signo | La columna por la que se cruza |
| Marca | Fila de la otra hoja con la que cruzó | Fila de la otra hoja con la que cruzó | Lo que ya está resuelto |
| Causa | Vacía si cruzó; si no, por qué | Vacía si cruzó; si no, por qué | Alimenta el cuadro final |
Un detalle que ahorra horas: en el extracto, «crédito» es dinero que entra; en su contabilidad, la cuenta de bancos aumenta por el débito. Por eso conviene la columna Valor con signo desde el punto de vista de la empresa: entradas positivas, salidas negativas, en las dos hojas. Con =REDONDEAR(E2-D2;0) en Banco y =REDONDEAR(D2-E2;0) en Contabilidad, según de qué lado venga cada columna, los valores quedan comparables.
El método: marcar partidas hasta que no quede nada suelto
- Ordene las dos hojas por Valor y, dentro del valor, por fecha. Los valores pequeños y repetidos, como comisiones, se despachan primero.
- Cruce lo que coincide en valor y fecha. En la columna Marca de cada fila escriba el número de fila de la otra hoja. Puede empezar con fórmula (siguiente sección) y terminar a mano.
- Revise cada cruce donde el valor se repite. Si hay tres consignaciones de 500.000 pesos, mire el tercero y la descripción antes de dar por buena la pareja.
- Lo que queda sin marca en Banco son partidas que el banco tiene y la empresa no: comisiones, IVA, gravamen a los movimientos financieros, rendimientos, consignaciones por identificar. Escriba la causa.
- Lo que queda sin marca en Contabilidad son partidas que la empresa tiene y el banco no: cheques girados y no cobrados, transferencias en tránsito, asientos duplicados, valores mal digitados, asientos contra la cuenta equivocada. Escriba la causa.
- Busque errores por diferencia. Si un movimiento del banco no encuentra pareja y hay uno en contabilidad por un valor parecido, compare los dígitos: 1.254.000 frente a 1.245.000 es una transposición, y la diferencia es divisible entre 9.
- Lleve cada causa al cuadro final y cierre.
Cómo cruzar por valor con BUSCARV, COINCIDIR y CONTAR.SI
Las fórmulas hacen el primer pase. Con las hojas llamadas Banco y Contabilidad, el valor en la columna F y el separador de argumentos punto y coma, que es el de Excel en español.
- Encontrar la fila de la pareja, en la columna Marca de Banco:
=SI.ERROR(COINCIDIR(F2;Contabilidad!$F:$F;0);""). Devuelve el número de fila del primer asiento con ese valor, o vacío si no hay ninguno. La misma fórmula, con las hojas al revés, va en Contabilidad. - Traer el tercero de la pareja para revisarlo sin cambiar de hoja:
=SI(G2="";"";INDICE(Contabilidad!$C:$C;G2)). - Contar cuántas veces se repite un valor antes de confiar en el cruce:
=CONTAR.SI(Contabilidad!$F:$F;F2). Todo lo que dé más de 1 se revisa a mano. - BUSCARV hace lo mismo que COINCIDIR más INDICE cuando lo que quiere traer está a la derecha de la columna de valor, por ejemplo la marca de la pareja, para saber si ya la cruzó con otra fila:
=SI.ERROR(BUSCARV(F2;Contabilidad!$F:$G;2;FALSO);""). Con el cuarto argumento en FALSO, para que la coincidencia sea exacta. - Sumar lo que quedó sin cruzar en cada hoja, para el cuadro final:
=SUMAR.SI(G:G;"";F:F).
Tres límites. Cruzan por valor, no por tercero, así que dos pagos iguales de clientes distintos se cruzan al revés sin aviso. No cruzan un pago contra varias facturas. Y una consignación con retención no coincide con nada si el asiento se hizo por el total de la factura; ese caso se resuelve a mano.
El cuadro final: del saldo del extracto al saldo en libros
Se puede llegar por los dos lados y los dos deben dar lo mismo.
| Línea | Signo | De dónde sale |
|---|---|---|
| Saldo según extracto | Última fila del extracto | |
| Consignaciones en libros que el banco no muestra | más | Contabilidad sin marca, entradas |
| Cheques girados y transferencias en tránsito | menos | Contabilidad sin marca, salidas |
| Saldo conciliado | igual | |
| Saldo según libros | Auxiliar, saldo final | |
| Rendimientos y abonos del banco sin causar | más | Banco sin marca, entradas |
| Comisiones, IVA, GMF y cargos sin causar | menos | Banco sin marca, salidas |
| Errores de registro, con signo según el caso | más o menos | Las dos hojas, causa «error» |
| Saldo conciliado | igual | Debe ser el mismo de arriba |
Si los dos saldos conciliados coinciden, el mes cierra. Las partidas del segundo bloque van a la contabilidad, como nota bancaria y como corrección de los errores. La guía sobre comisiones, GMF e IVA en la nota bancaria explica con qué cuenta se registra cada una.
Errores típicos de la conciliación en Excel
- Valores como texto. El CSV del banco trae «1.254.000,00» y Excel lo lee como texto. Ninguna fórmula lo encuentra; conviértalo con Datos, Texto en columnas.
- Signos al revés. Se cruza el débito del banco con el débito de la contabilidad y nada coincide. Unifique con la columna Valor.
- Filas de subtotal en el auxiliar. El informe trae una fila de «Total» por día o por comprobante y las fórmulas la cruzan como si fuera un movimiento. Bórrelas antes de empezar.
- Cerrar con diferencia «pequeña». Una diferencia de 3.000 pesos suele ser dos errores de signo contrario que se compensan. Se explica o no se cierra.
- Conciliar sobre el archivo del mes pasado. Se pierde el historial. Un archivo por mes y por cuenta.
Dónde se rompe el método
Excel funciona mientras una persona pueda mirar cada partida. Deja de funcionar en tres puntos concretos.
- Volumen. Con cientos de movimientos al mes por cuenta, los valores repetidos son la norma, y el primer pase con fórmulas deja más para revisar a mano de lo que resuelve.
- Los documentos de la DIAN. Excel dice si el banco y la contabilidad coinciden, pero no qué factura respalda cada consignación. Para eso hay que bajar los XML del portal, que no permite descarga en bloque, y cruzarlos a mano contra cada movimiento con sus retenciones y pagos parciales. En Cuadre los XML se cargan en bloque con la extensión de Chrome y se buscan por fecha, folio o NIT desde el movimiento.
- Varias personas o varias empresas. Un archivo por mes y por cuenta, en el computador de quien concilia, sin registro de quién cruzó qué. Quien retoma empieza de cero.
La comparación punto por punto está en Cuadre frente a Excel, incluida la parte en que Excel sigue ganando; lo que hace cada módulo, en la lista de funciones, y los planes se contratan después de probar. Si quiere ver el mismo mes con las dos herramientas abiertas, puede cargar el extracto y el auxiliar en Cuadre durante 7 días sin tarjeta y comparar el resultado con su hoja. Si la hoja le sigue bastando, nada se pierde.