Todos los trucos para optimizar y reducir el tamaño de los libros Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Alguna vez hemos detectado problemas en nuestros archivos, te mostramos todos los trucos para optimizar y reducir el tamaño de los libros Excel.

Todos los trucos para optimizar y reducir el tamaño de los libros Excel
  • ELIMINAR HOJAS DE TRABAJO INNECESARIAS

En ocasiones el libro incluye nuestra hoja de datos y varias hojas vacías.

– Eliminar las hojas no necesarias.

Barra de hojas con Enero, hoja2 y hoja3 y otra barra sólo con enero
  1. Situar el cursor sobre la hoja deseada.

  2. Pulsar botón derecho del ratón / Eliminar. Si la hoja tiene datos mostrará mensaje de confirmación.

 
  • ELIMINAR FORMATO DE CELDAS

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

– Eliminar el formato de los rangos de celdas no utilizados. Por ejemplo en toda la columna o toda la fila.

– Unificar a una única fuente, cuando aplicamos muchas fuentes diferentes.

– Valorar la necesidad de formato en tablas.

– Valorar la necesidad de formato en tablas dinámicas.

– Si es posible convertir las fórmulas en valor.

Hoja Excel con diferentes sombreados, bordes y fuentes
  1. Seleccionar toda la hoja o el rango deseado.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Modificar / Borrar / Borrar formatos.

 
  • FORMATO CONDICIONAL

El formato condicional aumenta el tamaño del archivo y también al ser un cálculo volátil, siempre que hay algún cambio en la hoja de cálculo, se vuelve a calcular. Esto puede llevar a un libro de trabajo más lento.

Siempre que sea posible:

– Eliminar las reglas no necesarias.
– Si el formato incluye fórmulas optimizarlas.

Ventana Excel del administrador de reglas de formato condicional
  1. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Administrar reglas.

 
  • COMPRIMIR IMÁGENES

Cuando nuestro libro incluye imágenes, deberemos optimizarlas según el destino del archivo.

Al comprimir imágenes en Excel también se reduce la calidad, siendo recomendable comprimirlas con algún programa de edición de fotos antes de insertarlas en la hoja.

Ventana Excel de comprimir imágenes
  1. Seleccionar la imagen deseada.

  2. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de imagen) / Ajustar / Comprimir imágenes. Mostrará ventana de comprimir.

  3. Desactivar la casilla: Aplicar solo a esta imagen.

  4. Activar la casilla: Eliminar las áreas recortadas de las imágenes. Por si alguna imagen es recortada.

  5. Ir a: Destino / Seleccionar el tipo deseado. Siempre que la imagen original tenga una resolución alta.

– Ampliar información: Reducir el peso de los libros con imágenes.

 
  • COMPROBAR Y ELIMINAR CELDAS OCULTAS INNECESARIAS

Las celdas ocultas también pueden agregar una carga adicional y aumentar el tamaño del archivo Excel.

Hoja Excel con fila 1 y columna A vacías, junto a rango con celdas vacías

– Columnas vacías. En algunas hojas se deja la columna A vacía comenzando los valores desde la columna B.

– Filas vacías. En algunas hojas se deja la fila 1 vacía comenzando los valores desde la fila 2.

– Minimizar el rango utilizado. Excel para ahorrar memoria y reducir el tamaño del archivo, almacena en un único rángo todo el área de la hoja.

En ocasiones el rango se amplía a sobre el rango de datos, por ejemplo al usar Ctrl+Fin se desplaza a la última celda pero no es la última celda con datos. Considerar la eliminación de todas las filas y columnas que se encuentran debajo ya la derecha de la última celda utilizada y luego guardar el libro de trabajo.

– Ampliar información: Identificar filas y columnas ocultas.

 
  • REVISAR FÓRMULAS Y FUNCIONES

– Minimiza el uso de fórmulas.

– Eliminar las referencias circulares. La iteración para resolver referencias circulares requiere tiempo, y si incluimos muchas fórmulas de referencia circulares, el tiempo requerido se incrementa.

– Evitar las funciones volátiles. Cada vez que se modifica una celda se produce un nuevo cálculo en el libro.

