Trucos

Permitir únicamente números pares

Aquí veremos una aplicación práctica de la función RESUDUO vista en un artículo anterior.

Supongamos que queremos que Excel solo nos permita introducir números pares en una lista de números.

Aunque en principio no habría forma de hacerlo, con este sencillo truco, utilizando la validación de datos y la función RESIDUO, podremos hacer que Excel no restrinja la introducción de valores que no sean números pares.

Esto lógicamente, serviría si lo que queremos introducir son números divisibles entre 3, 4 o cualquier otro valor.

Para suscribirte a mi canal de YouTube:

Suscríbete

Calcular el día de la semana en Excel

En el siguiente video podemos ver un sencillo truco para conocer el día de la semana en Excel de una forma rápida.

Si bien Excel no tiene un formato específico par transformar una fecha en su correspondiente día de la semana, podremos realizarlo por nuestra cuenta de una forma muy fácil.

Suscríbete a mi canal de Youtube no perderte los siguientes videos.

Suscríbete

Mostrar rápidamente las fórmulas utilizadas en una hoja de Excel

En ocasiones tenemos una serie de datos en una hoja de Excel y nos gustaría saber cuáles son números y qué celdas son el resultado de alguna fórmula. Eso es muy sencillo de realizar con un truco poco conocido.

Mostrar fórmulas de Excel

En el siguiente ejemplo tenemos una tabla que incluye las ventas realizadas durante el año 2017 y 2018, así como las proyecciones para el año 2019. Todo ello diferenciando por las distintas tiendas.

Si deseamos ver cómo se han realizado los cálculos, que celdas corresponden a números introducidos y cuáles provienen de fórmulas, lo más normal es situarnos encima de cada celda y ver qué aparece en la barra de fórmulas.

Así, si nos situamos encima de la celda B4 vemos en la barra de fórmulas que aparece el número introducido directamente.

En cambio, si nos situamos en la celda D4 vemos que esa cifra se ha obtenido como resultado de una fórmula.

De esa forma, nos haremos una idea de la forma en la que se hacen los cálculos de la hoja de Excel. Sin embargo, eso puede ser muy tedioso y se puede resolver muy fácilmente.

La mejor forma de mostrar el origen de los datos en Excel y la sintaxis de todas las celdas es mediante la pulsación de una combinación de teclas.

