Plantilla de inventario de almacén en Excel: estructura, fórmulas y control de stock

Casi todas las plantillas de inventario que circulan por internet fallan por lo mismo: son una única tabla donde alguien teclea el stock actual encima del anterior. Funciona la primera semana. A la tercera, nadie sabe por qué la referencia que debería tener 40 unidades tiene 12, no hay forma de reconstruir qué pasó y el fichero acaba en una carpeta que nadie abre. La diferencia entre eso y un sistema que dura años no está en los colores ni en los gráficos: está en separar el dato maestro del movimiento.

Esta guía es para quien gestiona un almacén pequeño o mediano y necesita algo funcionando esta semana, sin licencias nuevas ni proyectos de meses. Vamos a montar la estructura, escribir las fórmulas con la sintaxis correcta en castellano y su equivalente en inglés (porque media documentación está en inglés y las traducciones automáticas de funciones causan más problemas de los que resuelven), y decir con claridad dónde está el techo de Excel.

Estructura de la hoja: qué columnas necesitas de verdad

La tentación es meter treinta columnas. Resiste. La regla es sencilla: si una columna no sirve para decidir una compra, un recuento, una ubicación o un precio, no entra. Cada campo de más es un campo que alguien dejará vacío o rellenará mal, y un campo mal rellenado es peor que un campo que no existe, porque genera confianza falsa.

Las once columnas del Maestro

El Maestro es el catálogo. Una fila por referencia, y ninguna referencia repetida. Estas son las columnas que se ganan el sitio:

Columna	Tipo de dato	Para qué sirve	Trampa habitual
SKU	Texto	Clave única de la referencia. Es lo que une todas las hojas	Formatearlo como número: se pierden los ceros a la izquierda
Descripción	Texto	Identificar el artículo en pantalla y en los albaranes	Meter la talla o el color aquí en vez de en su propia columna
Familia / subfamilia	Lista	Agrupar para tablas dinámicas, ABC y recuentos	Escribirla a mano y acabar con «Ferretería» y «ferreteria»
Proveedor	Lista	Agrupar el pedido de reposición por proveedor	Un solo proveedor cuando en realidad hay alternativo
Ubicación	Texto codificado	Encontrar el artículo sin preguntar a nadie	«Estantería del fondo» en vez de un código estructurado
Unidad de medida	Lista	Saber si 12 son unidades, cajas o metros	Mezclar unidades y cajas en la misma columna de cantidad
Coste unitario	Número, 4 decimales	Valorar existencias y calcular margen	Guardar el PVP en vez del coste
Stock mínimo	Número entero	Umbral bajo el que no quieres bajar nunca	Ponerlo a ojo y no revisarlo jamás
Stock máximo	Número entero	Tope de cobertura; sirve para calcular cuánto pedir	Dejarlo vacío y pedir siempre la misma cantidad
Plazo de entrega	Número (días)	Entra directamente en el punto de pedido	Usar el plazo que promete el comercial, no el real
Estado	Lista	Activo, descatalogado, bajo pedido	Borrar la fila del descatalogado y perder su histórico

Todo lo demás (stock actual, disponible, punto de pedido, semáforo, valor, rotación) son columnas calculadas. Nadie las teclea. Si alguien puede escribir encima de una fórmula, tarde o temprano lo hará.

Cómo montar el SKU para que no te dé guerra

Un SKU es una etiqueta interna, no una descripción. Debe ser corto, estable y legible por una persona que esté con las manos ocupadas. Un esquema que funciona bien es familia de tres letras, secuencial de cuatro dígitos y variante de dos: TOR-0142-M8. Tres reglas que evitan disgustos:

• Sin espacios ni caracteres raros. Guiones sí, barras y comillas no. Los espacios al final son invisibles y rompen los BUSCARV. Limpia con =ESPACIOS(A2), que en inglés es TRIM, y unifica mayúsculas con =MAYUSC(A2), o sea UPPER.
• Formato de celda Texto antes de escribir nada. Si la columna está en General y el SKU es 0034512, Excel te deja 34512 y ya no cuadra con la etiqueta física.
• El SKU no cambia nunca. Si cambia el proveedor, el envase o el precio, se actualiza la fila; el SKU se queda. Solo se crea un SKU nuevo cuando el artículo es realmente otro para quien lo consume.

Para detectar duplicados antes de que hagan daño, deja una columna auxiliar oculta con =CONTAR.SI(Maestro[SKU];[@SKU]) (COUNTIF) y píntala en rojo cuando el resultado sea mayor que 1.

Ubicaciones: un código, no una frase

La ubicación es lo que convierte un listado en un almacén. El estándar de facto es pasillo, estantería, altura y hueco, con separadores fijos y ancho fijo: A-03-2-04 es el pasillo A, la estantería 3, la altura 2 y el hueco 4. Ancho fijo importa porque permite ordenar alfabéticamente y obtener la ruta de picking en el orden en que se recorre el almacén, sin programar nada.

Si tienes varios almacenes, añade un prefijo (VLC/A-03-2-04) en vez de crear una hoja por almacén. Una hoja por almacén multiplica las fórmulas y garantiza que dentro de seis meses una de ellas esté desactualizada.

Unidad de medida y factor de conversión

Este es el error silencioso más caro. Compras en cajas de 24, consumes en unidades y alguien apunta «entrada: 5». ¿Cinco cajas o cinco unidades? Dos columnas lo resuelven: unidad base (siempre la de consumo) y unidades por caja. La hoja de movimientos registra siempre en unidad base, y si el albarán viene en cajas, la conversión se hace en el momento de registrar:

=[@[Cajas recibidas]]*BUSCARX([@SKU];Maestro[SKU];Maestro[Unidades por caja];1;0) EN: =[@[Cajas recibidas]]*XLOOKUP([@SKU];Maestro[SKU];Maestro[Unidades por caja];1;0)

El cuarto argumento de BUSCARX es el valor que devuelve si no encuentra la referencia. Ponerlo a 1 en vez de dejar el error hace que un SKU no dado de alta no te reviente la cadena entera de cálculos, pero conviene además marcarlo en el semáforo de calidad de datos para que no pase inadvertido.

Una plantilla de inventario no se juzga por lo bonita que es el día que la montas, sino por lo poco que se degrada cuando la usan tres personas con prisa durante un año.

