La condición de selección en Excel se configura principalmente mediante la Validación de datos, que convierte una celda en una lista de opciones entre las que el usuario puede elegir. Esta herramienta guía la introducción de datos de forma coherente, reduce los errores tipográficos y evita que las fórmulas trabajen con valores escritos de distintas maneras.
Para crear una condición de selección, se prepara una lista de valores, se selecciona la celda o el rango de celdas donde se permitirá la elección, y se accede a Datos > Validación de datos. En el cuadro Permitir se selecciona Lista y en Origen se indica el rango o los valores disponibles.
Qué es una condición de selección en Excel
Las listas de validación de datos son conocidas como listas desplegables ya que al seleccionar la celda que contiene dicha lista, se mostrará un control en su extremo derecho que desplegará la lista de valores disponibles.
Cuando compartimos un libro de Excel con otras personas y ellos introducen información en las hojas, es de suma importancia validar los datos de manera que se eviten errores en las fórmulas o que se haga una mala interpretación al momento analizar los datos.
En esta ocasión centraremos nuestra atención en una de las opciones de la Validación de datos que son las listas de validación de datos, también conocidas como listas desplegables, y que convierten a una celda en una lista de opciones de las cuales el usuario podrá elegir alguna.
Ventajas de utilizar listas de validación
Las listas desplegables de Excel guían a los usuarios para que introduzcan datos de forma coherente, lo que reduce las posibilidades de errores tipográficos o entradas incorrectas.
Las listas de validación de datos son de mucha utilidad para evitar que un usuario introduzca cadenas de texto con errores de captura y en su lugar le permite elegir una opción dentro de una lista.
De esta manera se evita cualquier error en la captura de información que podría ser costoso posteriormente.
Supongamos que has compartido tu libro de Excel con varias personas y les has pedido que en una celda ingresen su día favorito de la semana. Imaginemos que el miércoles es el día favorito de 5 personas, pero han ingresado el nombre de diferentes maneras: Miércoles, Miercoles, Mercoles, Miercole y Miecoles.
Si tuviéramos que contar la cantidad de personas que han ingresado el miércoles como su día favorito, sería imposible de hacerlo con fórmulas porque cada cadena de texto es diferente.
Para nuestro ejemplo la solución sería realizar un análisis visual para corregir los datos, pero si tuviéramos un caso donde fuera necesario revisar la respuesta de cientos o miles de usuarios, estaríamos frente a un problema que podríamos haber evitado.
Crear una lista desplegable básica
Preparar los datos de origen
En un rango de la hoja deberás ingresar los valores que serán parte de la lista.
Antes de crear un menú desplegable, decide qué elementos incluir en la lista. Puedes escribir estas opciones directamente al configurar el menú desplegable o enumerarlas en celdas de la hoja de cálculo.
Idealmente, tendrá los elementos de lista en una tabla de Excel. Si no lo hace, puede convertir rápidamente la lista en una tabla seleccionando cualquier celda del rango y presionando Ctrl+T.
¿Por qué colocar los datos en una tabla? Cuando los datos se encuentran en una tabla, al agregar o quitar elementos de la lista, se actualizan automáticamente las listas desplegables basadas en esa tabla.
No necesita realizar ninguna otra acción. Ahora es un buen momento para Ordenar datos en un rango o tabla en la lista desplegable.
Aplicar la validación de datos
En seguida, seleccionamos la celda o celdas donde queremos que se muestre la lista.
Vamos a la pestaña Datos > Herramientas de datos > Validación de datos.
En el cuadro de diálogo elige la opción Lista.
En el cuadro de texto Origen, pulsa el botón de selección que se muestra en el extremo derecho y selecciona el rango de celdas que contiene los elementos de la lista que preparamos anteriormente.
Haz clic en Aceptar y habremos creado las listas desplegables.
Habrá una lista desplegable en cada una de las celdas del rango que hayamos seleccionado antes de iniciar el proceso de creación.
Procedimiento resumido
- Selecciona la celda o el rango de celdas donde deseas que aparezca el menú desplegable.
- Ve a la pestaña Datos en la cinta y haz clic en Validación de datos.
- En el cuadro de diálogo que aparece, en Permitir, selecciona Lista.
- En el cuadro Origen, escribe los valores directamente o haz referencia al rango de celdas donde se encuentra la lista.
- Pulsar OK para terminar.
- Haz clic en una de las celdas validadas.
Verás una pequeña flecha a la derecha, donde podrás seleccionar tu entrada de la lista.
Si introduces manualmente un elemento que no habías predefinido en la celda, aparecerá un error.
Esta validación ayuda a evitar errores durante la introducción de datos.
Crear listas desplegables con valores escritos directamente
En el ejemplo anterior, ingresamos las opciones de la lista en un rango de la hoja y posteriormente configuramos ese rango como el origen de la lista.
Si tu lista tiene pocas opciones, tal vez dos o tres opciones, una alternativa es ingresar los valores de la lista directamente en el cuadro de texto Origen.
En el cuadro de diálogo Validación de datos, después de elegir la opción Lista, ingresa los elementos de la lista separados por una coma de la siguiente manera:
En este ejemplo nuestras listas tendrán disponibles las opciones: Si y No.
Este método es ideal para listas desplegables que tendrán muy pocos elementos y que además no necesitarán remover o agregar nuevos elementos a la lista.
Por el contrario, si tienes listas desplegables que tendrán muchos elementos o donde necesitarás continuamente eliminar o agregar elementos, entonces es recomendable colocarlos en un rango de la hoja tal como se indicó en la sección anterior.
Crear una lista desplegable con un rango nombrado
Una tercera alternativa para definir los elementos de la lista desplegable es utilizar un rango nombrado.
Un rango nombrado no es más que una etiqueta que le colocaremos a un rango para utilizarla en lugar de su dirección.
Uno de los métodos para crear un rango nombrado es seleccionar las celdas que queremos nombrar y en el cuadro de nombres escribimos el nuevo nombre de rango.
En la siguiente imagen se observa la selección del rango K1:K7 y en el cuadro de nombres se ha ingresado el texto Semana.
Al terminar de escribir el texto en el cuadro de nombres, pulsamos la tecla Enter y habremos creado el rango nombrado.
Al momento de crear la lista desplegable podemos usar el nombre de rango como el origen de los datos de la siguiente manera.
Es importante ingresar primero el símbolo igual seguido del nombre del rango para que Excel lo reconozca adecuadamente.
Usar los datos de una tabla como origen
Las tablas en Excel tienen muchos beneficios, como el ordenamiento de los datos y el hecho de que podemos agregar fácilmente nuevos registros y Excel los detectará automáticamente.
Una cuarta alternativa que tenemos disponibles para crear una lista desplegable es usar los datos de una tabla y así obtener el beneficio de la actualización automática de sus elementos conforme la tabla vaya creciendo.
Sin embargo, Excel no nos permite usar directamente las referencias estructuradas para establecer el Origen de una lista desplegable.
Es evidente que no podemos usar este tipo de referencias como el origen de la lista desplegable y por lo tanto será necesario utilizar un método alterno para que Excel tome los datos de la tabla.
Opción 1: crear un nombre de rango intermedio
Los nombres de rango no tienen problema en usar referencias estructuradas y por lo tanto podemos usarlos como un intermedio para llegar a los datos de la tabla.
Para crear un nombre de rango vamos a la pestaña Fórmulas > Nombres definidos > Asignar nombre.
Una vez que se abre el cuadro de diálogo Nombre nuevo, colocamos un nombre de nuestra preferencia y que para este ejemplo he elegido el nombre MiLista.
En el cuadro de texto Se refiere a colocamos la referencia estructurada a la columna Vendedor de la Tabla1.
=Tabla1[Vendedor]
Pulsamos el botón Aceptar y se habrá creado el nombre.
Para crear nuestra lista desplegable, activamos la celda donde queremos crear la lista y vamos a Datos > Herramientas de datos > Validación de datos y elegimos la opción Lista.
En la caja de texto Origen colocaremos el nombre de rango que acabamos de crear.
=MiLista
De esta manera, el origen de la lista será el nombre MiLista, el cual a su vez hará referencia a la columna Tabla1[Vendedor].
Opción 2: usar la función INDIRECTO
El método anterior es funcional, pero requiere de un elemento intermedio, como es el caso del nombre de rango.
Si queremos usar un método directo y acceder a los datos de una tabla desde el origen de la lista desplegable, podemos usar la función INDIRECTO.
Esta función toma una cadena de texto y la convierte en una referencia válida por lo que la podemos usar al momento de definir el origen de la lista desplegable.
Para probarlo, activamos una celda donde crearemos la lista desplegable y vamos al comando Datos > Herramientas de datos > Validación de datos y elegimos la opción Lista.
Como el origen de los datos colocaremos la siguiente fórmula:
=INDIRECTO("Tabla1[Vendedor]")
Es importante encerrar en comillas dobles la referencia estructurada porque la función INDIRECTO estará esperando una cadena de texto como su argumento.
Al hacer clic en Aceptar tendremos una lista desplegable con todos los elementos de la columna Vendedor de la Tabla1.
Listas desplegables dinámicas
Las listas desplegables dinámicas se actualizan automáticamente cada vez que cambias los datos de origen.
Al convertir la lista de origen en una tabla, permites que Excel incluya automáticamente los nuevos elementos en la lista desplegable a medida que se añaden.
Si tu lista tiene duplicados, es recomendable utilizar primero la función « UNIQUE() » para extraer los valores distintos.
Por ejemplo, si tus datos están en «A2:A21», puedes utilizar la fórmula siguiente en otro lugar para crear una lista más clara para tu menú desplegable.
=UNIQUE(A2:A21)
A continuación, puedes utilizar este rango de salida como fuente para tu menú desplegable.
También puedes utilizar la función « OFFSET() » (Convertir en tabla) si tu lista crece, pero no deseas convertirla en una tabla formal.
=OFFSET(ListData!$A$2, 0, 0, COUNTA(ListData!$A:$A) -1)
Las listas desplegables dinámicas se utilizan en formularios en vivo, hojas de seguimiento u hojas de cálculo colaborativas.
Esta función garantiza las actualizaciones automáticas cuando los usuarios introducen datos o cuando estos cambian.
Listas desplegables dependientes o en cascada
Las listas desplegables dependientes (listas desplegables en cascada) son conjuntos de menús desplegables en los que las opciones de una lista dependen de la selección realizada en otra.
Son ideales para datos jerárquicos, como categorías y subcategorías.
Al crear las listas desplegables dependientes, primero creas rangos con nombre para cada grupo de subelementos.
El segundo menú desplegable utiliza la función « INDIRECT() » (Referencia de rango) para hacer referencia al rango con nombre correspondiente a la primera selección.
Preparar los datos de origen
Crea una lista de categorías y subcategorías en columnas separadas siguiendo una secuencia concreta.
Asegúrate de que cada rango de subcategorías se denomine con el nombre de «Categoría» adecuado tal y como aparece en la lista.
Crear la primera lista
En el primer menú desplegable, selecciona el rango de categorías principales.
Utiliza«Validación de datos» > « » y establece el origen en el rango de la columna «Categoría».
Este paso debería ser similar al que hemos utilizado anteriormente.
Crear la lista dependiente
A continuación, configura el menú desplegable de subcategorías.
Para la fuente, selecciónala en la primera lista desplegable.
=INDIRECT(A2)
Comprobar errores en listas dependientes
Asegúrate de que los rangos de la subcategoría nombrada coincidan con el texto del menú desplegable de la categoría principal.
No debes dejar espacios adicionales y debes respetar las mayúsculas y minúsculas.
Si INDIRECT() devuelve un error « #REF! », comprueba que los rangos nombrados existen y se corresponden con los valores de la lista principal.
Mensajes de entrada y alertas de error
Excel te permite adjuntar mensajes cortos a las celdas desplegables para ayudar a los usuarios a realizar la selección correcta.
Para configurar un mensaje de entrada:
- Selecciona la celda desplegable.
- Ir a Datos > Validación de datos.
- Cambia a la pestaña Mensaje de entrada.
- Introduce un título y un mensaje como «Elige un departamento de la lista».
Aparecerá la flecha desplegable con un mensaje junto a la celda seleccionada.
Esto ayudará al usuario a comprender los datos que se requieren para el campo.
Puedes crear alertas de «Error» que aparecerán si alguien intenta introducir datos que no coinciden con las opciones del menú desplegable.
Para personalizar esta función:
- Ir a Validación de datos > Alerta de error.
- Elige el estilo de alerta: Detener, Advertencia o Información.
- Proporciona un mensaje que explique la restricción, como «Selecciona un departamento válido de la lista».
Utiliza siempre un lenguaje claro y fácil de entender tanto en los mensajes de entrada como en las alertas de error para mejorar la claridad de los mensajes.
Tipos de alerta
Para mostrar un mensaje que no impide que los usuarios introduzcan datos que no están en la lista desplegable, seleccione Información o Advertencia.
La información mostrará un mensaje con este icono y la advertencia mostrará un mensaje con este icono.
Para impedir que los usuarios introduzcan datos que no están en la lista desplegable, seleccione Detener.
Si no agrega un título o un texto, de forma predeterminada el título será "Microsoft Excel" y el mensaje será: "Valor no válido. El usuario solo puede escribir ciertos valores en esta celda".
Permitir entradas manuales
A veces es posible que quieras añadir elementos a tu lista sin pasar por la validación original.
Si deseas añadir datos personalizados:
- Selecciona la celda o el rango de celdas donde deseas que aparezca el menú desplegable.
- Ir a Datos > Validación de datos.
- En la pestaña Alerta de error, desmarca la casilla «Mostrar alerta de error después de introducir datos no válidos».
Al desmarcar esta opción, podrás introducir cualquier valor que no se encuentre en la lista desplegable de opciones.
Aunque permitir la introducción manual aumenta la flexibilidad, puede dar lugar a datos incoherentes o no válidos si los usuarios cometen errores al escribir o introducen valores inesperados.
También reduce la ventaja de tener una lista controlada.
Para gestionar entradas no incluidas en la lista:
- Utiliza el formato condicional para marcar las celdas con valores que no se encuentran en la lista validada para su revisión.
- Crea columnas auxiliares que comprueben la validez.
- Por ejemplo, utiliza COUNTIF() para verificar si la entrada existe en la lista de fuentes.
- Audita y actualiza periódicamente tus listas de fuentes para incluir nuevas entradas válidas.
Actualizar una lista desplegable
Hasta ahora hemos visto 4 métodos para definir el origen de una lista desplegable en Excel y es un hecho que los elementos de esas listas tendrán que ser actualizados en algún momento ya sea eliminando o agregando nuevos elementos.
La actualización dependerá de la manera en que hayamos definido el origen de los datos.
Si creaste tu lista desplegable mediante entrada manual, simplemente añade el nuevo elemento al final de la lista.
También puedes añadir el nuevo elemento al rango de celdas si has seleccionado «Origen» como rango de celdas.
Si estás haciendo referencia a tu lista desde una tabla de Excel, escribe el nuevo valor debajo de la última fila.
Excel ampliará y actualizará automáticamente la tabla, lo que también actualizará tu lista.
Eliminar una lista desplegable
Puedes eliminar una lista desplegable de tu hoja de Excel sin borrar los datos que ya has introducido.
Para eliminar una lista desplegable creada con la validación de datos:
- Selecciona la celda o el rango que contiene el menú desplegable.
- Ve a la Datos > Validación de datos.
- En el cuadro de diálogo, haz clic en Borrar todo > Aceptar.
Este método elimina la regla de validación y la flecha desplegable.
Los valores de celda existentes permanecen intactos, pero ya no están restringidos a las opciones desplegables anteriores.
Finalmente te mostraré cómo remover una lista de validación de datos de una celda.
Lo primero será seleccionar la celda, o celdas, que contienen la lista desplegable y en seguida pulsar el comando Validación de datos para abrir las opciones de configuración de la lista.
Para remover la lista de la celda deberás seleccionar la opción Cualquier valor y pulsar el botón Aceptar.
Con esto la celda dejará de tener validación de datos y se comportará como el resto de las celdas de la hoja.
Formato condicional para destacar las selecciones
El formato condicional es una característica de Excel que ayuda a analizar visualmente los datos.
Funciona destacando las celdas a través de escalas de colores, barras de datos e iconos, lo que facilita la interpretación de los datos.
Además, esta característica permite la identificación de problemas, patrones o tendencias.
El formato condicional puede ayudar a que los patrones y tendencias en sus datos sean más visibles.
Para usarlo, cree reglas que determinen el formato de las celdas en función de sus valores, como los siguientes datos de temperatura mensual con colores de celda vinculados a valores de celda.
Aplicar formato condicional
Para aplicar el formato condicional, lo primero que debes hacer es seleccionar las celdas en las que deseas dar formato.
Para ello tienes que ir a la cinta Inicio, seleccionar grupo Estilos, hacer clic en Formato condicional y finalmente elegir uno de los formatos disponibles.
Puede aplicar formato condicional a un rango de celdas, tanto a una selección como a un rango con nombre, a una tabla de Excel y, en Excel para Windows, incluso a un informe de tabla dinámica.
Reglas para resaltar celdas
La primera relación que vas a encontrar del formato condicional es la de reglas para resaltar celdas.
Esta característica te permite resaltar celdas con colores.
Las opciones disponibles permiten establecer que:
- Las celdas deben tener valores mayores que, menores que o iguales a un valor dado.
- Las celdas deben encontrarse entre un rango de valores.
- Las celdas deben contener un texto específico o una fecha específica.
- Las celdas deben tener valores duplicados.
Cuando eliges una de estas opciones, aparece una nueva ventana.
En esta ventana puedes colocar las condiciones y elegir el formato.
Existe la opción de elegir uno de los formatos predefinidos, o también, puedes personalizarlo a tu gusto.
Las características que puedes personalizar son desde el color y la fuente, hasta los contornos y el relleno.
Reglas para valores superiores e inferiores
Esta característica te permiten resaltar los valores más altos o más bajos según ciertos criterios.
- 10 superiores
- 10% de valores superiores
- 10 inferiores
- 10% de valores inferiores
- Por encima del promedio
- Por debajo del promedio
En el caso de las opciones 10 superiores y 10 inferiores, puedes elegir la cantidad de elementos que deseas resaltar.
Por otro lado en las opciones 10% de valores superiores y 10% de valores inferiores puedes elegir el porcentaje deseado.
Barras de datos
Las “barras de datos” son la tercera relación.
Estas te permiten comparar visualmente el valor de una celda con otras.
La barra más grande representa el valor más alto y la más pequeña representa el valor más bajo.
Este tipo de regla es útil para encontrar los valores más altos y más bajos, especialmente cuando tenemos que analizar grandes cantidades de datos.
La única diferencia entre las distintas opciones presentadas son los colores y los efectos de las barras.
Escalas de color
Las escalas de colores son guías visuales que ayudan a comprender la variación y la distribución de datos.
Una escala de dos colores permite comparar un rango de celdas usando una gradación de dos colores.
El tono del color representa los valores superiores o inferiores.
Por ejemplo, en una escala de verde y amarillo, como se muestra abajo, se puede especificar que las celdas de valor superior tengan un color más verde y las celdas de valor inferior tengan un color más amarillo.
Las escalas de color te permiten percibir visualmente la distribución de datos a través de colores.
Puedes elegir escalas de 2 o 3 colores.
Una escala de tres colores permite comparar un rango de celdas usando una gradación de tres colores.
El tono de color representa los valores superiores, medios o inferiores.
Por ejemplo, en una escala de colores verde, amarillo y rojo, se puede especificar que las celdas con el valor superior tengan un color verde, las celdas de valor medio tengan un color amarillo y las celdas de valor inferior tengan un color rojo.
Conjuntos de iconos
Esta característica te permite ordenar tus datos de tres a cinco categorías, según los valores que establezcas.
Cada icono representa un rango de valores.
Puedes cambiar los valores del rango, los iconos u ocultar el valor y mostrar solo el icono.
Para hacer esto, solo tienes que ir a Formato condicional, elije Administrar reglas y haz clic en Editar regla.
Formato personalizado para condiciones de selección
Insertamos dos números el 1 y el 0, lo seleccionamos y damos clic derecho, en la opción Formato de celdas… damos clic luego se nos abrirá una ventana emergente y damos clic en Personalizar y colocamos lo siguiente «Pasa»;;»Falla», lo cual pasa irá al uno y falla irá al cero.
De esta manera, el valor numérico puede conservarse para las fórmulas mientras se muestra un texto más comprensible para el usuario.
Formato condicional con iconos para valores 1 y 0
Pestaña Inicio, Formato condicional, elegimos la opción Nueva regla…
En la ventana emergente damos clic en el tipo de regla :Aplicar formato a todas las celdas según sus valores, luego en Estilo de formato seleccionamos Conjunto de Iconos, damos un check a Mostrar icono únicamente luego en Icono elegimos los dos primeros iconos y en valor le damos el primero mayor que cero >0 será un aspa verde y la X roja será mayor o igual a cero >=0
Evitar errores con SI.ERROR
Para no tener error como datos cuando arrastramos nuestra fórmula insertamos la función =SI.ERROR(PROMEDIO(C3;G3);»»), el C3;G3 viene a ser nuestra fila seleccionada y las comillas del final es que si no encuentra valores que se quede en blanco.
Si una o varias celdas de la selección contienen una fórmula que devuelve un error, el formato condicional no se aplica a ninguna celda del rango.
Para garantizar que el formato condicional se aplique a todo el rango, use una función ES oSIERROR para devolver un valor, como 0 o "N/A", que no sea un valor de error.
Usar Análisis rápido para aplicar formato condicional
Utilice el botón Análisis para aplicar el formato condicional seleccionado a los datos seleccionados.
El botón Análisis rápido aparece automáticamente al seleccionar datos.
- Seleccione los datos donde quiera aplicar el formato condicional.
- El botón Análisis rápido aparece en la esquina inferior derecha de la selección.
- Haga clic en Análisis rápido o presione Ctrl+Q.
- En la ventana emergente, en la pestaña Formato, pase el puntero del mouse por las diferentes opciones para visualizar una Vista previa dinámica de los datos y haga clic en la opción de formato que desee.
Las opciones de formato que aparecen en la pestaña Formato dependerán de los datos que haya seleccionado.
Si la selección contiene solo texto, a continuación, las opciones disponibles son Texto, Duplicar, Único, Igual que y Borrar.
Cuando la selección contiene solo números, o texto y números, las opciones son Barras de datos, Colores, Conjuntos de iconos, Mayor que, Superior 10 % y Borrar.
La vista previa dinámica solo se representará para las opciones de formato que pueden usarse en sus datos.
Por ejemplo, si las celdas seleccionadas no contienen de coincidencia de datos y selecciona Duplicar, la vista previa dinámica no funcionará.
Si aparece el cuadro de diálogo Texto que contiene, escriba la opción de formato que desea aplicar y haga clic en Aceptar.
Administrar reglas de formato condicional
En la pestaña Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, a continuación, en Administrar reglas.
Aparecerá el cuadro de diálogo Administrador de reglas de formato condicionales.
Para agregar un formato condicional completamente nuevo, haga clic en Nueva regla.
Aparecerá el cuadro de diálogo Nueva regla de formato.
Para agregar un nuevo formato condicional basado en uno existente, seleccione la regla y, a continuación, haga clic en Duplicar regla.
La regla duplicada aparecerá en el cuadro de diálogo.
Seleccione el duplicado y, después, Editar regla.
Aparecerá el cuadro de diálogo Editar regla de formato.
Para cambiar un formato condicional, asegúrese de haber seleccionado la hoja de cálculo, tabla o informe de tabla dinámica correctos en el cuadro de lista Mostrar reglas de formato para.
Opcionalmente, puede cambiar el rango de celdas.
Para ello, haga clic en Contraer diálogo en el cuadro Se aplica a para ocultar temporalmente el cuadro de diálogo, seleccione el nuevo rango de celdas de la hoja de cálculo y luego seleccione Expandir diálogo.
Seleccione la regla y después haga clic en Editar regla.
Aparecerá el cuadro de diálogo Editar regla de formato.
Escalas de colores con configuración avanzada
Seleccione una o más celdas de un rango, tabla o informe de tabla dinámica.
En la pestaña Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, a continuación, haga clic en Borrar reglas.
Seleccione una escala de dos colores.
Mantenga el mouse sobre los iconos de escala de color para ver cuál corresponde a una escala de dos colores.
El color superior representa valores superiores y el color inferior representa valores inferiores.
Puede ordenar las celdas con este formato por su color, solo tiene que usar el menú contextual.
Tipos para valores mínimos y máximos
Para seleccionar un tipo en el cuadro Tipo para Mínima y Máxima, siga uno de los procedimientos siguientes:
- Aplicar formato a los valores inferiores y superiores: Seleccione Valor más bajo y Valor más alto.
- Aplicar formato a un valor de número, fecha u hora: Seleccione Número y después escriba un valor mínimo y un valor máximo.
- Aplicar formato a un porcentaje: Escriba un valormínimo y máximo.
- Aplicar formato a un percentil: Seleccione Percentil y después escriba un valor mínimo y un valor máximo.
- Aplicar formato al resultado de una fórmula: Seleccione Fórmula y, a continuación, escriba valores en Mínima y Máxima.
Los valores válidos son del 0 (cero) al 100.
No escriba un signo de porcentaje.
Use un porcentaje cuando desee ver todos los valores proporcionalmente porque la distribución de los valores es proporcional.
Los valores de percentiles válidos son del 0 (cero) al 100.
Use un percentil cuando desee ver un grupo de valores altos, como el percentil 20superior, en una proporción de escala de color y un grupo de valores bajos, como el percentil 20inferior, en otra proporción de escala de color porque representan valores extremos que podrían sesgar la presentación de sus datos.
La fórmula debe devolver un valor de número, de fecha o de hora.
Empiece la fórmula con un signo igual (=).
Las fórmulas no válidas hacen que no se aplique ningún formato.
Se recomienda probar la fórmula para asegurarse de que no devuelve ningún valor de error.
Asegúrese de que el valor de Mínima sea menor que el valor de Máxima.
Puede elegir tipos diferentes en Mínima y Máxima.
Por ejemplo, puede elegir un número en Mínima y un porcentaje en Máxima.
Para elegir una escala de color Mínima y Máxima, haga clic en Color para cada una y después seleccione un color.
Si desea elegir colores adicionales o crear un color personalizado, haga clic en Más colores.
La escala de colores que seleccione aparecerá en el cuadro Vista previa.
Formato condicional en tablas dinámicas
Normalmente, el formato condicional funciona de la misma manera en un rango de celdas, una tabla de Excel o un informe de tabla dinámica.
Sin embargo, el formato condicional en un informe de tabla dinámica tiene algunas consideraciones adicionales.
Algunos formatos condicionales no funcionan con los campos en el área Valores de un informe de tabla dinámica.
Por ejemplo, no se puede dar formato a esos campos en función de si contienen valores únicos o duplicados.
Estas restricciones se mencionan en el resto de las secciones de este artículo donde sea apropiado.
Si se cambia el diseño del informe de tabla dinámica filtrando, ocultando, contrayendo y expandiendo niveles o moviendo un campo, el formato condicional se mantendrá siempre y cuando no se quiten los campos de los datos subyacentes.
El ámbito del formato condicional para los campos del área Valores puede estar basado en la jerarquía de datos y queda determinado por todos los elementos secundarios visibles, el siguiente nivel inferior de una jerarquía, de un elemento primario, el siguiente nivel superior de una jerarquía, en filas para una o más columnas, o en columnas para una o más filas.
En la jerarquía de datos, los elementos secundarios no heredan el formato condicional del elemento primario y este no hereda el formato condicional de los elementos secundarios.
Métodos para especificar el ámbito
Existen tres métodos para especificar el ámbito del formato condicional de los campos del área Valores: por selección, por campo correspondiente y por campo de valor.
El método predeterminado para especificar el ámbito de los campos del área de valores es por selección.
Puede cambiar este método por el método de campo correspondiente o campo de valor mediante el botón de opción Aplicar regla de formato a, el cuadro de diálogo Nueva regla de formato o el cuadro de diálogo Editar regla de formato.
| Método | Utilícelo cuando desee |
|---|---|
| Especificar el ámbito por selección | Seleccionar un conjunto de campos contiguos o no contiguos en el área Valores. |
| Especificar el ámbito por campo de valor | Evitar seleccionar muchos campos no contiguos y aplicar formato a un conjunto de campos en el área Valores para todos los niveles de la jerarquía de datos. |
| Especificar el ámbito por campo correspondiente | Evitar seleccionar muchos campos no contiguos y aplicar formato a un conjunto de campos en el área Valores para un nivel específico de la jerarquía de datos. |
El método de selección puede utilizarse para un conjunto de campos contiguos en el área Valores, por ejemplo, todos los totales de productos de una región específica.
También puede utilizarse para un conjunto de campos no contiguos en el área Valores, como los totales de productos de diferentes regiones en distintos niveles de la jerarquía de datos.
El método de campo de valor permite incluir subtotales y totales generales.
El método de campo correspondiente permite excluir subtotales.
Cuando se aplica formato condicional a campos en el área Valores para valores superiores, inferiores, superiores al promedio o inferiores al promedio, la regla se basa en todos los valores visibles de forma predeterminada.
No obstante, cuando se especifica el ámbito por el método de campo correspondiente, en lugar de usar todos los valores visibles puede aplicar el formato condicional a cada combinación de una columna y el campo de su fila principal, o una fila y el campo de su columna principal.
Cuadros combinados y controles avanzados
Cuadros combinados de controles de formulario
Los cuadros combinados de control de formulario funcionan como las listas desplegables estándar, pero permiten a los usuarios vincularlos con otras celdas.
Este método es útil cuando se integran listas desplegables con formularios o paneles.
Para utilizar los cuadros combinados:
- Ve a la pestaña Programadores.
- Haz clic en Insertar > Controles de formulario, selecciona Cuadro combinado.
- Dibuja el cuadro combinado en tu hoja de trabajo.
- Haz clic con el botón derecho del ratón en el control y selecciona Formato de control.
- En la pestaña Control, configura el «Rango de entrada» y un «Vínculo de celda» donde aparecerá el índice del elemento seleccionado.
Observarás que el cuadro combinado devuelve un número correspondiente a la posición del elemento en la lista.
Puedes recuperar el valor real de la posición del elemento utilizando la función INDEX().
Los controles de formulario son preferibles cuando se crean paneles o informes interactivos.
También se pueden utilizar en situaciones en las que no es necesario VBA, pero se necesita un formato más flexible que el que permite la validación de datos estándar.
Cuadros de control ActiveX
Los cuadros de control ActiveX ofrecen más potencia y personalización, incluyendo control de fuentes, autocompletado y la capacidad de activar macros en función de la interacción del usuario.
Para añadir los cuadros de control ActiveX:
- Ir a Programadores > Insertar > Controles ActiveX > Cuadro combinado.
- Dibuja el recuadro en la hoja.
- Haz clic con el botón derecho y selecciona Propiedades para configurarlo como «ListFillRange», «LinkedCell» y, a continuación, personaliza las fuentes, los colores de fondo y los estilos de borde.
La ventaja de utilizar el cuadro combinado ActiveX es que ofrece una mayor flexibilidad de formato para las fuentes, los colores y el diseño.
También permite la programación basada en eventos para formularios y aplicaciones altamente interactivos.
Esta función se integra con macros y automatización.
Sin embargo, los controles ActiveX solo funcionan en Windows y no son compatibles con Excel para Mac ni con Excel Online.
También son más pesados que los controles de formulario y pueden ralentizar el rendimiento en cuadernos de trabajo grandes.
Para un uso avanzado, es posible que necesites algunos conocimientos de VBA.
Listas desplegables con función de búsqueda
En las versiones modernas de Excel, como Microsoft 365 y Excel para la web, puedes utilizar la función de menú desplegable con búsqueda, especialmente cuando navegas por una lista larga de elementos.
Al hacer clic en la flecha desplegable, puedes empezar a escribir y Excel filtrará la lista para que coincida con lo que has introducido.
Esta función es importante cuando se trabaja con listas largas, como nombres de clientes, referencias de productos o nombres de países.
Sin embargo, los menús desplegables con función de búsqueda solo están disponibles en las versiones recientes de Excel y no en versiones de escritorio anteriores, como Excel 2016 o 2019.
Si utilizas versiones anteriores de Excel, utiliza cuadros combinados o controles de formulario con funciones de búsqueda integradas a través de VBA para crear listas desplegables en las que se pueda buscar.
Trabajo con una lista desplegable
Tras crear su lista desplegable, asegúrese de que funciona de la manera que desea.
Por ejemplo, puede interesarle comprobar cómo Cambiar el ancho de columna y el alto de fila para mostrar todas sus entradas.
Si la lista de entradas para lista desplegable está en otra hoja de cálculo y desea evitar que los usuarios la vean o realicen cambios, piense en ocultar y proteger dicha hoja de cálculo.
Si decide que desea cambiar las opciones de su lista desplegable, vea Agregar o quitar elementos de una lista desplegable.
No es mala idea invertir un poco de tiempo para revisar nuestras hojas de cálculo para saber si podemos implementar la validación de datos y las listas desplegables ya que nos evitarán muchos problemas al permitir solo el ingreso de valores previamente validados.