Cómo hacer un control de gastos en Excel paso a paso
Organiza tus finanzas personales o del hogar sin planillas rígidas por mes: registra todo en una sola tabla con listas desplegables dinámicas, compara presupuesto vs real y automatiza la carga de tu cartola bancaria.
Un sistema de control de ingresos y gastos en Excel con catálogo de categorías, validación de datos sin errores de tipeo, tablero presupuesto vs real y segmentaciones por mes.
- Reemplazas las doce pestañas separadas por una sola tabla histórica fácil de analizar.
- Creas listas desplegables que se actualizan solas cuando agregas una categoría nueva usando =UNICOS(tblCategorias[Categoría]).
- Comparas cuándo conviene resumir con SUMAR.SI.CONJUNTO y cuándo con Tabla Dinámica, y conectas Power Query para importar cartolas bancarias.
Progreso: 0 / 5 pasos
0%Ruta paso a paso
5 pasos- 01
Catálogo de categorías
Define qué registrar y arma tblCategorias.
- 02
Tabla y desplegables
Valida movimientos con lista dinámica UNICOS.
- 03
Resumen mensual
Compara SUMAR.SI.CONJUNTO vs Tabla Dinámica.
- 04
Presupuesto vs real
Mide desvíos por categoría con alertas visuales.
- 05
Gráficos y cartola
Agrega segmentaciones e importa cartolas con Power Query.
Lo que necesitas
PrerrequisitosVersión recomendada: Excel 2021 o Microsoft 365 recomendado (para usar UNICOS; incluye alternativa compatible con Excel 2016/2019).
- Listado de categorías fijas y variables de tu hogar o finanzas personales.
- Opcional: archivo CSV o Excel de cartola mensual descargado desde tu banco.
Mac y Web: Las tablas, validación de datos, UNICOS, SUMAR.SI.CONJUNTO y tablas dinámicas funcionan tanto en Excel para Windows como en Excel para Mac y Excel para la Web.
Paso 1: Qué registrar y cómo definir tu catálogo de categorías
El motivo número uno por el que la gente abandona un control de gastos al segundo mes es el exceso de detalle: crear 40 categorías distintas o armar una hoja nueva para enero, otra para febrero y otra para marzo. Cuando quieres saber cuánto gastaste en supermercado en todo el año, terminas sumando doce pestañas a mano.
La regla de oro es mantener una única tabla de movimientos para todo el año y un catálogo acotado de 8 a 12 categorías claras en una hoja llamada Config. Crea allí una tabla (Ctrl + T) llamada tblCategorias con tres columnas: Tipo (Ingreso o Gasto), Categoría (por ejemplo Sueldo, Vivienda, Supermercado, Transporte, Cuentas Básicas, Salud, Ocio, Ahorro) y PresupuestoMensual (monto tope en CLP, usando cifras de referencia como $350.000 ficticios para Supermercado o $120.000 ficticios para Cuentas Básicas).
En cuanto a qué registrar exactamente, anota cada movimiento que altere tu liquidez disponible: compras con débito, pagos con tarjeta de crédito (al momento de la compra), transferencias electrónicas, giros en efectivo y abonos de sueldo o boletas de honorarios. No registres movimientos internos entre tus propias cuentas bancarias como si fueran ingresos o gastos reales, porque inflarían artificialmente tus totales mensuales.
Paso 2: Tabla de movimientos con validación de datos dinámica (UNICOS)
Si un día escribes "Supermercado", otro "super" y otro "Supermercado ", tus resúmenes se partirán en tres filas distintas. Para blindar el registro, crea una hoja Movimientos con la tabla tblGastos (Fecha, Tipo, Categoría, Medio de Pago, Descripción, Monto) y aplica formato de moneda CLP sin decimales en la columna Monto.
En la hoja Config, en una celda libre (por ejemplo F2), escribe la fórmula =UNICOS(tblCategorias[Categoría]). Esto genera una lista dinámica que crece sola cada vez que agregas una categoría nueva. Luego selecciona la columna Categoría de tblGastos, ve a Datos > Validación de datos > Permitir: Lista, y en Origen escribe =Config!$F$2# (el símbolo # al final le indica a Excel que tome todo el rango derramado por UNICOS). Así garantizas que nadie pueda ingresar una categoría mal escrita en toda la planilla.
=UNICOS(tblCategorias[Categoría])En Validación de datos > Origen escribe =Config!$F$2# (con el signo numeral). Si usas Excel 2016/2019 sin UNICOS, apunta directamente al rango =tblCategorias[Categoría] usando INDIRECTO.
Paso 3: Resumen por categoría y mes (SUMAR.SI.CONJUNTO vs Tabla Dinámica)
Al igual que en un flujo de caja, agrega una columna calculada Mes en tblGastos con =TEXTO([@Fecha];"aaaa-mm") (o con fecha de inicio de mes =FIN.MES([@Fecha];-1)+1) para agrupar cada compra en su mes correspondiente. A partir de aquí tienes dos caminos excelentes para resumir tus gastos: fórmulas con SUMAR.SI.CONJUNTO o una Tabla Dinámica.
Si optas por Tabla Dinámica, haz clic dentro de tblGastos, ve a Insertar > Tabla dinámica, elige ubicarla en una hoja nueva llamada Analisis y arrastra Categoría a Filas, Mes a Columnas y Monto a Valores (con formato de moneda sin decimales $ #.##0). En pocos segundos tendrás la matriz completa de todo el año sin escribir ni una fórmula.
¿Cuál conviene usar? La tabla comparativa siguiente te ayuda a decidir según cómo prefieres revisar tus números cada fin de mes:
| Criterio | Fórmulas (SUMAR.SI.CONJUNTO) | Tabla Dinámica |
|---|---|---|
| Actualización al ingresar un gasto | Inmediata y automática en tiempo real | Requiere clic derecho > Actualizar (o Ctrl + Alt + F5) |
| Diseño fijo de reporte (Presupuesto vs Real) | Ideal: mantiene el orden exacto de tus filas y colores | Menos flexible si mezclas columnas calculadas externas |
| Exploración rápida (por medio de pago o día) | Requiere escribir nuevas fórmulas | Instantánea arrastrando campos o agregando segmentadores |
| Riesgo de olvidar una categoría nueva | Debes agregarla al resumen si no usas UNICOS | Aparece sola al actualizar la tabla dinámica |
Paso 4: Comparar presupuesto vs gasto real por categoría
Saber que gastaste $380.000 ficticios en supermercado no dice mucho si no lo comparas contra tu meta. En una hoja Resumen, coloca una celda arriba (por ejemplo B2) con el mes a analizar, lista tus categorías de gasto y trae el gasto real del mes con SUMAR.SI.CONJUNTO. Al lado, muestra el Presupuesto Mensual usando BUSCARX sobre tblCategorias, la Diferencia (=Presupuesto - Real) y el % Ejecutado (=Real / Presupuesto).
Aplica formato condicional de barras de datos o escala de color sobre el % Ejecutado: verde cuando estás bajo el 85%, amarillo entre 85% y 100%, y rojo cuando superas el 100% del presupuesto asignado. De esta manera, si tu presupuesto de ocio era de $100.000 ficticios y al día 18 del mes ya llevas $92.000, la alerta amarilla te avisa con tiempo para frenar gastos discrecionales antes del cierre.
También conviene añadir una fila de tasa de ahorro al final del resumen: =(Ingresos_Reales - Gastos_Reales) / Ingresos_Reales. Medir ese porcentaje mes a mes te da una señal mucho más clara de salud financiera que mirar el saldo bancario aislado.
=SUMAR.SI.CONJUNTO(tblGastos[Monto];tblGastos[Categoría];A6;tblGastos[Mes];$B$2)Si también quieres filtrar por Tipo="Gasto" para excluir ingresos, agrega el par tblGastos[Tipo];"Gasto".
Paso 5: Gráficos, segmentaciones e importación de cartola con Power Query
Desde tu Tabla Dinámica, ve a Analizar tabla dinámica > Insertar segmentación de datos y marca Mes y Medio de Pago (Débito, Crédito, Transferencia). Luego agrega un gráfico de barras horizontales ordenado de mayor a menor gasto para ver al instante en qué se va el dinero cada mes.
Acompaña ese gráfico de barras con un gráfico de líneas de evolución mensual que compare tus ingresos totales frente a tus gastos totales a lo largo del año. Ver ambas curvas juntas te permite detectar rápidamente meses estacionales más caros —como marzo por permisos y matrículas, o septiembre y diciembre por celebraciones— y planificar ahorros previos.
Cuando ya tengas el hábito formado, el siguiente salto de productividad es dejar de tipear compra por compra: descarga la cartola mensual de tu banco en CSV o Excel y usa Power Query para limpiar las glosas, estandarizar fechas y anexar los movimientos a tu tabla. Si quieres aprender a armar ese flujo automático paso a paso, revisa nuestra guía para [automatizar Excel con Power Query](/guias/automatizar-excel-con-power-query).
Errores comunes y cómo resolverlos
Diagnóstico rápidoValidación de datos rechaza =Config!$F$2# diciendo que el origen evalúa a un error
Causa
La celda F2 no contiene una fórmula de matriz dinámica (UNICOS) sino un rango normal, o el rango derramado está vacío.
Cómo resolverlo
Verifica que en Config!F2 esté escrita exactamente la fórmula =UNICOS(tblCategorias[Categoría]) y que devuelva al menos una categoría.
Al agregar una compra nueva en tblGastos, la Tabla Dinámica no la muestra
Causa
Las tablas dinámicas en Excel no se recalculan solas al editar celdas; trabajan sobre una caché en memoria.
Cómo resolverlo
Haz clic derecho sobre la Tabla Dinámica y presiona Actualizar, o usa Datos > Actualizar todo.
Doble conteo al pagar la tarjeta de crédito
Causa
Se registraron las compras individuales hechas con tarjeta durante el mes y además se cargó como gasto el pago del estado de cuenta.
Cómo resolverlo
Clasifica el pago de la tarjeta como "Transferencia interna" (fuera de las categorías de gasto) para conservar intactos tus totales reales.
Fórmulas SUMAR.SI.CONJUNTO devuelven 0 aunque hay gastos ingresados en tblGastos
Causa
La celda de filtro $B$2 tiene el mes escrito en formato distinto al de la columna tblGastos[Mes] (por ejemplo "noviembre" frente a "2026-11").
Cómo resolverlo
Estandariza ambos lados usando siempre el formato "aaaa-mm" (como "2026-11") o fechas reales del día 1 de cada mes con FIN.MES([@Fecha];-1)+1.
Plantilla de Control de Gastos Personales y del Hogar
Archivo .xlsx con hoja Config (tblCategorias y lista UNICOS), hoja Movimientos (tblGastos validada) y tablero Presupuesto vs Real con segmentadores. Próximamente disponible para descarga.
Preguntas frecuentes
¿Es mejor usar signos negativos para los gastos o dejar todo en positivo?
Es mucho más cómodo digitar todos los montos en positivo y usar la columna Tipo ("Ingreso" o "Gasto") para distinguirlos. Así evitas errores de teclado al ingresar montos en el celular o en el computador.
¿Qué hago si no tengo UNICOS en mi versión de Excel?
En Excel 2016 o 2019 puedes crear la lista desplegable apuntando directamente a =tblCategorias[Categoría] (si no hay categorías repetidas en esa tabla) o usando =INDIRECTO("tblCategorias[Categoría]") en el cuadro de Validación de datos.
¿Cómo registro gastos compartidos en pareja o familia?
Agrega una columna Persona o Responsable en tblGastos. Con eso podrás usar una segmentación adicional en tu Tabla Dinámica para ver el total del hogar o filtrar cuánto gastó cada integrante.
¿Cada cuánto tiempo conviene actualizar el control de gastos?
Lo ideal es dedicar 5 minutos una vez a la semana o importar la cartola bancaria cada 15 días. Esperar al día 30 hace más difícil recordar en qué se gastó cada compra de supermercado o transferencia.
¿Sirve este mismo modelo para controlar gastos de un emprendimiento pequeño?
Para finanzas personales o del hogar funciona perfecto. Si manejas un emprendimiento o pyme con facturación a plazo y pagos de IVA (F29), te conviene dar el paso hacia un flujo de caja formal separado de tus gastos personales.
Fuentes oficiales y recursos
- Referencia oficial de validación de datos en Excel(Microsoft Learn)
- Documentación oficial de tablas dinámicas en Excel(Microsoft Learn)
Progreso: 0 / 5 pasos
0%