Las tres hojas del sistema: Maestro, Movimientos y Panel

La arquitectura de tres hojas es lo que separa una tabla de un sistema. Los datos que casi nunca cambian viven en un sitio, los que cambian a diario viven en otro, y el resumen no guarda nada: solo lee.

Hoja Movimientos: el libro de entradas y salidas

Cada línea es un hecho que ocurrió. Nunca se edita una línea antigua; si algo estaba mal, se añade una línea de corrección. Estas son las columnas:

• Fecha (y hora si mueves mucho volumen al día).
• SKU, con validación de datos contra el Maestro.
• Tipo: ENTRADA, SALIDA, DEVOLUCIÓN, AJUSTE, TRASPASO. Lista cerrada.
• Cantidad, siempre positiva; el signo lo pone el tipo.
• Documento: número de albarán, pedido o parte. Es lo que te permite auditar hacia atrás.
• Ubicación origen y ubicación destino, para los traspasos internos.
• Usuario: quién lo registró. Sin esto, ningún descuadre se puede investigar.
• Notas: opcional, texto libre, y que se quede en opcional.

Hay dos escuelas para el signo. La primera guarda la cantidad siempre positiva y usa la columna Tipo para sumar o restar; es la más legible para quien introduce datos. La segunda guarda la cantidad ya con signo (negativa en las salidas) y simplifica muchísimo las fórmulas. Si el fichero lo va a usar gente que no es de perfil técnico, quédate con la primera; si lo vas a mantener tú, la segunda te ahorra la mitad de los SUMAR.SI.CONJUNTO.

Por qué el stock no se edita a mano

Porque el stock es una consecuencia, no un dato. Si lo tecleas, pierdes las tres cosas que hacen útil un inventario: el histórico (no sabes qué pasó), la trazabilidad (no sabes quién lo hizo) y la conciliación (no puedes comparar teórico y físico, porque el teórico ya lo has machacado). Una hoja de movimientos de 30.000 líneas ocupa poco y te permite responder en treinta segundos a «¿cuándo entró este lote y con qué albarán?». Un número tecleado no responde a nada.

Convierte los rangos en tablas de Excel

Selecciona el rango y pulsa Ctrl+T. Después, en la pestaña Diseño de tabla, cambia el nombre a Maestro y Movimientos. Ganas cuatro cosas de golpe: las fórmulas se escriben con referencias estructuradas (Movimientos[Cantidad] en vez de Hoja2!$D$2:$D$5000), las filas nuevas heredan solas las fórmulas y el formato, los rangos crecen sin que tengas que tocar nada, y las tablas dinámicas y los gráficos se actualizan sin volver a definir el origen. Trabajar con rangos fijos del tipo D2:D5000 es la causa número uno de plantillas que dejan de calcular bien el día que se pasa de la fila 5000.

Fórmulas de Excel para el stock disponible

Aquí está el corazón del asunto. Ojo con la sintaxis: en la versión española con configuración regional de España, el separador de argumentos es el punto y coma y el separador decimal es la coma. Si copias una fórmula de una web en inglés, además de traducir el nombre de la función tendrás que cambiar las comas por puntos y comas.

Stock físico con SUMAR.SI.CONJUNTO

Con el esquema de cantidad positiva más columna Tipo, el stock físico de cada referencia es la suma de entradas menos la suma de salidas:

=SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[SKU];[@SKU];Movimientos[Tipo];"ENTRADA") +SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[SKU];[@SKU];Movimientos[Tipo];"DEVOLUCIÓN") -SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[SKU];[@SKU];Movimientos[Tipo];"SALIDA") +SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[SKU];[@SKU];Movimientos[Tipo];"AJUSTE") EN: =SUMIFS(...)+SUMIFS(...)-SUMIFS(...)+SUMIFS(...)

Si has optado por guardar la cantidad ya con signo, todo esto se colapsa en una sola función:

=SUMAR.SI(Movimientos[SKU];[@SKU];Movimientos[Cantidad]) EN: =SUMIF(Movimientos[SKU];[@SKU];Movimientos[Cantidad])

Fíjate en el orden de los argumentos, que es la confusión más repetida: SUMAR.SI pide primero el rango donde busca, luego el criterio y por último el rango que suma. SUMAR.SI.CONJUNTO lo hace al revés: primero el rango que suma y después los pares de rango y criterio. No es un capricho de la traducción, es así también en inglés.

Stock disponible, comprometido y en tránsito

El stock físico es lo que hay en la estantería. El comprometido es lo que ya está vendido pero todavía no ha salido. El en tránsito es lo que has pedido al proveedor y aún no ha llegado. Confundirlos es lo que provoca vender lo que ya está reservado:

Disponible =[@[Stock físico]]-[@Comprometido] Proyectado =[@[Stock físico]]-[@Comprometido]+[@[En tránsito]]

La alerta de reposición se calcula siempre contra el proyectado, no contra el disponible. Si no, el sistema te pedirá otra vez lo que ya has pedido y acabarás con el doble de stock del que necesitas. La promesa de entrega al cliente, en cambio, se hace contra el disponible.

Traer datos del Maestro: BUSCARX, BUSCARV e ÍNDICE con COINCIDIR

Para que la hoja de movimientos muestre la descripción y el coste sin duplicar el dato:

Moderna (Microsoft 365 / Excel 2021+): =BUSCARX([@SKU];Maestro[SKU];Maestro[Descripción];"SKU no dado de alta";0) EN: =XLOOKUP([@SKU];Maestro[SKU];Maestro[Descripción];"SKU no dado de alta";0) Compatible con Excel 2016 y 2019: =SI.ERROR(BUSCARV([@SKU];Maestro!$A:$K;2;FALSO);"SKU no dado de alta") EN: =IFERROR(VLOOKUP([@SKU];Maestro!$A:$K;2;FALSE);"SKU no dado de alta") Robusta ante columnas insertadas: =ÍNDICE(Maestro[Descripción];COINCIDIR([@SKU];Maestro[SKU];0)) EN: =INDEX(Maestro[Descripción];MATCH([@SKU];Maestro[SKU];0))

