CREAR PLANTILLAS PERSONALIZADAS DE GRAFICOS EN EXCEL

Si eres usuario habitual de Excel, habrás hecho gráficos alguna vez. Y has descubierto que las opciones por defecto de Excel para los gráficos son un desastre. Con este gráfico el jefe se pondría a bostezar:

Así que te pones a hacer todos esos ajustes de formato que has repetido un millón de veces (el área de gráfico, el color, la anchura de las columnas, el tipo y tamaño de fuente, esa leyenda…) incluso puede que tengas unas macros… ¿Y por qué no creas una plantilla personalizada de gráficos a tu gusto y ganas horas de trabajo?.

Como digo, pon primero el gráfico a tu gusto. Dedícale todo el tiempo que quieras, porque será la última vez que lo hagas. Pongamos que te queda así:

Ahora selecciona el gráfico, vete al menú Gráfico de Excel y selecciona Tipo de Gráfico…, en la segunda pestaña “Tipos personalizados” elige “Definido por el usuario” y pincha en “Agregar”:

dale un nombre y una descripción para recordar las características (aunque aparecerá en vista previa –Muestra- en el futuro no está de más que escribas algo de información):

Y ya está, la próxima vez que vayas a hacer un gráfico vete a los “Tipos Personalizados”, elige “Definidos por el usuario”, selecciona tu flamante plantilla y voilá, has ganado un tiempo precioso.

Haz pruebas hasta conseguir la plantilla deseada. Verás que hay cosas que no merece la pena almacenar como plantilla, como por ejemplo el título del gráfico (queda fijado el texto que tuviera la plantilla), pero eso ya depende de tus gustos. Si usas un tipo de gráfico más a menudo que ningún otro, puedes además establecerlo como “predeterminado” si pinchas en “Establecer como predeterminado”, con eso incluso te ahorras tener que seleccionarlo cada vez.

Nota: Las plantillas de gráficos en Excel se almacenan en un archivo llamado XLUSRGAL.XLS.

Por acabar, si en un archivo de Excel de otro usuario ves un gráfico que te gusta, puedes usar esta técnica para guardarlo como plantilla en tus Tipos Personalizados.

Una cosa más, en Internet encontrarás centenares de gráficos personalizados. Yo te recomiendo que visites a el blog de Chandoo, y te descargues sus plantillas de gráficos. Son 73 plantillas incluidas en un único archivo zip, comprende los tipos de gráficos más usuales, están bien diseñadas, hace un uso excelente de los colores, y son gratis. Este es el enlace: Plantillas de gráficos Chandoo. Si tenéis cualquier problema dejadme un mensaje y os la envío por correo, son solo 400 Kb.

TAMAÑO DE FUENTE EN TUS GRAFICOS EXCEL (AUTOESCALADO)

Si sueles hacer gráficos en Excel, seguramente has sufrido el proceso de tener que dar el formato deseado a todos los elementos del mismo, especialmente el tamaño de letra (los ejes, el título, la leyenda, etc).

Seguro que además has descubierto esa opción maldita de “Autoescala”, por la que cuando reduces el tamaño de tu gráfico, el tamaño de fuente se reduce hasta que es ilegible (o cuando quieres hacerlo mayor, las fuentes se hacen enormes).

1) Hay una forma rápida para deshabilitar a la vez el autoescalado de fuentes de todos los elementos del gráfico. Una vez creado el gráfico, y antes de darle formatos, haz doble click en el Area del gráfico, y en la pestaña de Fuente deshabilita el cuadro de Autoescala. Con eso todos los elementos el gráfico pierden esa molesta propiedad (excepto los que añadas posteriormente).

2) La segunda forma de obtener este resultado es ordenárselo a Excel con carácter general. Esto se consigue mediante la edición del Registro de Windows. Si no lo has hecho antes olvídalo, y si lo has hecho antes ya sabes cual es la forma correcta y las precauciones a tener en cuenta.

Básicamente, consiste en crear una clave nueva de registro en la que se deshabilita el autoescalado:

• Localiza en el Registro: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
• Crea una nueva DWORD, con Value Name= AutoChartFontScaling y Value Data= 0

Nota: Este es el método para Excel 2003 (versión 11.0). Para versiones anteriores en vez de …Office\11.0\Excel\Options tendrás que buscar la correspondiente a tu versión (9.0, 10.0, etc).

