Cómo utilizar Excel: 12 técnicas para usuarios avanzados

Excel es genial, pero tratar de averiguar cómo usar Excel por su cuenta sólo puede llegar hasta cierto punto porque no es intuitivo. Pero si utiliza las técnicas y consejos de este tutorial, podrá realizar su trabajo más rápido y sin mucho estrés.

Aprenda a aplicar fórmulas de Excel, cálculos, filtrado, manipulación de datos, eficiencias de flujo de trabajo y más. Aquí hay una docena de técnicas y características de Excel que necesita saber.

Screencast

How to Use Excel: 12 Techniques for Power Users

Antes de empezar, vaya y descargue la hoja de trabajo de Excel. Hay varias hojas incluidas en el archivo, una para practicar cada una de las doce técnicas de Excel. Vea el screencast tutorial completo arriba o trabaje con la versión escrita paso a paso de abajo.

1. Los fundamentos de escribir una fórmula

Cada fórmula debe comenzar con un signo igual. Si no tiene un signo igual, Excel no realizará el cálculo; Sólo mostrará lo que escribió. Es la manera de decir de Excel: "Hey, calcula esto". Piense en el resultado de su fórmula como lo que debería estar en el lado izquierdo del signo de igualdad si estuviera usando lápiz y papel.

¿Y dónde escribes una fórmula? Escríbala en la celda donde desea que aparezca la respuesta. Consulte la hoja 'simple' en el archivo de Excel libre para este ejercicio.

Ejemplo: añadir los valores de B2, B3 y B4 y poner el total en B6

En B6, ingrese esta fórmula:

=B2 + B3 + B4

A continuación, pulse Intro para obtener el resultado. Si no pulsa Intro (o hace clic en la pequeña marca de verificación justo encima de la columna A), no sucederá nada.

La resta, multiplicación, división y exponentes funcionan de la misma manera. Para la mayor parte, utilice los símbolos que usted utilizaría normalmente o refiera a esta carta:

2. Para cálculos más complejos, utilice Funciones

Si desea hacer un cálculo que es un poco más complicado que una simple fórmula, inserte una función en su fórmula. Excel tiene alrededor de 400 de ellos, por lo que probablemente encontrará alguno que necesite. Hay funciones para negocios, estadísticas, finanzas, fecha y hora, texto, información de documentos y más.

La sintaxis de todas las funciones es:

=NOMBRE DE LA FUNCIÓN(......)

Usted siempre comienza con un signo igual si la función está al principio de una fórmula, luego el nombre de la función, luego un conjunto de paréntesis. Normalmente hay algo entre paréntesis, pero no siempre.

Ejemplo: Encuentre el total y el promedio de una columna de números. Consulte la hoja 'funciones' en el archivo de Excel libre para este ejercicio.

En B10, ingrese la función:

=SUM(B2:B8)

En B11, ingrese la función:

=AVERAGE(B2:B8)

Pulse Intro para obtener los resultados.

3. Para copiar y pegar rápida y inteligente, use AutoFill

Digamos que escribe una fórmula en la parte superior de una columna y desea volver a usar la fórmula en la columna (o si tiene una fórmula al principio de una fila y desea volver a usar la fórmula en la fila). En lugar de copiar y pegar manualmente, utilice la función de relleno automático para hacer un trabajo rápido y hacer que la fórmula se ajuste automáticamente. También puede utilizar AutoFill para introducir meses o días de la semana automáticamente.

Para utilizar AutoFill, busque el pequeño punto en la esquina inferior derecha de la celda actual. (Si tiene varias celdas seleccionadas, comparten un punto común.) Cuando coloca el puntero del ratón sobre el punto, el puntero se convierte en una cruz. A continuación, arrastre la cruz en la columna o en la fila.

Ejemplo: Haga la multiplicación simple en D2, luego AutoFill abajo a D8. Consulte la hoja de "autofill" en el archivo de Excel gratuito para este ejercicio.

Escribe la primera fórmula como lo harías normalmente.

Introduzca la fórmula. Si es necesario, vuelva a pulsar D2. Coloque el puntero del ratón sobre el punto pesado en la esquina inferior derecha de la celda y el puntero del ratón se convertirá en un punto de mira.

Arrastrar el punto de mira a la columna D, o simplemente hacer doble clic. La columna tendrá las fórmulas correctas y los resultados correctos en la columna.

