Como usar a formatação condicional com base em outra célula no Google Sheets

Às vezes você precisa examinar montanhas de dados em uma planilha, muitas vezes procurando valores específicos. Mas, sem elementos de design ou pistas visuais, vasculhar os dados leva tempo e cansa a vista. Felizmente, no Planilhas Google , você pode usar a formatação condicional para mudar a cor das células que procura com base no valor da célula. Esse recurso se chama formatação condicional. Ela pode se basear na própria célula ou em outra célula. Vou mostrar como funciona com a ajuda de alguns exemplos.

Tipos de formatação condicional

Exemplo 1: identificar as células abaixo de um limite

Vou começar com um exemplo básico que usa a formatação condicional para mudar a cor de uma célula. Veja a planilha a seguir. O supervisor do almoxarifado de uma oficina mecânica local a usa para controlar o estoque de peças de reposição.

Ele precisa fazer um novo pedido de um produto se a quantidade em estoque (coluna B) ficar abaixo do limite de 20. Há muitos produtos, e os valores da coluna B mudam com o tempo. Por isso, fica difícil identificar os produtos que estão acabando. Mesmo que ele colorisse as células manualmente para encontrá-las depois com facilidade, o trabalho dobraria, e essa não é a solução ideal!

O recurso de formatação condicional resolve isso: com ele, você muda a cor das células com base no valor da célula no Google Sheets. Para aplicar essa formatação, primeiro selecione todas as células da coluna B.

Agora acesse Formatar > Formatação condicional. Uma barra lateral se abre no lado direito da tela. Você vai notar que no campo Aplicar ao intervalo , o valor já está definido como B2:B27. Isso acontece porque esse intervalo já está selecionado. De qualquer forma, se quiser mudar o intervalo, você pode.

Vou escolher Menor ou igual a no Formatar células se (menu suspenso). Em seguida, digite o valor 20 no campo logo ao lado. Basicamente, você está configurando o Google Sheets para formatar células de uma determinada maneira quando elas atendem a essa condição. Vou escolher um estilo de formatação vermelho, que combina com a situação. Para terminar, basta clicar em Feito botão

Você configura isso uma única vez, e a formatação se atualiza automaticamente mesmo que os dados mudem no futuro.

Usei uma condição direta neste exemplo. Você também pode mudar as cores das células usando uma fórmula. Para isso, basta garantir que o valor do menu suspenso Formatar células se seja A fórmula personalizada é, e digitar a fórmula no campo logo ao lado.

Exemplo 2: formatação condicional com base em outra célula

Agora que você já sabe usar a formatação condicional, vamos ver como mudar a cor de uma célula com base no valor de outra célula. Vou usar o mesmo exemplo de antes. Desta vez, porém, o supervisor do almoxarifado quer destacar o ID do produto em vez da quantidade.

Para isso, siga os mesmos passos de antes, mas selecione A fórmula personalizada é em Formatar células se (menu suspenso). Em seguida, digite a fórmula com base na célula da coluna B. Neste exemplo, digitei =B2<=20.

Agora consegui o mesmo destaque para as células com estoque baixo, mas é o ID do produto que fica destacado em vez da quantidade!

Exemplo 3: formatação com várias regras

Você não precisa se limitar a uma única regra ao usar a formatação condicional. Adicionar várias regras de formatação condicional ajuda a separar visualmente seus dados ainda mais.

Neste exemplo, uma professora do quinto ano quer destacar os alunos com notas abaixo ou acima de um limite. Mais especificamente, ela quer os alunos com média abaixo de 70 em vermelho e os alunos na faixa dos 90 em verde.

Para isso, você precisa adicionar duas regras separadas. O processo de cada regra é muito parecido com o do exemplo anterior.

Como você quer destacar os nomes com base nas médias, selecione os nomes como Aplicar ao intervalo, e adicione as notas digitando a regra na opção A fórmula personalizada é em Formatar células se (menu suspenso)

Depois de escrever a primeira regra, clique em Feito e depois Adicionar outra regra. As fórmulas que usei nas regras aqui são =F2<70 and ="F2">=90.

Exemplo 4: formatação com base em várias células