3) Y la tercera forma consiste en establecer tus propias plantillas de gráficos Excel, que contarán con las opciones predeterminadas que tú decidas. Pero esto lo explicaré en un post más detallado en el futuro.

ENLACES SOBRE EXCEL EN ESPAÑOL

Buscando en la web, no hay muchas páginas con consejos, ayuda o trucos Excel en español. Estos son dos de los que suelo visitar y recomiendo:

1) JLD Excel en castellano: Blog de Jorge Dunkelman sobre muchos temas relacionados con Excel. Es muy activo y realmente es un experto en Excel. El nivel de dificultad es Medio muchas veces, y Avanzado otras muchas. Sus explicaciones están bien documentadas y son didácticas, y tiene un gran número de seguidores a los que atiende a través de los comentarios de sus posts. Blog muy recomendable porque es trabajo de calidad.

Mantiene también blogs paralelos en inglés y hebreo, y por si fuera poco tiene otro blog que se centra sobre todo en trabajar con gráficos Excel (JLD Excel Gráficos).

2) Economía Excel: Blog de Artemio, español residente en A Coruña. No trata exactamente sobre temas Excel sino sobre aspectos económicos y empresariales. Lo que le distingue es que cada post incluye un archivo Excel muy bien preparado sobre un tema concreto, así como algunas explicaciones del propósito de dicho archivo.

Soy economista así que aprecio el trabajo de Artemio. Debo señalar que sus archivos están protegidos mediante contraseña, pero creo que es habitual que la facilite vía email privado si se la solicitas, para poder personalizar los archivos a tus necesidades.

 

[Si conoces otros enlaces decentes en castellano relacionados con Excel, déjame un comentario].

DATOS ALEATORIOS PARA TUS PRUEBAS EN EXCEL

Me preguntaban el otro día si puede evitarse teclear montones de datos ficticios con los que hacer pruebas en Excel (para ensayar con nuevas funciones, hacer tests con gráficos, etc).

Pues en realidad no suelo inventarme datos, dejo que Excel cree las series con la función =ALEATORIO().

Esta función no tiene argumentos, y simplemente ofrece un número aleatorio entre 0 y 1. De esa forma, si quiero un rango de datos entre 0 y 1000 para mis pruebas, escribo =ALEATORIO()*1000, y tengo todos los números que necesito.

Los datos obtenidos con esta función se recalculan cada vez que pulsas F9.

ELEGIR LAS SERIES DE TU GRAFICO EXCEL CON UN CHECKBOX

Tienes los datos listos y has hecho el gráfico correcto. Sin embargo el resultado es confuso y difícil de leer, pero no quieres hacer cuatro gráficos distintos si puedes evitarlo.

¡Ah, si pudiera elegir qué series mostrar u ocultar en mi gráfico con un par de clicks!…

Pues nada más fácil, vamos a ello:

Esta técnica, en resumen, se basa en dos pasos: 1) Crear una nueva tabla de datos, cuyos valores solo se usarán en el gráfico si el usuario selecciona una serie concreta; y 2) crear checkboxes para poder elegir entre las diversas series.

1) Tabla de datos auxiliar y restricción de series mostradas:

El objetivo es replicar la tabla de datos original, de forma que cada serie de datos (Agencia 1, Agencia 2, etc) solo muestre datos si yo quiero que lo haga. Para eso usaremos el valor VERDADERO/FALSO como determinante en una fórmula condicional de Excel.

De momento fíate de mí. Copia los encabezados de tu tabla original (los meses, y los nombres de las series), y escribe la fórmula que indico en la primera celda de datos: =SI($A11;C3;NOD()).

Lo que viene la decir la fórmula es: Mira en la celda A11, y si ésta dice VERDADERO copia el valor que hay en la C3, pero si dice FALSO usa NOD().

Nota: Lo que hace NOD() es decirle a Excel que no es un número, y la celda mostrará #N/A. Esto es muy importante, porque no aparece en los gráficos de Excel, que es lo que queremos exactamente.

Ahora copia esa fórmula en toda la tabla, y prueba a escribir VERDADERO o FALSO en las celdas A11 a A14. Este será el factor determinante de que la tabla muestre valores en cada serie o no.

