Tamaño. Peso. Volumen. Da igual cómo lo llamemos. Cuando un archivo pesa demasiado, se hace demasiado grande, es un problema. No podemos enviarlo por email, tarda mucho en cargar y en grabarse. Es especialmente molesto cuando realiza las copias de seguridad, pues ocupa varios segundos cuando nuestro cerebro estaba pensando en productividad.

La historia

El otro día mi amigo Quique  me comentó que le había llegado un archivo con un tamaño excesivo. Cuando su creador lo generó en su día, ocupaba menos de 1 Mb, pero ahora ocupaba casi 50 Mb. ¿Qué pasaba? Comentando por teléfono, apuntamos a algunos sospechosos habituales:

• ¿Hay datos en el modelo de datos?
• ¿Hay muchas formas (por ejemplo cuadros de texto vacíos) en las hojas?

¿Y esto cómo se hace? Empezamos con pico y pala.

Información sobre el modelo de datos

El modelo de datos es información que puede haber en el archivo, pero que no está en ninguna hoja. Está por detrás, y para ver esa información hay que conectar a través de tablas dinámicas o trabajar directamente en el modelo a través del administrador del modelo de datos.

En ocasiones estos datos pasan desapercibidos, porque el administrador del modelo de datos solo está disponible a partir de la versión 2013 de Excel. Por eso, en versiones anteriores puede no ser fácilmente localizable.

Aunque el modelo de datos es muy eficiente, puede almacenar mucha información, por lo que es posible que ocupe bastante espacio, generando archivos de tamaño muy grande. Para averiguar si hay información en el modelo de datos, se me ocurre que podemos hacer dos cosas:

1. Insertar una tabla dinámica. Al insertarla, se crea una ventana de diálogo que, si existe, permite seleccionar el modelo de datos como fuente de la tabla. Si la opción de modelo de datos no se puede marcar es porque no hay nada.

 

2. Acceder directamente al administrador del modelo de datos. Esto se puede hacer en la versión 2016 a través de la pestaña Datos, en el grupo ‘Herramientas de datos’. En versiones anteriores, a través de la pestaña Power Pivot, que en algunas versiones es un add-in que hay que instalar.

En nuestro caso, el archivo no tenía modelo de datos, así que pasamos al siguiente sospechoso de la lista.

Formas ocultas (o visibles)

En ocasiones me he encontrado con algún archivo que funcionaba muy lentamente, con un tamaño excesivo que ocupaba muchos megas. Tras mucho buscar, he encontrado que en alguna hoja había literalmente miles de cuadros de texto vacíos. Pero eso… ¿cómo se sabe?

Bueno, hay varias maneras. Una es “a ojo”. El icono del ratón habitual sobre una hoja de cálculo es una cruz. Si tenemos un cuadro de texto y ponemos el cursor encima, lo que obtendremos es un icono que se parece a una I mayúscula (como el que tiene Word). Mejor lo vemos en una imagen.

Esto es lo que se ve habitualmente en Excel:

Esto es lo que se ve si hay “intrusos”:

De la misma manera, si lo que hay es una forma u otras cosas, el icono será una flecha. Si teniendo el cursor alternativo este hacemos click, entonces se hace visible nuestro amigo:

Si tenemos una versión de Excel posterior a la 2007 (la tenemos, ¿verdad?), entonces podemos hacer visibles nuestros objetos de otra manera: mediante el panel de selección. Esto se encuentra, en la versión 2016, dentro de “Diseño de página” en la cinta de opciones, en el panel “organizar”. En versiones anteriores tenemos que seleccionar un objeto, y el botón para activar el panel de selección está dentro de la ficha “Herramientas de dibujo” o “Herramientas de gráficos”. El panel aparece a la derecha, y ahí tenemos una lista de todos los objetos que hay en la hoja.

Si hay algo, ahí estará.

Como ves, con unos cuantos copia-pega, he generado más de 33.000 copias del mismo cuadro de texto vacío. La generación de cuadros es exponencial, por lo que rápidamente se llega a cantidades desorbitadas si no nos damos cuenta. El problema ahora es borrarlos todos. ¿Tú le darías 33.000 veces a la tecla suprimir? Yo no. Para eso sé VBA. Pondría un código más o menos así:

Sub asdasd()
Dim obj As Object
For Each obj In ActiveSheet.Shapes
  obj.Delete
Next
End Sub

De esta manera elimino todos los shapes (cuadros de texto, pero también imágenes, formas, etc.) que haya en la hoja.

Sin embargo, este tampoco era el problema en el archivo de nuestro amigo. El panel de selección aparecía vacío, así que seguí buscando.

Formatos condicionales, formatos de celda, colores…

La hoja tenía muchos formatos de todo tipo. Condicionales, fondos de celda, tramas, colores de fondo y de texto… Probé a modificar esto, porque algo tenía que ser. Simplemente seleccioné toda la hoja, le puse sin color de fondo, sin líneas, sin tramas, apliqué y guardé… Y el tamaño del archivo era prácticamente el mismo. Eso no era.

Y me pregunté cómo se podía averiguar qué es lo que está ocupando espacio en disco concretamente… Y, por supuesto, había una solución.

Ver las tripas de un archivo .xlsx

Desde que Microsoft adoptó el estándar .xlsx, convirtió los archivos excel en archivos con estructura xml. Eso significa que se puede leer de muchas maneras, y que muchos tipos de programas entenderán la estructura. Curiosamente el importador xml del propio Excel nos da error. ¿Y esto por qué es?

Sencillamente, porque Excel no guarda directamente el archivo en formato xml, sino que, además, lo comprime en formato zip para que ocupe menos espacio. Entonces… ¿Y si probamos a cambiarle la extensión al archivo y lo abrimos con un compresor?

Pasos previos para cambiar la extensión al archivo

Supongo que todos sabemos cambiar la extensión a un archivo, pero de todas maneras vamos a decir cómo se hace. Si ya lo sabes hacer, puedes pasar al siguiente título sin problemas.

Lo primero es abrir una ventana del explorador de Windows. Esto se puede hacer rápidamente pulsando las teclas Windows + E.

Se nos abre la ventana del explorador de Windows en ‘Mi equipo’. Ahí seleccionamos el panel “Vista” y a la derecha del todo tenemos el botón Opciones, donde hacemos click en ‘Cambiar opciones de carpeta y búsqueda’.

Se nos abre una ventana donde seleccionamos la pestaña ‘Ver’ y luego bajamos hasta encontrar ‘Ocultar las extensiones de archivo para tipos de archivo conocidos’. Debemos dejar la casilla desmarcada y darle a Aceptar.

Ahora sí, cambiamos la extensión del archivo

Una vez hecho esto, nos vamos a nuestro archivo .xlsx y le cambiamos el nombre. Sustituimos .xlsx por .zip y aceptamos todos los mensajes de Windows.

Como ves, yo primero me hice una copia para mantener el .xlsx original (bueno, en nuestro caso era un .xlsm porque tenía macros). Y ya podemos abrir el nuevo archivo .zip en un compresor cualquiera. En mi caso tengo instalado WinRar, pero vale cualquiera que abra .zip, incluso el propio explorador de Windows.

Al abrirlo, verás una estructura de carpetas y de archivos. Resulta que nuestro archivo .xlsx es, en realidad, un montón de archivos XML y carpetas comprimidos. Podemos navegar por las carpetas para ver qué es lo que está ocupando espacio… En nuestro caso particular entré en la carpeta ‘xl’, y dentro en la carpeta ‘worksheets’. Ordenando por tamaño, ya sabemos qué hoja es la culpable:

¡Y ahí los tenemos! Nuestros culpables son las hojas 5 y 4 de nuestro archivo.

Entendiendo a qué hoja se refiere…

Lo que pasa es que en nuestro archivo, las hojas no se llaman ‘sheet5’ ni ‘sheet4’. Tienen nombres como ‘Portada’, ‘Índice’, ‘Datos básicos’…

La primera aproximación es acudir a nuestro editor de VBA, porque sabemos que en el panel de Proyecto ponen nuestros nombres entre paréntesis, pero por fuera las siguen llamando ‘Hoja 1’, ‘Hoja 2’, etc.

Hay un poquito de censura, porque el archivo no es mío y no tengo permiso explícito para utilizarlo. Solo he mantenido los nombres genéricos porque así se entiende bastante bien. Los nombres entre paréntesis son los que vemos en las pestañas de nuestras hojas. Y ‘Hoja 1’, ‘Hoja 2’, etc. es el nombre interno de las hojas. En realidad, es una propiedad que se llama “codename”, y que nos permite cierta facilidad para trabajar con las hojas. Pero este no es el nombre con el que se corresponde nuestra nomenclatura dentro del archivo zip. Voy a intentar aclararlo un poco.

En código nos podemos referir a una hoja de tres maneras: mediante su nombre, mediante su codename y mediante su index. Lo haríamos así:

nombre:
Worksheets("Listas").Activate

index:
Worksheets(1).Activate

codename:
hoja1.Activate

En este caso, el index es 1, y el codename es Hoja1. Pero no tienen por qué coincidir. De hecho, en mi archivo la hoja que se llama ‘Portada’ tiene el index 2 y el codename ‘Hoja5’.

Pues el número que hay detrás de ‘sheet’ en los nombres de archivo de nuestro zip son el index de cada hoja.

Resolviendo el caso: reducir el tamaño del archivo

Finalmente, al saber ya en qué hoja teníamos que actuar, y tras haber descartado el resto de sospechosos, simplemente me fui a la última fila con datos, seleccioné la fila siguiente entera y presioné Ctrl+Mayús+↓ para seleccionar todas las celdas hasta el final del archivo. Luego botón derecho y eliminar. De la misma manera, me fui a la última columna con datos, e hice lo mismo seleccionando la columna siguiente, y luego Ctrl+Mayús+→ para seleccionar todas las columnas hasta el final. Otra vez, botón derecho y eliminar.

Simplemente hice esto en ambas hojas que parecían tener problemas de tamaño en nuestro archivo zip. Y las hojas las seleccioné utilizando VBA con la orden que usaba el index del apartado anterior. Tras guardar, la magia se hizo.

¿Qué había ocurrido?

Como he comentado anteriormente, las hojas tenían muchos formatos aplicados. Sombreados, rayados, colores…

Parece ser que cuando una celda está vacía y no tiene formato, Excel la ignora. No guarda nada de información sobre ella porque está vacía y tiene las propiedades estándar. Pero cuando le cambiamos el formato, entonces Excel tiene que guardar esa información en el archivo. Si volvemos a eliminar el formato, para Excel ya es una celda modificada y la sigue guardando, a menos que la eliminemos completamente.

Al preparar la hoja, quien la hizo probablemente seleccionó columnas y filas completas para aplicar formatos. Quizás simplemente extendió con doble click hasta el final de la hoja sin percatarse de que no le hacía falta tanta información guardada en el archivo. Al repetir esto con cientos de columnas, el peso del archivo se multiplicó.

Así que, si has llegado hasta aquí, solo me queda decirte que no colorees de más las hojas Excel si no quieres encontrarte con 40 megas de archivo sin saber por qué.

Un saludo