Tutorial Excel

Aprende Excel

¡¡¡Más de 300 lecciones de Excel GRATIS!!!

Trucos

Celdas invisibles en Excel

Cuando nos interese hacer invisible el contenido de una celda o varias celdas tenemos una forma sencilla de conseguirlo.

En ocasiones, en alguna hoja podemos tener calculos accesorios que no deseamos que aparezcan en la pantalla. Podemos ocultar las filas o columnas, pero queda mucho mejor si le cambiamos el formato a la celda de la forma que se explica a continuación.

Truco para que el contenido de celdas no se pueda ver

En el siguiente ejercicio tenemos un cálculo de la antigüedad de los trabajadores de una empresa. Para hacer dicho cálculo precisamos de un dato que es la fecha actual y que en este caso hemos incluido en las celdas A3 y A4.

Como esos datos auxiliares no son necesarios que se vean vamos a darle el formato adecuado para que no se vean, aunque realmente sigan en ese lugar.

Lo que tenemos que hacer es señalar las celdas que contienen los datos que queremos hacer invisibles.

A continuación, iremos al Formato de celdas, bien dandole al botón derecho de ratón y eligiendo la opción Formato de celdas o con el atajo de teclas Ctrl + 1.

Se nos abriría la siguiente ventana en la que deberemos elegir la categoría Personalizada.

Una vez seleccionada la categoría Personalizada nos vamos a Tipo (donde señala la flecha roja).

En tipo escribimos tres veces punto y coma de la siguiente forma: ;;; (como se puede ver en la imagen).

Si le damos a Aceptar veremos que todas las celdas previamente señaladas han desaparecido. Sin embargo, siguen estando en ese lugar como vemos si nos colocamos encima de las celdas y observamos la barra de fórmulas.

Tenemos el peligro de sobreescribir esas celdas por lo que sería buena idea protegerlas. Igualmente nos puede interesar hacer aún más invisibles esas celdas, de forma que ni siquiera se puedan ver en la barra de fórmulas.

Esas dos opciones adicionales, las explicamos en en siguiente video:

Tablas dinámicas actualizadas constantemente

Como ya sabemos, las tablas dinámicas no se actualizan de forma automática cuando cambiamos alguno de los datos de origen, por lo que en algunas ocasiones podemos equivocarnos al considerar que la tabla dinámica se encuentra actualizada cuando no es así.

Para actualizar las tablas dinámicas tendremos que hacernos de forma manual dándole a la opción «Actualizar» en el menú contextual de la tabla dinámica.

Pero es posible hacer que las tablas se encuentren cada vez actualizadas cuando accedemos a la hoja en la que se encuentra la tabla dinámica.

Además es muy sencillo, ya que lo podemos hacer insertando únicamente una línea de código como veremos a continuación.

Tablas dinámicas actualizadas

Los pasos que debemos seguir para mantener actualizadas las tablas dinámicas de una hoja son los siguientes.

Acceder al Editor de Visual Basic de la hoja

Nos situamos en la pestaña de la hoja que contiene la tabla dinámica y le damos al botón derecho del ratón para abrir el menú contextual.

En ese menú le damos a la opción «Ver código» para que se abra el editor de VBA.

Preparar el editor de VBA

Lo primero que debemos de hacer es elegir el objeto «Worksheet» y el evento «Activate».

Eso nos abre unas instrucciones (las señalo con la flecha) en la parte superior y en la línea intermedia vamos a insertar el código que nos permitirá mantener las tablas dinámicas actualizadas.

Escribir el código

En la parte del código escribiremos el siguiente código:

ActiveSheet.PivotTables(«TablaDinámica1»).PivotCache.Refresh

Lo que está entre comillas («TablaDinámica1») es el nombre de la tabla dinámica que queremos mantener actualizada.

En el video explicativo se indica cómo se puede conocer el nombre de la tabla dinámica.

Una vez hayamos insertado ese código cada vez que accedamos a la hoja que incluya la tabla dinámica ésta se actualizará.

Guardar el fichero como Excel habilitado para macros

