Neste artigo você aprenderá como colorir a linha inteira de acordo com critérios no Excel.
Imagine que precisa que seja coloridas as linhas que estão com o vencimento em atraso em vermelho, nesta situação você precisa identificar se a data da coluna vencimento é menor do que o dia atual.
Veja passo-a-passo como colorir linhas com base em condições no Excel de forma automática.
- Defina o critério, por exemplo, vencimento menor que hoje e data de pagamento não preenchida.
- Selecione as células
- Clique em Página Inicial->Formatação condicional
- Clique em Nova regra de formatação
- Selecione Usar uma fórmula para determinar quais células devem ser formatadas
- Coloque a fórmula, lembrando de deixar $B1 por exemplo, não travando a célula, mas travando a coluna
- Pronto a formatação já irá funcionar
Veja agora detalhadamente este processo e como você pode fazer para colorir linhas no Excel de diversas formas, atendendo a diferentes critérios.
1. Introdução Colorir Linha Inteira Excel
Muitas vezes pode ser útil colorir uma linha inteira nos dados para evidenciar algo que desejamos. Um exemplo seria destacar todas as linhas com faturamento superior a R$ 100 mil ou ainda enfatizar todos os registros que sejam de vendas de um determinado produto.
É comum a formatação condicional de células individuais, mas neste artigo objetivamos mostrar como proceder para formatar condicionalmente a linha inteira de acordo com um ou mais critérios.
2. Cuidados ao aplicar a formatação condicional
O modo como selecionamos os dados pode fazer diferença na aplicação da formatação condicional. Recomenda-se sempre selecionar de cima para baixo e da esquerda para a direita. Fazer uma seleção de baixo para cima, antes de aplicar a formatação condicional, poderá “bagunçar” as referências de células, gerando resultados indesejados ou até memos não gerando resultados.
Outro ponto a destacar é que, em caso de volume considerável de registros, aplicar regras usando fórmulas poderá em algum momento trazer problemas de performance na pasta de trabalho.
3. Caminho para aplicar as regras de formatação
Em todos os exemplos deste artigo aplicaremos uma fórmula para determinar quais células devem ser formatadas. Devemos acessar a guia.
1) Página Inicial e o grupo Estilos. Em seguida clicamos no botão
2) Formatação Condicional e na opção
3) Nova Regra. Por fim, devemos escolher a opção
4) Usar uma fórmula para determinar quais células devem ser formatadas. Como no caso para colorir a linha inteira no Excel.
Nos itens de 4.1 a 4.9 mencionaremos as fórmulas a serem colocadas no espaço destacado em verde na imagem acima.
4. Aplicando formatação condicional para colorir a linha inteira no Excel
Para aplicar a formatação condicional que destaque a linha inteira precisamos criar regras que, quando verdadeiras, implementem a formatação.
Nos nossos exemplos lançaremos mão de fórmulas buscando destacar todo o registro. Teremos alguns cenários para diversificar as nossas possibilidades.
4.1. Destacando a linha inteira – critério “é igual”
Para implementarmos a formatação da linha inteira precisamos comparar todos os dados da coluna que contém os critérios com o nosso critério escolhido. Esta comparação fica implícita com a seleção prévia dos dados. A imagem a seguir mostra os dados previamente selecionados, a partir de B9 e indo até E27.
Nosso critério é o produto referenciado em C6.
Na nossa fórmula esta referência deverá ficar absoluta, ou seja, $C$6. Isso ocorre porque para todas as linhas dos dados o produto escolhido como critério não muda seu endereço uma vez que sempre está em C6.
A fórmula que deverá ser implementada precisará apenas estabelecer a comparação do primeiro registro com o critério escolhido em C6, visto que a seleção dos dados se encarrega de promover as demais comparações para as linhas seguintes.
Aqui, é importante aplicar a referência mista para ficar a coluna dos dados e manter as linhas livres.
A coluna do critério, que é a coluna D, possui os nomes dos produtos, sendo que o primeiro deles está na linha 9. Por isso, na fórmula, a referência deverá ser apenas para o primeiro produto e por isso deverá ficar como $D9.
A imagem a seguir ilustra a fórmula aplicada para formatar os registros sempre que o produto foi Uva Thompson.
Ao escolhermos outro critério automaticamente as linhas que os contém serão formatadas automaticamente.
Note a mudança que ocorre ao mudarmos o nosso critério de Uva Thompson para Tangerina Importada na imagem a seguir.
A fórmula inserida foi = $C$6=$D9.
4.2. Destacando a linha inteira – critério “é diferente”
Podemos também formatar tudo, exceto o nosso critério. Em relação à imagem anterior o ajuste é simples caso desejemos evidenciar todas as linhas que não se refiram ao produto Tangerina Importada.
Na fórmula, basta trocar o operador de comparação = por <> (trocar igual por diferente). Veja na imagem a seguir a fórmula e o resultado da formatação condicional para colorir a linha inteira no Excel.
A fórmula inserida foi = $C$6<>$D9.
4.3. Destacando a linha inteira – critério “contém”
Se desejarmos formatar condicionalmente a linha inteira ao conter parte de um critério ou até mesmo o critério em sua totalidade, devemos lançar mão de uma função de busca que localize o termo pesquisado (nosso critério) em cada valor da coluna pesquisada.
Utilizaremos a função LOCALIZAR para verificar se o termo pesquisado consta ao longo do texto dos dados da coluna na qual será pesquisado.
Ao constar, seja no início, no meio ou fim (ou seja, em qualquer posição) a formatação ocorrerá.
A função LOCALIZAR terá em seu primeiro argumento o termo pesquisado e no seu segundo argumento a primeira célula da coluna nos dados que contém o critério.
A função LOCALIZAR retorna um número inteiro em caso de localizar corretamente aquilo que foi pesquisado. Qualquer que seja a posição, LOCALIZAR retornará um número inteiro que vai de 1 até o número de caracteres presente em cada célula dos dados. Este número inteiro já representa o valor verdadeiro, garantindo o funcionamento da regra.
Quando LOCALIZAR não encontra o termo pesquisado, retorna um erro (#VALOR!). Este erro é interpretado como falso, e, desta forma, a formatação não ocorrerá.
A imagem a seguir ilustra a fórmula para formatar todos os registros contendo a palavra uva.
Note que a regra formatou tanto produtos que começam com a palavra uva quanto produtos que têm o nome terminado com esta palavra.
O mesmo ocorreria se o nome do produto contivesse a palavra uva em posições intermediárias. A fórmula utilizada foi: =LOCALIZAR($C$6;$D9).
Poderíamos também usar outras fórmulas que retornariam o mesmo resultado. Experimente usar a fórmula =CORRESP(“*”&$C$6&”*”;$D9;0) e também =CONT.SE($D9;”*”&$C$6&”*”).
4.4. Destacando a linha inteira – critério “não contém”
Para colorir a linha inteira no Excel sempre que o registro não contiver o critério selecionado, podemos usar a lógica inversa do critério “contém”, abordado no item 4.3. Tomando como base a fórmula em que usamos a função LOCALIZAR, no item 4.3, e, entendendo que ela não formata exatamente os valores que não contenham os critérios, podemos formatar os registros sempre que LOCALIZAR retornar o erro #VALOR!.
Basta encapsular toda a fórmula anteriormente apresentada na função ÉERROS.
A imagem a seguir ilustra a formatação condicional para todos os registros que não contenham a palavra uva na coluna Produto.
A fórmula empregada foi =ÉERROS(LOCALIZAR($C$6;$D9)). Poderíamos usar também a fórmula =NÃO(ÉNÚM((LOCALIZAR($C$6;$D9)))).
4.5. Destacando a linha inteira – critério “começa com” para colorir linha inteira no Excel
Para destacar apenas as linhas cujo nome do produto começa com um determinado conjunto de caracteres, podendo ser uma palavra completa ou não, utilizaremos o asterisco como caractere curinga, concatenando-o após o termo pesquisado.
Utilizaremos a função CORRESP que, quando encontra o termos buscado, retorna um número inteiro. Isso é o bastante para ser avaliado como verdadeiro, sendo suficiente para promover a formatação.
A imagem a seguir ilustra a fórmula utilizada para formatar condicionalmente todos os registros que contenham produtos que possuam o termo uva logo no início.
Perceba que os registros das linhas 16 e 21 possuem a palavra uva (destacada em vermelho) que não ocorre no início dos nomes dos produtos e por isso não foram formatados. A fórmula utilizada foi =CORRESP($C$6&”*”;$D9;0). Poderíamos também usar a fórmula =CONT.SE($D9;$C$6&”*”).
4.6. Destacando a linha inteira – critério “termina com”
A abordagem para formatar os registros sempre que o nome do produto termina com o termo usado como critério é similar ao que foi ilustrado no item anterior (4.5).
O que precisamos fazer é apenas mudar a posição do asterisco: ele precisará vir antes da célula na concatenação. Em vez de utilizar $C$6&”*” usaremos “*”&$C$6.
A imagem a seguir ilustra a fórmula ajustada para formatar apenas os nomes dos produtos terminados com o temo uva. Note que todos os registros iniciados com o termo uva (destacado em vermelho) não foram formatados.
A fórmula utilizada foi =CORRESP(“*”&$C$6;$D9;0). Poderíamos também usar a fórmula =CONT.SE($D9;”*”&$C$6).
4.7. Destacando a linha inteira ao comparar datas
Para exemplificar o destaque de toda a linha com base na comparação de datas, vamos simular um controle de pagamentos onde formataremos todos os registros nos quais o pagamento ocorreu com atraso, ou seja, após a data de vencimento.
Os dados mostram um conjunto de contas pagas em uma mesma data, assinalada em C6 (25/08/2021).
Embora estas contas tenham sido pagas nesta mesma data, elas possuem diversas datas de vencimento e eventualmente algumas foram pagas após o vencimento.
Devemos comparar todas as datas da coluna B (Data de vencimento) com a data de pagamento (C6). Sempre que a data de pagamento for superior à data de vencimento, a linha inteira deverá ser formatada.
Neste caso, optamos por formatar a linha inteira alterando apenas a fonte, colocando-a em negrito e itálico, com cor vermelha.
A fórmula utilizada foi =$C$6>$B9.
4.8. Destacando a linha inteira de acordo com critérios simultâneos (lógica E)
Além dos cenários já expostos, podemos também fazer formatação de acordo com a combinação de múltiplos critérios.
No nosso exemplo, que é um cenário de vendas, desejamos formatar o registro inteiro sempre que a venda ocorreu no estado selecionado e para um produto específico a partir de um certo valor.
Utilizaremos a função E para contemplar critérios simultâneos.
A imagem a seguir ilustra os registros formatados em verde quando a venda se refere à carne no estado de Rondônia, mas somente quando o valor foi no mínimo R$ 1.000,00.
Note que o registro destacado em vermelho não foi formatado porque embora a venda tenha sido de carne em RO, seu valor não chegou a R$ 1.000,00 (foi apenas R$ 200,00).
Perceba também que o registro destacado em azul não foi formatado porque embora a venda tenha sido em RO e com um valor bem acima de R$ 1.000,00 (chegou a R$ 3.800,00) o produto vendido foi camarão, e não carne.
A fórmula usada foi =E($B$6=$B9;$C$6=$C9;$D9>=$D$6).
4.9. Colorir a linha inteira no Excel de acordo com critérios opcionais (lógica OU)
Aproveitando o cenário montado no item anterior (4.8), desejamos formatar os registros em que as vendas ocorreram no mês de fevereiro ou foram realizadas no Pará.
Utilizaremos a função OU para implementar a nossa regra.
A imagem a seguir ilustra os registros formatados.
Note que os registros não são formatados apenas quando o estado em que a venda foi realizada não é o Pará ao mesmo tempo em que a venda não foi realizada em fevereiro.
A fórmula utilizada foi =OU($B$6=$E9;$C$6=$C9).
Download Planilha de Colorir Linha Inteira Excel
Realize o download da planilha para colorir linha inteira no excel com formatação condicional neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
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:
