Cómo hacer una hoja de inventario en Excel desde cero

Imagina una tienda de barrio que aún apunta las existencias en un cuaderno: cuando un cliente pregunta si queda aceite, hay que buscar la página, sumar a mano y rezar para no equivocarse. Una hoja de inventario en Excel pone en un mismo lugar el código del producto, las unidades que hay ahora, el mínimo para no quedarte sin stock, el precio y el valor total. Con ella sabes al instante qué tienes, cuánto vale lo que tienes en la estantería y qué tienes que pedir antes de que se agote.

En este tutorial montas esa hoja desde una hoja en blanco: empiezas con cinco productos, aprendes a calcular el valor de cada uno, a poner listas desplegables para no escribir de todo, a que la hoja se ponga en rojo cuando un producto baje del mínimo y, al final, la conviertes en una tabla que crece sola cuando añades más productos. Necesitas Excel 2007 o posterior en Windows, Excel 2016 o posterior en Mac, o Excel para la web. La mayor parte de lo que ves funciona igual en todas esas versiones; las notas van avisando cuando algo cambia.

Qué es una hoja de inventario y para qué te sirve

Una hoja de inventario es una tabla donde apuntas cada producto con la cantidad que tienes guardada. En una tienda pequeña basta con un Excel: una fila por artículo y unas columnas con los datos que importan en el día a día.

Sirve para tres cosas muy concretas. Primero, para responder de un vistazo a la pregunta «¿cuánto tengo de esto?». Segundo, para calcular cuánto dinero tienes invertido en mercancía sin hacer sumas a mano: multiplicas lo que tienes por lo que te costó y ya tienes el valor. Tercero, para detectar a tiempo los productos que están a punto de acabarse: si defines un mínimo, la hoja te avisa cuando un artículo baja de esa cifra y sabes que hay que pedir más antes de que se agote.

Cuando el negocio crece, esa misma información es la que se lleva luego a Access o a un programa de gestión. Por eso conviene montarla bien desde el principio: si las columnas están claras y los datos limpios, pasar a otro sistema es solo cambiar de herramienta.

Las columnas que necesita una hoja de inventario

Estas son las diez columnas que usaremos a lo largo del tutorial. La fila 1 lleva el encabezado en negrita y a partir de la fila 2 empiezan los productos.

Columnas de datos y columnas calculadas: la diferencia

Fíjate en que las columnas no son todas iguales. En E, F, G e I escribes tú un dato: cuentas el stock, decides el mínimo, apuntas el precio y el nombre del proveedor. En H y J, en cambio, no escribes nada: Excel las rellena solo a partir de los otros datos. A las primeras se las llama columnas de datos y a las segundas, columnas calculadas. La diferencia importa porque las columnas calculadas usan fórmulas y se actualizan solas cuando cambias un dato: si subes el precio de un producto, su valor total se corrige sin que toques la celda.

Montar la hoja con los primeros productos

Empezamos con un libro nuevo y una sola hoja, a la que llamaremos Inventario. La hoja está en blanco y vamos a rellenarla fila a fila con cinco productos de una tienda de barrio.

1. Haz doble clic en la pestaña inferior Hoja1 y escribe Inventario.
2. En A1 escribe Código, en B1 Producto, en C1 Categoría, en D1 Unidad, en E1 Stock actual, en F1 Stock mínimo, en G1 Precio unitario, en H1 Valor total, en I1 Proveedor y en J1 Estado.
3. Selecciona A1:J1 y pulsa Ctrl+N para poner los encabezados en negrita.
4. Rellena las filas 2 a 6 con los datos de la hoja de ejemplo de abajo.

Observa que las columnas H y J quedan vacías por ahora: las calcularemos en el siguiente paso. Fíjate también en que el producto REF-002 (Detergente) tiene 8 unidades y el mínimo es 12: ya está bajo de mínimo. El REF-005 (Servilletas), con 6 y mínimo 15, también. Esos dos casos nos servirán para probar luego el formato condicional.

Calcular el valor de cada producto con una fórmula

Ahora vamos a llenar la columna H, Valor total. La fórmula multiplica el stock actual (columna E) por el precio unitario (columna G). Como todavía no hay datos en la fila 7, veremos el problema típico: una celda vacía multiplicando por otra da el error #¡VALOR!. La solución la verás más adelante; por ahora escribe la fórmula tal cual y la arreglaremos cuando aparezca.

1. Selecciona la celda H2.
2. Escribe =E2*G2 y pulsa Intro.
3. Vuelve a seleccionar H2, sitúa el cursor sobre el pequeño cuadrado verde de la esquina inferior derecha de la celda (el controlador de relleno) y arrastra hacia abajo hasta H6.

=E2*G2

La fórmula es la misma en las cinco filas, pero el resultado cambia: 24 por 5,20 da 124,80; 8 por 8,90 da 71,20; 50 por 1,50 da 75,00; 15 por 3,75 da 56,25; 6 por 1,10 da 6,60. Si cambias el precio de un producto o el stock, su valor se recalcula al instante. Eso es lo que hace útil una columna calculada.

Crear la lista desplegable de categorías y estados

Escribir la categoría a mano en cada fila nueva es fuente de errores: «Alimentacion» sin tilde, «alimentación» en minúscula, «ALIMENTACIÓN» en mayúscula. Para evitarlo, convertimos las celdas de la columna C en listas desplegables. Haremos lo mismo con la columna J (Estado) más adelante, cuando tenga valores.

1. Selecciona el rango C2:C6.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir elige Lista.
4. En Origen escribe Alimentación;Limpieza;Oficina;Electrónica (con punto y coma entre medias, sin comillas).
5. Marca Celda con lista desplegable y pulsa Aceptar.

Ahora al hacer clic en cualquier celda de C2 a C6 aparece una flecha a la derecha. Si pulsas la flecha, ves las cuatro categorías y solo puedes elegir una de ellas.

La lista escrita a mano en el cuadro

La forma más rápida de tener una lista desplegable es escribir sus valores directamente en el campo Origen del cuadro de Validación de datos, separados por punto y coma. Es la opción ideal cuando la lista es corta y casi nunca cambia: las cuatro categorías de nuestra tienda encajan perfectamente. La pega es que, si un día añades una categoría nueva, tienes que volver a abrir Validación de datos y escribirla a mano.

La lista sacada de un rango de la hoja

Cuando la lista puede crecer, lo mejor es guardarla en una zona aparte de la hoja y apuntar a ese rango desde Validación de datos. Así, si mañana añades una categoría, solo tienes que escribirla en la zona de categorías y todas las celdas desplegables la verán.

1. En la columna M, escribe en M1 Categorías, en M2 Alimentación, en M3 Limpieza, en M4 Oficina y en M5 Electrónica.
2. Selecciona otra vez el rango C2:C6, abre Datos > Herramientas de datos > Validación de datos.
3. Cambia el Origen por =$M$2:$M$5 y pulsa Aceptar.

Los símbolos $ fijan el rango para que, si más adelante copias la regla a otras celdas, no se desplace. La flecha desplegable de las celdas C2:C6 sigue mostrando las mismas cuatro opciones, pero ahora la lista vive en M2:M5.

Avisar cuando un producto baja del mínimo con formato condicional

Ahora viene la parte más útil para el día a día: que la celda de stock se ponga en rojo cuando haya menos unidades de las que marcaste como mínimo. Para eso usamos formato condicional, una regla que cambia el aspecto de la celda según su valor.

1. Selecciona el rango E2:E6.
2. Abre Inicio > Estilos > Formato condicional > Nueva regla.
3. Elige Aplicar formato a las celdas que contengan.
4. En la descripción de la regla, deja Valor de la celda, cambia el segundo desplegable a menor que y escribe =$F2.
5. Pulsa Formato…, ve a la pestaña Relleno y elige un rojo claro; en la pestaña Fuente pon la letra en rojo oscuro.
6. Pulsa Aceptar dos veces.

Al volver a la hoja, las celdas E2 a E6 con stock por debajo de su mínimo se ven en rojo. Con los datos de la hoja, E3 (Detergente, 8) y E6 (Servilletas, 6) deberían verse ahora en rojo, porque sus mínimos son 12 y 15.

La referencia que tiene que moverse con cada fila

Fíjate en que en la regla escribimos =$F2 y no =$F$2. El $ delante de la F fija la columna para que la regla siempre compare con la columna F (el mínimo), pero no fija la fila: al evaluar la celda E3, Excel entiende que tiene que mirar F3; al evaluar E6, tiene que mirar F6. Si hubiéramos escrito =$F$2, todas las celdas compararían con el mínimo del primer producto y la regla no serviría para los demás.

=$F2

Esta mezcla (columna fija, fila relativa) es la forma habitual de escribir reglas que se aplican a una columna entera. La verás también en BUSCARV, en SUMAR.SI y en casi cualquier fórmula que se copia hacia abajo.

Convertir el rango en tabla para que crezca solo

Ahora mismo, si escribes un producto nuevo en la fila 7, tienes que copiar la fórmula del valor total, la lista desplegable y el formato condicional a mano. La solución es convertir el rango en una tabla de Excel: a partir de ese momento, todo lo que pongas en la fila de abajo hereda las fórmulas, los formatos y las reglas.

1. Selecciona el rango A1:J6.
2. Abre Insertar > Tablas > Tabla.
3. Marca La tabla tiene encabezados y pulsa Aceptar.
4. Escribe un producto nuevo en la fila 7, por ejemplo REF-006, Botellas de agua 1,5 L, Bebidas, Pack 6, 20, 10, 0,45 y comprueba que la columna H ya muestra 9,00 y la columna E tiene la regla en rojo si la pones por debajo del mínimo.

Fíjate en que la fila 7 aparece con el mismo aspecto que las demás: la columna H ya tiene la fórmula del valor total (9,00) sin que la copiaras y la columna E tendría el formato en rojo si el stock estuviera por debajo del mínimo. La tabla también te da filtros en cada encabezado (las flechitas a la derecha del nombre) y un estilo visual que se puede cambiar desde el menú Diseño de tabla.

Errores típicos al montar la hoja de inventario

Estos son los cinco tropiezos que más salen al montar una hoja de inventario por primera vez. Reconocerlos a tiempo ahorra un buen rato.

#¡VALOR! en Valor total cuando una celda está vacía

Si añades un producto nuevo en la fila 7 y dejas el precio en blanco, la fórmula =E7*G7 muestra #¡VALOR! porque Excel no puede multiplicar un número por una celda vacía. La fórmula arreglada deja la celda en blanco hasta que haya precio:

=SI(G2="";"";E2*G2)

La función SI (en inglés IF) evalúa si G2 está vacía: si lo está, deja la celda en blanco (las dos comillas seguidas); si no, calcula la multiplicación. Cambia la fórmula en H2 por esta nueva y cópiala al resto de la columna.

La lista desplegable deja escribir cualquier cosa al pegar

Copiar datos de otra hoja con Ctrl+V puede saltarse la regla de validación y dejar celdas con valores que no están en la lista. Para encontrar esos casos, abre Datos > Herramientas de datos > Validación de datos y pulsa Rodear con un círculo datos no válidos. Excel marca con un círculo rojo las celdas que no cumplen la regla.

La celda C3 muestra "aseo" y está rodeada con un círculo rojo porque no está en la lista. Decide si corriges el texto o si añades "Aseo" a la lista de categorías.

El formato condicional no salta al añadir filas

Si aplicaste el formato condicional al rango E2:E6 y después añadiste filas, esas filas nuevas no tendrán la regla y no se pondrán en rojo. La solución es haber convertido el rango en tabla antes: las tablas aplican el formato fila a fila automáticamente. Si todavía no tienes tabla, cambia el rango a E2:E1000 (o el número de filas que preveas) en Inicio > Estilos > Formato condicional > Administrar reglas.

La fila 7, fuera del rango E2:E6 original, no se pondría en rojo aunque su stock bajara del mínimo. Al convertir el rango en tabla, todas las filas nuevas heredan la regla.

Lo que ya sabes hacer con tu hoja de inventario

• Rellenar las diez columnas básicas de un inventario con datos reales.
• Calcular el valor de cada producto con =E2*G2 y arrastrar la fórmula con el controlador de relleno.
• Crear listas desplegables con Validación de datos, tanto con valores escritos a mano como desde un rango de la hoja.
• Avisar visualmente con formato condicional cuando un producto baja del mínimo.
• Convertir el rango en tabla para que las fórmulas y los formatos se apliquen solos a las filas nuevas.

El siguiente paso es filtrar la tabla para ver solo lo que te interesa: pulsa Ctrl+Mayús+L con cualquier celda de la tabla seleccionada y aparecerán las flechas de filtro en los encabezados. Filtra la columna E por Ordenar de menor a mayor para ver primero los productos con menos stock, o filtra la columna J (Estado) por Agotado cuando la rellenes con una fórmula SI para localizar de un vistazo lo que hay que pedir.

Dudas al montar una hoja de inventario en Excel