Probablemente los has encontrado muchas veces antes: archivos muy grandes en Microsoft Excel.
Y todo sobre ellos es terrible .
Son difíciles de enviar por correo electrónico a los colegas o clientes, tardan mucho en abrirse, y tardan una eternidad en actualizar o cambiar las fórmulas cada vez que se cambia un valor.
Lo que significa que estás sentado frente a una especie de barra de carga, perdiendo el tiempo cuando podrías estar volviendo a completar tu trabajo.
Entonces, ¿cómo es que estos archivos se vuelven tan grandes e inviables? Si compartes estos archivos con colegas, compañeros de clase o amigos, probablemente nunca lo sabrás.
Afortunadamente, hay un amplio rango de formas de arreglar esto dentro del mismo Excel.
En este post, puedes encontrar todos los mejores consejos para ayudarte a manejar estos grandes y voluminosos archivos de Excel. Todos los consejos están agrupados en las áreas principales:
- reduciendo directamente el tamaño del archivo ,
- ahorrando tiempo de cálculo ,y
- optimizando las fórmulas .
Glosario
Reducir directamente el tamaño del archivo
En esta sección están las opciones fáciles y directas para reducir el tamaño del archivo. Si no quieres alterar el contenido del libro de trabajo, prueba estas opciones primero.
1. Eliminar el «espacio en blanco» en sus hojas
Esta es definitivamente la fuente más común de tamaños de archivos grandes de Excel, y afortunadamente una de las más fáciles de arreglar.
Excel tiene un «rango usado» para cada hoja de su libro de trabajo. Cuanto más grande es, más grande es el tamaño del archivo.
Para los nuevos & archivos de Excel en blanco, el rango usado es sólo la celda A1. Pero a medida que trabaje en un archivo, aumentará para representar la columna más derecha y la fila más lejana que haya editado o formateado.
Especialmente en los archivos más antiguos, incluso si las celdas están en blanco y no tienen formato, Excel puede estar tratándolas dentro del rango utilizado, lo que lleva a un tamaño de archivo más grande sin ninguna razón.
Ejemplo del «rango usado» en Excel
Si presionas Ctrl + End en cualquier hoja de tu archivo Excel, puedes ver cuál es la «última celda usada». Si ese atajo lleva a muchas filas (o columnas) más allá del final de sus datos, significa que todas esas celdas están aumentando el tamaño del archivo sin ninguna razón.
Aquí hay un ejemplo. Tengo datos que van hasta la columna F y la fila 11, por lo que debería esperar que el rango utilizado vaya hasta la celda F11 (aunque no haya nada en esa celda específica).
Pero cuando presiono Ctrl + End, sucede esto:
(Nota: para obtener este resultado, simplemente introduje un valor en la celda K20 y luego lo borré.)
Cómo fijar el «Rango Usado» en Excel
Para reducir el rango usado a las celdas que estás usando, simplemente borra las filas o columnas en blanco extra:
- Selecciona la primera fila (o columna) en blanco. Puedes hacerlo fácilmente mediante atajos pulsando Mayúsculas + Espacio para la fila actual, o Ctrl + Espacio para la columna actual.
- Presiona Ctrl + Mayúsculas + Flecha Abajo (o Flecha Derecha) para ir al fondo o a la derecha de la hoja de Excel.
- Haga clic con el botón derecho del ratón y seleccione Eliminar. No NO simplemente presione el botón Eliminar – no tendrá el mismo efecto, ¡todo lo que hará es borrar el contenido de las celdas!
En las versiones antiguas de Excel, tendrás que guardar el archivo después de eliminar las filas y columnas innecesarias. Si presionas Ctrl + Fin de nuevo, la última celda usada debería estar ahora junto a las celdas que acabas de eliminar.
2. Comprobar y eliminar las hojas ocultas innecesarias
Si ha recibido un archivo de Excel de otra persona, puede que haya ocultado algunas hojas adicionales como parte del archivo, pero las ha escondido por alguna razón.
Puede reducir el tamaño del archivo de su libro de trabajo de Excel desocultando las hojas de cálculo y comprobando si aún necesita esos datos o no.
3. Guarda tus archivos en formato binario (.xlsb)
He escondido la mejor manera de reducir el tamaño de los archivos al número 3.
Si tienes un archivo de Excel con muchos datos en bruto y muchas fórmulas, ve y guárdalo en el formato binario de Excel (.xlsb) .
Cualquier Macro y VBA se conservará en este formato, así que no tienes que preocuparte por la pérdida de funcionalidad.
La mayoría de los archivos obtendrán una reducción en el tamaño de los mismos de aproximadamente un 50% al guardarlos en formato .xlsb, pero esto dependerá del tipo de datos que contenga el archivo.
¡A veces puedes reducir los archivos hasta un 20% de su tamaño original!
4. Eliminar el formato de los datos en bruto
Si tus datos en bruto contienen formato, entonces va a aumentar el tamaño del archivo en Excel.
Por supuesto, su trabajo o asignación puede requerir que los datos se presenten de una manera específica -pero si nunca se verán directamente y sólo necesitan ser usados para propósitos de cálculo, entonces elimine tanto formato como sea posible.
Se me ocurren algunos ex jefes que me odiarían si eliminara cualquier formato de los archivos que damos a nuestros clientes, pero si es un archivo interno de trabajo, evita formatearlo sólo para que te quede bonito. Ten cuidado de usar las opciones de Borrar Formatos para hacer esto – también quitarás cualquier formato de fecha o moneda de los datos que puede ser confuso (por ejemplo: el 1 de enero de 2016 cambiará a ‘42370’). Los formatos como los bordes, el resaltado y las fuentes únicas son buenos ejemplos para eliminar.
5.Verifique los rangos de formato condicional
El formato condicional es una forma fantástica de comparar visualmente cualquier cambio en los números de los rangos de datos, pero puede tener un precio elevado: ¡un archivo de Excel hinchado!
Un error que cometen algunos usuarios de Excel es aplicar el formato a una hoja entera, o a un grupo entero de filas o columnas.
Vaya a Formato condicional; Manejar reglas y compruebe el rango de celdas de cada una de las reglas. Si el rango aplicado es más grande de lo que necesita, entonces considere limitarlo sólo al rango de datos requerido.
¿Quieres más atajos de formato? Echa un vistazo a este resumen de atajos de Excel para convertirte en un formato rápido como el rayo en Excel.
Ahorre tiempo de cálculo
Si se trata de un archivo grande en Excel, entonces eventualmente terminará con un archivo con muchas fórmulas que se recalculan a medida que se hacen los cambios.
En la mayoría de los libros de trabajo, el tamaño del archivo y el número de cálculos serán tan pequeños que nunca notará que Excel recalcula cada vez que actualiza un valor del que depende. Pero, cuando tengas cientos de miles de fórmulas el tiempo comenzará a sumar, lo que significa que a menudo puedes estar sentado haciendo girar tus pulgares durante minutos.
1. Deshabilitar el cálculo automático de la fórmula
Si el cálculo de la fórmula está retrasando tu trabajo, entonces adelante y desactívalo.
Un gran número de usuarios de Excel ni siquiera saben que esta opción está disponible, la pueden encontrar en la pestaña Fórmulas en la cinta de Excel, hacia el final:
Mientras que el cálculo de la fórmula está en Manual, puede forzar un recálculo de todo el libro de trabajo pulsando la tecla F9 . Si sólo quieres recalcular una hoja, entonces Mayúsculas + F9 calculará la hoja actualmente seleccionada.
Ahora, puede que noten algún comportamiento extraño cuando se seleccione Manual. Si editas una celda que contiene una fórmula y presionas Enter, esa fórmula se calculará como de costumbre. Si luego copias esa fórmula a otras celdas, el resultado anterior se mostrará (¡temporalmente!) hasta que pulses la tecla F9. No te preocupes por eso, sólo sigue trabajando como siempre y una vez que pulses la tecla F9 (o cambies la opción de Cálculo de nuevo a Automático) la hoja se actualizará completamente.
Y si quieres que se actualice una fórmula simple, puedes editar la celda (F2), pulsar Intro, y esa fórmula se calculará.
Una nota final sobre este punto: siempre asegúrese de cambiar el cálculo a Automático cuando termine de trabajar en un archivo, o cuando esté a punto de enviar el archivo a otra persona.
¿Tienes problemas con los archivos lentos de Excel? Niégate a trabajar con libros de Excel torpes y de bajo rendimiento y echa un vistazo a Cómo arreglar un archivo de Excel lento.
2. Use una ventana de vigilancia para comprobar siempre las células específicas
También en el menú Fórmulas, puede hacer clic en la opción Watch Window para abrir un pequeño cuadro de diálogo que aparece delante de su libro de Excel.
La Ventana de Vigilancia contiene una lista de células a las que específicamente quieres vigilar. Por ejemplo, puede que estés trabajando en un presupuesto detallado que funciona en varias hojas. Todas esas hojas alimentan valores en una hoja de resumen de presupuesto de alto nivel, y quieres saber la ganancia o pérdida final mientras trabajas en otras hojas.
En la ventana de vigilancia, haz clic en el botón «Add Watch» y selecciona la celda que quieras vigilar de cerca. El valor siempre será visible, independientemente de la hoja que esté activa.
Si quieres rellenar el campo Nombre, sigue estos pasos:
- Seleccione el Administrador de nombres en el menú Fórmula.
- Selecciona Nuevo, y escribe la referencia que quieras en el campo Nombre.
- Asegúrate de que la casilla «Se refiere a:» apunta a la celda que estás usando en la Ventana del Reloj.
- Haz clic en OK y cierra el Administrador de nombres.
Optimiza tus fórmulas de Excel
Aparte de reducir el tamaño del archivo directamente, puedes usar los siguientes consejos para optimizar tus fórmulas de Excel para que se ejecuten más rápido.
1. Evitar el uso de fórmulas volátiles
Siete funciones en Excel conocidas como volátiles – es decir, calculan cada vez que cualquier celda se actualiza en el libro de trabajo.
Estas siete funciones sonRAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT() e INFO(). Microsoft las lista aquí en su página de ayuda.
Minimizar el uso de estas funciones en su libro de trabajo reducirá el tiempo de cálculo innecesario.
2.Usar PivotTables o Tablas de Excel
Puede parecer ligeramente contraintuitivo, pero el uso de tablas pivotantes en lugar de una serie de fórmulas es una forma muy eficaz de mostrar los resultados. Las Tablas Pivotantes están diseñadas por Microsoft para operar eficientemente en Excel, así que úsalas si tiene sentido.
Del mismo modo, poner sus datos en una tabla también es muy eficiente. Cualquier fórmula (campos calculados) dentro de la Tabla contribuye menos al tamaño del archivo en comparación con una serie de fórmulas fuera de la escala normal de datos.
¿Quieres saber más sobre las Tablas de Excel? Lee los 9 (+1) beneficios de las Tablas de Excel aquí .
3. Evite referirse a filas o columnas enteras
Soy especialmente culpable de esto – usando una función SUMIF() o VLOOKUP() que le pide a Excel que busque una columna entera, incluso si mis datos son sólo unos pocos cientos de filas. Así que en lugar de tener:
=SUMIF(A:A, $C4, B:B)
Deberías haberlo hecho:
=SUMIF(A1:A100, $C4, B1:B100)
De esta manera, Excel sólo buscará el rango de datos que especifiques, no todas las 1.048.576 filas.
4. Evitar los cálculos repetidos
Para este consejo, quieres reducir el número de referencias celulares que tienen tus fórmulas. No es algo en lo que la mayoría de los usuarios de Excel pensarán, pero vale la pena si estás planeando hacer un gran número de cálculos.
Usemos un ejemplo muy básico:
Digamos que tienes una fórmula que contiene «=$D$5+$E$5», y copias esa fórmula a cien celdas. En este caso, el número total de referencias de celdas es de doscientas, a pesar de que el número de celdas únicas es sólo de dos.
Pero si F5 tiene una fórmula que es =D5+E5, y actualizas esas cien celdas para referirte a la celda F5, entonces has reducido a la mitad el número de referencias de celdas. Excel sólo tiene que calcular D5+E5 una vez, en lugar de cien veces.
Así que si tienes un gran número de fórmulas que realizan el mismo cálculo, considera la posibilidad de romperlas en tu hoja para acelerar el cálculo.
5. Ordenar los datos cuando se usan fórmulas que buscan valores
Cuando navegas a través de un archivador ordenado alfabéticamente, es bastante rápido encontrar lo que quieres (siempre y cuando los artículos en el gabinete estén en orden)!
La misma idea se aplica a Microsoft Excel.
Si usas VLOOKUP(),INDEX(), MATCH(), SUMIF(), o cualquier otra función de Excel que busque un valor específico, tu hoja se calculará mucho más rápido si el dato que estás buscando ya está ordenado.
Aprende más sobre las fórmulas de Excel aquí .
Opción extra: cambiar la extensión del archivo
Además de actualizar el contenido del archivo directamente en Excel, puede probar este otro método para reducir el tamaño del archivo.
Debido a la forma en que se almacenan los archivos .xls/.xlsx, puedes tratarlos como archivos .zip y renombrarlos sin problemas.
Ahora no soy un gran fan de este proceso, ¡pero parece que funciona!
Este es el proceso:
- Navegue hasta su archivo en el Explorador de Windows, y cambie el nombre del archivo de .xls/.xlsx a .zip.
- Descomprime el archivo, como lo harías con cualquier otro archivo .zip. Deberías obtener unas cuantas carpetas y un archivo XML:
- Comprime los archivos que acaban de ser extraídos en un nuevo archivo .zip.
- Cambie el nombre del archivo a la extensión original .xls/.xlsx.
¡Y ahora ya está todo listo!
Así que ahí lo tienes. Ahora tienes muchas opciones para asegurarte de que estás trabajando en archivos de Excel más pequeños y manejables.
Y aún mejor, sus archivos deberían pasar menos tiempo para abrirse, y las fórmulas deberían tomar menos tiempo para calcularse!
¿Alguno de los consejos anteriores te ha ayudado, o todavía tienes problemas para reducir el tamaño del archivo? ¡Háganoslo saber en los comentarios!