Tres avisos que ahorran tardes enteras. El cuarto argumento de BUSCARV tiene que ser FALSO siempre: con VERDADERO hace coincidencia aproximada y te devuelve el artículo de al lado sin dar ningún error. BUSCARV solo mira a la derecha, así que la columna del SKU debe ser la primera del rango. Y ÍNDICE con COINCIDIR no se rompe cuando alguien inserta una columna en medio del Maestro, cosa que a BUSCARV con índice numérico le pasa siempre.

Tabla de equivalencias de funciones, castellano e inglés

Castellano	Inglés	Para qué la usas en el inventario
SUMAR.SI	SUMIF	Stock cuando la cantidad ya lleva signo
SUMAR.SI.CONJUNTO	SUMIFS	Entradas y salidas por tipo y por fecha
CONTAR.SI.CONJUNTO	COUNTIFS	Número de movimientos, referencias en rotura
BUSCARX	XLOOKUP	Traer descripción, coste o ubicación
BUSCARV	VLOOKUP	Lo mismo en Excel 2016 y 2019
ÍNDICE + COINCIDIR	INDEX + MATCH	Búsqueda que no se rompe al insertar columnas
SI.ERROR	IFERROR	Evitar divisiones entre cero y SKU inexistentes
SI.CONJUNTO	IFS	Semáforo de estado sin anidar cinco SI
REDONDEAR.MAS	ROUNDUP	Punto de pedido y cantidad a pedir
DESVEST.M	STDEV.S	Variabilidad de la demanda
DISTR.NORM.ESTAND.INV	NORM.S.INV	Coeficiente del nivel de servicio
SUMAPRODUCTO	SUMPRODUCT	Valor total de existencias en una celda
INDIRECTO	INDIRECT	Listas desplegables dependientes
RESIDUO	MOD	Dígito de control del código de barras
FILTRAR	FILTER	Lista dinámica de referencias a reponer
ÚNICOS	UNIQUE	Lista de proveedores o familias sin repetir

BUSCARX, FILTRAR, ÚNICOS y ORDENAR necesitan Microsoft 365 o Excel 2021 en adelante. Si compartes el fichero con alguien que tiene Excel 2016 o 2019, esas fórmulas le aparecerán con el prefijo _xlfn. y un error. Antes de decidir la arquitectura, pregunta qué versión usa la persona que menos actualizada esté.

Alertas de reposición: stock mínimo, stock de seguridad y punto de pedido

Tres conceptos que se usan como sinónimos y no lo son. El stock mínimo es una línea roja que fijas tú a criterio. El stock de seguridad es el colchón que cubre la variabilidad de la demanda durante el plazo de entrega. El punto de pedido es el nivel en el que hay que lanzar la compra para que llegue antes de agotarte. Solo el tercero sirve para automatizar decisiones.

Demanda diaria media

Se calcula sobre las salidas reales de un periodo reciente. Noventa días es un punto de equilibrio razonable entre reaccionar rápido y no volverse loco con el ruido semanal:

=SI.ERROR( SUMAR.SI.CONJUNTO(Movimientos[Cantidad]; Movimientos[SKU];[@SKU]; Movimientos[Tipo];"SALIDA"; Movimientos[Fecha];">="&HOY()-90)/90; 0) EN: =IFERROR(SUMIFS(...;Movimientos[Fecha];">="&TODAY()-90)/90;0)

La parte que más se atasca es ">="&HOY()-90: el operador va entre comillas y se concatena con el ampersand a la fecha calculada. Escribirlo todo dentro de las comillas no funciona, porque Excel lo interpretaría como texto literal.

Stock de seguridad

La versión sencilla es fijarlo como un número de días de consumo: si quieres cubrir siete días, es la demanda diaria media por siete. La versión estadística tiene en cuenta cuánto varía esa demanda y qué nivel de servicio quieres dar:

=DISTR.NORM.ESTAND.INV([@[Nivel servicio]]) *DESVEST.M([@[Serie demanda diaria]]) *RAÍZ([@[Plazo entrega]]) EN: =NORM.S.INV([@[Nivel servicio]])*STDEV.S(...)*SQRT([@[Plazo entrega]])

El nivel de servicio se expresa como probabilidad: 0,95 significa que aceptas quedarte sin stock en un 5 % de los ciclos de reposición. Subirlo a 0,99 dispara el colchón, y ahí está la decisión de negocio: cuánto capital inmovilizas para no fallar nunca. Esta fórmula asume que la demanda se comporta de forma más o menos normal y que el plazo de entrega es estable; si tienes un artículo estacional o un proveedor errático, el resultado hay que mirarlo con escepticismo y ajustarlo a mano.

Punto de pedido y semáforo

Punto de pedido: =REDONDEAR.MAS([@[Demanda diaria]]*[@[Plazo entrega]]+[@[Stock seguridad]];0) EN: =ROUNDUP([@[Demanda diaria]]*[@[Plazo entrega]]+[@[Stock seguridad]];0) Semáforo con SI anidado (funciona en todas las versiones): =SI([@Proyectado]<=0;"ROTURA"; SI([@Proyectado]<=[@[Punto de pedido]];"REPONER"; SI([@Proyectado]>=[@[Stock máximo]];"EXCESO";"OK"))) Semáforo con SI.CONJUNTO (Excel 2019 en adelante): =SI.CONJUNTO([@Proyectado]<=0;"ROTURA"; [@Proyectado]<=[@[Punto de pedido]];"REPONER"; [@Proyectado]>=[@[Stock máximo]];"EXCESO"; VERDADERO;"OK") Cantidad sugerida de pedido: =MAX(0;[@[Stock máximo]]-[@Proyectado])

El último argumento de SI.CONJUNTO tiene que ser VERDADERO (TRUE) seguido del valor por defecto. Si no lo pones y ninguna condición se cumple, la función devuelve #N/D y te ensucia todo el panel.

Para tener la lista de compras del día en una sola celda, con Microsoft 365:

=FILTRAR(Maestro[[SKU]:[Descripción]];Maestro[Estado alerta]="REPONER";"Nada que reponer hoy") EN: =FILTER(Maestro[[SKU]:[Descripción]];Maestro[Estado alerta]="REPONER";"Nada que reponer hoy")

Un apunte para no llevarse una decepción: Excel no manda correos ni notificaciones por sí solo. La alerta es visual y alguien tiene que mirar la hoja. Si necesitas un aviso que llegue al móvil, eso ya requiere una macro con el libro abierto, una automatización externa o directamente otra herramienta.

