Excel avanzado es la forma de trabajar en la que la hoja hace el trabajo repetitivo por usted. Sin embargo, no consiste en memorizar más funciones. Por el contrario, consiste en ordenar los datos, escribir fórmulas que no se rompen y saber qué herramienta toca en cada caso. Por eso esta guía es material de consulta: reúne las fórmulas, las funciones y los trucos que más se usan en una oficina, con ejemplos que puede copiar. Además, parte de lo que se considera Excel intermedio y llega hasta las fórmulas de Excel avanzado, con un apartado para los errores de Excel y otro para los trucos de Excel.
Además, cada fórmula de esta página se ejecutó en un Excel real, en español, antes de publicarla. En particular, usamos Excel para Microsoft 365 (versión 16.0.20430) sobre Windows 11. Por eso los nombres de las funciones son los que Excel muestra en español, y los resultados son los que devolvió. También anotamos lo que falló, porque ahí están las lecciones más útiles.
Qué es Excel avanzado y qué abarca
Para empezar, no existe una definición oficial de qué es Excel avanzado ni de los demás niveles. Sin embargo, las ofertas de empleo y los temarios coinciden bastante. De hecho, la diferencia real no está en la cantidad de funciones, sino en lo que usted puede resolver sin ayuda.
| Nivel | Qué resuelve | Herramientas habituales |
|---|---|---|
| Básico | Registrar datos y hacer cuentas sencillas | SUMA, PROMEDIO, formato de celdas, ordenar y filtrar, gráficos simples |
| Excel intermedio | Resumir y cruzar información de varias columnas | SI, SUMAR.SI.CONJUNTO, BUSCARV o BUSCARX, tablas, tablas dinámicas, formato condicional, validación de datos |
| Excel avanzado | Construir modelos que se actualizan solos y que otra persona puede mantener | Matrices dinámicas, LET, LAMBDA, Power Query, modelo de datos, macros, control de errores y de rendimiento |
Por lo tanto, quien pregunta qué debe saber de Excel intermedio tiene una respuesta corta: condicionales, sumas con condición, búsquedas y tablas dinámicas. En cambio, Excel avanzado añade tres ideas. Primero, las fórmulas devuelven listas completas y no una sola celda. Segundo, los datos se limpian con pasos que se repiten solos. Tercero, el libro se diseña para que no dependa de quien lo creó.
A quién le sirve cada parte de esta guía
Si usted trabaja en una oficina y no es de sistemas, empiece por los apartados de condicionales, sumas y búsquedas. Además, son los que más tiempo ahorran. Si usted es analista o profesional de TI, le interesarán más las matrices dinámicas, LET y LAMBDA, la tabla de rendimiento y los nombres en inglés de cada función.
Los datos de los ejemplos de Excel
Todos los ejemplos usan el mismo conjunto de datos de nuestra guía de tablas en Excel: doce tickets de soporte de una empresa con cuatro sedes. Así puede comparar cada resultado con el suyo.
| Ticket | Fecha | Sede | Área | Categoría | Prioridad | Horas |
|---|---|---|---|---|---|---|
| TK-1001 | 2026-08-03 | Bogotá | Contabilidad | Software | Alta | 2,5 |
| TK-1002 | 2026-08-03 | Medellín | Comercial | Red | Crítica | 4,0 |
| TK-1003 | 2026-08-04 | Bogotá | Operaciones | Hardware | Media | 1,5 |
| TK-1004 | 2026-08-05 | Cali | Contabilidad | Accesos | Alta | 0,5 |
| TK-1005 | 2026-08-05 | Bogotá | Gerencia | Correo | Crítica | 3,0 |
| TK-1006 | 2026-08-06 | Barranquilla | Comercial | Software | Media | 2,0 |
| TK-1007 | 2026-08-07 | Medellín | Operaciones | Hardware | Alta | 5,5 |
| TK-1008 | 2026-08-10 | Bogotá | TI | Red | Crítica | 6,0 |
| TK-1009 | 2026-08-11 | Cali | Comercial | Accesos | Media | 1,0 |
| TK-1010 | 2026-08-12 | Bogotá | Contabilidad | Correo | Alta | 2,5 |
| TK-1011 | 2026-08-12 | Medellín | Gerencia | Software | Media | 3,5 |
| TK-1012 | 2026-08-13 | Barranquilla | Operaciones | Red | Alta | 4,5 |
Para seguir los ejemplos de Excel avanzado, tenga en cuenta que estos datos viven en una tabla llamada tblTickets. Además, hay una segunda tabla, tblSedes, con tres columnas: Sede, Responsable y Región. Por último, una celda con el nombre Tarifa guarda el valor de la hora: 75.000 pesos. En resumen, son 36,5 horas y 2.737.500 pesos.
Punto y coma o coma: el separador de argumentos
Antes de copiar nada, revise un detalle. En efecto, Excel separa los argumentos con punto y coma o con coma según la configuración regional de Windows. Con la configuración de Colombia, el decimal es la coma y el separador es el punto y coma. Por eso esta guía escribe =SI([@Horas]>2,5; "Sobre la media"; "En la media").
Sin embargo, si su equipo usa el punto como decimal, el separador será la coma. Nuestro equipo de pruebas estaba así, y Excel adaptó cada fórmula sin problema. Si una fórmula de esta guía le da error al pegarla, cambie cada punto y coma por una coma. De hecho, es la causa más frecuente.
Antes de las fórmulas de Excel avanzado: tablas y nombres
La mayoría de las fórmulas frágiles tienen el mismo origen: apuntan a rangos como A2:G13. Entonces, cuando alguien añade una fila, el rango se queda corto y nadie lo nota. En cambio, una tabla crece sola y sus columnas tienen nombre.
Por eso =SUMA(tblTickets[Horas]) siempre suma todas las filas, y devuelve 36,5. Dentro de la tabla, [@Horas] significa «las horas de esta fila». Además, puede dar nombre a una celda suelta desde Fórmulas > Administrador de nombres. Así, el costo de cada ticket se escribe de forma que cualquiera lo entiende:
=[@Horas]*TarifaPor ejemplo, en la primera fila devuelve 187.500. Además, el costo total, 2.737.500, sale de una fórmula igual de clara:
=SUMA(tblTickets[Horas])*TarifaCuando sí necesite un rango clásico, recuerde el signo de pesos. $G$2 fija la celda, $G2 fija solo la columna y G$2 fija solo la fila. Además, la tecla F4 alterna entre las cuatro opciones mientras escribe.
Condicionales en Excel: SI, SI.CONJUNTO, Y, O y CAMBIAR
La función SI es la puerta de entrada a Excel avanzado. Es decir, evalúa una condición y devuelve un valor u otro. Por ejemplo, para marcar los tickets largos:
=SI([@Horas]>4; "Larga"; "Normal")Como resultado, en nuestros datos marca tres tickets como «Larga»: TK-1007, TK-1008 y TK-1012. Ahora bien, casi siempre hay más de dos casos. La solución antigua era anidar una función SI dentro de otra:
=SI([@Horas]>4; "Larga"; SI([@Horas]>2; "Media"; "Corta"))Funciona, pero con cinco niveles ya nadie la lee. Por eso existe SI.CONJUNTO, que revisa las condiciones en orden y se queda con la primera que se cumple. Además, el VERDADERO final hace de «en cualquier otro caso»:
=SI.CONJUNTO([@Prioridad]="Crítica"; 1; [@Prioridad]="Alta"; 2; VERDADERO; 3)Cuando todas las condiciones comparan la misma celda con valores fijos, CAMBIAR es todavía más limpia. En este caso, el último argumento es el valor por defecto:
=CAMBIAR([@Prioridad]; "Crítica"; "P1"; "Alta"; "P2"; "Media"; "P3"; "Sin clasificar")Combinar condiciones con Y y con O
Por un lado, la función Y exige que se cumplan todas las condiciones. En cambio, O se conforma con una. Por ejemplo, esta fórmula marca solo los tickets críticos de más de cuatro horas, y en nuestros datos señala uno, el TK-1008:
=SI(Y([@Prioridad]="Crítica"; [@Horas]>4); "Revisar"; "")Y este otro clasifica las sedes en dos grupos:
=SI(O([@Sede]="Cali"; [@Sede]="Barranquilla"); "Regional"; "Principal")Sumar y contar con condiciones en Excel
Aquí está la mitad del trabajo diario de una oficina, y también una parte central de Excel avanzado. La familia «.SI.CONJUNTO» suma, cuenta o promedia solo las filas que cumplen una o varias condiciones. Además, el orden de los argumentos es siempre el mismo: primero lo que se suma y después las parejas de columna y condición.
=SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Sede]; "Bogotá")
=SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Sede]; "Bogotá"; tblTickets[Prioridad]; "Crítica")
=CONTAR.SI.CONJUNTO(tblTickets[Prioridad]; "Crítica")
=PROMEDIO.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Categoría]; "Red")
=MAX.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Sede]; "Medellín")La primera devuelve 15,5 horas en Bogotá. Después, la segunda devuelve 9 horas críticas en Bogotá. La tercera cuenta 3 tickets críticos. Además, la cuarta da 4,83 horas de promedio en los casos de red. Por último, la quinta devuelve 5,5 horas, que es el ticket más largo de Medellín.
Condiciones con operadores, fechas y comodines
Además, las condiciones admiten operadores entre comillas. Por ejemplo, ">4" cuenta los tickets de más de cuatro horas y "<>Bogotá" suma todo lo que no es de Bogotá:
=CONTAR.SI.CONJUNTO(tblTickets[Horas]; ">4")
=SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Sede]; "<>Bogotá")Como resultado, devuelven 3 y 21. Para un rango de fechas, una el operador con la función FECHA mediante el símbolo &. Por ejemplo, esta fórmula suma las horas del 10 al 14 de agosto y devuelve 17,5:
=SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Fecha]; ">="&FECHA(2026; 8; 10); tblTickets[Fecha]; "<="&FECHA(2026; 8; 14))Además, el asterisco sirve de comodín. "Soft*" encuentra todo lo que empieza por «Soft», y aquí suma 8 horas:
=SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Categoría]; "Soft*")SUMAPRODUCTO, SUBTOTALES y AGREGAR
SUMAPRODUCTO multiplica columnas enteras fila por fila. Por eso sirve cuando la condición necesita un cálculo que SUMAR.SI.CONJUNTO no admite. Por ejemplo, esta suma las horas de Bogotá en tickets de más de dos horas, y devuelve 14:
=SUMAPRODUCTO((tblTickets[Sede]="Bogotá")*(tblTickets[Horas]>2)*tblTickets[Horas])Ahora bien, es más lenta, como verá en el apartado de rendimiento. Por lo tanto, úsela solo cuando haga falta. Por otro lado, =SUBTOTALES(109; tblTickets[Horas]) suma únicamente las filas visibles tras un filtro. Y AGREGAR hace lo mismo, pero además ignora las celdas con error:
=AGREGAR(9; 6; tblTickets[Horas])Buscar datos en Excel: BUSCARX, INDICE con COINCIDIR y BUSCARV
Buscar un dato en otra tabla es la tarea que más dudas genera. Sin embargo, hay tres caminos, y los tres devuelven 6 al buscar las horas del ticket TK-1008:
=BUSCARX("TK-1008"; tblTickets[Ticket]; tblTickets[Horas])
=INDICE(tblTickets[Horas]; COINCIDIR("TK-1008"; tblTickets[Ticket]; 0))
=BUSCARV("TK-1008"; tblTickets; 7; FALSO)BUSCARX es la opción recomendada hoy. En primer lugar, busca en cualquier dirección, usa la coincidencia exacta por defecto y admite un texto para cuando no encuentra nada. Sin embargo, según la página de soporte de Microsoft, no está disponible en Excel 2016 ni en Excel 2019. Por eso, si comparte el archivo con alguien que usa esas versiones, INDICE con COINCIDIR hace lo mismo y funciona en todas.
El fallo silencioso de BUSCARV
BUSCARV tiene una trampa que comprobamos en la prueba. Si olvida el cuarto argumento, Excel asume una coincidencia aproximada y espera los datos ordenados. Ahora bien, nuestra tabla de sedes no está en orden alfabético. Este fue el resultado:
=BUSCARV("Cali"; tblSedes; 2)
=BUSCARV("Cali"; tblSedes; 2; FALSO)La primera devolvió «Carlos Vega», que es el responsable de Bogotá. Además, no mostró ningún error. En cambio, la segunda devolvió «Paula Rojas», que es la respuesta correcta. Es decir, una fórmula incompleta entrega un dato falso con total normalidad. Por eso conviene escribir siempre FALSO, o pasar a BUSCARX, que no tiene ese riesgo.
Lo que BUSCARX hace y BUSCARV no
En primer lugar, controla el caso «no existe». Sin el cuarto argumento, un ticket inexistente devuelve #N/D. En cambio, con él devuelve el texto que usted elija:
=BUSCARX("TK-9999"; tblTickets[Ticket]; tblTickets[Horas]; "no existe")En segundo lugar, puede buscar desde el final. Para ello, el último argumento, -1, recorre la lista de abajo hacia arriba. Así obtiene el último ticket de Bogotá, que es TK-1010:
=BUSCARX("Bogotá"; tblTickets[Sede]; tblTickets[Ticket]; ; 0; -1)Además, devuelve varias columnas a la vez. Por ejemplo, esta fórmula trae la sede, el área, la categoría, la prioridad y las horas del ticket en cinco celdas seguidas:
=BUSCARX("TK-1008"; tblTickets[Ticket]; tblTickets[[Sede]:[Horas]])Por último, resuelve la búsqueda con dos criterios, que es una de las dudas más repetidas. El truco es buscar el número 1 en el producto de las dos condiciones. De este modo, encuentra el ticket de Medellín con prioridad alta, TK-1007:
=BUSCARX(1; (tblTickets[Sede]="Medellín")*(tblTickets[Prioridad]="Alta"); tblTickets[Ticket]; "ninguno")Por último, dentro de una tabla, la búsqueda más común es traer un dato de otra. Por ejemplo, el responsable de la sede de cada ticket:
=BUSCARX([@Sede]; tblSedes[Sede]; tblSedes[Responsable]; "sin dato")Matrices dinámicas en Excel: una fórmula, muchas celdas
Este es el cambio más grande de Excel en años, y el corazón de Excel avanzado. Es decir, una fórmula ya no devuelve una sola celda. En cambio, devuelve una lista completa que «se desborda» hacia las celdas vecinas. Además, esa lista se actualiza sola cuando cambian los datos. Según Microsoft, estas funciones están en Microsoft 365 y en Excel 2021 y 2024.
FILTRAR, ORDENAR y UNICOS
FILTRAR devuelve las filas que cumplen una condición. Además, el tercer argumento es el texto que aparece si no hay ninguna. Por ejemplo, esta fórmula devuelve los tres tickets críticos, con sus siete columnas:
=FILTRAR(tblTickets; tblTickets[Prioridad]="Crítica"; "sin resultados")Para combinar condiciones, multiplíquelas si deben cumplirse todas. En cambio, súmelas si basta con una. Así, la primera fórmula devuelve TK-1005 y TK-1008. En cambio, la segunda devuelve los cuatro tickets de Cali y Barranquilla:
=FILTRAR(tblTickets[Ticket]; (tblTickets[Sede]="Bogotá")*(tblTickets[Prioridad]="Crítica"))
=FILTRAR(tblTickets[Ticket]; (tblTickets[Sede]="Cali")+(tblTickets[Sede]="Barranquilla"))UNICOS quita los repetidos y ORDENAR pone orden. Por eso, juntas producen una lista limpia de áreas: Comercial, Contabilidad, Gerencia, Operaciones y TI.
=ORDENAR(UNICOS(tblTickets[Área]))Por otro lado, ORDENARPOR ordena una columna según los valores de otra, y TOMAR se queda con las primeras filas. Así se obtienen los tres tickets más largos, que son TK-1008, TK-1007 y TK-1012:
=TOMAR(ORDENARPOR(tblTickets[[Ticket]:[Horas]]; tblTickets[Horas]; -1); 3)El signo # y los resúmenes sin tabla dinámica
Suponga que escribe =UNICOS(tblTickets[Sede]) en la celda J2. Entonces Excel llena J2 a J5 con las cuatro sedes. Para referirse a toda esa lista, escriba J2#. Es decir, el signo significa «todo lo que desbordó esta celda». Por eso la siguiente fórmula, en K2, calcula las horas de cada sede y crece sola si aparece una sede nueva:
=SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Sede]; J2#)Como resultado, devuelve 15,5 para Bogotá, 13 para Medellín, 1,5 para Cali y 6,5 para Barranquilla. Sin embargo, las versiones recientes de Microsoft 365 lo resuelven en un solo paso con AGRUPARPOR. En nuestra prueba devolvió las cuatro sedes en orden alfabético y una fila de total con 36,5:
=AGRUPARPOR(tblTickets[Sede]; tblTickets[Horas]; SUMA)Además, PIVOTARPOR cruza dos campos, igual que una tabla dinámica. Por ejemplo, esta fórmula pone las sedes en filas y las prioridades en columnas, con sus totales:
=PIVOTARPOR(tblTickets[Sede]; tblTickets[Prioridad]; tblTickets[Horas]; SUMA)La diferencia con una tabla dinámica es que la fórmula se actualiza al instante, sin pulsar «Actualizar». A cambio, no tiene segmentaciones ni agrupación de fechas. Por último, tenga en cuenta que AGRUPARPOR y PIVOTARPOR son recientes: compruebe que existen en su versión antes de repartir el archivo.
Elegir, apilar y contar
Otras tres funciones completan la caja de herramientas. En primer lugar, ELEGIRCOLS se queda con las columnas que usted indique. Después, APILARV pone un bloque debajo de otro, y APILARH los pone lado a lado. Por ejemplo, esta fórmula muestra solo el ticket, la sede y las horas de los casos largos:
=ELEGIRCOLS(FILTRAR(tblTickets; tblTickets[Horas]>4); 1; 3; 7)Y para contar valores distintos, combine CONTARA con UNICOS. Aquí devuelve 5 áreas:
=CONTARA(UNICOS(tblTickets[Área]))Texto en Excel: separar, unir y limpiar
Los datos que llegan de otro sistema casi nunca vienen limpios. Por eso las funciones de texto son parte de cualquier temario de Excel avanzado. Además, las más nuevas ahorran las combinaciones enrevesadas de IZQUIERDA, EXTRAE y ENCONTRAR.
| Necesidad | Fórmula | Resultado |
|---|---|---|
| Lo que hay antes de un separador | =TEXTOANTES("TK-1008"; "-") | TK |
| El número del ticket, como número | =VALOR(TEXTODESPUES([@Ticket]; "-")) | 1001 |
| El dominio de un correo | =TEXTODESPUES("ana.gomez@ejemplo.com"; "@") | ejemplo.com |
| Partir un texto en columnas | =DIVIDIRTEXTO("Bogotá/Cali/Medellín"; "/") | Tres celdas |
| Unir una lista con comas | =UNIRCADENAS(", "; VERDADERO; UNICOS(tblTickets[Sede])) | Bogotá, Medellín, Cali, Barranquilla |
| Quitar espacios sobrantes | =ESPACIOS(" Bogotá D.C. ") | Bogotá D.C. |
| Mayúscula inicial en cada palabra | =NOMPROPIO("ana maría gómez") | Ana María Gómez |
| Quitar un prefijo | =SUSTITUIR("TK-1008"; "TK-"; "") | 1008 |
| Comparar con mayúsculas exactas | =IGUAL("Bogotá"; "bogotá") | FALSO |
Según Microsoft, TEXTOANTES, TEXTODESPUES y DIVIDIRTEXTO están en Microsoft 365 y en Excel 2024. En cambio, en versiones anteriores use Datos > Texto en columnas o el relleno rápido.
Expresiones regulares en Excel
Microsoft 365 incorporó además tres funciones para patrones de texto. Sobre todo, sirven para validar formatos y para extraer códigos de un texto libre. En nuestro Excel en español se llaman así:
=REGEXEXTRACCION("Caso TK-1008 cerrado"; "TK-\d+")
=REGEXPRUEBA("TK-1008"; "^TK-\d{4}$")La primera devuelve «TK-1008». La segunda devuelve VERDADERO, porque el texto cumple el formato «TK-» seguido de cuatro cifras. Sin embargo, según Microsoft solo existen en Microsoft 365.
La trampa de la función TEXTO en español
TEXTO convierte un número o una fecha en texto con el formato que usted pida. Aquí encontramos el fallo más curioso de toda la prueba. De hecho, los códigos de formato dependen del idioma de Excel. Así, en español el año se escribe con la letra «a», no con «y».
=TEXTO(FECHA(2026; 8; 3); "dd/mm/yyyy")
=TEXTO(FECHA(2026; 8; 3); "dd/mm/aaaa")La primera devolvió «03/08/yyyy», sin ningún aviso. En cambio, la segunda devolvió «03/08/2026». Por lo tanto, una fórmula copiada de un foro en inglés puede dejar la palabra «yyyy» en sus informes. En cambio, los códigos de día y de mes sí coinciden. Por ejemplo, =TEXTO(FECHA(2026; 8; 3); "mmmm aaaa") devuelve «agosto 2026».
Además, hay un segundo detalle. Si une una fecha con un texto, Excel muestra el número interno de la fecha. Por ejemplo, en nuestra prueba el ticket del 3 de agosto apareció como «46237». Por lo tanto, la solución es envolver la fecha con TEXTO:
=[@Ticket]&" · "&TEXTO([@Fecha]; "dd/mm/aaaa")Fechas y días hábiles en Excel
El manejo de fechas es otro bloque clásico de Excel avanzado. Excel guarda cada fecha como un número, y por eso puede restarlas. La resta =FECHA(2026; 8; 13)-FECHA(2026; 8; 3) devuelve 10 días. Sin embargo, en una oficina interesan los días hábiles. Para eso, DIAS.LAB los cuenta sin sábados ni domingos, e incluye el primero y el último:
=DIAS.LAB(FECHA(2026; 8; 3); FECHA(2026; 8; 13))
=DIAS.LAB(FECHA(2026; 8; 3); FECHA(2026; 8; 13); FECHA(2026; 8; 7))La primera devuelve 9. La segunda devuelve 8, porque el tercer argumento descuenta el festivo del 7 de agosto. Ahora bien, Excel no conoce los festivos de ningún país. Por eso usted debe darle la lista, que lo normal es guardar en una tabla aparte.
| Necesidad | Fórmula | Resultado |
|---|---|---|
| Fecha tras cinco días hábiles | =DIA.LAB(FECHA(2026; 8; 3); 5) | 10 de agosto |
| Lo mismo, con el festivo | =DIA.LAB(FECHA(2026; 8; 3); 5; FECHA(2026; 8; 7)) | 11 de agosto |
| Último día del mes | =FIN.MES(FECHA(2026; 8; 3); 0) | 31 de agosto |
| La misma fecha tres meses después | =FECHA.MES(FECHA(2026; 8; 3); 3) | 3 de noviembre |
| Años completos entre dos fechas | =SIFECHA(FECHA(2020; 3; 15); FECHA(2026; 8; 3); "y") | 6 |
| Día de la semana, con lunes = 1 | =DIASEM([@Fecha]; 2) | 1 |
| Número de semana ISO | =ISO.NUM.DE.SEMANA([@Fecha]) | 32 |
| Nombre del día | =TEXTO([@Fecha]; "dddd") | lunes |
Un aviso sobre SIFECHA: funciona, pero Excel no la sugiere mientras usted escribe. Por eso mucha gente cree que «no aparece». Por lo tanto, escríbala completa y a mano. Además, sus códigos de unidad van en inglés incluso en Excel en español: "y" para años y "ym" para los meses sobrantes.
Si DIA.LAB, FIN.MES o FECHA.MES devuelven un número en lugar de una fecha, no hay error. En ese caso, solo falta aplicar formato de fecha a la celda con Ctrl+1.
LET y LAMBDA en Excel avanzado: fórmulas que se leen y se reutilizan
Estas dos funciones separan Excel intermedio de Excel avanzado. Por un lado, LET pone nombre a los pasos intermedios de una fórmula. Así, cada cálculo se escribe una sola vez y la fórmula se lee de arriba abajo. Por ejemplo, esta fórmula calcula qué parte de las horas corresponde a tickets críticos:
=LET(h; tblTickets[Horas]; crit; FILTRAR(h; tblTickets[Prioridad]="Crítica"); SUMA(crit)/SUMA(h))Devuelve 0,356, es decir, el 35,6 %. Además, LET permite construir un informe entero en una celda. La siguiente fórmula lista las sedes con sus horas y su porcentaje, ordenadas de mayor a menor:
=LET(sedes; UNICOS(tblTickets[Sede]); horas; SUMAR.SI.CONJUNTO(tblTickets[Horas]; tblTickets[Sede]; sedes); ORDENAR(APILARH(sedes; horas; horas/SUMA(horas)); 2; -1))Como resultado, la lista empieza por Bogotá, con 15,5 horas y el 42,5 %. Después vienen Medellín con el 35,6 %, Barranquilla con el 17,8 % y Cali con el 4,1 %.
LAMBDA: su propia función, sin macros
LAMBDA convierte una fórmula en una función con nombre. Primero se define en Fórmulas > Administrador de nombres. Por ejemplo, cree el nombre COSTO con este contenido: =LAMBDA(horas; horas*Tarifa). A partir de ahí, cualquier persona del equipo puede escribir:
=COSTO([@Horas])Devuelve 187.500 en la primera fila, igual que la multiplicación directa. Sin embargo, la ventaja aparece cuando la regla cambia. Entonces usted corrige la definición una vez y todas las celdas se actualizan. Además, el archivo sigue siendo un .xlsx normal, sin macros ni avisos de seguridad. Según Microsoft, LAMBDA está en Microsoft 365 y en Excel 2024.
Por último, LAMBDA tiene funciones auxiliares. MAP aplica un cálculo a cada elemento de una lista, y BYROW lo aplica a cada fila:
=MAP(tblTickets[Horas]; LAMBDA(h; SI(h>4; "Larga"; "Normal")))Formato condicional y validación con fórmulas de Excel
El formato condicional y la validación de datos aceptan fórmulas. Ahí es donde dejan de ser decoración. Además, la regla es sencilla: escriba la fórmula pensando en la primera fila del rango y fije la columna con el signo de pesos. Después, Excel la aplica al resto.
| Objetivo | Dónde | Fórmula |
|---|---|---|
| Resaltar las filas por encima del promedio | Formato condicional | =$G2>PROMEDIO($G$2:$G$13) |
| Resaltar toda la fila de un ticket crítico | Formato condicional | =$F2="Crítica" |
| Marcar tickets duplicados | Formato condicional | =CONTAR.SI($A$2:$A$13; $A2)>1 |
| Aceptar solo horas entre 0 y 12 | Validación, Personalizada | =Y(ESNUMERO(G2); G2>0; G2<=12) |
| Lista desplegable con las sedes de una tabla | Validación, Lista | =INDIRECTO("tblSedes[Sede]") |
La última fila resuelve una limitación que comprobamos. Al crear la validación por automatización, Excel rechazó como origen el nombre directo de la columna de la tabla. En cambio, aceptó la versión con INDIRECTO, que devuelve las cuatro sedes. Además, así la lista crece sola cuando se añade una sede.
Qué herramienta de Excel usar en cada caso
Sin embargo, saber fórmulas no basta. La decisión que más pesa en Excel avanzado es elegir bien la herramienta. De hecho, muchas hojas lentas y frágiles nacen de resolver con fórmulas lo que pedía otra cosa.
| Situación | Herramienta | Por qué |
|---|---|---|
| Un cálculo por fila o un indicador suelto | Fórmulas en una tabla | Se ven, se revisan y se actualizan al instante |
| Resumir miles de filas por categoría, mes o sede | Tabla dinámica | Agrupa fechas, filtra con segmentaciones y no escribe ni una fórmula |
| Limpiar un archivo que llega igual cada semana | Power Query | Graba los pasos una vez y los repite con «Actualizar» |
| Cruzar varias tablas grandes | Modelo de datos o Power BI | Relaciona tablas sin miles de búsquedas |
| Repetir una secuencia de clics | Macro u Office Scripts | Automatiza acciones, no cálculos |
| Compartir tableros con muchas personas | Power BI | Controla quién ve qué y se actualiza de forma programada |
Cada una tiene su guía en este sitio: tablas dinámicas, Power Query, macros en Excel y Power BI. Como regla práctica, si usted copia y pega los mismos datos cada lunes, necesita Power Query. Y si su libro tarda en abrir, probablemente tiene fórmulas haciendo el trabajo de una tabla dinámica.
Errores de Excel: qué significa cada uno y cómo resolverlo
Los errores de Excel no son fallos del programa. Por el contrario, son mensajes, y cada uno señala una causa concreta. Por eso provocamos todos en la prueba para anotar el texto exacto que muestra Excel en español.
| Error | Qué significa | Cómo lo provocamos | Solución habitual |
|---|---|---|---|
#N/D | La búsqueda no encontró el valor | Buscar un ticket que no existe | Revisar espacios y formatos, o usar el cuarto argumento de BUSCARX |
#¡DIV/0! | División entre cero o entre una celda vacía | =1/0 | Comprobar el divisor con SI, o usar SI.ERROR |
#¡VALOR! | Un texto donde debía ir un número | ="a"+1 | Convertir el texto en número o corregir la referencia |
#¿NOMBRE? | Excel no reconoce una función o un nombre | Una función inexistente | Revisar la ortografía, el idioma y la versión de Excel |
#¡REF! | La fórmula apunta a algo que ya no existe | Referencia a una hoja inexistente | Deshacer el borrado o rehacer la referencia |
#¡NUM! | Un cálculo imposible | =RAIZ(-1) | Revisar los valores de entrada |
#¡DESBORDAMIENTO! | Una matriz dinámica no tiene sitio para extenderse | Una celda ocupada debajo de la fórmula | Vaciar las celdas que estorban |
#CALC! | Una matriz quedó vacía | FILTRAR sin resultados y sin tercer argumento | Añadir el texto de «sin resultados» |
#¡NULO! | Dos rangos separados por un espacio que no se cruzan | Un espacio en lugar de punto y coma | Corregir el separador |
#### | No es un error: la columna es estrecha | Un número largo en una columna de ancho 3 | Ensanchar la columna |
SI.ERROR y SI.ND no son lo mismo
SI.ERROR oculta cualquier error. Por eso es cómoda y peligrosa a la vez. Por ejemplo, si la usa para tapar un #N/D, también tapará un #¡REF! que avisaba de un problema real. En cambio, SI.ND solo actúa cuando la búsqueda no encuentra el valor. Lo comprobamos así:
=SI.ND(BUSCARX("x"; tblTickets[Ticket]; tblTickets[Horas]); "no está")
=SI.ND(1/0; "no está")La primera devolvió «no está». En cambio, la segunda siguió mostrando #¡DIV/0!, que es justo lo que se quiere. Por lo tanto, en las búsquedas use SI.ND o el cuarto argumento de BUSCARX. Además, reserve SI.ERROR para los casos en los que de verdad da igual el motivo.
Además, tenga presente el caso de la función COPILOT. Según la página de soporte de Microsoft, esa función dejó de estar disponible el 14 de septiembre de 2026. De hecho, en nuestra prueba, escribirla devolvió #¿NOMBRE?. Por lo tanto, si heredó un libro que la usa, verá ese error al recalcular.
Rendimiento de Excel: lo que medimos con 100.000 filas
Las fórmulas de Excel avanzado funcionan igual con doce filas que con cien mil. En cambio, la velocidad no. Para comprobarlo, generamos 100.000 filas con una clave de texto en orden aleatorio. Después lanzamos 10.000 búsquedas y cronometramos tres recálculos completos de cada variante. El equipo era un portátil con un procesador Intel Core i9 de 20 hilos.
| Variante | Tiempo por recálculo |
|---|---|
| BUSCARV exacta sobre datos sin ordenar | Entre 3,6 y 27,9 s |
| INDICE con COINCIDIR exacta | Entre 10,3 y 11,7 s |
| BUSCARX exacta | Entre 12,8 y 66,1 s |
| BUSCARX con búsqueda binaria sobre datos ordenados | Entre 0,05 y 0,07 s |
| 1.000 celdas con SUMAR.SI.CONJUNTO de dos condiciones | Entre 4,9 y 24,1 s |
| 1.000 celdas con SUMAPRODUCTO equivalente | Entre 32,2 y 41,9 s |
Los tiempos variaron mucho entre pasadas, así que no sirven para decir cuál de las tres búsquedas exactas es más rápida. Sin embargo, dos conclusiones sí son firmes. Primero, la búsqueda binaria sobre datos ordenados tardó centésimas de segundo, frente a varios segundos de cualquier búsqueda exacta. Se activa con el último argumento de BUSCARX en 2 y exige que la columna esté ordenada. Segundo, SUMAPRODUCTO fue más lenta que SUMAR.SI.CONJUNTO en las tres pasadas.
Por otro lado, guardamos ese mismo libro en dos formatos. El archivo .xlsx pesó 4,4 MB y tardó 2,6 segundos en guardarse. En cambio, el .xlsb pesó 2,7 MB y tardó 0,9 segundos. Por lo tanto, para libros grandes de uso interno, el formato binario ahorra espacio y tiempo. A cambio, otros programas lo leen peor.
Los límites de una hoja de Excel
Según las especificaciones de Excel, una hoja admite 1.048.576 filas y 16.384 columnas, y nuestro Excel confirmó esas cifras. Además, una celda guarda hasta 32.767 caracteres y una fórmula admite 8.192. Ahora bien, si sus datos se acercan al millón de filas, la hoja ya no es el lugar adecuado. En ese caso, cárguelos con Power Query al modelo de datos o llévelos a Power BI.
Trucos de Excel que ahorran tiempo
Estos trucos de Excel no necesitan fórmulas. Sin embargo, quien los domina trabaja con mucha más soltura, y forman parte de Excel avanzado tanto como las fórmulas. Además, los atajos son los que documenta Microsoft para Excel en Windows con teclado en español.
| Acción | Atajo |
|---|---|
| Editar la celda activa | F2 |
| Alternar entre referencia relativa y absoluta | F4 |
| Recalcular todos los libros abiertos | F9 |
| Convertir un rango en tabla | Ctrl+T |
| Relleno rápido | Ctrl+Mayús+E |
| Abrir Formato de celdas | Ctrl+1 |
| Pegado especial | Ctrl+Alt+V |
| Salto de línea dentro de una celda | Alt+Entrar |
| Escribir lo mismo en todas las celdas seleccionadas | Ctrl+Entrar |
| Ir al borde de los datos | Ctrl+flecha |
| Seleccionar hasta el borde de los datos | Ctrl+Mayús+flecha |
| Seleccionar la columna o la fila entera | Ctrl+Espacio o Mayús+Espacio |
| Insertar la fecha de hoy como valor fijo | Ctrl+punto y coma |
| Cambiar de hoja | Ctrl+Av Pág y Ctrl+Re Pág |
| Pegar un nombre definido en la fórmula | F3 |
Además de los atajos, hay cinco trucos de Excel que conviene conocer:
- Evalúe una parte de la fórmula. Seleccione un trozo en la barra de fórmulas y pulse F9 para ver su resultado. Después pulse Esc para no dejarlo fijo.
- Use «Evaluar fórmula». Está en la pestaña Fórmulas y ejecuta el cálculo paso a paso. Es la mejor forma de entender una fórmula ajena.
- Rastree las dependencias. «Rastrear precedentes» dibuja flechas hacia las celdas que alimentan un resultado.
- Pegue valores antes de enviar. Con el pegado especial, un informe deja de depender de otros libros.
- Deje el relleno rápido para tareas de una vez. Aprende un patrón de sus ejemplos, pero no se actualiza. Si los datos cambian, use una fórmula.
Copilot en Excel: qué cambia y qué no
Excel incluye hoy un asistente de inteligencia artificial. Según la documentación de Microsoft, Copilot en Excel puede generar fórmulas, crear gráficos y tablas dinámicas, aplicar formato condicional y responder preguntas sobre los datos. Sin embargo, la misma página advierte que puede no estar incluido en su suscripción a Microsoft 365, o que su organización puede haberlo restringido.
Por lo tanto, Copilot no sustituye lo que explica esta guía de Excel avanzado. En cambio, lo acelera. Por ejemplo, quien conoce SUMAR.SI.CONJUNTO puede revisar en segundos la fórmula que el asistente propone. En cambio, quien no la conoce no tiene forma de saber si el resultado es correcto. Además, recuerde el caso de BUSCARV: una fórmula puede devolver un dato falso sin mostrar ningún error. Por eso, revisar sigue siendo trabajo de una persona.
Recomendaciones de Excel avanzado para libros que otros puedan mantener
Excel avanzado también es disciplina. Por eso estas prácticas distinguen un libro profesional de uno que solo entiende su autor.
- Una tabla por tema y una fila por registro. Sin celdas combinadas, sin filas de subtotales en medio y sin títulos dentro de los datos.
- Separe datos, cálculos y presentación. Lo ideal es una hoja para cada cosa. Así nadie escribe encima de una fórmula.
- Ponga nombre a las tablas y a las constantes.
Tarifase entiende;$B$1no. - No escriba números fijos dentro de las fórmulas. Una tarifa o un porcentaje van en una celda con nombre, donde se puedan cambiar.
- Controle la entrada con validación de datos. Es más barato impedir «Bogota» sin tilde que corregirlo en cada informe.
- Evite las funciones volátiles sin necesidad. HOY, AHORA, INDIRECTO y DESREF se recalculan con cada cambio y frenan los libros grandes.
- Documente lo que no es evidente. Una hoja «Léame» con el origen de los datos y la fecha de actualización ahorra muchas preguntas.
- Guarde el libro en OneDrive o SharePoint. Así hay historial de versiones y varias personas pueden editar a la vez, sin copias con «final» y «final2» en el nombre.
- Proteger una hoja no es seguridad. La protección evita errores por descuido. No impide que alguien con el archivo lea los datos.
Escenarios de Excel avanzado por área
Las mismas funciones de Excel avanzado resuelven problemas distintos según el área. Por eso esta tabla le ayuda a encontrar por dónde empezar.
| Área | Problema típico | Qué usar |
|---|---|---|
| Finanzas y contabilidad | Conciliar dos listados de movimientos | BUSCARX con SI.ND, y FILTRAR para ver lo que no cruza |
| Recursos humanos | Antigüedad y vencimiento de contratos | SIFECHA, FECHA.MES y formato condicional con fechas |
| Ventas | Ventas por vendedor, mes y línea | Tabla dinámica, o AGRUPARPOR y PIVOTARPOR |
| Compras e inventario | Productos por debajo del mínimo | SI con Y, formato condicional y validación de datos |
| Operaciones | Días hábiles de entrega con festivos | DIAS.LAB y DIA.LAB con una tabla de festivos |
| TI y soporte | Horas por sede, prioridad y categoría | SUMAR.SI.CONJUNTO, LET y Power Query para los datos exportados |
| Gerencia | Un indicador que se actualice solo | Tablas, matrices dinámicas y un gráfico vinculado |
Nombres de las funciones de Excel en inglés
Mucha ayuda de internet está en inglés, y Excel no acepta el nombre inglés si está en español. Por eso estas equivalencias las tradujo el propio Excel durante la prueba.
| Español | Inglés | Español | Inglés |
|---|---|---|---|
| SI | IF | SI.CONJUNTO | IFS |
| Y / O | AND / OR | CAMBIAR | SWITCH |
| SI.ERROR | IFERROR | SI.ND | IFNA |
| SUMAR.SI.CONJUNTO | SUMIFS | CONTAR.SI.CONJUNTO | COUNTIFS |
| PROMEDIO.SI.CONJUNTO | AVERAGEIFS | MAX.SI.CONJUNTO | MAXIFS |
| SUMAPRODUCTO | SUMPRODUCT | SUBTOTALES | SUBTOTAL |
| AGREGAR | AGGREGATE | REDONDEAR | ROUND |
| BUSCARX | XLOOKUP | BUSCARV | VLOOKUP |
| INDICE | INDEX | COINCIDIR | MATCH |
| COINCIDIRX | XMATCH | INDIRECTO | INDIRECT |
| FILTRAR | FILTER | ORDENAR | SORT |
| ORDENARPOR | SORTBY | UNICOS | UNIQUE |
| SECUENCIA | SEQUENCE | TOMAR | TAKE |
| ELEGIRCOLS | CHOOSECOLS | APILARV / APILARH | VSTACK / HSTACK |
| AGRUPARPOR | GROUPBY | PIVOTARPOR | PIVOTBY |
| TEXTOANTES | TEXTBEFORE | TEXTODESPUES | TEXTAFTER |
| DIVIDIRTEXTO | TEXTSPLIT | UNIRCADENAS | TEXTJOIN |
| ESPACIOS | TRIM | NOMPROPIO | PROPER |
| EXTRAE | MID | SUSTITUIR | SUBSTITUTE |
| LARGO | LEN | TEXTO | TEXT |
| DIAS.LAB | NETWORKDAYS | DIA.LAB | WORKDAY |
| FIN.MES | EOMONTH | FECHA.MES | EDATE |
| SIFECHA | DATEDIF | DIASEM | WEEKDAY |
| HOY / AHORA | TODAY / NOW | CONTARA | COUNTA |
LET, LAMBDA, MAP, BYROW, CONCAT, MAX y MIN se escriben igual en los dos idiomas.
Preguntas frecuentes sobre Excel avanzado
¿Cuánto tiempo toma aprender Excel avanzado?
Depende del punto de partida. Sin embargo, el orden importa más que las horas. Primero tablas y referencias. Después condicionales, sumas con condición y búsquedas. Luego tablas dinámicas. Por último, matrices dinámicas, LET y Power Query. Además, aprenda cada tema con un problema real de su trabajo.
¿Qué versión necesito para estas fórmulas?
Todo lo de esta guía funciona en Microsoft 365. Las matrices dinámicas y BUSCARX también están en Excel 2021 y 2024. En cambio, Excel 2016 y 2019 no las tienen. Por último, las funciones de texto nuevas y LAMBDA requieren Excel 2024 o Microsoft 365.
¿Por qué mi fórmula muestra #¿NOMBRE? en otro equipo?
Casi siempre porque ese equipo tiene una versión anterior, sin esa función. El archivo se abre, pero la fórmula no se puede recalcular. Por eso, antes de compartir un libro, confirme qué versión usa quien lo recibe.
¿Excel o Power BI?
Excel es mejor para analizar, probar y calcular. En cambio, Power BI es mejor para publicar tableros que consultan muchas personas. De hecho, se complementan: comparten Power Query y el modelo de datos.
¿Las fórmulas en español funcionan en un Excel en inglés?
Sí, porque Excel guarda las funciones con un código interno y las muestra en el idioma de cada equipo. Sin embargo, lo que no se traduce es el texto entre comillas, como los códigos de formato de la función TEXTO.
Cómo lo aborda KHARONTE
Excel avanzado rinde cuando la plataforma acompaña. Por ejemplo, una misma oficina con Excel 2016 en unos equipos y Microsoft 365 en otros acaba con libros que muestran errores según quién los abra. Por eso el primer paso no es un curso: es tener a todos en la misma versión, con los archivos en un lugar compartido y con respaldo.
En KHARONTE no dictamos cursos de Excel ni construimos sus hojas de cálculo. En cambio, nos ocupamos de lo que hay debajo. Es decir, suministramos y administramos las licencias de Microsoft 365 para empresas, gestionamos usuarios y accesos, y mantenemos OneDrive y SharePoint en orden. Además, nuestra mesa de ayuda TI atiende los casos del día a día: el Excel que no abre, el complemento que falta o el archivo que alguien sobrescribió.
Todo esto forma parte de nuestros servicios administrados de TI, que canalizamos por una mesa de servicio con registro de cada caso. Si quiere ver el alcance completo, consulte los servicios de TI para empresas que prestamos.
Hablemos de la TI de su empresa
Cuéntenos qué necesita su operación y le enviamos una propuesta por escrito. Cada línea del portafolio se contrata por separado.