Você também pode usar a formatação condicional com base em várias células, tudo em uma única regra de formatação. Para isso, use as funções OR (OU) ou AND (E).

Neste exemplo, a professora quer considerar cada matéria individualmente em vez da média. Se um aluno tiver nota abaixo de 60 em qualquer matéria, o nome dele deve ficar destacado em vermelho para que a professora veja rapidamente quem está com dificuldades.

O método aqui é parecido com o dos outros exemplos, e a essa altura você já deve estar se acostumando! A diferença é que, na opção A fórmula personalizada é em Formatar células se (menu suspenso), digite =OR(B2<60,C2<60,D2<60,E2<60).

Mudar a cor de uma célula com base no valor de outra célula

Para mudar a cor de uma célula com base no valor de outra célula, use uma fórmula personalizada que aponte para essa outra célula. Isso ajuda quando você lida com dados muito dependentes entre si ou com muitos dados dinâmicos. Veja um segundo exemplo, com uma lista de vendedores. Digamos que eu queira destacar em vermelho os nomes da equipe de vendas que têm menos de 10 clientes. Neste exemplo, a formatação das células da equipe de vendas depende do número de clientes.

1. Selecione o intervalo de células que você quer formatar (aqui, os nomes em A2:B45) e acesse Formatar > Formatação condicional.

2. Em "Regras de formatação", selecione "A fórmula personalizada é" no menu suspenso. Adicione a fórmula que cria a condição certa e lembre-se de fazer referência à outra célula. Neste exemplo, minha fórmula será =C2<10, escrita para a primeira linha do intervalo. Como você pode ver, a fórmula faz referência à coluna "Number of clients".

3. Altere as preferências de "Estilo de formatação" e clique em "Concluído".

4. Como você pode ver, os nomes da minha equipe de vendas com menos de 10 clientes agora estão destacados em vermelho.

Destacar uma linha inteira com base em uma célula

Para colorir a linha inteira em vez de só o nome, selecione todas as colunas da tabela (por exemplo, A2:D45) em Aplicar ao intervalo e adicione um cifrão antes da letra da coluna: =$C2<10. O $ faz todas as células da linha olharem para a coluna C, então cada linha fica vermelha quando o seu próprio número de clientes é menor que 10.

Como usar uma fórmula personalizada na formatação condicional

Usar uma fórmula personalizada na formatação condicional é muito útil. Ela permite criar regras mais personalizadas, que dão exatamente o resultado que você quer, e também serve para outros objetivos importantes, como você verá mais adiante neste post. Você pode usar funções diferentes, como a fórmula IF (SE) na formatação condicional, para resolver objetivos mais complexos. Primeiro, vamos ver como usar a fórmula personalizada para criar regras de formatação.

Neste exemplo, quero destacar em laranja todas as vendas totais entre US$ 4.000 e US$ 8.000. Uma fórmula AND (E) simples resolve isso rapidamente.

1. Selecione o intervalo de dados que você quer formatar e acesse Formatar > Formatação condicional.

2. No menu suspenso de "Regras de formatação", selecione "A fórmula personalizada é". No campo abaixo, digite a fórmula de que você precisa para atingir seu objetivo. Neste caso, para destacar as vendas totais entre US$ 4.000 e US$ 8.000, preciso adicionar a seguinte fórmula AND: =AND(D1>4000,D1<8000).

3. Em "Estilo de formatação", defina a formatação das células que atendem à sua regra. Neste exemplo, vou usar laranja. Quando terminar, clique em "Concluído".

Como você pode ver abaixo, todas as vendas totais entre US$ 4.000 e US$ 8.000 estão com o fundo da célula em laranja.

Referências relativas e absolutas em uma fórmula personalizada

O Google Sheets lê uma fórmula personalizada como se ela fosse escrita para a primeira célula de Aplicar ao intervalo, e depois a ajusta para cada uma das outras células, do mesmo jeito que uma fórmula muda quando você a copia para baixo em uma coluna. É por isso que os exemplos desta página usam =B2 ou =C2, e não uma coluna inteira.

O cifrão trava a parte de uma referência que não deve mudar:

