Formato personalizado de números en Excel

Transcripción del video

Hay ocasiones en las que necesitamos aplicar algún formato específico a los valores numéricos de nuestra hoja, y por esa razón es importante conocer la manera en que funciona el formato personalizado de celdas de manera que podamos mostrar los valores tal como los necesitamos.

En esta clase, te mostraré algunos consejos que te ayudarán a aplicar un formato personalizado a los números sin perder sus propiedades para realizar cálculos en Excel.

En esta hoja, en la primera columna tengo una serie de números que representan órdenes de compra y todos ellos están compuestos por 8 dígitos

 El problema que se presenta es cuando quiero ingresar un número que contenga ceros a la izquierda.

Por ejemplo, en la celda A7 ingresaré el número 00-00-01-23. Al pulsar la tecla Entrar, Excel removerá los ceros a la izquierda.

Algunas personas intentan solucionar este problema insertando el número como una cadena de texto de la siguiente manera.

Ingreso el apóstrofo en la celda A8 y en seguida el número 00-00-00-45 y al pulsar Entrar se muestran los ceros a la izquierda, pero puedes observar que Excel los ha alineado a la izquierda porque los identifica como un texto.

Activaré la celda A8 y en la barra de fórmulas removeré el apóstrofo que se muestra al inicio de la cadena de texto y al pulsar Entrar desaparecerán los ceros de la izquierda.

Si queremos que Excel despliegue en pantalla esos ceros a la izquierda, podemos utilizar el formato personalizado.

Seleccionaré las celdas que contienen los números y hasta la fila 10 y haré clic derecho para elegir la opción Formato de celdas.

Me aseguro de tener activa la pestaña Número y en seguida haré clic en la categoría Personalizada y te mostraré lo que significan las opciones del panel de la derecha.

En primer lugar, tenemos la sección Muestra la cual despliega el valor de una de las celdas seleccionadas y la manera en que se desplegaría en pantalla después de aplicar el formato elegido.

En este momento solo se muestra un número de 8 dígitos porque tenemos activado el formato Estándar.

Debajo tenemos un cuadro de selección que contiene un listado de formatos personalizados previamente establecidos y de los cuales podemos elegir alguno, pero en esta ocasión crearemos un nuevo formato personalizado.

Para ingresar nuestro código de formato debemos hacer clic en el cuadro de texto Tipo, y eliminaré la palabra Estándar para ingresar 8 veces el número cero.

Este código le dice a Excel que, en caso de tener un número formado por menos de 8 dígitos, debe rellenar el resto de los espacios con un cero a la izquierda hasta completar las 8 posiciones.

Pulsaré el botón Aceptar para que se aplique este formato a las celdas y puedes observar que los valores de las celdas A7 y A8 ya muestran los ceros a la izquierda para completar los 8 dígitos que debe tener cada número.

Si en la celda A9 ingreso el número 1, se desplegarán automáticamente los 7 ceros de la izquierda.

Y sucederá lo mismo al ingresar el número 2 en la celda A10. De esta manera puedes pedirle a Excel que muestre en pantalla los ceros a la izquierda de cualquier número.

El segundo ejemplo que te mostraré se refiere a dar un formato de número telefónico a los dígitos que tenemos en una celda.

En la columna C tengo un listado de números telefónicos que no tienen un formato que nos ayude a distinguir adecuadamente este tipo de dato.

En este ejemplo, supondré que el código de área está compuesto de los primeros 3 dígitos y queremos colocarlo entre paréntesis.

Los números restantes tendrán un guion medio después del tercer dígito.

Para aplicar el formato, seleccionaré las celdas que contienen los números y de nueva cuenta necesito abrir el cuadro de diálogo Formato de celdas, pero en esta ocasión pulsaré el atajo CTRL + 1 y se mostrará la ventana.

Activaré la categoría Personalizada y en la caja de texto Tipo ingresaré el siguiente código.

Abro el paréntesis y enseguida ingresaré el símbolo numeral y cerraré el paréntesis.

Quiero que observes la sección Muestra donde ahora tenemos el número encerrado en paréntesis.

Esto quiere decir que el símbolo numeral representa cualquier número y por esa razón Excel coloca todos los dígitos dentro del paréntesis.

Ahora observa lo siguiente. A la derecha del paréntesis ingresaré un espacio en blanco y otro símbolo numeral y eso ocasiona que el último dígito de la derecha sea colocado fuera del paréntesis.

Ingresaré dos símbolos numerales más y puedes notar que los dígitos se van acomodando en base a las posiciones que he indicado en el código de formato.