Dado que hemos incluido una línea de código, una macro al fin y al cabo, deberemos guardar el fichero con la extensión de Excel habilitado para macros (.xlsm) para no perder el código que hemos insertado.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Gráfico con esquinas redondeadas en Excel

Cuando utilizamos gráficos en Excel tienen las esquinas rectangulares.

Sin embargo, existe una forma de hacer que nuestros gráficos muestren las esquinas redondeadas.

Para ello tenemos que marcar una opción en el Formato del área del gráfico.

Lo veremos en el siguiente ejemplo.

Crear un gráfico con esquinas redondeadas

En el siguiente ejemplo hemos creado un gráfico de columnas a partir de los datos de una tabla.

Para conseguir que el gráfico tenga las esquinas redondeadas señalamos el gráfico y hacemos clic con el botón derecho del ratón.

De las opciones que nos aparecen hacemos clic en Formato del área del gráfico…

En la parte derecha se abre una barra de opciones.

En la parte superior hay 3 iconos y debemos señalar la primera (la que está marcada por defecto): Relleno y línea.

En esta opción podemos elegir: Relleno y Borde. Tenemos que elegir Borde.

Al elegir Borde se despliega una amplia serie de opciones para modificar la configuración del formato de las líneas del borde del gráfico.

Si bajamos hasta el final, la última opción de abajo tenemos la posibilidad de marcar la opción Esquinas redondeadas.

Si marcamos el recuadro, veremos que el gráfico ya tiene las esquinas redondeadas como vemos en la siguiente imagen.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Fecha y hora actual en Excel

Cuando deseamos insertar en una hoja de Excel la fecha y hora actual, tenemos varias opciones, según que el datos insertado sea estático o dinámico.

La diferencia entre una fecha u hora estática o dinámica es la siguiente:

  • Si la fecha que insertamos es estática nos aparecerá la fecha actual de ese día y no se modificará cuando la abramos en otros dias posteriores.
  • En cambio, si la fecha es dinámica cada vez que accedamos al fichero la fecha que aparecerá es la fecha de ese día. Se actualizará cada vez que accedamos al fichero.

Con la hora pasaría exactamente lo mismo. Puede ser un dato estático o dinámico.

Insertar fecha actual estática

Para insertar la fecha actual de una forma estática utilizaremos un atajo que es Ctrl + «;» o Ctrl + «,» según nuestra configuración regional.

Introduciendo Ctrl + «;»  y pulsando Intro, nos ha introducido la fecha de hoy. 
Si nos colocamos encima vemos, en la barra de  fórmulas, que ha introducido la fecha de una forma estática. Siempre aparecerá esa misma fecha, aunque abramos el archivo al día siguiente.

Insertar fecha actual dinámica

Si lo que queremos es introducir la fecha actual de una forma dinámica, es decir, que se actualice cada 
vez que abramos el fichero, utilizamos la función HOY.

Esta es una función que no tiene argumentos, tenemos escribir =HOY(), y también nos inserta la fecha actual.

Si nos situamos sobre la barra de fórmulas veremos que está la función HOY, por lo que cada vez que abramos el fichero se actualizará la fecha con la fecha actual.

Insertar hora actual estática

Para insertar la hora actual de una forma estática utilizaremos un atajo que es Ctrl + Shift + «.» .

Introduciendo Ctrl + Shift + «.»  y pulsando Intro, nos ha introducido la hora actual. 
Si nos colocamos encima vemos, en la barra de  fórmulas, que ha introducido la hora de una forma estática. Siempre aparecerá esa misma hora, aunque abramos el archivo al día siguiente.

Insertar hora actual dinámica

Si lo que queremos es introducir la hora actual de una forma dinámica, es decir, que se actualice cada 
vez que abramos el fichero, utilizamos la función AHORA.

Esta es una función que no tiene argumentos, tenemos escribir =AHORA(), y también nos inserta la hora actual.

Si nos situamos sobre la barra de fórmulas veremos que está la función AHORA, por lo que cada vez que abramos el fichero se actualizará la fecha con la hora actual.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Rellenar celdas inferiores de forma instantánea

