20 habilidades de Excel y hojas de cálculo que te ahorrarán horas cada semana

La mayoría de las personas que usan Excel todos los días están utilizando menos de una cuarta parte de lo que puede hacer. Vuelven a escribir datos que Relleno Rápido podría limpiar en segundos. Se desplazan por miles de filas buscando un solo valor que una fórmula de búsqueda podría encontrar al instante. Reconstruyen la misma tabla resumen cada semana en lugar de configurar una tabla dinámica una vez y actualizarla con un clic. Nada de esto es un fracaso de esfuerzo. La interfaz de Excel oculta sus características más útiles detrás de los menús, y la mayoría de la gente aprende el programa a través de ensayo y error en lugar de a través de cualquier tipo de instrucción formal.

Las habilidades en esta lista no son exóticas. Son los pequeños movimientos repetibles que separan a alguien que lucha con su hoja de cálculo de alguien que la domina. Un puñado de atajos de teclado, un conjunto de funciones que reemplazan el conteo manual lento y algunos trucos de formato convierten una pared de números en algo legible de un vistazo. Algunas de estas características han existido durante décadas y nunca se incluyeron en la capacitación de nadie. Otras, como XLOOKUP, son adiciones más nuevas que reemplazaron silenciosamente métodos más antiguos y torpes a los que la mayoría de la gente todavía recurre por hábito.

Ninguna de estas requiere un conocimiento de programación o ayuda de un departamento de TI. La mayoría toma menos de cinco minutos para aprender, y una vez aprendidas, ahorran ese tiempo o más todos los días. Un especialista en marketing que elabora un informe mensual, un propietario de pequeña empresa que rastrea inventario, un estudiante que organiza datos de investigación y un gerente que revisa un presupuesto encontrarán algo útil aquí.

La lista se mueve de forma general a más especializada. Los atajos y trucos de formato que se aplican a casi cualquier hoja de cálculo vienen primero, seguidos de funciones y herramientas adecuadas para conjuntos de datos más grandes o desordenados. La mayoría de lo que sigue funciona de manera similar en Google $GOOGL Sheets, con solo pequeñas diferencias en los nombres de los menús o la sintaxis. La lógica subyacente se traslada incluso para los lectores que rara vez abren el propio Excel.

No es necesario aprender las 20 habilidades a la vez. Elegir dos o tres y usarlas de manera constante durante una semana es suficiente para cambiar cómo se construye una hoja de cálculo y cuánto tiempo lleva construir una.

Credit: Canva Images

Desplazarse por una hoja de cálculo con un mouse es una de las formas más lentas de moverse en Excel, y también es una de las más comunes. Un puñado de atajos de teclado elimina casi todo.

Ctrl más una tecla de flecha salta al borde de un bloque de datos en esa dirección. Pasa por alto las celdas vacías y se detiene en la última celda llena antes de un espacio. Esta es la manera más rápida de ir desde la parte superior de una columna hasta la parte inferior de miles de filas sin un solo desplazamiento. Ctrl+Inicio salta a la celda A1 sin importar dónde se encuentre actualmente el cursor. Ctrl+Fin salta a la última celda que contiene datos o formato, lo cual es útil para confirmar exactamente cuán grande es una hoja en realidad.

Cambiar entre pestañas de hojas es más rápido con Ctrl+Re Pág y Ctrl+Av Pág que haciendo clic en cada pestaña individualmente, especialmente en libros con una docena o más de hojas. Para ir directamente a una celda específica, F5 o Ctrl+G abre el cuadro Ir a, donde escribir una referencia de celda como Z500 mueve el cursor allí inmediatamente, sin necesidad de desplazarse.

Seleccionar filas o columnas enteras es más rápido con Shift+Espacio, que selecciona la fila, y Ctrl+Espacio, que selecciona la columna, que arrastrando a través de un encabezado. Combina cualquiera de ellos con Ctrl+Shift+Flecha para extender una selección desde la celda actual hasta el borde de un bloque de datos en un solo movimiento. Esto es útil antes de copiar, eliminar o formatear un rango grande.

Ninguno de estos atajos requiere memorizar un manual. Se asignan a la lógica que Windows usa en otros lugares, lo cual es parte de por qué se quedan una vez aprendidos. Una persona que usa incluso tres o cuatro de ellos constantemente notará dentro de una semana que navegar un gran libro de trabajo lleva una fracción del tiempo que solía.

Estos atajos también se trasladan casi exactamente a Google $GOOGL Sheets. Ambos programas comparten convenciones de teclado heredadas de décadas de diseño de software de hojas de cálculo. Cualquiera que divida su trabajo entre las dos plataformas no necesita aprender un segundo conjunto de hábitos.

Credit: Canva Images

Llenado Rápido, introducido en Excel 2013, observa lo que una persona escribe en una columna e intenta detectar un patrón basado en los datos ya sentados al lado de ella. Una vez que reconoce ese patrón, ofrece completar automáticamente el resto de la columna, sin necesidad de fórmula.

El ejemplo clásico es dividir un nombre completo en nombres de pila y apellidos. Escribir "John" en la primera fila de una columna al lado de una que dice "John Smith" provoca una sugerencia. Una vez que una segunda entrada confirma el patrón, Excel completa el resto de los nombres, correctamente separados de los apellidos. La misma herramienta puede extraer un dominio de una dirección de correo electrónico, reformatear números de teléfono en un estilo consistente o combinar un nombre y apellido en un solo campo. También puede convertir un formato de fecha desordenado en uno estándar.

Llenado Rápido se puede activar escribiendo el patrón manualmente hasta que Excel gris una sugerencia, luego presionando Enter para aceptarlo. También se puede activar directamente mediante Ctrl+E una vez que al menos una fila de ejemplo esté llena, o a través del botón Llenado Rápido en la pestaña Datos.

La herramienta funciona al coincidir patrones en datos visibles, no aplicando una regla fija. Esto la hace adecuada para datos de origen limpios y consistentes y trabajos de limpieza únicos, en lugar de algo que necesite seguir funcionando automáticamente a medida que se agregan nuevas filas. Si los datos de origen cambian, Flash Fill no se actualiza por sí solo de la manera que lo haría una fórmula. Para un conjunto de datos que solo necesita ser limpiado una vez, eso rara vez es un problema.

Vale la pena revisar los resultados cuidadosamente antes de aceptarlos, especialmente con conjuntos de datos más grandes. Flash Fill a veces puede malinterpretar un patrón si los datos de origen tienen un formato inconsistente a mitad de camino. Revisar la columna llenada en busca de entradas que parecen fuera de lugar toma unos segundos y evita que un pequeño error se filtre en un informe terminado.

Credit: Lukas Blazek / Pexels

Una tabla dinámica toma una larga lista de datos en bruto y la convierte en un resumen sin requerir una sola fórmula. En lugar de sumar manualmente las ventas por región o contar pedidos por mes, una tabla dinámica realiza la agregación arrastrando los nombres de campo a diferentes cuadros.

Para construir una, seleccione cualquier celda dentro de un rango de datos, luego vaya a la pestaña Insertar y elija Tabla Dinámica. Excel abre una nueva hoja con una lista de campos al lado. Arrastrar un campo al cuadro de Filas desglosa el resumen por esa categoría. Arrastrar un campo al cuadro de Valores calcula un total para él, suma por defecto, aunque esto se puede cambiar a promedio, conteo, o varias otras opciones. Arrastrar un campo al cuadro de Filtros permite que un informe se reduzca a una región, una línea de productos, o un período de tiempo sin tocar los datos subyacentes.

Las fechas colocadas en una tabla dinámica se pueden agrupar automáticamente en meses, trimestres o años. Esto convierte una columna de fechas de transacciones individuales en una tendencia mensual limpia sin escribir una sola función de fecha. Cambiar el resumen de un total a un promedio, o de un conteo a un porcentaje del total general, toma un par de clics en lugar de una fórmula reconstruida.

La mayor ventaja sobre un resumen manual es lo que sucede cuando cambian los datos de origen. Un resumen construido manualmente necesita ser reconstruido, o tener sus fórmulas verificadas, cada vez que se agregan nuevas filas. Una tabla dinámica solo necesita una actualización, realizada a través de la pestaña de Datos o haciendo clic derecho en cualquier lugar dentro de la tabla, y se recalcula automáticamente contra los datos actuales.

Las tablas dinámicas tienen una reputación de ser complicadas, pero la mecánica real se asemeja más a clasificar un conjunto de cajas etiquetadas que a programar. Cualquiera que se sienta cómodo arrastrando y soltando puede construir una funcional en el primer intento. La habilidad se transfiere directamente a Google $GOOGL Sheets, que tiene su propia versión de la misma herramienta bajo el mismo nombre.

Credit: RDNE Stock project / Pexels

VLOOKUP ha sido parte de Excel desde las versiones más tempranas del software y sigue siendo una de sus funciones más utilizadas. También tiene limitaciones que afectan incluso a usuarios experimentados. Solo busca en la columna más a la izquierda de un rango seleccionado y devuelve un valor de una columna a la derecha, contado por posición en lugar de por nombre. Insertar una nueva columna en ese rango cambia la cuenta, rompiendo la fórmula sin advertencia.

XLOOKUP, introducido por Microsoft $MSFT en 2019 y lanzado más ampliamente a los suscriptores de Microsoft 365 en 2020, soluciona la mayoría de estos problemas. Puede buscar en cualquier dirección, izquierda o derecha, por lo que la columna de búsqueda ya no necesita estar a la izquierda de la columna de resultados. Hace referencia a la columna de resultados directamente en lugar de contar su posición, por lo que insertar o eliminar columnas en otra parte de la hoja no rompe la fórmula. También por defecto busca una coincidencia exacta, donde VLOOKUP por defecto busca una aproximada — una diferencia que ha causado más errores de hoja de cálculo que casi cualquier otra configuración en Excel.

XLOOKUP también tiene una forma incorporada de manejar un valor que no se encuentra. En lugar de devolver el error #N/A que produce VLOOKUP, XLOOKUP puede configurarse para devolver un mensaje específico, un cero o una celda en blanco. Esto evita que ese error se propague a otras fórmulas que dependen del resultado.

Un caso de uso típico es buscar el precio de un producto de una lista separada usando su código de producto. Otro es obtener el departamento de un empleado a partir de un número de identificación almacenado en una hoja diferente. Donde VLOOKUP requiere contar exactamente cuántas columnas a la derecha se encuentra la respuesta, XLOOKUP simplemente apunta a la columna misma.

XLOOKUP requiere Microsoft 365 o Excel 2021 o posterior. No está disponible en Excel 2019, 2016 ni en ninguna versión anterior independiente. Cualquiera con una licencia más antigua deberá quedarse con VLOOKUP o INDEX y MATCH hasta que actualicen. Google $GOOGL Sheets ha añadido desde entonces su propia versión de la función también.

Credit: Canva Images

Leer una columna de números buscando algo inusual es lento y fácil de equivocarse, especialmente después de unas pocas docenas de filas. El formato condicional realiza ese escaneo automáticamente aplicando un formato, como un color de relleno o un ícono, a las celdas que cumplen con una regla establecida de antemano.

La función se encuentra en la pestaña Inicio bajo Formato condicional. Algunas reglas integradas cubren las necesidades más comunes. Estas incluyen resaltar valores duplicados en una lista, resaltar celdas que están por encima o por debajo del promedio o resaltar el conjunto superior o inferior de valores por rango. Las reglas personalizadas pueden ir más allá, utilizando una fórmula para definir exactamente qué celdas deben ser marcadas, como marcar cualquier gasto que exceda una línea presupuestaria específica.

Las escalas de color aplican un gradiente en un rango, sombreando los valores más altos de un color y los más bajos de otro. Esto convierte una tabla de números en algo más cercano a un mapa de calor que se puede leer de un vistazo. Las barras de datos llenan cada celda con una pequeña barra horizontal dimensionada a su valor, útil para comparar magnitudes en una columna sin un gráfico separado. Los conjuntos de iconos agregan pequeños símbolos, como flechas o colores de semáforo, junto a cada valor según el rango en el que se encuentra.

Un gerente que revisa un presupuesto mensual puede configurar el formato condicional una vez para que cualquier partida que exceda el objetivo se ponga automáticamente en rojo. Eso supera revisar cada fila a simple vista cada vez que se actualiza la hoja. Un reclutador que escanea una hoja de cálculo de candidatos puede resaltar a cualquiera que falte un campo requerido sin buscar manualmente.

Las reglas se pueden editar o eliminar a través de la opción Administrar reglas de Formato condicional, que enumera cada regla aplicada actualmente a una hoja en un solo lugar. Esto se vuelve útil una vez que una hoja ha acumulado varias reglas, ya que de lo contrario es fácil olvidar cuál está haciendo qué. Las reglas de formato condicional viajan con las celdas a las que se aplican, por lo que copiar un rango formateado en una nueva hoja lleva el formato automáticamente.

Credit: Birgit Müller (WMDE)  / Wikimedia Commons (CC BY-SA 4.0)

Una celda que acepta cualquier entrada escrita es una celda que eventualmente contendrá un error tipográfico, una abreviatura inconsistente o un valor que no corresponde. La validación de datos previene esto al restringir lo que se puede ingresar en una celda, más comúnmente a través de una lista desplegable.

Configurar una comienza en la pestaña Datos, bajo Validación de datos. Elegir "Lista" como el tipo de validación permite un conjunto corto de valores escritos directamente en el cuadro de configuración, separados por comas. También acepta una referencia a un rango de celdas en otro lugar del libro que contiene las opciones aprobadas. Una vez aplicada, la celda muestra una pequeña flecha que abre la lista de opciones. Escribir algo fuera de esa lista genera una advertencia o bloquea la entrada por completo, dependiendo de cuán estricta esté configurada la regla.

Esto es especialmente útil en cualquier hoja que más de una persona complete, como un rastreador de gastos compartidos o un registro de estado del proyecto. Sin validación, una persona podría escribir "En Progreso", otra "en progreso" y una tercera "EPC", lo que hace que la columna sea inútil para filtrar o resumir más tarde con una fórmula como CONTAR.SI. Una lista desplegable obliga a todos a usar el mismo conjunto de opciones desde el principio.

La validación de datos no se limita a las listas. El mismo menú permite que una celda acepte solo números enteros dentro de un rango, solo fechas dentro de una cierta ventana, o solo texto bajo cierta longitud. También puede requerir un valor que pase una fórmula personalizada, como una entrada en una celda antes de que otra celda pueda ser llenada. También se puede agregar un mensaje de entrada, para que al hacer clic en la celda se muestre una pequeña descripción emergente explicando lo que se necesita allí. Esto es útil en una plantilla que otras personas llenarán sin orientación.

Debido a que la lista fuente para un menú desplegable puede apuntar a un rango separado, actualizar los valores aprobados en un solo lugar actualiza cada menú desplegable creado a partir de él. No hay necesidad de editar la configuración de cada celda individualmente.

Credit: Canva Images

Una fórmula como =B2*$C$1 funciona perfectamente bien, pero no le dice nada a un lector sobre lo que B2 o C1 realmente representan sin abrir la hoja para verificar. Un rango nombrado resuelve esto al adjuntar una etiqueta en lenguaje sencillo a una celda o rango, para que la misma fórmula pueda en su lugar leerse =Ingresos*TasaImpositiva.

Crear uno lleva unos segundos. Seleccione la celda o rango, haga clic en el Cuadro de Nombre justo a la izquierda de la barra de fórmulas, escriba un nombre sin espacios y presione Enter. Para más control, la pestaña de Fórmulas incluye una opción de Definir Nombre con un campo de descripción más largo. También ofrece una opción entre dos alcances: el nivel del libro de trabajo, donde el nombre funciona en cualquier hoja, o el nivel de la hoja de trabajo, donde solo se aplica a esa pestaña.

Una vez que un rango tiene un nombre, ese nombre se puede escribir directamente en cualquier fórmula en lugar de la referencia de celda, y Excel lo trata exactamente de la misma manera. Esto es más importante en libros de trabajo con muchas fórmulas que hacen referencia a las mismas pocas constantes, como una tasa de impuestos, una tasa de cambio o un porcentaje de comisión. Nombrar esa única celda significa que cada fórmula que la referencia se lee claramente, y actualizar la tasa en un solo lugar actualiza cada fórmula que depende de ella.

Los rangos nombrados también hacen que las fórmulas sean más fáciles de auditar. Una fórmula construida enteramente a partir de referencias de celdas requiere hacer clic en la hoja para confirmar a qué apunta cada una. Una fórmula construida a partir de rangos nombrados se explica a sí misma en su mayoría.

Una advertencia vale la pena tener en cuenta. Excel generalmente actualiza automáticamente un rango nombrado cuando se insertan filas dentro de él, pero un rango definido de manera inusual puede a veces permanecer fijo en lugar de expandirse. Verificar que un rango nombrado aún cubra las celdas correctas después de una edición importante toma un momento y evita que una fórmula trabaje silenciosamente con un conjunto de datos incompleto.

Credit: Wolf Art / Pexels

Desplazarse hacia abajo en una hoja de cálculo larga eventualmente empuja la fila de encabezado fuera de la parte superior de la pantalla, dejando un muro de números sin etiquetas adjuntas. Congelar paneles resuelve esto bloqueando filas o columnas específicas para que permanezcan visibles sin importar cuánto se desplace el resto de la hoja.

La versión más simple está en la pestaña Vista. Congelar la fila superior mantiene la primera fila de la hoja fijada en su lugar mientras todo lo que está debajo se desplaza libremente. Congelar la primera columna hace lo mismo para la columna más a la izquierda, lo cual es útil en una hoja donde las etiquetas de fila están en la columna A en lugar de en la parte superior. Ambas opciones se aplican con un solo clic y no requieren configuración.

Para más control, seleccionar una celda específica antes de abrir Congelar paneles bloquea todo lo que está arriba y a la izquierda de esa celda. Todo lo que está abajo y a la derecha permanece libre para desplazarse. Esta es la opción a usar cuando una hoja necesita tanto una fila de encabezado congelada como una columna de etiqueta congelada al mismo tiempo. Una gran tabla de datos a menudo necesita esto, con etiquetas de fila en el lado izquierdo y encabezados de columna en la parte superior.

Un panel congelado aparece como una línea gris delgada que cruza o baja la hoja, marcando el límite entre las secciones bloqueadas y desplazables. Se puede eliminar en cualquier momento a través del mismo menú de la pestaña Vista, que ahora muestra Descongelar paneles en su lugar.

Esta función importa más en conjuntos de datos demasiado grandes para caber en una pantalla, que describe la mayoría de las hojas de cálculo de trabajo fuera de cálculos rápidos. Sin ella, verificar a qué columna corresponde un número 300 filas abajo significa desplazarse de nuevo hacia arriba, leer el encabezado y luego desplazarse de nuevo hacia abajo. Con ella, el encabezado siempre está justo ahí. Google $GOOGL Sheets incluye la característica idéntica bajo Vista, luego Congelar.

Credit: Canva Images

Seleccionar un bloque de datos y presionar Ctrl+T lo convierte en lo que Excel llama una tabla, un objeto estructurado que se comporta de manera diferente a un rango simple en varias formas útiles.

El beneficio más inmediato es el llenado automático de fórmulas. Agregar una fórmula a una celda en una columna de tabla la aplica a cada fila en esa columna automáticamente, incluidas las nuevas filas que se agregan más tarde en la parte inferior. Un rango simple requiere copiar manualmente la fórmula cada vez que aparece una nueva fila. Ese paso es fácil de olvidar y es una fuente común de informes que dejan de actualizarse correctamente en parte del camino.

Las tablas también introducen referencias estructuradas, una forma de escribir fórmulas usando nombres de columnas en lugar de direcciones de celdas. Una fórmula escrita como =SUMA(Tabla1[Ventas]) se refiere a toda la columna Ventas de una tabla llamada Tabla1. Sigue totalizando el rango correcto a medida que se agregan o eliminan filas, sin necesidad de reescribirla. Esto se lee más claramente que una fórmula como =SUMA(B2:B847) y nunca necesita actualizarse a medida que la tabla crece.

Cada tabla viene con botones de filtro incorporados en su fila de encabezado por defecto, por lo que la clasificación y el filtrado están disponibles de inmediato sin aplicar Filtro Automático por separado. Las tablas también aplican sombreado de filas alternas automáticamente, alternando un color de fondo claro fila por fila. Esto facilita el seguimiento de una fila a través de una hoja amplia sin una regla o entrecerrar los ojos.

Nombrar una tabla algo más específico que el predeterminado de Excel, hecho a través de la pestaña de Diseño de Tabla, facilita su referencia más adelante. Las fórmulas, tablas dinámicas y gráficos pueden apuntar a una tabla con nombre y actualizarse automáticamente a medida que la tabla crece o disminuye. Un gráfico construido a partir de un rango de tabla se extiende para incluir nuevos puntos de datos agregados en la parte inferior. No hay necesidad de editar el rango de origen del gráfico manualmente.

Credit: Marina Agrelo / Pexels

Sumar una columna de números es simple con SUMA. Sumar solo los números que cumplen una condición específica, como cada venta realizada en una cierta región durante un cierto mes, requiere una herramienta diferente: SUMAR.SI.CONJUNTO.

La función toma un rango de suma, seguido de pares de criterios: un rango a verificar y una condición que ese rango debe cumplir. Una fórmula como =SUMAR.SI.CONJUNTO(MontoVentas, Región, "Oeste", Mes, "Junio") suma cada valor en la columna MontoVentas donde la fila tiene "Oeste" en Región y "Junio" en Mes. Lo hace en un paso, sin necesidad de filtrar manualmente primero. Se pueden agregar pares de criterios adicionales para especificar aún más la condición, y no hay un límite fijo sobre cuántos se pueden combinar en una sola fórmula.

CONTAR.SI.CONJUNTO funciona de la misma manera, pero cuenta filas que coinciden en lugar de sumar un valor. Eso lo hace útil para responder a una pregunta como cuántos pedidos realizó un cliente específico dentro de un rango de fechas determinado. PROMEDIO.SI.CONJUNTO aplica la lógica idéntica para calcular un promedio condicional, como el valor promedio de pedido para una categoría de producto específica durante un trimestre específico.

Antes de que estas funciones existieran en su forma actual, la solución era filtrar los datos a las filas coincidentes, luego aplicar una SUMA o CUENTA simple a lo que quedaba. Luego había que quitar el filtro y repetir el proceso para la siguiente condición. Ese enfoque es lento, fácil de equivocarse y no deja registro de lo que se calculó una vez que se quita el filtro.

SUMIFS, COUNTIFS y AVERAGEIFS resuelven el mismo problema como una fórmula que vive permanentemente en una celda. Se recalcula automáticamente cada vez que cambian los datos subyacentes, sin necesidad de volver a aplicar un filtro. Esto los hace adecuados para cualquier informe que se reconstruya regularmente con nuevos datos, como un resumen de ventas semanal o un desglose mensual de gastos por departamento.

Credit: Canva Images

El pegado estándar Ctrl+V copia todo acerca de una celda: su fórmula, su formato y su valor, todo a la vez. Pegado Especial, que se abre con Ctrl+Alt+V, divide esa acción única en varias más específicas, cada una resolviendo un problema diferente.

Pegar solo valores toma el resultado de una fórmula y lo coloca en la nueva ubicación como un número simple, sin fórmula adjunta. Esto importa más cuando un informe lleno de fórmulas necesita enviarse fuera del libro en el que se construyó. Las fórmulas que hacen referencia a otras hojas o libros pueden romperse o devolver errores una vez removidas de su contexto original. Convertir un informe terminado a valores antes de enviarlo elimina completamente ese riesgo.

Pegar solo formatos copia el estilo visual de una celda, como su color, bordes y formato de número, sin tocar el valor o fórmula que ya reside en la celda de destino. Esta es una forma más rápida de aplicar un formato consistente en una hoja que reconstruirlo manualmente en cada nueva ubicación.

Transponer invierte la orientación de los datos copiados, convirtiendo una fila en una columna o una columna en una fila. Esto resuelve un problema común de disposición: datos ingresados en una columna que realmente necesitan ir en una fila para coincidir con el resto de un informe, o viceversa. Reconstruir esa disposición reescribiendo cada valor lleva mucho más tiempo que copiar el rango, abrir Pegado Especial y marcar la casilla de Transponer.

Pegado Especial también admite pegar con una operación matemática: sumar, restar, multiplicar o dividir el valor copiado en lo que ya está en la celda de destino. Esto se adapta a una tarea como aplicar un único aumento porcentual a toda una columna de precios de una vez. Copia el porcentaje, selecciona la columna de precios y elige multiplicar, en lugar de escribir y arrastrar hacia abajo una fórmula separada para cada fila.

Credit: Avinash Kumar / Unsplash

Escribir "$1,200.00" directamente en una celda parece correcto en la pantalla, pero convierte lo que debería ser un número en texto. Eso rompe cualquier fórmula que intente sumar, promediar o calcular de otra manera. Los formatos de números personalizados resuelven esto cambiando cómo se muestra un valor sin cambiar el valor en sí.

La configuración se encuentra bajo Formato de celdas, abierto con Ctrl+1, en la pestaña Número. Seleccionar Personalizado abre un campo donde un código de formato controla exactamente cómo aparece el número. Un código como $#,##0.00 muestra un número simple como moneda, con un separador de miles y dos decimales. La celda aún contiene el número original, disponible para cualquier fórmula que se use directamente.

Los formatos personalizados pueden agregar una unidad después de un número sin cambiar lo que se almacena, mostrando "1,200 unidades" mientras la celda todavía contiene el número 1200 para fines de cálculo. Un formato de porcentaje multiplica la pantalla por 100 y agrega un signo de porcentaje, mientras que el valor subyacente permanece como un decimal. Eso lo mantiene compatible con otras fórmulas que esperan un porcentaje bruto en lugar de un número entero.

Los números negativos se pueden configurar para mostrarse en rojo, entre paréntesis o con un signo menos, dependiendo de qué convención necesita seguir un informe. Todo esto funciona a través del mismo código de formato, sin necesidad de ninguna regla de formato condicional. Las fechas pueden mostrarse en docenas de órdenes y estilos diferentes, desde una fecha escrita completa hasta una corta numérica. Nada de esto cambia el valor de fecha real almacenado en la celda.

La distinción entre formateo y escritura importa más en cualquier hoja que luego se sumará, promediará o referenciará por otra fórmula. Una columna de números escritos como texto con un símbolo de moneda adjunto no se sumará correctamente con SUMA. La misma columna usando un formato de número personalizado sí lo hará, ya que los valores subyacentes permanecen como números genuinos durante todo el tiempo.

Credit: Canva Images

Copiar una fórmula hacia abajo en una columna o a través de una fila es una de las acciones más comunes en Excel. También es donde aparece un tipo específico de error con mayor frecuencia. Por defecto, Excel trata cada referencia de celda en una fórmula como relativa, lo que significa que se desplaza para coincidir con la nueva posición cada vez que la fórmula se copia a otro lugar.

Ese comportamiento de desplazamiento generalmente es exactamente lo que se desea. Una fórmula que multiplica la cantidad de cada fila por su propio precio debe desplazarse al copiarse por una columna, de modo que cada fila calcule contra sus propios valores. El problema comienza cuando una fórmula necesita hacer referencia a una celda fija, como una tasa de impuesto única o una tasa de cambio, sin importar dónde se copie la fórmula.

Un signo de dólar bloquea una referencia para que deje de desplazarse. Escribir $C$1 en lugar de C1 bloquea tanto la columna como la fila, de modo que copiar esa fórmula en cualquier lugar de la hoja siempre apunta a la misma celda exacta. Un solo signo de dólar solo antes de la letra de la columna, como $C1, bloquea solo la columna mientras permite que la fila se desplace. Un signo de dólar antes solo del número de la fila, como C$1, hace lo contrario.

Presionar F4 mientras una referencia de celda está seleccionada dentro de una fórmula pasa por estas cuatro opciones en orden, sin necesidad de escribir los signos de dólar manualmente. Esto es más rápido que editar la referencia a mano, especialmente en una fórmula con varias referencias que necesitan cada una una combinación diferente de bloqueo.

Un caso de uso común es una hoja de cálculo que calcula comisiones a través de docenas de filas de ventas. La comisión de cada fila depende de su propia cifra de ventas pero de la misma tasa de comisión almacenada en una celda. Bloquear esa única celda de tasa con signos de dólar antes de copiar la fórmula hacia abajo asegura que cada fila haga referencia a la tasa correcta. Sin él, la referencia puede desplazarse a una celda vacía unas filas más abajo y devolver un error o un cero.

Credit: Maria Stewart / Unsplash

El pequeño cuadrado en la esquina inferior derecha de una celda seleccionada, conocido como el controlador de relleno, extiende mucho más allá de copiar un solo valor por una columna. Arrastrarlo reconoce y continúa automáticamente un rango de patrones comunes.

Escribir una fecha en una celda y arrastrar el controlador de relleno hacia abajo continúa la secuencia por día, sin necesidad de fórmula. Escribir el nombre de un día de la semana hace lo mismo para los días de la semana, volviendo a lunes después de domingo. Un nombre de mes continúa a lo largo del año calendario de la misma manera. Escribir dos números en celdas adyacentes, como 10 y 20, luego seleccionar ambos antes de arrastrar, indica a Excel que continúe el patrón en ese mismo intervalo. Rellena 30, 40, 50 y en adelante.

Hacer doble clic en el controlador de relleno en lugar de arrastrarlo llena automáticamente una fórmula o patrón, igualando la longitud de una columna adyacente. Esto es más rápido que arrastrar a mano en una hoja con cientos de filas, ya que Excel detecta dónde termina la información adyacente y se detiene allí.

Más allá de los patrones incorporados, se puede crear una lista personalizada para cualquier secuencia específica de un lugar de trabajo o conjunto de datos. Nombres de departamentos, ubicaciones de tiendas y categorías de productos que no siguen un orden alfabético o numérico son ejemplos comunes. Esto se configura a través de Archivo, luego Opciones, luego Avanzado, luego Editar listas personalizadas, donde se puede escribir una secuencia una vez y guardarla. Después de eso, escribir el primer elemento de la secuencia y arrastrar el controlador de relleno continúa la lista en el orden correcto cada vez. Esto funciona de la misma manera que Excel ya maneja los días de la semana.

Esto convierte una tarea repetitiva de escritura, ingresar la misma secuencia fija de etiquetas en docenas de filas, en un solo arrastre. Es una función pequeña, pero en una hoja reconstruida semanalmente con las mismas categorías recurrentes, se ahorra tiempo real a lo largo de un año de uso.

Credit: cottonbro studio / Pexels

Los datos rara vez llegan en la forma que una hoja de cálculo realmente necesita. Una columna puede contener una dirección completa que necesita dividirse en calle, ciudad y código postal. Un informe exportado de otro sistema puede combinar varios campos en uno que necesita separarse antes de que cualquier fórmula pueda usarlo adecuadamente.

Texto en columnas, que se encuentra en la pestaña de Datos, maneja la versión más simple de este problema. Seleccionar una columna y elegir Texto en columnas abre un asistente que divide su contenido en múltiples columnas según un delimitador. Ese delimitador puede ser una coma, un espacio o un carácter específico que los datos utilizan para separar campos. Esto funciona bien como una solución única: ejecútalo una vez y la columna se divide inmediatamente. La división no se repite automáticamente si se pegan nuevos datos en la misma columna más tarde.

Power Query, al que se accede a través de la sección Obtener y transformar de la pestaña de Datos, resuelve una gama más amplia de problemas y recuerda cómo lo hizo. En lugar de aplicar una acción única, Power Query registra cada paso de limpieza —división de columnas, eliminación de filas en blanco, fusión de tablas de diferentes fuentes, cambio de nombres de encabezados— como una secuencia guardada. Esa secuencia se puede volver a ejecutar con un clic cada vez que se actualizan los datos de origen. Esto se adapta mucho mejor a un informe que se reconstruye regularmente con datos frescos de la misma fuente que una división única.

Power Query también puede combinar datos de múltiples hojas, libros de trabajo o incluso archivos externos en una sola tabla limpia, algo que Texto en columnas no puede hacer en absoluto. Un proceso de informes mensual que extrae datos de varias hojas de cálculo regionales y necesita combinarlos en una tabla consistente es un ajuste natural para Power Query. Los pasos de combinación solo necesitan construirse una vez y luego simplemente actualizarse cada mes.

Las dos herramientas resuelven diferentes escalas del mismo problema. Texto en columnas maneja una división rápida y única, mientras que Power Query maneja cualquier cosa que necesite suceder de la misma manera una y otra vez.

Credit: Richard Heinen / Unsplash

Un informe con detalle mensual en 12 columnas es útil para alguien que verifica un mes específico, pero abrumador para alguien que solo quiere ver el total anual. Agrupar y Esquema resuelve esto colapsando filas o columnas detalladas a un nivel de resumen, sin eliminar ninguno de los datos subyacentes.

Seleccionar un conjunto de filas o columnas y elegir Agrupar, que se encuentra en la pestaña Datos, agrega un pequeño corchete en el margen. Un signo menos en ese corchete colapsa el rango seleccionado a una sola línea cuando se hace clic, y un signo más lo expande de nuevo. Se pueden anidar múltiples niveles de agrupación. Una hoja organizada por año, trimestre y mes puede colapsar hasta totales anuales con un solo clic, luego expandirse un nivel a la vez según sea necesario.

Esto difiere de simplemente ocultar filas o columnas, lo cual no deja ninguna indicación visible de que algo esté oculto y puede confundir a cualquiera que abra la hoja más tarde. Una sección agrupada y colapsada muestra una barra de esquema clara con un signo más, señalando que existe más detalle y se puede expandir a demanda.

El atajo de teclado Shift+Alt+Flecha derecha agrupa un rango seleccionado sin abrir la pestaña Datos en absoluto, y Shift+Alt+Flecha izquierda lo desagrupa. Excel también puede aplicar agrupamiento automáticamente a través de la opción Auto Esquema. Detecta filas de subtotal existentes en una hoja y agrupa el detalle debajo de cada una. Esto es útil en un informe que ya tiene subtotales pero nunca ha sido colapsado para facilitar la visualización.

Un informe financiero mensual es un caso de uso común: 12 meses de detalle en las columnas, colapsándose a una única columna trimestral o anual para una vista de resumen. El detalle mensual completo permanece disponible debajo para cualquiera que necesite verificar un período específico. Nadie tiene que elegir entre una hoja detallada y una limpia, ya que Agrupar y Esquema permite que la misma hoja sirva como ambas.

Credit: Ron Lach / Pexels

Buscar y Reemplazar, abierto con Ctrl+H, maneja una búsqueda directa de un texto exacto. Los comodines lo extienden para coincidir con patrones en lugar de texto exacto, lo que resuelve una gama más amplia de tareas de limpieza en una sola pasada.

Un asterisco sustituye a cualquier número de caracteres, incluidos ninguno. Buscar "Factura*" encuentra cada celda que comienza con la palabra Factura, sin importar lo que le siga, ya sea un número, una fecha o nada más. Esto es útil para encontrar y actualizar cada entrada que sigue un patrón de nombres sin necesidad de conocer cada valor individual de antemano.

Un signo de interrogación sustituye exactamente a un carácter, lo cual es más preciso que un asterisco cuando importa la longitud del patrón. Buscar "200?" encuentra entradas de cuatro caracteres que comienzan con 200, como 2001 a 2009, sin coincidir también con un número más largo como 20015 como lo haría un asterisco.

Ambos comodines funcionan dentro del cuadro de búsqueda Buscar y Reemplazar de forma predeterminada, sin necesidad de configurar nada especial primero. Esto es diferente de usar comodines dentro de una fórmula, como dentro de una condición COUNTIF o SUMIF, donde también funcionan pero solo dentro de funciones específicamente diseñadas para aceptarlos.

Un caso de uso común es limpiar el etiquetado inconsistente en una hoja grande, como reemplazar cada entrada que comienza con "Dept-" con un prefijo diferente. Otro es encontrar cada celda que contiene una palabra específica en medio de una cadena más larga que varía de fila en fila. Combinar comodines con Reemplazar Todo aplica la corrección en una hoja o libro completo en una sola acción, en lugar de editar cada celda coincidente individualmente.

Vale la pena ejecutar una búsqueda primero, sin reemplazar nada, para revisar cuántas celdas coinciden con el patrón antes de comprometerse a un reemplazo masivo. Esto toma unos segundos y evita que un patrón de comodín demasiado amplio cambie celdas que nunca debieron ser incluidas.

Credit: Canva Images

Un gráfico completo ocupa espacio real en una hoja, lo que se convierte en un problema en un informe con docenas de filas, cada una necesitando mostrar su propia tendencia al lado. Los minigráficos resuelven esto comprimiendo un gráfico para ajustarse dentro de una sola celda.

Encontrados en la pestaña Insertar, los minigráficos vienen en tres tipos. Un minigráfico de línea traza una tendencia simple a través de una fila de datos, útil para mostrar 12 meses de ventas de un solo producto sin un gráfico separado para cada uno. Un minigráfico de columnas muestra los mismos datos como un pequeño conjunto de barras verticales en lugar de una línea conectada. Esto es útil cuando los valores individuales importan más que la forma general de la tendencia. Un minigráfico de ganancia-pérdida reduce los datos aún más, mostrando solo si cada período fue positivo o negativo. Esto se adapta a algo como un chequeo mensual de ganancias y pérdidas en muchas líneas de productos a la vez.

Configurar uno significa seleccionar la celda donde debe aparecer el gráfico sparkline, elegir el tipo de gráfico sparkline desde la pestaña Insertar y luego seleccionar el rango de datos que debe resumir. El gráfico sparkline se sitúa dentro de esa única celda, lo suficientemente pequeño como para caber junto a una fila de otros datos en una tabla. Se redimensiona automáticamente si la altura de la fila o el ancho de la columna cambia posteriormente.

La principal ventaja sobre un gráfico completo es la densidad. Una tabla de resumen con 50 filas, cada una mostrando la tendencia de ventas mensuales de un producto como un sparkline en la columna final, cabe completamente en una pantalla. Construir 50 gráficos a tamaño completo para mostrar la misma información sería poco práctico y ocuparía mucho más espacio que los datos subyacentes.

Los sparklines no están diseñados para reemplazar un gráfico completo cuando importan los valores precisos, ya que no tienen etiquetas de eje o líneas de cuadrícula por defecto. Están hechos para un propósito diferente: mostrar la forma de una tendencia de un vistazo, a través de muchas filas a la vez, sin exigir mucha atención del lector.

Credit: Tanja Tepavac / Unsplash

Una lista construida durante meses, o reunida de múltiples fuentes, a menudo termina con la misma entrada registrada más de una vez. Eso podría ser un registro de cliente repetido, un número de orden duplicado o la misma dirección de correo electrónico ingresada dos veces bajo nombres ligeramente diferentes. Encontrarlas desplazándose manualmente por la lista es lento e ineficaz después de unas pocas docenas de filas.

La herramienta Eliminar Duplicados de Excel, que se encuentra en la pestaña Datos, gestiona esto en una sola pasada. Seleccionar el rango de datos y elegir Eliminar Duplicados abre un cuadro de diálogo que enumera cada columna en la selección. Las casillas de verificación controlan cuáles se revisan al decidir si dos filas cuentan como duplicados. Marcar solo la columna de dirección de correo electrónico, por ejemplo, trata dos filas como duplicados si comparten una dirección de correo electrónico, incluso si el nombre adjunto a cada una está escrito de manera diferente.

Una vez confirmado, Excel elimina cada fila duplicada que encuentra según las columnas seleccionadas e informa exactamente cuántas se eliminaron y cuántos valores únicos quedan. Este informe vale la pena leerlo antes de continuar. Un número inesperadamente alto de duplicados eliminados puede indicar un problema más profundo con la forma en que se recopilaron los datos, más que un simple error de entrada aislado.

Eliminar Duplicados es una acción destructiva: las filas eliminadas se borran de la hoja, no solo se ocultan. No hay una forma incorporada de revisar qué filas específicas se eliminaron después del hecho. Trabajar en una copia de los datos vale los pocos segundos adicionales que lleva. Lo mismo se aplica a asegurarse de que Ctrl+Z esté disponible para deshacer la acción si el resultado parece incorrecto.

A veces, la eliminación de duplicados necesita ser más selectiva, como mantener la más reciente de varias entradas duplicadas en lugar de una arbitraria. Ordenar los datos por fecha antes de ejecutar Eliminar Duplicados asegura que la fila que sobrevive sea la prevista. Excel mantiene la primera aparición que encuentra y elimina las que siguen.

Credit: Kindel Media / Pexels

La mayoría de las fórmulas de hoja de cálculo funcionan en una dirección: se introducen los datos y la fórmula calcula un resultado. Buscar Objetivo funciona en la dirección opuesta, comenzando desde un resultado deseado y calculando qué entrada lo produciría.

Encontrado bajo la pestaña Datos dentro de Análisis de hipótesis, Buscar Objetivo pide tres piezas de información. Estas son qué celda contiene la fórmula a resolver, qué valor debería igualar esa fórmula, y qué celda de entrada única debería cambiar para alcanzarlo. Excel luego prueba diferentes valores en esa celda de entrada, ajustando automáticamente hasta que el resultado de la fórmula coincide con el objetivo lo más cerca posible, e informa el valor de entrada en el que aterrizó.

Un caso de uso común es averiguar cuántas unidades deben venderse para alcanzar el punto de equilibrio. La forma antigua es adivinar una cifra de ventas, introducirla en una fórmula de ganancias y ajustar manualmente si el resultado está equivocado. Buscar Objetivo omite esa adivinanza. Dados los costos fijos y el precio por unidad incorporados en la hoja, toma una ganancia objetivo de cero y calcula la cantidad exacta de unidades necesarias para alcanzarlo.

Otro uso común es trabajar hacia atrás desde un pago de préstamo. Dado un monto de préstamo y un plazo, Buscar Objetivo puede calcular la tasa de interés que produciría un pago mensual específico. Eso supera probar manualmente diferentes tasas una a la vez dentro de una fórmula de pago hasta que los números coincidan.

Buscar Objetivo solo ajusta una celda de entrada a la vez, lo que conviene a una pregunta directa de una sola variable, pero no a un escenario donde varias entradas podrían necesitar cambiar juntas. Para ese tipo de problema de múltiples variables, el complemento Solver de Excel ofrece más flexibilidad. A diferencia de Buscar Objetivo, que está disponible por defecto en cada instalación estándar, Solver requiere ser habilitado primero a través del menú de Complementos.

Buscar Objetivo funciona probando valores hasta que converge en una respuesta, en lugar de resolver la fórmula algebraicamente. Debido a eso, ocasionalmente necesita un momento en una hoja muy grande o compleja. Para la mayoría de las preguntas de fórmula única, el resultado aparece casi de inmediato.