Control de inventario en Excel: plantilla gratuita y sus límites

El control de inventario en Excel funciona bien si se apoya en una regla: la cantidad en stock nunca se escribe, se calcula. Esta guía construye ese archivo paso a paso, con las fórmulas exactas. También le ofrece la plantilla gratuita y le dice con franqueza dónde se queda corto Excel.

¿Cómo llevar el control de inventario en Excel?

Cree dos tablas de Excel. La primera recoge los artículos: referencia, descripción, precio de compra, stock inicial y punto de pedido. La segunda registra cada movimiento en una fila: fecha, referencia, entrada o salida, cantidad. El stock actual de cada artículo se calcula a partir de esos movimientos, sin escribirlo a mano.

Muchos archivos de inventario empiezan con una sola hoja y una columna «Cantidad» que se corrige con cada entrega. El primer día es rápido. Al cabo de un mes, una cifra errónea ya no tiene explicación: nadie sabe quién la cambió, cuándo ni por qué.

Separar los artículos de los movimientos resuelve el problema. Cada recepción, cada venta, cada rotura se convierte en una fila con fecha y comentario. El stock de un artículo deja de ser un valor frágil: es una suma que se puede rehacer.

El principio cabe en una fórmula:

Use tablas de Excel de verdad (Inicio y luego Dar formato como tabla), no simples rangos. Una tabla crece sola cuando usted añade una fila, y sus fórmulas la siguen.

¿Qué contiene la plantilla gratuita?

La plantilla de control de inventario en Excel es un libro de dos hojas, cada una con formato de tabla de Excel. La hoja «Articulos» contiene quince artículos de ejemplo y todas las fórmulas. La hoja «Movimientos» contiene cuarenta entradas y salidas para ver funcionar los cálculos. Las dos tablas se llaman Articulos y Movimientos. El archivo se descarga sin registro.

La hoja «Articulos» tiene trece columnas:

La hoja «Movimientos» tiene cinco columnas: Fecha, Referencia, Tipo, Cantidad y Comentario. La columna Tipo ofrece dos valores, «Entrada» o «Salida», en una lista desplegable. En la hoja «Articulos», las filas que hay que pedir aparecen coloreadas. Para usarla, sustituya los ejemplos por sus artículos y anote los movimientos a medida que se producen.

¿Cómo calcular el stock actual con SUMAR.SI.CONJUNTO?

La función SUMAR.SI.CONJUNTO suma los valores de una columna que cumplen varias condiciones. Aquí suma las cantidades de la hoja «Movimientos» cuya referencia es la del artículo y cuyo tipo es «Entrada». La misma fórmula con «Salida» da las salidas. De ahí se deduce el stock actual.

La sintaxis es SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterios1; [rango_criterios2; criterios2]; …), según la página de Microsoft sobre SUMAR.SI.CONJUNTO. Primero va el rango que se suma y después cada pareja de rango y criterio.

En la plantilla, la columna Entradas (H) contiene, en la fila 2:

=SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[Referencia];A2;Movimientos[Tipo];"Entrada")

La columna Salidas (I) repite la misma fórmula con «Salida»:

=SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[Referencia];A2;Movimientos[Tipo];"Salida")

La columna Stock actual (J) suma el stock inicial (G) y las entradas (H), y resta las salidas (I):

=G2+H2-I2

Movimientos[Cantidad] es una referencia estructurada: designa toda la columna Cantidad de la tabla Movimientos, aunque la tabla crezca. A2 es la referencia del artículo en la misma fila. La plantilla combina los dos estilos para que se lea mejor.

Si prefiere escribirlo todo con referencias estructuradas, sustituya A2 por [@Referencia]. La @ designa la celda de la fila actual, según la documentación sobre referencias estructuradas. Las dos versiones dan el mismo resultado.

El punto y coma separa los argumentos en un Excel configurado en español de España. En una tabla, una fórmula escrita en la fila 2 se copia sola en toda la columna.

¿Cómo definir un punto de pedido y la lista de artículos a pedir?

El punto de pedido es la cantidad por debajo de la cual hay que pedir. Escríbalo para cada artículo y compárelo con el stock actual mediante la función SI. La columna «Pedir» muestra «Sí» en cuanto el stock alcanza el punto de pedido o baja de él. Un filtro sobre «Sí» da la lista de pedidos del día.

La función SI se escribe SI(prueba_lógica; valor_si_verdadero; [valor_si_falso]), según la página de Microsoft sobre SI. En la columna Pedir:

=SI(J2<=K2;"Sí";"No")

J2 es el stock actual y K2 el punto de pedido. El signo <= pasa el artículo a «Sí» en cuanto alcanza su punto de pedido, no solo cuando baja de él.

Para fijar el punto de pedido basta una regla sencilla. Multiplique el consumo medio diario por el plazo de entrega del proveedor, en días, y añada un margen. Un artículo que sale a razón de 4 unidades al día, con entrega en 5 días, necesita al menos 20 unidades en el momento de pedir. Con un margen de 10 unidades, el punto de pedido sube a 30.

¿Cómo calcular el valor del stock?

El valor del stock de un artículo es su stock actual multiplicado por su precio de compra sin IVA. El valor total es la suma de esa columna. Indica cuánto dinero está parado en las estanterías. También es un buen punto de partida para el inventario de cierre de ejercicio.

En la plantilla, la columna Valor del stock (M) multiplica el stock actual (J) por el precio de compra sin IVA (F):

=J2*F2

El archivo no incluye un total. Para obtenerlo, escriba en una celda fuera de la tabla:

=SUMA(Articulos[Valor del stock])

La plantilla valora cada artículo a un único precio de compra, lo que basta para el día a día. Si sus precios varían mucho de una entrega a otra, pregunte a su asesor contable qué método aplicar.

Para seguir ese valor en el tiempo, con un gráfico por categoría o por proveedor, consulte nuestra guía del dashboard en Excel.

Proteger la entrada de datos: listas desplegables y formato condicional

Dos ajustes evitan la mayoría de los errores, y la plantilla ya incluye los dos. La validación de datos ofrece «Entrada» o «Salida» en la columna Tipo, para evitar un «Entrda» que SUMAR.SI.CONJUNTO ignoraría. El formato condicional colorea los artículos que han llegado al punto de pedido.

La lista desplegable del tipo de movimiento

Seleccione la columna Tipo de la tabla Movimientos. En la pestaña Datos, grupo Herramientas de datos, elija Validación de datos. En el cuadro Permitir, seleccione Lista y escriba los dos valores en el cuadro Origen, según el procedimiento de Microsoft. Microsoft los separa con comas: Entrada,Salida, como en la plantilla.

El mismo método sirve para la columna Referencia: una lista que apunte a las referencias de la hoja «Articulos» evita códigos mal escritos. Un movimiento con una referencia desconocida no se suma en ningún sitio, y el stock queda falseado sin ningún aviso.

Colorear los artículos que hay que pedir

Seleccione las filas de datos de la tabla Articulos, sin el encabezado. En la pestaña Inicio, abra Formato condicional y después Nueva regla. Elija «Utilice una fórmula que determine las celdas para aplicar formato», como explica la página de Microsoft sobre el formato condicional.

En la plantilla, el encabezado está en la fila 1 y la columna Pedir es la columna L. La regla usa, por tanto, la fórmula:

=$L2="Sí"

El signo de dólar fija la columna, no la fila: cada fila comprueba su propia celda.

¿Cómo hacer un inventario y corregir las diferencias?

Hacer inventario consiste en contar físicamente cada artículo y comparar ese recuento con el stock actual del archivo. La diferencia entre ambos es un descuadre. El stock inicial no se corrige: la diferencia se registra como un movimiento, una entrada si en el archivo faltan unidades y una salida si sobran.

Para un comercio, el inventario es además la base para valorar las existencias al cierre del ejercicio. Su asesor contable le indicará qué obligaciones se aplican a su empresa.

El método con la plantilla:

1. Añada temporalmente dos columnas junto a la tabla Articulos: «Contado» y «Diferencia».
2. Cuente los artículos y escriba las cantidades, a ser posible fuera de las horas con movimientos.
3. Calcule la diferencia: cantidad contada menos stock actual.
4. Por cada diferencia, añada una fila en Movimientos con la fecha del recuento y el comentario «Inventario».
5. Compruebe que el stock actual coincide con el recuento y elimine las columnas temporales.

Los movimientos de inventario quedan a la vista. Una diferencia que se repite en el mismo artículo señala un problema: una salida sin anotar, una rotura, una unidad de embalaje mal interpretada. El comentario sirve para localizarlo.

Buenas prácticas para mantener un archivo fiable

Un archivo de inventario sigue siendo exacto si los movimientos se anotan el mismo día, por una sola persona o con una regla clara. Mantenga una referencia única por artículo, no borre nunca una fila de movimiento y proteja las columnas calculadas. Unos pocos hábitos valen más que otra fórmula.

• Una referencia, un artículo. Sin duplicados y sin reutilizar una referencia para otro producto.
• Corregir con un movimiento. Un error al anotar se corrige con un movimiento inverso comentado, no borrando la fila.
• Anotar el mismo día. Un albarán registrado a final de semana son cinco días de stock erróneo.
• Proteger las fórmulas. Todas las celdas están bloqueadas por defecto. Desbloquee las columnas que se rellenan a mano y proteja la hoja (pestaña Revisar, Proteger hoja).
• Archivar por año. Cuando la hoja Movimientos se haga larga, empiece un archivo nuevo con el stock contado como stock inicial.

¿Hasta dónde llega una hoja de inventario en Excel?

Excel funciona muy bien para una persona que gestiona unas decenas o unos cientos de artículos desde su oficina. Llega a su límite cuando varias personas anotan movimientos o cuando estos se registran en el almacén desde un móvil. O cuando una rotura de stock sale cara porque nadie miró la columna Pedir.

Estos puntos proceden de las páginas de Microsoft sobre la coautoría, la visualización de cambios y la protección de hojas. Microsoft aconseja además asignar zonas u hojas a cada persona para evitar conflictos. Es factible, pero el archivo pasa a ser una cuestión de organización más que de fórmulas.

Si ha llegado a este punto, tiene dos caminos: un programa de inventario específico o una herramienta en la que conserva su propia plantilla. Nuestra comparativa de programas de inventario gratis repasa las opciones, Rangee incluida.

¿Cómo pasar la misma plantilla a una herramienta compartida?

Importe el libro en Rangee. La importación inteligente reconoce un archivo de inventario por sus columnas (referencia, cantidad, punto de pedido, precio) y propone la plantilla «Stock e inventario». A partir de ella crea una base y una interfaz: inicio, catálogo, lista «A pedir» y una ficha por artículo. Nada se crea hasta que usted lo aprueba.

La página de inicio muestra el número de artículos, el valor del stock y los artículos que han llegado al punto de pedido. El catálogo lista todos los artículos. La página «A pedir» solo muestra los que han alcanzado su punto de pedido, y la ficha reúne todo lo relativo a un producto. Usted conserva o descarta cada tabla, cada columna y cada página propuesta. Para leer su archivo, la IA solo recibe su perfil: nombres de columnas, tipos y como máximo diez valores de ejemplo por columna, nunca el archivo completo.

Tres añadidos responden a los límites vistos antes:

• Un formulario de movimientos. La persona del almacén elige el artículo, el tipo y la cantidad desde su móvil. En el móvil, las interfaces se muestran en una sola columna.
• Una automatización sobre el punto de pedido. Se dispara cuando un artículo cumple la condición «stock actual menor o igual que el punto de pedido». Entonces puede avisar al comprador por correo electrónico, en Slack o en Teams. Usted la prueba antes de activarla.
• Permisos e historial. El historial de revisiones guarda cada cambio con su autor. Los lectores y comentaristas son gratuitos: la dirección consulta el valor del stock sin pagar una licencia.

Una precisión honesta sobre los volúmenes: cada movimiento es un registro. El plan Free admite 500 registros por base, suficiente para un catálogo pequeño durante unas semanas o unos meses. Más allá, el plan Pro cuesta 5 € por editor y mes y llega a 100.000 registros por base (ver precios). Al acercarse al límite, recibe un aviso al 80 %, y no se pierde ningún dato.

Descargue la plantilla, rellénela con sus artículos y consérvela mientras le baste. El día en que varias personas tengan que escribir en ella, impórtela en Rangee: el plan Free no caduca, y su archivo se convierte en una herramienta compartida en pocos minutos.