8 formas de reducir el tamaño de los archivos de Excel (que realmente funcionan)

Se sabe que los libros de trabajo de Excel se vuelven dolorosamente lentos y pesados a medida que se añaden más datos/fórmulas/imágenes.

A veces, es fácil deshacerse de la flacidez extra y mantener el tamaño del archivo de Excel bajo control, y a veces, no lo es.

Y como Excel no es una herramienta basada en la web, la mayoría de nosotros trabajamos y colaboramos enviando archivos de Excel por correo electrónico. Y un archivo de Excel grande que pesa 50 MB puede ser difícil de compartir con tus colegas/clientes.

En mi último trabajo, utilizábamos el cliente de correo electrónico Lotus Notes, que es caracol-lento, y enviar archivos pesados de Excel a través de eso era una tortura.

Aunque no hay una forma mágica de reducir el tamaño de un libro de Excel, hay algunas técnicas que puede utilizar para reducir algo de flacidez.

Consejos para reducir el tamaño del archivo de Excel

A continuación se presentan las técnicas que puede utilizar para reducir el tamaño del archivo de su libro de Excel.

Para cada consejo, he hecho algunas pruebas y he añadido las instantáneas para mostrarle el impacto en el tamaño del archivo. Los resultados que obtengas variarán en función de tus datos y archivos.

En función de tu libro, puedes utilizar uno o varios de estos consejos.

Eliminar hojas de trabajo/datos innecesarios

Las mejores soluciones suelen ser las más sencillas.

Y esta técnica es más de sentido común y menos de cualquier mago de Excel.

Si tiene un libro de Excel que tiene algunas hojas de trabajo que no son necesarias (y no se están utilizando en ninguna fórmula), vea si puede deshacerse de ellas.

Esto tiene una correlación directa con el tamaño del archivo de Excel. Así que tan pronto como elimine algunas de las hojas de trabajo y guarde el libro, verá instantáneamente un tamaño de archivo reducido.

Resultados de la prueba:

Creé un archivo de Excel con 10 hojas de trabajo donde cada hoja de trabajo tiene 2.6 millones de puntos de datos (26 columnas con 100.000 puntos de datos en cada columna).

El tamaño resultante de este libro es de 143 MB (y está tardando bastante en guardarse y abrirse).

Ahora cuando he eliminado cinco hojas, el tamaño se ha reducido a la mitad (duh).

Como puedes ver, los datos que tiene tu hoja de trabajo tienen una correlación directa con el tamaño del archivo.

Convertir a formato binario (XLSB)

¿Sabes que sólo con guardar un archivo de Excel en formato XLSB puedes reducir el tamaño del archivo?

Sí, es cierto.

Todo lo que tienes que hacer es, mientras guardas un libro de trabajo, utilizar el formato .xlsb.

Aquí tienes los pasos para hacerlo:

1. Haz clic en la pestaña Archivo.
2. Haz clic en Guardar como.
3. Haga clic en Examinar.
4. En el cuadro de diálogo Guardar como, cambie el tipo de archivo a Libro de trabajo binario de Excel (.xlsb)

Resultados de la prueba

Tenía un libro de Excel que ocupaba 14,6 MB de espacio.

Después de convertir el mismo archivo al formato XLSB, pude reducir el tamaño a 10,4 MB (una reducción de tamaño de ~28%).



¿Qué es XLSB?

Cuando guardas un archivo de Excel (.xlsx o .xlsm), se guarda en formato XML.

Por ejemplo, si cambio la extensión del archivo XLSX a ZIP y luego lo abro, esto es lo que obtengo.



Y si además voy a la carpeta ‘xl’ y luego voy a la carpeta de hojas de trabajo, me muestra las hojas de trabajo de ese libro (y estas hojas de trabajo se guardan como un documento XML).



Ahora vamos a hacer lo mismo con un archivo XLSB.

Así que primero cambio la extensión del archivo a ZIP, luego lo abro y localizo la hoja1. Esto es lo que se obtiene:



