Subtotales y agrupar filas: informes que se pliegan y despliegan
Ordena la lista por la columna del grupo (por ejemplo, Región) y ve a Datos › Esquema › Subtotal. En «Para cada cambio en» elige Región, en «Usar función» Suma y marca la columna Importe. Excel inserta una fila de total por grupo con SUBTOTALES y un total general, y añade los botones 1, 2 y 3 a la izquierda para plegar y desplegar el detalle.
Tienes una lista de ventas con cientos de filas y te piden «el total por región, pero que se pueda ver el detalle». Lo habitual es insertar filas a mano, escribir sumas una a una y rezar para que nadie añada una venta en medio. Al día siguiente, los totales ya no cuadran.
Excel tiene una herramienta pensada justo para esto: Subtotal. Funciona como el índice plegable de un libro: ves los capítulos (los totales por grupo) y, si quieres, abres uno para leer las páginas (las filas de detalle). En esta guía añades subtotales en un clic, aprendes a plegar el informe por niveles, a copiar solo los totales y a agrupar filas o columnas a mano.
Qué vas a aprender
- Cómo insertar subtotales por grupo con Datos › Esquema › Subtotal
- Por qué la función SUBTOTALES no cuenta dos veces los totales
- Cómo plegar y desplegar el informe con los botones de nivel
- Cómo copiar solo las filas de total y agrupar filas o columnas a mano
Paso a paso
Ordena la lista por la columna que define los grupos
Subtotal crea un total cada vez que cambia el valor de una columna. Si la lista no está ordenada, tendrás un «Total Norte» cada pocas filas. Haz clic en cualquier celda de la columna Región y ve a Datos › Ordenar y filtrar › Ordenar…. Elige ordenar por Región, de A a Z, y pulsa Aceptar.
En el ejemplo quedan juntas las dos ventas del Norte y las tres del Sur.
Subtotal no funciona dentro de una tabla de Excel (el botón aparece gris). Si tus datos son una tabla, conviértelos antes en Diseño de tabla › Herramientas › Convertir en rango.
Abre Datos › Esquema › Subtotal
Con una celda cualquiera de la lista seleccionada, ve a la pestaña Datos y, en el grupo Esquema (a la derecha de la cinta), pulsa Subtotal. Excel selecciona automáticamente todo el bloque de datos y abre la ventana Subtotales.
Un esquema es la estructura de niveles que permite plegar y desplegar filas; Subtotal lo crea por ti.
Elige el grupo, la función y la columna que quieres sumar
Rellena los tres campos de arriba:
- Para cada cambio en: Región (la columna por la que ordenaste).
- Usar función: Suma. También puedes elegir Cuenta, Promedio, Máx o Mín.
- Agregar subtotal a: marca Importe y desmarca el resto.
Deja marcadas Reemplazar subtotales actuales y Resumen debajo de los datos. Si vas a imprimir una región por página, marca también Salto de página entre grupos. Pulsa Aceptar.
Revisa los totales: Excel usa la función SUBTOTALES
Excel inserta una fila Total Norte y otra Total Sur debajo de cada grupo, y un Total general al final. Haz clic en C4 y mira la barra de fórmulas: no pone SUMA, sino =SUBTOTALES(9;C2:C3). El 9 significa «suma».
La gracia está en el total general: =SUBTOTALES(9;C2:C8) abarca también las filas de total, pero SUBTOTALES ignora otros subtotales, así que no cuenta nada dos veces. Compruébalo: 7.300 + 8.250 = 15.550 €.
Suma las ventas del Norte: 4.200 + 3.100 = 7.300 €.
Suma las ventas del Sur: 2.750 + 3.600 + 1.900 = 8.250 €.
Total general: ignora las filas de subtotal y da 15.550 €.
Pliega y despliega con los botones 1, 2 y 3
A la izquierda de los números de fila han aparecido unos botones con 1, 2 y 3, y unos signos − junto a cada grupo:
- 1: solo el total general.
- 2: los totales por región (lo que quiere ver tu jefe).
- 3: todo el detalle.
Con los signos + y − abres o cierras un solo grupo. También puedes usar Datos › Esquema › Mostrar detalle y Ocultar detalle con una celda del grupo seleccionada.
Si no ves los botones de nivel, ve a Archivo › Opciones › Avanzadas y, en las opciones de presentación de la hoja, activa «Mostrar símbolos de esquema si se aplica un esquema».
Copia solo los totales con Ir a Especial
Pulsa el botón 2 para ver solo los totales. Si ahora copias y pegas, Excel pegará también las filas ocultas. Para evitarlo, selecciona el bloque visible y ve a Inicio › Edición › Buscar y seleccionar › Ir a Especial…. Marca Solo celdas visibles y pulsa Aceptar.
Ahora copia (Ctrl+C) y pega (Ctrl+V) en otra hoja o en un correo: solo llegan Total Norte, Total Sur y Total general.
Agrupa filas o columnas a mano con Datos › Esquema › Agrupar
No siempre quieres totales automáticos. Si tu hoja ya tiene sus sumas (por ejemplo, un presupuesto con columnas de enero a diciembre y una columna por trimestre), puedes crear el plegado tú. Selecciona las columnas de enero a marzo y ve a Datos › Esquema › Agrupar. Si seleccionaste celdas sueltas en lugar de columnas enteras, Excel pregunta si quieres agrupar Filas o Columnas.
Aparece una barra con un signo − encima de las columnas: un clic y el trimestre se pliega hasta dejar solo su total.
Atajo para agrupar la selección: Alt+Mayús+Flecha derecha. Para desagrupar: Alt+Mayús+Flecha izquierda.
Quita los subtotales o el esquema cuando ya no los necesites
Para volver a la lista original, selecciona una celda de los datos, abre otra vez Datos › Esquema › Subtotal y pulsa Quitar todos. Excel borra las filas de total y el esquema, y deja tus datos intactos.
Si lo que quieres quitar es un agrupado manual, usa Datos › Esquema › Desagrupar con las filas o columnas seleccionadas, o Desagrupar › Borrar esquema para eliminar todos los niveles de la hoja.
Trucos de experto
- Dos niveles de subtotal. Ordena por Región y luego por Vendedor. Aplica Subtotal por Región; después vuelve a abrirlo, elige Vendedor y desmarca Reemplazar subtotales actuales. Tendrás totales por vendedor dentro de cada región y un botón de nivel más.
- Usa SUBTOTALES también fuera de esta herramienta. Debajo de una lista filtrada,
=SUBTOTALES(9;C2:C100)suma solo las filas visibles tras el filtro, cosa queSUMAno hace. Con109en lugar de9ignora además las filas que ocultes a mano. - Autoesquema. Si tu hoja ya tiene fórmulas de total bien colocadas, Datos › Esquema › Agrupar › Autoesquema crea los niveles solo, sin insertar filas.
- ¿Necesitas cruzar regiones con meses? Subtotal se queda corto para eso: usa una tabla dinámica, que resume en dos dimensiones y no toca tus datos.
Errores comunes y cómo arreglarlos
Aparece un «Total Norte» cada pocas filas, repetido.
La lista no estaba ordenada por la columna del grupo. Pulsa Subtotal › Quitar todos, ordena por Región y vuelve a aplicar Subtotal.
El botón Subtotal está gris.
Tus datos son una tabla de Excel. Ve a Diseño de tabla › Herramientas › Convertir en rango y vuelve a intentarlo.
Al pegar los totales en otra hoja aparecen también todas las filas de detalle.
Antes de copiar, usa Inicio › Edición › Buscar y seleccionar › Ir a Especial › Solo celdas visibles.
El total general sale el doble de lo que debería.
Alguien ha cambiado SUBTOTALES por SUMA en la última fila. Vuelve a poner =SUBTOTALES(9;C2:C8) o aplica Subtotal de nuevo.
En Excel para Mac el camino es el mismo: Datos › Subtotal (o Datos › Esquema › Subtotal, según el ancho de la ventana) y Datos › Agrupar. Los botones de nivel 1, 2 y 3 y los signos + y − funcionan igual. En los atajos, la tecla Alt de Windows no tiene equivalente directo: usa los botones de la cinta.
Preguntas frecuentes
¿Qué diferencia hay entre Subtotal y una tabla dinámica?
Subtotal inserta filas de total dentro de tu propia lista y te permite plegarla. Una tabla dinámica crea un resumen aparte, sin tocar los datos, y puede cruzar dos dimensiones (región por mes). Para un informe rápido e imprimible, Subtotal; para analizar, tabla dinámica.
¿Qué significan los números 9 y 109 en SUBTOTALES?
El primer argumento elige la operación: 9 es suma, 1 promedio, 2 contar números, 4 máximo, 5 mínimo. Sumándole 100 (109, 101…), además ignora las filas ocultas a mano. Las filas ocultas por un filtro se ignoran siempre.
Mi fórmula SUBTOTALES da error al copiarla. ¿Qué hago?
Si tu Excel usa la coma como separador (por ejemplo, en México), escribe , en lugar de ;. El botón de la página que convierte los separadores lo hace por ti.
¿Puedo poner el total encima de cada grupo en lugar de debajo?
Sí. En la ventana Subtotales, desmarca Resumen debajo de los datos antes de pulsar Aceptar.
Se guarda solo en este navegador.
Sigue aprendiendo
Ordenar y filtrar datos en Excel sin desordenar las filas
Ordena por una o varias columnas, filtra por región, importe o fecha y suma solo lo visible, sin mezclar los datos de cada fila. Paso a paso, en 5 minutos.
Tablas dinámicas en 5 minutos: resume miles de filas sin escribir fórmulas
Crea tu primera tabla dinámica en Excel y obtén el total de ventas por región y producto sin fórmulas. Paso a paso con imágenes y datos de ejemplo.
Tus primeras fórmulas: SUMA, PROMEDIO, MAX, MIN y Autosuma
Escribe tus primeras fórmulas en Excel: suma un rango con SUMA, calcula la media con PROMEDIO, encuentra el máximo y el mínimo y usa Autosuma, paso a paso.
Imprimir en Excel sin que se corte: área de impresión, ajustar a una página y repetir títulos
Define el área de impresión, ajusta todas las columnas a una página y repite la fila de títulos en cada hoja. Paso a paso, con imágenes, en 5 minutos.
Tablas de Excel: el formato que hace que filtros, fórmulas y gráficos funcionen solos
Convierte tus datos en una tabla de Excel: filtros, totales y fórmulas que se amplían solos cuando añades filas. Paso a paso y con ejemplos, en 6 minutos.