Validación de datos y listas desplegables

El 80 % de los descuadres de una plantilla de inventario no vienen de las fórmulas: vienen de que alguien escribió TOR 0142 con espacio en vez de TOR-0142. La validación de datos es barata y evita casi todos esos casos.

Lista simple contra el Maestro

Selecciona la columna SKU de la hoja Movimientos, ve a Datos → Validación de datos, elige Permitir: Lista y en Origen escribe =Maestro[SKU]. En la pestaña Mensaje de error, deja el estilo en Detener: así Excel rechaza cualquier valor que no esté en el catálogo, en vez de limitarse a avisar. Repite la operación con Tipo (con la lista literal ENTRADA;SALIDA;DEVOLUCIÓN;AJUSTE;TRASPASO escrita directamente en el origen) y con Ubicación.

Listas dependientes con INDIRECTO

Si quieres que al elegir la familia se filtren las subfamilias, el truco clásico son los nombres definidos. Crea un rango con nombre por cada familia (los nombres no admiten espacios, así que sustituye los espacios por guiones bajos) y en la validación de la segunda columna pon:

=INDIRECTO(SUSTITUIR($B2;" ";"_")) EN: =INDIRECT(SUBSTITUTE($B2;" ";"_"))

Ancla la columna con el dólar y deja la fila libre, para que al arrastrar hacia abajo cada fila mire su propia familia. Dos limitaciones que conviene conocer antes de montarlo: INDIRECTO es una función volátil, o sea que recalcula constantemente y ralentiza libros grandes, y no funciona si el rango al que apunta está en un libro cerrado.

Bloquear las fórmulas y proteger la hoja

Por defecto todas las celdas de Excel están marcadas como bloqueadas, pero ese bloqueo solo tiene efecto cuando proteges la hoja. El orden correcto es al revés de lo que parece: primero se quita el bloqueo a la hoja entera y luego se vuelve a poner solo donde interesa.

1. Selecciona toda la hoja y en Formato de celdas → Proteger desmarca «Bloqueada».
2. Selecciona ahora solo las columnas calculadas y marca «Bloqueada».
3. Ve a Revisar → Proteger hoja, pon contraseña y deja marcado «Seleccionar celdas desbloqueadas».

La contraseña de hoja de Excel no es un mecanismo de seguridad serio: evita el accidente, no al que quiere saltárselo. Sirve exactamente para lo que la necesitas, que es que nadie machaque una fórmula sin darse cuenta.

Una plantilla que no valida lo que se escribe no es una plantilla de inventario: es un cuaderno con cuadrícula.

Formato condicional para ver el stock bajo

El formato condicional es lo que hace que abras el fichero y en dos segundos sepas qué hay que comprar. Se aplica por fórmula, no por valor, porque quieres pintar la fila entera en función de una comparación entre columnas.

Selecciona el rango de datos empezando por la primera fila de datos (no por la cabecera), ve a Inicio → Formato condicional → Nueva regla → Utilice una fórmula y escribe estas tres, en este orden:

Rotura de stock (rojo intenso): =$H2<=0 Por debajo del punto de pedido (ámbar): =Y($H2>0;$H2<=$K2) Exceso sobre el máximo (azul): =Y($K2>0;$H2>=$L2) EN: =AND(...) en lugar de =Y(...)

Donde H es la columna del stock proyectado, K la del punto de pedido y L la del stock máximo. Tres detalles que explican el 90 % de los formatos condicionales que «no funcionan»:

• El dólar va solo delante de la letra: $H2, no $H$2 ni H2. Con la columna anclada y la fila libre, la regla se propaga bien hacia abajo y pinta la fila completa.
• La fórmula se escribe pensando en la celda activa del rango seleccionado, que es la de arriba a la izquierda. Si seleccionaste desde la fila 2, la fórmula habla de la fila 2.
• El orden de las reglas manda. Excel las evalúa de arriba abajo y la primera que se cumple gana si marcas «Detener si es verdad». Si la regla de exceso está por encima de la de rotura, verás azules donde debería haber rojos.

Para las fechas de caducidad, una barra de datos o una escala de color no dicen gran cosa; funciona mejor una regla por fórmula sobre los días que quedan: =Y($M2<>"";$M2-HOY()<=30). El <>"" del principio evita que las celdas vacías se pinten como si caducasen mañana, que es lo que hace Excel si no se lo dices, porque una celda vacía vale cero y cero es el 0 de enero de 1900.

Códigos de barras y lector: entradas y salidas sin teclear

Un lector de códigos de barras es la mejora con mejor relación entre lo que cuesta y lo que ahorra en un almacén pequeño. No porque vaya más rápido, que también, sino porque elimina la clase de error más difícil de encontrar: la referencia mal tecleada que existe.

El lector es un teclado

Prácticamente cualquier lector USB o Bluetooth de gama básica trabaja en modo HID, es decir, se comporta como un teclado: lee el código, lo escribe en la celda activa y envía un Intro. No hay que programar nada ni instalar drivers. Lo único que preparas en Excel es la hoja de captura:

• Una hoja aparte con dos o tres columnas: SKU escaneado, cantidad y tipo de movimiento.
• La columna del código, formateada como Texto antes de escanear nada.
• En Archivo → Opciones → Avanzadas, deja «Después de presionar Entrar, mover selección» hacia abajo, para que cada lectura caiga en la fila siguiente.
• Un botón o una fórmula que vuelque esas líneas a la hoja Movimientos con la fecha y el usuario ya rellenos.

La mayoría de lectores permiten programar un sufijo (tabulador en lugar de Intro) escaneando un código de configuración del manual. Con tabulador, el cursor salta a la derecha y puedes escanear el SKU, teclear la cantidad y seguir; suele ser más cómodo para recepciones de mercancía.

EAN-13, CODE 128 y el problema de los ceros

El EAN-13 es el código de producto que ves en el comercio: trece dígitos, el último de los cuales es un dígito de control calculado a partir de los doce anteriores. Los prefijos de país los administra GS1 y para usar EAN propios hay que darse de alta en esa organización; para uso puramente interno no hace falta, y ahí es donde encaja el CODE 128, que admite letras y números y es el que usarás para codificar tus SKU y tus ubicaciones.

