Como usar Formatar Células Personalizado no Excel

Neste artigo você aprenderá como formatar células de modo personalizado no Excel.

Esta é uma função que vai muito além do formatar CPF, CNPJ e CEP que utilizamos normalmente.

Com a formatação de células personalizada você conseguirá realizar o conteúdo de células com textos com visuais diferentes, textos, cores, formatação diferente para números positivos, negativos, zeros e textos, pode aplicar inclusive fórmulas condicionais diretamente na formatação personalizada.

Vejamos então as várias formatações que podemos aplicar nas células usando a Formatação Personalizada no Excel.

Com Realizar a Formatação Personalizada de Células no Excel

As células podem ser formatadas em inúmeros formatos no Excel, muitos já vem prontos.

Mas há situações em que precisamos uma formatação personalizada da célula no Excel, para isso podemos usar a opção Personalizado.

Para acessar esta opção pressione CTRL+1 ou senão clique com o botão direito sobre o intervalo de células que deseja formatar e escolha a opção Formatar Células.

Em seguida escolha na guia Número a opção Personalizado.

Ao longo deste artigo você verá as diversas possibilidades do Personalizado no Formatar Células do Excel.

Atalhos de Formatação Personalizado

O Excel possui muitos atalhos e alguns deles são para formatação personalizada.

Isso facilita bastante as coisas, evitando que tenha que usar o mouse.

Os que principalmente recomendo são os abaixo que são os mais comuns:

1. CTRL+~ : Número Geral
2. CTRL+$ : Formato de Moeda
3. CTRL+% : Percentual
4. CTRL+^ : Número Científico
5. CTRL+# : Formato de data
6. CTRL+@ : Formato de hora
7. CTRL+1 : Abre o formato personalizado de células no Excel

A formatação fica como no quadro acima e se quiser mais atalhos baixe nosso e-book gratuito com 220 atalhos do Excel em: E-BOOK GRATUITO 220 ATALHOS DO EXCEL.

Formatar Células Personalizado para Data e Hora no Excel

A formatação de data e hora no Excel é um ponto a ser entendido antes de iniciar.

Toda formatação de data e hora no Excel na verdade não é uma data e hora e sim um número.

Considere que um dia é igual a 1 e a data no Excel começa no dia 1 que é igual ao dia 01/01/1900.

Desta forma, no dia 05/04/2021 estamos no dia 44291 no Excel.

Faça o teste digitando 44291 e usando o atalho Ctrl+#, veja como fica, ele não mudou o número, mas mudou o formato.

Entendido isso, temos ainda que um dia é igual a 1, e os seus decimais partes de hora, minuto, segundo, milésimos.

Então o formato de tempo está sempre após a vírgula dos decimais.

O Excel possui os seguintes coringas para formatação de data e hora:

1. aaaa : Este é o formato de ano, colocando dois “a” o Excel formata apenas com as últimas duas casas do ano, com 4 “a”, são exibidos os 4 dígitos do ano.
2. mmm : Esta formatação exibe o nome abreviado do mês na célula.
3. mmmm : Exibe o mês por extenso no Excel.
4. d : O dia do mês com um número apenas, sem zeros insignificantes.
5. dd : O dia do mês com zero insignificante à esquerda.
6. ddd : Dia da semana abreviado.
7. dddd : Dia da semana por extenso.
8. hh : Hora.
9. hh:mm : Hora e minuto.
10. hh:mm:ss : Hora, minuto e segundo na célula.
11. [h]:mm : Tempo transcorrido em horas no Excel, muito utilizado para somas e cálculos.
12. [mm] : Tempo transcorrido em minutos.
13. [ss] : Tempo transcorrido em segundos.

Estrutura da Formatação Personalizada no Excel

Entenda agora a estrutura da formatação personalizada no Excel.

Veja o quadro abaixo:

A formatação de células no Excel é composta por 4 partes com relação a formatação dos seus dados.

