Formato Personalizado De Celdas En Excel 234t1v

https://sites.google.com/site/glgexcel2007av anzado/ Formato personalizado de celdas en Excel Por Moisés Ortíz el 20 septiembre, 2012 Con el formato personalizado de celdas en Excel podemos dar una apariencia particular al valor de dicha celda. Un formato personalizado puede contener hasta 4 códigos diferentes dentro de la misma cadena de texto.

Formato personalizado de una celda Para asignar un formato personalizado a una celda en Excel solamente debes hacer clic derecho sobre la celda deseada y seleccionar la opción Formato de celdas. Al mostrarse el cuadro de diálogo debes asegurarte de estar en la sección Número y elegir la opción Personalizada:

El formato personalizado de la celda se debe especificar dentro del cuadro de texto Tipo. Un formato personalizado es una cadena de texto que indicará a Excel la manera en cómo debe mostrarse el valor contenido en una celda.

El formato personalizado en Excel En términos de formato personalizado de celdas en Excel, cada celda puede tener 4 posibles valores: positivos, negativos, cero y texto. De esta manera un formato personalizado nos permite especificar un código para cada uno de ellos dentro de la misma cadena de texto con la condición de separarlos por un punto y coma (;) con el siguiente orden:

Cada uno de los recuadros de la imagen representa un código, de manera que podemos indicar a Excel que trate de manera diferente el valor de una celda de acuerdo al tipo de valor que tenga.

Ejemplo de formato personalizado de una celda Para tener claro la manera en que Excel aplica un formato personalizado a una celda crearemos el siguiente formato: [Azul]Estándar;[Rojo]-Estándar;[Negro]Estándar;[Verde]Estándar

La palabra Estándar significa que Excel dará un trato “normal” al valor de la celda, es decir, no modificará su apariencia. Sin embargo, entre cada corchete [] he especificado un color diferente de acuerdo al valor de la celda. Si el valor de la celda es positivo debe mostrarse de color azul, si es negativo de color rojo y además he colocado el símbolo “-“ de manera que Excel coloque el símbolo negativo precediendo el valor de la celda. Si el valor es cero entonces se mostrará de color negro y si es de tipo texto será de color verde. En la siguiente animación podrás observar cómo se comporta una celda que tiene este formato personalizado:

Al usar formato personalizado de celdas en Excel solamente tendremos disponibles 8 nombres de colores para modificar la apariencia de una celda: Negro, Verde, Blanco, Azul, Magenta, Amarillo, Aguamarina, Rojo. Existe otra opción que nos permite elegir un color por su código y tendremos disponibles hasta 56 colores diferentes. Este código lo podemos especificar de la siguiente manera: [Color5]Estándar;[Color20]-Estándar;[Color40]Estándar;[Color50]Estándar

A diferencia del ejemplo anterior donde colocamos directamente el nombre del color, en este ejemplo debemos indicar el número del color que deseamos aplicar.

Códigos de formato personalizado Ya hemos visto cómo modificar el color de una celda de acuerdo a su valor pero también podemos modificar otros elementos como la cantidad de decimales que deseamos mostrar. En el siguiente ejemplo he especificado un formato personalizado que hará que siempre se muestren 3 decimales en todas las celdas. Observa el resultado:

El código # nos ayuda a representar la posición de un número y hemos indicado que deseamos tener 3 posiciones después del punto decimal. Para conocer más sobre el código # así como otros códigos te sugiero consultar el articulo Códigos de formato personalizado.

Formato personalizado a texto Una pregunta muy frecuente de los s de Excel es saber si se puede aplicar un formato personalizado a un texto. En realidad los formatos personalizados fueron creados para dar formato a números y por consecuencia también a las fechas ya que las fechas en Excel son también números. Los formatos personalizados solo nos permiten efectuar algunas acciones con texto como hacer que se despliegue un carácter en cierta posición cuando acompaña a un número. En el siguiente ejemplo he utilizado el formato personalizado #! que colocará el signo de exclamación al final del valor de la celda.

Observa cómo la barra de fórmulas muestra que el valor de la celda es 365 pero el formato personalizado indica a Excel que se agregue el símbolo “!” al final del valor. Así como agregamos un solo carácter también podemos agregar una palabra completa al final de un número. El formato personalizado que utilizaré será el siguiente: #.## “pesos”

De nueva cuenta observa la barra de fórmulas y observa que el valor de la celda no considera la palabra “pesos” sino que es agregada por el formato personalizado. Tanto los caracteres como las palabras se pueden colocar en cualquier posición que queramos, solamente debemos indicarlo correctamente dentro del formato personalizado. Recuerda que el formato personalizado de celdas en Excel no es una manera de manipular cadenas de texto sino que fue creado primordialmente para ayudarnos a dar formato a datos numéricos. Puedes leer un artículo complementario que escribí sobre el tema de formatos personalizados de números. Artículos relacionados Formato de fechas y horas Ocultar el contenido de una celda con formato personalizado Compartir → inShare

9 comentarios para Formato personalizado de celdas en Excel

1.

Andy Williams dice: 28 diciembre, 2012 a las 1:58 pm Muchas gracias por la publicación me resulto muy útil, felicidades Responder

2.

Vanessa dice: 30 diciembre, 2012 a las 9:34 pm

Me gustó mucho este artículo, espero encontrar en tu web lo que necesito para armar mi hoja de datos, muchas gracias por tu trabajo Saludos Responder

3.

Edgar dice: 20 febrero, 2013 a las 6:31 pm Excelente explicación!, me gustaria saber que formato personalizado debo usa para que me aparezca el dia de semana con la primera letra en mayuscula, seguido de la fecha en numero, ejemplo Miercoles, 20/02/2013, utilizo en formato personalizado dddd, dd/mm/yyyy ,pero me da la fecha en letras en minusculas. Gracias. Saludos. Responder

o

Moisés Ortíz dice: 21 febrero, 2013 a las 8:12 pm Hola Edgar, el hecho de que los días aparezcan en minúsculas trasciende al formato personalizado de Excel y no conozco algún “parámetro” que permita hacer este cambio. Te recomiendo utilizar la función TEXTO para dar el formato adecuado. Si la fecha estuviera en la celda A1, puedes utilizar la siguiente fórmula: =NOMPROPIO(TEXTO(A1,”dddd dd/mm/aaaa”)) Esta fórmula me da el siguiente resultado: Miércoles 20/02/2013 Responder

4.

Vanesa dice: 6 marzo, 2013 a las 12:46 pm Hola Moisés! Lamento molestar, pero me estoy volviendo loca con este tema.

Necesito que la celda me aparezca “xxx kcal/yyy kJ”. En formato personalizado he puesto 0″ kcal”/0″ kJ” y me agrega sin problema la parte de texto, pero me pasa los números a fracción. Por ejemplo, si escribo 10/2 me aparece 5 kcal/1kJ. Tambien he reemplazado el 0 por #, sin exito. Espero puedas ayudarme, ya que tengo que hacer una tabla para el trabajo larguisima con este formato! Gracias!!! Vanesa Responder

o

Moisés Ortíz dice: 7 marzo, 2013 a las 5:50 pm Hola Vanesa, hay un problema mas grande que el formato personalizado porque parece que quieres que una misma celda tenga el valor numérico 10 kcal y además el valor numérico 2 kJ y eso no es posible en Excel bajo ninguna circunstancia. Si ingresas en una celda el valor 10/2 inevitablemente Excel realizará la división de ambos números. Lo más natural en Excel sería ingresar el valor 10 en una celda y el valor 2 en otra celda. Luego con fórmulas crear la cadena de texto “10 kcal/2 kJ”. Responder

5.

Rafa dice: 7 marzo, 2013 a las 4:55 am Muy buena explicación Moisés. En mi caso la duda que tengo no se si está relacionada con los formatos personalizados de celda, los formatos condicionales o con ambos. Lo que necesito es que dentro de una celda que tiene concatenadas 2 cadenas de texto una de ellas se vea y la otra no. He intentado buscar un formato condicional indicando la palabra de una de las cadenas de texto y que en el formato, dicho texto me aparezca en blanco mientras que el resto del texto está en negro. Aun no he conseguido saber como hacerlo.

Otra de mis dudas es si el formato personalizado permite tocar el tamaño de parte de la cadena de texto de una celda. No soy un experto en este tema, mas bien me considero un recién iniciado por lo que quizás estoy buscando en el sitio no adecuado (formatos). Muchas gracias por tu ayuda, Un saludo, Responder

o

Moisés Ortíz dice: 8 marzo, 2013 a las 12:16 pm Hola Rafa, lo que intentas hacer será excesívamente complicado, si no es que imposible de lograr, utilizando el formato personalizado de celdas o el formato condicional. La mejor solución es crear una macro que busque dentro de una celda la palabra y modifique su color y el tamaño de la fuente. Responder

6.

onelia landecho dice: 18 marzo, 2013 a las 3:20 pm muy interasante, didáctica tu forma de traspasar conocimientos. Son de mucha ayuda, para avanzar en los formatos de excel gracias,Onelia Responder

Deja un comentario Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados * Nombre * Correo electrónico *

Comentario

 o

Nombre (sin apellidos):

o

Email:

o  



TUTORIALES • Fundamentos de Excel • Fórmulas y funciones • Gráficos y presentación de datos • Uso de tablas • Formato condicional • Tablas dinámicas • Macros en Excel • Introducción a Access



LO MÁS LEÍDO • La función BUSCARV en Excel • Función SI anidada • La función SI en Excel • Diagrama de Gantt en Excel • Función BUSCARV en Excel 2010 • Fórmulas de Excel • Función CONSULTAV explicada • Fromato personalizado en Excel • Diagrama de Pareto en Excel • La función SUMAR.SI en Excel



Unidad 17. Características avanzadas de Excel En este tema estudiaremos algunas características avanzadas de Excel que nos pueden ser de utilidad cuando queramos realizar algún trabajo un poco más profesional o que se salga un poco de lo corriente.

Diferentes formas de cambiar un formato Para modificar el formato de una o varias celdas, podemos elegir entre las distintas formas que nos proporciona Excel, es interesante conocerlas todas para luego elegir la que nos resulte más cómoda. Estas son las diferentes formas que podemos utilizar: 

Utilizando la pestaña Inicio.

Ya hemos visto, en temas anteriores, que en la pestaña Inicio, tenemos varios iconos para asignar de forma rápida un determinado formato a los números incluidos en las celdas.

Los formatos incluidos en la barra son los más comunes.



Utilizando las teclas de rápido.

También podemos escoger un formato utilizando las teclas de rápido que exponemos en la siguiente tabla: Teclas de Rápido

Efecto

Aplicar el formato Moneda con dos CTRL + MAYÚS + $ decimales (los números negativos aparecen en rojo). Aplicar el formato Porcentaje sin decimales. Aplica el contorno a las celdas CTRL + MAYÚS + & seleccionadas.

CTRL + MAYÚS + %

CTRL + ;

Inserta la fecha actual.

CTRL + MAYÚS + : Inserta la hora actual. CTRL + MAYÚS + !

Aplicar el formato Número con dos decimales, separador de miles y signo menos (-) para los valores negativos.

Esta forma me permite aplicar también en un sólo paso un formato, incluye formatos que no tenemos en la barra Formato, pero es tal vez más complicada de utilizar ya que requiere que memoricemos las combinaciones de teclas.



Utilizando el cuadro de diálogo Formato de celdas.

Esta última forma es menos rápida ya que requiere de varios pasos (abrir el cuadro de diálogo, elegir la pestaña adecuada y luego el formato deseado), pero permite utilizar muchos más formatos y sobre todo nos permite ver todo el

amplio abanico de formatos que nos ofrece Excel, además de permitir que definamos nuestro propio formato personalizado. Para abrir el cuadro de diálogo Formato de celdas podemos elegir entre: - Hacer clic en la flecha que aparece al pie de la sección Número (en la imagen más arriba). - Hacer clic con el botón derecho sobre la celda y escogiendo del menú contextual la opción Formato de celdas... - Presionar la combinación de teclas Ctrl + 1. Al realizar cualquiera de estas operaciones nos aparece el cuadro de diálogo como el que vemos en la imagen donde podemos escoger entre los diversos formatos numéricos.

Definir formatos personalizados Excel provee una gran cantidad de formatos numéricos, pero es posible que en algún momento necesitemos algún formato que no esté disponible en los estándares de Excel. Para subsanar esta carencia Excel dispone de una herramienta para crear formatos personalizados. Por defecto todas las celdas de Excel tienen el formato de número general. Su funcionamiento se basa en que lo que escribimos es lo que tenemos, es decir se adapta a lo que hayamos escrito, ya sea texto, números con decimales, porcentajes, etc. Para crear un nuevo formato debemos acceder al cuadro de diálogo Formato de celdas, seleccionar la Categoría: Personalizada y seguidamente en el cuadro de texto Tipo: debemos escribir el código del formato que deseamos crear.

Para crear un formato de número personalizado, primero debemos conocer las reglas de los códigos para crear un formato. El formato se compone de cuatro secciones separadas por; (punto y coma).

____________ ;____________ ; ____________ ; ____________ La primera sección define el formato que tendrá el número en la celda si es positivo; la segunda, si el número es negativo, la tercera, si el número vale cero; la cuarta si la celda contiene texto. 0 : Reserva un dígito para un número, si no se completa el tamaño definido por el formato se completa con ceros. # : Reserva un dígito para un número, pero si no se completa el tamaño definido por el formato se deja en blanco.

? : Reserva un dígito. Añade espacios en ceros insignificantes. . : Punto decimal. % : Símbolo porcentual. , : Separador de millares. E- E+ e- e+ : Notación científica. $ - + / ( ) : : Muestra estos caracteres. No necesitan comillas. \ : Muestra el siguiente carácter en el formato. * : Repite el siguiente carácter hasta completar el ancho de la celda. _ : Deja un espacio "texto" : Muestra el texto escrito entre la comillas dobles. @ : Espacio reservado para texto. [color] : Muestra el carácter en el color especificado. Puede ser Negro, Azul, Cian, Verde, Magenta, Verde, Rojo y Amarillo. [color n] : Muestra el correspondiente color de la paleta de colores, n puede ser un número entre 0 y 56. [valor condición] : Permite escoger tu propio criterio para cada sección de un formato numérico. m : Muestra el mes como número sin ceros (1-12). mm : Muestra el mes como número con ceros (01-12). mmm : Muestra el nombre del mes como abreviación (Ene, Feb). mmmm : Muestra el nombre del mes por completo (Enero, Febrero).

mmmmm : Muestra la inicial del mes (E, F). d : Muestra el día como número sin ceros (1-31). dd : Muestra el día como número con ceros (01-31). ddd : Muestra el nombre del día como abreviación (Lun, Mar). dddd : Muestra el nombre del día por completo (Lunes, Martes). yy o yyyy : Muestra el año en dos dígitos (00-99) o cuatro (1900-9999). h o hh : Muestra la hora como números sin ceros (0-23) o como números con ceros (00-23). m o mm : Muestra los minutos como números sin ceros (0-59) o como números con ceros (00-59). s o ss : Muestra los segundos como números sin ceros (0-59) o como números con ceros (00-59). AM/PM : Muestra la hora en formato de 12 horas, si no se indica esta opción se muestra la hora en formato 24 horas. Por ejemplo, puedes utilizar el siguiente formato personalizado:

€#,##[Verde];(0,00€)[Rojo];"Cero";"" Este formato contiene cuatro secciones separadas por el signo de punto y coma y utiliza un formato diferente para cada sección. Con este formato estamos indicando que los números positivos (1ªsección) se escriben en verde, llevan el signo del euro delante, no se muestran los ceros no significativos y solo se muestran 2 decimales (#. ##); los números negativos se escriben en rojo, entre paréntesis con el € detrás y con dos decimales siempre; los valores cero se sustituyen por la palabra cero y los textos por la cadena nula, es decir que si en la celda hay texto, ese texto desaparecerá.

El formato condicional

El formato condicional sirve para que dependiendo del valor de la celda, Excel aplique un formato especial o no sobre esa celda. El formato condicional suele utilizarse para resaltar errores, para valores que cumplan una determinada condición, para resaltar las celdas según el valor contenido en ella, etc... Cómo aplicar un formato condicional a una celda: - Seleccionamos la celda a la que vamos a aplicar un formato condicional. - Accedemos al menú Formato condicional de la pestaña Inicio. Aquí tenemos varias opciones, como resaltar algunas celdas dependiendo de su relación con otras, o resaltar aquellas celdas que tengan un valor mayor o menor que otro.

Utiliza las opciones Barras de datos, Escalas de color y Conjunto de iconos para aplicar diversos efectos a determinadas celdas. Nosotros nos fijaremos en la opción Nueva regla que permite crear una regla personalizada para aplicar un formato concreto a aquellas celdas que cumplan determinadas condiciones. Nos aparece un cuadro de diálogo Nueva regla de formato como el que vemos en la imagen.

En este cuadro seleccionaremos un tipo de regla. Normalmente querremos que se aplique el formato únicamente a las celdas que contengan un valor, aunque puedes escoger otro diferente. En el marco Editar una descripción de regla deberemos indicar las condiciones que debe cumplir la celda y de qué forma se marcará. De esta forma si nos basamos en el Valor de la celda podemos escoger entre varias opciones como pueden ser un valor entre un rango mínimo y máximo, un valor mayor que, un valor menor que y condiciones de ese estilo.

Los valores de las condiciones pueden ser valores fijos o celdas que contengan el valor a comparar. Si pulsamos sobre el botón Formato... entramos en un cuadro de diálogo donde podemos escoger el formato con el que se mostrará la celda cuando la condición se cumpla. El formato puede modificar, el color de la fuente de la letra, el estilo, el borde de la celda, el color de fondo de la celda, etc. Al pulsar sobre Aceptar se creará la regla y cada celda que cumpla las condiciones se marcará. Si el valor incluido en la celda no cumple ninguna de las condiciones, no se le aplicará ningún formato especial. Si pulsamos sobre Cancelar, no se aplicarán los cambios efectuados en el formato condicional.

La validación de datos La validación de datos es muy similar al formato condicional, salvo que esta característica tiene una función muy concreta y es validar el contenido de una celda; pudiendo incluso mostrar un mensaje de error o aviso si llegara el caso. Para aplicar una validación a una celda. - Seleccionamos la celda que queremos validar. - Accedemos a la pestaña Datos y pulsamos Validación de datos. Nos aparece un cuadro de diálogo Validación de datos como el que vemos en la imagen donde podemos elegir entre varios tipos de validaciones.

En la sección Criterio de validación indicamos la condición para que los datos sean correctos. Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y personalizada. Por ejemplo si elegimos Número entero, Excel sólo permitirá números enteros en esa celda, si el intenta escribir un número decimal, se producirá un error. Podemos restringir más los valores permitidos en la celda con la opción Datos:, donde, por ejemplo, podemos indicar que los valores estén entre 2 y 8. Si en la opción Permitir: elegimos Lista, podremos escribir una lista de valores para que el pueda escoger un valor de los disponibles en la lista. En el recuadro que aparecerá, Origen: podremos escribir los distintos valores separados por; (punto y coma) para que aparezcan en forma de lista. En la pestaña Mensaje de entrada podemos introducir un mensaje que se muestre al acceder a la celda. Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa celda. En la pestaña Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al cuando introduzca en la celda un valor incorrecto.

Además de definir condiciones de validación para los datos, podemos controlar que una página esté libre de errores, si quieres saber más sobre cómo localizar y depurar errores haz clic aquí

.

Enlazando y consolidando hojas de trabajo 

Enlazar hojas de trabajo.

El concepto de enlazar en Excel es el hecho de utilizar fórmulas de varias hojas para combinas datos. Al enlazar hojas de trabajo estamos creando una dependencia de una con respecto a la otra, apareciendo así dos conceptos: - el libro de trabajo dependiente: es el que contiene las fórmulas. - el libro de trabajo fuente: es el que contiene los datos. La cuestión que debemos plantearnos antes de enlazar hojas de trabajo, es si realmente nos hace falta complicarnos tanto o sencillamente podemos hacerlo todo en una misma hoja. No existe una respuesta genérica, dependerá de la envergadura de los datos y de las fórmulas; si las hojas las van a utilizar varias personas, etc... Para crear un libro de trabajo dependiente debemos crear fórmulas de referencias externas, es decir fórmulas que hacen referencia a datos que se encuentran en una hoja externa a la que está la fórmula. ¿Cómo crear fórmulas de referencias externas? Para crear fórmulas de referencia externa debemos seguir los siguientes pasos: - Abrir el libro de trabajo fuente (el libro que contiene los datos). - Abrir el libro de trabajo dependiente y seleccionar la celda donde queremos incluir la fórmula.

- Introducir la fórmula de la forma que hemos hecho siempre, cuando se llegue al punto de escoger las celdas de datos, activar el libro de trabajo fuente y seleccionar las celdas necesarias. - Terminar la fórmula y pulsar Intro. Las referencias al libro externo las gestiona automáticamente Excel, incluso si cambiamos el nombre del archivo donde están los datos desde Archivo → Guardar como... las referencias también se cambiarían. Si en el libro de trabajo dependiente queremos ver todos los vínculos a las páginas fuente podemos hacerlo accediendo a la pestaña Datos y haciendo clic en el botón Editar vínculos... Nos aparece el cuadro de diálogo Modificar vínculos donde podemos hacer modificaciones sobre los vínculos.

Actualizar valores: Actualiza la lista con los vínculos. Cambiar origen: Podemos modificar el origen del vínculo seleccionado. Abrir origen: Abre el libro del vínculo seleccionado. Romper vínculo: Quita los vínculos de las fórmulas externas. Comprobar estado: Comprueba el estado del origen.

Pregunta inicial: Podemos incluir una advertencia al abrir el libro dependiente, indicando que tenemos enlaces externos.



Consolidar hojas de trabajo.

El concepto de consolidar hojas de trabajo viene muy ligado al concepto de enlace que acabamos de ver. Cuando hablamos de consolidar hojas de trabajo estamos entablando una relación entre varias hojas de trabajo, por tanto es muy posible que existan enlaces entre esas hojas de trabajo. Un ejemplo donde se puede utilizar la consolidación de hojas de trabajo puede ser una compañía que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearíamos una hoja dependiente y los libros fuentes serían los libros de los departamentos con sus prepuestos.

Si pulsamos sobre la pestaña Datos y pulsando el botón Consolidar... nos aparece el cuadro de diálogo Consolidar como vemos en la imagen donde podemos escoger: - la Función: a utilizar, - en Referencia: vamos seleccionando las celdas de uno de los libros

fuentes, - pulsamos sobre Agregar para añadir las celdas seleccionadas a la lista de todas las referencias, - repetimos los dos últimos pasos para seleccionar las celdas de los diferentes libros fuentes, - finalmente pulsamos en Aceptar para finalizar la fórmula. Si marcamos la casilla Crear vínculos con los datos de origen, los datos de los libros fuente se incluirán en el libro consolidado a modo de esquema, y si cambiamos un valor en el origen, ese cambio quedará automáticamente reflejado en el libro consolidado.

Excel e Internet Excel nos ofrece la posibilidad de compartir nuestras hojas de trabajo en la Web y también recoger información de Internet. 

Convertir una hoja en página web.

Para poder exponer ("colgar") en Internet nuestro trabajo realizado en Excel, debemos convertirlo a un formato reconocible por los navegadores (html o xml). Para hacer esto debemos seguir los siguientes pasos: - Pulsar sobre el Botón Office. - Seleccionar la opción Guardar como.

- En el cuadro de diálogo Guardar como tipo tendremos que seleccionar el tipo Página Web en Guardar como tipo: En esta pantalla disponemos de dos posibles modos de guardar la página Web: Una vez hayamos elegido la opción que más nos interesa según nuestras necesidades podemos pulsar sobre Guardar y tendremos nuestra hoja Excel guardada como una página web con la extensión “.htm” en lugar del clásico “.xls”. Una vez tenemos nuestra hoja convertida a formato “html” tendremos que subirla a un servidor de Internet para que pueda ser vista por los internautas.

Ejercicios Tenéis que realizar los siguientes ejercicios para saber los conocimientos que

habéis adquirido de ésta Unidad 17.