Tutorial Excel

Aprende Excel

¡¡¡Más de 300 lecciones de Excel GRATIS!!!

Trucos

Fórmulas matriciales en Excel: Guía completa del método clásico al moderno

Las fórmulas matriciales en Excel constituyen una de las herramientas más potentes y avanzadas para el análisis de datos. Sin embargo, su concepto suele generar confusión. A diferencia de las fórmulas estándar que operan con celdas individuales, las fórmulas de matriz están diseñadas para trabajar con bloques o series de datos completos de una sola vez.

En los últimos años, el motor de cálculo de Microsoft ha sufrido la mayor revolución de su historia con la llegada de las matrices dinámicas. Esto ha dividido el uso de matrices en dos mundos: el método clásico tradicional (CSE) y el sistema moderno de desbordamiento.

En esta guía te explicamos a fondo qué son, cómo utilizarlas según tu versión de Excel, sus ventajas estratégicas en el ámbito empresarial y cómo evitar los errores más comunes.

¿Qué es una fórmula matricial en Excel?

Una fórmula matricial (o fórmula de matriz) es una ecuación que realiza múltiples cálculos sobre uno o más conjuntos de datos (denominados matrices) y que puede devolver un único resultado o múltiples resultados en un rango de celdas.

Es fundamental aclarar que, en el entorno de Excel, el término «operación matricial» se refiere a procesar colecciones de celdas simultáneamente y no tiene relación directa con las operaciones de álgebra matricial matemática.

El gran cambio: Matrices clásicas (CSE) vs. Matrices dinámicas

Dependiendo de la versión de software que hayas identificado en tu equipo, la forma de interactuar con estas fórmulas cambia radicalmente:

1. El método clásico: Fórmulas CSE (Ctrl + Mayús + Intro)

En las versiones tradicionales de Excel (Excel 2019 e inferiores), las fórmulas matriciales se reconocen visualmente porque aparecen encerradas entre llaves { }.

Estas llaves no se pueden escribir a mano; si las tecleas, Excel interpretará la fórmula como texto plano. Para activarlas, tras escribir la función, debes confirmar pulsando la combinación de teclas Ctrl + Mayús + Intro (en inglés, Ctrl + Shift + Enter, de ahí las siglas CSE).

Pasos para introducir una fórmula matricial clásica:

  1. Selecciona la celda o el rango completo donde se volcarán los resultados.
  2. Escribe la fórmula de forma habitual.
  3. En lugar de pulsar Intro, presiona simultáneamente Ctrl + Mayús + Intro.

2. El método moderno: Matrices dinámicas y desbordamiento

Si utilizas Microsoft 365 o Excel 2021/2024, el programa incorpora un nuevo motor de cálculo. Ya no necesitas utilizar la combinación CSE ni verás las llaves {}.

Ahora, al introducir una fórmula que devuelve múltiples datos, el resultado «desborda» automáticamente ocupando las celdas contiguas necesarias. A este rango azulado que aparece alrededor se le conoce como rango de desbordamiento.

Tipos de fórmulas matriciales

Podemos clasificar estas funciones en dos grandes grupos según el volumen de datos que devuelven en la hoja de cálculo:

  • De resultado único (Escalares): Toman una matriz de datos, realizan la operación interna y consolidan el resultado en una sola celda. Un ejemplo clásico es calcular el total de una facturación multiplicando el rango de Cantidad por Precio Unitario dentro de una función SUMA, sin necesidad de crear una columna intermedia de «Total por línea».
  • De resultados múltiples (Rango de matriz): Devuelven una serie de datos que ocupan varias celdas. En el Excel clásico debías preseleccionar el rango exacto antes de pulsar CSE; en el Excel moderno, la fórmula se escribe en una sola celda y se expande sola hacia abajo o hacia la derecha.

Ventajas e inconvenientes en la gestión de datos

Implementar matrices en tus libros de control financieros o de inventario ofrece importantes beneficios, aunque también exige ciertas precauciones.

Ventajas estratégicas:

  • Optimización del espacio: Permiten realizar análisis de costes o auditorías complejas eliminando la necesidad de filas o columnas auxiliares de «pasos intermedios».
  • Integridad y seguridad de datos: En el método clásico CSE, no es posible modificar, desplazar o eliminar una sola celda que pertenezca a la matriz. Si lo intentas, Excel lanzará el aviso «No se puede cambiar parte de una matriz», blindando tus fórmulas contra errores humanos.
  • Consistencia: Garantizan que la misma lógica de cálculo se aplique de forma exacta e idéntica a todo el bloque de datos seleccionado.

Inconvenientes a tener en cuenta:

  • Curva de aprendizaje: Pueden resultar difíciles de interpretar y auditar para usuarios con un nivel básico o intermedio que compartan el archivo.
  • Rendimiento del procesador: Si se aplican fórmulas matriciales complejas (especialmente el método CSE clásico) sobre decenas de miles de filas, el recalculo automático del libro puede ralentizar el rendimiento del ordenador.

Caso práctico: Evitando errores comunes de desbordamiento

Uno de los errores más frecuentes en el entorno moderno de matrices dinámicas es el error #¡DESBORDAMIENTO! (o #SPILL!).

Este error ocurre cuando introduces una fórmula matricial que necesita ocupar, por ejemplo, 5 celdas hacia abajo, pero hay texto o datos escritos manualmente en alguna de esas celdas bloqueando el camino.

[Aquí mantienes tu imagen: Formula-matricial-1.jpg u otra que ilustre el concepto]

Cómo solucionarlo: Tan pronto como limpies o elimines el contenido de las celdas que están obstruyendo el paso, la matriz se expandirá automáticamente y mostrará los resultados correctos de inmediato.

Ejemplos prácticos y guías paso a paso

Para dominar por completo el comportamiento de estas herramientas y ver escenarios reales de aplicación con datos numéricos, te recomendamos revisar nuestros tutoriales detallados:

Los pasos a seguir para introducir una fórmula matricial son los siguientes:
  1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Puede ser un rango de celdas o una celda.
  2. Introducir la fórmula propiamente dicha.
  3. Para validar la fórmula no se ha de pulsar Intro, sino que lo que hemos de pulsar es la combinación de teclas Ctrl + Mayus + Intro.

Ejemplos del uso de las fórmulas matriciales

Cómo saber qué versión de Excel tengo instalada: Guía paso a paso

Saber con total exactitud qué versión de Excel estás utilizando es mucho más que una simple curiosidad técnica. En el día a día profesional, la versión de tu hoja de cálculo determina qué funciones tienes disponibles, el límite de datos que puedes procesar y la compatibilidad de tus plantillas con las de tus clientes o compañeros de departamento.

Si intentas utilizar matrices dinámicas modernas o funciones avanzadas como LET, SECUENCIA o BUSCARX en una versión antigua, tu hoja de cálculo se romperá y mostrará el temido error #¿NOMBRE?.

En esta guía te explicamos detalladamente cómo identificar tu versión de Microsoft Excel, desde las ediciones clásicas hasta las más recientes de Microsoft 365, tanto en su interfaz como en los detalles técnicos internos.

¿Por qué es fundamental conocer tu versión exacta de Excel?

El entorno corporativo exige que compartamos libros de trabajo constantemente. No conocer las especificaciones de tu software de hojas de cálculo puede acarrear los siguientes inconvenientes:

  • Incompatibilidad de funciones avanzadas: Microsoft introduce mejoras continuamente. Las versiones perpetuas antiguas carecen del motor de cálculo para interpretar las fórmulas más eficientes del Excel moderno.
  • Límites de memoria (32 bits vs. 64 bits): Una versión de 32 bits solo puede aprovechar un máximo de 2 GB de la memoria RAM de tu ordenador, lo que genera cuelgues constantes si trabajas con bases de datos pesadas o modelos contables complejos. La versión de 64 bits elimina esta barrera.
  • Seguridad y soporte: Las versiones anteriores a Excel 2016 ya no reciben actualizaciones oficiales de seguridad por parte de Microsoft, lo que expone tus datos financieros a vulnerabilidades.

Cómo identificar las versiones clásicas de Excel por su interfaz

Si trabajas en un entorno corporativo con sistemas heredados, es posible que utilices alguna de las versiones clásicas del programa. Observando la interfaz y los menús de inicio de la aplicación es relativamente sencillo comprobar la versión de Excel que estamos utilizando.

Excel 2007: La era del Botón de Office

La versión de Excel 2007 es inconfundible dentro de la suite ofimática. Fue la primera versión histórica de Excel que incorporó la conocida cinta de opciones (Ribbon), sustituyendo a los antiguos menús desplegables de los años 90.

A diferencia de todas las versiones posteriores, contiene en la parte superior izquierda el icónico Botón de Office (un círculo con el logotipo del programa). Este botón, en el que se encuentran principalmente las opciones que se refieren a la gestión del libro de Excel (Guardar, Abrir, Imprimir) y a las opciones de configuración de la aplicación, es la seña de identidad característica de Office 2007.

Excel 2010: El regreso de la pestaña Archivo

Diferenciar las versiones posteriores puede resultar un poco más complejo a simple vista. Vamos a ver cómo comprobamos si estamos utilizando la versión de Office 2010.

En esta edición, Microsoft eliminó el botón redondo y devolvió una estructura más limpia introduciendo la pestaña Archivo de color verde. Para verificar la versión exacta en Excel 2010:

  1. Abre cualquier fichero de Excel o crea uno nuevo.
  2. Dirígete a la pestaña Archivo (o menú de Inicio).
  3. Haz clic en el menú Ayuda.
  4. En el panel de la derecha, te aparecerá una sección informativa detallada que indica el número de compilación y si tu producto está activo.

Cómo comprobar tu versión en Excel 2013 y Excel 2016

Las interfaces de Excel 2013 y Excel 2016 son sumamente parecidas a simple vista debido al diseño plano implantado por Microsoft. Para salir de dudas, debemos realizar una comprobación interna a través del sistema de gestión de licencias del programa.

Comprobación en Excel 2013

  1. Abre un fichero de Excel y pincha sobre la ficha Archivo en la esquina superior izquierda.
  2. En el menú vertical que se despliega, pulsa sobre la opción Cuenta.
  3. En el bloque central verás la sección llamada Información de Producto, en la que se indica el producto activado, detallando explícitamente «Microsoft Office 2013».

Si necesitas conocer detalles técnicos más específicos, puedes pulsar el botón Acerca de Excel. Se abrirá una ventana emergente donde se muestra el código de compilación exacto del software.

Comprobación en Excel 2016

El procedimiento para auditar la versión 2016 es idéntico al anterior, ya que comparten la misma estructura de paneles:

  1. Ve a la ficha Archivo y selecciona la opción de menú Cuenta.
  2. Observa el apartado de Información de Producto. En algunas licencias por volumen, el nombre comercial exacto puede no aparecer a primera vista en este panel principal.
  3. Si el nombre de la versión no está visible de forma clara, pulsa sobre el botón Acerca de Excel. Esto abrirá la ventana técnica donde comprobarás de forma inequívoca que estás trabajando bajo el entorno de Excel 2016.

El estándar moderno: Excel 2019, 2021, 2024 y Microsoft 365

Si tu barra superior de Excel tiene un buscador centralizado y un diseño redondeado moderno, estás utilizando una de las versiones de última generación. Sin embargo, hay una diferencia crucial que debes conocer: las versiones perpetuas frente al modelo de suscripción.

  • Microsoft 365 (Antes Office 365): Es un servicio de suscripción que garantiza que siempre tengas las herramientas y funciones más recientes de Microsoft (como la tecnología de matrices dinámicas avanzadas). Se actualiza mes a mes.
  • Excel 2019 / 2021 / 2024: Son versiones de pago único. Compras la licencia y el programa funciona para siempre, pero se queda «congelado» en el tiempo. No recibe las funciones nuevas que Microsoft va inventando en los años posteriores.

Pasos para identificar las versiones modernas y saber si usas 32 o 64 bits:

Para comprobar cuál de estas versiones modernas tienes en tu ordenador, el método definitivo es el siguiente:

  1. Entra en Excel y haz clic en la pestaña Archivo.
  2. Selecciona Cuenta en la parte inferior del menú izquierdo.
  3. Bajo el título Información del producto, verás el nombre comercial de tu suite (por ejemplo: Microsoft 365 para empresas o Microsoft Office Hogar y Estudiantes 2021).
  4. Para conocer el dato más importante, haz clic en el botón Acerca de Excel.
  5. En la primera línea de la ventana flotante que aparece, junto al número de versión, leerás claramente si tu Excel es de 32 bits o de 64 bits.

Tabla comparativa rápida de compatibilidad de funciones

Para evitar errores al trabajar con otras personas, revisa esta tabla que muestra qué herramientas clave están disponibles según la versión de Excel que hayas detectado en tu equipo:

Viendo la interfaz de un programa de Excel es relativamente sencillo comprobar la versión de Excel que estamos utilizando.

Versión de Excel¿Tiene función BUSCARX?¿Admite Matrices Dinámicas?Soporte Oficial Microsoft
Excel 2007 / 2010❌ No❌ No🚫 Finalizado
Excel 2013 / 2016❌ No❌ No🚫 Finalizado / Crítico
Excel 2019❌ No❌ No⚠️ Solo parches básicos
Excel 2021 / 2024SíSí (Básicas)Activo
Microsoft 365Sí (Siempre actualizado)Sí (Completo: LET, LAMBDA…)Totalmente compatible

¿Necesitas encontrar un dato en una tabla? INDICE y COINCIDIR es la solución definitiva

Cuando trabajamos con grandes matrices o tablas de doble entrada en Microsoft Excel (como tarifas de precios por producto y talla, o catálogos complejos), las funciones de búsqueda tradicionales como BUSCARV pueden quedarse cortas, ya que solo buscan de izquierda a derecha y en una sola dirección.

Para solucionar búsquedas bidimensionales de forma exacta y dinámica, la combinación de las funciones ÍNDICE y COINCIDIR es la herramienta más potente y flexible que puedes aprender. A continuación, te explicamos cómo funciona paso a paso.

Entendiendo los componentes de la fórmula

Para localizar con precisión un dato en una cuadrícula, necesitamos saber en qué fila y en qué columna se intersectan. Aquí es donde entran en juego nuestras dos funciones:

  1. La función COINCIDIR: Su objetivo es buscar un elemento dentro de un rango y devolvernos la posición numérica (el índice) que ocupa.
    • Sintaxis: =COINCIDIR(valor_buscado; matriz_buscada; [tipo_de_coincidencia])
    • Para búsquedas exactas, el tipo_de_coincidencia siempre se establece en 0.
  2. La función ÍNDICE: Una vez que conocemos los números de fila y columna, esta función va a la matriz general y extrae el valor exacto que se encuentra en esa intersección.
    • Sintaxis: =ÍNDICE(matriz; num_fila; [num_columna])

Paso 1: Crear listas desplegables dinámicas (Validación de datos)

Para evitar errores de escritura y hacer que tu buscador sea completamente interactivo, lo ideal es crear menús desplegables para tus criterios de búsqueda (en este ejemplo, Producto y Talla):

  1. Selecciona la celda donde irá el nombre del producto.
  2. Dirígete a la pestaña Datos y haz clic en Validación de datos.
  3. En el criterio de evaluación, cambia «Cualquier valor» por Lista.
  4. En el cuadro de Origen, selecciona todo el rango vertical que contiene los nombres de tus productos (por ejemplo, de Jersey a Chaqueta) y pulsa Aceptar.
  5. Repite el mismo proceso para la celda de la Talla, pero seleccionando el rango horizontal de las cabeceras (desde XS hasta XXL).

Paso 2: Localizar la posición con la función COINCIDIR

Antes de armar la fórmula final, es útil entender cómo calcula Excel las posiciones automáticamente:

  • Para la Fila del Producto: Si quieres saber en qué posición está un producto concreto dentro de tu lista, escribe: =COINCIDIR(Celda_Producto_Buscado; Rango_Lista_Productos; 0) Si seleccionas «Falda» y es el tercer elemento de la lista de arriba a abajo, Excel devolverá un 3.
  • Para la Columna de la Talla: Para obtener la posición horizontal de la talla, introduce: =COINCIDIR(Celda_Talla_Buscada; Rango_Cabecera_Tallas; 0) Si buscas la talla «M» y es la tercera columna de izquierda a derecha, la función devolverá un 3.

Paso 3: El truco maestro: Combinar ÍNDICE y COINCIDIR

Una vez dominados los pasos anteriores, procedemos a realizar la búsqueda bidimensional anidando las funciones en la celda donde quieres ver el Precio final:

  1. Escribe =ÍNDICE( y selecciona la matriz completa que contiene exclusivamente los datos numéricos o precios (sin incluir las cabeceras de texto de filas ni columnas).
  2. Añade un punto y coma (;) e introduce la primera función COINCIDIR para que calcule dinámicamente el número de fila basándose en el producto seleccionado.
  3. Añade otro punto y coma (;) e introduce la segunda función COINCIDIR para obtener el número de columna basándose en la talla seleccionada.

La estructura de tu fórmula final lucirá exactamente así:

=ÍNDICE(Matriz_Precios; COINCIDIR(Producto_Buscado; Lista_Productos; 0); COINCIDIR(Talla_Buscada; Lista_Tallas; 0))

Resultado Final

¡Listo! Al presionar Intro, Excel buscará de forma instantánea el precio exacto en la intersección de los dos criterios. Gracias a las listas desplegables, cada vez que cambies el producto o la talla en tu buscador, el precio se actualizará de manera automática y sin errores, dándote una solución profesional y robusta para la gestión de tus bases de datos.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Los 10 trucos de Pegado Especial que debes conocer

El Pegado Especial es una de las herramientas más potentes de Microsoft Excel, pero la mayoría de los usuarios solo la utiliza para una única función: eliminar fórmulas. En realidad, este menú oculto te permite realizar operaciones matemáticas masivas, corregir formatos defectuosos y replicar diseños complejos en cuestión de segundos.

En este artículo, desglosamos paso a paso los 10 trucos esenciales para llevar tu productividad al siguiente nivel.

1. Convertir números positivos a negativos (y viceversa)

Cuando importas extractos bancarios o datos de contabilidad, a veces los gastos o abonos se registran con el signo incorrecto. En lugar de cambiarlos uno a uno:

  1. En una celda vacía, escribe el número -1 y cópialo (Ctrl + C).
  2. Selecciona todo el rango de números que deseas transformar.
  3. Ve a Pegado Especial, selecciona las opciones Valores y Multiplicar, y haz clic en Aceptar. Todos tus números cambiarán de signo de forma inmediata.

2. Eliminar fórmulas manteniendo los resultados (Pegar Valores)

El uso más clásico pero imprescindible. Si vas a enviar un archivo a un tercero y tus fórmulas dependen de otros libros de trabajo locales, los vínculos se romperán.

  1. Selecciona las celdas con las fórmulas, por ejemplo, una columna de comisiones calculadas.
  2. Haz clic en Copiar.
  3. En el mismo lugar (o en el destino), haz clic derecho y selecciona la opción de Pegar Valores. Las fórmulas desaparecerán dejando únicamente los datos finales.

3. Invertir la estructura de una tabla (Transponer)

Si has organizado un reporte con los campos distribuidos en filas, pero necesitas cambiarlos a columnas para procesar mejor la información:

  1. Copia toda la tabla original.
  2. Sitúate en la celda de destino donde comenzará tu nueva estructura.
  3. Abre el menú de Pegado Especial y marca la casilla Transponer. Las filas pasarán a ser columnas automáticamente.

4. Convertir texto con formato de número a valor numérico real

Al extraer datos de softwares externos o webs, los números suelen alinearse a la izquierda. Esto indica que Excel los lee como «texto», impidiendo que fórmulas como SUMA funcionen (dando un resultado de cero).

  1. Escribe el número 1 en cualquier celda y dale a Copiar.
  2. Selecciona el rango con los «falsos números».
  3. En Pegado Especial, elige Valores y Multiplicar. Al multiplicar el texto por 1, Excel lo fuerza a convertirse en un número real alineado a la derecha.

5. Copiar y replicar el ancho de las columnas

Si tienes un diseño de reporte por trimestres y notas que una de las columnas ha perdido su anchura ideal por accidente:

  1. Haz clic en cualquier celda de una columna que tenga el tamaño correcto y cópiala.
  2. Selecciona la columna o celda desconfigurada.
  3. Abre Pegado Especial, marca la opción Ancho de la columna y acepta. El tamaño se corregirá al instante sin alterar los datos internos.

6. Duplicar el formato de un gráfico en otro

No pierdas tiempo editando colores, fuentes y ejes si ya tienes un gráfico perfectamente diseñado.

  1. Haz clic sobre el gráfico que tiene el diseño final y selecciónalo para Copiar [06:25].
  2. Haz clic sobre el segundo gráfico (el que tiene el formato antiguo).
  3. Entra a Pegado Especial, selecciona Formatos y pulsa Aceptar [06:35]. El segundo gráfico adoptará la estética idéntica del primero.

7. Transformar valores enteros a formato de porcentaje

Si cuentas con una lista de números enteros (por ejemplo, 10, 20, 50) y necesitas que se muestren como porcentajes reales (10%, 20%, 50%), si aplicas directamente el estilo porcentual se multiplicarán por 100 dando resultados erróneos (como 1000%). El truco es el siguiente:

  1. Escribe el número 100 en una celda y cópialo.
  2. Selecciona tus números y en Pegado Especial selecciona la operación Dividir.
  3. Ahora que están expresados en decimales correctos (0,10, 0,20), aplícales el Estilo porcentual de la barra de herramientas de Excel.

8. Incrementar precios o valores masivamente en un porcentaje fijo

Imagina que necesitas aumentar todos los precios de un catálogo un 25% debido a la inflación:

  1. En una celda limpia escribe 1,25 (que representa el 100% original más el 25% de aumento) y dale a Copiar.
  2. Sombrea toda la lista de precios a modificar.
  3. Ve a Pegado Especial, marca Valores y selecciona Multiplicar. Todos tus precios se actualizarán sumando el 25% de recargo de un solo golpe.

9. Sumar de forma directa una columna sobre otra

Si tienes dos columnas independientes con datos y quieres consolidarlas sumando los valores de la segunda directamente sobre la primera (incluso si están en hojas o libros distintos):

  1. Selecciona y copia los datos de la columna origen.
  2. Selecciona el rango destino.
  3. Ve a Pegado Especial y haz clic en la operación Sumar. Los valores copiados se añadirán aritméticamente a los números preexistentes.

10. Reemplazar datos omitiendo celdas vacías (Saltar blancos)

Si dispones de una columna de información actualizada (Columna C) que pretendes volcar sobre una lista antigua (Columna A), pero la lista nueva contiene celdas en blanco debido a que ciertos registros no variaron:

  1. Selecciona y copia toda la columna nueva (incluyendo sus espacios vacíos).
  2. Selecciona el destino en la lista antigua.
  3. En Pegado Especial, marca la casilla inferior llamada Saltar blancos y acepta. Las celdas con información se sobrescribirán, pero donde había un espacio en blanco en tu copia, se conservará intacto el valor original de la lista vieja .

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Crear Gráficos de Línea en Excel con Datos Vacíos

Cuando tratamos de crear un gráfico de líneas con dos series de las que una tiene los datos incompletos nos podemos encontrar con el siguiente problema:

Vemos que de la primera serie de datos (nº de fruterías) contamos con datos para todos los años. Sin embargo, para la segunda serie de datos (nº de pescaderías) no tenemos datos para todos los años.

El problema es que la linea del gráfico se ve interrumpida para los años en los que no tenemos datos. Lo peor es que además si tenemos datos un año suelto (como es el caso del año 2000, 2002 y 2007 en nuestro ejemplo) no aparece nada en el gráfico. Por tanto el gráfico mostrado de esa forma no es completo.

Utilización de marcadores

Una forma de corregir el hecho de que no aparecen los años sueltos es que decidamos incluir marcadores en la línea de la serie. Si por ejemplo decidimos que la línea tenga un marcador tipo cuadrado nos encontraríamos con el siguiente gráfico:

Con estea solución solucionamos el problema de no ver los años puntuales que tenemos, como podemos ver con los años 2000, 2002 y 2007. Ahora vemos el dato con un punto o cuadrado.

Sin embargo posiblemente no deseamos que el gráfico aparezca de esa forma. Lo más habitual es que deseemos que la línea de la serie una los puntos de un año con el siguiente del que tengamos datos. Es decir que una los datos que tengamos sin tener en cuenta los años en blanco. Eso además debe de poder hacerlo sin que tenga nada que ver que tenga o no marcadores. La forma de hacerlo es la siguiente.

Conectar puntos de datos con línea

Para que se conecten los puntos de datos de una serie debemos de pulsar sobre la opción «Seleccionar datos«.

En la ventana que aparece elegimos la opción «Celdas ocultas y vacías«

Y finalmente, sobre la siguiente ventana marcamos la opción «Conectar puntos de datos con línea«

Le damos a «Aceptar» y vemos que el gráfico ha cambiado, obteniendo el resultado que buscábamos.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

10 Trucos de FORMATOS en Excel

Microsoft Excel ofrece una variedad de herramientas de formato que pueden hacer tus hojas de cálculo más atractivas y funcionales.

A continuación vamos a ver 10 trucos que posiblemente no conozcáis y que nos permitirán exprimir al máximo el potencial que nos ofrecen los formatos en Excel.

1. Ocultar una selección de celdas

En una hoja Excel podemos hacer que unas celdas no sean visibles, aunque el valor de la celda se mantiene, como podemos ver en la barra de fórmulas.

Para ello debemos seleccionar las celdas que queremos ocultar, ir a Formato de celdas y en categoría personalizada incluiríamos tres veces punto y coma.

2. Eliminar o modificar valores nulos

Cuando tenemos una serie de datos y queremos que los valores nulos no se muestran o queremos que muestren algún texto como «No disponible», lo podemos hacer fácilmente con el Formato de celdas.

En categoría personalizada escribimos para ese caso el tipo: #.##0;-#.##0;»No disponible».

3. Centrar en selección

Para centrar el texto en el ancho de unas celdas seleccionadas, sin fusionarlas, manteniendo la estructura de la hoja de cálculo, podemos utilizar la opción de «Centrar en la selección».

Para ello seguiremos los siguientes pasos:

  1. Seleccionamos las celdas donde queremos centrar el texto.
  2. Hacemos clic derecho en la selección y elegimos Formato de celdas.
  3. En el cuadro de diálogo, vamos a la pestaña Alineación.
  4. En el campo Horizontal, seleccionamos la opción Centrar en la selección.
  5. Hacemos clic en Aceptar.

4. Valores según condiciones

Podemos establecer en el Formato de celdas directamente unas condiciones para que, en función del valor de la celda, me aparezca un dato u otro.

Por ejemplo, si tengo una serie de notas de alumnos y quiero que cuando escriba la nota, directamente me aparezca «Aprobado» o «Suspenso», según que la nota sea igual o superior a 5 (Aprobado) o menor que 5 (Suspenso).

Para ello entro en Formato de celdas, pestaña Número, Categoría Personalizada y en tipo escribo la siguiente condición:

[>=5]»Aprobado»;»Suspenso»

Como vemos, la condición debe ir siempre entre corchetes.

5. Combinar por filas

Para combinar las celdas seleccionadas en una sola fila, manteniendo el contenido de la celda más a la izquierda, podemos hacerlo siguiendo los siguientes pasos:

  1. Seleccionamos las celdas en la misma fila que deseas combinar.
  2. Vamos a la pestaña Inicio en la barra de herramientas.
  3. Hacemos clic en la flecha junto al botón Combinar y centrar.
  4. Seleccionamos la opción Combinar horizontalmente del menú desplegable.

6. Decimales según valores

Si queremos que nos muestre decimales únicamente para números inferiores a una cantidad, como por ejemplo la unidad, lo podemos hacer usando de nuevo las condiciones en la ventana de Formato de celdas.

Así, para el caso indicado, de que muestre dos decimales para números inferiores a la unidad, y sin decimales para números superiores utilizaríamos la siguiente condición en la opción Personalizada de la ventana de Formato de celdas:

[<1]0,00;0

7. Transformar a miles o millones

Si queremos pasar datos a miles y millones debemos actuar de la siguiente manera.

Para pasar datos a miles no situamos encima y vamos a Formato de celda y en la categoría Personalizada, escribimos en tipo: #. «miles»

Para pasar datos a millones no situamos encima y vamos a Formato de celda y en la categoría Personalizada, escribimos en tipo: #,000.. «millones»

8. Copiar ancho de columnas

Cuando queremos copiar unos datos y deseamos que nos mantenga el ancho de columnas de los datos originales, podemos expandir las opciones del botón Pegar y seleccionar la opción «Mantener ancho de columnas de origen».

9. Alinear y distribuir objetos

Cuando trabajamos con objetos en Excel debemos conocer las posibilidades que nos ofrece Excel para homogeneizar el tamaño, alinearlos o distribuirlos.

Para unificar el tamaño de distintos gráficos debemos seleccionarlos, manteniendo pulsada la tecla Control, y en la pestaña Formato de forma, le daríamos el tamaño que queramos.

Para alinear varios objetos los seleccionamos y en la pestaña Formato de forma desplegamos el botón Alinear y seleccionamos a qué parte queremos alinearlos.

De igual forma, para distribuir varios objetos, vertical u horizontalmente, los seleccionamos y en la pestaña Formato de forma desplegamos el botón Alinear y seleccionamos a qué parte queremos distribuirlos.

10. Duración en horas

Por último veremos un truco para pasar unos datos que muestran las horas en formato decimal a un formato de horas y minutos.

Aquí tenemos unas horas de entrada y salida. por diferencia se obtiene la duración, que por defecto aparece en formato decimal.

Si queremos que se muestre la duración en formato de horas y minutos tendremos que señalar las celdas correspondientes a la duración y seleccionar Formato de celdas.

En la pestaña número, categoría Personalizada escribimos en Tipo lo siguiente [h]:mm

De esa forma nos muestra el total de horas y los decimales los muestra como minutos.

En el siguiente video veremos una explicación detallada de los diferentes trucos de formatos que hemos comentado anteriormente.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Principales atajos o métodos abreviados en Excel

Utilizar los métodos abreviados que nos ofrece Excel nos ahorra mucho trabajo a la hora de desplazarnos en las hojas. También son muy prácticos cuando queremos realizar una tarea o función. Generalmente, los usuarios más avanzados de Excel utilizan mucho los atajos con el teclado, por el tiempo que les ahorra a la hora de trabajar.

Tenemos una lista completa de los métodos abreviados de Excel en el soporte oficial de Microsoft.

Sin embargo, generalmente no es necesario saberse tantos atajos. Lo importante es conocer los más útiles o los que más utilicemos. Debemos de tener en cuenta que según el idioma que tengamos configurado pueden cambiar las teclas.

A continuación presentaremos algunos de los métodos abreviados de Excel más utilizados.

Principales atajos o métodos abreviados de Excel

Ctrl + C

Copiar.

Pulsar simultáneamente las teclas «Control» y la letra «C».

Es el atajo más típico de Excel. Se utiliza mucho y por tanto es imprescindible que lo utilicemos para ahorrar tiempo en nuestro día a día.

Ctrl + X

Cortar.

Pulsar simultáneamente las teclas «Control» y la letra «X».

Similar al de copiar, aunque en este caso los datos pegados no se mantienen en su ubicación inicial, como sí ocurre cuando se copia. Otro atajo imprescindible.

Ctrl + V

Pegar.

Pulsar simultáneamente las teclas «Control» y la letra «V».

Es el complemento de los dos atajos anteriores. Tanto si se copia como si se corta, a continuación hay que pegar los datos en la celda o rango de destino.

Ctrl + Z

Deshacer.

Pulsar simultáneamente las teclas «Control» y la letra «Z».

Equivale a la flechita de Deshacer que normalmente tenemos en la barra de acceso rápido.

Ctrl + N

Negrita.

Pulsar simultáneamente las teclas «Control» y la letra «N».

Convierte en negrita el texto o contenido de las celdas seleccionadas. Nos evitamos marcar la negrita desde la ficha Inicio.

Ctrl + K

Cursiva.

Pulsar simultáneamente las teclas «Control» y la letra «K».

Convierte en cursiva el texto o contenido de las celdas seleccionadas. Nos evitamos marcar la cursiva desde la ficha Inicio.

Ctrl + S

Subrayado.

Pulsar simultáneamente las teclas «Control» y la letra «S».

Convierte en texto subrayado contenido de las celdas seleccionadas. Nos evitamos marcar el subrayado desde la ficha Inicio.

Ctrl + B

Buscar.

Pulsar simultáneamente las teclas «Control» y la letra «B».

Abre la ventana de Buscar y Reemplazar, estando activa la pestaña de Buscar.

Ctrl + L

Reemplazar.

Pulsar simultáneamente las teclas «Control» y la letra «L».

Abre la ventana de Buscar y Reemplazar, estando activa la pestaña de Reemplazar.

Ctrl + Alt + V

Pegado especial.

Pulsar simultáneamente las teclas «Control», «Alt» y la letra «V».

Abre la ventana de Pegado especial.

Ctrl + 1

Formato de celdas.

Pulsar simultáneamente las teclas «Control» y el número «1».

Abre la ventana de Formato de celdas.

Ctrl + «+»

Insertar.

Pulsar simultáneamente las teclas Control y el signo «+»

Abre la ventana de Insertar. Desde ella podemos insertar una fila o una columna entera. Igualmente se puede insertar únicamente la celda seleccionada desplazando las demás celdas hacia abajo o hacia la derecha.

Ctrl + «-«

Eliminar.

Pulsar simultáneamente las teclas Control y el signo «-«

Abre la ventana de Eliminar. Desde ella podemos eliminar una fila o una columna entera. Igualmente se puede eliminar únicamente la celda seleccionada desplazando las demás celdas hacia arriba o hacia la izquierda.

Ctrl + G

Guardar.

Pulsar simultáneamente las teclas Control y la letra «G»

Guarda el fichero en uso.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

 

Conectar tablas dinámicas

Nos podemos encontrar con la situación de tener varias tablas dinámicas y que queramos incluir unos segmentadores de datos que filtren a la vez todas las tablas dinámicas.

Esto sería muy indicado, por ejemplo, si tenemos un Dashboard, que utilice varias tablas dinámicas y que queramos que con un mismo segmentador se filtren los datos de todas las tablas de la hoja.

En la siguiente imagen tenemos dos tablas dinámicas y dos segmentadores. Si pulsamos sobre una zona o un contrato, el filtro se aplicará únicamente a la tabla desde la que hemos generado la segmentación.

Para conectar las tablas dinámicas para que se filtren todas ellas a la vez al pulsar una opción del segmentador nos situamos sobre el segmentador y nos vamos a la pestaña Segmentación.

Después pulsamos sobre el comando Conexiones de informes.

Se abre una ventana de Conexión de informe para la Segmentación Zona. Vemos que tiene marcado un check para la tabla dinámica a la que afecta esa segmentación.

Como queremos que afecte a más tablas lo único que tenemos que hacer es marcar el nombre de las tablas dinámicas que queremos que se vean afectadas con ese segmentador..

En nuestro caso, como queremos que se filtren las dos tablas dinámicas, marcamos las dos. Incluso se podrían marcar tablas dinámicas de hojas diferentes.

Eso lo tendríamos que hacer para cada una de las segmentaciones.

Ahora, si pulsamos algunas opciones de los segmentadores, veremos que se aplica a la dos tablas dinámicas que le hemos indicado.

Para ver en detalle cómo hemos seguido todos estos pasos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Alternativa a la función FILTRAR

Ya vimos lo útil y potente que resulta la nueva función FILTRAR en Excel.

Sin embargo, actualmente solo está disponible para los suscriptores de Microsoft 365, por lo que los usuarios de versiones anteriores de Excel no pueden utilizar esta función.

Por ello, en este artículo presentaremos dos alternativas a la función FILTRAR en Excel.

Supuesto con la función FILTRAR

En el ejercicio original utilizamos la función FILTRAR para mostrar de una forma dinámica los cursos realizados y los cursos pendientes.

En función del valor que aparece en la columna C, que son las celdas vinculadas a las casillas de verificación, se filtran los nombres de los cursos.

Alternativa a FILTRAR con tablas dinámicas

La forma más inmediata de filtrar los cursos si no tenemos la función FILTRAR sería mediante una tabla dinámica en la que mostramos por filas los cursos que cumplen el filtro de que el curso se encuentre realizado o no.

La apariencia es muy similar a la de utilizar la función FILTRAR. Ello se debe a que hemos ocultado una fila, la fila 10, en la que aparece el filtro del campo «Realizado».

Como vemos, hemos tenido que utilizar dos tablas dinámicas: una para los cursos realizado y otra para los pendientes.

Sin embargo, el empleo de tablas dinámicas presenta un importante inconveniente: las tablas dinámicas no se actualizan automáticamente cuando se cambia algún valor.

Por tanto, si marcamos un curso como realizado, con la función FILTRAR se actualizan los datos de cursos realizados y pendientes, pero con las tablas dinámicas no se actualizan hasta que no le demos expresamente a actualizar.

Dado este inconveniente, veremos otra alternativa a la función FILTRAR.

Filtrar mediante las funciones SI, K.ESIMO.MENOR, INDICE y SI.ERROR

La alternativa a la función FILTRAR que sí nos permite filtrar los datos de una forma dinámica sería utilizando varias funciones y columnas intermedias.

La ventaja respecto del uso de las tablas dinámicas es que se actualizarían automáticamente los datos conforme se cambian los datos de origen.

Los pasos y funciones a utilizar serían los siguientes.

Establecer el orden en función de la fila

Lo primero que haremos es establecer en una columna intermedia un número de orden. Lo ideal es empezar por el número 1 por lo que utilizaremos la función FILA y le restaremos en nuestro caso una unidad, para que aparezca el número de orden empezando por el 1.

Arrastramos hacia abajo la fórmula para obtener el orden de los demás cursos.

Separar el orden de los cursos realizados de los pendientes con función SI

Utilizando la función SI separaremos el número de orden de los cursos realizados en una columna (columna E) de los cursos pendientes (columna F).

Ordenar según realización del curso con la función K.ESIMO.MENOR

Una vez que tenemos separados los números de orden de los cursos, separados según estén realizados o pendientes, los ordenaremos de menor a mayor, utilizando la función K.ESIMO.MENOR.

Eso lo haremos para los dos casos: los cursos realizados y los pendientes.

Filtrar utilizando la función INDICE y SI.ERROR

Ahora buscaríamos el nombre del curso en función de su número de orden.

Para ello utilizaríamos la función INDICE, con la fórmula que se ve en la barra de fórmulas.

Dado que al final aparecen errores de tipo #¡NUM! anidaremos la función INDICE dentro de una función SI.ERROR.

Si ahora ocultamos todas las columnas intermedia, tendríamos un filtro de cursos realizados y pendientes idéntico al uso de la función FILTRAR.

Para ver en detalle cómo hemos seguido todos estos pasos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Crear un checklist con casillas de verificación

En este ejercicio veremos cómo podemos crear un checklist con casillas de verificación en Excel. Además, veremos cómo podemos utilizar los resultados para cambiar el formato de los valores marcados y para mostrar en otro lugar de la hoja los valores marcados y desmarcados.

Si bien desde hace pocos meses tenemos la herramienta Checkbox, que nos facilita mucho la utilización de las casillas de verificación, en este video utilizaremos los controles de formulario, dado que los usuarios de versiones anteriores de Excel no tendrán disponible la herramienta checkbox.

Para ver la utilización de la nueva herramienta, actualmente únicamente disponible para usuarios de Microsoft 365, os remito al siguiente video.

Crear un checklist con control de formulario (casillas de verificación)

Queremos obtener algo similar a lo que vemos a continuación.

En la columna A tenemos una serie de cursos y en la columna B unas casillas de verificación, que cuando las marcamos significa que hemos realizado el curso y si está desmarcado es que el curso está pendiente de realización.

Además en las columnas E y G, de una forma dinámica, se muestran qué cursos se han realizado y cuáles están pendientes.

Para descargaros directamente el archivo de Excel podéis pinchar en el siguiente enlace.

Datos iniciales

Partimos de los datos iniciales, que son todos los cursos.

Inserción de las casillas de verificación

Para insertar las casillas de verificación vamos a la pestaña Programador y desplegamos la opción Insertar. Dentro de los controles de formulario seleccionamos la tercera opción, casilla.

Ahora vamos a la celda correspondiente, en la columna B e insertamos la casilla.

Marcando la casilla con el botón derecho del ratón y haciendo doble clic sobre el nombre del control de formulario, le podemos cambiar el nombre.

Esa casilla la podemos copiar tantas veces como necesitemos.

Vincular casilla de verificación con celda

Ahora tenemos 7 casillas de verificación que podemos marcar o desmarcar pero no tienen ninguna funcionalidad asociada.

Para ello tenemos que seleccionar, con el botón derecho del ratón, la casilla y seleccionar Formato de control.

Se abre la ventana Formato de control y dentro de la pestaña Control, debemos rellenar el apartado de Vincular con la celda.

Para el curso de Excel lo vamos a vincular con la celda C2.

Ahora vemos que, cuando marcamos la casilla que está en B2 nos muestra en la celda C2 el texto VERDADERO. En caso de que esté desmarcado, nos aparece FALSO.

Haremos esto para todos los cursos.

Cambiar formato a cursos realizados

Como queremos que cuando el curso esté realizado, éste aparezca en color gris y tachado tendremos que utilizar el formato condicional para ello.

Seleccionamos todos los cursos de la columna A y pulsamos sobre Formato condicional y le damos a Nueva regla…

Es muy importante marcar, si procede, si queremos usar los rótulos en fila superior y columna izquierda.

Nos aparece la ventana de Editar la regla de formato y seleccionamos la opción Utilice una formula que determine las celdas para aplicar formato.

Escribimos la fórmula =C2=VERDADERO y pulsamos sobre el botón formato, donde elegimos el formato que deseemos.

En mi caso he cambiado el color de la fuente y lo indico como tachado.

Ya hemos conseguido que cuando se marque nos cambie el formato del curso y aparezca tachado.

Mostrar listado de cursos realizados y pendientes

Para mostrar en unas columnas aparte qué cursos se han realizado y qué cursos están pendientes utilizaremos la función FILTRAR.

Aplicando esa fórmula aparecerán todos los cursos que en la columna C tienen en valor VERDADERO y, por tanto, se han realizado.

De igual forma haremos para los cursos pendientes.

Con esa fórmula mostraremos de forma dinámica los cursos pendientes de realizar.

Ocultar valores de las celdas vinculadas

Finalmente, para mejorar el diseño de la hoja, vamos a ocultar el valor de las celdas vinculadas con las casillas de verificación, para que no veamos el valor de VERDADERO o FALSO.

Para ello marcamos las celdas de la columna C y le damos al botón derecho del ratón, seleccionando Formato de celdas.

Dentro de la ventana que se abre seleccionamos la categoría Personalizada y en tipo escribimos tres veces el símbolo punto y coma ;;;

Le damos a Aceptar y ya tendríamos creado el checklist con todas las funcionalidades que hemos visto.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Celda de enfoque

Excel acaba de liberar una nueva funcionalidad que se llama Celda de enfoque y que se utiliza para resaltar la fila y la columna de la celda activa.

Esto es especialmente útil cuando tenemos muchos datos y perdemos la perspectiva de la fila y columna que hemos seleccionado.

Dado que es una funcionalidad nueva solamente lo tendrán disponibles los suscriptores de Microsoft 365.

Si tenemos una versión diferente de Excel también podemos obtener el mismo efecto de alguna de las siguientes dos maneras:

  • utilización de macros
  • mediante el formato condicional

De hecho, en el siguiente video explicamos cómo podemos resaltar la fila y columna activa utilizando para ello el formato condicional.

Utilización de la celda de enfoque

Para activar la funcionalidad de celda de enfoque tendremos que ir a la pestaña Vista, y dentro del grupo Mostrar tenemos la opción Celda de enfoque.

Si pulsamos ese botón se activará la celda de enfoque y, cada vez que seleccionemos una celda, se resaltará su fila y columna.

Para desactivar está opción haremos de nuevo clic sobre el botón de Celda de enfoque.

Además podemos cambiar el color del resaltado, para le damos al desplegable del botón Celda de enfoque y marcamos la opción Celda de foco Color.

Se abre una paleta de colores para seleccionar y en función del color elegido se mostrará el color del resaltado.

Si, por ejemplo elegimos el verde, aparecerá de la siguiente forma.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Aumentar interlineado en impresión

Cuando tenemos una serie de datos y, a la hora de imprimirlos, queremos imprimirlos dejando un mayor interlineado no es necesario insertar filas en blanco en medio.

La mejor manera de hacerlos es seleccionando todos los datos y aumentando el alto de la fila. Lo mejor es seleccionando la línea que separa el número de las filas y arrastrando hasta obtener la altura deseada.

Si vamos a la Vista previa de impresión comprobamos que existe un interlineado más amplio.

Si queremos que se incluyan las líneas de división no es necesario seleccionar todos los datos y dibujar los bordes de las celdas.

Una forma más sencilla es ir a Vista previa de impresión y pulsando sobre el enlace de abajo de Configurar página.

Se abriría la ventana Configurar página, donde debemos seleccionar la pestaña Hoja y en el apartado Imprimir marcamos la opción Líneas de división.

De esa forma se visualizarán las líneas de división de las celdas correspondientes.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Síguenos

Para aprovechar al máximo el potencial de Excel. Desde un nivel básico hasta un nivel experto.

Ir al blog

Mi canal de Youtube

Contacto

No te pierdas las novedades

100% libre de spam.