Notarás que tanto XLSX como XLSB guardan la hoja de trabajo en un formato diferente.

XLSX/XLSM la guarda como un archivo XML y XLSB la guarda como un archivo binario (.bin).

Y sin ponernos demasiado técnicos, los archivos XML son de gran tamaño en comparación con un archivo binario.

Así que cuando guarda su libro de Excel en el formato XLSB, el tamaño del archivo se reduce. En nuestro ejemplo anterior, la reducción de tamaño fue de ~30%.

XLSB Vs XLSX/XLSM

Cuando se trata de utilizar el formato XLSB, la reducción de tamaño es un gran beneficio. Pero hay que tener un poco de precaución cuando se trabaja con el formato XLSB.

Ventajas del formato XLSB:

1. Reduce el tamaño del archivo de Excel. Esto puede ser útil cuando tienes archivos grandes y quieres compartirlo por correo electrónico. Puedes reducir rápidamente el tamaño guardándolo en XLSB.
2. Los archivos XLSB tienden a abrirse y guardarse más rápido que el archivo XML. Según las pruebas limitadas que hice, los archivos XLSB se abrían y guardaban entre un 25% y un 50% más rápido. Esta diferencia es notable sólo en archivos Excel grandes. Con archivos más pequeños, tanto XLSB como XLSX son rápidos.

Aunque XLSB tiene un aspecto estupendo, hay algunas razones por las que debería seguir utilizando archivos XLSX/XLSM:

1. Con el formato XLSB, no tiene forma de saber si tiene una macro o no. Esto lo hace más arriesgado ya que alguien puede utilizarlo para ejecutar código malicioso.
2. Los archivos XLSB están codificados en formato de archivo bin propietario mientras que XML es un formato de archivo legible de código abierto. Esto significa que si utiliza herramientas de terceros con Excel, es mejor utilizar el formato XML.
3. Power Query no puede leer datos de un archivo XLSB. Así que si usas Power Query, es mejor tener tus datos en formatos XLSX/XLSM.

Como regla general, si el tamaño de tu archivo es pequeño (menos de 5 MB), es mejor ceñirse a los formatos XLSX/XLSM.

En base a lo que he escuchado de la gente y he leído en muchos foros, mucha gente prefiere usar XLSB como formato de archivo cuando se trata de usar Excel.

Aquí hay un buen artículo sobre la comprensión del formato XLSB.

Eliminar fórmulas innecesarias

Demasiadas fórmulas hincharán el tamaño de su archivo de libro de trabajo.

Así que si tiene un conjunto de datos que necesita, pero no necesita las fórmulas en él, es mejor convertir estas fórmulas en valores.

Aquí están los pasos para convertir rápidamente las fórmulas en valores en Excel:

1. Seleccione toda la hoja de trabajo/conjunto de datos.
2. Pulse la tecla F5.
3. En el cuadro de diálogo Ir a especial, haga clic en ‘Especial’.
4. En el cuadro de diálogo Ir a especial, seleccione Fórmulas.
5. Haga clic en Aceptar.

Los pasos anteriores seleccionarían todas las celdas que tienen una fórmula en ella.

Ahora utilice los siguientes pasos para convertir estas fórmulas en valores:

1. Copie las celdas seleccionadas (control + C)
2. Haga clic en la pestaña Inicio.
3. En el grupo Portapapeles, haz clic en Pegar.
4. Haga clic en el icono Pegar valor.

Los pasos anteriores convertirían todas las celdas con fórmulas en valores.

Resultados de la prueba

Creé un archivo de Excel con 2,7 millones de puntos de datos (1048576*26) y luego añadí tres columnas con fórmulas (SUM, COUNT y AVERAGE).

Luego convertí estas fórmulas en valores y guardé el archivo con un nombre diferente.

A continuación se muestra el resultado:



El archivo sin fórmulas tiene un tamaño ~8% menor (y ten en cuenta que sólo tenía 3 columnas de fórmulas).

En caso de que tengas muchas fórmulas, puede aumentar el tamaño.

