Cómo convertirse en un experto en Excel

Excel es una herramienta de Microsoft que se ha convertido en un básico en prácticamente cualquier oficina. ¿Recuerdas cuando tenías que hacer cientos de cuentas a mano o con una calculadora? ¡Qué difíciles tiempos eran aquellos!

Pero Excel no sólo sabe sumar, multiplicar o dividir. ¿Necesitas combinar dos hojas con datos similares? Excel puede hacerlo. ¿Necesitas realizar cálculos con miles de datos en cuestión de segundos? Excel puede hacerlo. ¿Necesitas combinar la información de varias celdas? Excel puede hacerlo. Antes de dedicar horas y horas a contar celdas o a copiar y pegar datos, busca en Excel, seguro que tiene alguna opción para hacerlo por ti.

Para ayudarte a ser más eficiente en tu trabajo (y evitarte trabajo aburrido y tedioso), aquí te traemos algunos trucos para ayudarte a iniciarte con esta fantástica herramienta de trabajo: Excel.

14 trucos para convertirte en un maestro de Excel

1. Tablas dinámicas

Se utilizan para reorganizar datos en una hoja de cálculo. No modificarás los datos que tienes en tu hoja de cálculo, pero pueden resumir valores y comparar diferente información de tu hoja, dependiendo de lo que quieras que hagan.

Por ejemplo, imagina que quieres saber cuántos alumnos hay en cada uno de los 4 cursos de una carrera universitaria. Quizá pienses que no es un número excesivo, pero ten en cuenta que esto se puede extrapolar a documentos con miles de datos.

Para crear una tabla dinámica tienes que ir a Datos -> Tabla dinámica. 

Excel rellenará automáticamente tu tabla dinámica, aunque puedes cambiar el orden de los datos. Tienes 4 opciones para elegir:

• Filtros: te permite ver sólo algunas filas dentro del conjunto de datos. Por ejemplo, si quisieras ver solamente  a los alumnos de 2º curso podrías incluir un filtro por curso. 
• Columnas: aquí colocarás los datos que quieras como nombres de tus columnas.
• Filas: serán los nombres de tus filas. 
• Valores: aquí puedes ver los números de diferentes formas. Puedes sumar, contar, hacer la media, ver el máximo o el mínimo, etc. De manera predeterminada, al arrastrar un campo en Valor se realiza un recuento.

2. Añadir más de una fila o columna

Es posible que, después de tener todos los datos en tu hoja de cálculo, te des cuenta de que necesitas incluir más filas o columnas con datos. Añadir una a una puede ser una tarea tediosa si necesitas incluir cientos de datos.

Para añadir múltiples filas o columnas, lo único que necesitas hacer es seleccionar el mismo número de columnas o filas que necesites, clicar en el botón derecho de tu ratón y seleccionar Insertar.

3. Filtros

A veces, cuando tienes bases de datos con muchos datos por entrada no necesitas tener todos los datos a la vista continuamente. Los filtros se utilizan para seleccionar qué variables de los datos quieres ver en ese momento y trabajar con una hoja de datos más despejada.

En Excel puedes añadir un filtro en cada columna y desde ahí elegir qué datos de esa columna quieres que se muestren. 

En nuestro ejemplo anterior, vamos a añadir un filtro a la columna curso para ver quién pertenece al curso de 3º. Para ello, seleccionamos la columna a la que queremos añadir el filtro, nos vamos a la pestaña Datos y desde ahí clicamos en Filtros.

4. Eliminar entradas duplicadas

Cuando trabajamos con grandes bases de datos, puede suceder que tengamos datos duplicados. Esto puede suceder, por ejemplo, con los datos de contacto que los usuarios ingresan para apuntarse a nuestra newsletter: hay algunos usuarios que se apuntan varias veces porque pueden haber olvidado que ya estaban apuntados. Cuando esto ocurre, debemos eliminar aquellos datos duplicados que empañan los resultados de nuestros análisis.

Para eliminar duplicados, selecciona todas tus columnas, vete a la pestaña Datos y selecciona Quitar duplicados.

Como se observa en la primera imagen, teníamos duplicado al usuario de 1º curso Juan Martín. Después de hacer la limpieza de duplicados, Excel nos informa de que ha eliminado un valor duplicado.

5. Transposición

Cuando tienes tu hoja de datos llena de columnas, puede suceder que quieras convertir alguna de ellas en filas, o viceversa. Copiar y pegar cada dato para transformar la columna en fila es una labor titánica, e innecesaria.