En este momento, tenemos 3 dígitos fuera del paréntesis e ingresaré un guion medio seguido de 4 símbolos numerales y ahora puedes notar que la sección Muestra despliega el formato tal como lo necesitamos.

Al pulsar el botón Aceptar, todas las celdas tendrán el formato de teléfono que hemos definido anteriormente, pero quiero que observes que la barra de fórmulas continúa mostrando el número sin formato alguno.

Es decir, los formatos solamente son aplicados en la celda y modifican la manera en que es desplegado el número en pantalla.

Para el siguiente ejemplo utilizaremos los valores de la columna E que representan el peso en kilogramos de un grupo de personas. Lo que queremos hacer con el formato personalizado es agregar las letras “kg” en cada celda de manera que quede claro que los valores están expresados en kilogramos.

Seleccionaré las celdas que contienen los números y en seguida pulsaré el atajo CTRL +1 y se mostrará el cuadro de diálogo Formato de celdas y en la categoría personalizada ingresaré el símbolo numeral seguido de un espacio en blanco y colocaré unas comillas dobles porque voy a ingresar las letras “kg” y finalmente cerrare las comillas dobles.

Observa que la muestra ya despliega el valor numérico seguido de las letras “kg”, así que al pulsar el botón Aceptar se reflejará este cambio en todas las celdas.

Para comprobar que seguimos teniendo valores numéricos, seleccionaré las celdas que contienen el peso y en seguida iré a la pestaña Inicio y dentro del grupo Edición, haré clic en el menú del botón Autosuma y elegiré la opción Promedio.

Excel inserta la formula correspondiente que nos ayuda a saber el promedio de todos los valores indicados en las celdas superiores.

De esta manera hemos dado un formato personalizado a las celdas para mostrar que son kilogramos, pero dichas celdas siguen teniendo un valor numérico que será utilizado en los cálculos.

Para nuestro último ejemplo, tenemos una lista de precios de productos en dólares y voy a obtener el precio equivalente en pesos mexicanos utilizando el tipo de cambio indicado en la celda K1.

Pero primero daré formato básico a la lista de precios en dólares así que seleccionaré todas las celdas y en la lista de formatos de la pestaña Inicio elegiré el formato Moneda.

Ahora haremos la conversión a pesos, y en la celda H1 ingresaré el símbolo igual y en seguida la referencia a la celda G2 y lo multiplicaré por el valor de la celda K1. Ya que esta fórmula será copiada hacia abajo, pulsaré el atajo de teclado F4 para convertir esta referencia en absoluta.

Pulso la tecla Entrar y obtengo el precio equivalente en pesos, así que solo haré doble clic en el controlador de relleno de la celda H1 para copiar la fórmula haca abajo y obtener el resto de precios.

Sin embargo, nuestro jefe nos ha pedido identificar cada celda con la moneda correspondiente, es decir, necesitamos anteponer las letras USD a los dólares y las letras MXN a los pesos.

Seleccionaré las celdas que contienen los precios en dólares y pulsaré el atajo CTRL +1 y activaré la categoría Personalizada y puedes observar que en este momento el cuadro de texto Tipo ya muestra un código de formato que está siendo utilizado por las celdas.

Así que, sin modificar el código actual, simplemente moveré el cursor al inicio del código e ingresaré las letras USD encerradas en comillas dobles y seguidas de un espacio en blanco y al pulsar el botón Aceptar tendremos la leyenda en todas las celdas.

Haré lo mismo para la otra columna, seleccionaré las celdas y pulsaré el atajo CTRL + 1 y en la categoría Personalizada, insertaré las letras MXN encerradas en comillas dobles y seguidas de un espacio en blanco.

Pulso el botón Aceptar y tenemos claramente identificadas las monedas en cada columna.

Para comprobar que seguimos teniendo valores numéricos en las celdas, puedo cambiar el valor de la última celda de la columna dólares e ingresaré el precio 500 y al pulsar Entrar, la fórmula de la derecha actualizará automáticamente el precio que está en pesos mexicanos.

Además, seleccionaré el rango completo de precios y pulsaré el botón Autosuma y obtendré el total de cada columna en su respectiva moneda.

Estos son algunos ejemplos de cómo podemos utilizar el formato personalizado para presentar de una mejor manera los datos, pero sin afectar el valor numérico de las celdas con el cual se realizan los cálculos.

Te invito a practicar estos formatos personalizados en tus hojas de trabajo porque estoy seguro que llegará el momento en que utilizarás alguno de ellos.