Cómo hacer un Excel de gastos e ingresos con fórmulas y gráficos

Una hoja de Excel de gastos e ingresos sirve para apuntar cada movimiento del mes y ver, sin hacer cuentas a mano, cuánto has gastado, cuánto has ingresado y cuánto te queda. En este tutorial montas esa hoja desde cero: empiezas con cuatro columnas (Fecha, Concepto, Categoría e Importe) y terminas con totales automáticos, una lista desplegable para no escribir mal la categoría, formato condicional para localizar los gastos grandes y una tabla dinámica que resume el mes por categorías. Todo funciona en Microsoft 365, Excel 2024, 2021, 2019 y 2016, y también en Excel para la web y para Mac.

Qué es una hoja de gastos e ingresos y para qué te sirve

Una hoja de gastos e ingresos no es más que una tabla donde apuntas, uno debajo de otro, cada gasto y cada ingreso del mes. La gracia está en que, si las columnas están bien pensadas, Excel se calcula solo cuánto llevas gastado, cuánto llevas ingresado y cuánto te queda a final de mes.

La usas para tres cosas muy concretas: saber en qué se te va el dinero (transporte, comida, ocio), detectar los meses en los que te pasas y, cuando llega el momento de hacer cuentas, tener los datos ya ordenados para resumirlos. No necesitas saber programar ni haber usado fórmulas antes: las funciones que aparecen aquí (SUMA, SUMAR.SI, BUSCARV si quieres) están en cualquier versión de Excel.

La idea de fondo es que la hoja crezca contigo. Empiezas con un mes y, cuando le pillas el truco, la misma estructura te sirve para un trimestre, un año o para varios miembros de la familia. El resto del tutorial va de eso: de armar esa hoja para que trabaje sola.

Las piezas de la hoja: categorías, fechas, importes y totales

La hoja se sostiene sobre cuatro columnas básicas. La primera es Fecha, en formato de fecha corta, para ordenar los movimientos y filtrar por meses. La segunda es Concepto, en texto libre, donde escribes qué fue ese gasto o ingreso. La tercera es Categoría, con valores fijos como Comida, Transporte, Suministros, Ocio, Nómina o Transferencia, para poder agrupar después. La cuarta es Importe, en formato moneda, donde los gastos van en positivo y los ingresos también, porque lo que distingue a unos de otros es la categoría.

Debajo de la última fila de datos se colocan tres totales: Total de gastos, Total de ingresos y Balance (ingresos menos gastos). La fila de Balance es la que miras a fin de mes para saber si te ha sobrado o has entrado en números rojos.

La primera fila de la hoja lleva los encabezados en negrita, y a partir de la segunda fila empiezan los datos. Así, cuando conviertas el rango en Tabla, Excel entiende dónde acaba cada columna y dónde empieza la siguiente.

Montar la hoja a mano: columnas, formatos y primera fila de totales

Empezamos con la hoja Gastos, que es donde vas a apuntar todo. Primero, las cabeceras; después, cinco gastos y dos ingresos reales; por último, el formato Moneda en la columna D. Más adelante, en otra sección, sumaremos estos importes.

1. Renombra la hoja inferior como Gastos (doble clic sobre la pestaña Hoja1 y escribe el nombre).
2. Escribe los encabezados en A1:D1: Fecha, Concepto, Categoría, Importe.
3. Rellena las filas 2 a 10 con estos datos:

1. Selecciona el rango D2:D10, abre Formato de celdas con Ctrl+1 y en la pestaña Número elige Moneda con dos decimales.

Calcular totales y el balance del mes con SUMA y SUMAR.SI

Ahora que la hoja tiene datos, le sumamos los totales. Para que la fórmula pueda arrastrarse, usamos referencias absolutas cuando haga falta. La idea es ver de un vistazo cuánto se ha gastado, cuánto se ha ingresado y cuál es la diferencia.

En la fila 11, debajo de los datos, escribe las etiquetas Total Gastos, Total Ingresos y Balance en A11, A12 y A13, y en D11, D12 y D13 las fórmulas.

La primera suma coge todo: como los ingresos también son positivos, sumarlos todos y restar solo los que son gastos no sirve. La forma limpia es sumar por categoría. Reescribe las tres celdas así:

=SUMAR.SI(C2:C10;"<>Nómina";D2:D10)
=SUMAR.SI(C2:C10;"<>Varios";D2:D10)-SUMAR.SI(C2:C10;"<>Nómina";D2:D10)+SUMAR.SI(C2:C10;"<>Varios";D2:D10)
=D12-D11

La segunda fórmula es un lío. Mejor separar los ingresos en otra columna, pero como aquí queremos mantener una sola hoja, lo más claro es añadir un signo a los gastos y dejar los ingresos en positivo, y después usar SUMA con un filtro.

En realidad, lo más sencillo y limpio es: en la columna C usar categorías de gasto y una categoría especial llamada Ingreso para nóminas y ventas. Reescribe la celda C8 como Ingreso y C9 como Ingreso, y deja las fórmulas así:

=SUMAR.SI(C2:C10;"<>Ingreso";D2:D10)
=SUMAR.SI(C2:C10;"Ingreso";D2:D10)
=D12-D11

Una lista desplegable para no escribir la categoría mal

Escribir la categoría a mano siempre acaba en «Comida», «comida» y «comidas» como tres valores distintos. Una lista desplegable en C2:C10 obliga a elegir entre las categorías válidas, y los SUMAR.SI funcionarán siempre.