– Reescribe las fórmulas para que sean más eficientes. Para obtener el incremento de un 10% sobre 100 aplicamos la fórmula: =100*10/100 (realiza 2 operaciones multiplicar y dividir). Se puede optimizar con un único cálculo con: =100*0,1, o también =100*10%, o también =100*0,9.

– Insertar totales y subtotales a la izquierda o debajo de las celdas con valores (Referencia izquierda y abajo).

– Referencias de columnas completas. Podemos referirnos a todos los valores de una columna con la estructura $A:$A (columna A) permitiendo añadir tantos datos como deseemos.

– En las funciones con búsqueda de datos, ordenar primero la información y si es posible, usar coincidencias más cercanas.

– En las funciones con búsqueda de datos con coincidencia exacta, restringir al mínimo el rango de celdas (por ejemplo, pre-calcular un límite de rango inferior y un límite de rango superior para la búsqueda).

Funciones volátiles en Excel: Barra de fórmulas con función HOY, =HOY().

– Ampliar información: Las funciones volátiles.
– Ampliar información: Optimizar fórmulas, hojas y libros.
– Ampliar información: Referencia izquierda y abajo en fórmulas.

 
  • NOMBRES DEFINIDOS

Los nombres que hacen referencia a hojas, celdas y rangos requieren un tiempo de cálculo adicional.

– Evitar nombres anidados Nombres que hacen referencia a otros nombres.

Ventana Excel del administrador de nombres
  1. Ir a: Cinta de opciones / Pestaña: Fórmulas / Grupo: Nombres definidos / Administrador de nombres.

 
  • ENLACES EXTERNOS

– Eliminar de los enlaces externos a menos que sean necesarios. las fuentes a través de una red puede ralentizar los tiempos de carga.

– Es más rápido que las búsquedas y los datos se encuentren en la misma hoja. El uso de muchas hojas de trabajo puede hacer que su libro sea más fácil de usar, pero en general es más lento calcular las referencias a otras hojas

– Evitar enlaces entre libros de trabajo siempre que sea posible. Pueden ser lentos, fáciles de romper y no siempre fáciles de encontrar y reparar.

Ventana de modificar vínculos Excel

– Ampliar información: Quitar datos externos de la hoja.

 
  • CÁLCULO AUTOMÁTICO

Si los tiempos de interrupción son bastante largos, podemos desactivar el cálculo automático del libro o automático excepto para tablas de datos.

Ventana de opciones de Excel
  1. Ir a: Cinta de opciones / Pestaña: Archivo / Opciones. Mostrará la ventana de opciones de Excel.

  2. Ir a: Categoría Fórmulas / Grupo: Opciones de cálculo / Pulsar sobre el botón de opciones Manual. O la opción deseada.

  3. El cálculo se puede realizar manualmente pulsando F9.

 
  • EMPLEO DE TABLAS

Las referencias estructuradas nos permiten trabajar con los datos de las tablas donde las referencias a las celdas cambian a menudo ajustándose automáticamente sin tener que volver a escribir fórmulas cuando se agregan o eliminan filas y columnas o se actualizan los datos externos.

– En Excel 2016 se ha optimizado para un número ilimitado de filas.

– Las tablas presentan mayor rendimiento que los rangos dinámicos y las referencias a columnas completas.

– Permite trabajar con varias tablas en una hoja.

– Las fórmulas que están insertadas en la tabla también se expanden y contraen con los datos.

Hoja Excel: Con tabla de Excel en el rango A1:E6

– Ampliar información: Rango, tabla, tabla de datos y tabla dinámica.

 
  • EMPLEO DE TABLA DE DATOS

La tabla de datos es un rango de celdas que muestra los resultados de la sustitución de diferentes valores en una o más fórmulas, permitiendo mostrar cómo afecta el cambio de una o dos variables de las fórmulas a los resultados.

– Primero calcula el libro. Para cada par de valores de fila y columna, a continuación, sustituye los valores, realiza un recálculo de un solo hilo y almacena los resultados en la tabla de datos.

Hoja Excel: Con tabla de datos en el rango A7:D12

– Ampliar información: Rango, tabla, tabla de datos y tabla dinámica.

 
  • CACHE DE TABLAS DINÁMICAS

Al trabajar con tablas dinámicas, Excel crea automáticamente la cache de la tabla dinámica (contiene una réplica del origen de datos). Cuando realizamos cambios en la tabla dinámica, no utiliza el origen de datos, sino que utiliza la memoria caché dinámica.

Siempre que sea posible:

– Eliminar la cache de las tablas dinámicas.

– Mantener los datos de origen y eliminar la cache.

– Mantener la cache y eliminar los datos de origen.

– En lugar de crear varias tablas dinámicas que tengan el mismo código fuente, copiarlas y pegarlas donde las necesitemos, compartiendo el mismo caché de datos.

Ventana de opciones de tabla dinámica
  1. Sobre el área de la tabla dinámica / botón derecho del ratón / Opciones de tabla dinámica / Pestaña: Datos.

  2. Desactivar la casilla: Guardar datos de origen con el archivo.

  3. Activar la casilla: Actualizar al abrir el archivo.

  4. Seleccionar elementos que deseamos conservar.

– Ampliar información: Reducir el tamaño de tablas dinámicas.
– Ampliar información: Los 5 errores más frecuentes en tablas dinámicas.

 
  • CONVERTIR A FORMATO BINARIO (XLSB)

Guardar el libro xlsx como libro binario de Excel xlsb. Será compatible con otros usuarios y versiones.

Ventana Excel de guardar como
  1. Ir a: Cinta de opciones / Pestaña: Archivo.

  2. Pulsar sobre: Guardar como / Indicar carpeta y nombre / Ir a: Tipo / Seleccionar: Libro binario de Excel .xlsb

– Ampliar información: Libros de gran tamaño en Excel.

 
  • UTILIZAR POWERPIVOT Y POWERQUERY

Las herramientas de PowerPivot y PowerQuery son muy potentes y muy rápidos en el procesamiento de grandes cantidades de datos.

Ventana con entorno de PowerPivot de Excel
  1. Considerar rediseñar las hojas para trabajar con los nuevos complementos.

– Ampliar información: Crear un modelo de datos eficiente en Excel.
– Ampliar información: Qué hacer en Excel y en PowerPivot.

 
  • DIVIDIR EL LIBRO

Si el libro dispone de muchas hojas, se puede dividir en varios libros.

Por ejemplo, disponemos de los 12 meses del año en un único libro, pudiendo crear 2 libros uno para el primer semestre y otro para el segundo semestre.

1 libro con datos del año formado por 2 libros del primer y segundo semestre
 
  • COMPRIMIR EL ARCHIVO (ZIP)

Al comprimir un archivo Excel, se verá reducido su tamaño. Siendo ideal para el envío por correo electrónico o almacenar el archivo como final.

Icono de winzip, winrar y 7zip

Disponemos de programas como:

Zip
WinRAR
7-Zip

 
  • MACROS Y VBA

– Lenguaje C / C++. Las funciones definidas por el usuario programadas en C y C++ que usan la API de C (XLL funciones de complementos) generalmente se calculan con más rapidez que las funciones definidas por el usuario desarrolladas en VBA o automatización (XLA o automatización complementos).

– Evitar la selección y activación de objetos. Hacer referencia a un objeto como un intervalo o de una forma directa.

– Funciones definidas por el usuario (UDF). Por lo general es más rápido usar cálculos de fórmulas y funciones de Excel que las funciones UDF.

Existe una pequeña sobrecarga por cada llamada de función definida por el usuario y una sobrecarga significativa que transfiere información desde Excel a la función definida por el usuario.

Emplearemos siempre que estén bien diseñadas y llamadas.

Ventana del módulo de macros VBA Excel

– Desactivar todo excepto lo esencial mientras se ejecuta el código, posteriormente restaurar la funcionalidad a su estado original:

a. Application.ScreenUpdating desactivar la actualización de la pantalla.
b. Application.DisplayStatusBar desactivar la barra de estado.
c. Application.Calculation cambiar a cálculo manual.
d. Application.EnableEvents desactivar eventos.
e. ActiveSheet.DisplayPageBreaks desactivar los saltos de página.

Nivel de dificultad: Básico Excel Básico

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *