Formatos Personalizados de Números no Excel (com vídeo)

Formatos personalizados de números

Resumo

A Formatação Numérica controla a forma como os números são mostrados no Excel. A principal vantagem é que esta formatação muda o aspecto mas não altera os dados. O número continua a ser um número independentemente do aspecto que lhe queiramos dar. Esta ferramenta permite-nos formatar automaticamente os dados e confere às folhas de cálculo um aspecto mais profissional.

O que é um número formatado?

Um número formatado é um código especial que controla a forma como o valor é mostrado no Excel. Um exemplo bastante fácil de visualizar é a possibilidade de mostrar a data de várias formas distintas (sem alterar os dados; apenas alteramos a forma de mostrar e o que mostramos).
DataData em Númeroddmmmmaaaa
01/02/20204386201fevereiro2020

O que podemos formatar?

Podemos controlar a forma como visualizamos números, datas, frações, percentagens. Na prática, podemos controlar a formatação de quaisquer valores númericos.

Onde podemos aplicar a formatação numérica?

A formatação numérica está presente em praticamente todos os elementos do Excel: tabelas, gráficos, tabelas dinâmicas, fórmulas, etc.

Folha de Cálculo --> Formatação de Células
Tabelas Dinâmicas --> Formato Numérico...
Gráficos --> Formatar Rótulos de Dados e Formatar Eixo
Fórmulas --> Usando a Função TEXTO (Text)

Caminho e atalhos para a Formatação de Números

No friso "Base" encontramos um menu com uma seleção de formatos incorporados (ex.: Moeda, Percentagem, etc.). Por baixo temos a opção de entrar directamente na caixa de diálogo "Formatar Células".

Frisos Excel

Na caixa de diálogo temos todas as opções de formatação de número organizadas por categoria dentro do friso "Número".

Caixa de diálogo

Atalho de Teclas

A forma mais rápida de abrir esta caixa de diálogo é a combinação de teclas Control + 1. Outros formatos mais comuns têm os seguintes atalhos:
FormatoAtalho
GeralCtrl + Shift + ~
MoedaCtrl + Shift + $
PercentagemCtrl + Shift + %
CientíficoCtrl + Shift + ^
DataCtrl + Shift + #

Caixa de Diálogo de Formatação de Números Personalizados

Na caixa de diálogo de formatos pré-definidos encontra-se a opção "Personalizado" e dentro desta opção temos estilos personalizados pré-definidos que podemos usar ou podemos criar os nossos próprios estilos de formatação personalizada.

Números personalizados

Criar Formatos Personalizados de Números

Podemos criar os nossos próprios formatos de personalização da seguinte forma:

1) Selecionar a célula (ou células) com os valores que queremos personalizar
2) Abrir a Caixa de Diálogo de Formatação de Números (CONTROL + 1 | Números | Personalizado)
3) Introduzir a formatação
4) Verificar o resultado na pré-visualização IMPORTANTE!
5) Dar OK para salvar e aplicar

Editar um Formato pré-definido

Não é possível editar um formato existente mas é possível usar um formato existente como base de partida. Basta selecionar esse formato e de seguida alterar a partir da opção "Personalizado". Ao fazer alterações é criado um formato novo.

Nota: Atenção ao apagar formatos. Não existe a opção de desfazer (undo) depois de apagar!

Sintaxe

Os números personalizados do Excel têm uma estrutura específica que divide cada formatação em (até) quatro secções que são separadas por ponto e vírgula ";".

Secção 1 ; Secção 2 ; Secção 3 ; Secção 4

Esta sintaxe, se criarmos muitas regras, pode ficar extremamente complexa. O primeiro passo para entender os formatos personalizados é separar o código em secções divididas pelos ";".

Secção 1 // formatação para valores Positivos
Secção 2 // formatação para valores Negativos
Secção 3 // formatação para valores Zero
Secção 4 // formatação para Texto

Não é obrigatório definir as quatros secções para criar uma formatação personalizada.

Secção 1

Se introduzirmos apenas 1 secção, o Excel aplica esse formato para todos os valores.

Secção 1 ; Secção 2

Com 2 secções, o primeiro formato é usado para números Positivos e Zeros e o segundo formato para números Negativos:

Secção 1 ; ; Secção 3

Podemos ignorar algumas secções colocando apenas ";" sem qualquer código de formatação.

Alguns exemplos de Formatações Personalizadas

+#,##0;-#,##0;0

Colocar o sinal de menos e mais antes dos números.
ValorResultado
00
-1-1,0
1+1,0
-50,5555-50,556
50,5555+50,556

# ##,00 " M"

Simplificar números na casa dos Milhões colocando um M.
ValorResultado
-15000000-15 M
-1000000-1 M
10000 M
50000005 M
Neste tipo de formatação é preciso ter em consideração as configurações definidas de Separador decimal e Separador de milhares. No nosso exemplo, temos o Excel configurado com "," como separador decimal e " " como separador de milhares.

Opções do excel

Dependendo da configuração do Excel, estes separadores podem ser, por exemplo, ponto ("."), vírgula (",") ou espaço (" ").

O Separador Decimal usado no número formatado fará com que as casas decimais sejam sempre mostradas, seja um decimal ou um inteiro.O Separador de Milhares define a forma como mostramos os dígitos do número.

"Entrega em "@

Acrescentar um texto expecífico antes dos valores a formatar.
ValorResultado
1 diaEntrega em 1 dia
2 semanasEntrega em 2 semanas
1 mêsEntrega em 1 mês

Caracteres normais

Estes caracteres podem ser utilizados directamente no código de formatação, ou seja não é preciso qualquer tipo de encapsulamento para os diferenciar.
CaracteresDescrição
Euro
+-Mais, Menos
()Parentesis
{}Chavetas
<>Menos que, Maior que
=Igual
:Dois pontos
^Acento Circunflexo
'Apostrofe
/Barra (forward Slash)
!Ponto de Exclamação
&E Comercial (ampersand)
~Til
 Espaço

Caracteres Encapsulados

Alguns caracteres não são reconhecidos no código sem serem encapsulados. Por exempl: "*", "#" ou "%" têm que ser antecedidos pela barra "\" (Back Slash) para que o Excel os reconheça.

ValorFormataçãoResultado
100\#0#100
100\*0*100
100\%0100%

Caracteres Especiais

Outros caracteres funcionam como "código" na construção das formatações personalizadas.

CaracteresDescrição
.Separador de milhares (depende da configuração do Excel)
,Separador decimal (depende da configuração do Excel)
0Mostrar zeros insignificantes
#Mostrar digitos significantes
?Mostrar decimais alinhados
*Repetir caracteres
_Adicionar espaço
@Marcador para texto

Mostrar Zeros Insignificantes - Zero ("0")

O Zero ("0") é usado para mostrar zero insignificantes quando o número formatado tem menos dígitos que os zeros no estilo de formato.

Zeros

Mostrar Digitos Significantes - Cardinal ("#")

O número formatado irá mostrar o mesmo número de dígitos definidos com o Cardinal ("#"). Se o número tiver menos dígitos que a formatação de # será menor.

Cardinal

Mostrar Decimais Alinhados - Ponto de Interrogação ("?")

O Ponto de Interrogação ("?") é usado para alinhar dígitos acrescentando um espaço para manter o alinhamento visual.

Interrogação

Repetir Caracteres - Asterisco ("*")

O Asterisco ("*") usa-se para repetir caracteres. O caracter a seguir ao asterisco repete-se quantas vezes for necessário até ocupar o espaço vazio da célula.

Asterisco

Adicionar Espaço - Sublinhado ("_")

O Traço Sublinhado ("_") adiciona espaços à formatação do número.

Underscore

Marcador para texto - Arroba ("@")

No exemplo abaixo, se o número a formatar tiver texto ficará a vermelho.

Arroba

Formatação de Números com TEXTO

Para colocar texto em conjunto com números podemos usar Aspas (""). O texto pode ser colocado antes e/ou depois dos números.

ValorFormataçãoResultado
100Geral" Unidades"100 Unidades
10000.0" KMs"1000.0 KMs
1,50.0" Metros"1,5 Metros
150000" pessoas"15000 pessoas
99,9"Nota: "0.0Nota: 99.9
01/jan"Data: "mmmm dData: Janeiro 1

Formatação de DATA

O Excel trata as DATAS como números sendo que a grande diferença é a forma como mostramos esse número.

DescriçãoFormataçãoResultado
Exemplo de Data 1/2/2020
Ano com 2 dígitosaa20
Ano com 4 dígitosaaaa2020
Dígito do mêsm2
Dígito do mês com 0mm02
Mês abreviadommmFev
Mês por extensommmmFevereiro
Dígito do diad1
Dígito do dia com 0dd01
Dia abreviadodddSáb
Dia por extensoddddSábado

Formatação de HORA

A HORAS no Excel, tal como as DATAS, são números. Concretamente, são frações correspondentes ao dia:

6:00 AM corresponde a 0,25
12:00 PM corresponde a 0,50
18:00 PM corresponde a 0,75
00:00 AM corresponde a 1,00

A HORA também tem uma série de formatações específicas que permitem mostrar os dados de diferentes formas.
DescriçãoFormataçãoResultado
Exemplo de Hora 7:09:01
Dígito da horah7
Dígito da hora com 0hh07
Dígito do minutom9
Dígito do minuto com 0mm09
Dígito do segundos1
Dígito do segundo com 0ss01

Formatação de TEMPO DECORRIDO

O Excel também permite formatar tempo decorrido usando Parêntesis Rectos ("[]") para mostrar horas, minutos e segundos.
DescriçãoFormataçãoResultado
Exemplo de Tempo Decorrido 10 dias
Horas decorridas[h]240
Horas decorridas com minutos[h]:mm240:00
Minutos decorridos[m]14400
Minutos decorridos com segundos[m]:ss14400:00
Segundos decorridos[s]864000
Segundos decorridos com milisegundos[ss],00864000,00

Formatação de CORES

O Excel permite usar cores para formatar números de 2 formas:

1. Chamando directamente 8 cores que são Preto, Branco, Vermelho, Verde, Azul, Amarelo, Magenta e Turquesa.
2. Através de um índice de cores de 1 a 56, em que Cor1 = preto, Cor2 = Branco, Cor3 = Vermelho, etc.

Cores

Formatação CONDICIONAL

É possível definir até 2 condições, fechadas em parêntesis rectos, na formatação de números. Ao utilizar estas condições estamos a substituir a regra que já explicamos acima de [positivo];[negativo];[zero];[texto], usando a seguinte formatação:

[verde][>=50]0;[vermelho][<50]0

[verde][>=50]0 // números maiores ou iguais a 50 ficarão formatados a verde
[vermelho][<50]0 // números menores que 50 ficarão formatados a vermelho

Condicional

Nota: Para aplicar mais que 2 condições e/ou mudar atributos de células (limites, cor de fundo, etc.) teremos que usar a "Formatação Condicional" que oferece um conjunto de opções muito superior e mais flexível que a utilização de fórmulas.

Esconder conteúdo com a Formatação

É também possível formatar a célula para esconder todo o conteúdo utilizando uma formatação muito simples de 3 pontos e vírgula (";"):

;;;

Para reverter, basta alterar a célula para a Formatação Geral. A forma mais rápida é usando o atalho Control + Shift + ~ que aplica formatação geral.

Arredondamentos

Todas as formatações que abordámos no artigo são mudanças visuais. Não há modificação de valores. Se um determinado número é maior que a formatação que pretendemos - por exemplo: 1,3678901 = #,# = 1,37 - o arredondamento é estético. Para o Excel o valor na célula continua a ser 1,3678901, apesar de visualmente mostrar 1,37.

Para arredondamentos reais devemos recorrer às Funções ARRED (Round), ARRED.PARA.CIMA (Roundup) ou ARRED.PARA.BAIXO (Rounddown).

Descargas

Descarrega aqui o ficheiro com os exemplos para usar Formatos Personalizados
Artigo anterior Artigo seguinte
Sem comentários
Comentar
url comentário

Pesquisa Google