• =$C2 fixa a coluna e deixa a linha mudar. Use para colorir linhas inteiras.
• =C$2 fixa a linha e deixa a coluna mudar.
• =$C$2 faz todas as células apontarem para a mesma célula, como um valor de meta que você digita uma única vez.

Você também não precisa de uma função IF dentro de uma fórmula personalizada. A regra se aplica sempre que a fórmula retorna TRUE, então =D2>5000 faz o mesmo que =IF(D2>5000,TRUE,FALSE). Nosso guia de funções IF no Google Sheets mostra os casos em que IF é útil. As fórmulas personalizadas também podem sinalizar entradas repetidas com COUNTIF, como mostramos em como destacar duplicatas no Planilhas Google.

Como usar a formatação condicional com várias condições

Às vezes você vai querer aplicar mais de uma condição aos seus dados. A formatação condicional do Google Sheets permite criar e aplicar várias condições aos seus dados com muita facilidade. Neste exemplo, digamos que eu queira destacar duas coisas: o vendedor com o maior total de vendas e o com o menor. Assim você compara duas colunas de dados ao mesmo tempo. Para isso, também precisamos usar uma fórmula personalizada em cada regra. Primeiro, criamos a condição do total de vendas máximo. Depois, criamos outra condição para o total de vendas mínimo.

1. Selecione o intervalo de células que você quer formatar e acesse Formatar > Formatação condicional.

2. Crie sua primeira regra com a fórmula personalizada. Neste exemplo, vamos configurar a condição do total de vendas máximo com a fórmula personalizada =D:D=max(D:D) e mudar a cor para verde. Clique em "Concluído".

3. Agora clique em "+ Adicionar outra regra".

4. Crie sua segunda regra com a fórmula personalizada. Neste exemplo, vamos configurar a condição do total de vendas mínimo com a fórmula personalizada =D:D=min(D:D) e mudar a cor para vermelho. Clique em "Concluído".

5. Como você pode ver, depois de aplicar várias condições, agora sei que Melanie ficou em primeiro lugar no total de vendas, e Ameline, em último.

Qual regra vale quando as regras se sobrepõem

O Google Sheets verifica as regras de um intervalo de cima para baixo, e a primeira regra verdadeira decide a aparência da célula. Se uma célula atender a duas regras, ela recebe só a formatação da regra mais acima. Para mudar a ordem, passe o cursor sobre uma regra na barra lateral Regras de formatação condicional e arraste-a para cima ou para baixo pela alça à esquerda dela.

Se você precisar das duas condições em uma única regra, combine-as com AND ou OR, como no exemplo 4 acima.

Formatação condicional com base em outra página

Uma fórmula personalizada não pode apontar diretamente para uma célula de outra aba, então uma fórmula como =D2<Targets!B1 não vai funcionar. Em vez disso, coloque a referência dentro de INDIRECT:

=D2<INDIRECT("Targets!B1")

Isso colore cada valor de vendas totais abaixo da meta guardada na célula B1 da aba Targets. Para comparar cada linha com a linha correspondente da outra aba, monte o endereço com ROW: =D2<INDIRECT("Targets!B"&ROW()). Saiba mais sobre a função no nosso guia da fórmula INDIRECT.

As fórmulas personalizadas também não conseguem buscar valores de outro arquivo. Se os valores que você usa na comparação estão em outra planilha, traga-os primeiro para uma aba. O Sheetgo, uma plataforma de automação de dados com IA, pode manter essa aba sincronizada com o arquivo de origem de forma programada.

Regras integradas e escalas de cores no Google Sheets

Há muitas formas de usar a formatação condicional no Google Sheets, dependendo do tipo de dados ou do que você quer fazer.

Os exemplos desta seção usam uma lista de vendedores com o número de clientes e o total de vendas de cada um. Com a formatação condicional, posso destacar valores importantes, como as vendas ou o número de clientes mais altos ou mais baixos, destacar as vendas totais dentro de uma faixa ou até encontrar conteúdo duplicado. Primeiro, vamos ver passo a passo a forma mais básica de usar a formatação condicional no Google Sheets.

A formatação condicional do Google Sheets tem duas opções: "Cor única" e "Escala de cores". Vamos ver como usar as duas.

Regras de cor única

Neste exemplo, quero destacar em vermelho todas as vendas totais abaixo de US$ 5.000.

1. Abra o arquivo do Google Sheets com seus dados. Selecione as células em que você quer aplicar a regra de formatação condicional (por exemplo, 'D1:D45') e acesse Formatar > Formatação condicional.

2. O menu aparece no lado direito da tela. Confira se você está na guia "Cor única". Como você pode ver, o intervalo de células já está definido.

3. Defina a regra de formatação. No menu suspenso de "Formatar células se", escolha a condição que deve acionar a regra. Por exemplo, se eu quiser destacar todas as vendas totais abaixo de US$ 5.000, seleciono a opção "Menor que". No campo "Valor ou fórmula" abaixo, digito '5000'.

4. Em "Estilo de formatação", defina a formatação das células que atendem à sua regra. Neste exemplo, vamos deixar a cor de fundo da célula em vermelho. Quando terminar, clique em "Concluído".

5. As regras de formatação condicional são aplicadas aos seus dados. Como você pode ver, todas as vendas totais abaixo de US$ 5.000 agora estão destacadas em vermelho.

Com os mesmos passos, você também cria condições para valores maiores. Basta mudar o menu suspenso "Formatar células se" para "Maior que" e adicionar o seu valor.

O texto contém e outras condições integradas

As regras de cor única não se limitam a números. O menu suspenso Formatar células se também verifica textos e datas:

• Está vazio e Não está vazio
• O texto contém, O texto não contém, O texto começa com, O texto termina com e O texto é exatamente
• A data é, A data é anterior a e A data é posterior a
• Maior que, Menor que, É igual a, Está entre e suas variantes
• A fórmula personalizada é, para tudo o que a lista não cobre

Por exemplo, para sinalizar todos os pedidos atrasados, selecione a coluna de status, escolha O texto contém, digite Late e escolha uma cor. Qualquer célula que inclua essa palavra muda de cor, mesmo que tenha outro texto junto.

Escalas de cores

Agora digamos que eu queira aplicar uma escala de cores à coluna 'Total sales'. Com essa opção, o fundo de cada célula ganha uma cor ou um tom diferente conforme o valor. Neste exemplo, quero as vendas totais mais baixas em vermelho e as mais altas em verde.

1. Assim como na seção anterior, selecione o intervalo de células e abra a formatação condicional. Desta vez, confira se a guia "Escala de cores" está selecionada.

2. Na seção "Visualização", clique na escala de cores e escolha a paleta que preferir. Para o meu objetivo, vou escolher a escala de vermelho para verde.

3. Em seguida, escolha os valores e as cores de Ponto mínimo, Ponto médio e Ponto máximo. No menu suspenso de cada seção, escolha o valor em que as cores vão se basear. Para mudar as cores, clique nos ícones de lata de tinta e selecione a cor que preferir. Quando terminar, clique em "Concluído".

4. As regras de formatação condicional são aplicadas aos seus dados. Como você pode ver, os valores de vendas totais mais baixos estão em vermelho e os mais altos, em verde.

Como editar, copiar ou remover regras de formatação condicional

Para mudar uma regra, selecione qualquer célula do intervalo dela, acesse Formatar > Formatação condicional, e clique na regra na barra lateral. Edite o intervalo, a condição ou o estilo e depois clique em Feito.

Para copiar regras para outro intervalo, copie uma célula formatada, selecione o novo intervalo e use Editar > Colar especial > Somente formatação condicional. As referências relativas de uma fórmula personalizada se ajustam ao novo local, enquanto as referências com $ ficam fixas.

Para remover uma regra, passe o cursor sobre ela na barra lateral e clique no ícone de lixeira.

Parabéns! Você usou a formatação condicional para criar pistas visuais que deixam a identificação dos dados muito mais fácil!

Saiba mais sobre cores alternadas no seguinte post do blog: Como criar uma lista com cores alternadas no Google Sheets?

Trabalha no Excel? As fórmulas personalizadas funcionam do mesmo jeito lá. Este guia de formatação condicional no Excel explica como.