Cuadro de Excel para control de gastos: guía para empezar

Un cuadro de Excel para control de gastos es un libro con una hoja para registrar movimientos y otra para resumirlos. Sirve para anotar ingresos, gastos y ahorro, calcular totales y comparar lo gastado con lo previsto. Al terminar tendrás un modelo mensual con tablas, fórmulas, formato condicional y un gráfico; las funciones y herramientas mostradas están disponibles en Excel para la Web y en las versiones de escritorio compatibles con la plantilla.

Qué es un cuadro de Excel para control de gastos

El libro que vamos a construir controla las finanzas de un hogar durante enero. La hoja Presupuesto contiene cada movimiento y la hoja Resumen muestra cuánto se ingresó, cuánto se gastó y cuánto queda. Separar los datos permite comparar categorías sin borrar el registro original.

Usaremos seis movimientos: un ingreso de 1.200 euros, tres gastos y un ahorro. La hoja final quedará así:

Aunque la fila 8 no contiene un movimiento réel, sirve para mostrar cómo se reserva una operación pendiente. No se suma ningún importe hasta recibirlo.

Cómo preparar las hojas de ingresos y gastos

Antes de escribir fórmulas, crea un libro con dos hojas: Presupuesto y Resumen. En la primera, escribe los encabezados de la tabla mostrada arriba: Fecha, Concepto, Categoría, Ingreso, Gasto y Notas. Usa fechas en la columna A e importes numéricos en las columnas D y E.

Un movimiento de ingreso lleva su cantidad en Ingreso y un cero o una celda vacía en Gasto. Un movimiento de gasto hace lo contrario. El ahorro se registra como salida del presupuesto del mes; así el saldo disponible refleja el dinero que todavía puede usarse.

1. Haz doble clic en la pestaña de la hoja inferior y escribe Presupuesto.
2. Selecciona A1 y escribe los seis encabezados separados por tabulación.
3. Cambia el nombre de la segunda hoja a Resumen.
4. En Presupuesto, registra los seis movimientos iniciales.
5. Formatea las columnas D y E como número con dos decimales.

Qué plantilla de presupuesto puedes elegir

Las plantillas oficiales de Microsoft Excel incluyen fórmulas y gráficos que puedes modificar. El tipo adecuado depende de la frecuencia y del uso del presupuesto.

Una plantilla puede Traer una hoja de movimientos, fórmulas predefinidas, categorías y gráficos. No es obligatorio conservar todos sus elementos: borra las secciones innecesarias y conserva las referencias del libro que estés construyendo.

Para este libro, elige un presupuesto mensual sencillo. El resumen tendrá tres categorías: Vivienda, Alimentación y Transporte. El ahorro aparece como movimiento, pero se registra aparte para no confundirlo con un gasto cotidiano.

Cómo crear la tabla de movimientos mensuales

Ahora vamos a transformar los movimientos en una tabla. Registra primero los datos en Presupuesto y deja las columnas de importes separadas. Después insertarás una tabla para que Excel mantenga los encabezados cuando añadas nuevas filas.

1. Selecciona A1:F7.
2. En la cinta, abre la ruta siguiente:

1. Pulsa Insertar tabla.
2. Activa La tabla tiene encabezados.
3. Acepta el rango A1:F7 y el nombre tblMovimientos.
4. Añade después la fila del 20 de enero con Concepto Devolución pendiente, Categoría Ingreso y ambos importes a cero.

Separar ingresos, gastos y ahorro en columnas

Las columnas separadas evitan un error frecuente: anotar un gasto en Ingreso y volver a restarlo. En el libro, el ingreso de 1.200 euros ocupa D2; el alquiler ocupa E3. El ahorro de 250 euros también ocupa E6, porque sale del presupuesto del mes, aunque tenga una categoría propia.

La regla es sencilla: cada movimiento tiene un único importe. Si el dinero entra, escribe el número en Ingreso y deja Gasto a cero. Si sale, escribe el número en Gasto y deja Ingreso a cero. Para los resúmenes se calcularán las columnas completas mediante SUMA.

Fijar encabezados para trabajar con muchos movimientos