Para transponer una columna en una fila, o al revés, selecciona los datos que quieres modificar y cópialos con el botón derecho del ratón (o el comando CTRL + c). Después, selecciona la celda donde quieres que comience tu nueva fila/columna y clicla en Pegado especial. Te aparecerá un cuadrado como el de la imagen de la derecha, selecciónalo y dale a Aceptar.

6. Texto en columnas

Es posible que tengas información en una celda que quieras dividir en dos celdas diferentes. Por ejemplo, a lo mejor tienes el correo de trabajo de una persona y de ahí quieras extraer el nombre de la empresa y colocarlo en otra celda distinta. O separar el nombre completo de una persona en nombre y apellidos.

Con Excel podemos hacer esto de una forma muy sencilla. Primero selecciona la columna que deseas dividir. Después vete a Datos y clica en Texto en columnas. Aparecerá un cuadrado con información adicional.

Primero debes seleccionar cómo son los datos: delimitados o de ancho fijo.

• Delimitado significa que quieres dividir la celda basándote en caracteres como comas, espacios o tabulaciones.​
• De ancho fijo significa que quieres elegir el espacio concreto de todas las celdas en el que harás la separación. 

Aquí seleccionaremos Delimitados para que podamos separar la primera parte del correo de la segunda.

7. Cálculos sencillos

Además de realizar cálculos complejos, Excel te permite hacer cálculos sencillos con miles de datos como sumar, restar, multiplicar y dividir.

• Para sumar, escribe + en el cuadro de fórmulas.
• Para restar, escribe – en el cuadro de fórmulas.
• Para multiplicar, escribe * en el cuadro de fórmulas.
• Para dividir, escribe / en el cuadro de fórmulas.

Además, puedes utilizar los paréntesis para determinar qué operaciones se realizan primero. Por ejemplo, en (10+10*10), primero se realizaría la cuenta 10*10 y al resultado se le sumaría 10. Sin embargo, en (10+10)*10, primero se sumaría 10+10 y el resultado se multiplicaría por 10.

8. Formato condicional

Esta herramienta te permite cambiar el color de las celdas basándose en la información contenida. Por ejemplo, si quieres acceder fácilmente a los 10 clientes que más facturan para hacerles un obsequio, o los que están por debajo de la media para tratar de incentivar sus compras, puedes hacer esto y acceder de manera más sencilla a la información.

Para ello, selecciona las celdas en las que quieras aplicar el formato condicional, selecciona desde el menu Inicio el botón Formato condicional. Elige desde el menú desplegable la opción que desees, o crea una nueva regla.

Como veis, hemos aplicado el formato condicional en el curso de los alumnos para saber en qué nivel se encuentran cada uno. A aquellos alumnos de color verde que están en el último curso podremos ofrecerles sesiones de orientación laboral para su inminente futuro laboral.

9. El condicional SI

A veces necesitamos contar un dato de celdas que vamos a añadir que contengan una determinada información, pero no podemos hacerlo manualmente por la cantidad de tiempo que perderíamos. Para ese tipo de casos tenemos la formula SI.

Por ejemplo, imagina que queremos darle una beca a los alumnos cuya nota sea sobresaliente. En lugar de ir buscando alumno por alumno cuáles son los que poseen un sobresaliente, podemos utilizar la fórmula SI y establecer qué y cuántos alumnos obtienen beca.

