Ocultar valores cero en Excel - Dejar las celdas en blanco si el valor es 0

Ver video - Cómo ocultar valores cero en Excel + Cómo eliminar valores cero

En caso de que prefiera leer antes que ver un video, a continuación se muestra el tutorial escrito completo.

A veces, en Excel, es posible que desee ocultar valores cero en su conjunto de datos y muestre estas celdas como espacios en blanco.

Suponga que tiene un conjunto de datos como se muestra a continuación y desea ocultar el valor 0 en todas estas celdas (o desea reemplazarlo con algo como un guión o el texto "No disponible").

Si bien puede hacer esto manualmente con un conjunto de datos pequeño, necesitará mejores formas cuando trabaje con conjuntos de datos grandes.

En este tutorial, le mostraré varias formas de ocultar valores cero en Excel y un método para seleccionar y eliminar todos los valores cero del conjunto de datos.

Nota IMPORTANTE: Cuando oculta un 0 en una celda utilizando los métodos que se muestran en este tutorial, solo ocultará el 0 y no lo eliminará. Si bien las celdas pueden parecer vacías, esas celdas todavía contienen los ceros. Y en caso de que use estas celdas (que tienen ceros ocultos) en alguna fórmula, estas se usarán en la fórmula.

¡Comencemos y profundicemos en cada uno de estos métodos!

Ocultar automáticamente el valor cero en las celdas

Excel tiene una funcionalidad incorporada que le permite ocultar automáticamente todos los valores cero para toda la hoja de trabajo.

Todo lo que tiene que hacer es desmarcar una casilla en las opciones de Excel y el cambio se aplicará a toda la hoja de trabajo.

Suponga que tiene el conjunto de datos de ventas como se muestra a continuación y desea ocultar todos los valores cero y mostrar celdas en blanco en su lugar.

A continuación se muestran los pasos para ocultar ceros de todas las celdas en un libro de trabajo en Excel:

1. Abra el libro de trabajo en el que desea ocultar todos los ceros
2. Haga clic en la pestaña Archivo
3. Haga clic en Opciones
4. En el cuadro de diálogo Opciones de Excel que se abre, haga clic en la opción "Avanzado" en el panel izquierdo.
5. Desplácese hacia abajo hasta la sección que dice "Mostrar opción para esta hoja de trabajo" y seleccione la hoja de trabajo en la que desea ocultar los ceros.
6. Desmarque la opción "Mostrar un cero en las celdas que tienen un valor cero".
7. Haga clic en Aceptar

Los pasos anteriores ocultarían instantáneamente ceros en todas las celdas de la hoja de trabajo seleccionada.

Este cambio también se aplica a las celdas donde cero es el resultado de una fórmula.

Recuerde que este método solo oculta el valor 0 en las celdas y no los elimina. El 0 sigue ahí, simplemente está oculto.

Este es el método mejor y más rápido para ocultar valores cero en Excel. Pero esto es útil solo si desea ocultar valores cero en toda la hoja de trabajo. En caso de que solo desee que esto oculte los valores cero en un rango específico de celdas, es mejor usar otros métodos que se describen a continuación en este tutorial.

Ocultar valor cero en celdas usando formato condicional

Si bien el método anterior es el más conveniente, no le permite ocultar ceros solo en un rango específico (más bien, oculta los valores cero en toda la hoja de trabajo).

En caso de que solo desee ocultar ceros para un conjunto de datos específico (o varios conjuntos de datos), puede usar el formato condicional.

Con el formato condicional, puede aplicar un formato específico según el valor de las celdas. Entonces, si el valor en algunas celdas es 0, simplemente puede programar el formato condicional para ocultarlo (o incluso resaltarlo si lo desea).

Suponga que tiene un conjunto de datos como se muestra a continuación y desea ocultar ceros en las celdas.

A continuación se muestran los pasos para ocultar ceros en Excel usando formato condicional:

1. Seleccionar el conjunto de datos
2. Haga clic en la pestaña "Inicio".
3. En el grupo "Estilos", haga clic en "Formato condicional".
4. Coloque el cursor sobre las opciones de "Resaltar reglas de celdas" y, en las opciones que aparecen, haga clic en la opción "Igual a".
5. En el cuadro de diálogo "Igual a", introduzca "0" en el campo de la izquierda.
6. Seleccione el menú desplegable Formato y haga clic en "Formato personalizado".
7. En el cuadro de diálogo "Formato de celdas", seleccione la pestaña "Fuente".
8. En el menú desplegable Color, elija el color blanco.
9. Haga clic en Aceptar.
10. Haga clic en Aceptar.

Los pasos anteriores cambian el color de fuente de las celdas que tienen ceros a blanco, lo que hace que estas celdas se vean en blanco.

Esta solución funciona solo cuando tiene un fondo blanco en su hoja de trabajo. En caso de que haya cualquier otro color de fondo en las celdas, el color blanco aún puede mantener visibles los ceros.

En caso de que realmente desee ocultar los ceros en las celdas, donde las celdas se ven en blanco sin importar el color de fondo que se le dé a las celdas, puede seguir los pasos a continuación.

1. Seleccionar el conjunto de datos
2. Haga clic en la pestaña "Inicio".
3. En el grupo "Estilos", haga clic en "Formato condicional".
4. Coloque el cursor sobre las opciones de "Resaltar reglas de celdas" y, en las opciones que aparecen, haga clic en la opción "Igual a".
5. En el cuadro de diálogo "Igual a", ingrese 0 en el campo de la izquierda.
6. Seleccione el menú desplegable Formato y haga clic en Formato personalizado.
7. En el cuadro de diálogo "Formato de celdas", seleccione la pestaña "Número" y haga clic en la opción "Personalizado" en el panel izquierdo.
8. En la opción Formato personalizado, ingrese lo siguiente en el campo Tipo: ;;; (tres puntos y coma sin espacio)
9. Haga clic en Aceptar.
10. Haga clic en Aceptar.

Los pasos anteriores cambian el formato personalizado de las celdas que tienen el valor 0.

Cuando usa tres puntos y coma como formato, oculta todo en la celda (ya sea numérico o de texto). Y dado que este formato se aplica solo a aquellas celdas que tienen el valor 0, solo oculta los ceros y no el resto del conjunto de datos.

También puede utilizar este método para resaltar estas celdas en un color diferente (como rojo o amarillo). Para hacer esto, simplemente agregue otro paso (después del Paso 8) donde debe seleccionar la pestaña Relleno en el cuadro de diálogo "Formato de celdas" y luego seleccione el color en el que desea resaltar las celdas.

Ambos métodos cubiertos anteriormente también funcionan cuando 0 es el resultado de una fórmula en una celda.

Nota: Ambos métodos cubiertos en esta sección solo ocultan los valores cero. No elimina estos ceros. Esto significa que en caso de que use estas celdas en los cálculos, también se usarán todos los ceros.

Ocultar valor cero con formato personalizado

Otra forma de ocultar valores cero de un conjunto de datos es creando un formato personalizado que oculta el valor en las celdas que tienen ceros, mientras que cualquier otro valor se muestra como se esperaba.

A continuación se muestran los pasos para usar un formato personalizado para ocultar ceros en las celdas:

1. Seleccione el conjunto de datos completo al que desea aplicar este formato. Si desea esto para toda la hoja de trabajo, seleccione todas las celdas
2. Con las celdas seleccionadas, haga clic en la pestaña Inicio
3. En el grupo Celdas, haga clic en la opción Formato
4. En la opción desplegable, haga clic en Formato de celdas. Esto abrirá el cuadro de diálogo Formato de celdas
5. En el cuadro de diálogo Formato de celdas, seleccione la pestaña Número
6. En la opción del panel izquierdo, haga clic en la opción Personalizar.
7. En las opciones de la derecha, ingrese el siguiente formato en el campo Tipo: 0;-0;;@
8. Haga clic en Aceptar

Los pasos anteriores aplicarían un formato personalizado a las celdas seleccionadas donde solo aquellas celdas que tienen el valor 0 están formateadas para ocultar el valor, mientras que el resto de las celdas muestran los datos como se esperaba.

¿Cómo funciona este formato personalizado?

En Excel, hay cuatro tipos de formatos de datos que especificas:

1. Números positivos
2. Números negativos
3. Ceros
4. Texto

Y para cada uno de estos tipos de datos, puede especificar un formato.

En la mayoría de los casos, existe un formato predeterminado: General, que mantiene visibles todos estos tipos de datos. Pero tiene la flexibilidad de crear un formato específico para cada uno de estos tipos de datos.

A continuación se muestra la estructura de formato personalizado que debe seguir en caso de que esté aplicando un formato personalizado a cada uno de estos tipos de datos:

;;;

En el método cubierto anteriormente, he especificado el formato para:

0;-0;;@

El formato anterior hace lo siguiente:

1. Los números positivos se muestran tal cual
2. Los números negativos se muestran con un signo negativo
3. Los ceros están ocultos (ya que no se especifica ningún formato)
4. El texto se muestra como está

Consejo profesional: Si desea que los números negativos aparezcan en rojo en su conjunto de datos, use el siguiente formato personalizado: 0; [Rojo] 0; 0; @

Reemplazar ceros con guiones o algún texto específico

En algunos casos, es posible que desee no solo ocultar los ceros en las celdas, sino reemplazarlos con algo más significativo.

Por ejemplo, es posible que desee eliminar los ceros y reemplazarlos con un guión o un texto como "No disponible" o "Datos aún por venir".

De nuevo, esto es posible mediante el formato de número personalizado, donde puede especificar qué debe mostrar una celda en lugar de un cero.

A continuación se muestran los pasos para convertir un 0 en un guión en Excel:

1. Seleccione el conjunto de datos completo al que desea aplicar este formato. Si desea esto para toda la hoja de trabajo, seleccione todas las celdas
2. Con las celdas seleccionadas, haga clic en la pestaña Inicio
3. En el grupo Celdas, haga clic en la opción Formato
4. En la opción desplegable, haga clic en Formato de celdas. Esto abrirá el cuadro de diálogo Formato de celdas
5. En el cuadro de diálogo Formato de celdas, seleccione la pestaña Número
6. En la opción del panel izquierdo, haga clic en la opción Personalizar.
7. En las opciones de la derecha, ingrese el siguiente formato en el campo Tipo: 0;-0;-;@
8. Haga clic en Aceptar

Los pasos anteriores mantendrían todas las demás celdas como están y cambiarían las celdas con el valor 0 para mostrar un guión (-).

Nota: En este ejemplo, elegí reemplazar el cero con un guión, pero también puedes usar cualquier texto. Por ejemplo, si desea mostrar el NA en las celdas, puede usar el siguiente formato: 0; -0; ”NA”; @Consejo profesional: También puede especificar algunos colores de texto en un formato de número personalizado. Por ejemplo, si desea que las celdas con ceros muestren el texto NA en color rojo, puede usar el siguiente formato: 0; -0; [Rojo] ”NA”; @

Ocultar valores cero en tablas dinámicas

Puede haber dos escenarios en los que una tabla dinámica muestra el valor como 0:

1. Las celdas de datos de origen que se resumen en la tabla dinámica tienen valores 0
2. Las celdas de datos de origen que se resumen en la tabla dinámica son espacios en blanco y la tabla dinámica se ha formateado para mostrar las celdas vacías como cero

Veamos cómo ocultar los ceros en la tabla dinámica en cada escenario:

Cuando las celdas de datos de origen tienen 0

A continuación se muestra un conjunto de datos de muestra que he usado para crear una tabla dinámica. Tenga en cuenta que los números de Cantidad / Ingresos / Beneficios para todos los registros de "Oeste" son 0.

La tabla dinámica creada con este conjunto de datos se ve como se muestra a continuación:

Dado que había 0 en el conjunto de datos para todos los registros de la región oeste, aquí verá un 0 en la tabla dinámica para el oeste.

Si desea ocultar cero en esta tabla dinámica, puede usar el formato condicional para hacer esto.

Sin embargo, el formato condicional en la tabla dinámica en Excel funciona un poco diferente a los datos normales.

Estos son los pasos para aplicar formato condicional a la tabla dinámica para ocultar ceros:

1. Seleccione cualquier celda de la columna que tenga ceros en el resumen de la tabla dinámica (no seleccione todas las celdas, sino solo una celda)
2. Haga clic en la pestaña Inicio
3. En el grupo "Estilos", haga clic en "Formato condicional".
4. Coloque el cursor sobre las opciones de "Resaltar reglas de celdas" y, en las opciones que aparecen, haga clic en la opción "Igual a".
5. En el cuadro de diálogo "Igual a", ingrese 0 en el campo de la izquierda.
6. Seleccione el menú desplegable Formato y haga clic en Formato personalizado.
7. En el cuadro de diálogo "Formato de celdas", seleccione la pestaña Número y haga clic en la opción "Personalizar" en el panel izquierdo.
8. En la opción Formato personalizado, ingrese lo siguiente en el campo Tipo: ;;; (tres puntos y coma sin espacio)
9. Haga clic en Aceptar. Verá un pequeño icono de Opciones de formato a la derecha de la celda seleccionada.
10. Haga clic en este icono de "Opciones de formato".
11. En las opciones que estén disponibles, elija - Todas las celdas que muestran los valores de "Suma de ingresos" para "Región".

Los pasos anteriores ocultan el cero en la tabla dinámica de tal manera que si cambia la estructura de la tabla dinámica (al agregarle más encabezados de filas / columnas, los ceros seguirán ocultos).

Si desea obtener más información sobre cómo funciona esto, aquí hay un tutorial detallado sobre la forma correcta de aplicar formato condicional a las tablas dinámicas.

Cuando las celdas de datos de origen tienen celdas vacías

A continuación se muestra un ejemplo en el que tengo un conjunto de datos que tiene celdas vacías para todos los registros de West.

Si creo una tabla dinámica con estos datos, obtendré el resultado como se muestra a continuación.

De forma predeterminada, la tabla dinámica muestra celdas vacías cuando todas las celdas de los datos de origen están en blanco. Pero en caso de que todavía vea un 0 en este caso, siga los pasos a continuación para ocultar el cero y mostrar celdas en blanco:

1. Haga clic con el botón derecho en cualquiera de las celdas de la tabla dinámica
2. Haga clic en "Opciones de tabla dinámica".
3. En el cuadro de diálogo "Opciones de tabla dinámica", haga clic en la pestaña "Diseño y formato"
4. En las opciones de Formato, marque las opciones y "Para mostrar celdas vacías:" y déjelo en blanco.
5. Haga clic en Aceptar.

Los pasos anteriores ocultarían los ceros en la tabla dinámica y mostrarían una celda en blanco en su lugar.

En caso de que desee que la tabla dinámica muestre algo en lugar del 0, puede especificarlo en el paso 4. Por ejemplo, si desea mostrar el texto "Datos no disponibles", en lugar del 0, escriba este texto en el campo (como se muestra a continuación).

Buscar y eliminar ceros en Excel

En todos los métodos anteriores, le he mostrado formas de ocultar el valor 0 en la celda, pero el valor aún permanece en la celda.

En caso de que desee eliminar los valores cero del conjunto de datos (lo que lo dejaría con celdas en blanco), use los pasos a continuación:

1. Seleccione el conjunto de datos (o la hoja de trabajo completa)
2. Haga clic en la pestaña Inicio
3. En el grupo "Edición", haga clic en "Buscar y seleccionar"
4. En las opciones que se muestran en el menú desplegable, haga clic en "Reemplazar"
5. En el cuadro de diálogo "Buscar y reemplazar", realice los siguientes cambios:
   • En el campo "Buscar qué", ingrese 0
   • En el campo "Reemplazar con", no ingrese nada (déjelo en blanco)
   • Haga clic en el botón Opciones y marque la opción "Coincidir con todo el contenido de la celda".
6. Haga clic en Reemplazar todo

Los pasos anteriores reemplazarían instantáneamente todas las celdas que tenían un valor cero con un espacio en blanco.

En caso de que no desee eliminar los ceros de inmediato y primero seleccione las celdas, siga los siguientes pasos:

1. Seleccione el conjunto de datos (o la hoja de trabajo completa)
2. Haga clic en la pestaña Inicio
3. En el grupo "Edición", haga clic en "Buscar y seleccionar"
4. En las opciones que se muestran en el menú desplegable, haga clic en "Reemplazar"
5. En el cuadro de diálogo "Buscar y reemplazar", realice los siguientes cambios:
   • En el campo "Buscar qué", ingrese 0
   • En el campo "Reemplazar con", no ingrese nada (déjelo en blanco)
   • Haga clic en el botón Opciones y marque la opción "Coincidir con todo el contenido de la celda".
6. Haga clic en el botón "Buscar todo". Esto le dará una lista de todas las celdas que tienen 0 en ella.
7. Presione Control-A (mantenga presionada la tecla de control y presione A). Esto seleccionará todas las celdas que tengan 0 en él.

Ahora que tiene todas las celdas con 0 seleccionado, puede eliminarlas, reemplazarlas con otra cosa o cambiar el color y resaltarlas.

Ocultar los ceros vs. Eliminar los ceros

Al tratar las células con ceros, necesita saber la diferencia entre ocultarlas y eliminarlas.

Cuando oculta el valor cero en una celda, no es visible para usted, pero aún permanece en la celda. Esto significa que si tiene un conjunto de datos y lo usa para el cálculo, ese valor 0 también se usará en el cálculo.

Por el contrario, cuando tiene una celda en blanco, usarla en fórmulas puede significar que Excel ignora automáticamente estas celdas (dependiendo de la fórmula que esté usando).

Por ejemplo, a continuación se muestra lo que obtengo cuando uso la fórmula PROMEDIO para obtener el promedio de dos columnas (el resultado está en la fila 14). La columna A tiene celdas en las que se ha ocultado el valor 0 (pero todavía está en la celda) y la columna B tiene celdas en las que se ha eliminado el valor 0.

Puede ver la diferencia en el resultado a pesar del hecho de que el conjunto de datos para ambos conjuntos de datos tiene el mismo aspecto.

Esto sucede porque la fórmula PROMEDIO ignora las celdas en blanco en la columna B, pero usa las de la Columna A (ya que las de la columna A todavía tienen el valor 0).