Convertirte en experto de Excel: qué dominios hay que controlar

Ser experto en Excel significa manejar con soltura las herramientas que automatizan y modelan datos: Power Query, Solver, BUSCARX, tablas dinámicas con segmentaciones y macros grabadas. Este tutorial recorre las cinco prácticas que resumen ese nivel sobre un mismo libro de ventas: una tabla dinámica con segmentación, un cruce con BUSCARX y SUMAR.SI.CONJUNTO, una consolidación con Power Query, un reparto optimizado con Solver y una macro que formatea facturas. Al terminar sabrás decidir si ya estás en nivel experto o qué te falta, y tendrás el contenido justo para presentarte al examen MO-211 de Microsoft. Las funciones nuevas (BUSCARX, matrices dinámicas) están en Excel 2021, 2024 y Microsoft 365; Power Query está integrado desde Excel 2016.

Qué significa ser experto en Excel y qué cubre este nivel

Llegas a este nivel cuando ya cierras libros con BUSCARV y tablas dinámicas y lo siguiente que necesitas es automatizar tareas repetitivas, modelar escenarios y cruzar tablas que antes daban error. El nivel experto se apoya en cuatro bloques:

• Datos: traer, limpiar y combinar hojas con Power Query, en lugar de copiar y pegar a mano.
• Fórmulas: BUSCARX y las matrices dinámicas (DESREF, UNICOS, ORDENAR) que sustituyen los trucos antiguos.
• Automatización: macros grabadas, VBA básico y controles de formulario para que un clic haga lo que antes eran cinco.
• Gráficos y tablas avanzados: tablas dinámicas con segmentaciones y escalas de tiempo, Solver y escenarios para responder preguntas del tipo "qué pasaría si".

Las herramientas que dominarás en el nivel experto

• Power Query: trae, limpia y combina hojas sin tocar las fórmulas. Sustituye al copiar y pegar de toda la vida.
• Solver: busca la mejor combinación de variables que cumpla unas condiciones (máximo beneficio, mínimo coste).
• BUSCARX (XLOOKUP): busca un valor en otra tabla y trae lo que pidas, hacia cualquier lado, sin el truco del SI.ERROR.
• Matrices dinámicas: funciones como DESREF, UNICOS, FILTRAR o ORDENAR que devuelven varios resultados a la vez.
• Tablas dinámicas con segmentaciones y escalas de tiempo: filtran al instante por categoría y por texto.
• Macros grabadas y VBA básico: graba tus clics y asócialos a un botón; si necesitas más, el editor VBA te permite leer y modificar lo grabado.
• Controles de formulario: botones, casillas y listas que disparan macros sin escribir una línea de código.
• Escenarios y Buscar objetivo: comparan variantes o fuerzan un resultado y ven qué variable hay que cambiar.

Certificación Microsoft Office Specialist Excel Expert (MO-211)

El examen MO-211 es la certificación oficial de Microsoft que avala el nivel experto en Excel para escritorio. Está disponible en español y en inglés, se rinde en un centro autorizado o en línea bajo supervisión, y al aprobarlo recibes un diploma que las empresas reconocen internationally. Demuestra que dominas la gestión de libros, el análisis de datos con fórmulas y tablas dinámicas, la automatización con macros y la creación de gráficos avanzados.

Microsoft recomienda unas 150 horas de práctica con Excel antes de presentarte. La certificación caduca a los 60 meses y se renueva volviendo a aprobar el examen vigente. Las funciones y herramientas que evalúa coinciden con las que cubre este tutorial.

Temas que evalúa el examen MO-211

De Excel básico a experto: el camino de aprendizaje

• Básico: sumas, restas, referencias y formato de celdas. Escribes =SUMA(A1:A10) y entiendes por qué la barra de fórmulas muestra la fórmula y la celda el resultado.
• Intermedio: tablas dinámicas sencillas, BUSCARV, formato condicional y gráficos de barras. Cierras tu primer informe mensual sin copiar datos a mano.
• Avanzado: SUMAR.SI.CONJUNTO, INDICE + COINCIDIR, tablas dinámicas con segmentaciones, grabación de macros y Power Query para limpiar hojas.
• Experto: Power Query consolidando hojas, BUSCARX con manejo de errores, Solver para optimizar, macros asignadas a botones y VBA básico. Aquí ya te presentas al MO-211 o automatizas el trabajo de tu equipo.

Tu primera tabla dinámica con segmentación y escala de tiempo

Para esta práctica vas a usar el libro Ventas2026, una hoja con 60 filas de ventas de enero a junio con cinco columnas: Fecha, Producto, Comercial, Zona e Importe. La idea: resumir cuánto vendió cada comercial y poder filtrar al instante por zona y por mes.

Crear la tabla dinámica paso a paso

En la lista de campos que aparece a la derecha:

1. Arrastra Comercial al área Filas.
2. Arrastra Importe al área Valores; se quedará como Suma de Importe.

Añadir la segmentación por zona