Excel no genera códigos de barras de forma nativa. Hay dos caminos: instalar una fuente tipográfica de códigos de barras (funciona bien para CODE 39, que necesita asteriscos como delimitadores, y peor para CODE 128, que requiere calcular un carácter de control) o generar las etiquetas con una herramienta específica y combinar correspondencia desde Word. Para volúmenes pequeños la fuente sirve; en cuanto imprimas cientos de etiquetas, la herramienta dedicada compensa.

Si trabajas con EAN-13, este es el cálculo del dígito de control a partir de los doce primeros dígitos en A2, teniendo la celda formateada como texto:

=RESIDUO(10-RESIDUO(SUMAPRODUCTO( --EXTRAE(A2;FILA(INDIRECTO("1:12"));1); SI(ES.PAR(FILA(INDIRECTO("1:12")));3;1));10);10) EN: =MOD(10-MOD(SUMPRODUCT(--MID(A2;ROW(INDIRECT("1:12"));1); IF(ISEVEN(ROW(INDIRECT("1:12")));3;1));10);10)

Las posiciones impares pesan 1 y las pares pesan 3; se suma todo, se toma el resto entre 10 y se resta de 10, y el RESIDUO exterior convierte el caso del 10 en 0. En Excel 2016 y 2019 hay que confirmarla con Ctrl+Mayús+Intro; en Microsoft 365 se calcula sola. Y un aviso de sintaxis: dentro de una constante matricial escrita a mano, la versión en castellano separa las columnas con la barra invertida y las filas con punto y coma, cosa que rompe casi todas las fórmulas que copies de webs en inglés. La versión de arriba evita ese problema porque no usa constantes matriciales.

Formato Texto: la trampa de los ceros y la notación científica

Un código que empieza por cero pierde el cero si la columna está en General. Uno de trece dígitos se convierte en 8,41234E+12 y ya no hay quien lo recupere sin reescribirlo. Formatea la columna como Texto antes de pegar o escanear nada: si lo haces después, Excel no te devuelve el dato original, solo cambia cómo muestra lo que ya destruyó. Si el mal ya está hecho y aún tienes el fichero de origen, reimporta con el asistente de texto marcando esa columna como Texto, o usa Power Query especificando el tipo de dato en el paso de importación.

Inventario físico frente a teórico y recuento cíclico

El inventario teórico es lo que dice tu hoja. El físico es lo que hay cuando alguien va y lo cuenta. La diferencia entre ambos es la merma, y siempre existe: roturas que nadie apuntó, errores de picking, unidades que entraron sin registrar, mermas naturales, robos. El objetivo no es que la diferencia sea cero, es que sea pequeña, conocida y explicable.

Recuento cíclico frente a inventario general

El inventario general obliga a parar el almacén, se hace mal porque se hace deprisa y da una foto que caduca al día siguiente. El recuento cíclico reparte el trabajo: cada día se cuentan unas pocas referencias siguiendo un calendario ligado a la clasificación ABC. Un ritmo que funciona bien:

Clase	Peso sobre el valor	Frecuencia de recuento	Tolerancia de desviación
A	Alrededor del 80 % del valor con pocas referencias	Mensual	Muy estrecha: cualquier diferencia se investiga
B	Un tramo intermedio de valor y de número	Trimestral	Se investiga a partir de un umbral acordado
C	Muchas referencias con poco valor acumulado	Semestral o anual	Se ajusta sin investigar salvo desviación grande

Además del cíclico, hay un recuento al cierre del ejercicio. El Código de Comercio obliga a los empresarios a llevar una contabilidad ordenada que incluye un libro de inventarios y cuentas anuales, y las existencias finales entran directamente en el resultado del ejercicio. El calendario y el detalle concreto que necesita tu empresa conviene cerrarlo con tu asesor, porque cambia según el régimen y el tipo de actividad.

La hoja de recuento y la fórmula de exactitud

Monta una hoja Recuento con SKU, ubicación, teórico congelado en el momento del conteo, contado, diferencia y motivo. Que el contador no vea el teórico: si lo ve, cuenta lo que espera encontrar. Se imprime la hoja sin esa columna o se oculta.

Diferencia: =[@Contado]-[@Teórico] Diferencia en €: =[@Diferencia]*BUSCARX([@SKU];Maestro[SKU];Maestro[Coste unitario];0;0) Exactitud del inventario (IRA), en el panel: =1-SUMAPRODUCTO(ABS(Recuento[Contado]-Recuento[Teórico]))/SUMA(Recuento[Teórico]) EN: =1-SUMPRODUCT(ABS(...))/SUM(...)

Fíjate en que se usa el valor absoluto de cada diferencia: si sumas diferencias con signo, un sobrante de 10 en una referencia tapa un faltante de 10 en otra y el indicador te dice que todo va perfecto cuando tienes dos errores. Un almacén razonablemente ordenado se mueve en exactitudes altas; el número concreto que debes exigirte depende de tu sector y de lo que te cueste una rotura.

Cómo se registra el ajuste

Nunca escribiendo encima del stock. El ajuste es un movimiento más: fecha, SKU, tipo AJUSTE, cantidad con el signo de la diferencia, documento con el número de acta de recuento, usuario y motivo. Así el histórico sigue cuadrando, puedes contar cuántos ajustes lleva cada referencia y cada motivo, y al cabo de unos meses tienes un dato valiosísimo: qué ubicaciones y qué familias generan el descuadre. Casi siempre son cuatro o cinco referencias las culpables de la mitad del problema, y suelen tener algo en común (están en alto, se sirven en fracciones, o comparten hueco con una parecida).

Valoración de existencias: FIFO y precio medio ponderado

Aquí toca prudencia, porque esto ya no es organización interna: acaba en las cuentas anuales y en la base imponible. Lo que sigue es divulgativo y describe el marco general; la decisión y su reflejo contable los cierras con tu asesor o con un economista colegiado.

El Plan General de Contabilidad valora las existencias por su precio de adquisición o coste de producción, e incluye en ese precio de adquisición el importe facturado por el vendedor menos descuentos, más los gastos adicionales que se produzcan hasta que los bienes estén en el almacén: transporte, aranceles, seguro. Los impuestos indirectos recuperables, como el IVA soportado deducible, no se incluyen. Para bienes intercambiables entre sí, admite con carácter general el precio medio ponderado y también el FIFO, primera entrada primera salida. El LIFO no está admitido. El método elegido se aplica de manera uniforme a bienes de naturaleza y uso similares, y no se cambia de un ejercicio a otro sin motivo justificado.