De nuevo vamos a ver una forma de ahorrar tiempo a la hora de trabajar con Excel, dado que en este video veremos cómo podemos rellenar las celdas inferiores a otra de una forma instantánea.

Rellenar celdas inferiores

Aquí tenemos una serie de artículos que tienen establecidos unos precios para el año 2020, y los queremos actualizar para saber los precios correspondientes al año 2021.

Para ello le vamos a aplicar un incremento de precio, que tenemos en la casilla G1, que será del 2,4%.

La fórmula matemática a utilizar, sería multiplicar el precio de cada artículo del año 2020 por (1+2,4%) y si lo referenciamos a la celda que contiene el incremento, habría que multiplicar los precios de 2020 por (1+G1).

Para poder copiar esta misma fórmula a los demás artículos tenemos que establecer que la celda G1, donde está el incremento de precio, sea una referencia absoluta, por lo que finalmente la multiplicación sería por (1+$G$1), para que, cuando lo copiemos hacia abajo, esta celda se mantenga.

Así irá aplicando a cada una de las filas, que corresponden a los distintos artículos, el mismo incremento de precio.

Ahora veremos qué posibilidades tenemos para copiar esa misma fórmula a los demás artículos, y para ello tenemos 3 posibilidades.

Copiar y pegar la fórmula

La primera posibilidad es señalar la celda que tiene la fórmula. Es decir, copiar en este caso la celda C4 y pegarla en el rango A5:A18.

Como en la fórmula, la celda de la actualización la hemos incluido con referencias absolutas, al pegar la fórmula se copiará correctamente.

El problema es que puede ser un procedimiento algo lento cuando hay muchas celdas en donde pegar la fórmula.

Arrastrar hacia abajo la fórmula

De esta forma conseguimos lo mismo que copiando y pegando, pero de una forma más rápida.

En este caso nos situamos sobre la celda que incluye la fórmula y nos dirigimos a la parte inferior derecha del recuadro que señala la celda.

Vemos que hay un recuadro en el que, si nos situamis encima con el ratón el puntero cambia de forma a una pequeña cruz negra.

Cuando cambia de forma el puntero, arrastramos hacia abajo y la fórmula se volverá a copiar a las celdas inferiores.

Hacer doble clic en el recuadro

Aunque con la segunda posibilidad ganamos tiempo frente a la primera opción, hay un método aún más rápido: Hacer doble clic en el recuadro inferior derecho.

De nuevo nos situamos encima del recuadro de la parte inferior derecha de la celda activa y cuando el puntero cambia de forma, en lugar de arrastrar hacia abajo, lo que hacemos es hacer doble clicl.

De esa forma se rellenan de forma instantánea todas las celdas inferiores.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Rellenar varias celdas de una vez

En Excel es muy útil conocer trucos que nos sirvan para ganar tiempo. Notaremos y notarán que somos muchos más productivos con la aplicación de atajos y trucos que no son demasiado conocidos en algunos casos.

Este es uno de los trucos que más nos permite ganar tiempo. Rellenar varias celdas de una vez. Nos permite introducir en diversas celdas un valor, número o función, aunque las mismas no sean celdas contiguas. No me refiero al relleno automático de Excel que se puede utilizar en celdas contiguas.

Rellenar valores en varias celdas de una vez

Rellenar números de una sola vez

Para ello deberemos seleccionar las celdas en las que deseemos introducir los valores o números.

Vamos a empezar seleccionando tres celdas contiguas.

Hemos seleccionado el rango de celdas B2:B4 y queremos rellenar el número 15 en las tres celdas.

Si le damos a Enter lo que hace es rellenar el número en la celda activa, en este caso B2 y el cursor pasa a la siguiente celda.

Eso no es lo que nosotros pretendíamos. Lo que queremos es que rellene las tres celdas con el número 15.

Para ello, lo único que debemos hacer es pulsar, en lugar de Enter, la combinación de teclas Ctrl + Enter.

Pulsando Ctrl + Enter se rellenan todas las celdas previamente seleccionadas.

Rellenar textos de una sola vez

