10 formas estupendas de limpiar datos en hojas de cálculo de Excel

Ver video: 10 formas de limpiar datos en Excel

Los datos forman la columna vertebral de cualquier análisis que realice en Excel. Y cuando se trata de datos, hay muchas cosas que pueden salir mal, ya sea la estructura, la ubicación, el formato, los espacios adicionales, etc.

En esta publicación de blog, le mostraré 10 formas sencillas de limpiar datos en Excel.

# 1 Deshazte de espacios adicionales

Los espacios adicionales son dolorosamente difíciles de detectar. Si bien de alguna manera puede detectar los espacios adicionales entre palabras o números, los espacios finales ni siquiera son visibles. Aquí hay una forma ordenada de deshacerse de estos espacios adicionales: use la función TRIM.

Sintaxis: TRIM (texto)

La función TRIM de Excel toma la referencia de celda (o texto) como entrada. Elimina los espacios iniciales y finales, así como los espacios adicionales entre palabras (excepto los espacios simples).

# 2 Seleccionar y tratar todas las celdas en blanco

Las celdas en blanco pueden causar estragos si no se tratan de antemano. A menudo me enfrento a problemas con celdas en blanco en un conjunto de datos que se utiliza para crear informes / paneles.

Es posible que desee completar todas las celdas en blanco con "0" o "No disponible", o simplemente desee resaltarlo. Si hay un gran conjunto de datos, hacerlo manualmente podría llevar horas. Afortunadamente, hay una manera de seleccionar todas las celdas en blanco a la vez.

1. Seleccionar el conjunto de datos completo
2. Presione F5 (esto abre el cuadro de diálogo Ir a)
3. Haga clic en el botón Especial … (en la parte inferior izquierda). Esto abre el cuadro de diálogo Ir a especial
   
4. Seleccione En blanco y haga clic en Aceptar
   

Esto selecciona todas las celdas en blanco en su conjunto de datos. Si desea ingresar 0 o No disponible en todas estas celdas, simplemente escríbalo y presione Control + Entrar (recuerde que si presiona solo enter, el valor se inserta solo en la celda activa).

# 3 Convierta números almacenados como texto en números

A veces, cuando importa datos de archivos de texto o bases de datos externas, los números se almacenan como texto. Además, algunas personas tienen la costumbre de usar un apóstrofe (‘) antes de un número para convertirlo en texto. Esto podría crear problemas graves si utiliza estas celdas en los cálculos. Aquí hay una forma infalible de convertir estos números almacenados como texto nuevamente en números.

1. En cualquier celda en blanco, escriba 1
2. Seleccione la celda donde escribió 1 y presione Control + C
3. Seleccione la celda / rango que desea convertir a números
4. Seleccione Pegar -> Pegado especial (Atajo de teclado - Alt + E + S)
5. En el cuadro de diálogo Pegar especial, seleccione Multiplicar (en la categoría de operaciones)
   
6. Haga clic en Aceptar. Esto convierte todos los números en formato de texto a números.

Puede hacer mucho más con las opciones de pegado de operaciones especiales. Aquí hay varias otras formas de multiplicar en Excel usando Pegado especial.

# 4 - Eliminar duplicados

Puede haber 2 cosas que puede hacer con datos duplicados: Resaltarlo o Bórralo.

• Resalte datos duplicados:
  • Seleccione los datos y vaya a Inicio -> Formato condicional -> Resaltar reglas de celdas -> Valores duplicados.
  • Especifique el formato y todos los valores duplicados se resaltarán.
    
• Eliminar duplicados en datos:
  • Seleccione los datos y vaya a Datos -> Eliminar duplicados.
  • Si sus datos tienen encabezados, asegúrese de que la casilla de verificación en la parte superior derecha esté marcada.
  • Seleccione las columnas de las que desea eliminar los duplicados y haga clic en Aceptar.
    

Esto elimina los valores duplicados de la lista. Si desea que la lista original esté intacta, copie y pegue los datos en otra ubicación y luego haga esto.

Relacionada: La guía definitiva para buscar y eliminar duplicados en Excel.

# 5 Resaltar errores

Hay 2 formas de resaltar errores en los datos en Excel:

Usar formato condicional

1. Seleccionar el conjunto de datos completo
2. Vaya a Inicio -> Formato condicional -> Nueva regla
3. En el cuadro de diálogo Nueva regla de formato, seleccione "Dar formato solo a las celdas que contienen"
4. En la Descripción de la regla, seleccione Errores en el menú desplegable
5. Establezca el formato y haga clic en Aceptar. Esto resalta cualquier valor de error en el conjunto de datos seleccionado
   

Uso de Ir a especial

1. Seleccionar el conjunto de datos completo
2. Presione F5 (esto abre el cuadro de diálogo Ir a)
3. Haga clic en el botón especial en la parte inferior izquierda
4. Seleccione Fórmulas y desmarque todas las opciones excepto Errores
   

Esto selecciona todas las celdas que tienen un error. Ahora puede resaltarlos manualmente, eliminarlos o escribir cualquier cosa en ellos.

# 6 Cambiar el texto a minúsculas / mayúsculas / mayúsculas y minúsculas

Cuando hereda un libro de trabajo o importa datos de archivos de texto, a menudo los nombres o títulos no son consistentes. A veces, todo el texto puede estar en minúsculas / mayúsculas o puede ser una combinación de ambos. Puede hacer que todo sea coherente con estas tres funciones:

LOWER (): convierte todo el texto en minúsculas.
MAYÚSCULAS (): convierte todo el texto en mayúsculas.
PROPER (): convierte todo el texto en mayúsculas y minúsculas.

# 7 Analizar datos usando texto en columna

Cuando obtiene datos de una base de datos o los importa de un archivo de texto, puede suceder que todo el texto esté apretado en una celda. Puede analizar este texto en varias celdas utilizando la funcionalidad Texto a columna en Excel.

1. Seleccione los datos / texto que desea analizar
2. Ir a datos -> Texto a columna (esto abre el Asistente de texto a columnas)
   • Paso 1: Seleccione el tipo de datos (seleccione Delimitado si sus datos no están igualmente espaciados y están separados por caracteres como coma, guión, punto…). Haga clic en Siguiente
     
   • Paso 2: seleccione Delimitador (el carácter que separa sus datos). Puede seleccionar un delimitador predefinido o cualquier otra cosa usando la opción Otro
     
   • Paso 3: seleccione el formato de datos. También seleccione la celda de destino. Si no se selecciona la celda de destino, se sobrescribe la celda actual
     

Relacionada: Extraiga el nombre de usuario de la identificación del correo electrónico usando el texto en la columna.

# 8 Corrector ortográfico

Nada reduce la credibilidad de su trabajo que un error de ortografía.

Utilice el método abreviado de teclado F7 para ejecutar una revisión ortográfica de su conjunto de datos.

Aquí hay un tutorial detallado sobre cómo usar el corrector ortográfico en Excel.

# 9 Eliminar todo el formato

En mi trabajo, utilicé varias bases de datos para obtener los datos en Excel. Cada base de datos tenía su propio formato de datos. Cuando tenga todos los datos en su lugar, así es como puede eliminar todo el formato de una sola vez:

1. Seleccione el conjunto de datos
2. Vaya a Inicio -> Borrar -> Borrar formatos
   

Del mismo modo, también puede borrar solo los comentarios, hipervínculos o contenido.

# 10 Use Buscar y reemplazar para limpiar datos en Excel

Buscar y reemplazar es indispensable cuando se trata de la limpieza de datos. Por ejemplo, puede seleccionar y eliminar todos los ceros, cambiar referencias en fórmulas, buscar y cambiar el formato, etc.

Obtenga más información sobre cómo se puede utilizar Buscar y reemplazar para limpiar datos.

Estas son mis 10 técnicas principales para limpiar datos en Excel. Si desea aprender algunas técnicas más, aquí hay una guía del equipo de MS Excel: Limpiar datos en Excel.

Si hay más técnicas que utilizas, ¡compártelas con nosotros en la sección de comentarios!