1. Números positivos: Nesta primeira parte temos a formatação de números positivos e também de partes que não foram informadas. Por exemplo, se não informar o formato para número negativo, será usado o formato Positivo, o mesmo para os demais.
2. Números negativos: formatação para números negativos, a separação dos itens é feita com ponto e vírgula.
3. Nulo ou Zero: Para número 0 ou Nulo a formatação é a terceira, então você passa o formato que deseja para estes valores.
4. Texto: Este último é o formato personalizado utilizado para células com texto.

Lembrando que caso algum destes não seja informado será utilizado o formato do Positivo para a formatação.

Formatar zeros insignificantes no Formatar Células Personalizado

Você já deve ter percebido na formatação de células o número “0”.

Este número zero significa exibir números zeros insignificantes.

Ou seja, digamos que tenhamos o número 0,1 e queiramos exibir o 0,10 ao invés de 0,1.

Neste caso usamos a formatação personalizada 0,00 para que seja exibido o zero insignificante da esquerda e da direita.

Esta formatação é muito útil para termos uma padronização na formatação.

Nos exemplos acima você pode notar que também podemos colocar zeros à esquerda como desejar e inclusive mostrar o valor arredondado, simplesmente não colocando a vírgula.

Como por exemplo 23,5, e queremos exibir no formato 0024, então colocamos na formatação personalizada 0000, sem a vírgula e dizendo que queremos pelo menos 4 zeros insignificantes.

Um ponto importante é a salientar é que independente da quantidade de zeros que colocar, o número principal será totalmente exibido.

Por exemplo 123456, e formatamos ele para 0000, ele irá exibir 123456, pois passam de 4 o número e por isso não há necessidade do zero insignificante ser exibido.

Formatar Células Personalizado com zeros significantes

Da mesma forma que temos os zeros insignificantes que queremos exibir, podemos também ocultar estes zeros.

Para isso o formato que usamos é o #.

O coringa # faz com que a formatação do zero não seja exibida no caso de ele ser insignificante, como no quadro abaixo:

Por exemplo, temos o valor 0,1, e queremos que não seja exibido o 0 da esquerda, então usamos #,##, isso fará com que sejam exibidos apenas a parte significativa do número, no caso ,1.

Como pode notar é muito útil, principalmente para decimais, evitando assim que mostre zeros que não deseja, mas para a parte inteira não fica tão bom.

Por isso o melhor é usar os dois em conjunto como você pode notar abaixo.

Formatar Zeros significantes e insignificantes em conjunto no Excel

Podemos usar qualquer coringa de formatação em conjunto.

Veja no exemplo abaixo aonde usamos os coringas 0 e # em conjunto para exibir um número formatado de até 3 casas decimais.

No formato acima temos o uso de 0 antes da vírgula para exibir o valor do número mesmo seja zero na sua parte inteira, e em seguida usamos ### para exibir até três casas decimais de números, mas somente se não forem 0.

Veja o exemplo: 1,200, será exibido 1,2 ao invés de 1,200 porque a parte dos zeros á direita é insignificante e o # os retira.

No entanto para um número 0,015, seria exibido exatamente 0,015, inclusive mostrando o 0 insignificante da esquerda, pois o formatamos com 0 antes da vírgula.

Formatar Células com Coringas no Excel

A formatação personalizada possui alguns coringas que permitem que realizemos diversas formatações interessantes, veja a seguir:

1. ? : A interrogação realiza o alinhamento dos decimais à direita. Não é muito utilizado, mas é interessante de saber.
2. , : A vírgula é muito utilizada é o separador de decimais
3. . no meio: O ponto quando usado dentro do número e não no final realiza a formatação de milhares. Como por exemplo #.###
4. . no final : O ponto no final do número divide o número por 1000, pode usar vários, colocando mais pontos ele irá mostrar o número arredondado para mil, milhões, bilhões. Conforme a quantidade de pontos inclusos, por exemplo 10569040917 com a formatação #.###.. ficará 10.569 “milhões”.
5. . e , : Pode usar em conjunto o ponto e vírgula para montar a formatação de decimal que desejar como por exemplo #.###,00 que é uma muito utilizada.
6. * : Este coringa repete um texto várias vezes, até preencher a célula. Lembrando que é só formatação, não texto realmente.
7. _ : Este coringa inclui um espaço na formatação personalizada, permitindo então que o número seja colocado um espaço do tamanho que deseja. Por exemplo, queremos um espaço do tamanho do caractere ), então colocamos _) para que seja incluso o espaço referente a este tamanho.

