Formatar números em milhares e milhões em relatórios do Excel

Use a função TEXT para formatar números como milhares, milhões ou bilhões

A função TEXTO do Excel é extremamente útil para formatar números e datas em formatos específicos.

Nesse caso, você pode usá-lo para formatar seus números como milhares, milhões ou bilhões. O resultado será convertido em um valor de texto, portanto, esta não é uma opção que você deseja usar se precisar realizar mais cálculos com os números.

Sintaxe para a função TEXT

TEXTO (valor, formato)

• valor é o valor numérico que você deseja formatar.
• formato é uma string de texto que representa a regra de formatação da saída.

A função TEXT recebe um número e retorna um valor de texto que foi formatado.

Exemplo com a função TEXT

Nesta postagem, a função TEXTO será usada para adicionar separadores de vírgula, ocultar quaisquer milhares de dígitos e adicionar um  k até o final para indicar que o resultado está em milhares.

Neste exemplo, o número grande está na célula B3, e a fórmula TEXTO é usada na célula C3.

= TEXTO (B3, "#,##0, " ) & "k"

A fórmula acima mostrará o número da célula B3 como um valor de texto em milhares junto com um k para indicar que o número está em milhares.

Observe que os resultados estão alinhados à esquerda? Isso indica que os resultados são valores de texto e não números.

O símbolo hash (#) é usado para fornecer espaços reservados para os dígitos dos números na string de formatação. O símbolo de vírgula (,) é usado para fornecer um símbolo divisor entre os grupos de 3 dígitos.

O zero é incluído para que, caso o número seja menor que 1000, será mostrado um 0 em vez de nada. Se fosse apenas um espaço reservado (#), a célula não mostraria nada em vez de 0 quando o número em B3 fosse menor que 1000.

A última vírgula (,) é necessária para indicar que os números abaixo de 1000 permanecerão ocultos, pois não há espaços reservados após esta vírgula.< /span>

Observe que mesmo quando o número na célula B3 for superior a um milhão, os espaços reservados e vírgulas só precisam ser fornecidos para um grupo de 3 dígitos.

O número na célula C3 é muito mais fácil de ler do que o número bruto na célula B3.

= TEXTO (B3, "#,##0,, " ) & "M"

Use a fórmula acima se quiser exibir os números em milhões.

= TEXTO (B3, "#,##0,,, " ) & "B"

Use a fórmula acima se quiser exibir os números em bilhões. Uma vírgula extra é tudo o que é necessário para ocultar cada conjunto de 3 dígitos nos resultados e quaisquer caracteres podem ser adicionados ao final, conforme necessário.

Use formatação condicional para formatar números como milhares, milhões ou bilhões

Também pode formatar números de acordo com seu valor. Isso pode ser feito criando uma regra de formatação condicional para uma célula ou intervalo de células, de modo que aconteça o seguinte.

• Mostra o número normalmente se for menor que 1k
• Mostra o número em milhares quando entre 1k e 1 milhão
• Mostra o número em milhões quando mais de 1 milhão

Siga estas etapas para criar esse formato condicional.

Selecione a célula ou intervalo de células que deseja formatar.

Acesse a guia Página inicial na faixa de opções.

Clique no comando Formatação condicional encontrado em Estilos grupo.

Selecione Nova regra nas opções.

Selecione a opção Formatar apenas células que contenham encontrada na lista Selecionar um tipo de regra.

Nas configurações Formatar somente células com , selecione Valor da célula e maior ou igual a nos menus suspensos e insira 1000 na caixa de entrada.

Clique no botão Formatarpara escolher qual formatação será exibida quando as condições forem atendidas. Isso abrirá uma versão mais leve do Formato Menu de células com apenas um Número, Fonte, Borda e Preenchimento guia.

Acesse a guia Número .

Selecione Personalizado nas opções de Categoria disponíveis.: #,##0, "k"

Insira a string acima na seção Tipo . Se você já usou essa formatação personalizada em outro lugar, também poderá selecioná-la nas opções predefinidas abaixo do campo Tipo .

Pressione o botão Ok .Isso fechará o  O menu Formatar células e o menu Nova regra de formatação voltarão ao foco. Agora você deverá ver uma prévia da formatação que será aplicada quando a fórmula for verdadeira.

Pressione o botão OK para fechar a Nova regra de formatação menu e salve sua nova regra de formatação condicional.

Agora você verá que todos os números em sua seleção parecem normais quando são menores que 1.000 e aparecem em milhares quando estão acima de 1.000.

Agora você repita as etapas acima para adicionar outra regra de formatação condicional para cuidar de números maiores que 1.000.000.

O processo é exatamente o mesmo de antes, com pequenas alterações na Formatar apenas células com entrada e na Formato personalizado usado.

Na etapa 6 insira o valor 1000000 no campo caixa de entrada.

#,##0,, "M"

Na etapa 10 use o formato personalizado acima.

Agora você verá seus números aparecerem com uma mistura de formatação dependendo do valor. Os números abaixo de 1k parecem normais, enquanto os números acima de 1k, mas abaixo de 1 milhão, são mostrados em milhares, e os números acima de 1 milhão são mostrados em milhões.

Para números menores que 1k, o formato geral padrão é adequado, pois não há necessidade de separadores de vírgula.

Se você espera números negativos, precisará repetir o processo para adicionar regras para números menores ou iguais a -1000 e menores ou iguais a -1000000.