Para conseguir este resultado, debes posicionarte en la celda donde quieres que aparezca el resultado y escribir «=SI». La fórmula que te aparecerá será: =SI(prueba_lógica; [valor_si_verdadero]; [valor_si_falso].

• Prueba_lógica: se refiere a la condición que se debe cumplir para rellenar la celda. Es decir, en nuestro ejemplo hemos determinado la fórmula como «Si la celda E2 contiene la palabra sobresaliente, el resultado en la columna beca será 1; si no contiene la palabra sobresaliente, el resultado será 0». Para copiar esta fórmula en el resto de celdas, posiciónate en aquella donde escribiste la fórmula y pincha y arrastra en el icono + que te aparecerá abajo a la derecha.

10. El símbolo del dólar

El símbolo del dólar $ se utiliza en Excel para fijar una celda, una fila o una columna cuando utilizas fórmulas. Por ejemplo, si quisieras saber qué porcentaje del total de ingresos ha sido facturado por cada alumno, debes hacer una división de la facturación del primer alumno entre el total facturado. Para que la fórmula se copie al resto de alumnos, pincha en el símbolo + que te aparece en la parte inferior derecha de la primera celda.

¿Por qué aparece #¡DIV/0! a partir del 2º alumno? Muy sencillo, al copiar la fórmula del primer alumno al resto de celdas, como no hemos utilizado el símbolo $ para fijar el valor de la facturación total, Excel ha entendido que elegíamos la siguiente celda tanto para la facturación del alumno 2 como para la facturación total, y ésta última celda no contiene datos por lo que estamos dividiendo 2.100 euros entre 0.

¿Cómo lo solucionamos? Fijando la celda con los símbolos del dólar.

Esta aplicación es muy útil, pero escribir a mano los símbolos del dólar es un poco pesado. Para ayudarnos con esta tarea podemos hacer algo muy sencillo: colócate en la celda donde estás escribiendo la fórmula, selecciona el número de celda que quieres fijar y presiona F4. De esta forma se añadirán los símbolos automáticamente y tu celda se hará fija. ¿Quieres fijar una fila o una columna entera? Pulsa F4 dos veces para fijar toda la fila y tres veces para fijar una columna entera.

11. La función BUSCARV

¿Alguna vez has tenido dos conjuntos de datos en dos hojas de cálculo diferentes y has necesitado combinarlas? Supongamos que tienes una hoja de Excel con los nombres y direcciones de correo electrónico de leads de la empresa, y otra con los nombres y la compañía para la que trabajan esas mismas personas. Esto es difícil de manejar, lo más útil sería combinar esos datos en una misma hoja de cálculo, ¿verdad?

Para conseguir combinar estos datos, necesitas que, al menos una columna de una hoja sea idéntica a una columna de la otra hoja. Revisa tus columnas para comprobar que son iguales, incluyendo el número de espacios en blanco de cada celda.

Nosotros tenemos 2 hojas en las que poseemos, por un lado el nombre de los alumnos y sus apellidos, y por otro lado, el nombre de los alumnos y sus correos electrónicos. Queremos combinar ambos datos para obtener una única hoja que contenga el nombre, los apellidos y el email.

La fórmula =BUSCARV(valor_buscado; matriz_buscar_en; indicador_columnas; [ordenado]) funciona de la siguiente manera:

• ​Valor_buscado: este es el valor idéntico que tienes en ambas hojas de cálculo. Elige el primer valor de tu primera hoja. En nuestro caso sería el nombre, que en la hoja 1 es la celda A2, es decir, «Hoja1!A2».
• Matriz_buscar_en: es el rango de columnas de la hoja 2 de donde vas a sacar los datos, incluyendo  la columna de datos idénticos, en nuestro caso, el nombre que vas a combinar en la hoja 1. Nosotros tendríamos que escribir en la fórmula: «Hoja2!A:B». La A es la columna A de la hoja 2, que es la columna idéntica de la hoja 1 (el nombre). La B significa columna B de la hoja 2, que es la información que queremos combinar en la hoja 1.
• Indicador_columnas: aquí debes indicar en qué columna de la selección que hemos realizado en la hoja 2 se encuentra la columna que quieres incluir en la hoja 1. En nuestro caso, nuestra selección sólo tiene 2 columnas y el correo electrónico se encuentra en la 2º columna.  Si nuestros datos de la hoja 2 estuviesen en la columna B y C (siendo B el nombre del alumno y C el correo electrónico), seguiríamos escribiendo 2 en nuestra fórmula, pues el correo se encuentra en la 2º columna de la selección.
• Ordenado: utiliza FALSO para asegurarte que incluyes sólo las coincidencias exactas.

12. Las funciones ÍNDICE y COINCIDIR

Al igual que la función BUSCARV, las funciones ÍNDICE y COINCIDIR extraen información de otra hoja de cálculo y la llevan a una misma localización, aunque tienen algunas diferencias:

1. La función BUSCARV es una fórmula mucho más sencilla. Pero si trabajas con hojas de datos extensas que requieren miles de búsquedas, las funciones ÍNDICE y COINCIDIR pueden reducir drásticamente el tiempo utilizado.
2. La función BUSCARV sólo te permite encontrar valores que están a la derecha del valor coincidente, mientras que ÍNDICE y COINCIDIR te permiten buscar valores que se encuentran a la izquierda.

Por tanto, si quisieras combinar información de las hojas 1 y 2 en la hoja 1, pero los datos de la columna no están iguales en las 2 hojas, para utilizar BUSCARV tendríamos que reorganizar las columnas. En ese caso, sería mejor utilizar las funciones ÍNDICE y COINCIDIR.

Imaginemos en nuestro ejemplo que tenemos en una hoja el nombre completo de los alumnos y su dirección de correo electrónico, y en otra hoja su correo electrónico (en otro orden diferente) y sus notas. La información coincidente en ambas hojas es el correo electrónico, pero se encuentra ordenada de diferente manera. Utilizar la función combinada ÍNDICE COINCIDIR en lugar de BUSCARV nos evitará tener que reordenar una de las columnas de correo electrónico.

La fórmula sería =INDEX(matriz, COINCIDIR), que sería así, una vez desarrollado =INDICE(matriz; COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia)).

