8 formas de reducir el tamaño del archivo de Excel (que realmente funcionan)

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

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

Y dado que 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 Excel grande que pesa 50 MB puede ser difícil de compartir con sus colegas / clientes.

En mi último trabajo, usamos el cliente de correo electrónico Lotus Notes, que es lento como un caracol, y enviar archivos pesados ​​de Excel fue una tortura.

Si bien no existe una forma mágica de reducir el tamaño de un libro de Excel, existen algunas técnicas que puede utilizar para reducir un poco la flacidez.

Nota: El impacto de estas técnicas para reducir el tamaño del archivo de Excel dependería de sus datos. Para algunos, puede ayudar a reducir el tamaño en un 50% y para algunos, solo puede terminar reduciendo el tamaño en un 5%.

Consejos para reducir el tamaño del archivo de Excel

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

Para cada consejo, hice algunas pruebas y agregué las instantáneas para mostrarle el impacto en el tamaño del archivo. Los resultados que obtenga variarán según sus datos y archivos.

Según su libro de trabajo, puede utilizar uno o más de estos consejos.

Eliminar hojas de trabajo / datos innecesarios

Las mejores soluciones suelen ser las más fáciles.

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

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

Esto tiene una correlación directa con el tamaño del archivo de Excel. 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 de trabajo es de 143 MB (y se está tardando bastante en guardar y abrir).

Ahora, cuando eliminé cinco hojas, el tamaño se redujo a la mitad (duh).

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

Convertir a formato binario (XLSB)

¿Sabe que con solo guardar un archivo de Excel en formato XLSB se puede reducir el tamaño del archivo?

Sí, es verdad.

Todo lo que tiene que hacer es utilizar el formato .xlsb mientras guarda un libro de trabajo.

Estos son los pasos para hacer esto:

1. Haga clic en la pestaña Archivo.
2. Haga 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 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 guarda 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 voy a la carpeta "xl" y luego a la carpeta de la hoja de trabajo, me muestra las hojas de trabajo en ese libro de trabajo (y estas hojas de trabajo se guardan como un documento XML).

Ahora hagamos lo mismo con un archivo XLSB.

Entonces, primero cambio la extensión del archivo a ZIP, luego lo abro y ubico sheet1. Aquí está lo que obtuviste:

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

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

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

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

XLSB frente a XLSX / XLSM

Cuando se trata de utilizar el formato XLSB, la reducción de tamaño es un gran beneficio. Pero debe ser un poco cauteloso al trabajar con el formato XLSB.

Ventajas del formato XLSB:

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

Si bien XLSB se ve muy bien, hay algunas razones por las que debe seguir usando archivos XLSX / XLSM:

1. Con el formato XLSB, no tiene forma de saber si tiene una macro o no. Esto lo hace más riesgoso, ya que alguien puede usarlo para ejecutar código malicioso.
2. Los archivos XLSB están codificados en formato de archivo bin patentado, mientras que XML es un formato de archivo legible de código abierto. Esto significa que si usa herramientas de terceros con Excel, es mejor usar el formato XML.
3. Power Query no puede leer datos de un archivo XLSB. Entonces, si usa Power Query, es mejor tener sus datos en formatos XLSX / XLSM.

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

Según lo que escuché de la gente y leí 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 cómo comprender el formato XLSB.

Eliminar fórmulas innecesarias

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

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

Estos son los pasos para convertir rápidamente fórmulas a valores en Excel:

1. Seleccione la hoja de trabajo / conjunto de datos completo.
2. presione el F5 clave.
3. En el cuadro de diálogo Ir a, 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.

Ahora use los pasos a continuación para convertir estas fórmulas en valores:

1. Copiar las celdas seleccionadas (control + C)
2. Haga clic en la pestaña Inicio.
3. En el grupo Portapapeles, haga 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 agregué 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 tenga en cuenta que solo tenía 3 columnas de fórmulas).

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

NOTA: Las fórmulas volátiles pueden aumentar mucho el tamaño de su archivo. Como parte de la prueba, cuando reemplacé la fórmula SUM con una fórmula volátil (OFFSET), dio lugar a un salto en el tamaño del archivo (de 186 MB a 198 MB). Entonces, si tiene fórmulas volátiles que puede reemplazar con fórmulas no volátiles, o puede convertirlas en valores, esto lo ayudará a reducir el tamaño del archivo.

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

Si trabaja con archivos de Excel que contienen imágenes, debe optimizar estas imágenes y asegurarse de que el tamaño sea menor.

El problema con las imágenes es que a medida que las comprime, también se produce una reducción de la calidad, que es posible que no desee.

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

Una buena herramienta en línea para hacer esto es TinyPNG (o puede 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 aún así mantiene una calidad de imagen decente.

A continuación se muestra la captura de pantalla en la que pude comprimir una imagen con un tamaño de 3.9 MB a 331 KB con una pérdida mínima 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.

Estos son los pasos para comprimir una imagen en Excel:

1. Seleccione la imagen que desea comprimir.
2. Haga clic en la pestaña Formato de herramientas de imagen. Esta es una pestaña contextual que solo aparece cuando hace clic en una imagen.
3. En el grupo Ajustar, haga clic en la opción "Comprimir imagen".
4. En el cuadro de diálogo Comprimir imágenes:
   • Anule la selección de "Aplicar solo a esta imagen" si desea comprimir todas las imágenes del libro.
   • Seleccione Correo electrónico (96 ppp) para obtener la máxima compresión.
5. Haga clic en Aceptar.
6. Guarda el archivo.

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

Resultados de la prueba:

Usé la opción de comprimir imagen en Excel usando una imagen.

A continuación se muestran los resultados:

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

Esto también dependería de la imagen. Es posible que una imagen que ya esté comprimida no produzca una gran reducción de tamaño.

Eliminar caché de pivote para ahorrar espacio

Si trabaja con tablas dinámicas y tiene problemas con archivos de Excel de gran tamaño, conocer la caché dinámica puede resultarle útil.

Cuando crea una tabla dinámica con 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.

Si bien no puede verlo, es parte del libro de trabajo y está conectado a la tabla dinámica. Cuando realiza cambios en la tabla dinámica, no utiliza la fuente de datos, sino que utiliza la caché dinámica.

Si bien cree que está directamente vinculado a los datos de origen, en realidad, accede a la caché dinámica (y no a los datos de origen) cuando realiza cambios en la tabla dinámica.

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

Tan pronto como cree una tabla dinámica, notará que el tamaño del archivo de Excel aumenta.

En el ejemplo anterior, tan pronto como agrego la tabla dinámica, el tamaño del archivo aumenta al doble.

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

1. Conserve los datos de origen y elimine la caché dinámica
2. Mantenga la caché dinámica y elimine los datos de origen

Veamos ambas técnicas.

Conservar los datos de origen y eliminar la caché dinámica

Cuando haya terminado con la tabla dinámica y la esté guardando, puede obligar a Excel a guardar solo los datos de origen y la tabla dinámica resultante, y no la caché dinámica.

Hacer esto hará que su archivo de Excel sea más liviano y tendrá una manera de recrear el caché de Pivot cuando lo necesite la próxima vez.

A continuación se muestra la diferencia de tamaño cuando guarda un archivo de Excel con y sin Pivot Cache.

Estos son los pasos para NO guardar la caché dinámica:

1. Seleccione cualquier celda de la tabla dinámica.
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 tabla dinámica, haga clic en la pestaña Datos.
5. En las opciones de Datos de la tabla dinámica, desmarcar "Guardar datos de origen con archivo". Esta opción garantiza que la caché dinámica no se guarde cuando guarde el libro de trabajo (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 asegura que tan pronto como 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á un tamaño de archivo 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 solo la caché dinámica.

Esto funciona como una caché dinámica, es una imagen reflejada de sus datos y no accede a los datos de origen cuando realiza cambios en la tabla dinámica, sino que utiliza la caché dinámica.

A continuación se muestran 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 desee recuperar los datos de origen, haga doble clic en la celda "Total general" de la tabla dinámica 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 aproximadamente un 10-15% de inmediato.

Luego puede compartir este archivo comprimido por correo electrónico, y la persona que lo reciba puede descomprimir el archivo y usar el libro de Excel.

Estos son los pasos para comprimir un archivo de Excel (para Windows):

1. Haga clic derecho en el archivo de Excel
2. Coloca el cursor sobre la opción Enviar a.
3. Haga clic en la opción "Carpeta comprimida (en 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 archivos que se encuentran en el rango de 20 a 50 MB. A medida que aumenta el tamaño del archivo, la reducción porcentual no es muy alta.

Eliminar formato de datos

El formato de datos, como la aplicación de un color de fondo, la adición de bordes o el cambio de estilo de fuente, pueden aumentar el tamaño del archivo.

Aunque, según mis pruebas, noté 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 trabajo) y el otro con los mismos datos pero con el formato aplicado (color de celda, borde y en cursiva).

Resultados de la prueba

Puede ver que la diferencia entre los dos archivos no es tan grande (aunque tiene un impacto cuando tiene muchos datos).

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

Entonces, si desea reducir el tamaño de archivo de su libro de Excel, puede buscar el formato que no necesita. Eliminarlo puede ahorrarle algo de espacio en disco.

Estos son los pasos para eliminar instantáneamente todo el formato, mientras se mantienen los datos intactos:

1. Seleccione el conjunto de datos completo (del que desea eliminar el formato).
2. Haga 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 de trabajo.

Quitar formato condicional

Al igual que el formato de datos normal, el formato condicional también aumenta el tamaño del archivo.

Sin embargo, según mis pruebas, noté que la diferencia es menor (a menos que tenga varias reglas aplicadas a varias hojas con grandes conjuntos de datos).

Sin embargo, es una buena idea eliminar el formato condicional de cualquier conjunto de datos donde no lo necesite.

Aparte del tamaño, hay otra buena razón para eliminar el formato condicional: VELOCIDAD.

El formato condicional es volátil y cada vez que hay algún cambio en la hoja de trabajo, se vuelve a calcular. Esto puede llevar 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 solo de unos pocos KB.

Leer también: Consejos para hacer que las hojas de cálculo de Excel lentas sean rápidas

Consejos para mejorar el rendimiento de Excel

Si bien los métodos anteriores lo 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 el uso de fórmulas volátiles. Además de aumentar el tamaño del archivo, las fórmulas volátiles también ralentizan los libros de trabajo. Ejemplos de funciones volátiles incluyen RAND, TODAY, OFFSET, INDIRECT, etc.
2. Si necesita crear resúmenes de un conjunto de datos grande, en lugar de usar fórmulas, vea si puede usar 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, use 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 una tabla dinámica utilizando las conexiones creadas con Power Query.
4. Convierta sus datos tabulares en una tabla de Excel y use la tabla de Excel en fórmulas. Hace que su libro de trabajo funcione más rápido y las fórmulas creadas con referencias estructuradas de tablas de Excel son más fáciles de administrar (ya que 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 de trabajo, desactive el cálculo automático y cambie al cálculo manual. Al hacer esto, cada vez que hay un cambio en las hojas de trabajo, las fórmulas no se recalculan. Puede realizar 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.

¿Qué más podría agregar a este tutorial para que sea más útil? Házmelo saber en la sección de comentarios.