A continuación se describen los pasos, a través de un ejemplo,  para hacer un gráfico de barras en una celda contigua a las celdas que contienen los datos.

1. Supongamos cinco filiales de una empresa con la cifra de ventas de cada una de ellas:

2. Crearemos una columna que nos indique el % de ventas sobre el total. Podemos llamar la columna  % sobre el total. Para obtener el resultado en tanto por ciento, se divide cada celda entre la suma de todas ellas y multiplicando por 100.

Para esto escribimos  en la celda C2 la siguiente fórmula =(B2/$B$7)*100 y arrastraremos hacia abajo:

3. En la cuarta columna, cambiaremos el formato de tamaño a 8 y en la celda D2 escribiremos la fórmula =REPETIR(“|”;C2). Arrastraremos hacia abajo para copiarla en el resto de celdas.

Como vemos, estos pasos han generado una barra en la celda contigua a la cifra de ventas muy útil para ver, de una forma más visual, la contribución en las ventas totales de cada una de las filiales. Las barras más largas representarán las filiales que han contribuido más en la cifra de ventas total y las más cortas las que menos. 

En el caso de gráficas de tipo línea, Excel nos permite suavizar los picos para mejorar el aspecto.

Para ello, basta que pongamos el mouse sobre la línea a suavizar, pulsar el botón derecho y seleccionar la opción de Formato de la Serie de Datos. En Excel 2003, marcar directamente la opción de suavizar la línea. En Excel 2007 (ver imagen), seleccionar “Estilo de línea” en el formulario que se abre, y marcar la opción de suavizar.

Para poner el mismo texto en muchas hojas de trabajo, una forma rápida de copiarlo a la vez es la siguiente:

1. Abrir en Excel un libro nuevo y vacío.

2. Pulsar la tecla Control y mientras se mantiene presionada hacer clic sobre las hojas en las que deben aparecer los mismos datos. Por ejemplo, sobre la Hoja1, 2 y 3.

3. A continuación, hacer clic en la celda A1 de la Hoja1 y escribir el texto.

4. Posteriormente, hacer clic en la Hoja2 para comprobar que el texto escrito en la Hoja1 también aparece en la celda A1 de la Hoja2 y de la Hoja3...

Dado un conjunto de datos (por ejemplo la siguiente tabla)

Podemos crear una tabla dinámica con ellos, de manera que en las filas ponemos la fecha y en los datos mostramos el importe (ver imagen)

Para crear y analizar los datos agrupando por años, meses, etc. debemos poner el cursor sobre el campo Date (Celda E2 en la imagen) y ejecutar la opción de menú: Datos à Agrupar y Esquema à Agrupar

Y en la ventana emergente seleccionar el tipo de agrupación, por ejemplo meses y trimestres

Automáticamente en la tabla dinámica se añadirán las dimensiones que hayamos creado

Pudiendo filtrar la información por cualquiera de dichos conceptos.

Supongamos un gráfico en el que se muestra un valor real comparado con un objetivo para cada período con formato de columnas, es decir, se tienen configuradas dos series (una para el valor real y otra para el objetivo), cada una con un color.

Lo que se pretende es poder colorear el valor real en base al cumplimiento (en verde si se supera o igual el objetivo) y en rojo si es menor.

