7 trucos asombrosos de formato de número personalizado de Excel (debe saber)

El formato de número personalizado de Excel es la ropa para los datos en las celdas de Excel. Puedes vestirlos como quieras. Todo lo que necesita es un poco de conocimiento sobre cómo funciona el formato de número personalizado de Excel.

Con el formato de número personalizado en Excel, puede cambiar la forma en que se muestran los valores en las celdas, pero al mismo tiempo manteniendo intacto el valor original.

Por ejemplo, puedo hacer que un valor de fecha se muestre en diferentes formatos de fecha, mientras que el valor subyacente (el número que representa la fecha) seguirá siendo el mismo.

En este tutorial, cubriré todo sobre el formato de número personalizado en Excel y también le mostraré algunos ejemplos realmente útiles que puede usar en su trabajo diario.

¡Entonces empecemos!

Construcción de formato de número personalizado de Excel

Antes de mostrarle lo asombroso que es, permítame explicarle brevemente cómo funciona el formato de número personalizado en Excel.

Por diseño, Excel puede aceptar los siguientes cuatro tipos de datos en una celda:

• Un numero positivo
• un número negativo
• 0
• Cadenas de texto (esto puede incluir cadenas de texto puro, así como cadenas alfanuméricas)

Otro tipo de datos que puede aceptar Excel son las fechas, pero dado que todos los valores de fecha y hora se almacenan como números en Excel, estos se cubrirían como parte del número positivo.

Para cualquier celda de una hoja de cálculo en Excel, puede especificar cómo se debe mostrar cada uno de estos tipos de datos en la celda.

Y a continuación se muestra la sintaxis en la que puede especificar este formato.

;;;

Tenga en cuenta que todos estos están separados por punto y coma (;).

Todo lo que ingrese en una celda en Excel se incluiría en cualquiera de estas cuatro categorías y, por lo tanto, se puede especificar un formato personalizado para ello.

Si menciona solo:

• Un formato: Se aplica a las cuatro secciones. Por ejemplo, si escribe General, se aplicará a las cuatro secciones.
• Dos formatos: El primero se aplica a números positivos y ceros, y el segundo se aplica a números negativos. El formato de texto por defecto se convierte en General.
• Tres formatos: El primero se aplica a números positivos, el segundo se aplica a números negativos, el tercero se aplica a cero y el texto desaparece porque no se especifica nada para el texto.

Si desea obtener más información sobre el formato de número personalizado, le recomiendo encarecidamente la sección de Ayuda de Office.

Más adelante en este tutorial se explica cómo funciona y dónde ingresar estos formatos. Por ahora, solo tenga en cuenta que esta es la sintaxis para el formato de cualquier celda

Cómo funciona el formato de número personalizado en Excel

Luego abre un nuevo libro de Excel, por defecto todas las celdas en todas las hojas de trabajo en ese libro tienen el formato General donde:

• Números positivos se muestran como están (alineados a la izquierda)
• Números negativos se muestran con un signo menos (y alineados a la izquierda)
• Cero se muestra como 0 (y alineado a la izquierda)
• Texto se muestra como está (alineado a la derecha)

Y en caso de que ingrese una fecha, se mostrará según su configuración regional (en formato DD-MM-AAAA o MM-DD-AAAA).

Entonces, de manera predeterminada, el formato del número de celda es:

General; General; General; General

Donde cada tipo de datos tiene el formato General (y siga las reglas que mencioné anteriormente, en función de si es un número positivo, un número negativo, 0 o texto)

Ahora la configuración predeterminada está bien, pero ¿cómo se cambia el formato? Por ejemplo, si quiero que mis números negativos aparezcan en color rojo o quiero que mis fechas aparezcan en un formato específico, ¿cómo puedo hacerlo?

Uso del menú desplegable de formato en la cinta

Puede encontrar algunos de los formatos numéricos más utilizados en la pestaña Inicio dentro del grupo Número.

Es un menú desplegable que muestra muchos de los formatos de uso común que puede aplicar a una celda simplemente seleccionando la celda y luego seleccionando el formato de esta lista.

Por ejemplo, si desea convertir números en porcentajes o convertir fechas en formato de fecha corta o formato de fecha larga, puede hacerlo fácilmente utilizando las opciones del menú desplegable.

Uso del cuadro de diálogo Formato de celdas

Cuadros de diálogo de formato de celdas donde obtiene la máxima flexibilidad cuando se trata de aplicar formatos a una celda en Excel.

Ya tiene muchos formatos prefabricados que puede usar, o si desea crear su propio formato de número personalizado, también puede hacerlo.

A continuación se explica cómo abrir el cuadro de diálogo Formato de celdas;

1. Haga clic en la pestaña Inicio
2. En el grupo Número, haga clic en el icono del iniciador del cuadro de diálogo (la pequeña flecha inclinada en la parte inferior derecha del grupo).

Esto abrirá el cuadro de diálogo Formato de celdas. Dentro de él, encontrará todos los formatos en la pestaña Número (junto con la opción de crear formatos de celda personalizados)

También puede utilizar el siguiente método abreviado de teclado para abrir el cuadro de diálogo de formato de celdas:

CONTROL + 1 (toda la tecla Control y luego presione la tecla 1).

En caso de que esté usando Mac, puede usar CMD + 1

Ahora tengamos un par de ejemplos prácticos en los que crearemos nuestros propios formatos de números personalizados.

Ejemplos de uso de formato de número personalizado en Excel

Ahora tengamos un par de ejemplos prácticos en los que crearemos nuestros propios formatos de números personalizados.

Ocultar un valor de celda (u Ocultar todos los valores en un rango de celdas)

A veces, es posible que tenga la necesidad de ocultar el contenido de la celda (sin ocultar la fila o columna que tiene la celda).

Un caso práctico de esto es cuando está creando un tablero de Excel y hay un par de valores que usa en el tablero pero no quiere que los usuarios lo vean. para que pueda simplemente ocultar estos valores

Suponga que tiene un conjunto de datos como se muestra a continuación y desea ocultar todos los valores numéricos:

A continuación se explica cómo hacer esto usando formato personalizado en Excel:

1. Seleccione la celda o el rango de celdas para el que desea ocultar el contenido de la celda
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

   ;;;

¿Como funciona esto?

El formato para cuatro tipos diferentes de tipos de datos se divide por punto y coma en el siguiente formato:

;;;

En esta técnica, hemos eliminado el formato y solo especificamos el punto y coma, lo que indica que no queremos ningún formato para las celdas. Esta es una indicación para que Excel oculte cualquier contenido que esté en la celda.

Tenga en cuenta que, si bien esto oculta el contenido de la celda, todavía está allí. por lo tanto, si tiene números que ha ocultado, aún puede usarlos en los cálculos (lo que lo convierte en un gran truco para los paneles e informes de Excel).

Mostrar números negativos en color rojo

De forma predeterminada, los números negativos se muestran con un signo menos (o entre paréntesis en algunas configuraciones regionales).

Puede utilizar un formato de número personalizado para cambiar el color de los números negativos y hacer que se muestren en color rojo.

Suponga que tiene un conjunto de datos como se muestra a continuación y desea resaltar todos los valores negativos en color rojo.

A continuación se muestran los pasos para hacer esto:

1. Seleccione el rango de celdas donde desea mostrar los números negativos en rojo
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

General; [Rojo] -General

El formato anterior hará que sus celdas con valores negativos se muestren en color rojo.

¿Como funciona esto?

Cuando solo especifica dos formatos como formato de número personalizado, el primero se considera para números positivos y 0, y el segundo se considera para números negativos (el siguiente valor por defecto se convierte en el formato general).

En el formato anterior que hemos utilizado, hemos especificado el color que queremos que adopte ese formato entre corchetes.

Entonces, mientras que los números negativos toman el formato general, se muestran en color rojo con un signo menos.

En caso de que desee que los números negativos se detengan en color rojo entre paréntesis (en lugar del signo menos), puede utilizar el siguiente formato:

General; [Rojo] (General)

Agregar texto a números (como millones / miles de millones)

Una cosa sorprendente sobre el formato de número personalizado es que puede agregar un prefijo o un sufijo de texto a un número, mientras mantiene el número original en la celda.

Por ejemplo, puedo hacer que "100" aparezca como "$ 100 millones", sin dejar de tener el valor original en la celda para poder usarlo en los cálculos.

Suponga que tiene un conjunto de datos como se muestra a continuación y desea mostrar estos números con un signo de dólar y la palabra millón delante.

A continuación se muestran los pasos para hacer esto:

1. Seleccione el conjunto de datos donde desea agregar el texto a los números
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

$ General "Millones"

Tenga en cuenta que este formato de número personalizado solo afectará a los números. Si ingresa texto en la misma celda, aparecerá como está.

¿Como funciona esto?

En este ejemplo, al formato general, agregué la palabra millón entre comillas dobles. Todo lo que agregue entre comillas dobles al formato se muestra como está en la celda.

También agregué el signo de dólar antes del formato general para que todos mis números también muestren el signo de dólar antes del número.

Nota: Si se pregunta por qué el signo de dólar no está entre comillas dobles mientras que la palabra Millón está entre comillas dobles, es porque Excel reconoce el dólar como un símbolo que se usa a menudo en el formato de número personalizado y no lo requiere para agregarle comillas dobles. Puede continuar y agregar comillas dobles al signo de dólar, pero cuando cierre el cuadro de diálogo de formato de celdas y lo abra nuevamente, notará que Excel lo ha eliminado

Disfraz de números y texto

Con el formato de número personalizado, puede evaluar un máximo de dos condiciones y, en función de estas condiciones, puede aplicar un formato a la celda.