Así funcionan las variables de esta función combinada:

• Matriz: el rango de columnas de la hoja 2 que contiene la nueva información que deseas incluir en la hoja 1. En nuestro ejemplo serían las notas, que la tenemos en la hoja 2, la columna B completa: «Hoja2!B:B».
• Valor_buscado: es la columna de la hoja 1 que contiene la misma información (aunque con otro orden) que la hoja 2. En nuestro caso es el correo electrónico, que se encuentra en la columna B, por lo que sería «B:B».
• Matriz_buscada: es la columna de la hoja 2 que contiene la misma información que la hoja 1, es decir, el correo electrónico de nuevo, pero en la hoja 2. A nosotros nos quedaría así «Hoja2!A:A».
• Tipo_de_coincidencia: «1» menor que, «0» coincidencia exacta y «-1» mayor que. En nuestro caso, es coincidencia exacta.

13. La función CONTAR.SI

A la hora de hacer un recuento de determinados valores, en lugar de hacerlo manualmente, podemos recurrir a la fórmula CONTAR.SI. Con esta fórmula, Excel contará el número de veces que una palabra o una cifra aparece en un rango determinado datos.

Por ejemplo, imaginemos que queremos contar el número de notables que tenemos en nuestra base de datos.

La fórmula =CONTAR.SI(rango; criterio) quedaría así en nuestra hoja de cálculo =CONTAR.SI(D:D; «notable»). 

Las variables de la fórmula funcionan así:

• Rango: dónde se encuentran los datos que queremos contabilizar. En nuestro caso, en la columna D, pero nos habría dado el mismo resultado si hubiéramos puesto, por ejemplo, rango de la columna A a la D (A:D) porque esos datos sólo aparecen en la columna D.
• Criterio: qué palabra o cifra quieres que cuente Excel. Ten en cuenta que debes incluir las comillas para que Excel pueda contabilizar esas palabras concretas. Para cifras esto no es necesario.

14. Combinar celdas utilizando «&»

Las bases de datos suelen tener la información dividida para obtener la información más exacta posible. Por ejemplo, los formularios de contacto suelen separar, por un lado, el nombre y, por el otro, los apellidos para que la información que proporciona el usuario contenga la mayor cantidad de datos.

Pero, a la hora de trabajar, quizá nos resulte más cómodo tener el nombre y apellidos en una misma celda (para evitar falsos duplicados), o quizá queramos tener en el mismo lugar la ciudad y el código postal de nuestros clientes para segmentar mejor futuros contactos de marketing.

Para realizar esta combinación de forma sencilla se utiliza el comando «&». En nuestro ejemplo de los alumnos de una facultad, para combinar el nombre y los apellidos, que se encuentran en la columna A y B, respectivamente, debemos escribir la siguiente fórmula =A2&» «&B2. De esta forma combinaremos el nombre de la primera alumna, María, con su apellido, Pérez, en una nueva columna C llamada Nombre completo.

¿Por qué añadimos «un_espacio_en_blanco» a fórmula? Si simplemente escribiésemos =A2&B2, el nombre de la alumna aparecería unido y quedaría MaríaPérez. Al agregar =A2&» «&B2, permitimos un espacio de separación entre ambas palabras.

Ahora ya sabes unos cuantos trucos que te ayudarán a ser más eficiente y, sobre todo, a ahorrar tiempo cuando utilizas Excel. ¿Conoces más trucos de Excel que te ayudan con tus tareas diarias? ¡Cuéntanos en comentarios y ayuda a mejorar a nuestra comunidad!