EXCEL 2021 Y MICROSOFT 365 / MATRICES DINÁMICAS
Desbordamiento en Excel: 6 funciones con ejemplos
Una fórmula puede devolver una lista completa de clientes o un reporte de facturas pendientes. Aprende a usar seis funciones de matrices dinámicas, comprueba sus resultados y encuentra la causa cuando Excel muestra un error.
Por Roger Pérez · Publicado el 5 de enero de 2025
Portada de la guía de desbordamiento en Excel.
QUÉ HACE LA FÓRMULA
¿Qué significa el desbordamiento en Excel?
El desbordamiento en Excel ocurre cuando una fórmula devuelve varios valores y Excel los coloca en las celdas vecinas. Escribe la fórmula una vez, en la celda superior izquierda. Las demás celdas del rango muestran los resultados que dependen de ella.
Para ver el comportamiento, escribe esta fórmula en F2, con F2:F5 libres, y pulsa Entrar.
=SECUENCIA(4)
Verás 1, 2, 3 y 4 en F2:F5. Cambia el 4 por 6 en F2 para ampliar el rango. Esta es la diferencia entre una matriz dinámica y un resultado de tamaño fijo.
Puedes usar este comportamiento en reportes contables, inventarios o análisis de ventas. Un filtro puede devolver solo las facturas pendientes, y una lista de valores únicos puede mostrar cada cliente una vez.
Demostración animada de una fórmula que llena varias celdas.
6 funciones que generan desbordamiento en Excel
Las seis funciones están disponibles en Excel 2021 y Microsoft 365, además de versiones posteriores compatibles. Elige la función según el resultado que necesitas. La sección de sintaxis incluye un ejemplo de cada una.
ANTES DE ESCRIBIR LA FÓRMULA
Cómo trabajar con un rango desbordado
Confirma con Entrar. Excel calcula el tamaño del rango de salida y lo ajusta cuando la fórmula devuelve más o menos elementos.
Usa la tabla como origen. Puedes filtrar datos de una tabla de Excel con referencias estructuradas, pero escribe la fórmula en una celda fuera de ella para que tenga espacio para desbordarse.
Identifica la celda que contiene la fórmula. Al seleccionar una celda del resultado, Excel resalta el rango con un borde. Solo se edita la celda superior izquierda. En las otras celdas, la barra de fórmulas muestra el texto atenuado.
Si necesitas actualizar el criterio, cambia la fórmula en la celda de origen y confirma con Entrar. El borde desaparece al seleccionar una celda fuera del rango.
Referencia todo el resultado con #. Si la fórmula de desbordamiento en Excel comienza en F2, F2# representa el rango completo y se adapta cuando cambia su tamaño. Consulta el comportamiento de las matrices dinámicas.
Borde de selección que identifica el área de desbordamiento.
EJEMPLOS PARA COPIAR
Sintaxis y ejemplos de desbordamiento en Excel
Los argumentos entre corchetes son opcionales. Los ejemplos usan punto y coma como separador de argumentos. Si tu configuración regional usa comas, adapta el separador al pegar la fórmula.
Datos para practicar en A1:D6
Para reproducir los ejemplos de desbordamiento en Excel, copia estos datos con los encabezados en la fila 1. Escribe las fórmulas en un área libre, fuera de la tabla de origen. Prueba cada ejemplo por separado o usa distintas zonas de la hoja para que sus resultados tengan espacio.
Cliente
Factura
Estado
Saldo
Ana
F-101
Pendiente
120
Luis
F-102
Pagada
0
Ana
F-103
Pendiente
75
Marta
F-104
Pendiente
240
Luis
F-105
Pagada
0
FILTRAR
FILTRAR(matriz; incluir; [si_vacío])
Matriz es el rango de origen que quieres filtrar. Incluir es una matriz de valores lógicos del mismo alto o ancho que la matriz, según filtres filas o columnas. Si_vacío indica qué devolver cuando ninguna entrada cumple la condición.
EJEMPLO
=FILTRAR(A2:D6;D2:D6>0;"Sin pendientes")Muestra las tres facturas con saldo mayor que cero. Si escribes la fórmula en F2, los resultados ocupan F2:I4. Cuando no haya facturas pendientes, aparecerá el texto «Sin pendientes».
Ver la lección de FILTRARORDENAR
ORDENAR(matriz; [índice_orden]; [orden]; [por_col])
Matriz es el rango que quieres ordenar. Índice_orden señala la fila o columna que sirve de criterio. Usa 1 para ascendente y -1 para descendente. Por_col es FALSO de forma predeterminada para ordenar filas y VERDADERO para ordenar columnas.
EJEMPLO
=ORDENAR(A2:D6;4;-1)
En este ejemplo de desbordamiento en Excel, ORDENAR organiza las filas por la cuarta columna, Saldo, de mayor a menor. La factura de 240 aparece primero y las de saldo cero quedan al final.
ORDENARPOR
ORDENARPOR(matriz; por_matriz1; [orden1]; [por_matriz2]; [orden2]; …)
Indica qué matriz vas a mostrar y el rango que define el primer criterio. Usa 1 para ascendente y -1 para descendente. Puedes agregar otro par de rango y orden. Cada rango de ordenación debe corresponder a las filas o columnas de la matriz.
EJEMPLO
=ORDENARPOR(A2:D6;A2:A6;1;D2:D6;-1)
Ordena primero por cliente y, dentro de cada cliente, por saldo de mayor a menor. Así, las dos facturas de Ana aparecen juntas y la de 120 precede a la de 75.
UNICOS
UNICOS(matriz; [por_col]; [exactamente_una_vez])
Por_col en FALSO u omitido compara filas, y VERDADERO compara columnas. Exactamente_una_vez en VERDADERO devuelve solo las entradas que aparecen una vez. Si se omite o es FALSO, devuelve los valores distintos.
EJEMPLO
=UNICOS(A2:A6)
Devuelve Ana, Luis y Marta. Si quieres solo clientes con una aparición en esta lista, usa VERDADERO en el tercer argumento. En estos datos, el resultado sería Marta.
SECUENCIA
SECUENCIA(filas; [columnas]; [inicio]; [paso])
Filas y columnas fijan las dimensiones de la matriz. Inicio define el primer número y paso cuánto aumenta o disminuye cada valor. Con una columna puedes generar una lista vertical de identificadores de prueba.
EJEMPLO
=SECUENCIA(4;1;1001;1)
Con SECUENCIA, el desbordamiento en Excel genera 1001, 1002, 1003 y 1004. Cambia el argumento filas para ampliar la lista o usa más columnas si necesitas una cuadrícula.
MATRIZALEAT
MATRIZALEAT([filas]; [columnas]; [mínimo]; [máximo]; [entero])
Filas y columnas definen el tamaño. Mínimo y máximo acotan los valores. El último argumento es VERDADERO para obtener enteros y FALSO para obtener decimales.
EJEMPLO
=MATRIZALEAT(3;2;10;50;VERDADERO)
Genera tres filas y dos columnas de enteros entre 10 y 50. Los valores pueden cambiar al recalcular. Si necesitas conservar una muestra de prueba, copia el resultado y pégalo como valores.
Cuenta las filas sin fijar el tamaño del rango
Después de probar FILTRAR en F2, escribe esta fórmula en una zona libre. Cuenta las filas del resultado usando el operador #. Con los datos del ejemplo devuelve 3 y se ajusta si cambia la cantidad de facturas pendientes.
=FILAS(F2#)
Un reporte de facturas con desbordamiento en Excel
Construye un reporte que se actualice cuando agregues una factura. Usa los datos de práctica de A1:D6 en una copia del libro. Deja las columnas F:I para mostrar el resultado y utiliza K2 y K3 para revisar el número de facturas y el saldo pendiente.
1. Convierte la base en tabla. Selecciona A1:D6 y elige Insertar → Tabla. Indica que la primera fila contiene los encabezados y asigna el nombre Facturas en Diseño de tabla. Verifica que el encabezado de D1 sea Saldo. El desbordamiento en Excel puede consultar una tabla como origen y devolver sus resultados en una zona separada.
2. Crea el reporte en F2. Escribe la siguiente fórmula fuera de la tabla. Al usar Facturas y Facturas[Saldo], Excel toma las filas de la tabla y mantiene alineado el criterio de saldo con cada registro. Las referencias se ajustan al incorporar filas en Facturas.
=FILTRAR(Facturas;Facturas[Saldo]>0;"Sin pendientes")3. Comprueba el resultado con dos indicadores. En K2 cuenta las facturas con saldo positivo. En K3 suma esos mismos saldos. Con los datos iniciales, deberías obtener tres facturas y un saldo de 435. Estos indicadores permiten verificar el reporte sin depender de cuántas celdas ocupe el desbordamiento en Excel.
=CONTAR.SI(Facturas[Saldo];">0")=SUMAR.SI(Facturas[Saldo];">0";Facturas[Saldo])4. Agrega una factura al final de la tabla. Escribe Marta, F-106, Pendiente y 60 en la fila siguiente. Comprueba que la nueva fila pertenezca a Facturas. El reporte debe pasar de F2:I4 a F2:I5, y los indicadores deben mostrar cuatro facturas y 495 de saldo.
Para comprobar que el desbordamiento en Excel se ajusta al registrar un pago, cambia el saldo de F-101 a cero y marca su estado como Pagada. Debe desaparecer del reporte, y los indicadores deben bajar a tres facturas y 375 de saldo. Si la fila sigue visible, revisa el saldo de origen y confirma que lo cambiaste en la tabla Facturas.
Si tu tabla usa otro nombre o encabezado, sustituye esos nombres en las fórmulas. Puedes consultar la guía de referencias estructuradas de Microsoft para adaptar el ejemplo a tu archivo.
ENCUENTRA LA CAUSA
Cómo corregir el error #¡DESBORDAMIENTO!
El error de desbordamiento en Excel aparece cuando una fórmula devuelve varios valores y Excel no puede colocarlos en la cuadrícula. Empieza por identificar el área de salida prevista y qué la está bloqueando.
Rango ocupado
Selecciona la celda con el error para ver el borde discontinuo del área prevista. Abre la advertencia y usa Seleccionar celdas con obstrucción. Mueve la anotación o la fórmula a otra zona si necesitas conservarla.
Celdas que parecen vacías
Una fórmula que devuelve una cadena vacía sigue ocupando la celda. Revisa la barra de fórmulas de la celda señalada y mueve esa fórmula antes de probar UNICOS otra vez.
Celdas combinadas
Si el rango cruza una celda combinada, separa esa combinación o traslada la fórmula a otra área. Comprueba que todo el espacio previsto quede disponible.
Fórmula dentro de una tabla
Para usar SECUENCIA o FILTRAR con una tabla de Excel como origen, escribe la fórmula fuera de la tabla. La tabla puede seguir guardando tus datos y ampliarse con nuevas filas.
Resultados que salen de la hoja
Si el desbordamiento en Excel intenta pasar de la última fila, mueve la fórmula a una zona con espacio o reduce el rango que usa ORDENARPOR. Evita columnas completas si puedes usar un rango definido.
Tamaño que cambia durante el cálculo
Una dimensión aleatoria puede impedir que Excel determine cuántas celdas necesita. Usa un número fijo para las filas y columnas de prueba. En MATRIZALEAT pueden variar los valores y mantenerse fijas las dimensiones.
Rango demasiado grande
Si Excel indica memoria insuficiente, prueba con menos filas o columnas. Usa una tabla de origen o un rango que incluya solo los registros que necesitas.
Consulta la guía de Microsoft para corregir #¡DESBORDAMIENTO! si la advertencia muestra una causa distinta.
Ejercicio para corregir un error de desbordamiento en Excel
Haz el ejercicio en una copia. Si probaste el reporte anterior y agregaste F-106, vuelve a los datos iniciales de A1:D6. La fórmula de FILTRAR con esos cinco registros debe mostrar tres filas y cuatro columnas desde F2.
1. Reproduce la obstrucción. Quita temporalmente la fórmula de F2 y escribe una nota de prueba en G3. Vuelve a introducir el ejemplo de FILTRAR en F2. Verás el error porque G3 queda dentro de F2:I4. Al seleccionar F2, el borde discontinuo indica dónde intentaba colocar los resultados el desbordamiento en Excel.
2. Ubica la celda que bloquea. Abre el menú de advertencia junto a F2 y usa Seleccionar celdas con obstrucción. Mueve la nota de G3 a K6 para conservarla. La fórmula en F2 debe recalcularse y volver a mostrar las tres facturas. Compara sus números con F-101, F-103 y F-104.
3. Comprueba una celda que parece vacía. Retira otra vez la fórmula de F2 y coloca en G3 una fórmula que devuelva texto vacío. Al volver a introducir FILTRAR, el desbordamiento en Excel se bloquea aunque G3 no muestre texto. Selecciona G3 y mira la barra de fórmulas para identificar el contenido.
Si la fórmula de G3 debe permanecer donde está, mueve FILTRAR a una zona libre, como F8:I10. Comprueba las tres filas y actualiza cualquier fórmula de resumen que todavía apunte a F2#. Trasladar la fórmula también sirve cuando el área original atraviesa una celda combinada.
Cómo distinguir el desbordamiento en Excel de otros errores
Empieza por leer el mensaje de la celda. Una matriz puede tener espacio suficiente y mostrar otro error por el criterio de búsqueda, la versión de Excel o el origen de los datos. Estas comprobaciones te ayudan a elegir la corrección adecuada.
FILTRAR sin resultados. Prueba un criterio que ninguna factura cumpla, como un saldo mayor que 500 en la base inicial. Si omitiste el argumento si_vacío, Excel puede devolver un error de cálculo porque la matriz quedaría vacía. Agrega el mensaje «Sin pendientes» como tercer argumento. En este caso, despejar celdas no cambia el resultado.
#¿NOMBRE? o prefijo _xlfn. Si una función de desbordamiento en Excel no se reconoce, verifica su nombre en el idioma de tu aplicación y comprueba que tu versión admita la función. En un libro compartido con alguien que usa una versión anterior, conviene comprobar la compatibilidad antes de enviar el reporte.
#¡REF! en un archivo vinculado. Las matrices dinámicas vinculadas entre libros tienen compatibilidad limitada cuando se cierra el archivo de origen. Abre ambos archivos para comprobarlo. Si el reporte debe funcionar por separado, considera reunir la tabla de origen y la fórmula en el mismo libro.
Solo ves un valor. Revisa si la fórmula contiene el operador @. Puede indicar que Excel toma un solo elemento de un rango. Si buscas una lista con desbordamiento en Excel, revisa el propósito de la fórmula original antes de cambiar ese operador y confirma que haya espacio para la lista.
Por último, prueba con un registro que cumpla el criterio y otro que no lo cumpla. Si la salida cambia al editar la base, el reporte sigue siendo dinámico. Si pegaste los resultados como valores, repite la fórmula cuando necesites actualizar la lista.
CURSO EN VIDEO
Desbordamiento en Excel en 3 videos
Mira primero cómo funcionan las matrices dinámicas. Después identifica la causa del error y aplica las correcciones a los mismos ejercicios.
PARTE 1 DE 3
Seis funciones de matrices dinámicas
Empieza con ejemplos de facturas, clientes y saldos pendientes. Aprende a reconocer qué celdas necesita cada fórmula para devolver sus resultados.
Ver en YouTubePARTE 2 DE 3
Causas del error de desbordamiento
Identifica qué bloquea los resultados en seis ejercicios, desde una anotación o una fórmula que parece vacía hasta una tabla o el final de la hoja.
Ver en YouTubePARTE 3 DE 3
Soluciones paso a paso
Corrige el desbordamiento en Excel en los ejercicios de la parte anterior y comprueba que la matriz vuelve a ocupar las celdas esperadas.
Ver en YouTubeDUDAS HABITUALES
Preguntas frecuentes sobre el desbordamiento en Excel
¿Cómo solucionar el error de desbordamiento en Excel?
Selecciona la celda con el error, revisa el área prevista y localiza las obstrucciones. Mueve la fórmula fuera de la tabla si se encuentra dentro de ella. Ver el diagnóstico por causa.
¿Cómo detener el desbordamiento de celdas en Excel?
Si solo necesitas un resultado de la matriz, elige qué fila o columna devolver. Por ejemplo, INDICE(F2#;1;1) obtiene el primer valor del rango desbordado. Si quieres conservar una lista fija, copia el resultado y pégalo como valores.
¿Cómo hacer para que no se desborde el texto en Excel?
Para mostrar el texto dentro de la propia celda, selecciona Inicio → Alineación → Ajustar texto o amplía el ancho de la columna. Esta opción cambia la presentación del texto. Ver los pasos para ajustar texto en Excel.
¿Cuándo se produce el desbordamiento?
El desbordamiento en Excel se produce cuando una fórmula devuelve varios valores y tiene espacio para mostrarlos. FILTRAR, ORDENAR, ORDENARPOR, UNICOS, SECUENCIA y MATRIZALEAT son ejemplos. El error aparece cuando Excel no puede colocar el resultado en el área requerida.
PARA SEGUIR PRACTICANDO
Continúa con la lista de reproducción de YouTube
La lista «Desbordamiento en Excel: funciones, causas y soluciones» reúne los tres videos de ExcelyContabilidad. Para seguir los ejercicios en el orden de la guía, empieza por la parte 1 y continúa con la 2 y la 3.
Si necesitas ayuda, déjame tu duda en los comentarios o escríbeme por el chat, WhatsApp o correo electrónico. — Roger Pérez, Aplica Excel Contable.
Comentarios y dudas
Si te aparece un error de desbordamiento en Excel, cuéntame qué función usas, dónde empieza la fórmula y qué hay en el área de salida.