4. Filtrado y clasificación de datos

Puede utilizar Excel como una pequeña base de datos, ordenar filas de datos alfabéticamente o numéricamente y filtrar sólo los datos que desee. Funciona mejor cuando las columnas tienen encabezados, como Nombre, Apellido y así sucesivamente.

Lo más importante a recordar es no seleccionar una columna antes de ordenar. Eso ordenará la columna independientemente del resto de los datos, y eso probablemente no es lo que quieres. Al hacer clic en una columna, Excel es lo suficientemente inteligente para saber lo que está haciendo.

Cuando se trata de filtrado, Excel tiene excelentes herramientas automáticas.

Ejemplo: Ordenar una lista por apellido, ciudad u otra columna, en orden ascendente o descendente. Consulte la hoja 'filter and sort' en el archivo de Excel gratuito para este ejercicio.

Haga clic en cualquier celda de la columna Apellido. En la ficha Inicio de la cinta de opciones, haga clic en Ordenar y filtrar, a continuación, elija A a Z o Z a A. Intente esto con cualquiera de las otras columnas.

Es posible que haya notado que puede hacer filtrado desde el mismo menú desplegable.

Ejemplo: Ahora Filtre la lista para mostrar solo las direcciones de Alaska y California.

Haga clic en cualquier lugar dentro del área de datos y en el menú Ordenar y Filtrar, elija Filtro.

Tan pronto como lo haga, notará que los encabezados de columna reciben las flechas desplegables. Ahora:

• Haga clic en la flecha desplegable en la columna Estado.
• A continuación, haga clic en el cuadro Seleccionar todo para borrar todas las casillas de verificación de estado.
• Vuelva a seleccionar sólo AK y CA y, a continuación, haga clic en Aceptar.

La lista muestra ahora sólo las filas de esos dos estados. Si lo desea, clasifique los estados de A a Z. Eso agrupará todas las filas de Alaska sobre las filas de California (destacadas en la captura de pantalla abajo).

Si desea borrar el filtro, haga clic de nuevo en la flecha desplegable de la columna Estado y seleccione Borrar filtro.

Puede quitar las flechas desplegables de la misma manera que las obtuvo: haga clic en el menú Ordenar y Filtrar y, a continuación, seleccione Filtro.

5. Eliminación de filas duplicadas

Si tiene muchos datos, especialmente si se importan de algún sitio como un formulario web, es posible que tenga filas duplicadas. Excel hace un gran trabajo de eliminar los duplicados y le permite decidir cómo filas similares deben ser para que se consideren duplicados.

Ejemplo: Utilice información de dirección para determinar qué filas son duplicadas. Consulte la hoja 'remove duplicates' en el archivo de Excel gratuito para este ejercicio.

Cuando se desplaza hacia abajo este archivo de ejemplo, vemos que hay tres filas idénticas donde el nombre es Samantha Carter: los datos en todas las columnas son idénticos, por lo que sabemos que es la misma persona que aparece tres veces. Hay también dos filas donde el nombre es Sharon Valeri, pero toda la otra información es diferente, así que sabemos que éstas son dos personas con el mismo nombre.

Asegúrese de hacer clic en algún lugar del área de datos. A continuación, en la ficha Datos de la cinta de opciones, haga clic en Eliminar duplicados.

Deje todas las casillas de verificación seleccionadas. De esta forma, Excel considerará que las filas se duplicarán sólo si todas sus columnas son idénticas.

Haga clic en Aceptar. Nos dice que se eliminaron dos duplicados, que es lo que esperábamos. Cuando nos desplazamos hacia abajo, vemos que dos de los tres registros de Samantha Carter fueron eliminados, y ambos registros de Sharon Valeri permanecen.

6. Separar datos en una columna en múltiples columnas

Si tiene una columna de datos que necesita dividir en varias columnas, utilice el comando Texto a columnas. Sólo tenga en cuenta que no es perfecto porque no puede leer su mente, por lo que podría tener que hacer alguna limpieza manual.

Ejemplo: Divida una columna que contenga nombres completos en columnas separadas para nombres y apellidos. Consulte la hoja de "splitting" en el archivo de Excel para este ejercicio.

Aquí tenemos datos similares a los de los ejemplos anteriores. Tenga en cuenta que tres personas en la lista tienen tres nombres, por lo que se necesitan dos columnas vacías.

En primer lugar, inserte dos columnas en blanco a la derecha de la columna A:

Coloque el puntero del ratón en el encabezado de la columna B, por lo que se convierte en una flecha hacia abajo. Arrastre hacia la derecha, por lo que las columnas B y C se seleccionan.

Haga clic con el botón derecho del ratón y seleccione Insertar en el menú emergente. Puesto que tenía dos columnas seleccionadas, ahora tendrá dos en blanco.

Seleccione todos los nombres en la columna A. (Modo rápido: haga clic en el primero en A3 y, a continuación, presione Ctrl + Shift + Flecha abajo. Luego, desplácese de nuevo hacia arriba.)

En la ficha Datos, seleccione Texto a columnas. Seleccione Delimitado y, a continuación, haga clic en Siguiente.

Elija Espacio como el único delimitador. El tratamiento de los delimitadores consecutivos como uno se seleccionará de forma predeterminada, asi que que dejelo. Desplácese por la lista para ver los nombres que se extenderán en tres columnas.

Haga clic en Siguiente y luego en Finalizar. La columna A tendrá ahora solo nombres, la columna B tendrá apellidos y la columna C tendrá tres nombres que se derramarán.

Puede editar manualmente estos tres nombres y, a continuación, eliminar la columna C, que debería estar vacía.

7. Unir varias columnas en una sola columna

Excel hace un buen trabajo en el otro sentido, también: si tiene varias columnas y desea unirlos en uno. Hay un par de maneras de hacer esto, pero te mostraré la más simple, que es usar una fórmula. Consulte la hoja 'joining' en el archivo de Excel para este ejercicio.

Como lo hizo anteriormente, inserte una nueva columna después de la columna B. Coloque el puntero del ratón en el encabezado de la columna C para que el puntero del ratón se convierta en una flecha hacia abajo ...

... luego seleccione Insertar en el menú emergente. Dé a la nueva columna un título de Nombre completo.

Haga clic en la celda C3 en la parte superior de la nueva columna para hacer la primera fórmula. Para hacerlo más comprensible, lo haremos con un pequeño error, luego lo arreglaremos.

Puesto que deseamos unir el contenido de la célula, no agregar números, el operador a utilizar es un signo &. Así que haga la fórmula:

=A3 & B3

Pero al entrar en él, los nombres y apellidos se aplastan juntos, sin un espacio entre ellos. Así que elimínalo. Vuelva a escribir la fórmula, pero con otro elemento incluido:

=A3 & " " & B3

Poner un espacio dentro de las comillas dobles significa que la fórmula debe incluir literalmente un espacio en blanco.

Escríba y AutoFill hasta la parte inferior.

8. Formato rápido de números y hojas de trabajo

Aquí hay algunas formas rápidas de dar formato a números (dólares, porcentaje, etc.) y hojas de cálculo (colores, fuentes).

Consulte la hoja de "formatting" en el archivo de Excel gratuito para este ejercicio.

Los números de esta hoja de cálculo deben tener comas para separar a miles, pero no necesita decimales. Es posible que desee aplicar signos de dólar a la primera fila, la fila Total y la columna Total. Además, la última columna está formateada como decimales, pero debe parecer porcentajes.

Seleccione la fila superior de números a través de la columna Total.

Mantenga presionada la tecla Ctrl y seleccione a través de la fila Total en la parte inferior (las filas superior e inferior deberían estar ambas seleccionadas).

En la cinta, haga clic en el botón de signo de dólar o presione Ctrl + Shift + $. Esto se aplica signos de dólar, comas y decimales. (Eliminaremos los decimales en un minuto.)

Ahora seleccione todos los números entre y haga clic en el botón de estilo coma o presione Ctrl + Shift + !. Esto aplica comas y decimales.

Para eliminar todos los decimales, seleccione todos los números de la hoja excepto la columna Porcentaje al final y haga clic dos veces en el botón Disminuir decimal.

Ahora vamos a aplicar porcentajes.

Seleccione todos los números en la columna % de Total, luego haga clic en el signo % o presione Ctrl + Shift +%.

Si lo desea, haga clic en el botón Aumentar decimal una o dos veces.

Para formatear la hoja, hay tres maneras de hacerlo:

• Seleccione los encabezados de fila y columna y aplique los colores, las fuentes, etc.
• Seleccione los encabezados de fila y columna y aplique estilos incorporados.
• Formatee toda la hoja como una tabla y deje que Excel aplique el diseño.

Por velocidad, aquí están los métodos segundo y tercero.

Seleccione todos los encabezados de columna y, a continuación, en la cinta de opciones, haga clic en Estilos de celda. Elige uno.

Haga lo mismo para los encabezados de fila.

Deshacer un par de veces.

Ahora haga clic en Formato como tabla y elija un diseño.

Haga clic en Aceptar en el cuadro de diálogo que aparece. Observe que las columnas tienen flechas desplegables para poder filtrarlas.

9. Manipulación de columnas, filas, saltos de página y hojas de cálculo

Cambio de tamaño de fila y columna

Si una columna es demasiado estrecha, es posible que tenga texto cortado o números que se muestren como signos de libra. Si una línea es demasiado corta, es posible que no pueda leerla. Consulte la hoja 'rows & columns' en el archivo de Excel gratuito para este ejercicio.

Ajustar el ancho de la columna y la altura de la fila funciona básicamente de la misma manera: coloque el puntero del ratón en el borde derecho o inferior de la fila o columna para que el puntero del ratón se convierta en una flecha de dos cabezas y arrástrela hasta que la fila o la columna sea del tamaño que desee . O puede hacer doble clic para obtener la fila o columna de tamaño automáticamente. (Esto funciona de la misma manera en el Explorador de archivos de Windows en la vista de detalles.)

Manipulando saltos de página

Hay dos formas de crear saltos de página:

• Insértelos manualmente, en la pestaña Diseño de página
• Usando la vista preliminar de salto de página

Para insertar un salto de página manualmente, seleccione el encabezado de fila o columna en el que desea que se rompa la página y, a continuación, en la pestaña Diseño de página de la cinta, haga clic en el cuadro desplegable salto y seleccione Insertar salto de página.

Para ajustar los saltos de página, utilice la vista previa de salto de página.

Haga clic en el icono Vista previa de salto de página en la esquina inferior derecha y, a continuación, arrastre las líneas. Las líneas punteadas son saltos de página automáticos y, si inserta saltos de página, esas líneas serán sólidas.

Ajuste de hojas de trabajo

Para insertar, eliminar, mover o cambiar el nombre de hojas de cálculo, utilice las pestañas en la esquina inferior izquierda de la pantalla.

Este libro tiene sólo una hoja, denominada Hoja1. Para cambiarle el nombre:

• Haga doble clic en la pestaña.
• Escriba un nuevo nombre (como América del Norte) y, a continuación, pulse Intro.

Para insertar una hoja nueva, en blanco, haga clic en el signo más redondo junto a la pestaña.

Para duplicar la hoja:

• Arrastre la pestaña de la hoja hacia la derecha hasta que aparezca un triángulo apuntando hacia abajo junto a ella.
• Presione Ctrl para que vea un pequeño signo más en el puntero del ratón.

Suelte primero el ratón. Verá una nueva hoja con el mismo nombre y un "(2)" después de ella.

Puede arrastrar las hojas a la posición que desee. Las hojas se caen donde se ve el triángulo apuntando hacia abajo.

10. Mantener los encabezados de columna visibles

Cuando tiene una hoja larga, es fácil para un encabezado de columna desplazarse fuera de la vista, pero hay una característica para evitar que. Cuando imprima una hoja larga, asegúrese de que los encabezados de las columnas aparezcan en la parte superior de cada página—y, por favor, no lo haga copiando y pegando. Eso se convertirá en una pesadilla.

Consulte la hoja de "headers" en el archivo de Excel gratuito para este ejercicio.

Mantener los encabezados visibles al desplazarse

En esta hoja, haga clic en la celda A3, que está justo debajo de la fila de encabezados de columna. A continuación, en la pestaña Vista de la cinta, haga clic en Congelar paneles. Y seleccione Bloquear paneles en el menú desplegable.

Ahora puede desplazarse hacia arriba y hacia abajo de la hoja y seguir viendo los encabezados.

Puede desbloquear los paneles de la misma manera: haga clic en Congelar paneles y, a continuación, seleccione Desbloquear paneles en el menú desplegable.

Sin embargo, esto no tiene ningún efecto en la impresión. Para imprimir encabezados de columna en cada página:

• Vaya a la pestaña Diseño de página de la cinta de opciones y haga clic en Imprimir títulos.
• Haga clic dentro del cuadro Filas para repetir arriba.
• En la hoja de cálculo, seleccione los encabezados de las filas 1 y 2. Que pone $1:$2 en el cuadro.

Para ver que funciona, vaya a Vista preliminar: Archivo > Imprimir o presione Ctrl + P.

Haga clic en las pestañas de la página inferior para desplazarse por las páginas y ver los encabezados.

11. Función IF: Hacer que el valor de la célda dependa de una condición

Excel tiene varias funciones que pueden tomar decisiones de qué valor mostrar. Todos ellos se basan en la función IF. Una vez que esté familiarizado con él, será más fácil para usted utilizar los más complejos.

La función IF tiene tres argumentos:

• Cuál es la condición que está probando.
• Qué debe mostrar la celda si la condición es verdadera.
• Qué debe mostrar la celda si la condición es falsa.

Cuando una función tiene varios argumentos, los separa con comas. Así que esta es la sintaxis:

=IF(condición para probar, qué devolver si es cierto, qué devolver si es falso)

Consulte la hoja 'functions' en el archivo de Excel para este ejercicio.

Ejemplo: Tenemos una lista de órdenes colocadas por personas en varios estados. Si una orden es de New Jersey, aplique un impuesto de ventas del 7%. Si una orden es de cualquier otro lugar, no aplique impuestos.

En la parte superior de la columna de impuestos (H3), escriba esta fórmula:

=IF(C3="nj", G3 * .07, "")

Esto significa:

• La condición para probar es: ¿la celda en la columna Estado contiene el valor "nj"? (Tenga en cuenta que esto no distingue entre mayúsculas y minúsculas, y porque es texto, debe estar entre comillas.)
• Si la condición es verdadera, la celda debe tener un valor de venta * 7%.
• Si la condición es falsa, la celda no debe tener ningún valor (conjunto vacío de comillas).

AutoFill abajo a la parte inferior. La mayoría de las celdas de la columna Impuestos quedarán en blanco y las que no tengan un impuesto sobre las ventas porque la fila muestra NJ en la columna Estado, como se muestra a continuación.

12. SUMIF y AVERAGEIF: Adición y promedio dependiendo de una condición

Similar a la función IF, las funciones SUMIF y AVERAGEIF encontrarán la suma y el promedio de un rango de celdas donde existe un cierto valor o condición.

Las funciones obtienen tres argumentos:

• ¿Qué rango buscamos para encontrar la condición?
• ¿Cuál es la condición o valor a buscar?
• En las filas donde existe ese valor, ¿de qué rango tomamos la suma o el promedio?

La sintaxis de las dos funciones son similares—nombres diferentes, pero los mismos argumentos:

=SUMIF(rango a buscar, valor a escoger, rango a añadir)

=AVERAGEIF(rango a buscar, valor a elegir, rango a añadir)

Consulte la hoja 'SUMIF' en el archivo de Excel para este ejercicio.

Ejemplo: encuentre el total de todas las órdenes a California, y el orden promedio de Kona.

Hacer un SUMIF Primero

En la parte inferior de la columna Venta, en G203, ingrese esta fórmula para encontrar el total de pedidos a California:

=sumif(C3:C201,"ca",G3:G201)

Esto significa:

• Mira abajo de la columna C.
• En la columna C, seleccione las filas que contienen "ca".
• En la misma fila donde encontramos "ca", agregue el valor de la columna G al total.

Haz un AVERAGEIF

Ahora use la misma técnica para encontrar el promedio de todas las ventas de Kona.

Entre en G204 e ingrese esta fórmula:

=averageif(D3:D201,"kona",G3:G201)

Esto significa:

• Mire hacia abajo en la columna D.
• En la columna D, selecciona las filas que contienen "kona".
• En la misma fila donde encontramos "kona", incluya el valor de la columna G en el promedio.

Esto debe ser el resultado:

Uso de estas técnicas de Excel

Aprender a usar Excel vale la pena el esfuerzo. Con estas técnicas y sugerencias, puede poner más de las características potentes y ahorradoras de tiempo de Excel para usarlas en sus propias hojas de cálculo. Práctica las lecciones anteriores, y luego trabaja en la incorporación de estas técnicas en su flujo de trabajo.