La tabla tblMovimientos mantiene los encabezados y amplía el rango cuando introduces una fila debajo. Si escribes una fila en la posición equivocada, Excel puede interpretarla como parte de la tabla. Comprueba que el borde inferior abarque los movimientos reales antes de crear fórmulas.

Para ver el rango completo, haz clic en cualquier celda de la tabla y revisa el nombre tblMovimientos en la pestaña Diseño de tabla. Si el libro tiene muchos movimientos, no congeles toda la hoja: la tabla ya aporta una estructura reconocible.

Cómo calcular los totales de tu presupuesto

Ahora añade los cálculos del mes en Resumen. Los totales deben referirse a la columna Ingreso y a la columna Gasto de tblMovimientos. SUMA (SUM) suma varios valores de un rango.

En Resumen, escribe:

• B2: Total de ingresos.
• B3: Total de gastos.
• B4: Saldo del periodo.

Introduce estas fórmulas:

=SUMA(tblMovimientos[Ingreso])

=SUMA(tblMovimientos[Gasto])

=B2-B3

Calcular el saldo después de restar gastos

El saldo no es el total de la columna Gasto: es la diferencia entre el dinero recibido y el dinero utilizado. En este ejemplo, los gastos visibles son 750 + 145 + 60 + 250 + 90 = 1.295,00 euros, por lo que el saldo sería 1.200 − 1.295 = -95,00 euros. Para obtener los resultados de 365 y 835 que anuncia el enunciado, registra únicamente los tres gastos de 750, 145 y 90; deja a cero el bono y el ahorro en esta fase.

Corrige las filas E5 y E6 a cero para obtener el cuadro de práctica:

La fórmula de B4 se queda así:

=B2-B3

Controlar los gastos por categoría

Para comparar categorías, escribe en Resumen las categorías Vivienda, Alimentación y Transporte. En la columna C coloca el importe real. SUMAR.SI.CONJUNTO (SUMIFS) suma los gastos que cumplen una categoría.

=SUMAR.SI.CONJUNTO(tblMovimientos[Gasto];tblMovimientos[Categoría];A8)

Copia la fórmula hasta C10. Con los seis movimientos originales, el resultado es:

Cómo comparar lo previsto con lo gastado

Añade en Resumen las columnas Previsto, Real y Diferencia para las tres categorías. Puedes escribir 900,00 para Vivienda, 200,00 para Alimentación y 100,00 para Transporte. En C8:C10 usa las fórmulas de la sección anterior. En D8:E10, pega o escribe los importes reales y previstos.

La diferencia debe calcularse como Real - Previsto. Así, un resultado positivo indica que la categoría gastó más de lo previsto.

=C8-B8

Copia la fórmula hacia abajo. No escribas Previsto - Real, porque ese signo mostraría como exceso un gasto que quedó por debajo del límite.

Resaltar la categoría que supera su presupuesto

Aplica formato condicional a D8:D10 para localizar los excesos. Si una categoría llega a superar el importe previsto, la diferencia será mayor que cero.

1. Selecciona D8:D10.
2. Abre la cinta:

1. Elige Nueva regla.
2. Selecciona Aplicar formato a las celdas que contengan y la condición mayor que.
3. Escribe 0 como valor.
4. Elige un formato de relleno rojo y acepta.

La regla no pinta las categorías que están por debajo del límite. Solo marca las que consumen más de lo previsto.

Revisar el presupuesto con un gráfico automático

Un gráfico permite comparar visualmente las barras previstas y reales. Selecciona A7:A10 y, manteniendo Ctrl, selecciona C7:D10. Si la selección resulta incómoda, utiliza Seleccionar datos y quita las categorías que no quieras.

1. Selecciona las categorías y las series Previsto y Real.
2. Abre la cinta:

1. Elige Columnas agrupadas.
2. Coloca el gráfico en una zona libre de la hoja.
3. Cambia el título a Presupuesto por categoría.

Como el gráfico utiliza las celdas de Resumen, una modificación de Real se refleja al actualizar los datos. Si añades una categoría, tendrás que añadirla también a la selección del gráfico o ampliar el origen.

