Por qué Excel se cae con archivos grandes y qué hacer en su lugar

Excel casi nunca se cae porque un archivo tenga demasiadas filas. Se cae por lo que hay en esas filas.

Una hoja de 900.000 filas de valores simples abre sin problema en un portátil de 2015. Una hoja de 40.000 filas con formato condicional aplicado a columnas enteras, unos miles de BUSCARV (VLOOKUP) y un caché de tabla dinámica dejará la misma máquina colgada del todo. El número de filas es lo que se nota; casi nunca es la causa.

Así que la pregunta útil es cuál de las cinco causas es la tuya, porque cada una tiene un arreglo distinto y cuatro de ellos llevan menos de un minuto.

Las cinco causas, en orden de probabilidad

1. Se quedó sin memoria (y quizá tengas la versión de 32 bits)

Excel carga todo el libro en RAM, más la cadena de cálculo, el historial de deshacer y un caché por cada tabla dinámica, a menudo varias veces el tamaño del archivo en disco.

Revisa primero tu versión: Archivo → Cuenta → Acerca de Excel. Si la primera línea dice 32 bits, Excel está limitado a unos 2 GB de memoria sin importar cuánta tenga el equipo, y esa es tu respuesta. Microsoft entregó la versión de 32 bits como predeterminada durante años y muchas instalaciones nunca se cambiaron. Reinstalar en 64 bits es, para quien le aplique, el arreglo de mayor valor de esta página.

La señal de un problema de memoria: «Excel no puede completar esta tarea con los recursos disponibles», o un cierre justo al guardar o al abrir un segundo libro.

2. Las fórmulas volátiles lo recalculan todo, todo el tiempo

Algunas funciones se recalculan con cada cambio en cualquier parte del libro, no solo cuando cambian sus entradas. NOW() (AHORA), TODAY() (HOY), RAND() (ALEATORIO), OFFSET() (DESREF), INDIRECT() (INDIRECTO), INFO() y CELL() (CELDA) son las volátiles, y cada una arrastra consigo todo lo que depende de ella.

INDIRECT() suele ser el culpable de un libro lento, y OFFSET() el de uno que se volvió inutilizable. Unos cientos de cualquiera de las dos en una hoja grande bastan para que cada pulsación de tecla dispare un recálculo completo.

Se diagnostica en diez segundos: Fórmulas → Opciones para el cálculo → Manual. Si el libro responde de inmediato, el problema era el recálculo, no el tamaño.

Los arreglos, por orden de preferencia: reemplaza OFFSET/INDIRECT por INDEX, reemplaza VLOOKUP sobre una columna completa por INDEX/MATCH o XLOOKUP sobre un rango acotado, y nunca hagas referencia a A:A cuando A1:A50000 basta. Una referencia a columna completa es un barrido de un millón de celdas por fórmula.

3. Formato aplicado a columnas enteras

Selecciona la columna A, aplica formato condicional o un color de relleno, y Excel puede guardar el estado de formato de un millón de celdas. Hazlo en una docena de columnas y el archivo se infla. Los libros de 40 MB con 5.000 filas de datos son comunes, y abren como si tuvieran un millón.

Dos señales:

• Ctrl+Fin salta a un lugar absurdo (fila 900.000, columna BZ) cuando tus datos terminan en la fila 5.000. Ese es el rango usado, y Excel reserva memoria en función de él.
• El archivo es enorme en relación con su contenido.

El arreglo: selecciona todas las filas debajo de tus datos hasta el final, elimina las filas (no solo el contenido), haz lo mismo con las columnas a la derecha, guarda, cierra y vuelve a abrir. Ctrl+Fin ahora debería caer en la última celda real. Luego vuelve a aplicar el formato condicional al rango real en lugar de a columnas completas.

4. Un caché por cada tabla dinámica

Cada tabla dinámica guarda su propia copia de los datos de origen, a menos que comparta explícitamente un caché con otra. Cinco tablas dinámicas sobre un origen de 200.000 filas son un millón de filas de duplicación oculta dentro del archivo.

El arreglo: crea las tablas dinámicas adicionales copiando una existente en lugar de empezar de cero desde el origen, porque las copias comparten el caché. Y en Opciones de tabla dinámica, desactiva «Guardar los datos de origen con el archivo» cuando puedas.

5. Al final sí es el número de filas

Si el archivo es un CSV de varios millones de filas, nada de lo anterior aplica. Excel admite 1.048.576 filas por hoja y eso es estructural. No se caerá, en rigor; cargará lo que quepa y se detendrá, lo cual es peor. Cómo abrir un archivo CSV grande que Excel no abre es la guía específica para eso, y cómo abrir un archivo CSV de 1 GB compara lo que puede manejarlo en su lugar.

Cómo saber cuál tienes

Dos minutos, en este orden:

1. Archivo → Cuenta → Acerca de Excel. ¿32 bits? Detente aquí; ese es el arreglo.
2. Ctrl+Fin. ¿Cae muy lejos de tus datos? Causa 3.
3. Fórmulas → Opciones para el cálculo → Manual. ¿Responde ahora? Causa 2.
4. Compara el tamaño del archivo con su contenido. Un archivo de 30 MB con 8.000 filas es causa 3 o 4.
5. ¿Nada de lo anterior y el archivo tiene millones de filas? Causa 5. Excel es la herramienta equivocada aquí, no una rota.

Descarta los complementos por separado. Inicia Excel con excel /safe. Si el libro se comporta bien, un complemento era el culpable y nada de lo anterior importa.

Qué hacer en su lugar

Si es un archivo de datos y no un documento. El patrón que complica la vida a la gente es usar una hoja de cálculo como base de datos: cientos de miles de filas, columnas de fórmulas calculadas sobre todas ellas y una tabla dinámica encima. Esa carga de trabajo pide un motor de consultas:

duckdb -c "SELECT region, SUM(amount) FROM 'export.csv' GROUP BY region"

DuckDB responde en un segundo lo que Excel tarda dos minutos y un congelamiento en hacer, y la sintaxis es más fácil que un SUMIFS anidado. Hay un recorrido más completo en ejecutar consultas SQL sobre un CSV sin una base de datos.

Si necesitas quedarte en Excel. Power Query (Datos → Obtener datos) procesa el archivo completo y deja en la hoja solo el resumen, así que millones de filas nunca ocupan una hoja de cálculo. El Modelo de datos maneja más filas de las que cabe en una hoja y permite ejecutar tablas dinámicas directamente sobre él.

Si solo necesitas leer el archivo. Abrir un archivo de datos en algo que lo trate como datos y no como un documento evita las cinco causas de una vez:

CSV File Viewer - Smart CSViPhone y iPad · Android

Lee el archivo en lugar de importarlo: sin recálculo, sin estado de formato, sin cachés de tablas dinámicas. SQL o preguntas en lenguaje normal sobre los valores tal como están escritos.

Qué hace

Dos hábitos que previenen casi todo esto

Separa los valores de la presentación. Una hoja de datos sin procesar, sin formato y sin fórmulas, y una hoja de análisis que haga referencia a ella. La mayoría de los libros catastróficos son una sola hoja haciendo los tres trabajos.

Haz referencia a rangos, no a columnas. A1:A50000 en lugar de A:A, en todas partes. Es el hábito con mayor efecto en que un libro siga siendo utilizable a medida que crece.