1. Escribe en F2:F8 las categorías: Comida, Transporte, Ocio, Suministros, Salud, Ingreso, Varios.
2. Selecciona C2:C10.
3. Abre Validación de datos.

1. En la pestaña Mensaje de error deja el estilo Detener para que no se pueda escribir una categoría que no esté en la lista.

Colorear de un vistazo los gastos grandes con formato condicional

Con la hoja ya validada, llega el momento de que los números grandes salten a la vista. El formato condicional cambia el aspecto de la celda según su valor, sin que tengas que repasar la columna a mano.

1. Selecciona D2:D10.
2. Abre el menú de formato condicional.

1. En el cuadro que se abre, escribe 100 y elige Relleno rojo claro con texto rojo oscuro.

Después, para tener una pista visual de la magnitud de cada importe, añade una barra de datos sobre la misma columna.

En la columna D, la celda D8 (1.650,00) y la D9 (120,00) aparecen sombreadas en rojo, y todas las celdas llevan una barra de datos azul proporcional al importe.

Ver los gastos por categoría con una tabla dinámica

La tabla dinámica resume los datos sin que escribas una sola fórmula. Primero hay que convertir el rango en Tabla, así cada fila nueva se incluye sola en el resumen.

1. Selecciona A1:D10.
2. Convierte el rango en Tabla.

1. Marca La tabla tiene encabezados y pulsa Aceptar. Excel pasa a llamarla Tabla1 y le da un estilo.
2. Con una celda de la Tabla1 seleccionada, inserta una tabla dinámica en una hoja nueva.

1. Acepta el destino Hoja nueva.
2. En el panel de la derecha, arrastra Categoría a Filas e Importe a Valores (ya viene como Suma por defecto).

Un gráfico rápido que se actualice solo

Sobre la tabla dinámica puedes montar un gráfico de columnas que se actualice al añadir filas. Selecciona cualquier celda de la tabla dinámica, ve a la pestaña Analizar tabla dinámica que aparece al seleccionarla, y pulsa Gráfico dinámico. Elige Columnas agrupadas y colócalo a la derecha de la tabla.

[Gráfico de columnas con barras para Comida, Ingreso, Ocio, Salud, Suministros y Transporte, con Ingreso como la más alta]

Para comprobar que se actualiza solo, vuelve a la hoja Gastos y añade una fila nueva con un gasto de 80,00 en Comida. Al volver a la tabla dinámica, pulsa Actualizar todo en la pestaña Analizar tabla dinámica y verás la barra de Comida crecer y el gráfico moverse a la vez.

Por qué tu fórmula falla: los errores más habituales

Tres tropecientas veces la hoja se rompe y la fórmula devuelve algo que no esperas. Casi siempre es por lo mismo: la categoría escrita con un acento o una mayúscula distintos a los de la lista, los datos sin convertir en Tabla, o el símbolo de moneda que no es el que querías.

SUMAR.SI devuelve 0 o #¡DIV/0!

Si =SUMAR.SI(C2:C10;"comida";D2:D10) da 0, es porque en la lista desplegable escribiste «Comida» con C mayúscula. SUMAR.SI distingue mayúsculas y minúsculas solo en el texto que tú escribes, no en el de la celda, pero sí distingue acentos y, sobre todo, no agrupa «comida», «Comida» y «COMIDA» como si fueran la misma cosa si en la lista solo está una.

La solución es unificar criterio: usa siempre la lista desplegable que montaste en F2:F8 para escribir la categoría, y revisa que no haya filas anteriores a la validación con categorías distintas.

Cambia la celda C2 a «Comida» y D11 pasará de 0,00 a 85,40.

La tabla dinámica no incluye las filas nuevas

Si al añadir una fila debajo de los datos la tabla dinámica sigue mostrando los mismos totales, el motivo es que los datos no están en una Tabla. El rango se quedó en A1:D10 y las filas 11, 12, 13 quedan fuera.

La solución es volver a la hoja Gastos, seleccionar una celda del rango, y convertirlo en Tabla con Insertar > Tablas > Tabla. Después, en la hoja de la tabla dinámica, abre Analizar tabla dinámica > Cambiar origen de datos y pon el nuevo nombre Tabla1.

El formato Moneda sale con el símbolo del país equivocado

A veces la columna D muestra euros cuando quieres pesos, o al revés. Eso se cambia en Ctrl+1.

Elige el símbolo de la lista Símbolo (EUR, MXN, ARS, CLP, PEN, según el país) y pulsa Aceptar. La columna entera se reformatea con el símbolo elegido y los totales de la tabla dinámica heredan el nuevo formato al actualizar.

Lo que ya sabes hacer con tu hoja de gastos e ingresos

• Crear una hoja con las cuatro columnas básicas (Fecha, Concepto, Categoría, Importe) y la fila de totales.
• Sumar importes con SUMA y filtrarlos por categoría con SUMAR.SI (SUMIF).
• Validar la columna Categoría con una lista desplegable para que no se cuelen valores mal escritos.
• Aplicar formato condicional para localizar gastos grandes y barras de datos para ver la magnitud de un vistazo.
• Convertir el rango en Tabla y montar una tabla dinámica que agrupa por categoría.
• Insertar un gráfico dinámico que se actualiza al añadir filas nuevas.

El siguiente paso natural es dejar de escribir los movimientos a mano y traerlos del banco: con Power Query, integrado en Excel 2016, 2019, 2021, 2024 y Microsoft 365, puedes cargar extractos en CSV y unirlos a la hoja de gastos sin picar números.

Dudas al montar tu hoja de Excel de gastos e ingresos