Precio medio ponderado paso a paso

Es el más fácil de llevar en una hoja de cálculo porque solo necesitas dos números por referencia: unidades en stock y coste medio actual. Cada vez que entra mercancía, recalculas:

Nuevo coste medio = (Stock_anterior*Coste_medio_anterior + Unidades_entrada*Precio_entrada) / (Stock_anterior + Unidades_entrada) En la hoja, con la fila anterior en las columnas F (stock) y G (coste medio): =SI.ERROR((F2*G2+[@Cantidad]*[@[Precio unitario]])/(F2+[@Cantidad]);[@[Precio unitario]])

Las salidas no modifican el coste medio: reducen unidades al coste medio vigente. Es el punto donde más gente se equivoca, porque intuitivamente parece que una venta debería recalcular algo. No.

FIFO en Excel: capas de coste

FIFO no se resuelve con una fórmula en una celda, y quien te venda lo contrario está simplificando. Necesitas mantener capas: cada entrada crea una capa con su fecha, sus unidades y su coste, y cada salida va consumiendo las capas más antiguas hasta cubrir la cantidad. Se puede montar de tres formas:

• Tabla de capas con columna de saldo restante y una consulta que reparte cada salida entre las capas ordenadas por fecha. Es lo más transparente y lo que mejor se audita, pero requiere disciplina.
• Power Query con una suma acumulada por SKU y fecha, que asigna cada unidad vendida a su capa correspondiente. Es la vía más limpia si el volumen es medio.
• Macro en VBA que recorre el histórico. Funciona, pero convierte tu plantilla en algo que solo entiende quien la programó, y eso tiene su propio coste.

Un atajo honesto: si tus precios de compra apenas se mueven, la diferencia entre FIFO y precio medio ponderado en el valor final de existencias será pequeña. En ese caso, el precio medio ponderado te da el 95 % del resultado con el 20 % del trabajo. Cuando los precios de compra se mueven mucho, la diferencia sí importa y merece la pena montarlo bien.

Deterioro y valor neto realizable

Si el valor neto realizable de una existencia (lo que esperas obtener al venderla, menos los costes necesarios para venderla) es inferior a su precio de adquisición, hay que reconocer una corrección valorativa por deterioro. En la práctica, esto afecta a lo obsoleto, lo deteriorado y lo estacional que se quedó sin vender. En la hoja lo puedes anticipar con una columna de antigüedad del stock:

Días sin movimiento de salida: =SI.ERROR(HOY()-MAX.SI.CONJUNTO(Movimientos[Fecha];Movimientos[SKU];[@SKU];Movimientos[Tipo];"SALIDA");"Sin salidas") EN: =IFERROR(TODAY()-MAXIFS(...);"Sin salidas")

MAX.SI.CONJUNTO (MAXIFS) está disponible desde Excel 2019 y en Microsoft 365. En versiones anteriores se resuelve con una fórmula matricial de MAX y SI. Una referencia con muchos meses sin una sola salida es candidata a revisión: puede que haya que liquidarla, y desde luego no hay que reponerla.

Rotación, días de cobertura y análisis ABC

Con la estructura montada, estos indicadores salen casi solos y son los que convierten la hoja en una herramienta de gestión y no solo de recuento.

Rotación y cobertura

Rotación = Coste de las ventas del periodo / Existencias medias a coste =SI.ERROR([@[Coste ventas]]/PROMEDIO([@[Existencias inicio]];[@[Existencias fin]]);"") Días de cobertura: =SI.ERROR(365/[@Rotación];"") Valor total del almacén, en una sola celda del panel: =SUMAPRODUCTO(Maestro[Stock físico];Maestro[Coste unitario]) EN: =SUMPRODUCT(...)

Una rotación de 6 significa que has renovado el almacén seis veces en el año, equivalente a unos 61 días de cobertura. No hay un número bueno universal: en alimentación fresca una rotación baja es una catástrofe, y en recambios industriales una rotación alta puede significar que estás dando un servicio pésimo. Lo que sí es siempre informativo es la tendencia y la dispersión: si la rotación media es 6 pero tienes cincuenta referencias por debajo de 1, ahí está tu dinero parado.

Análisis ABC

El ABC ordena las referencias por el valor que consumen en el año y las agrupa en tres bloques. La mecánica en Excel, en cuatro columnas:

1. Valor de consumo anual: unidades salidas en el año por coste unitario.
2. Ordenar de mayor a menor por esa columna (o usar ORDENAR si tienes Microsoft 365).
3. Porcentaje acumulado con una suma parcial anclada: =SUMA($D$2:D2)/SUMA($D:$D). El truco es el ancla mixta del principio del rango; al arrastrar, el rango crece solo.
4. Clase: =SI(E2<=0,8;"A";SI(E2<=0,95;"B";"C")). Recuerda la coma decimal de la configuración española.

Los cortes en 80 % y 95 % son la convención habitual, no una ley: si tu curva es muy plana, ajústalos. Lo que hagas con cada clase importa más que los cortes exactos. Las A se cuentan a menudo, se negocian con el proveedor y no se quedan sin stock. Las C se piden en lotes grandes y espaciados, porque el coste de gestionar cada pedido supera al de tenerlas paradas. Y las B se revisan cuando hay tiempo.

El análisis ABC no sirve para clasificar productos: sirve para decidir dónde inviertes tu atención, que es el recurso que de verdad tienes limitado.

Cuándo Excel se queda corto y toca un ERP

Excel es una respuesta razonable para muchísimos almacenes, y decir lo contrario suele ser un argumento comercial. Pero tiene un techo real, y reconocerlo a tiempo evita un año de parches.

Situación	Excel	ERP o SGA
Una persona registra los movimientos	Funciona bien	Sobra
Varias personas escriben a la vez	Coautoría en la nube ayuda, pero los conflictos y los bloqueos aparecen	Es el escenario para el que está hecho
Hasta unos pocos miles de referencias	Manejable si usas tablas y evitas volátiles	Indiferente
Decenas de miles de movimientos al año	Recalcula lento; hay que archivar histórico	Indiferente
Lotes y caducidades con trazabilidad	Se puede forzar, pero se vuelve frágil	Viene resuelto de serie
Números de serie individuales	Inviable a partir de cierto volumen	Resuelto
Varios almacenes con traspasos	Posible con prefijo de ubicación, con esfuerzo	Resuelto
Enlace con facturación y tienda online	Requiere exportaciones manuales o desarrollo	Es su razón de ser
Terminales de radiofrecuencia en el pasillo	No	Sí
Auditoría de quién cambió qué y cuándo	Solo lo que registre tu columna Usuario	Registro completo
Coste de arranque	Muy bajo: la licencia ya la tienes	Licencia, puesta en marcha y formación
Dependencia de una persona	Alta: quien montó el fichero	Menor, pero aparece la del proveedor

Los límites duros de la herramienta son 1.048.576 filas y 16.384 columnas por hoja, pero el límite práctico llega mucho antes: con decenas de miles de movimientos y varias columnas de SUMAR.SI.CONJUNTO sobre toda la tabla, cada cambio dispara un recálculo perceptible. Antes de rendirte, prueba tres cosas: cambia el cálculo a manual mientras trabajas, sustituye las fórmulas volátiles (INDIRECTO, DESREF, HOY en columnas enteras) y carga los movimientos con Power Query dejando en la hoja solo el resumen. Suele dar otro año de vida al fichero.

La señal más fiable de que ha llegado el momento no es técnica: es que el tiempo que se va en mantener el fichero, cuadrarlo y explicárselo a quien entra nuevo supera al que ahorra. Cuando eso pasa, el debate ya no es Excel contra ERP; es cuánto te está costando no decidir. Si tu actividad es de obra o de servicio a domicilio más que de almacén puro, échale un ojo a nuestra guía sobre ERP de construcción y gestión de obras, porque los criterios de salto son distintos.

Un apunte de normativa para no mezclar cosas: la regulación española de sistemas informáticos de facturación (Real Decreto 1007/2023) afecta al programa que emite facturas, no a tu hoja de inventario. Si tu plantilla solo controla stock, queda fuera de ese ámbito; si además la usas para facturar, es otra conversación y conviene tenerla con tu asesor, porque el calendario de entrada en vigor ha ido cambiando.

Los diez errores que arruinan una plantilla de inventario

1. Teclear el stock encima del anterior. Pierdes histórico, trazabilidad y la posibilidad de conciliar. Es el pecado original.
2. Rangos fijos del tipo D2:D5000. El día que se pasa de la fila 5000, la hoja sigue calculando y deja de decir la verdad, que es mucho peor que dar error.
3. SKU en formato numérico. Ceros a la izquierda perdidos, notación científica y coincidencias que fallan sin avisar.
4. Columnas de texto sin validación. «Madrid», «madrid» y «Madrid » son tres almacenes distintos para una tabla dinámica.
5. Mezclar unidades y cajas en la misma columna de cantidad, sin factor de conversión.
6. Borrar las referencias descatalogadas. Se marcan como inactivas; borrarlas deja huérfanos todos sus movimientos históricos.
7. Confundir disponible con físico y prometer al cliente lo que ya está reservado para otro.
8. Calcular la reposición contra el disponible en vez del proyectado, y comprar dos veces lo mismo.
9. Fórmulas desprotegidas en un fichero que tocan varias personas. Basta un pegado con formato para llevarse media columna por delante.
10. Una sola copia del fichero. Guárdalo en almacenamiento con versiones e histórico. Un fichero de inventario corrupto sin copia es reconstruir el año contando a mano.

Cómo montar la plantilla paso a paso

Si empiezas de cero, este es el orden que menos rehacer implica. Con el catálogo ya digitalizado, una tarde larga basta para tenerlo funcionando.

1. Paso 1. Crea el libro y las tres hojas. Abre un libro nuevo y crea tres hojas: Maestro, Movimientos y Panel. Guárdalo en formato .xlsx en una carpeta compartida con copia automática. Ese reparto es el que sostiene todo lo demás: los datos que casi no cambian viven en Maestro, los que cambian a diario viven en Movimientos y el Panel solo lee.
2. Paso 2. Define las columnas del Maestro. En Maestro crea una fila por referencia con SKU, descripción, familia, proveedor, ubicación, unidad de medida, coste unitario, stock mínimo, stock máximo, plazo de entrega y estado. Una fila por SKU y ninguna fila repetida.
3. Paso 3. Convierte los rangos en tablas de Excel. Selecciona cada rango y pulsa Ctrl+T para convertirlo en tabla. Nómbralas Maestro y Movimientos desde Diseño de tabla. A partir de ahí las fórmulas se escriben con referencias estructuradas y las filas nuevas heredan solas los cálculos.
4. Paso 4. Monta la hoja de movimientos. En Movimientos crea las columnas Fecha, SKU, Tipo, Cantidad, Documento, Ubicación origen, Ubicación destino y Usuario. Cada entrada, salida, devolución y ajuste es una fila nueva. El stock nunca se edita a mano en el Maestro.
5. Paso 5. Calcula el stock con SUMAR.SI.CONJUNTO. En el Maestro añade la columna Stock físico con SUMAR.SI.CONJUNTO sobre la hoja de movimientos, restando las salidas de las entradas. Añade después Comprometido y calcula el Disponible restando lo comprometido al stock físico.
6. Paso 6. Añade el punto de pedido y el semáforo. Calcula la demanda diaria media de los últimos noventa días, multiplícala por el plazo de entrega del proveedor y súmale el stock de seguridad para obtener el punto de pedido. Compara el disponible con ese punto de pedido en una columna de estado con la función SI.
7. Paso 7. Protege la entrada de datos. Aplica validación de datos de tipo lista a las columnas SKU, Tipo y Ubicación, bloquea las celdas con fórmula y protege la hoja dejando desbloqueadas solo las celdas de captura. Añade formato condicional para pintar de rojo lo que está por debajo del punto de pedido.
8. Paso 8. Construye el panel y cierra el circuito. Inserta una tabla dinámica sobre Movimientos para ver consumo por familia y por mes, y añade en el Panel el valor total de existencias, la rotación, los días de cobertura y la lista de referencias a reponer. Programa el recuento cíclico y registra cada ajuste como un movimiento más.

Si el inventario que necesitas no es de mercancía sino de bienes de la empresa (mobiliario, equipos, herramienta), el enfoque cambia bastante y lo tienes desarrollado en cómo hacer el inventario de bienes de la empresa. Para documentar las entradas y salidas hacia terceros te vendrá bien una plantilla de albarán de entrega, y si trabajas en logística, la versión sectorial en albarán en Excel para logística. Para atar el inventario con la parte económica, mira la plantilla de presupuesto de empresa en Excel y la de control de gastos de empresa. Y si quieres profundizar en la parte fiscal, tenemos una guía fiscal para autónomos.

Preguntas frecuentes sobre la plantilla de inventario de almacén en Excel

¿Qué columnas mínimas necesita una plantilla de inventario de almacén en Excel?

Con once columnas en el Maestro tienes un sistema que funciona: SKU, descripción, familia, proveedor, ubicación, unidad de medida, coste unitario, stock mínimo, stock máximo, plazo de entrega y estado. A partir de ahí, el stock actual, el disponible, el punto de pedido y la alerta son columnas calculadas, no datos que teclee nadie. Si una columna no se usa para decidir una compra, un recuento o una ubicación, sobra.

¿Cómo se calcula el stock disponible con fórmulas de Excel?

El stock físico sale de sumar los movimientos de esa referencia con SUMAR.SI.CONJUNTO, restando las salidas de las entradas. El disponible es el stock físico menos lo que ya está comprometido en pedidos de venta pendientes de servir. Si guardas la cantidad ya con signo (positiva en entradas, negativa en salidas) te vale un único SUMAR.SI. En Excel en inglés las funciones son SUMIFS y SUMIF.

¿Cuál es la fórmula del punto de pedido en Excel?

El punto de pedido es la demanda diaria media multiplicada por el plazo de entrega del proveedor en días, más el stock de seguridad. En Excel se escribe como REDONDEAR.MAS(demanda_diaria*plazo+stock_seguridad;0), que en inglés es ROUNDUP. La demanda diaria media la sacas dividiendo entre noventa las salidas de los últimos noventa días. Cuando el disponible baja de ese número, toca lanzar el pedido.

¿Cómo hago que Excel avise cuando el stock está bajo?

Con formato condicional por fórmula. Selecciona el rango de datos, crea una regla nueva de tipo fórmula y escribe una condición con la columna anclada y la fila libre, por ejemplo que el disponible sea menor o igual que el punto de pedido. Excel pinta la fila entera. Añade una segunda regla para la rotura de stock y una tercera para el exceso. Excel no manda correos por sí solo: el aviso es visual y hay que mirarlo.

¿Se pueden usar listas desplegables en la plantilla de inventario?

Sí, y son la diferencia entre un fichero limpio y uno inservible. Ve a Datos, Validación de datos, Permitir Lista, y apunta el origen a la columna de SKU del Maestro. Para listas dependientes (familia y luego subfamilia) se usa INDIRECTO sobre nombres definidos, que en inglés es INDIRECT. Marca la casilla de mensaje de error para que Excel rechace lo que no esté en la lista.

¿Cómo se usa un lector de códigos de barras con una plantilla de Excel?

Casi todos los lectores USB o Bluetooth funcionan en modo teclado: leen el código, lo escriben en la celda activa y envían un Intro. No hace falta programar nada. Lo único que hay que preparar es formatear la columna del código como Texto para no perder los ceros de la izquierda ni que Excel convierta códigos largos a notación científica, y dejar la hoja de captura con una sola columna activa para que el salto de línea caiga donde toca.

¿Cuál es la diferencia entre inventario físico y teórico?

El teórico es lo que dice tu hoja después de sumar entradas y restar salidas. El físico es lo que hay de verdad cuando alguien va y lo cuenta. La diferencia entre los dos es la merma: roturas, robos, errores de picking, unidades que entraron sin registrarse. No se corrige machacando la celda del stock: se registra un movimiento de tipo AJUSTE con la diferencia, la fecha y el motivo, para que el histórico siga cuadrando.

¿Cada cuánto hay que hacer recuento de inventario?

Con recuento cíclico no paras el almacén: cuentas todos los días un puñado de referencias según su clase. Las de clase A, que concentran la mayor parte del valor, cada mes; las B cada trimestre; las C una o dos veces al año. Además hay un recuento general al cierre del ejercicio, porque el Código de Comercio obliga a llevar un libro de inventarios y cuentas anuales. Consulta el calendario concreto con tu asesor.

¿Qué método de valoración de existencias puedo usar en España?

El Plan General de Contabilidad admite con carácter general el precio medio ponderado y también el FIFO, es decir, primera entrada, primera salida. El LIFO no está admitido. El método elegido se aplica de forma uniforme a bienes de naturaleza y uso similares y no se cambia de un año para otro sin justificarlo. Esta página es divulgativa: la decisión contable y su reflejo en cuentas anuales conviene cerrarla con tu asesor o con un economista colegiado.

¿Cómo se calcula la rotación de inventario en Excel?

La rotación es el coste de las ventas del periodo dividido entre las existencias medias valoradas a coste, y las existencias medias son la media entre el saldo inicial y el final. Si te sale 6, has renovado el almacén seis veces en el año. Los días de cobertura son 365 divididos entre esa rotación. Protege siempre la división con SI.ERROR, que en inglés es IFERROR, porque las referencias sin consumo dan división entre cero.

¿Cuántas referencias aguanta una plantilla de inventario en Excel?

Una hoja de cálculo admite 1.048.576 filas y 16.384 columnas, pero el límite práctico llega mucho antes. Con varios miles de referencias y decenas de miles de movimientos acumulados, las fórmulas de suma condicional empiezan a recalcular lento y el fichero se vuelve pesado. Una salida intermedia es cargar los movimientos con Power Query y dejar en la hoja solo el resumen.

¿Cuándo conviene dejar Excel y pasar a un ERP o un SGA?

Cuando aparece cualquiera de estas cuatro cosas: más de una persona necesita escribir a la vez, hay lotes o caducidades que trazar, trabajas con varios almacenes o ubicaciones que se mueven entre sí, o el inventario tiene que hablar con la facturación y con la tienda online. También cuando el tiempo que se va en mantener el fichero supera al que ahorra. Hasta ahí, Excel es una respuesta razonable y barata.