Esto marcha. Si ahora seleccionas el recuadro amarillo y haces un gráfico Excel, las series correspondientes a Agencia 2 y Agencia 3 no se mostrarán, porque contienen #N/A.

2) Añadir la forma de seleccionar las series a mostrar:

Ahora veamos cómo hacer para que los valores VERDADERO y FALSO (que determinan qué series tendrán valores y por tanto se muestren en el gráfico) sean definibles por el usuario.

Excel permite incluir Controles en sus hojas de cálculo. Uno de ellos es el Checkbox, que no es más que un cuadrado en el que puedes pinchar para activarlo o desactivarlo. Si no hay usado nunca Controles pensarás que es difícil, pero te aseguro que no. Empecemos por mostrar dos Barras de Herramientas de Excel si no las tienes ya activadas (menú Ver > Barras de Herramientas, y selecciona las barras de Visual Basic y Cuadro de Controles). Son estas dos:

Pincha en el penúltimo icono de la barra de Visual Basic (éste habilita el trabajo en Modo Diseño, que es el que permite editar y modificar los Controles en una hoja de cálculo), y luego usando el cuarto icono de la barra de Cuadro de Controles podrás insertar un Checkbox en tu hoja de cálculo. Inserta cuatro uno debajo de otro, así:

Ahora pincha con el botón derecho en uno de ellos, y del menú desplegable elige Propiedades. Te saldrá un cuadro lleno de datos, pero solo necesitarás completar un par.

Solo tienes que cambiar dos campos, pinchando en la columna de la derecha:

• Caption: Es el nombre que mostrará el checkbox. Ponle a cada uno Agencia 1, Agencia 2, etc.
• Linked cell: Este es el más importante. Sirve para que cuando el checkbox esté seleccionado, la celda que pongas en este campo muestre el valor VERDADERO. Si no, aparecerá el FALSO. [A estas alturas seguro que ya te has dado cuenta de a dónde quiero llegar]. Para Agencia 1 el valor de Linked cell será A11, para Agencia 2 será A12, etc.

Si todo ha ido bien, tienes algo parecido a esto de abajo:

Y si ahora sales del Modo Diseño (pincha en el penúltimo icono de la barra de Visual Basic), ahora puedes jugar a activar y desactivar cada checkbox, y comprobarás que las celdas enlazadas a cada uno (de A11 hasta A14) cambian su valor respondiendo a tus selecciones, lo mismo que las series que aparecen en el gráfico.

Ahora es cuestión de gusto, yo he decidido bajar un poco el gráfico y situarlo junto a los checkbox. Con eso ya tenemos listo nuestro gráfico en el que podemos elegir qué series mostrar:

Me dejo unas cuantas cosas en el tintero, quizá vuelva otro día con un post complementario (cómo hacer que la Leyenda del gráfico no muestre las series que no están, cómo dejar fija una serie que no pueda omitirse, cómo combinar otros tipos de gráfico…).

LA FUNCION SUBTOTALES EN EXCEL

Esta es una de las pocas funciones en Excel que sirve para cálculos muy diversos. De acuerdo con su definición: SUBTOTALES (función; rango), el parámetro función determina la operación a realizar (suma, promedio, desviación típica, etc) con los datos del rango seleccionado. El valor función puede ser de 1 a 11.

Esta tabla resumen muestra las 11 operaciones posibles con Subtotales sobre el rango B3:B7, y la función Excel equivalente (que proporcionaría el mismo resultado, pero sin usar Subtotales). Comprueba que al usar Subtotales, solo el primer parámetro de la fórmula cambia.

Nota: El parámetro función puede tomar también los valores 101 a 111. La distinción es que usando (1 a 11) la función Subtotales incluye los valores de las filas que estén ocultas después de aplicar Formato > Fila > Ocultar a parte de nuestros datos (es decir, no importa que las filas no sean visibles), mientras que usando (101 a 111) las filas ocultas no se tienen en cuenta (y por tanto los resultados obtenidos son distintos).

Nota: Lo más usual es que usemos Subtotales con datos en vertical. Sin embargo es perfectamente posible usarlo con datos en horizontal, con la única restricción es que entonces la distinción entre (1 a 11) y (101 a 111) no es aplicable (aunque ocultes columnas los resultados no cambian).

