Libro diario en Excel: cómo crear tu plantilla paso a paso

Una plantilla de libro diario en Excel te permite registrar cada asiento contable (fecha, cuenta, importe al Debe y al Haber) en una hoja que crece contigo y que puedes ordenar, filtrar y proteger. En este tutorial la montas desde una hoja en blanco y terminas con una plantilla de 8 columnas, fórmulas que arrastran el saldo fila a fila y un aviso visual si un asiento no cuadra. Funciona en Excel para Microsoft 365, Excel 2024, 2021, 2019 y 2016, en Excel para Mac y en Excel para la web.

A lo largo del artículo usaremos el mismo caso: una panadería que compra harina, vende pan, cobra facturas pendientes, paga la luz y paga la nómina. Cada paso deja la hoja más completa.

Qué es un libro diario y por qué tiene sentido tenerlo en Excel

Un libro diario es el registro cronológico de todos los hechos contables de una empresa: cada vez que compras, vendes, cobras o pagas, queda una fila con la fecha, las cuentas afectadas y el importe en el Debe y en el Haber. Por el principio de partida doble, la suma del Debe tiene que coincidir con la suma del Haber en cada asiento.

La panadería del ejemplo vende pan y compra harina, huevos y luz. Cada mañana registra qué ha pasado: la venta de pan a un cliente, el cobro de una factura pendiente, el pago del recibo de la luz. Con todo en una sola hoja, el dueño ve de un vistazo el movimiento del ejercicio y puede pasar después los saldos al libro mayor y a la cuenta de pérdidas y ganancias.

La estructura de una buena plantilla de libro diario

La plantilla tiene una fila de cabecera con el nombre del negocio y el ejercicio, una fila con los títulos de las columnas y, debajo, los asientos en orden cronológico. La estructura mínima que usaremos es esta:

Las cuentas usadas (430 Clientes, 472 IVA soportado, 477 IVA repercutido, 570 Caja, 572 Bancos, 600 Compras, 628 Suministros, 640 Sueldos y salarios, 700 Ventas, 475 Hacienda, acreedora por retenciones, 476 Seguridad Social acreedora) son las del PGC español; la misma idea sirve con cualquier otro plan contable.

Las fórmulas que hacen el trabajo por ti

La columna Saldo (G) lleva el acumulado Debe menos Haber asiento a asiento. Para que la fórmula se pueda arrastrar hacia abajo sin que se rompa, el primer rango se fija con dólares y el segundo se deja relativo:

=SUMA($E$2:E2)-SUMA($F$2:F2)

La fila final comprueba que el asiento cuadra. En una fila fuera de los datos (por ejemplo la 17) escribimos:

=SUMA(E2:E16)
=SUMA(F2:F16)

Si ambas celdas muestran el mismo importe, el ejercicio cuadra. Verás los totales reales al registrar los asientos en la sección siguiente.

Crea tu libro diario desde una hoja en blanco

Vas a montar la plantilla paso a paso: cabeceras, formatos y conversión a Tabla.

Poner las cabeceras y los formatos básicos

1. Haz clic en A1 y escribe Asiento. Pulsa Tab para pasar a B1 y escribe Fecha. Sigue con Cuenta (C1), Descripción (D1), Debe (E1), Haber (F1), Saldo (G1) y Documento (H1).
2. En la celda justo encima (por ejemplo una fila nueva insertada arriba) escribe el nombre de la panadería y, al lado, el ejercicio.
3. Selecciona A1:H1. En la pestaña Inicio, grupo Fuente, pon la fuente en Negrita (Ctrl+N).
4. Con el rango seleccionado, abre Inicio > Fuente > Bordes > Todos los bordes para que la cabecera tenga una cuadrícula fina.

Convertir el rango de datos en una Tabla

Convertir el rango en una Tabla te da filtros automáticos, formato a rayas y crecimiento automático al añadir filas.

Ahora cada columna tiene una flecha de filtro y, al escribir un nuevo asiento en la fila de abajo, la Tabla se amplía sola.

Registra tus primeros asientos en la plantilla

Con la plantilla lista, llega el momento de llenarla con cinco asientos reales de la panadería. Cada asiento lleva varias líneas: una por cada cuenta afectada. La columna G se calcula sola arrastrando la fórmula.

El IVA al tipo general en una sola línea

La primera compra es harina al contado. Es un asiento de tres líneas: el IVA soportado va a una cuenta aparte, la compra a 600 y el pago a 570 Caja.

Cobros, pagos y nóminas con sus cuentas

Seguimos con la venta de pan, el cobro de una factura pendiente, el pago de la luz y la nómina. Estos cuatro asientos completan el primer mes de la panadería:

Comprueba que cada asiento cuadra y suma el ejercicio

Para detectar descuadres de un vistazo, añade un formato condicional en las celdas que tengan que volver a cero al cierre de cada asiento y pon los totales del ejercicio al final.

Aplica la regla al rango G2:G16. Si un asiento no cuadra, la última línea de ese asiento queda en rojo. En la fila 17 (fuera de la Tabla para que no se desplace al añadir datos) escribe:

=SUMA(E2:E16)
=SUMA(F2:F16)

Si los dos importes coinciden, el ejercicio cuadra; si no, tienes una pista clara de dónde buscar.

Aviso visual si un asiento no cuadra

La regla anterior marca cualquier celda de G cuyo valor sea distinto de 0. Para que el aviso sea aún más útil, crea una segunda regla que solo se aplique a la última línea de cada asiento (filas 4, 7, 9, 11 y 14): la condición es Valor de la celda distinto de 0 con relleno rojo. Así detectas enseguida en qué asiento se rompió el equilibrio.

Por qué tu plantilla no funciona como esperabas

Cinco fallos típicos al montar el libro diario y cómo arreglarlos sin rehacer la hoja.

La fórmula del Saldo muestra #¡REF! al arrastrar

Si al copiar la fórmula hacia abajo alguna celda muestra #¡REF!, el problema es casi siempre que la referencia absoluta apuntaba a una columna que se borró. Vuelve a escribir la fórmula en G2 exactamente así y arrástrala hasta el final:

=SUMA($E$2:E2)-SUMA($F$2:F2)

El primer rango lleva dólares para que el inicio no se mueva; el segundo se queda relativo para que crezca fila a fila. Verás el saldo acumulado columna G limpio, sin errores.

El IVA sale descuadrado al cambiar el tipo

Si el 21 % estaba escrito a mano en cada celda y un día pasas al 10 %, los importes no se recalculan. Mete el tipo en una celda fija (por ejemplo K1 con el texto "Tipo general IVA" en J1) y úsala en la fórmula:

=importe*$K$1

Ahora basta cambiar K1 para que todas las líneas se actualicen.

La fila de totales suma líneas vacías y se desajusta

Si la fila de totales está dentro de la Tabla, al añadir asientos la Tabla la traga y los totales se quedan por debajo. Tienes dos opciones:

1. Saca los totales a una fila fija, fuera de la Tabla (por ejemplo la fila 17).
2. Si prefieres tenerlos dentro, usa SUBTOTALES (en inglés SUBTOTAL) para que solo sume las celdas visibles y siga funcionando con los filtros:

=SUBTOTALES(109;E2:E1000)

El 109 equivale a SUMA e ignora las filas ocultas por filtro.

Excel no reconoce el 1 de enero de 1900 como fecha

Una fecha anterior a 1900 queda como texto y no se ordena. Si te ocurre, selecciónala y usa el asistente para convertirla:

En el paso 3 del asistente elige DMY como formato de fecha y termina. Excel la convierte a número de serie y vuelve a ordenarla.

Protege la plantilla para no romper las fórmulas

Una vez que la plantilla funciona, conviene proteger la hoja para que las cabeceras y las fórmulas no se borren por accidente. Antes de proteger, desbloquea las celdas donde se van a escribir los datos:

1. Selecciona E2:F16 y B2:D16 (las celdas donde escribes importes, fechas y cuentas).
2. Pulsa Ctrl+1 para abrir Formato de celdas, ve a la pestaña Proteger y desmarca Bloqueada.
3. Ahora protege la hoja:

Guarda, comparte y lleva tu libro diario a otro Excel

El formato que elijas al guardar marca lo que podrás hacer después con la plantilla:

• .xlsx es el libro normal de Excel desde la versión 2007. Guarda fórmulas, formatos y todas las hojas.
• .xlsm es el libro con macros. Si más adelante le añades VBA, guárdalo así.
• .xls es el formato de Excel 97-2003 con un máximo de 65.536 filas.
• .csv guarda solo los valores de la hoja activa, sin fórmulas, sin formatos y sin el resto de hojas. Úsalo solo para mover datos a otro programa.

Para compartir la plantilla sin perder nada, guárdala como .xlsx y súbela a OneDrive o SharePoint; desde ahí se abre directamente en Excel para la web sin instalación y conserva los formatos. Ten en cuenta que Excel para la web no ejecuta macros VBA: si las necesitas, abre el archivo en el escritorio.

Lo que ya sabes hacer con tu libro diario y el siguiente paso

Repaso de lo que tienes entre manos:

• Una hoja con 8 columnas (Asiento, Fecha, Cuenta, Descripción, Debe, Haber, Saldo y Documento) y una Tabla que crece sola.
• Una fórmula de saldo acumulado en la columna G que se arrastra fila a fila.
• Una fila de totales con =SUMA(E2:E16) y =SUMA(F2:F16) que avisa si no cuadran.
• Un formato condicional que pinta en rojo las líneas que no cuadran.
• La hoja protegida para que las fórmulas no se borren por accidente.

El siguiente paso natural es vincular el libro diario con una hoja de Pérdidas y Ganancias que recoja los saldos de las cuentas de ingresos (grupo 7) y gastos (grupo 6) y calcule el resultado del ejercicio con un =SUMAR.SI.CONJUNTO(C:C;"6*";G:G) o =SUMAR.SI.CONJUNTO(C:C;"7*";G:G) aplicado al libro mayor.

Dudas al usar tu libro diario en Excel