Abaixo o quadro com exemplos de uso destes coringas:

Formatação de Texto Personalizada no Excel

Algo muito interessante quando tratamos com a formatação personalizada de células é que podemos aplicar textos neles.

Como no exemplo abaixo colocamos textos de várias formas:

De um modo geral para incluir um texto utilize o “” e inclua o texto entre as aspas duplas.

Desta forma os textos serão inclusos diretamente, e pode usar também outros tipos de formatação concatenando-os, como nos casos acima citados.

Por exemplo: “Joinville, ” dd “de” mmmm “de” aaaa, nesta formatação temos a cidade e a data formatada e como saída temos: Joinville, 30 de março de 2021.

Formatação de Células Personalizada com Cores no Excel

É possível também adicionar cores na formatação personalizada de células.

Para incluir uma cor formatada basta digitar antes do campo positivo, negativo, zero ou texto [Vermelho], mudando o Vermelho pela cor que desejar.

No caso das cores Azul e Vermelho, pode digitar diretamente, para outras cores use [Cor26] A palavra Cor e o número correspondente.

Exemplos de como formatar células com cores usando a formatação personalizada no quadro abaixo.

Tabela de Cores na Formatação de Células Personalizada

Este é o quadro com as 56 cores que podem ser aplicadas para realizar Formatação Personalizada de Células no Excel:

Formatação Personalizada Condicional no Excel

Você também pode formatar as células de forma condicional.

Para isso usamos a condição entre [], por exemplo [<10] e separamos as condições com ponto e vírgula, [<10]Suaformatação;[>10]SuaFormatação;FormataçãoSeNãoAtenderNenhuma.

O formato de condicional pode ser aplicado com qualquer das formatações que vimos até agora, bastando colocar a formatação logo após a condição.

Por exemplo a formatação abaixo pode ser utilizada para formatar CPF ou CNPJ, de acordo com o número:

[<=99999999999]000″.”000″.”000-00;[>99999999999]00″.”000″.”000″/”0000-00

Outra formatação bastante usada é para usar o plural, veja o exemplo de dia e dias no singular e no plural:

[=1]#” dia”;[>1]#” dias”

Algo muito importante também é que você pode realizar cálculos mesmo que esteja formatado o número, inclusive com textos.

Isso significa que você pode usar a célula para realizar qualquer cálculo e o resultado será com a formatação que está pegando, por exemplo:

Uma célula que estiver formatada e estiver como 1 dia, se somar 1 em outra célula retornará 2 dias, usando a formatação: [=1]#” dia”;[>1]#” dias”

Ocultar valores com formatação personalizada

Outro ponto muito interessante é que podemos ocultar valores na formatação personalizada.

Para isso é bem simples, basta colocar o ponto e vírgula e não preencher o campo que deseja.

1. ;Geral;Geral;@ : Ocultar valores positivos na formatação da célula.
2. Geral;;Geral;@ : Ocultar valores negativos
3. Geral;Geral;;@ : Ocultar valor zero das células
4. Geral;Geral;Geral; : Ocultar textos das formatações de células

Como pode notar é bastante simples, e inclusive esta formatação pode ser aplicada em gráficos e qualquer objeto do Excel que permita a formatação de dados.

Assim ela se torna muito interessante e inclusive fazendo com que não se torne necessário o uso de fórmulas e formatações condicionais nas células deixando assim a sua planilha muito mais leve e também mais fácil de trabalhar.

Download

Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: