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.

Cuando se consolida el diseño de una tabla dinámica, se tiende a dejar en una hoja Excel y usar como un informe más. A partir de ese momento puede ser útil definir que siempre que se muestre la tabla sea con los datos actualizados. Y de esta manera no será necesario hacerlo manualmente mediante la opción (“Actualizar Datos”). Esto es muy cómodo si no se tiene activa la barra de herramientas de ‘Tabla dinámica’ en nuestra configuración propia de Excel.

Para conseguir esta actualización de datos automática, basta con realizar estos dos pasos:

1. Actualizar la tabla cuando se abre el documento Excel: Marcar la opción “Actualizar al abrir” en Opciones de Tabla dinámica (se accede desde la barra de T. dinámica o bien pulsando el botón derecho del mouse encima de la tabla dinámica.

2. Actualizar la tabla cuando se accede a la hoja que contiene la tabla dinámica :

Esta acción es muy práctica cuando se tienen los datos origen de la tabla en una hoja diferente (mayoría de casos). Se trata de introducir un código fuente que refresque los datos cada vez que se active la hoja, de manera que si se han hecho cambios en las celdas del rango de datos origen de la tabla, estos se reflejen automáticamente en la tabla en cuanto se quiera visualizar.

Concretamente se ha de añadir este código al evento de ActivarHoja, en la hoja que contiene la Tabla dinámica:

Private Sub Worksheet_Activate()

Dim iNumTable As Integer

For iNumTable = 1 To ActiveSheet.PivotTables.Count

ActiveSheet.PivotTables(iNumTable).RefreshTable

Next iNumTable

End Sub