Sugerencia: Es posible hacer que el primer parámetro de la función Subtotales esté referenciado a otra celda, de forma que sea el usuario el que elija qué operación quiere hacer con los datos. Por ejemplo aquí hago depender la función del número que se escriba en B3. También he puesto un comentario para indicar las once posibles operaciones a realizar.

 

COMENTARIOS EN EXCEL

Los comentarios son útiles para establecer indicaciones, recordatorios de la fórmula, o para complementar la información mostrada. Pero puedes hacer más cosas:

1) Incluir imágenes en comentarios:

Para hacerlo: Inserta un comentario en una celda, selecciona el borde con el botón secundario del ratón y elige “Formato de comentario…”, en el cuadro de diálogo selecciona “Colores y Líneas”, y en la parte superior despliega la opción “Color” y selecciona la última opción “Efectos de relleno”:

Ve a la última pestaña (Imagen) del cuadro de diálogo y pincha en “Seleccionar imagen” para elegir una imagen de tu disco duro. El resto es historia: Aceptar, Aceptar, y dimensiona el comentario para que muestre tu imagen sin distorsión. Listo.

2) Haz más vistoso ese comentario tan soso:

Puedes asignar a tus comentarios cualquiera de las autoformas de dibujo que tiene Excel.

• Primero haz visible la Barra de Herramientas de Dibujo: Menú Ver > Barras de Herramientas > Dibujo.

• Selecciona el borde de tu comentario, y ahora pincha en el desplegable “Dibujo” de la Barra de Dibujo. Selecciona la opción “Cambiar autoforma” y elige la que prefieras. Listo.

3) Mensajes en celdas seleccionadas:

Aunque no sea un comentario, esta opción es útil cuando quieres que un usuario que llegue a determinada celda de tu hoja de cálculo reciba cierta información. Su propósito es la Validación de datos, pero puedes usarlo para forzar comentarios importantes:

Para aplicarlo sigue estos pasos:

• Sitúate en la celda, selecciona el menú Datos > Validación...
• En la pestaña de “Mensaje entrante” escribe lo que quieras. Listo

Ah!, puedes seleccionar el mensaje entrante y desplazarlo en tu hoja de cálculo a la posición que quieras, y también copiar y pegar esa celda y el mensaje se pegará también.

4) Leyendo comentarios:

Es un incordio ir pasando por encima de celdas con comentarios para poder leerlos todos. Además de la opción clásica de “Mostrar u ocultar comentarios”, para que todos sean visibles permanentemente (o todos queden ocultos), puedes usar las opciones de revisión de Excel para ir uno a uno sin problemas:

• Haz visible la Barra de Herramientas de Revisión (más arriba explico cómo):

• Esos dos iconos con flechas azules que indico te permiten ir saltando de comentario en comentario.
• Ah!, y esto te permite leer en secuencia todos los comentarios del Libro, porque saltará de una hoja de cálculo a la siguiente si hay varias con comentarios.

5) Imprimir los comentarios:

Si los comentarios tienen indicaciones importantes, puede ser necesario imprimirlos. Para eso, tienes que seleccionar dicha opción, que está deshabilitada por defecto en Excel.

Sigue el menú Archivo > Configurar página…, y vete a la pestaña Hoja del cuadro de diálogo. Verás un desplegable junto a Comentarios, donde podrás elegir imprimir los comentarios de dos formas:

1. Imprimir los comentarios Al final de la hoja: Excel imprimirá el documento y al final añadirá una hoja con todos los comentarios de la hoja, indicando la celda en que se encuentran
2. Imprimir los comentarios Como en la hoja: Excel imprimirá los comentarios exactamente igual a como se vean en pantalla (si son visibles).

6) Tamaño automático del comentario:

Por defecto Excel genera los comentarios con un tamaño predeterminado. Y por supuesto siempre es demasiado pequeño o demasiado grande para lo que queríamos. Para remediarlo, puedes hacer que el tamaño del comentario se ajuste exactamente a lo que vayas escribiendo.

Para eso, después de insertar un comentario y seleccionarlo con el botón derecho y elegir “Formato de comentario…” vete a la pestaña de “Alineación”. Marca ahora la opción de Tamaño automático y verás como el tamaño del comentario se irá ajustando conforme escribes. Incluso si pulsas Enter para tener varias líneas, el tamaño se ajustará.