Macro para desplazarse entre hojas

Al construir un reporte en Excel es común mostrar un resumen de la información en la primera hoja que nos permitirá consultar el detalle que se encuentra en el resto de las hojas del libro y que también ofrecen información relevante. Nuestro objetivo es hacer que el usuario llegue al detalle que está buscando de una manera sencilla. 

Una buena alternativa para crear este sistema de “navegación” en nuestro libro, es colocar un botón para moverse entre hojas. De esta manera podemos guiar a los usuarios hacia las hojas que contienen todo el detalle y evitar que ellos tengan que visitar cada una de las hojas en busca de la información.

Moverse a otra hoja con la grabadora de macros

El método más sencillo para crear un botón que se desplace a otra hoja es a través de la grabadora de macros. Debes ubicarte en la hoja inicial y hacer clic en el botón Grabar macro que se encuentra en la ficha Vista > Macros > Grabar macro o que también puedes iniciar con el icono ubicado en el extremo lateral izquierdo de la barra de estado. Al pulsar ese botón se mostrará el cuadro de diálogo Grabar macro.

Una vez que hayas especificado un nombre adecuado para la macro haz clic en Aceptar y solamente deberás realizar una sola acción: Hacer clic sobre la pestaña de la hoja a donde deseas moverte.

Inmediatamente después deberás detener la grabación pulsando el botón Detener grabación que se encuentra en la barra de estado de Excel. Con esto hemos creado la macro que nos ayudará a movernos a una hoja dentro del libro y solamente nos restará agregar el botón a la hoja. Para agregar este botón debes hacer clic en el botón desplegable Insertar que se encuentra dentro del grupo Controles de la ficha Programador y seleccionar el elemento Botón.

El puntero del ratón te permitirá definir el tamaño del botón sobre la Hoja y al terminar de dibujarlo se mostrará el cuadro de diálogo Asignar macro.

Lo único que tenemos que hacer es seleccionar la macro que recién acabamos de crear y pulsar el botón Aceptar. Al pulsar el botón recién creado nos moveremos hacia la nueva hoja.  De esta misma manera podemos grabar una macro diferente que nos lleve de regreso a la hoja principal. Observa la siguiente animación en donde ya he creado una segunda macro que me permite regresar a la Hoja inicial:

Analizando el código VBA

Si analizas el código creado con la grabadora de macros, podremos aprender un poco sobre programación VBA. Las macros que fueron creadas generaron un código que puedes ver en el Editor de Visual Basic y que será similar al siguiente:

Observa que el objeto Sheets tiene un método llamado Select el cual hace la selección de dicha hoja:

Sheets("Hoja1").Select

Esta instrucción siempre seleccionará la Hoja1 independientemente de la hoja en la que me encuentre. De esta manera, la macro que creamos la podemos asignar a un botón en cualquier otra hoja de nuestro libro y siempre nos moveremos a laHoja1.

Moverse entre hojas con VBA

El ejemplo anterior funciona perfectamente siempre y cuando no se cambien los nombres de las hojas del libro ya que si esto llega a suceder entonces nuestra macro dejará de funcionar. Para evitar este problema debemos utilizar el nombre del objeto en VBA de manera que sin importar que cambie de nombre podremos movernos hacia dicha hoja. En la siguiente imagen puedes observar cómo he renombrado la Hoja1 con el nombre Principal y que se ve reflejado en el Explorador de proyectos:

Para garantizar que el botón que nos mueve a la hoja Principal funcione adecuadamente debo modificar el código VBA de la siguiente manera:

Con la nueva instrucción (Hoja1.Select) estaremos utilizando el nombre interno del objeto y no importará si cambia de nombre la hoja, nuestro sistema de navegación siempre funcionará adecuadamente.

Instrucciones VBA adcionales para cambiar de hoja

Algunas instrucciones VBA que pueden ser de utilidad al movernos entre hojas son las siguientes:

Sheets(Sheets.Count).Select

Esta instrucción siempre nos moverá a la última hoja de nuestro libro.

Sheets(1).Select

Esta instrucción nos moverá a la primera hoja de nuestro libro.

Con los métodos e instrucciones presentados en este artículo estás listo para crear tus propios botones de navegación en tus reportes de Excel. Descarga el libro de trabajo utilizado en este artículo y revisa el código VBA creado en este ejemplo.

Formato de fechas y horas

Las fechas son un tipo de dato muy común dentro de Excel y aprender a darles el formato adecuado es de suma importancia para poder presentar la información de manera adecuada. De igual manera el formato de horas es importante.

Formatos de fecha y hora en Excel

De manera predeterminada en la lista de formatos que se encuentra en la ficha Inicio dentro del grupo Número tenemos un par de formatos para fecha y otro para hora.

Sin embargo en el cuadro de diálogo Formato de celdas tenemos muchas más opciones de donde elegir. Asegúrate de elegir la categoría Fecha o la categoría Hora para tener acceso a los diferentes tipos de formato adicionales:

Fecha y hora en la misma celda

Es posible tener celdas que contengan un valor tanto de fecha como de hora. Esto es posible al separar la información por un espacio tal como se muestra a continuación:

Observa cómo la barra de fórmulas muestra el contenido de la celda tanto con la fecha como con la hora sin embargo el valor de la celda solamente muestra la fecha. Esto se debe a que la celda tiene un formato de fecha. Si cambiamos al formato de la celda por un formato de hora entonces la celda se mostrará de manera diferente:

Para que la celda muestre ambos datos debe tener un formato personalizado el cual podemos elegir dentro del cuadro de diálogo Formato de celdas.

Ingresando fechas y horas en Excel

Excel trata a los datos de tipo fecha y de tipo hora como un tipo especial de datos numéricos y al momento de presentarlos en pantalla les da el formato adecuado para que podamos entender mejor dichos valores.

La primera fecha que Excel reconoce es el día 1 de enero de 1900 y el cual tiene asignado el número de serie 1 y a partir de ahí la cuenta es progresiva de uno en uno. Ya que las fechas son valores numéricos, las operaciones entre fechas se facilitan en Excel.

Formatos de fecha en Excel

La siguiente tabla muestra los formatos de fecha en Excel que son válido y que podemos utilizar en nuestras hojas. Es importante mencionar que estos formatos dependerán de las configuraciones regionales del equipo. La siguiente tabla la he realizado considerando una configuración Español (México).

Texto introducido	Interpretación de Excel
1-08-2011	1 de Agosto de 2011
1-08-11	1 de Agosto de 2011
1/08/2011	1 de Agosto de 2011
1/08/11	1 de Agosto de 2011
2011-08-01	1 de Agosto de 2011
1 Agosto, 2011	1 de Agosto de 2011
1 Agosto	1 de Agosto del año actual
1 Ago	1 de Agosto del año actual
1-8	1 de Agosto del año actual
1/8	1 de Agosto del año actual

Debes poner mucha atención al momento de introducir fechas ya que si no utilizas alguno de los formatos adecuados Excel tratará la información como si fuera una cadena de texto en lugar de fecha.

Observa la última entrada de la tabla. ¿Qué sucede si lo que quiero especificar a Excel es una valor numérico que represente a la fracción 1/8? Si introducimos el texto “1/8” se interpretará como fecha pero si anteponemos el símbolo igual (=) entonces obtendremos el valor fraccionario que buscamos.

Formatos de hora en Excel

Cuando trabajamos con horas en Excel entonces comenzaremos a utilizar valores decimales. Por ejemplo, el valor de serie para el 1 de agosto de 2011 es 40756 y si queremos indicar el medio día de esa misma fecha entonces Excel agregará una parte decimal a ese valor para tener 40756.5 especificando así las horas del día.

Realmente nunca nos preocupamos sobre los números de serie de las fechas o sus fracciones sino que solamente ingresamos en Excel el formato que estamos acostumbrados a utilizar todos los días. A continuación te muestro una tabla con los formatos de hora en Excel que podemos utilizar:

Texto introducido	Interpretación de Excel
9:45 a.m.	9:45 a.m.
9:45 A.M.	9:45 a.m.
9:45 am	9:45 a.m.
9:45 AM	9:45 a.m.
9:45	9:45 a.m.
9:45 p.m.	9:45 p.m.
21:45	9:45 p.m.

En estos ejemplos no existe una fecha asociada a las horas sin embargo puedes hacer una combinación de una fecha con una hora dejando un espacio en blanco entre ambos datos. Por ejemplo, las 9:45 a.m. del día 1 de Agosto del 2011 se especificará en Excel de la siguiente manera:

Lista de validación y función BUSCARV

Las listas de validación nos ayudan a controlar el tipo de información ingresada en nuestra hoja de cálculo. Si combinamos esta funcionalidad con la función BUSCARV podemos crear una tabla que cargue información adicional.
En una hoja de Excel tengo información sobre diferentes archivos ubicados en un servidor de la empresa. Los datos está ubicados en el rango AA1:AE7 tal como se muestra en la siguiente imagen.

Necesito crear una tabla donde al seleccionar el nombre de uno de los archivos se muestren sus datos específicos así como un vínculo directo a su ubicación en la red. Para crear este tipo de tabla debemos seguir los siguientes pasos.

Crear la lista de validación

Primero debemos crear la lista de validación con los nombres de los archivos. Si no recuerdas cómo crear una lista de validación consulta el artículo: Lista para validación de datos en Excel.

El siguiente paso es muy importante porque es donde obtendremos el valor de las demás columnas de acuerdo al valor seleccionado en la lista de validación y para ello utilizaremos la función BUSCARV.

Buscando valores con la función BUSCARV

Después de seleccionar un valor de la columna Archivo se deberán llenar automáticamente el resto de columnas de la tabla. Así que empezaré por mostrar el Tamaño del archivo seleccionado.

Recuerda que los datos se encuentran en el rango de celdas AA1:AE7 por lo que a la función BUSCARV le pido encontrar el valor de la celda A2 dentro dicho rango de datos y que como resultado devuelva el valor de la segunda columna que es precisamente la columna Tamaño. El último argumento de la función indica que la coincidencia en el nombre del archivo tiene que ser exacta.
De la misma manera puedo crear la fórmula para las otras columnas de la tabla. Observa la fórmula para obtener el nombre del Autor.

La función es prácticamente la misma y solamente cambia el tercer argumento que es el número de columna dentro del rango de datos que necesito me regrese la función.
La última columna necesita un trato especial porque quiero crear un hipervínculo de manera que cuando se haga clic sobre él se abra el archivo desde su ubicación en la red. Es por eso que la misma función BUSCARV la coloco dentro de la función HIPERVINCULO de la siguiente manera:

Con esto han quedado configuradas todas las columnas pero nos hace falta algo muy importante.  Seguramente te has fijado que al cargar una lista de validación aparece sin ningún valor seleccionado.

Para este caso todas nuestras fórmulas devuelven un error porque no encuentran un valor vacío dentro de la lista de nombres de archivo. La solución es utilizar la función SI.ERROR para mostrar un mensaje diferente al error #N/A.

Captura de errores con la función SI.ERROR

El uso de la función SI.ERROR para capturar los errores es muy sencillo, solamente debemos incluir el resultado de la función BUSCARV como el primer argumento de la función y el segundo argumento nos permite especificar el texto a mostrar en caso de obtener un error.

En este ejemplo he colocado un guion medio “-“ como el texto a mostrar en caso de que exista un error. Finalmente sugiero crear una tabla de manera que al insertar una nueva fila se cree automáticamente la lista de validación junto con las fórmulas de las demás columnas.


Si lo deseas puedes descargar el archivo de trabajo.

Controles de formulario en Excel

Los controles de formulario en Excel son objetos que podemos colocar dentro de una hoja de nuestro libro, o dentro de un formulario de usuario en VBA, y nos darán funcionalidad adicional para interactuar mejor con los usuarios y tener un mejor control sobre la información.
Podemos utilizar estos controles para ayudar a los usuarios a seleccionar elementos de una lista predefinida o permitir que el usuario inicie una macro con tan solo pulsar un botón. Los controles de formulario en Excel se encuentran dentro de la ficha Programador dentro del grupo Controles. Solamente pulsa el botón Insertar y observarás cada uno de ellos:

Justo por debajo de los controles de formulario podrás observar el grupo de controles ActiveX pero sus diferencias y similitudes las discutiremos en otro artículo. Por ahora nos enfocaremos solamente en los controles de formulario.

¿Cómo insertar un control de formulario en Excel?

Para insertar cualquiera de los controles de formulario debes seleccionarlo del menú desplegable y hacer clic sobre la hoja de Excel arrastrando el borde para “dibujar” el contorno del control. Observa este procedimiento.

Los diferentes controles de formulario

Existen diferentes tipos de controles de formulario en Excel que ofrecen diversos tipos de funcionalidad e interacción con el usuario. Desde una simple etiqueta hasta controles que permiten una selección múltiple de sus opciones. A continuación una breve descripción de cada uno de ellos.

• Barra de desplazamiento. Al hacer clic en las flechas se va desplazando la barra dentro de un intervalo predefinido.
• Botón. El botón nos permite ejecutar una macro al momento de hacer clic sobre él.
• Botón de opción. Nos permite una única selección dentro de un conjunto de opciones.
• Casilla de verificación. Permite la selección o no selección de una opción.
• Control de número.  Nos ayuda a aumentar o disminuir un valor numérico.
• Cuadro combinado. Es una combinación de un cuadro de texto con un cuadro de lista.
• Cuadro de grupo. Agrupa varios controles dentro de un rectángulo.
• Cuadro de lista. Muestra una lista de valores de los cuales podemos elegir una sola opción  o múltiples opciones de acuerdo a la configuración del control.
• Etiqueta. Permite especificar un texto o breves instrucciones en el formulario.

Controles de formulario no disponibles en Excel 2010

Los controles de formulario han estado presentes por varias versiones de Excel, sin embargo a partir de Excel 2010 existen algunos controles que ya no pueden ser utilizados dentro de las hojas como lo son el Campo de texto, el Cuadro combinado de lista y el Cuadro combinado desplegable, sin embargo podremos alcanzar funcionalidad similar utilizando controles ActiveX.

Búsquedas aproximadas en Excel

Cuando tenemos grandes cantidades de datos podemos tener dificultades para encontrar la información que necesitamos. El cuadro de diálogo Buscar y remplazar tiene una característica especial que muchos pasan por alto y es la posibilidad de utilizar caracteres comodín para realizar búsquedas aproximadas.

Palabras diferentes por una sola letra

¿Te ha sucedido que no recuerdas como se escribe el nombre de una persona? Por su puesto que si. Recuerdas muy bien que su apellido es Vásquez ¿o será Vázquez?

Cuando tienes duda sobre un carácter específico dentro de una palabra puedes remplazarlo por un carácter comodín de manera que Excel busque tanto Vásquez como Vázquez. Este carácter comodín es el símbolo de interrogación (?) y para realizar la búsqueda simplemente debes sustituir la letra sobre la cual tienes duda con el símbolo ? de la siguiente manera:

Esta búsqueda encontrará tanto Vásquez como Vázquez y podrás decidir cuál de los dos resultados es el adecuado.

Palabras diferentes por varias letras

Si el problema no es una letra sino que son varias letras entonces tenemos otra opción que es utilizar el asterisco (*). Con este carácter comodín podemos remplazar varias letras a la vez y de esta manera podríamos buscar todas las palabras que terminan en “ción” de la siguiente manera:

El asterisco le dice a Excel que no importan las letras que sean, ni la cantidad que sean, mientras tanto y sean precedidas por “ción”. El cuadro de diálogo Buscar y remplazar soporta el uso de dos caracteres comodín:

• ? reemplaza un solo carácter.
• * reemplaza cualquier número de caracteres.

Los caracteres comodín pueden ser colocados en cualquier posición dentro de una palabra: al inicio, en medio o al final, lo único que harán será sustituir una letra o un grupo de letras. Estos caracteres también funcionan con valores, por ejemplo, la búsqueda *5 devolverá todas los valores numéricos que terminen en 5. Por el contrario, una búsqueda como 8?3 encontrará todos los números de tres dígitos que empiecen con 8 y terminen con 3.

Buscar un carácter comodín

Ahora la pregunta natural es ¿Cómo hago para encontrar una palabra que contiene un carácter comodín?

Supongamos que necesito encontrar una celda cuyo valor es *ADA*. Si coloco esta frase en el cuadro de diálogo de búsqueda  traerá como resultado todas las palabras que contengan las letras ADA.

Para indicar a Excel que deseo incluir el asterisco en la búsqueda y que no lo deseo utilizar como carácter comodín, debo anteponer una tilde (~). De esta manera, para encontrar las celdas que tengan el valor *ADA* debo realizar la búsqueda de la siguiente forma:

De igual manera podemos anteponer una tilde si queremos buscar un texto que contenga el símbolo de interrogación (?).

Fórmulas matriciales en Excel

Las fórmulas matriciales en Excel son una herramienta muy poderosa. Pero debo advertirte que es un concepto de Excel avanzado pero estoy seguro que con un poco de práctica pronto será sencillo utilizarlas fácilmente.

¿Qué es una fórmula matricial?

Una formula matricial en Excel es una fórmula que trabaja con matrices de datos. Las fórmulas matriciales no trabajan con un solo valor sino con una serie de datos.

Tipos de fórmulas matriciales

Podemos catalogar a las fórmulas matriciales en Excel en dos tipos. El primer tipo son aquellas fórmulas que trabajan con una serie de datos, realizan una operación sobre ellos y como resultado se regresa un solo valor. Generalmente este tipo de fórmulas hace una suma, un promedio o una cuenta de los elementos de las serie de datos y arroja un único resultado en una sola celda.
El segundo tipo de fórmulas matriciales también trabaja con series de datos pero el resultado es colocado en dos o más celdas, es decir, nos devuelve una matriz de datos.

Fórmulas matriciales con un valor único como resultado

En su forma básica la fórmula =FILA(A1:A5) regresará el valor 1 porque es el número de fila de la primera celda del rango. Sin embargo, si esta función se introduce como fórmula matricial trabajará entonces con los valores {1, 2, 3, 4, 5} que son todos los números de filas de las celdas pertenecientes al rango especificado.
Antes de seguir con nuestro ejemplo, hablemos un poco sobre la manera en que se introduce una fórmula matricial. A diferencia de las formulas tradicionales que solemos ingresar en la barra de fórmulas, en lugar de pulsar la tecla Entrar al final de la fórmula, tendremos que pulsar la combinación de teclas Ctrl + Mayus + Entrar. Eso será suficiente para indicar a Excel que estamos utilizando una fórmula matricial y podrás identificarla de inmediato porque se colocarán unos símbolos de corchete {} alrededor de la fórmula. Probemos con el ejemplo que estamos analizando.

Observa cómo en la barra de fórmulas se muestran los símbolos de corchete alrededor. Es importante mencionar que dichos corchetes son colocados por Excel automáticamente y no deben ser ingresados manualmente. Ahora observa que el resultado de la fórmula matricial de nuestro ejemplo es el valor 1, así que ¿cómo sabemos si efectivamente se está trabajando con el arreglo de datos {1, 2, 3, 4, 5}? Para realizar esta comprobación utilizaré la función SUMA de la siguiente manera:

Con el resultado de la celda A1 comprobamos que la función fila está utilizando el arreglo de datos {1, 2, 3, 4, 5} ya que si sumas cada uno de los elementos de esa matriz de datos obtendrás el valor 15. Observa la diferencia en el resultado si no utilizamos la fórmula matricial:

Ya que en esta fórmula no tenemos los corchetes alrededor sabemos que no es una fórmula matricial y por lo tanto la función FILA solamente regresará el valor 1 y no habrá ningún otro valor con el cual sumarlo, por lo tanto el resultado final es también 1. Debes de recordar que después de editar una fórmula matricial siempre debes volver a pulsar la combinación de teclas Ctrl + Mayus + Entrar de lo contrario se convertirá en una fórmula normal.

Fórmulas matriciales con un arreglo como resultado

El segundo tipo de fórmula matricial es aquél que nos da como resultado una matriz de datos. En el ejemplo anterior nuestra función matricial nos regresaba un solo valor que colocamos en la celda A1 y que era la suma de todos los elementos del arreglo. Utilicé la función SUMA para comprobar que efectivamente la función FILA estaba considerando la matriz de datos {1, 2, 3, 4, 5}.
Sin embargo existe otra manera de realizar esta comprobación y es hacer que la función FILA muestre su resultado dentro de un rango de celdas. La única condición para que esto funcione adecuadamente es que debemos seleccionar primero el rango de celdas donde se colocará la matriz de resultados de la fórmula matricial. Para nuestro ejemplo seleccionaré el rango de celdas C1:C5, introduciré la fórmula =FILA(A1:A5) y pulsaré la combinación de teclas Ctrl + Mayus + Entrar. El resultado será el siguiente:

Observa cómo los resultados se extienden sobre al arreglo de celdas colocando un elemento del arreglo de valores en cada una de ellas. La relación entre las celdas de esta matriz de resultados es tan fuerte que si intentas modificar alguno de sus valores Excel mostrará un mensaje:


Las funciones matriciales en Excel son útiles porque nos ayudan a realizar operaciones que no son posibles con las fórmulas normales. Aunque parecen complicadas de utilizar al principio pronto comprobarás que con un poco de práctica comprenderás perfectamente su funcionamiento.

Recuperar un archivo de Excel

Seguramente has pasado por la amarga experiencia de perder todo el trabajo que habías realizado por haber olvidado guardar el libro de Excel o porque tal vez hubo un corte de energía eléctrica y el trabajo que no se había guardado se perdió.

Excel 2010 incluye algunas características especiales que nos ayudarán a hacer menos frecuentes esos momentos de tristeza o frustración y nos ayudarán a recuperar archivos de Excel no guardados.

Autorrecuperación en Excel

La funcionalidad de autorrecuperación realiza un respaldo automático del archivo de una manera periódica. Eso sucede tras bambalinas, es decir, nunca te darás cuenta del momento preciso en que se realiza el guardado automático sin embargo puedes configurar la frecuencia con la que esto sucede.

Para modificar la frecuencia de la autorrecuperación debes abrir el cuadro de diálogo Opciones de Excel y dentro de la sección Guardar debes especificar el número de minutos que pasarán entre cada acción de guardado automático.

Aunque la autorrecuperación ha estado presente en versiones anteriores de Excel, lo que es completamente nuevo es la posibilidad de acceder a todas las versiones autoguardadas del libro.

Recuperar versiones autoguardadas

Para ver las versiones autoguardadas de un libro debes ir a la ficha Archivo y seleccionar la opción Información. La sección Versiones mostrará todas las versiones autoguardadas del libro.

Para abrir cualquiera de estas versiones solamente debes hacer clic sobre su nombre y el archivo se mostrará en pantalla. Podrás distinguir la versión autoguardada por un mensaje que se mostrará por debajo de la cinta de opciones.

Con las dos versiones en pantalla puedes decidir si prefieres la versión autoguardada haciendo clic sobre el botón Restauraro simplemente puedes copiar información de una versión a otra en caso de que solamente desees recuperar algunas partes de la versión autoguardada. Debes tomar muy en cuenta que una vez que se cierra un libro todas sus versiones autoguardadas son eliminadas.

Recuperar archivos Excel no guardados

Cuando cerramos un libro sin guardar los cambios suceden dos cosas. Lo primero que sucede es  que Excel verifica si existen versiones autoguardadas del archivo. Si encuentra al menos una versión autoguardada entonces mostrará un cuadro de diálogo como el siguiente:

Este cuadro de diálogo nos informa que si hacemos clic sobre el botón No guardar el libro de Excel  se cerrará pero tendremos una copia del archivo disponible temporalmente. Para recuperar la versión del archivo sin guardar, vuelve a abrir el archivo de Excel y ve a la ficha Inicio y elige la opción Información. Dentro de la sección Versiones observarás la versión disponible del archivo que tendrá la leyenda “(cuando se cerró sin guardar)”.

Si quieres ver las versiones de todos los libros no guardados debes pulsar el botón Administrar versiones y seleccionar la opción Recupera libros no guardados. Las versiones temporales de los libros no guardados estarán disponibles por solo cuatro días o hasta que se vuelva a editar el archivo.

Extraer valores únicos en Excel

Existen diferentes métodos para extraer valores únicos en Excel, ya sea utilizando comando de la herramienta o a través de una fórmula. Pero si quieres automatizar por completo este proceso será necesario crear una macro.

A continuación revisaremos varias alternativas que tenemos en Excel para extraer los elementos únicos de un rango. Te recomiendo leer cada una de ellas y elegir la opción que mejor se adapte a tus necesidades.

Extraer valores únicos con comandos

Si en tus labores cotidianas la tarea de extraer valores únicos es poco frecuente, entonces será suficiente con utilizar el comando Quitar duplicados. Antes de utilizar este comando te recomiendo hacer una copia de la columna que contiene los datos originales ya que dicho comando modificará el valor de las celdas.

Una vez que hayas hecho la copia, deberás seleccionar una celda de dicho rango e ir a Datos > Herramientas de datos > Quitar duplicados, lo cual mostrará el cuadro de diálogo Quitar duplicados.

Si has colocado un encabezado a la copia de datos realizada deberás asegurarte de marcar la caja de selección Mis datos tienen encabezados. Al pulsar el botón Aceptar se mostrará un mensaje indicando la cantidad de valores duplicados encontrados y de valores únicos que permanecen después de realizar la operación. Como resultado obtendremos una lista de valores únicos:

Además del comando Quitar duplicados existe otro comando para extraer valores únicos en Excel y me refiero al Filtro Avanzado. A través de este comando podemos indicar a Excel que deseamos copiar los registros únicos de nuestra lista:

Si quieres conocer más sobre el Filtro Avanzado te recomiendo consultar el artículo Crear una lista de valores únicos en Excel donde encontrarás un ejemplo detallado.

Extraer valores únicos con una fórmula

Los comandos mencionados anteriormente requieren que tú hagas algunas cosas, como seleccionar los rangos y pulsar ciertos botones. Si quieres utilizar un método que no requiera tanta intervención de tu parte, entonces puedes utilizar una fórmula de Excel para extraer los valores únicos. La fórmula que utilizaremos será la siguiente:

=SI.ERROR(INDICE($C$2:$C$31,COINCIDIR(0,INDICE(CONTAR.SI($F$1:F1,$C$2:$C$31),0,0),0)),"")

En esta fórmula existen solo dos rangos de celdas que explicaré a continuación. El primer rango es $C$2:$C$31 que se refiere a la ubicación donde se encuentran los datos originales. El segundo rango es $F$1:F1, que es el primer argumento de la función CONTAR.SI, se refiere a la primera celda de la columna donde se colocarán los valores únicos. Observa que la primera referencia de este rango es absoluta y la segunda es relativa y deberás mantenerla de esa manera.

Otra condición necesaria para que la fórmula funcione correctamente, es que la columna que contendrá los valores únicosdeberá tener una celda de encabezado. Esto es necesario porque la fórmula será colocada en la celda F2 haciendo referencia al rango $F$1:F1 y si no tuviéramos la celda con el encabezado se crearía una referencia circular.

Una vez que hayas adecuado ambos rangos a tus datos y la columna de resultados tenga un encabezado, deberás colocar la fórmula en la segunda fila y copiarla hacia abajo tantas veces como sea necesario para contener todos los valores únicos que serán extraídos. En la siguiente imagen puedes observar el resultado de esta fórmula:

La función más importante de esta fórmula es CONTAR.SI ya que para cada fila se toma en cuenta el resultado de las filas superiores, de manera que podamos saber si un valor ya ha aparecido previamente. Por esta razón, el primer argumento de la función COINCIDIR es el valor cero, que nos asegura que cada valor analizado ha aparecido cero veces en la columna de resultados.

La función INDICE se encarga de copiar el valor de los datos originales y además utilizamos la función SI.ERROR para evitar desplegar el error #N/A que se mostrará en las celdas inferiores una vez que se ha terminado el listado de valores únicos.

Extraer valores únicos con una macro

Una manera más directa y automática de extraer valores únicos en Excel es utilizando una macro. El código VBA que nos ayudará a realizar dicha extracción es el siguiente:

1 2 3 4 5 6 7 8 9 10 11 12

Sub ValoresUnicos() Dim listaOrigen As Range   On Error Resume Next Set listaOrigen = Application.InputBox _ (Prompt:="Rango de datos origen:", Title:="Seleccionar rango", Type:=8)   listaOrigen.AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=ActiveCell, Unique:=True   Canceled: End Sub

La línea 5 se encarga de mostrar un cuadro de diálogo para preguntarnos el rango que contiene la lista con los datos de origen. La línea 8 ejecuta el método AdvancedFilter que nos ayuda a obtener los valores únicos y colocarlos en la celda activa.

Para utilizar esta macro comienzo por seleccionar la celda donde se colocarán los valores únicos y posteriormente ir a Vista > Macros y elegir la macro a ejecutar. De inmediato se mostrará el cuadro de diálogo  que solicitará el rango de datos origen donde colocaré la referencia a la columna completa de la siguiente manera:

Como resultado obtendremos la lista de valores únicos a partir de la celda activa:

De esta manera hemos revisado diferentes alternativas para obtener una lista de valores únicos ya sea utilizando comandos, fórmulas o una macro. Descarga el libro de trabajo utilizado en este artículo y sigue experimentando con la extracción de valores únicos en Excel.