Veamos otro ejemplo con celdas dispersas. En esta ocasión, en lugar de introducir un número, trataremos de rellenar un texto.

En este ejemplo, hemos seleccionado un conjunto de celdas dispersas de la hoja. Recordemos que para seleccionar celdas dispersas debemos dejar pulsada la tecla Ctrl mientras realizamos la selección.

Vemos que si tras escribir en nuestro ejemplo, el texto «Manuel» y pulsar Ctrl + Enter rellena todas las celdas seleccionadas.

Rellenar fórmulas de una sola vez

En esta ocasión veremos que también funciona con las fórmulas. Si seleccionamos las celdas en las que queremos que se aplique una determinada fórmula, si pulsamos Ctrl + Enter, las fórmulas se copian en todas las celdas seleccionadas.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Insertar ECUACIONES en Excel

Vamos a ver cómo podemos insertar ecuaciones en Excel, ya que en Excel es posible agregar ecuaciones complejas en una hoja de cálculo, para que se pueda entender mejor lo que se quiere resolver.

Además el editor de ecuaciones incluye unos símbolos que no encontramos en el teclado como pueden ser símbolos o letras griegas o símbolos matemáticos típicos. 

Es importante saber que Excel no devuelve el resultado de lo que vamos escribiendo. Simplemente lo deja como una imagen para que podamos apreciar la misma. Para Excel una ecuación es igual que 
un objeto, del estilo de un dibujo o una imagen.

Como ejemplo, a continuación vemos tres tipos de ecuaciones o de funciones que podemos insertar desde el mismo editor de ecuaciones.

Cómo insertar una ecuación en Excel

Para insertar una ecuación lo que tenemos que hacer es irnos a la ficha Insertar, y aquí, en el último icono, que es el de Símbolo, tenemos Ecuación.  

Ahora se nos abre la ficha de ecuación. En esta parte tenemos la parte de símbolos, donde podemos elegir diferentes símbolos matemáticos. También letras griegas, otro tipo de símbolos, operadores, flechas, relaciones negadas. Aquí tenemos distintos símbolos que podemos querer integrar en una función.

Aparte tenemos una serie de estructuras de ecuaciones de funciones que nos pueden interesar.

Dentro del grupo de Escrtucturas elegimos el tipo de ecuación que deseamos crear y después solo tendremos que ir afinando las partes a especificar.

Si por ejemplo, deseamos representar una fracción elegimos el icono de Fracción y después elegimos el tipo que más se ajuste a nuestras necesitades.

Entrada de lápiz de ecuación

También podemos intentar  introducir las ecuaciones de una forma manual. Para ello nos vamos a la ficha Insertar. En el icono símbolos – ecuación, desplegamos y en la parte inferior tenemos la opción «Entrada de lápiz de ecuación».  

O si estamos ya dentro de la ficha Ecuación, tenemos aquí también esa opción, de entrada de lápiz de ecuación.

Pulsando sobre esa opción, si yo quiero introducir cualquier tipo de función o ecuación de una forma manual, tenemos que hacerlo aquí con mucho cuidado.

En la parte superior nos interpreta lo que estamos dibujando, y es donde podemos ir viendo 
si lo estamos haciendo de una forma correcta.

En caso de que veamos que lo estamos haciendo bien y que Excel lo ha interpretado de forma correcta le 
damos en la parte inferior, en el botón Insertar.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Realizar una búsqueda por cadena de texto en Excel

En este artículo veremos cómo podemos hacer una búsqueda de una cadena de texto en un listado de datos, de forma que conforme vayamos escribiendo una cadena de texto en una celda nos vaya mostrando todos aquellos datos que incluyan esa cadena de texto,

Eso puede ser muy útil cuando deseamos filtrar entre todos los artículos de nuestro inventario de productos.

Para ello utilizaremos tres columnas auxiliares y diversas fórmulas que se pueden implementar fácilmente para el cualquier otro caso que pueda requerirse.

Caso práctico: búsqueda de cadena de texto en Excel

En este caso práctico tenemos en la columna A un listado con todos los países del mundo. Conforme escribamos un texto en la celda G2 queremos que debajo, de G3 hacia abajo de todos los países que contengan esa cadena de texto.