1. Pulsa sobre la tabla dinámica (cualquier celda de la zona G3:K6 vale).
2. Ve a Insertar > Filtros > Segmentación de datos.
3. Marca la casilla Zona y pulsa Aceptar.
4. En la segmentación, clic derecho > Conexiones del informe y desmarca cualquier tabla dinámica que no quieras que filtre.
5. En la segmentación, en Herramientas de segmentación de datos > Diseño de segmentación de datos, ajusta el número de columnas (por ejemplo, 3).

Poner la escala de tiempo por fecha

1. Con la tabla dinámica seleccionada, ve a Insertar > Filtros > Escala de tiempo.
2. Marca Fecha y pulsa Aceptar.
3. En la escala, arriba, elige el nivel Meses para que muestre botones en vez de días.
4. Pulsa Ctrl y haz clic en los meses que quieras cruzar.

Cruzar dos tablas con BUSCARX y SUMAR.SI.CONJUNTO

Ahora el libro Ventas2026 se completa con una hoja Productos que tiene el catálogo (Código, Nombre, Categoría). La idea: en la hoja de ventas traer el nombre y la categoría de cada producto a partir del código, y luego resumir las unidades por categoría con SUMAR.SI.CONJUNTO.

Traer el nombre del producto con BUSCARX

=BUSCARX(B2;Productos!A:A;Productos!B:B;"No existe";0)

En C2 escribe la fórmula y arrástrala hasta C6 con el controlador de relleno. BUSCARX busca B2 en la columna A de Productos y devuelve el valor de la columna B (Nombre). El cuarto argumento ("No existe") es lo que se mostrará cuando no encuentre el código; el quinto (0) indica coincidencia exacta.

Sumar unidades por categoría con SUMAR.SI.CONJUNTO

=SUMAR.SI.CONJUNTO(Ventas!E:E;Ventas!D:D;$F2)

En la hoja Ventas, crea una mini tabla en F1:G3 con encabezados Categoría y Total Unidades. En G2 escribe la fórmula anterior. El primer rango (E:E) se suma; el segundo (D:D) es donde se busca el criterio (la categoría que escribas en F2). La referencia $F2 lleva el dólar solo en la columna para que, al arrastrar hacia abajo, el criterio se mueva por las filas pero siempre lea la columna F.

Cuando BUSCARX no encuentra el código

En C6 BUSCARX devuelve "No existe" porque P-005 no está en Productos. Antes, con BUSCARV, este caso se resolvía con un SI.ERROR(BUSCARV(...);"No existe") que anidaba dos funciones; BUSCARX trae ese manejo de errores en el cuarto argumento, así que la fórmula queda en una sola línea. La trampa típica: olvidar poner el cuarto argumento y acabar viendo #N/A en pantalla.

Consolidar tres hojas de ventas con Power Query

El libro de ventas creció y ahora hay tres hojas separadas por zona (Norte, Sur, Centro) con el mismo formato (Fecha, Producto, Importe) y 20 filas cada una. La idea: dejar de copiar a mano y consolidar las tres hojas con Power Query, calculando el total por zona automáticamente.

Traer la primera hoja con Power Query

1. Abre la hoja Norte, selecciona A1:C21 y ve a Datos > Obtener y transformar > De tabla o rango.
2. En el cuadro que aparece, marca La tabla tiene encabezados y pulsa Aceptar. Se abre el editor de Power Query.
3. En la cinta del editor, Inicio > Cerrar y cargar > Cerrar y cargar en.... Elige Solo crear conexión y acepta.

Anexar las otras dos hojas

1. Repite los pasos del apartado anterior con Sur y con Centro; al final tendrás tres consultas ("Norte", "Sur", "Centro") en el panel.
2. Abre Norte en el editor. En Inicio > Combinar > Anexar consultas elige Tres o más tablas, añade Sur y Centro a la lista de tablas disponibles y pulsa Aceptar.
3. Comprueba que las tres columnas (Fecha, Producto, Importe) aparecen alineadas. Si falta alguna, revisa los encabezados de las hojas.
4. Inicio > Cerrar y cargar > Cerrar y cargar en... y elige de nuevo Solo crear conexión.

Cerrar y cargar el resumen

1. Con la consulta "Norte" ya anexada, ve a Transformar > Agrupar por.
2. Configura: Agrupar por Producto, Nueva columna Total Importe, Operación Suma, Columna Importe.
3. Inicio > Cerrar y cargar > Cerrar y cargar en... y elige Tabla en la hoja existente, celda G3.

Power Query y las versiones de Excel

Power Query está integrado en la pestaña Datos desde Excel 2016. En Excel 2010 y 2013 hay que descargar el complemento gratuito Power Query del centro de descarga de Microsoft. En Excel para la web las consultas se pueden refrescar, pero el editor de Power Query no está disponible, así que las transformaciones se hacen en el escritorio y después se sube el archivo.

Resolver un reparto con Solver

El equipo de dirección tiene 100.000 para repartir entre tres proyectos (A, B, C) y quiere maximizar el retorno esperado. Cada proyecto tiene un retorno por euro invertido y una limitación: el proyecto A no puede recibir más del 40 % del total. La idea: dejar que Solver busque la combinación que cumple las restricciones y maximiza el retorno.

Cargar Solver cuando no aparece en la cinta

1. Ve a Archivo > Opciones > Complementos.
2. En la parte inferior, donde pone Administrar, elige Complementos de Excel y pulsa Ir.
3. Marca Solver y pulsa Aceptar.
4. Solver queda en Datos > Análisis > Solver.

Plantear el modelo: celda objetivo, variables y restricciones

Pasos:

1. Abre Datos > Análisis > Solver.
2. Objetivo: $D$5. Marca Máx.
3. Cambiando las celdas de variables: $B$2:$B$4.
4. Pulsa Agregar y añade las restricciones:

– $B$2:$B$4 <= 40000 (A no supera el 40 %). – $B$5 = 100000 (suma igual al total disponible). – $B$2:$B$4 >= 0 (no se puede asignar negativo).

1. Pulsa Resolver y elige Mantener solución de Solver.

Cuando no hay solución posible

Si en Sujetas a las restricciones escribes $B$2:$B$4 <= 30000 (A no supera 30.000), $B$2:$B$4 <= 30000 (B no supera 30.000) y $B$2:$B$4 <= 30000 (C no supera 30.000), el máximo repartible sería 90.000, no 100.000. Solver mostrará "Solver no encontró una solución factible". La causa: las restricciones suman menos de lo obligatorio. La solución es revisar las restricciones hasta que sean compatibles (en este ejemplo, subir uno de los topes hasta que la suma máxima llegue a 100.000).

Grabar una macro y asignarla a un botón

El libro Facturas tiene una hoja con 30 facturas y cada vez que llega un lote nuevo hay que poner Mayúscula inicial en cada palabra del nombre del cliente, dar formato de moneda al importe y ordenar por fecha. La idea: grabar esos clics una vez en una macro y asignarla a un botón.

Activar la pestaña Programador y empezar a grabar

1. Archivo > Opciones > Personalizar cinta de opciones. En la columna derecha marca Programador y pulsa Aceptar.
2. Ve a Programador > Código > Grabar macro.
3. Nombre: FormatearFacturas. Guardar macro en: Este libro.
4. Pulsa Aceptar y empieza a grabar:

– Selecciona B2:B31 y aplica Mayúscula inicial en cada palabra (Inicio > Fuente > Cambiar mayúsculas y minúsculas). – Selecciona D2:D31 y aplica formato de moneda (Ctrl+1 > Moneda). – Selecciona A1:D31 y ordena por Fecha ascendente (Datos > Ordenar y filtrar > Ordenar).

1. Pulsa Programador > Código > Detener grabación.

Ejecutar la macro con un botón en la hoja

1. Programador > Controles > Insertar > Botón (control de formulario).
2. Dibuja el botón en la hoja, por ejemplo en F2.
3. Aparece el cuadro Asignar macro: selecciona FormatearFacturas y pulsa Aceptar.
4. Clic derecho sobre el botón > Modificar texto y escribe Formatear facturas.

Ver y entender el código VBA generado

Pulsa Programador > Código > Macros > selecciona FormatearFacturas > Modificar. Se abre el editor de VBA con el código grabado.

Sub FormatearFacturas()
    Range("B2:B31").Select
    Selection.Application.Cells.Replace _
        What:=".*", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
    Selection.Font.Bold = False
    Range("D2:D31").Select
    Selection.NumberFormat = "#,##0.00 €"
    Range("A1:D31").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End Sub

La línea Selection.NumberFormat = "#,##0.00 €" aplica el formato de moneda a la selección. La línea Selection.Sort Key1:=Range("A2") ordena por la columna A (Fecha) usando la celda A2 como clave. La macro grabada no es código limpio, pero se puede leer y modificar: por ejemplo, cambiar A1:D31 por Selection para que trabaje sobre lo seleccionado.

Cuando la macro no se ejecuta al pulsar el botón

Si guardas el libro como Libro de Excel (.xlsx) en lugar de Libro de Excel habilitado para macros (.xlsm), la macro se elimina. La causa: el formato .xlsx no admite macros por seguridad. La solución: Archivo > Guardar como > Libro de Excel habilitado para macros (.xlsm). Al volver a abrir, si ves la barra Advertencia de seguridad, pulsa Habilitar contenido.

Lo que ya sabes hacer como experto y el siguiente paso

• Tablas dinámicas con segmentaciones y escalas de tiempo cruzando zona y mes al instante.
• Power Query consolidando tres hojas de ventas y calculando el total por producto en una sola consulta.
• BUSCARX trayendo el nombre y la categoría del producto sin SI.ERROR anidado, con manejo de errores integrado.
• Solver encontrando la mejor distribución de inversión que cumple las restricciones del equipo de dirección.
• Macros grabadas y asignadas a un botón que formatean y ordenan la hoja de facturas en un solo clic.

El siguiente paso es presentarte al examen MO-211 para certificar este nivel, o abrir el editor VBA y empezar a programar: declarar variables, escribir bucles `For y crear una función propia que se pueda llamar desde cualquier celda.

Dudas al llegar al nivel experto en Excel