Por ejemplo, suponga que tiene una lista de puntajes para algunos estudiantes, desea mostrar el texto Aprobado o No aprobado según el puntaje (donde cualquier puntaje menor a 35 debe mostrar "No aprobado", de lo contrario, debe mostrar "Aprobado".

A continuación, se muestran los pasos para mostrar las puntuaciones como Aprobado o No aprobado en lugar del valor mediante el formato de número personalizado:

1. Seleccione el conjunto de datos donde tiene las puntuaciones
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

   [<35] "No aprobado"; "Aprobado"

A continuación se muestra cómo se verían sus datos después de haber aplicado el formato anterior.

Tenga en cuenta que no cambia el valor de las celdas. La celda sigue teniendo las puntuaciones en valores numéricos mientras muestra "Pasa" o "No pasa" según el valor de la celda.

¿Como funciona esto?

En este ejemplo, la condición debe especificarse entre corchetes. El formato de número personalizado luego evalúa el valor en la celda y aplica el formato en consecuencia.

Entonces, si el valor en la celda es menor que 35, entonces el número está oculto y, en su lugar, se muestra el texto "Fallo", agregando todos los demás casos, se muestra "Aprobado".

De manera similar, si desea convertir un rango de celdas que tienen 0 y 1 en VERDADERO y FALSO, puede usar el siguiente formato:

[= 0] "FALSO"; [= 1] "VERDADERO"

Recuerde que solo puede utilizar dos condiciones en el formato de número personalizado. Si intenta utilizar más de dos condiciones, su mensaje le informará que no puede aceptar ese formato.

Ocultar texto pero mostrar números

A veces, cuando descarga sus datos de bases de datos, ya que están vacías, se llenan con valores como No disponible o NA o -

Con el formato de número personalizado, puede ocultar todos los valores de texto mientras mantiene los números como están. esto también asegura que sus datos originales estén intactos (ya que no está eliminando el valor del texto, simplemente lo está ocultando)

Suponga que tiene un conjunto de datos como se muestra a continuación donde desea ocultar todos los valores de texto:

A continuación se muestran los pasos para hacer esto:

1. Seleccione el conjunto de datos donde tiene los datos
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

General; -General; General;

Tenga en cuenta que he utilizado el formato General para los números. Puede cualquier formato que desee (como 0, 0. #, # 0.0%). Además, tenga en cuenta que hay un signo negativo (-) antes del segundo formato General, ya que representa el formato de los números negativos.

¿Como funciona esto?

En este ejemplo, dado que queríamos ocultar todos los valores de texto, simplemente no especificamos el formato.

Y dado que especificamos el formato para los otros tres tipos de datos (número positivo, número negativo y cero), Excel entendió que hemos dejado a propósito el formato para el valor de texto vacío, y no debería mostrar nada si una celda tiene un texto. cuerda.

Mostrar números como porcentajes (%)

Con el formato de número personalizado, también puede cambiar los números y hacer que aparezcan en porcentajes.

Suponga que tiene un conjunto de datos como se muestra a continuación y desea convertir estos números para mostrar el valor porcentual equivalente:

A continuación se muestran los pasos para hacer esto:

1. Seleccione las celdas donde tiene los números (que desea convertir a porcentaje)
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

0%

Esto cambiará los números a sus porcentajes. Por ejemplo, cambia de 0,11 a 11%.

Si todo lo que desea hacer es convertir un número en su valor de porcentaje equivalente, una mejor manera de hacerlo sería usar la opción de porcentaje en el cuadro de diálogo de formato de celdas.

Donde el formato personalizado puede ser útil es cuando desea tener más control sobre cómo desea mostrar el valor porcentual.

Por ejemplo, si desea mostrar algún texto adicional con el porcentaje, todo lo que desea mostrar porcentajes negativos en color rojo, entonces es mejor usar formato personalizado

Mostrar números en una unidad diferente (millones como miles de millones o gramos como kilogramos)

Si trabaja con números grandes, es posible que desee mostrarlos en una unidad diferente. por ejemplo, si tiene los datos de ventas de algunas empresas, es posible que desee mostrar las ventas en millones o miles de millones (manteniendo el valor de venta original en las celdas)

Afortunadamente, puede hacerlo fácilmente con un formato de número personalizado.

Suponga que tiene un conjunto de datos como se muestra a continuación y desea mostrar estos valores de ventas en millones.

A continuación se muestran los pasos para hacer esto:

1. Seleccione las celdas donde tiene los números
2. Abra el cuadro de diálogo Formato de celdas (método abreviado de teclado Control + 1)
3. En la pestaña Número, haga clic en Personalizado
4. Ingrese el siguiente formato en el campo Tipo:

0.0,,

Lo anterior le daría el resultado que se muestra a continuación (valores en millones en un dígito después del decimal):

¿Como funciona esto?

En el formato anterior, un cero antes del decimal indica que Excel debe mostrar el número completo (exactamente como está en la celda), y un 0 después del decimal indica que debe mostrar solo un dígito significativo después del punto decimal.

Entonces 0.0 siempre sería un número con un dígito después del punto decimal.

Cuando agregas una coma después del 0.0, eliminará los últimos tres dígitos del número. Por ejemplo, 123456 se convertiría en 123,4 (los últimos tres dígitos se eliminan y, dado que tiene que mostrar 1 dígito después del punto decimal, muestra 4)

Si lo piensa bien, es similar a dividir el número por 1000. Por lo tanto, si desea mostrar un número en la unidad de "millones", debe agregar 2 comas para eliminar los últimos 6 dígitos.

Y si también desea mostrar la palabra millón o el alfabeto M después del número, use el siguiente formato

0.0 ,, "M"