A formatação de números personalizados do Excel é a roupa dos dados em células do Excel. Você pode vesti-los do jeito que quiser. Tudo que você precisa é um pouco de conhecimento de como funciona o formato de número personalizado do Excel.
Com a formatação de número personalizada no Excel, você pode alterar a forma como os valores nas células são exibidos, mas ao mesmo tempo mantendo o valor original intacto.
Por exemplo, posso fazer um valor de data aparecer em diferentes formatos de data, enquanto o valor subjacente (o número que representa a data) permaneceria o mesmo.
Neste tutorial, vou cobrir tudo sobre o formato de número personalizado no Excel e também mostrar alguns exemplos realmente úteis que você pode usar no seu trabalho do dia-a-dia.
Então vamos começar!
Construção de formato de número personalizado do Excel
Antes de mostrar a você como isso é incrível, deixe-me explicar brevemente como a formatação de números personalizada funciona no Excel.
Por design, o Excel pode aceitar os seguintes quatro tipos de dados em uma célula:
- Um número positivo
- um número negativo
- 0
- Strings de texto (isso pode incluir strings de texto puro, bem como strings alfanuméricas)
Para qualquer célula em uma planilha do Excel, você pode especificar como cada um desses tipos de dados deve ser mostrado na célula.
E abaixo está a sintaxe na qual você pode especificar este formato.
;;;
Observe que todos eles são separados por ponto e vírgula (;).
Qualquer coisa que você inserir em uma célula no Excel se enquadrará em qualquer uma dessas quatro categorias e, portanto, um formato personalizado pode ser especificado.
Se você mencionar apenas:
- Um formato: É aplicado a todas as quatro seções. Por exemplo, se você escrever apenas Geral, ele será aplicado a todas as quatro seções.
- Dois formatos: O primeiro é aplicado a números positivos e zeros, e o segundo é aplicado a números negativos. O formato de texto por padrão torna-se Geral.
- Três Formatos: O primeiro é aplicado a números positivos, o segundo é aplicado a números negativos, o terceiro é aplicado a zero e o texto desaparece porque nada foi especificado para o texto.
Se você quiser saber mais sobre a formatação de número personalizada, recomendo enfaticamente a seção Ajuda do Office.
Como isso funciona e onde inserir esses formatos são abordados posteriormente neste tutorial. Por enquanto, lembre-se de que esta é a sintaxe do formato de qualquer célulaComo o formato de número personalizado funciona no Excel
Em seguida, você abre uma nova pasta de trabalho do Excel; por padrão, todas as células em todas as planilhas dessa pasta de trabalho têm o formato Geral, onde:
- Números positivos são mostrados como estão (alinhados à esquerda)
- Números negativos são mostrados com um sinal de menos (e alinhados à esquerda)
- Zero é mostrado como 0 (e alinhado à esquerda)
- Texto é mostrado como está (alinhado à direita)
E, caso você insira uma data, ela é exibida com base em sua configuração regional (no formato DD-MM-AAAA ou MM-DD-AAAA).
Portanto, por padrão, a formatação do número da célula é:
Geral; Geral; Geral; Geral
Onde cada tipo de dados é formatado como Geral (e siga as regras que mencionei acima, com base no fato de ser um número positivo, negativo, 0 ou texto)
Agora a configuração padrão está certa, mas como você altera o formato. Por exemplo, se eu quiser que meus números negativos apareçam em vermelho ou que minhas datas apareçam em um formato específico, como faço isso.
Usando a lista suspensa de formatos na faixa de opções
Você pode encontrar alguns dos formatos de número comumente usados na guia Página Inicial do grupo Número.
É um menu suspenso que mostra muitos dos formatos comumente usados que você pode aplicar a uma célula simplesmente selecionando a célula e, em seguida, selecionando o formato nesta lista.
Por exemplo, se você deseja converter números em porcentagens ou datas convertidas em formato de data abreviada ou formato de data longo, você pode fazer isso facilmente usando as opções no menu suspenso.
Usando a caixa de diálogo Formatar células
Caixas de diálogo de formatação de células, onde você obtém o máximo de flexibilidade quando se trata de aplicar formatos a uma célula no Excel.
Ele já tem vários formatos predefinidos que você pode usar ou, se quiser criar seu próprio formato de número personalizado, também pode fazer isso.
Veja como abrir a caixa de diálogo Formatar células;
- Clique na guia Home
- No grupo Número, clique no ícone do iniciador da caixa de diálogo (a pequena seta inclinada na parte inferior direita do grupo).
Isso abrirá a caixa de diálogo Formatar células. Nele, você encontrará todos os formatos na guia Número (junto com a opção de criar formatos de células personalizados)
Você também pode usar o seguinte atalho de teclado para abrir a caixa de diálogo de formatação de células:
CONTROL + 1 (todas as teclas Control e, em seguida, pressione a tecla 1).
Caso você esteja usando Mac, você pode usar CMD + 1
Agora vamos dar alguns exemplos práticos em que criaremos nossos próprios formatos de número personalizados.
Exemplos de uso da formatação de números personalizados no Excel
Agora vamos dar alguns exemplos práticos em que criaremos nossos próprios formatos de número personalizados.
Ocultar um valor de célula (ou ocultar todos os valores em um intervalo de células)
Às vezes, pode ser necessário ocultar o conteúdo da célula (sem ocultar a linha ou coluna que contém a célula).
Um caso prático disso é quando você está criando um painel do Excel e há alguns valores que você usa no painel, mas você não deseja que os usuários os vejam. então você pode simplesmente ocultar esses valores
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja ocultar todos os valores numéricos:
Aqui está como fazer isso usando formatação personalizada no Excel:
- Selecione a célula ou o intervalo de células para o qual deseja ocultar o conteúdo da célula
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
;;;
Como é que isso funciona?
O formato para quatro tipos diferentes de tipos de dados é dividido por ponto e vírgula no seguinte formato:
;;;
Nesta técnica, removemos o formato e especificamos apenas o ponto-e-vírgula, o que indica que não queremos qualquer formatação para as células. Esta é uma indicação para o Excel ocultar qualquer conteúdo que esteja na célula.
Observe que, embora isso oculte o conteúdo da célula, ainda está lá. portanto, se você tiver números que ocultou, ainda poderá usá-los em cálculos (o que torna esse um ótimo truque para painéis e relatórios do Excel).
Exibir números negativos em vermelho
Por padrão, os números negativos aparecem com um sinal de menos (ou entre parênteses em algumas configurações regionais).
Você pode usar a formatação de número personalizada para alterar a cor dos números negativos e fazê-los aparecer na cor vermelha.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja destacar todos os valores negativos na cor vermelha.
Abaixo estão as etapas para fazer isso:
- Selecione o intervalo de células onde deseja mostrar os números negativos em vermelho
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
Geral; [Vermelho] -Geral
O formato acima fará com que suas células com valores negativos sejam exibidas em vermelho.
Como é que isso funciona?
Quando você especifica apenas dois formatos como o formato de número personalizado, o primeiro é considerado para números positivos e 0, e o segundo é considerado para números negativos (o próximo valor por padrão torna-se o formato geral).
No formato acima que usamos, especificamos a cor que queremos que o formato assuma nos colchetes.
Portanto, embora os números negativos tenham o formato geral, eles são mostrados em vermelho com um sinal de menos.
Caso queira que os números negativos parem na cor vermelha entre parênteses (em vez do sinal de menos), você pode usar o formato abaixo:
Geral; [Vermelho] (Geral)
Adicione texto aos números (como milhões / bilhões)
Uma coisa incrível sobre a formatação de número personalizada é que você pode adicionar um prefixo ou um texto de sufixo a um número, mantendo o número original na célula.
Por exemplo, posso ter ‘100’ mostrado como ‘$ 100 milhões’, embora ainda tenha o valor original na célula para que possa usá-lo nos cálculos.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja mostrar esses números com um cifrão e a palavra milhão na frente dele.
Abaixo estão as etapas para fazer isso:
- Selecione o conjunto de dados onde deseja adicionar o texto aos números
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
$ General "Milhão"
Observe que esse formato de número personalizado afetará apenas os números. Se você inserir texto na mesma célula, ele aparecerá como está.
Como é que isso funciona?
Neste exemplo, ao formato geral, adicionei a palavra milhão entre aspas duplas. tudo o que eu adicionar entre aspas duplas ao formato é mostrado como está na célula.
Também adicionei o cifrão antes do formato geral para que todos os meus números também mostrem o cifrão antes do número.
Observação: se você está se perguntando por que o cifrão não está entre aspas, enquanto a palavra Million está entre aspas duplas, é porque o Excel reconhece o dólar como um símbolo que é frequentemente usado na formatação de números personalizada e não exige que você para adicionar aspas duplas a ele. Você pode ir em frente e adicionar aspas duplas ao cifrão, mas quando você fechar a caixa de diálogo de formatação de células e abri-la novamente, você notará que o Excel a removeu
Disfarce números e texto
Com a formatação de número personalizada, você pode avaliar no máximo duas condições e, com base nessas condições, pode aplicar um formato à célula.
Por exemplo, suponha que você tenha uma lista de pontuações para alguns alunos, deseja mostrar o texto Aprovado ou Reprovado com base na pontuação (onde qualquer pontuação inferior a 35 deve mostrar 'Reprovado', caso contrário, deve mostrar 'Aprovado'.
Abaixo estão as etapas para mostrar as pontuações como Aprovado ou Reprovado em vez do valor usando o formato de número personalizado:
- Selecione o conjunto de dados onde você tem as pontuações
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
[<35] "Reprovado"; "Aprovado"
Abaixo está como seus dados ficariam depois de aplicar o formato acima.
Observe que isso não altera o valor nas células. A célula continua a ter as pontuações em valores numéricos enquanto exibe 'Aprovado' ou 'Reprovado', dependendo do valor da célula.
Como é que isso funciona?
Neste exemplo, a condição precisa ser especificada entre colchetes. a formatação de número personalizada avalia o valor na célula e aplica o formato de acordo.
Portanto, se o valor na célula for menor que 35, o número ficará oculto e, em vez disso, o texto 'Reprovado' será mostrado, adicionando todos os outros casos, 'Aprovado' será mostrado.
Da mesma forma, se você deseja converter um intervalo de células que têm 0s e 1s em VERDADEIROS e FALSOS, você pode usar o formato abaixo:
[= 0] "FALSO"; [= 1] "VERDADEIRO"
Lembre-se de que você só pode usar duas condições na formatação de número personalizada. Se você tentar e usar mais de duas condições, seu prompt informará que ele não pode aceitar esse formato.
Ocultar texto, mas exibir números
Às vezes, quando você baixa seus dados de bancos de dados, uma vez que estão vazios, são preenchidos com valores como Not Available ou NA ou -
Com a formatação de número personalizada, você pode ocultar todos os valores de texto enquanto mantém os números como estão. isso também garante que seus dados originais estejam intactos (já que você não está excluindo o valor do texto, você apenas o está ocultando)
Suponha que você tenha um conjunto de dados conforme mostrado abaixo, onde deseja ocultar todos os valores de texto:
Abaixo estão as etapas para fazer isso:
- Selecione o conjunto de dados onde você tem os dados
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
Em geral; -Em geral; Em geral;
Observe que usei o formato geral para números. Você pode usar qualquer formato que desejar (como 0, 0. #, # 0.0%). Além disso, observe que há um sinal negativo (-) antes do segundo formato geral, pois representa o formato para números negativos.
Como é que isso funciona?
Neste exemplo, como queríamos ocultar todos os valores de texto, simplesmente não especificamos o formato para ele.
E uma vez que especificamos o formato para os outros três tipos de dados (número positivo, número negativo e zero), o Excel entendeu que deixamos propositalmente o formato do valor do texto em branco e não deve mostrar nada se uma célula tiver um texto corda.
Exibir números como porcentagens (%)
Com a formatação de número personalizada, você também pode alterar os números e fazê-los aparecer em porcentagens.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja converter esses números para mostrar o valor percentual equivalente:
Abaixo estão as etapas para fazer isso:
- Selecione as células onde você tem os números (que deseja converter em porcentagem)
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
0%
Isso mudará os números para suas porcentagens. Por exemplo, ele muda de 0,11 para 11%.
Se tudo o que você deseja fazer é converter um número em seu valor de porcentagem equivalente, a melhor maneira de fazer isso seria usar a opção de porcentagem na caixa de diálogo de formatação de células.
A formatação personalizada pode ser útil quando você deseja mais controle sobre como deseja mostrar o valor percentual.
Por exemplo, se você quiser mostrar algum texto adicional com a porcentagem, tudo que você deseja mostrar as porcentagens negativas na cor vermelha, então é melhor usar a formatação personalizada
Exibir números em uma unidade diferente (milhões como bilhões ou gramas como quilogramas)
Se você trabalha com números grandes, pode querer mostrá-los em uma unidade diferente. por exemplo, se você tem os dados de vendas de algumas empresas, pode querer mostrar as vendas em milhões ou bilhões (enquanto ainda mantém o valor de venda original nas células)
Felizmente, você pode fazer isso facilmente com a formatação de números personalizada.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja mostrar esses valores de vendas em milhões.
Abaixo estão as etapas para fazer isso:
- Selecione as células onde você tem os números
- Abra a caixa de diálogo Formatar células (atalho de teclado Control + 1)
- Na guia Número, clique no botão Personalizado
- Insira o seguinte formato no campo Tipo:
0.0,,
O acima forneceria o resultado mostrado abaixo (valores em milhões em um dígito após o decimal):
Como é que isso funciona?
No formato acima, um zero antes do decimal indica que o Excel deve mostrar o número inteiro (exatamente como está na célula) e um 0 após o decimal indica que ele deve mostrar apenas um dígito significativo após o ponto decimal.
Portanto, 0,0 seria sempre um número com um dígito após a vírgula decimal.
Quando você adiciona uma vírgula após o 0,0, ele remove os últimos três dígitos do número. Por exemplo, 123456 se tornaria 123,4 (os últimos três dígitos são removidos e, como deve mostrar 1 dígito após a vírgula decimal, mostra 4)
Se você pensar sobre isso, é semelhante a dividir o número por 1000. Então, se você quiser mostrar um número na unidade de 'milhões', você precisa adicionar 2 vírgulas, para que ele raspe os últimos 6 dígitos.
E se você também quiser mostrar a palavra milhão ou o alfabeto M após o número, use o formato abaixo
0,0 ,, "M"