Para ello se crearan dos columnas adicionales, de real, y creamos un gráfico con 3 series (las dos de real y el objetivo:

Ahora, sólo falta cambiar las propiedades de las series.

Las otras dos series se trazaran en el eje secundario (vigilar la escala de los dos ejes).

El gráfico final queda de la siguiente manera:

Cuando se abre un nuevo libro, Excel muestra 3 hojas por defecto. En la mayoría de casos sólo necesitamos una o dos, y, el resto las eliminamos. Para evitar esto, podemos indicar a Excel que sólo cree una hoja por defecto, y añadir más si las necesitamos. En Menu -> Herramientas --> Opciones -->General cambiar la opción “Número de hojas en nuevo libro” de 3 a 1.

Cuando se trabaja en Excel con números de gran magnitud, puede ser interesante formatear su visualización y ocultar los dígitos menos significativos (por ejemplo, mostrar los números en miles o en millones como unidad). A continuación se muestra un uso del formato personalizado de Excel que nos permitirá obtener los resultados que buscamos:

Primero formateamos la celda o serie de números que nos interese, aplicando el siguiente formato personalizado

Formato: [>=1000000]0.." M";[>=1000]0." K";0

Lo que hará que visualicemos el dato de la siguiente forma:

El ejemplo anterior se puede simplificar. Si por ejemplo sólo queremos un formato en miles y no el de millones, el formato personalizado sería: [>=1000]0." K";0

Incluso se puede combinar de forma que sigamos manteniendo el separador de miles y este formato (el separador de miles aplicaría a partir de los miles de millones). Para ello usaremos el siguiente formato personalizado: [>=1000000]#.##0.." M";[>=1000]0." K";0

Existen dos caminos para introducir de forma rápida la fecha y hora actual en una celda de Excel.

Fecha actual:CTRL+SHIFT+; en la celda dónde desee incluir la fecha.

Hora actual: CTRL+SHIFT+: en la fecha dónde desee incluir la hora.

En el caso que desee incluir fecha y hora en la misma celda sólo debe introducir la primera fórmula, teclear espacio e introducir la segunda.

Las infinitas posibilidades que nos ofrece la potente tarea de Ejecución de scripts definida dentro de un proceso de Datacycle Reporting nos permite realizar acciones como esta, ordenar una hoja en función del color de las celdas. Este ejemplo que hemos diseñado nos permite ordenar una lista de nombres de informes donde cada uno tiene asignado un color en función del estado: Revisar, Pendiente, Aprobado, No aprobado,...

Click to read more ...

En algunos casos, puede aparecer la necesidad de no querer imprimir ciertas filas en una hoja de Excel, por ejemplo, filas que contienen información confidencial o resultados intermedios que no son necesarios imprimirlos. Para no imprimir estas filas una opción es ocultarlas antes de imprimirlas. A continuación se muestra una opción rápida.

Click to read more ...

Excel no provee una manera directa de obtener el ratio entre dos valores. Por ejemplo, si la celda A1 contiene 3 y la celda B1 contiene 24, el ratio entre estos dos valores es 1:8 A continuación se muestra una fórmula que muestra el ratio entre dos valores:

Click to read more ...

Este cambio permite definir gráficos con un aspecto interesante, mostrando los resultados con un efecto impactante.

1. En el gráfico, seleccionar la serie deseada (la requerida para reemplazar por un dibujo).

2. Ir al menú Inserar, seleccionar Imágenes Predefinidas (podría utilizarse las otras opciones)

3. Seleccionar la imagen.

Cuando trabajamos con Subtotales en Excel, es frecuente encontrarnos con un tema de formatos; El texto de Subtotal aparece en negrita pero los valores subtotales no lo están, de manera que esto puede hacer que los resultados sean más complicados de leer. A continuación se detallan los pasos para dar un formato a los subtotales a través del Formato condicional.

Click to read more ...

Una característica de Excel muy útil es poder crear una imagen "viva" de un rango y ubicarla en cualquier otra ubicación. Esta técnica puede ser útil, por ejemplo, para imprimir rangos no contiguos en una única página.

Click to read more ...

En un boletín anterior mostramos el uso del formato numérico personalizado en celdas de Excel. En este caso iremos un paso más allá y lo usaremos para definir formatos personalizados de los números visualizados en una tabla dinámica. El proceso es muy similar, pero con la ventaja que Excel dinámicamente aplica el formato personalizado aunque se altere la posición, filtros y campos de la tabla dinámica. Esto es muy útil cuando queremos mostrar desviaciones de previsiones o crecimientos que exceden unos límites que nos hemos marcado.

En el ejemplo, queremos que un informe de ventas de tabla dinámica muestre el valor de la columna "diferencia respecto a año anteror" en rojo si es el incremento está por debajo de -10%, en verde si es superior al 10%, o en negro en cualquier otro caso.

Nos situaremos en la tabla dinámica, en una de las celdas del campo que queremos formatear y pulsaremos el botón derecho del ratón. En el menú contextual que aparecerá de Excel iremos a "Configuración de campo..."

Y en el siguiente diálogo pulsaremos "Número".

Nos situaremos en la opción "Personalizada".

En "Tipo:" pondremos "[verde][>0,1]0,00% ;[Rojo][<-0,1](0,00%);0,00%", tal y como muestra la figura anterior. Al aceptar, veremos como ya tenemos personalizado el formato en el que se muestra la columna "Diferencia".

A veces puede ser interesante ver las fórmulas utilizadas en una hoja sin tener que ir casilla a casilla. A continuación se explica cómo podemos conseguir este efecto:

 Para ello, ir al Editor de Visual Basic, y, añadir en el ThisWorkbook el siguiente código:

 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim cell As Range
    
    For Each cell In Range("A1:E11")
        If cell.HasFormula Then
            cell.ClearComments
            cell.AddComment cell.Formula
            cell.Comment.Shape.TextFrame.AutoSize = True
        End If
    Next cell
End Sub

Nota: el rango "A1:E11", corresponde a las columnas y filas donde afecta este efecto.

En los encabezados de fila y de columna normalmente necesitamos tener listas o series de información normalmente relacionada, pero que no suele ser información numérica ni celdas con formulas.

Excel oferece la opción de crear nuestras propias listas, para automatizar la inserción

de estos datos en las hojas de un libro. Estas se gestionan desde el menu: Herramientas > Opciones > Listas personalizadas

Por ejemplo hemos creado una lista con las provincias de Cataluña.

Esta opción sumada a la función de Autorelleno de Excel potencian mucho más el sentido de las listas.

El autorelleno permite, con solo arrastrar desde el vertice inferior-derecho de un rango de celdas que contenga una serie , rellenar las celdas siguientes con los siguientes elementos de la serie.

Esta misma función de autorelleno se puede aplicar a las listas con un elemento de la lista:

Por Ejemplo:

También se puede realizar en dirección horizontal , extendiendo la lista a diferentes columnas, para utilizarla como cabeceras.

Una necesidad común es la de saber qué día es el último de un mes cualquiera dentro de Excel. Esto lo podemos conseguir con la siguiente fórmula:

=FECHA(AÑO(B4); MES(B4)+1;0)

Dónde B4, en este caso, es una celda de Excel donde tenemos la fecha de la que queremos saber el último día de mes.

Este truco muestra una fórmula para poder contar el número de valores únicos de una lista sin tener en cuenta las celdas vacías.

Existe una función en Excel para extraer datos de una tabla dinámica a una celda fuera de la tabla, esta es IMPORTARDATOSDINAMICOS.

No es necesario conocer su funcionamiento, ya que si nos situamos en una celda externa a la tabla cualquiera, pulsamos = y seleccionamos el valor dinámico que queremos, nos aparece directamente la formula con todos los parámetros, etc.

En algunas versiones de Excel no está activa esta opción, y lo único que hace es poner el nombre de la celda referenciada.

Para solucionar esto, hemos de acceder al menú Ver, Barra de Herramientas, Personalizar, ir a la pestaña Comandos, seleccionar la Categoría Datos y en la ventana derecha (Comandos) busca el botón "General Get Pivot Data".

Este botón nos permite cambiar en el modo de presentación de la formula, tiene este aspecto:

Esta funcionalidad está a partir de Office XP.