En el ejemplo que tenemos al escribir «UN» aparecen todos los países que contienen UN en su nombre (Burundi, Hungría, etc.).

Para poder realizar este ejercicio necesitaremos 3 columnas auxiliares que tendremos ocultas.

Las tres columnas auxiliares son las columnas B, C y D.

Asignar número de fila

La columnas B lo que hace es asignarle un número de fila a cada uno de los países y lo vamos a obtener de una forma dinámica, con la función FILAS.

La función FILAS devuelve el número de filas de una referencia o matriz.

La formula a introducir en este caso sería =FILAS(A$3:A3).

De esa forma obtenemos todos los países numerados según su posición en filas.

Ver si el dato contiene la cadena de texto

En la columna auxiliar C introduciremos un fórmula para que, en el caso de que cumpla la condición de que el país contenga el texto introducido en la celda G2, nos devuelva el número de fila de ese país. En caso contrario queremos que la celda quede en blanco.

Con la función ENCONTRAR buscaría la cadena de texto en el nombre del país. En caso de encontrarlo, nos devuelve la posición de esa cadena de texto. Es decir, devolvería un número. Por tanto, si contiene la cadena de texto devuelve un número y en caso contrario, devolvería el error #¡VALOR!.

Con la función ESNUMERO obtenemos el valor VERDADERO si encuentra un número en la celda y FALSO si no tiene un número.

En consecuencia realizamos utilizaremos la función SI para que nos devuelva el número de fila del país si el VERDADERO y la celda en blanco si es FALSO.

Anidando las tres funciones (ENCONTRAR, ESNUMERO y SI) en una fórmula obtenemos el resultado buscado: el número de fila del país que cumple la condición de incluir la cadena de texto.

La fórmula a utilizar en nuestro caso práctico sería la siguiente:

=SI(ESNUMERO(ENCONTRAR($G$2:A3));B3;»»)

Agrupar los países que cumplen la condición

En la tercera columna, la columna D, lo que haremos es colocar todos los países que cumplen la fórmula anterior (es decir, que contienen la cadena de texto).

Para ello utilizaremos la función K.ESIMO.MENOR.

Con esa función ordenaremos en la columna D todos los países de menor a mayor número de fila.

Lo anidaremos en la función SI.ERROR para que no aparezca un error cuando el país no contenga la cadena de texto.

La fórmula a utilizar es la siguiente:

=SI.ERROR(K.ESIMO.MENOR($C$3:$C$238;B3);»»)

Vemos que todos los países que cumplen que contienen la cadena de texto, su número de fila aparece en la columna D, ordenado de menos a más.

Obtener el nombre del país, una vez que tenemos la fila sería sencilla.

Obtener el listado de países

Para obtener el nombre del país utilizaremos la función INDICE.

Realizaría la búsqueda del rango de datos de países y como tenemos en la columna auxiliar D el número de fila, ese sería el segundo argumento de la función.

También aquí lo anidaremos en la función SI.ERROR.

La fórmula que utilizamos es =SI.ERROR(INDICE(A$3:A$238;D3);»»)

Una vez realizados todos estos cálculos podemos ocultar las columnas auxiliares y utilizar la hoja para hacer una búsqueda de datos según que contengan una cadena de texto o no.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Copiar datos de una hoja protegida en Excel

En ocasiones accedemos a un fichero de Excel y no podemos copiar datos del mismo, dado que ni siquiera nos permite seleccionar el rango de datos que deseamos copiar.

El motivo suele ser que se ha protegido la hoja y se ha indicado que no permita seleccionar los datos de

Para proteger una hoja debemos ir a la pestaña Revisar y pulsamos sobre la opción Proteger hoja.

Por defecto, vemos que se quedan marcadas las opciones:

  • Seleccionar celdas bloqueadas
  • Seleccionar celdas desbloqueadas

De esa forma la hoja estará protegida (no nos permitirá hacer cualquiera de los cambios indicadas en las demás opciones), pero sí nos permite seleccionar celdas, que podremos copiar sin problemas.

Pero también puede darse el caso de que se hayan desmarcado esas opciones con lo que no podremos seleccionar celdas, ni consecuentemente copiarlas.

En ese caso, si la hoja estaba protegida y no nos permite seleccionar celdas, tendremos que utilizar otro método para copiar datos de una hoja protegida.

¿Cómo podemos copiar datos de una hoja protegida?

Para copiar datos o un rango de datos de una hoja que no nos permite seleccionar esos datos tendremos que utilizar el cuadro de nombres.

Vamos a ver el caso siguiente en el que deseamos copiar el rango de datos que se encuentra en el recuadro azul.

Si intentamos seleccionar esos datos vemos que no nos lo permite.

Vamos a ver como podemos seleccionar los datos haciendo uso del Cuadro de nombres (recuadro rojo).

Como lo que deseamos es copiar los datos del rango B7:G21 escribimos ese rango en el cuadro de nombres.

Si escribimos el rango de datos en el Cuadro de nombres y pulsamos la tecla Intro parece que no ocurre nada, pero si a continuación le damos a la combinación de teclas Ctrl+C para copiar, veremos que nos señala todo el rango de celdas que hemos incluido en el cuadro de nombres.

Ahora que se ha quedado el rango seleccionado y copiado al portapapeles, podemos pegarlo en cualquier otra hoja o archivo con la opción Pegar o la combinación de teclas Ctrl+V.

Video explicativo:

Suscríbete a mi canal de YouTube:

Suscríbete

Auditoría de fórmulas en Excel

Las opciones de Auditoría de fórmulas en Excel se encuentran en la ficha de Fórmulas y nos permitirán comprender mejor cómo funcionan las fórmulas y entender la estructura de los cálculos realizados en una hoja. Además, con estas herramientas podremos detectar los posibles errores con el fin de corregirlos.

Con vemos en la imagen, el grupo Auditoría de fórmulas tiene 6 opciones:

  • Rastrear precedentes
  • Rastrear dependientes
  • Quitar flechas
  • Mostrar fórmulas
  • Comprobación de errores
  • Evaluar fórmula

Rastrear precedentes

Esta funcionalidad nos muestra con una serie de flechas azules que van desde las celdas relacionadas con la celda en activa que está siendo auditada. Así sabremos qué celdas intervienen en el cálculo de la celda que estamos auditando, permitiéndonos entender cómo funciona dicha celda.

En el caso anterior queremos saber de qué celdas depende la celda D8. Situándonos encima de la celda y pinchando sobre la opción «Rastrear precedentes» nos nuestra el rango de celdas que influyen en su resultado.

Si volvemos a pulsar sobre «Rastrear precedentes» nos muestra las celdas que intervienen en el cálculo de estas celdas precedentes, es decir, serían las precedentes de 2º grado.

Rastrear dependientes

Esta funcionalidad nos muestra con una serie de flechas azules que van desde la celda activa hacia todas las celdas que dependen de la misma.

En el ejemplo anterior queremos saber qué celdas dependen de la celda C3, es decir, si hago algún cambio en la celda C3, qué celdas se van a ver afectadas.

Con la flecha nos muestra que la celda que se verá afectada de una forma directa es la celda D3.

Si pulso otra vez sobre «Rastrear dependientes» vemos las celdas que se verán afectadas al ser dependientes de la celda D3.

Quitar flechas

Para eliminar esas flechas azules debemos utilizar la opción Quitar flechas. De esa forma eliminamos todas las flechas de auditoría que aparecen en pantalla.

Si solamente queremos eliminar las flechas precedentes o dependientes, deberemos desplegar y elegir «Quitar flechas precedentes» o «Quitar flechas dependientes«, respectivamente.

Mostrar fórmulas

Al pulsar esta opción, todas las celdas de nuestra hoja (que contenga una fórmula) mostrarán la fórmula en lugar del valor calculado.

De esa forma podremos entender mejor el funcionamiento de los cálculos que se realizan en una hoja.

Si volvemos a pulsar sobre «Mostrar fórmulas» veremos la hoja de nuevo como estaba.

Comprobación de errores

La opción «Comprobación de Errores» nos permite encontrar errores más rápido.

En este caso hemos incluido un dato de tipo texto en la celda B4, con lo cual me aparece un error en las celdas que dependen de esa celda.

Si pulsamos sobre esta opción «Comprobación de Errores» se abre la siguiente ventana.

Como vemos nos indica en qué celda se ha producido el error y una descripción del tipo de error.

Tenemos varias opciones, como es la de pulsar sobre el botón «Ayuda sobre este error«, con lo que se abre la web de soporte e Microsoft que nos explica en detalle el tipo de error y posible solución.

Otra opción que tenemos es la de hacer un «Seguimiento de error«, nos señala con flechas las celdas que se ven afectadas por el error. En azul las precedentes a la celda en la que se produce el error, y el rojo las dependientes de la misma.

Podemos omitir el error, con la opción «Omitir error» o hacer las modificaciones oportunas en la barra de fórmulas para corregir ese error.

Si este es el caso, haciendo clic en «Modificar en la barra de fórmulas» nos lleva directamente a la barra de fórmulas en la que se señalan oportunamente las celdas implicadas.

Evaluar fórmula

Al hacer clic en el botón de «Evaluar fórmula» presenta paso a paso como se calcula la fórmula con el fin de hallar el error.

Situándonos encima de una celda con error, si marcamos la opción Evaluar fórmulas, nos abre la ventana Evaluar fórmulas, que nos muestra en el cuadro Evaluación la fórmula que será evaluada.

Como vemos, B4 aparece subrayada, lo que indica que al pulsar el botón Evaluar se calculará el resultado de dicha celda, reemplazándola por su resultado.

De esa forma podemos comprobar en qué parte de la fórmula me da un resultado erróneo.

Conforme vamos pulsando el botón «Evaluar» nos muestra el resultado de la parte de la fórmula y desplazando la parte subrayada a otra parte de la fórmula.

Una vez evaluadas todas las parte de la fórmula, se subrayan todos los componentes de la misma, lo que significa que cuando pulsemos de nuevo Evaluar, nos muestra el resultado de la fórmula completo.

Así vemos, paso a paso todos los resultados de los cálculos intermedios de la fórmula, permitiéndonos comprobar en qué momento de los cálculos se genera el error.

Paso a paso para entrar

Esta es una forma muy interesante de evaluar las fórmulas ya que identifica muy rápidamente cualquier tipo de error y su motivo.

Vemos que directamente nos señala la celda que nos motiva el error en la fórmula.

Como vemos existen diversas opciones dentro del grupo de Auditoría de fórmulas que nos permiten hacer comprobaciones en las fórmulas y detectar las causas que originan errores en las mismas.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Insertar una fórmula en Excel

Las fórmulas son las ecuaciones que realizan cálculos de los valores en la hoja. Todas las fórmulas comienzan con un signo igual (=). 

Fórmulas con constantes

Podemos crear una fórmula sencilla usando constante cálculo y operador. Por ejemplo, la fórmula =5+2*3, multiplica dos números y después, agrega un número al resultado.

Fórmulas con referencias a celdas

Cuando queremos hacer referencia a variables en lugar de constantes, podemos usar valores de celda, por ejemplo, =A1+A2. 