Si la versión de Office es anterior a 2010, tendremos que presionar Alt+º (la tecla que está sobre el tabulador)
Si contamos con una versión posterior, tendremos que pulsar Ctrl+` (la tecla que está justo a la derecha de la letra P)

De esa forma, automáticamente, se mostrará la sintaxis original de todas las celdas.

Con una nueva pulsación de las mismas teclas se restablecerá la visualización original.

Forzar el uso de letras mayúsculas en Excel

Tenemos varias formas de forzar la escritura en mayúscula de un texto en una celda de Excel. Posiblemente la más sencilla y cómoda sea mediante la validación de datos.

Mediante la validación de datos se restringen el tipo de datos o los valores que los usuarios pueden escribir en una celda. De esta forma podemos indicar que solo se permitan incluir datos de una lista, números enteros, fechas, horas,…

Restringir valores a letras mayúsculas en Excel

Para ello seleccionamos las celdas en las que queremos restringir los valores permitidos y después hacemos clic en la ficha Datos – Validación de datos y dentro de las opciones elegimos «Validación de datos…»

Dentro de la ventana que se abre podemos elegir los tipos de datos que están permitidos para esa o esas celdas.

Como vemos no hay ningún criterio de validación que indique solamente mayúsculas. Por ello deberemos elegir la opción de «Personalizada» e indicar una fórmula que verifique si el valor indicado está en mayúsculas.

Una vez le damos a Aceptar se abre otra ventana en la que nos pide la fórmula que se debe cumplir para que acepte el criterio como válido.

En la barra de «Fórmula» debemos incluir la siguiente fórmula si deseamos aplicar el criterio, por ejemplo, a la celda C2.

=IGUAL(C2;MAYUSC(C2))

Esa fórmula utiliza la función IGUAL, que lo que hace es comprobar si dos cadenas de texto son iguales. Como la segunda cadena de texto es la misma que la primera pero con la función MAYUSC, está comprobando si la cadena de texto de la celda C2 es igual que la cadena de texto que escribe en mayúsculas lo que hay en la celda C2. Es decir, comprueba si el texto escrito en la celda es igual que ese mismo texto en transformado a mayúsculas. Para que esta igualdad se cumpla, y la fórmula sea VERDADERA, necesariamente el texto original se ha tenido que escribir con mayúsculas.

Por tanto introducimos esa fórmula en la barra de formulas de la ventana de validación de datos que se ha abierto.

Le damos a Aceptar y ya estamos restringiendo los datos que se pueden incluir en la celda C2 a textos en letras mayúsculas.

Para facilitar al usuario el uso de la aplicación lo ideal es que en la pestaña de Mensaje de error le indiquemos que debe introducir un texto en mayúsculas.

Vamos a comprobar a continuación si funciona y solo nos permite introducir valores con letras mayúsculas.

Cuando introducimos en la celda C2 un valor en mayúsculas, en esta caso «AGOSTO» no hay ningún problema.

Sin embargo, si tratamos de introducir «Agosto» nos aparece el mensaje de error que le indicamos y no introduce el valor.

Por tanto, vemos que con este método podemos forzar que solo se puedan introducir letras mayúsculas en las celdas que deseemos.

Insertar imagen en un comentario en Excel

En un artículo anterior vimos todo lo relacionado con el uso de los comentarios en Excel.

En esta ocasión veremos una posibilidad muy visual y curiosa de los comentarios: insertar una imagen en los comentarios en lugar de texto. Ello puede ser muy útil cuando tenemos por ejemplo un listado de productos o artículos y le queremos asociar la imagen a través de un comentario.

Ya vimos cómo podíamos vincular imágenes a celdas, que puede ser otra posibilidad si deseamos que la imagen se encuentre siempre visible.

Insertar una imagen en un comentario en Excel

Veamos paso a paso cómo podemos incluir una imagen en un comentario en Excel.

En primer lugar seleccionamos la celda en la que queremos insertar el comentario. Una vez se despliega la ventana del comentario nos colocamos con el puntero del ratón sobre uno de los bordes del comentario y hacemos clic con el botón derecho del ratón.

Seleccionamos la opción «Formato de comentario…»

Se abrirá un cuadro de texto en el que vamos a la pestaña “Colores y líneas” y dentro de la misma, en el apartado “Relleno”, abrimos el desplegable “Color”.

Pulsamos en la opción de abajo llamada «Efectos de relleno…»

Se abrirá un nuevo cuadro en que tenemos que ir a la pestaña «Imagen»

Una vez que estamos en la pestaña «Imagen» le damos al botón «Seleccionar imagen…»

Nos abrirá una nueva ventana en la que le debemos de indicar desde donde vamos a seleccionar la imagen.

Si tenemos la imagen en un archivo de nuestro PC le damos a la opción Examinar y seleccionamos la foto que queremos insertar.

Una vez tenemos seleccionada la imagen aparecerá en la ventana de «Efectos de relleno«.

Le damos a «Aceptar» a las ventanas que tengamos abiertas aparecerá en el comentario la imagen seleccionada. Si aparece el texto del usuario deberemos editar el comentario para borrar el texto del usuario.

Como vemos ahora, cada vez que nos situemos sobre la celda con el modelo de coche aparecerá la foto como si fuera un comentario.

Personalizar la barra de herramientas de acceso rápido

Cuando nos encontrams con que utilizamos con cierta frecuencia algunos comandos en Excel no tenemos que ir cambiando constantemente entre las distintas fichas de la cinta de opciones.

La mejor alternativa sería agregarlos a la barra de herramientas de acceso rápido. Así, estos comandos siempre estarían visibles y podemos ejecutarlos con un solo clic.

La barra de herramientas de acceso rápido de Excel

De forma predeterminada la barra de herramientas de acceso rápido está localizada en la parte superior izquierda de la interfaz de Excel, a la izquierda de la barra de título de Excel y justo por encima de la cinta de opciones.

Esta barra, por defecto, incluye tres botones: Guardar, Deshacer y Repetir.

Podemos mostrar la barra de herramientas de acceso rápido por debajo de la cinta de opciones si así lo preferimos. Para ello, colocando el cursor sobre la barra de herramientas de acceso rápido hacemos clic con el botón derecho y señalamos la opción «Mostrar la barra de herramientas de acceso rápido por debajo de la cinta de opciones».

Agregar comandos a la barra de herramientas de acceso rápido

Agregar comandos desde la cinta de opciones

Si queremos agregar un comando que se encuentra en la cinta de opciones lo podemos agregar localizando el comando y haciendo clic derecho sobre el mismo. Una vez desplegado el menú emergente seleccionaríamos la opción Agregar a la barra de herramientas de acceso rápido.

Agregar comandos desde el menú desplegable

Este sería el método que permite elegir entre todos los comandos existentes. No solamente las que están accesibles desde la cinta de opciones.

Para ello podemos hacer clic sobre el botón desplegable de la barra de herramientas. Obtendremos una lista que contiene varios comandos adicionales.

Al seleccionar cualquiera de los comandos de la lista se agregará automáticamente a la barra de herramientas de acceso rápido.

Personalizar la barra de herramientas con más comandos

El último método es muy útil, especialmente cuando el comando que deseamos agregar no se encuentra en la cinta de opciones. Dentro del menú desplegable podemos elegir alguno de los comandos más habituales (Nuevo, Abrir, etc.).

Si queremos seleccionar otros comandos debemos seleccionar la opción Más comandos.

Se abrirá el cuadro de diálogo Opciones de Excel el cual mostrará del lado izquierdo la lista de los comandos de Excel organizados por categorías y del lado derecho los comandos presentes en la barra de herramientas de acceso rápido.

Si en el recuadro izquierdo deseamos ver absolutamente todos los comandos disponibles de Excel debes seleccionar de la lista desplegable de la parte superior la opción Todos los comandos.

Si queremos agregar un comando solamente debes seleccionarlo del recuadro izquierdo y pulsar el botón Agregar lo cual creará una copia del comando en el recuadro derecho.

Por el contrario, para quitar un comando de la barra de herramientas debemos seleccionarlo del recuadro derecho y pulsar el botón Quitar.

Además, en la parte derecha, se muestran unos botones de flechas que nos permitirán subir o bajar los comandos de la barra de herramientas para obtener el orden deseado.

No existe límite a los comandos que podemos agregar a la barra de herramientas de acceso rápido, aunque solo aparecerán en una sola línea. Si ocupamos todo el espacio de la línea se mostrará un botón al final de la línea llamado Más controles y al hacer clic sobre este botón se mostrarán los comandos que estaban ocultos por falta de espacio.

Mantener los ceros a la izquierda de los números

En diversas ocasiones podemos necesitar que nuestros números tengan una cantidad de dígitos fija. Por tanto, en muchas de las ocasiones deberán mantener los ceros a la izquierda de los números.

Esto puede ocurrir en los códigos postales, números de identificación, códigos o similares. También nos puede ocurrir cuando queremos preparar los números para ser importados en otra aplicación que precisa de esa codificación.

Cuando ingresamos números empezando por ceros en Excel, automáticamente elimina los ceros que se encuentran a la izquierda del primer número no nulo. Es decir, si introduzco en una celda «00018» Excel lo transforma directamente en «18», ya que lo considera un número y prescinde de los ceros iniciales.

Forma de establecer una cantidad fija de dígitos

La mejor forma de establecer una cantidad fija de dígitos cuando introducimos valores en un rango de datos es mediante el «Formato de celdas«.

Así, por ejemplo, si deseamos ingresar códigos postales que tienen 5 dígitos queremos que cualquier código mantenga los 5 dígitos. Si introduzco el código postal «1450» quiero que de forma directa aparezca el valor «01450».

En este ejemplo tenemos los códigos postales en el rango de celdas A2:A7.

Tenemos que seleccionar ese rango y haciendo clic con el botón derecho seleccionar la opción «Formato de celdas…«. Eso también lo podemos hacer de forma más rápida con la combinación de teclas «Control + 1«.

Se nos abre el cuadro de diálogo en el que tendremos que elegir la categoría «Personalizada» y en tipo «00000«. Tantos ceros como dígitos queremos que contengan los valores que ingresamos en la hoja.

Tras darle a «Aceptar» vemos que ha transformado todos los números, teniendo ahora los dígitos que deseábamos.

Las funciones de texto con formato fecha

Excel nos permite obtener numerosos resultados a partir de una fecha. Con las funciones de texto con formato fecha, podemos establecer el día de la semana, mes o año, en diversos formatos. Esto nos puede ser de mucha utilidad, por ejemplo, si queremos establecer un calendario o cuadrante.

Con la función texto y aplicando el formato adecuado podremos obtener el resultado que deseemos. Son las funciones de texto con formato de fecha.

Función de texto con formato de día

A partir de una fecha, como por ejemplo, el día 8/9/2018 nos puede interesar que nos indique el día del mes (con uno o dos dígitos) o bien el día de la semana (abreviado o completo). Todo ello lo obtenemos con mucha facilidad aplicando la función TEXTO de la siguiente forma.

Si en la celda A5 tenemos la fecha 08/09/2018 y:

aplicamos la fórmula TEXTO(A5,»D») obtendremos el valor «8», es decir, el día del mes con un dígito. Se obtendrá un sólo dígito cuando el día del mes se encuentre entre el 1 y 9. En caso de ser el día 10 o superior aparecerá con sus dos dígitos.
aplicamos la fórmula TEXTO(A5,»DD») obtendremos el valor «08», es decir, el día del mes con dos dígitos. Por tanto, si el día es entre 1 y 9 nos colocará un cero delante.
aplicamos la fórmula TEXTO(A5,»DDD») obtendremos el valor «sáb», es decir, el día de la semana de forma abreviada.
aplicamos la fórmula TEXTO(A5,»DDDD») obtendremos el valor «sábado», es decir, el día de la semana de forma completa.

Función de texto con formato de mes

A partir de la misma, el día 8/9/2018 nos puede interesar que nos indique el mes con formato de número o texto. De nuevo utilizaremos las posibilidades que nos brinda la función TEXTO.

Si en la celda A9 tenemos la fecha 08/09/2018 y:

aplicamos la fórmula TEXTO(A9,»M») obtendremos el valor «9», es decir, el mes con un dígito. Se obtendrá un sólo dígito cuando el mes se encuentre entre el 1 y 9. En caso de ser el mes 10 o superior aparecerá con sus dos dígitos.
aplicamos la fórmula TEXTO(A9,»MM») obtendremos el valor «09», es decir, el mes con dos dígitos. Por tanto, si el mes correspondiente esta entre el 1 y 9 nos colocará un cero delante.
aplicamos la fórmula TEXTO(A9,»MMM») obtendremos el valor «sept», es decir, el mes de septiembre de forma abreviada.
aplicamos la fórmula TEXTO(A9,»MMMM») obtendremos el valor «septiembre», es decir, el mes de forma completa.

Función de texto con formato de año

De nuevo, siguiendo con el ejemplo del día 8/9/2018 nos puede interesar que nos indique el año de forma reducida o completa.

Si en la celda A13 tenemos la fecha 08/09/2018 y:

aplicamos la fórmula TEXTO(A5,»A») obtendremos el valor «18», es decir, el año con los dos últimos dígitos. Lo mismo obtendríamos si aplicamos el formato «AA».
aplicamos la fórmula TEXTO(A5,»AAA») obtendremos el valor «2018», es decir, el año completo, con los cuatro dígitos. Lo mismo obtendríamos si aplicamos el formato «AAAA».

Evitar parpadeos al ejecutar macro

En muchas ocasiones, cuando ejecutamos una macro en Excel se producen parpadeos mientras se está ejecutando. Esto hace que la macro se ejecute más lentamente.

Esto es debido a que la pantalla se está refrescando continuamente durante la ejecución de cada instrucción que compone la macro. Por ello, cuando más instrucciones y más «grande» sea nuestra macro.

Además provoca una ejecución más molesta para el usuario, ya que está viendo, aunque de forma muy rápida, lo que está haciendo la macro mientras se está ejecutando.

Esto no es algo deseable cuando implantamos nuestra macro.

Pero esto lo podemos evitar muy fácilmente con la propiedad Application.ScreenUpdating .

Evitar parpadeos con Application.ScreenUpdating

La propiedad Application.ScreenUpdating es la que indica a la pantalla que se refresque después de cada instrucción de la macro. ç

Por tanto, lo que nos interesa es que antes de ingresar el código de la macro esta propiedad tenga el valor de FALSE (falso). Después del código de la macro volvemos a establecer el valor de TRUE (verdadero), para que se refresque la pantalla después de ejecutar todo el código de la macro.

Las dos líneas a incluir en nuestro código son las siguientes:

Application.ScreenUpdating = False (Esta linea desactiva el parpadeo de la pantalla)
Application.ScreenUpdating = True (Esta linea activa el parpadeo de la pantalla)

Veámoslo con un ejemplo.

Evitar el parpadeo si nosotros estamos escribiendo el código de la macro

Cuando no estamos utilizando la grabadora de macros, sino que estamos escribiendo nosotros mismos el código, lo único que tenemos que hacer es ingresar las dos líneas de código relacionadas con la propiedad Application.ScreenUpdating.

Lo haríamos de la siguiente forma:

Sub NombreMacro()

Application.ScreenUpdating = False

Código de la macro…

Application.ScreenUpdating = True

End Sub

Evitar el parpadeo cuando hemos utilizado la grabadora de macros

Cuando hemos grabado la macro con la grabadora de macros, no tenemos la forma de ingresarle esas dos líneas de código mientras estamos grabando. Por lo tanto, después de haber grabado la macro deberemos modificarlas para insertarle las dos líneas que evitan el parpadeo.

Para ello, en la ficha Programador o Desarrollador seleccionaremos Macros.

Después seleccionamos la macro que nos interesa, y pincharemos en el botón Modificar.Se nos abrirá el código de la macro, por lo que lo único que debemos hacer es insertar las dos líneas señaladas en color verde.

Sub NombreMacro()

Application.ScreenUpdating = False

Código de la macro…

Application.ScreenUpdating = True

End Sub

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.

Agregar líneas diagonales en Excel

Este es un truquillo básico pero no muy conocido: la forma de agregar líneas diagonales en Excel.

Cuando tenemos una tabla de doble entrada nos puede interesar que en la esquina superior izquierda, la que separa las filas de las columnas, aparezca una línea diagonal.

Aunque muchos saben que en Inicio – Fuentes – Bordes se puede modificar el aspecto de los bordes de una celda, es menos conocido que también se pueden agregar líneas diagonales desde esa misma ventana.

Para ello debemos de hacer clic en “Más bordes…”. Ahí tenemos la opción de seleccionar las líneas diagonales.

Pulsando en la línea diagonal que tenga la inclinación que queramos se incluirá en la celda selccionada.

Rellenar varias celdas de una vez

En Excel, la utilización de trucos que nos sirvan para ganar tiempo nos permitirán ganar mucho tiempo a lo largo del 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. Por tanto, 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.