NOTA: Las fórmulas volátiles pueden realmente inflar el tamaño de tu archivo. Como parte de la prueba, cuando sustituí la fórmula SUM por una fórmula volátil (OFFSET), se produjo un salto en el tamaño del archivo (de 186 MB a 198 MB). Así que si tienes fórmulas volátiles que puedes sustituir por fórmulas no volátiles, o puedes convertirlas en valores, te ayudará a reducir el tamaño del archivo.

Comprimir imágenes (antes y después de subirlas)

Si trabajas con archivos de Excel que tienen imágenes, necesitas optimizar estas imágenes y asegurarte de que el tamaño es menor.

El problema con las imágenes es que a medida que las comprime, también hay una reducción de la calidad, que puede no querer.

Cuando se trabaja con imágenes, la mejor manera de mantener el tamaño de su archivo de Excel bajo es comprimiendo las imágenes antes de insertarlo en Excel.

Una buena herramienta online para hacer esto es TinyPNG (o puedes usar Photoshop o cualquier otra herramienta similar).

Es una herramienta gratuita que puede reducir el tamaño de una imagen hasta en un 90% y sigue manteniendo una calidad de imagen decente.

Abajo está la captura de pantalla en la que pude comprimir una imagen con el tamaño de 3,9 MB a 331 KB con una mínima pérdida de calidad.



Una vez que haya subido estas imágenes comprimidas a Excel, puede comprimirlas aún más utilizando la opción incorporada en Excel.

Aquí tienes los pasos para comprimir una imagen en Excel:

1. Selecciona la imagen que quieres comprimir.
2. Haz clic en la pestaña Formato de herramientas de imagen. Se trata de una pestaña contextual que sólo aparece al hacer clic en una imagen.
3. En el grupo Ajuste, haga clic en la opción ‘Comprimir imagen’.
4. En el cuadro de diálogo Comprimir imágenes:
   • Seleccione «Aplicar sólo a esta imagen» si desea comprimir todas las imágenes del libro de trabajo.
   • Seleccione Correo electrónico (96 ppi) para obtener la máxima compresión.
   • Haga clic en Aceptar.
   • Guarde el archivo.

   Esto comprimirá todas las imágenes y le ayudará a reducir el tamaño del archivo de su libro de Excel.

   Resultados de la prueba:

   Utilicé la opción de comprimir imge en Excel utilizando una imagen.

   A continuación se muestran los resultados:

   

   Al utilizar la opción de comprimir imágenes, el tamaño del archivo se redujo de 445 KB a 43 KB (~90% de reducción en el tamaño del archivo).

   Esto también dependería de la imagen. Una imagen que ya está comprimida puede no dar lugar a una gran reducción de tamaño.

   Eliminar la caché de pivote para ahorrar espacio

   Si trabaja con tablas dinámicas y lucha con archivos de Excel de gran tamaño, conocer la caché de pivote puede ser útil.

   Cuando crea una tabla dinámica utilizando un conjunto de datos, Excel crea automáticamente la Caché dinámica.

   La Caché dinámica es un objeto que contiene una réplica de la fuente de datos.

   Aunque no puede verla, forma parte del libro de trabajo y está conectada a la Tabla dinámica. Cuando usted realiza cualquier cambio en la Tabla Pivotante, no utiliza el origen de datos, sino que utiliza la Caché Pivotante.

   Aunque usted piensa que está directamente vinculado a los datos de origen, en realidad, accede a la caché pivotante (y no a los datos de origen) cuando realiza cambios en la tabla pivotante.

   Ahora bien, dado que estás creando una réplica del conjunto de datos, ésta ocupa algo de espacio.

   Así que en cuanto crees una tabla dinámica, notarás que el tamaño del archivo de Excel aumenta.

   

   En el ejemplo anterior, en cuanto añado la tabla dinámica, el tamaño del archivo se multiplica por dos.

   Ahora bien, hay dos formas de reducir el tamaño del archivo cuando se trabaja con tablas dinámicas.

   1. Mantener los datos de origen y eliminar la caché de la tabla dinámica
   2. Mantener la caché de la tabla dinámica y eliminar los datos de origen

   Veamos estas dos técnicas.

   Mantener los datos de origen y eliminar la Caché de la Pivote

   Cuando haya terminado con la tabla Pivote y la esté guardando, puede forzar a Excel a que sólo guarde los datos de origen y la Tabla Pivote resultante, y no la Caché de la Pivote.

   Hacer esto hará que su archivo de Excel sea más ligero, y tendrá una forma de recrear la Caché de la Pivote cuando la necesite la próxima vez.

   A continuación se muestra la diferencia de tamaño al guardar un archivo de Excel con y sin Caché Pivotante.

   

   Aquí están los pasos para NO guardar la Caché Pivotante:

   1. Seleccione cualquier celda de la Tabla Pivotante.
   2. Haga clic en la pestaña Analizar.
   3. Haga clic en el icono ‘Opciones’ de la tabla dinámica.
   4. En el cuadro de diálogo Opciones de la tabla dinámica, haga clic en la pestaña Datos.
   5. En las opciones de Datos de la tabla dinámica, desactive la opción ‘Guardar datos de origen con el archivo’. Esta opción garantiza que la caché de la tabla dinámica no se guarde al guardar el libro (y ayuda a reducir el tamaño del archivo de Excel).
   6. Marque la opción – ‘Actualizar datos al abrir el archivo’. Esta opción garantiza que, en cuanto abra el libro de trabajo, la tabla dinámica se actualice y la caché dinámica se genere automáticamente. Si no marca esta opción, tendrá que actualizar la tabla dinámica manualmente para generar la caché dinámica.
   7. Haga clic en Aceptar.

   Ahora, cuando guarde el archivo con la tabla dinámica, notará que el tamaño del archivo se ha reducido.

   Mantenga la caché dinámica y elimine los datos de origen

   Otra forma de reducir el tamaño del archivo de Excel es eliminar los datos de origen de una tabla dinámica y mantener sólo la caché dinámica.

   Esto funciona ya que una Caché Pivotante es una imagen reflejada de sus datos y usted no accede a los datos de origen cuando hace cambios en la Tabla Pivotante, en su lugar, utiliza la Caché Pivotante.

   Abajo están los resultados cuando guardé el archivo de Excel sin los datos de origen.

   

   Para hacer esto, simplemente elimine los datos (ya sea eliminando los datos o la hoja de trabajo que tiene los datos).

   Cuando quiera recuperar los datos de origen, haga doble clic en la celda ‘Grand Total’ de la tabla Pivot y generará instantáneamente todo el conjunto de datos en una nueva hoja de trabajo.

   Comprimir el archivo (ZIP IT)

   Esto es algo que puede hacer fuera de la aplicación Excel.

   Cuando comprime un archivo de Excel, verá una reducción de tamaño de alrededor del 10-15% inmediatamente.

   A continuación, puede compartir este archivo comprimido a través de correo electrónico, y la persona que lo reciba puede descomprimir el archivo y utilizar el libro de Excel.

   Aquí están los pasos para comprimir un archivo de Excel (para Windows):

   1. Haga clic con el botón derecho del ratón en el archivo de Excel
   2. Pase el cursor por la opción Enviar a.
   3. Haga clic en la opción ‘Carpeta comprimida (zip)’.

   Esto creará un archivo comprimido para el libro de Excel seleccionado.

   También notará una reducción en el tamaño del archivo (como se muestra a continuación).

   Resultados de la prueba

   

   Con esta técnica, pude reducir el tamaño del archivo del libro de Excel en un 8%.

   Esta técnica funciona mejor para los archivos que están en el rango de 20-50 MB. A medida que el tamaño del archivo aumenta, el porcentaje de reducción no es muy alto.

   Eliminar el formato de los datos

   El formato de los datos, como aplicar un color de fondo, o añadir bordes, o cambiar el estilo de la fuente, puede aumentar el tamaño del archivo.

   Aunque, basándome en mis pruebas, he observado que no tiene un gran impacto.

   A continuación se muestran los tamaños de dos libros de Excel, uno con 2,6 millones de puntos de datos (en una hoja de cálculo), y el otro con los mismos datos pero con el formato aplicado (color de celda, borde y cursiva).

   Resultados de la prueba

   Puedes ver que la diferencia entre los dos archivos no es tan grande (aunque sí influye cuando tienes muchos datos).

   

   El archivo con datos ocupa 14.6 MB y el archivo con datos y formato es de 14,9 MB.

   Así que si quieres reducir el tamaño del archivo de tu libro de Excel, puedes buscar el formato que no necesitas. Eliminarlo puede ahorrarte algo de espacio en el disco.

   Aquí tienes los pasos para eliminar instantáneamente todo el formato, manteniendo los datos intactos:

   1. Selecciona todo el conjunto de datos (del que quieres eliminar el formato).
   2. Haz clic en la pestaña Inicio.
   3. En el grupo Edición, haga clic en ‘Borrar’.
   4. Haga clic en ‘Borrar formatos’.
   5. Guarde el libro.

   Quitar el formato condicional

   Al igual que el formato de datos regular, el formato condicional también se suma al tamaño del archivo.

   Sin embargo, en base a mis pruebas, me di cuenta de que la diferencia es menor (a menos que tenga múltiples reglas aplicadas a múltiples hojas con enormes conjuntos de datos).

   No obstante, es una buena idea eliminar el formato condicional de cualquier conjunto de datos en el que no lo necesite.

   Además del tamaño, hay otra buena razón para eliminar el formato condicional: la VELOCIDAD.

   El formato condicional es volátil y cada vez que hay algún cambio en la hoja de trabajo, se recalcula. Esto puede conducir a un libro de trabajo más lento.

   Resultados de la prueba

   Creé un archivo con 27,2 millones de puntos de datos y le apliqué 5 reglas de formato condicional.

   La diferencia de tamaño parece ser de sólo unos pocos KBs.

   

   Also Read: Tips to Make Slow Excel Spreadsheets Fast

   Consejos para mejorar el rendimiento de los libros de Excel

   Aunque los métodos anteriores le ayudarán a reducir el tamaño del archivo de Excel, aquí hay algunos consejos adicionales para mejorar el rendimiento de los libros de Excel:

   1. Evite utilizar fórmulas volátiles. Además de aumentar el tamaño del archivo, las fórmulas volátiles también hacen que los libros de trabajo sean lentos. Ejemplos de funciones volátiles son RAND, TODAY, OFFSET, INDIRECT, etc.
   2. Si tiene la necesidad de crear resúmenes de un gran conjunto de datos, en lugar de utilizar fórmulas, vea si puede utilizar una tabla dinámica en su lugar. Una tabla dinámica es rápida y muy intuitiva en comparación con las fórmulas.
   3. Si extrae datos de varias fuentes o combina datos de varios archivos, utilice Power Query para obtener y transformar los datos. Puede guardar los datos como una conexión de Power Query en lugar de mantenerlos en la hoja de trabajo. Puede crear fácilmente tablas dinámicas utilizando las conexiones creadas con Power Query.
   4. Convierta sus datos tabulares en una tabla de Excel y utilice la tabla de Excel en las fórmulas. Hace que su libro de trabajo funcione más rápido y las fórmulas creadas utilizando referencias estructuradas de la tabla de Excel son más fáciles de manejar (ya que éstas son más intuitivas).
   5. Evite hacer referencia a toda la fila o columna en una fórmula. Esto ayudará a que sus fórmulas sean más eficientes.
   6. Si tiene muchas fórmulas en su libro, desactive el cálculo automático y cambie al cálculo manual. Haciendo esto, cada vez que haya un cambio en las hojas de trabajo, las fórmulas no se recalcularían. Puede hacer este cambio yendo a la pestaña Fórmula y cambiando las ‘Opciones de cálculo’.

   Espero que el método y los consejos de este tutorial le resulten útiles.