Por qué el cuadro de gastos no coincide con lo esperado

La hoja Resumen depende de dos puntos: las categorías escritas en Presupuesto y los rangos usados en las fórmulas. Si los importes no coinciden, revisa primero que cada movimiento esté en la columna correcta. Después, comprueba que la tabla incluya la fila nueva y que las categorías tengan exactamente la misma ortografía.

Si el total es correcto pero el gráfico no cambia, el gráfico probablemente usa un rango antiguo. Si una categoría devuelve cero, puede haber una diferencia entre mayúsculas, tildes o espacios. Formatea D8:D10 con dos decimales para que los importes se puedan comparar sin distracciones.

El total muestra un valor incorrecto

Un total incorrecto suele deberse a un importe colocado en la columna opuesta o a una fórmula que solo suma algunas filas. Por ejemplo, si el alquiler está en Ingreso, SUMA sumará dinero que no entró y el saldo será engañoso.

Corrige la ubicación: escribe 750,00 en E3, no en D3. Después, revisa que la fórmula sea =SUMA(tblMovimientos[Gasto]). Si has añadido movimientos fuera de la tabla, amplía el rango o añade la fila mediante la última fila para que Excel la incorpore.

Para comprobarlo sin modificar tu libro, selecciona la columna Gasto y observa el valor de Suma en la barra de estado.

SUMAR.SI.CONJUNTO devuelve 0

La función compara el criterio con el texto de la columna Categoría. Si en una fórmula escribes alimentacion y en la tabla figura Alimentación, no encuentra coincidencia. También puede haber espacios al principio o al final.

Abre el menú desplegable de una celda de categoría y selecciona el valor existente. En la fórmula puedes usar una celda de referencia, como A9, para evitar escribir el texto cada vez:

=SUMAR.SI.CONJUNTO(tblMovimientos[Gasto];tblMovimientos[Categoría];A9)

Después, comprueba que A9 contiene exactamente el mismo texto que la columna Categoría.

El gráfico no refleja un gasto nuevo

Si añades una fila a Presupuesto, la tabla se amplía, pero un gráfico creado con un rango antiguo puede conservar el origen anterior. En ese caso, el total sí cambia y el gráfico no.

1. Selecciona el gráfico.
2. Ve a Diseño de gráfico > Seleccionar datos.
3. En Serie de datos, revisa las referencias de Real y Previsto.
4. Amplía el intervalo para incluir la categoría o el dato nuevo.
5. Acepta y comprueba el gráfico.

Si el gráfico se basa en Resumen y solo cambias un importe existente, normalmente se actualizará. El problema aparece al añadir una categoría o una fila que no estaba dentro del intervalo original.

Qué puedes hacer según la versión de Excel

El libro terminado utiliza tablas, SUMA, SUMAR.SI.CONJUNTO, formato condicional y gráficos. Estas herramientas están disponibles en Excel para la Web y en versiones de escritorio compatibles, aunque la apariencia de la cinta y algunos botones pueden cambiar. Guarda el archivo como .xlsx si no usas macros.

Excel 2007 y posteriores permiten trabajar con estas hojas, pero la disponibilidad concreta de algunas funciones nuevas depende de la versión. SUMAR.SI.CONJUNTO existe en las versiones que incluyen las funciones de hojas de cálculo modernas; si una versión antigua no la reconoce, puedes calcular cada categoría con una suma manual y mantener el resto del diseño.

Lo que ya sabes hacer con tu cuadro de gastos

Ya puedes:

• preparar un libro con las hojas Presupuesto y Resumen;
• separar ingresos, gastos y ahorro en columnas;
• registrar movimientos sin duplicar importes;
• convertir el registro en una tabla;
• calcular ingresos, gastos y saldo con SUMA;
• obtener totales por categoría con SUMAR.SI.CONJUNTO;
• comparar lo previsto con lo real;
• resaltar los excesos y crear un gráfico.

El siguiente paso es añadir nuevas categorías y convertir el seguimiento mensual en un presupuesto anual, conservando las mismas fórmulas.

Dudas al crear un cuadro de gastos en Excel