Control de gastos personales en Excel: plantilla y fórmulas

Antes de abrir tu primera pestaña piensa en esto: cada mes cobras una cantidad, pero a mitad de mes ya no sabes dónde quedó el dinero. Una hoja de gastos te lo enseña: apuntas cada compra con su importe y Excel te muestre en tu pantalla lo que llevas gastado y lo que aún te queda. Al terminar este tutorial tendrás una hoja llamada "Gastos" con una lista desplegable de categorías, totales automáticos por categoría, un gráfico circular y una regla que pinta en rojo los totales que se pasan del 50 % de tu sueldo. Sirve para Microsoft 365, Excel 2024, 2021, 2019 y 2016; las tablas y la validación de datos están en todas ellas.

Qué es una hoja de gastos y por qué te conviene armarla en Excel

Una hoja de gastos personales es un cuaderno en versión hoja de cálculo: una fila por cada gasto que haces, con la fecha, el concepto, la categoría, el importe y la forma de pago. Abajo de esa lista, unas fórmulas suman lo que llevas gastado en el mes y lo que aún te queda del sueldo.

La gracia de hacerlo en Excel y no en una libreta es que las cuentas se hacen solas. Tú escribes "Mercadona, Comida, 85,40" en una fila y, sin tocar nada más, el total de Comida sube, el total del mes sube, y el gráfico se redibuja. Si luego te equivocas, borras la celda y todo se recalcula.

Otra ventaja es que puedes preguntar cosas sin escribir nada nuevo. ¿Cuánto llevo en Ocio este mes? Una fórmula te lo dice. ¿Me pasé del 50 % del sueldo en Suministros? Un formato condicional pinta esa celda en rojo y lo ves de un golpe.

Y la última: la hoja es un archivo, así que la puedes copiar, mandar por correo, abrir en el móvil o guardarla en la nube. Una libreta no te deja eso.

Las piezas de una hoja de gastos y dónde se hacen

La hoja tiene una zona de datos arriba y una zona de totales abajo. Arriba, cinco columnas: Fecha, Concepto, Categoría, Importe y Forma de pago. Cada fila es un gasto. Abajo, en columnas a la derecha, una pequeña tabla resumen con cada categoría y su total.

Las cinco columnas se escriben a mano: en A pones la fecha, en B el sitio donde compraste, en C la categoría (Comida, Transporte, Ocio…), en D el importe y en E la tarjeta o efectivo.

La columna de Categoría lleva una lista desplegable para que todos los meses uses las mismas palabras. Eso se monta con Datos > Herramientas de datos > Validación de datos, en una ventana que se llama "Validación de datos".

La columna de Importe lleva formato de moneda: Formato de celdas (Ctrl+1) > Número > Moneda, eliges el símbolo de tu país en la lista Símbolo y dos decimales. Así "85,40" se ve "85,40 €" o "85,40 $" según donde vivas.

Los totales por categoría usan la función SUMAR.SI (en inglés SUMIF). Cada celda de la tabla resumen mira la columna de categorías y suma los importes que coinciden con la suya.

La columna de Categoría con una lista desplegable

Para que la columna C siempre diga lo mismo, escribe la lista de categorías en una zona aparte (por ejemplo G2 a G6: Comida, Transporte, Ocio, Suministros, Otros) y dile a Excel que la celda C2 solo deje elegir valores de esa lista.

Cuando pulses Aceptar, la celda C2 muestra una flecha a la derecha. Al hacer clic se despliegan las cinco categorías y eliges una con un clic. Esto lo aplicas a toda la columna C de una vez: seleccionas C2:C500 antes de abrir Validación de datos.

La columna de Importe con formato de moneda

Los importes se escriben con coma decimal (85,40) y Excel los enseña como números normales. Para que parezcan dinero, abre Formato de celdas (Ctrl+1), ve a Número > Moneda, deja dos decimales y elige el símbolo de tu país en la lista Símbolo (€, $, ¥, £…).

Selecciona toda la columna D antes de aplicar el formato y así cada importe nuevo que escribas ya sale como moneda.

Los totales por categoría con SUMAR.SI

La función SUMAR.SI (en inglés SUMIF) mira una columna, busca las celdas que coinciden con lo que tú le pidas y devuelve la suma de los importes de esas filas. Su sintaxis es:

=SUMAR.SI(rango_categoría;"Comida";rango_importe)

Tres argumentos separados por punto y coma:

• rango_categoría: dónde mira (C2:C500), las celdas donde están las categorías.
• criterio: qué busca (la palabra "Comida" entre comillas, o la celda G2 que la contiene).
• rango_importe: qué suma (D2:D500), los importes de las filas que coinciden.

Armar la hoja desde cero en diez minutos

Vamos a montar el libro entero en pasos cortos. Abre un libro nuevo, guárdalo como "Mis gastos" y crea dentro una hoja llamada "Gastos". En esa hoja pondremos las cabeceras en la fila 1 y los datos de un mes de enero en las filas siguientes.

Escribir los datos del mes

Empezamos con los seis primeros gastos de enero, uno cada día. Tú escribes las cabeceras en A1:E1 y los datos en A2:E7 a mano, tal cual los ves en la hoja.

Crear la lista desplegable de categorías

Primero escribe las cinco categorías en G2:G6. Luego abre Validación de datos y aplícala sobre C2:C500 para que solo deje usar esas cinco palabras. La fila 3, que decía "Combida", la corriges eligiendo "Comida" en la flecha.

Calcular el total del mes con SUMA

Ahora añadimos el total del mes: cuánto llevan sumados los seis importes. La fórmula va en D9.

=SUMA(D2:D7)

Calcular el total por cada categoría con SUMAR.SI

Ahora añadimos el total por categoría en H2:I6. La fórmula de H2 (la categoría) la pones a mano, y la de I2 (el total) usa SUMAR.SI mirando la columna G2.

=SUMAR.SI($C$2:$C$500;$G2;$D$2:$D$500)

Las referencias son absolutas (con $) en los rangos para que al copiar hacia abajo no se desplacen, y la celda G2 lleva solo $ en la columna para que sí se actualice a G3, G4… al copiar.

Para escribir las cinco fórmulas, escribe la primera en I2, pon el cursor en I2, agarra el controlador de relleno (el cuadradito de la esquina inferior derecha) y arrastra hasta I6.

Convertir el rango de datos en una Tabla para que crezca

Ahora la hoja tiene seis filas de datos y las fórmulas miran hasta la fila 500. Eso funciona, pero cuando escribas el gasto número 7, las fórmulas no se mueven solas: hay que ajustar los rangos a mano. El truco para que la hoja crezca sola es convertir A1:E7 en una Tabla de Excel.

Al pulsar Aceptar, A1:E7 pasa a tener filas alternas en azul y un menú desplegable en cada cabecera para filtrar. Las cabeceras cambian de A1:F1 etc.

Ahora reescribe los totales así D10 con DNI:

=SUMA(Tabla1[Importe])

Y los totales por categoría en I2:I6 con referencias a la columna Categoría de la Tabla:

=SUMAR.SI(Tabla1[Categoria];$G2;Tabla1[Importe])

Ver los gastos a simple vista con un gráfico

Con los totales por categoría ya calculados, podemos añadir un gráfico que los muestre de un vistazo. Selecciona H1:I6 (la columna de categorías y la de totales), ve a Insertar y elige Gráfico circular.

Aparece un gráfico de quesito donde cada porción es una categoría y su tamaño es proporcional al total gastado. Si Comida es 142,60 y Transporte es 53,00, el cacho de Comida es casi tres veces más grande que el de Transporte. Esto te deja ver de un vistazo en qué se te va más dinero.

Al añadir un gasto nuevo en una fila de la Tabla, el total de su categoría sube y la porción del gráfico se redibuja sola.

Cuando prefieres un gráfico de barras

Si te cuesta comparar tamaños en un circular, pasa a barras: cuando el gráfico esté seleccionado, la cinta muestra las pestañas Herramientas de gráficos > Diseño. Ahí elige Diseño de gráficos > Cambiar tipo de gráficos y marca Barra agrupada o Barras agrupadas 2D.