Si trabaja con columnas largas de datos o datos que se encuentran en diferentes partes de una hoja o en otra hoja, podemos usar un intervalo (por ejemplo, =SUMA(A1:A100), que representa la suma de las 100 primeras celdas de la columna A.

La ventaja de utilizar fórmulas con referencias a celdas es que ,cuando cambien los datos de cualquiera de las celdas, Excel recalculará los resultados automáticamente.

Fórmulas con funciones

También podemos crear una fórmula usando una función, una fórmula predefinida que simplifica la introducción los cálculos.

Generalmente somos conocedores de las principales funciones y su funcionamiento. Sin embargo, incluso si no conocemos las diferentes funciones, Excel nos lo pone muy fácil para que sean utilizadas en nuestras fórmulas.

Una forma de acceder a las funciones haciendo clic en la ficha Fórmulas.

En el grupo Biblioteca de funciones tenemos acceso a todas las funciones de Excel, categorizadas según categoría.

Si desplegamos por ejemplo, las funciones lógicas nos aparecerán todas las funciones lógicas con que cuenta Excel.

También podemos acceder a las distintas funciones si hacemos clic en el icono de Insertar función que se encuentra justo a la izquierda de la barra de fórmulas.

Si pulsamos sobre ese símbolo se nos abre la ventana Insertar función.

En esa ventana tenemos la posibilidad de buscar una función escribiendo brevemente lo que deseamos realizar.

También podemos elegir una categoría de función y elegir de entre la funciones que conforman esa categoría.

Además en la parte inferior tenemos un enlace «Ayuda sobre esta función» que nos redirige hacia la página de soporte de Microsoft que nos describe la función, su funcionamiento y ejemplos de utilización.

Cuando uno está más habituado a trabajar con Excel, se suele conocer muchas de las principales funciones, por lo que no es necesario buscar la función, sino que escribimos directamente el nombre de la función en la barra de fórmulas.

¿Qué tenemos que tener en cuenta cuando vamos a insertar una función?  

Lo más importante es que comience por el signo igual. Así, conforme nosotros vamos escribiendo además nos aparecen las funciones que incluyen estos caracteres dentro del nombre de la función.  

Debemos de tener en cuenta que cuando hacemos referencia a celdas dentro de los argumentos:  

  • podemos hacer referencia a celdas individuales, por ejemplo, A1. 
  • podemos necesitar hacer referencia a un rango de celdas, como puede ser por ejemplo desde B2 hasta B13. En ese caso la referencia se hace de la forma B2:B13.
  • también podemos hacer referencia a celdas no son contiguas. La referencia se haría de la forma A1;B3. La separación, en lugar de hacerse con dos puntos, se haría con punto y coma. (según la configuración regional puede que sea con coma en lugar de punto y coma).

Se pueden anidar funciones unas dentro de otras. Es decir, se puede insertar una función como uno de los argumentos dentro de otra función en Excel.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Identificación de duplicados de un rango

En ocasiones nos puede interesar resaltar o identificar los datos duplicados de un rango. Por ejemplo, en una lista de artículos o de empleados en los que queremos saber si hay algunos que están duplicados.

En la ficha de Datos existe la opción «Quitar duplicados». Con esa opción se eliminan los datos duplicados de una lista o rango de datos.

Sin embargo, muchas veces no queremos eliminar esos datos duplicados. Solo queremos localizarlos de alguna manera para poder decidir qué hacer con esos datos. En este caso no nos sirve la herramienta de «Quitar duplicados«. Deberemos proceder a la identificación de duplicados en un rango utilizando otro método diferente.

Eso se puede realizar de forma sencilla con el formato condicional.

Identificación de duplicados

Imaginemos la siguiente tabla la que tenemos la identificación de un grupo de trabajadores.

Queremos comprobar si en los códigos de la primera columna existen datos duplicados. Queremos que los identifique y los señale de alguna forma.

Para ello, lo primero que tenemos que hacer es señalar el rango de datos dentro del cual queremos que compruebe si hay valores duplicados.

A continuación vamos a la opción de Formato condicional, que se encuentra en la ficha Inicio.

Dentro de las opciones que se despliegan, elegimos las de crear una «Nueva regla…«.

Ahora marcamos las opciones siguientes:

  • Estilo clásico
  • Aplicar formato únicamente a los valores únicos o duplicados.
  • Duplicado.
  • Aplicar formato según lo que deseemos. En el ejemplo quiero que los duplicados los marque de un color rojo claro.

Si pulsamos sobre Aceptar vemos que nos ha señalado las dos celdas que están duplicadas.

De esta forma podemos señalar los valores duplicados que existan dentro de un rango seleccionado aplicando el formato condicional.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Síguenos

Para aprovechar al máximo el potencial de Excel. Desde un nivel básico hasta un nivel experto.

Ir al blog

Mi canal de Youtube

Contacto

No te pierdas las novedades

100% libre de spam.