Las barras se ven mejor cuando hay categorías con importes parecidos, como Suministros (62,30) y Transporte (53,00): de un vistazo ves cuál es más alto. El circular, en cambio, sirve para ver proporciones: qué parte del total del mes se va en Comida frente al resto.

Detectar los gastos que se pasan del límite con formato condicional

Imagina que tu sueldo son 1.500,00 € y quieres que cualquier saldo mayor en rojo te avise de que esa categoría se está pasando. En I2:I6 aplicas una regla: si la celda vale más del 50 % de tu sueldo (750,00), se pone en rojo.

Primero escribe el sueldo en una celda aparte, por ejemplo K1, con el valor 1500. Después, en la cinta, abre Inicio > Estilos > Formato condicional > Nueva regla.

En el campo "Aplicar formato a las celdas que sean mayores que" escribe la fórmula:

=$I2>$K$1/2

Pulsa Formato, ve a Relleno y elige rojo claro; ve a Fuente y elige rojo oscuro. Aceptar dos veces. Ahora cada vez que un total por categoría supere 750 €, se pinta solo en rojo.

Por qué tu fórmula da #¡REF! o no suma

Tres errores típicos al montar esta hoja: la fórmula da #¡REF!, el desplegable no aparece o SUMAR.SI devuelve 0.

#¡REF! sale cuando borras una fila o columna que la fórmula necesita. Si copiaste I2 a otra hoja y borraste la fila de cabeceras (fila 1), todas las referencias a $C$2:$C$500 se desplazan y queda algo como $C$1:$C$499, que apunta a la fila de cabeceras. Vuelve a escribir los rangos a mano, con las filas correctas.

Otro motivo: has separado los argumentos con coma en vez de punto y coma, o al revés. Revisa el separador según tu configuración regional.

Si la fórmula muestra el texto de la fórmula en vez del resultado (ves "=SUMA(D2:D7)" en la celda), es porque la celda está en formato Texto. Cámbiala a General con Ctrl+1 > Número > General, y vuelve a escribir la fórmula.

El desplegable deja escribir cualquier texto

Si al hacer clic en C2 no sale la flecha, esa celda no tiene validación aplicada. Suele pasar al copiar y pegar celdas desde otra hoja: el pegado normal no arrastra la validación. Reabre Datos > Herramientas de datos > Validación de datos, vuelve a elegir Lista con Origen =$G$2:$G$6 y Aceptar.

SUMAR.SI devuelve 0 cuando debería sumar

Cuando SUMAR.SI da 0 sin motivo, mira tres cosas:

• El criterio. Si en la columna C hay "Comida" con tilde y la celda G2 dice "Comida" sin tilde, no coinciden. Revisa que sean idénticas.
• El rango de suma. Si pones D2:D7 pero hay gastos en D8 y D9, esos no se suman. Amplía el rango o convierte los datos en Tabla.
• Los rangos del mismo tamaño. El rango_categoría y el rango_importe deben tener las mismas filas: si uno va hasta la fila 7 y el otro hasta la 8, la fórmula suma ceros en las que no coinciden.

El gráfico no se actualiza al añadir filas

Si el gráfico se quedó con los seis primeros gastos y no incluye los nuevos, es porque al crearlo seleccionaste A1:E7 a mano (un rango fijo). Excel guarda esa selección literal. Convierte los datos en una Tabla (Insertar > Tablas > Tabla) y vuelve a crear el gráfico: al usar los datos desde una Tabla, el gráfico sigue automáticamente las filas nuevas.

Lo que ya sabes hacer con tu hoja de gastos

• Lista desplegable de categorías con Validación de datos.
• Totales por categoría con SUMAR.SI, que se actualizan solos al añadir filas.
• Formato de moneda en la columna Importe con el símbolo de tu país.
• Formato condicional que pinta en rojo los totales que superan el 50 % del sueldo.
• Gráfico circular o de barras que muestra los totales y se redibuja con los datos.

El siguiente paso es añadir una columna de Presupuesto por categoría (por ejemplo en H1:H6, con un importe al lado en I1:I6) y una columna de Diferencia para comparar lo previsto con lo real. Cuando la real supere el presupuesto, la pintas en rojo con otra regla de formato condicional, igual que has hecho aquí.

Dudas al montar tu hoja de gastos en Excel