Adicionar, alterar, encontrar ou limpar formatos condicionais Saiba mais sobre a formatação condicional A formatação condicional ajuda você a responder visualmente perguntas específicas sobre seus dados. Você pode aplicar formatação condicional a um intervalo de células, uma tabela do Microsoft Excel ou um relatório de tabela dinâmica. Há diferenças importantes para entender quando você usa formatação condicional em um relatório de tabela dinâmica. Os benefícios da formatação condicional Sempre que você analisa dados, muitas vezes você faz perguntas, tais como: Onde estão as exceções em um resumo dos lucros nos últimos cinco anos Quais são as tendências em uma pesquisa de marketing nos últimos dois anos Quem vendeu? Mais de 50.000 dólares este mês Qual é a distribuição total de idade dos funcionários Quais os produtos têm maiores que 10 receitas aumentam de ano para ano Quem são os alunos de melhor desempenho e de baixo desempenho na classe de primeiro ano A formatação condicional ajuda a responder a essas perguntas tornando mais fácil Para destacar células interessantes ou intervalos de células, enfatizar valores incomuns e visualizar dados usando barras de dados, escalas de cores e conjuntos de ícones. Um formato condicional altera a aparência de um intervalo de células com base em condições (ou critérios). Se a condição for verdadeira, o intervalo da célula é formatado com base nessa condição se o condicional for falso, o intervalo da célula não é formatado com base nessa condição. Você pode classificar e filtrar por formato, incluindo a cor da célula e a cor da fonte, se você formatou manual ou condicionalmente as células. Observação: quando você cria um formato condicional, você pode fazer referência apenas a outras células na mesma planilha ou, em certos casos, células em planilhas na mesma pasta de trabalho atualmente aberta. Você não pode usar a formatação condicional em referências externas para outro caderno de trabalho. Formatação condicional para um relatório de tabela dinâmica A formatação condicional em um relatório de tabela dinâmica é diferente da formatação condicional em um intervalo de células ou uma tabela do Excel de várias maneiras: se você alterar o layout do relatório de tabela dinâmica por filtragem, esconder níveis, colapsar e expandir níveis, ou Movendo um campo, o formato condicional é mantido enquanto os campos nos dados subjacentes não forem removidos. O escopo do formato condicional para campos na área de Valores pode basear-se na hierarquia de dados e é determinado por todas as crianças visíveis (o próximo nível inferior em uma hierarquia) de um pai (o próximo nível mais alto em uma hierarquia) em linhas Para uma ou mais colunas, ou colunas para uma ou mais linhas. Observação: na hierarquia de dados, os filhos não herdam a formatação condicional do pai e o pai não herda a formatação condicional das crianças. Existem três métodos para escopo do formato condicional dos campos na área Valores: por seleção, pelo campo correspondente e por campo de valor. O método padrão de campos de escopo na área Valores é por seleção. Você pode alterar o método de escopo para o campo de campo ou valor correspondente, usando a regra de Implementação de formatação no botão de opção, a caixa de diálogo Nova regra de formatação ou a caixa de diálogo Editar regra de formatação. Os três métodos de escopo (guia Início, Grupo de estilos, Comando de formatação condicional) oferecem maior flexibilidade dependendo das suas necessidades: Escalonamento por seleção Use este método se desejar selecionar: um conjunto contíguo de campos na área Valores, como todos Dos totais do produto para uma região. Um conjunto de campos desconectável na área Valores, como os totais dos produtos para regiões diferentes em todos os níveis da hierarquia de dados. Escopo por campo de valor Use este método se desejar: Evite fazer muitas seleções desconectáveis. Acondicionalmente, formatar um conjunto de campos na área Valores para todos os níveis na hierarquia de dados. Inclua subtotais e grandes totais. Escopo pelo campo correspondente Use este método se desejar: Evite fazer muitas seleções descontínuas. Acondicionalmente, formateie um conjunto de campos na área Valores para um nível na hierarquia de dados. Quando você condicionalmente formatar campos na área Valores para valores superiores, inferiores, acima da média ou abaixo da média, a regra é baseada em todos os valores visíveis por padrão. No entanto, quando você atinge o campo correspondente, em vez de usar todos os valores visíveis, você pode opcionalmente aplicar o formato condicional para cada combinação de: Uma coluna e seu campo de linha principal. Uma linha e seu campo de coluna pai. Formatação condicional em planilhas Você pode usar formatação condicional em células que são referenciadas em outra planilha na mesma pasta de trabalho. Esse recurso não está disponível entre pastas de trabalho. Formate todas as células usando uma escala de duas cores. As escalas de cores são guias visuais que o ajudam a entender a distribuição e a variação de dados. Uma escala de duas cores ajuda você a comparar um intervalo de células usando uma gradação de duas cores. A sombra da cor representa valores mais altos ou mais baixos. Por exemplo, em uma escala de cores verde e vermelha, você pode especificar que as células de maior valor tenham uma cor mais verde e as células de menor valor tenham uma cor mais vermelha. Se uma ou mais células do intervalo contiverem uma fórmula que retorna um erro, a formatação condicional não é aplicada em todo o intervalo. Para garantir que a formatação condicional seja aplicada em todo o intervalo, use uma função IS ou IFERROR para retornar um valor diferente de um valor de erro. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique Escalas de cores. Selecione uma escala de duas cores. Passe o mouse sobre os ícones da escala de cores para ver qual ícone é uma escala de duas cores. A cor superior representa valores mais altos e a cor inferior representa valores mais baixos. Você pode alterar o método de escopo para campos na área Valores de um relatório de tabela dinâmica usando o botão Opções de formatação que aparece ao lado de um campo de tabela dinâmica com formatação condicional aplicada. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique em Gerenciar Regras. A caixa de diálogo Gerenciador de regras de formatação condicional é exibida. Siga um destes procedimentos: Para adicionar um formato condicional, clique em Nova regra. A caixa de diálogo Nova regra de formatação é exibida. Para alterar um formato condicional, faça o seguinte: Certifique-se de que a tabela apropriada, a tabela ou o relatório de tabela dinâmica são selecionados nas regras Exibir formatação para a caixa de listagem. Opcionalmente, altere o intervalo de células clicando em Collapse Dialog na caixa Applies to para ocultar temporariamente a caixa de diálogo, selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expand Dialog. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar regra de formatação é exibida. Sob aplicar regra para. Para opcionalmente alterar o escopo para campos na área Valores de um relatório de tabela dinâmica por: Seleção, clique em células selecionadas. Todas as células para um rótulo de valor, clique em Todas as células que exibem os valores do labelgt ltValue. Todas as células para um rótulo de valor, excluindo subtotais e o total geral, clique em Todas as células que mostram os valores do labelgt ltValue para ltRow Labelgt. Em Selecionar um tipo de regra. Clique em Formatar todas as células com base em seus valores (padrão). Em Editar a Descrição da Regra. Na caixa de listagem Formato de estilo, selecione Escala de 2 cores. Para selecionar um tipo na caixa Tipo para Mínimo e Máximo. Siga um destes procedimentos: Formato dos valores mais baixos e mais altos Selecione o valor mais baixo eo valor mais alto. Nesse caso, você não insere um valor mínimo e máximo. Formate um número, data ou valor de hora Selecione o número. E, em seguida, insira um valor mínimo e máximo. Formatar uma porcentagem Selecione Porcentagem. E, em seguida, insira um valor mínimo e máximo. Os valores válidos são de 0 (zero) a 100. Não insira um sinal de porcentagem. Use uma porcentagem quando quiser visualizar todos os valores proporcionalmente porque a distribuição de valores é proporcional. Formate um percentil Selecionar percentil e, em seguida, insira um valor mínimo e máximo. Os percentis válidos são de 0 (zero) a 100. Use um percentil quando você deseja visualizar um grupo de valores altos (como o percentil superior 20) em uma proporção de cor e valores baixos (como o percentil inferior 20) em outra cor Grau, porque representam valores extremos que podem distorcer a visualização de seus dados. Formatar um resultado de fórmula Selecione a Fórmula. E então insira valores para Mínimo e Máximo. A fórmula deve retornar um valor de número, data ou hora. Comece a fórmula com um sinal igual (). As fórmulas inválidas resultam em que nenhuma formatação seja aplicada. É uma boa idéia testar a fórmula para se certificar de que não retorna um valor de erro. Certifique-se de que o valor mínimo seja inferior ao valor máximo. Você pode escolher um tipo diferente para o Mínimo e Máximo. Por exemplo, você pode escolher um número para uma porcentagem mínima de máximo. Para escolher uma escala de cores mínima e máxima, clique em Cor para cada uma, e selecione uma cor. Se quiser escolher cores adicionais ou criar uma cor personalizada, clique em Mais cores. A escala de cores que você seleciona é exibida na caixa de visualização. Formate todas as células usando uma escala de três cores. As escalas de cores são guias visuais que o ajudam a entender a distribuição e a variação de dados. Uma escala de três cores ajuda você a comparar uma variedade de células usando uma gradação de três cores. A sombra da cor representa valores superiores, médios ou inferiores. Por exemplo, em uma escala de cores verde, amarelo e vermelho, você pode especificar que as células de maior valor possuem uma cor verde, as células de valor médio têm uma cor amarela e as células de valor inferior têm uma cor vermelha. Se uma ou mais células do intervalo contiverem uma fórmula que retorna um erro, a formatação condicional não é aplicada em todo o intervalo. Para garantir que a formatação condicional seja aplicada em todo o intervalo, use uma função IS ou IFERROR para retornar um valor que você especifica (como 0 ou NA) em vez de um valor de erro. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique Escalas de cores. Selecione uma escala de três cores. A cor superior representa valores mais altos, a cor central representa valores médios e a cor inferior representa valores mais baixos. Passe o mouse sobre os ícones da escala de cores para ver qual ícone é uma escala de três cores. Você pode alterar o método de escopo para campos na área Valores de um relatório de tabela dinâmica usando o botão Opções de formatação que aparece ao lado de um campo de tabela dinâmica com formatação condicional aplicada .. Selecione uma ou mais células em um intervalo, tabela ou tabela dinâmica relatório. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique em Gerenciar Regras. A caixa de diálogo Gerenciador de regras de formatação condicional é exibida. Siga um destes procedimentos: Para adicionar um formato condicional, clique em Nova regra. A caixa de diálogo Nova regra de formatação é exibida. Para alterar um formato condicional, faça o seguinte: Certifique-se de que a tabela apropriada, a tabela ou o relatório de tabela dinâmica são selecionados nas regras Exibir formatação para a caixa de listagem. Opcionalmente, altere o intervalo de células clicando em Collapse Dialog na caixa Applies to para ocultar temporariamente a caixa de diálogo, selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expand Dialog. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar regra de formatação é exibida. Sob aplicar regra para. Para alterar opcionalmente o escopo para campos na área Valores de um relatório de tabela dinâmica por: Seleção, clique apenas em estas células. Campo correspondente, clique em Todas as células do fieldgt do ltvalue com os mesmos campos. Campo de valor, clique em Todas as células do fieldgt do ltvalue. Em Selecionar um tipo de regra. Clique em Formatar todas as células com base em seus valores. Em Editar a Descrição da Regra. Na caixa de listagem Formato de Estilo, selecione Escala de 3 cores. Selecione um tipo para Mínimo. Ponto intermediário. E Máximo. Siga um destes procedimentos: Formato dos valores mais baixos e mais altos Selecione um ponto intermediário. Nesse caso, você não insere um valor mais baixo e mais alto. Formate um número, data ou valor de hora Selecione o número. E então insira um valor mínimo. Ponto intermediário. E Máximo. Formatar uma porcentagem Selecione Porcentagem. E então insira um valor mínimo. Ponto intermediário. E Máximo. Os valores válidos são de 0 (zero) a 100. Não insira um sinal de porcentagem (). Use uma porcentagem quando quiser visualizar todos os valores proporcionalmente, pois usar uma porcentagem assegura que a distribuição de valores seja proporcional. Formate um percentil Selecione Percentile e, em seguida, insira um valor para Mínimo. Ponto intermediário. E Máximo. Os percentis válidos são de 0 (zero) a 100. Use um percentil quando você deseja visualizar um grupo de valores elevados (como o percentil 20 superior) em proporção de uma cor e valores baixos (como o percentil inferior 20) em outro Proporção de grau de cor, porque representam valores extremos que podem distorcer a visualização de seus dados. Formatar um resultado de fórmula Selecione a Fórmula. E então insira um valor mínimo. Ponto intermediário. E Máximo. A fórmula deve retornar um valor de número, data ou hora. Comece a fórmula com um sinal igual (). As fórmulas inválidas resultam em que nenhuma formatação seja aplicada. É uma boa idéia testar a fórmula para se certificar de que não retorna um valor de erro. Você pode definir valores mínimos, medianos e máximos para o intervalo de células. Certifique-se de que o valor em Mínimo seja menor que o valor em ponto médio. Que por sua vez é inferior ao valor em Máximo. Você pode escolher um tipo diferente para Mínimo. Ponto intermediário. E Máximo. Por exemplo, você pode escolher um número mínimo, percentil intermediário e porcentagem máxima. Em muitos casos, o valor Midpoint padrão de 50 por cento funciona melhor, mas você pode ajustar isso para atender aos requisitos exclusivos. Para escolher um Mínimo. Ponto intermediário. E Escala de cores máxima, clique em Cor para cada uma e selecione uma cor. Se quiser escolher cores adicionais ou criar uma cor personalizada, clique em Mais cores. A escala de cores que você seleciona é exibida na caixa de visualização. Formate todas as células usando barras de dados Uma barra de dados ajuda você a ver o valor de uma célula em relação a outras células. O comprimento da barra de dados representa o valor na célula. Uma barra mais longa representa um valor maior, e uma barra mais curta representa um valor menor. As barras de dados são úteis para detectar números mais altos e mais baixos, especialmente com grandes quantidades de dados, como brinquedos mais vendidos e de venda inferior em um relatório de vendas de férias. Dica: se uma ou mais células do intervalo contiverem uma fórmula que retorna um erro, a formatação condicional não é aplicada em todo o intervalo. Para garantir que a formatação condicional seja aplicada em todo o intervalo, use uma função IS ou IFERROR para retornar um valor (como 0 ou NA) em vez de um valor de erro. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilo, clique na seta ao lado da formatação condicional. Clique em Barras de dados. E depois selecione um ícone de barra de dados. Você pode alterar o método de escopo para campos na área Valores de um relatório de tabela dinâmica usando a opção Aplicar regra de formatação ao botão de opção. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique em Gerenciar Regras. A caixa de diálogo Gerenciador de regras de formatação condicional é exibida. Siga um destes procedimentos: Para adicionar um formato condicional, clique em Nova regra. A caixa de diálogo Nova regra de formatação é exibida. Para alterar um formato condicional, faça o seguinte: Certifique-se de que a tabela apropriada, a tabela ou o relatório de tabela dinâmica são selecionados nas regras Exibir formatação para a caixa de listagem. Opcionalmente, altere o intervalo de células clicando em Collapse Dialog na caixa Applies to para ocultar temporariamente a caixa de diálogo, selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expand Dialog. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar regra de formatação é exibida. Sob aplicar regra para. Para alterar opcionalmente o escopo para campos na área Valores de um relatório de tabela dinâmica por: Seleção, clique apenas em estas células. Campo correspondente, clique em Todas as células do fieldgt do ltvalue com os mesmos campos. Campo de valor, clique em Todas as células do fieldgt do ltvalue. Em Selecionar um tipo de regra. Clique em Formatar todas as células com base em seus valores. Em Editar a Descrição da Regra. Na caixa de listagem Formato de estilo, selecione Barra de dados. Selecione um tipo mínimo e máximo. Execute um dos seguintes procedimentos: Formate os valores mais baixos e mais altos Selecione o valor mais baixo eo valor mais alto. Nesse caso, você não insere um valor mínimo e máximo. Formate um número, data ou valor de hora Selecione o número. E, em seguida, insira um valor mínimo e máximo. Formatar uma porcentagem Selecione Porcentagem. E então insira um valor para Mínimo e Máximo. Os valores válidos são de 0 (zero) a 100. Não insira um sinal de porcentagem (). Use uma porcentagem quando quiser visualizar todos os valores proporcionalmente, pois usar uma porcentagem assegura que a distribuição de valores seja proporcional. Formate um percentil Selecione Percentile e, em seguida, insira um valor para Mínimo e Máximo. Os percentis válidos são de 0 (zero) a 100. Use um percentil quando você deseja visualizar um grupo de valores altos (como o percentil 20 superior) em uma proporção de barra de dados e valores baixos (como o percentil inferior 20) em outro Proporção da barra de dados, porque eles representam valores extremos que podem distorcer a visualização de seus dados. Formatar um resultado de fórmula Selecione a Fórmula. E então insira um valor para Mínimo e Máximo. A fórmula deve retornar um valor de número, data ou hora. Comece a fórmula com um sinal igual (). As fórmulas inválidas resultam em que nenhuma formatação seja aplicada. É uma boa idéia testar a fórmula para se certificar de que não retorna um valor de erro. Certifique-se de que o valor mínimo seja inferior ao valor máximo. Você pode escolher um tipo diferente para Mínimo e Máximo. Por exemplo, você pode escolher um número mínimo e uma porcentagem máxima. Para escolher uma escala de cores mínima e máxima, clique em Cor da barra. Se quiser escolher cores adicionais ou criar uma cor personalizada, clique em Mais cores. A cor da barra que você seleciona é exibida na caixa de visualização. Para mostrar apenas a barra de dados e não o valor na célula, selecione Mostrar barra somente. Para aplicar uma borda sólida às barras de dados, selecione Borda sólida na caixa de listagem Borda e escolha uma cor para a borda. Para escolher entre uma barra sólida e uma barra degradada, selecione preenchimento sólido ou preenchimento de gradiente na caixa de listagem de preenchimento. Para formatar barras negativas, clique em Valor negativo e Eixo e, em seguida, na caixa de diálogo Configuração de valores negativos e configurações de eixo, escolha as opções para o preenchimento da barra negativa e as cores da borda. Você pode escolher configurações de posição e uma cor para o eixo. Quando terminar de selecionar as opções, clique em OK. Você pode alterar a direção das barras escolhendo uma configuração na caixa de listagem da direção do bar. Isso é definido como Contexto por padrão, mas você pode escolher entre uma direção da esquerda para a direita e uma direção da direita para a esquerda, dependendo de como você deseja apresentar seus dados. Formate todas as células usando um conjunto de ícones Use um ícone configurado para anotar e classificar dados em três a cinco categorias separadas por um valor limiar. Cada ícone representa uma gama de valores. Por exemplo, no conjunto de ícones de 3 setas, a seta verde para cima representa valores mais altos, a seta lateral amarela representa valores médios e a seta vermelha para baixo representa valores mais baixos. Você pode escolher mostrar ícones apenas para células que atendam a uma condição, por exemplo, exibindo um ícone de aviso para as células que ficam abaixo de um valor crítico e sem ícones para aqueles que o excedem. Para fazer isso, você esconde ícones selecionando Nenhum ícone de célula na lista suspensa de ícones ao lado do ícone quando estiver configurando condições. Você também pode criar sua própria combinação de conjuntos de ícones, por exemplo, uma marca de seleção de símbolo verde, um semáforo amarelo e uma bandeira vermelha. Problema: A formatação condicional não aparece para nenhuma célula no intervalo Se uma ou mais células do intervalo contiverem uma fórmula que retorna um erro, a formatação condicional não é aplicada em todo o intervalo. Para garantir que a formatação condicional seja aplicada a todo o intervalo, use uma função IS ou IFERROR para retornar um valor (como 0 ou NA) em vez disso, um valor de erro. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilo, clique na seta ao lado da formatação condicional. Clique em Conjunto de ícones. E depois selecione um conjunto de ícones. Você pode alterar o método de escopo para campos na área Valores de um relatório de tabela dinâmica usando a opção Aplicar regra de formatação ao botão de opção. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique em Gerenciar Regras. A caixa de diálogo Gerenciador de regras de formatação condicional é exibida. Siga um destes procedimentos: Para adicionar um formato condicional, clique em Nova regra. A caixa de diálogo Nova regra de formatação é exibida. Para alterar um formato condicional, faça o seguinte: Certifique-se de que a tabela apropriada, a tabela ou o relatório de tabela dinâmica são selecionados nas regras Exibir formatação para a caixa de listagem. Opcionalmente, altere o intervalo de células clicando em Collapse Dialog na caixa Applies to para ocultar temporariamente a caixa de diálogo, selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expand Dialog. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar regra de formatação é exibida. Sob aplicar regra para. Para alterar opcionalmente o escopo para campos na área Valores de um relatório de tabela dinâmica por: Seleção, clique apenas em estas células. Campo correspondente, clique em Todas as células do fieldgt do ltvalue com os mesmos campos. Campo de valor, clique em Todas as células do fieldgt do ltvalue. Em Selecionar um tipo de regra. Clique em Formatar todas as células com base em seus valores. Em Editar a Descrição da Regra. Na caixa de listagem Formato de estilo, selecione Conjunto de ícones. Selecione um conjunto de ícones. O padrão é 3 semáforos (desarmado). O número de ícones e os operadores de comparação padrão e os valores de limiar para cada ícone podem variar para cada conjunto de ícones. Se desejar, você pode ajustar os operadores de comparação e os valores de limiar. O intervalo de valores padrão para cada ícone é igual em tamanho, mas você pode ajustá-los para atender aos seus requisitos exclusivos. Certifique-se de que os limiares estão em uma seqüência lógica do mais alto para o mais baixo de cima para baixo. Execute um dos seguintes procedimentos: Formate um número, data ou valor de hora Selecione o número. Formatar uma porcentagem Selecione Porcentagem. Os valores válidos são de 0 (zero) a 100. Não insira um sinal de porcentagem (). Use uma porcentagem quando quiser visualizar todos os valores proporcionalmente, pois usar uma porcentagem assegura que a distribuição de valores seja proporcional. Formate um percentual Selecione o percentil. Os percentis válidos são de 0 (zero) a 100. Use um percentil quando você deseja visualizar um grupo de valores altos (como o percentil 20 superior) usando um ícone particular e valores baixos (como o percentil 20 inferior) usando outro ícone , Porque eles representam valores extremos que podem distorcer a visualização de seus dados. Formatar um resultado de fórmula Selecione a Fórmula. E, em seguida, insira uma fórmula em cada caixa de valor. A fórmula deve retornar um valor de número, data ou hora. Comece a fórmula com um sinal igual (). As fórmulas inválidas resultam em que nenhuma formatação seja aplicada. É uma boa idéia testar a fórmula para se certificar de que não retorna um valor de erro. Para que o primeiro ícone represente valores mais baixos e o último ícone represente valores mais altos, selecione Reverse Icon Order. Para mostrar apenas o ícone e não o valor na célula, selecione Mostrar Ícone Somente. Talvez seja necessário ajustar a largura da coluna para acomodar o ícone. Existem três tamanhos de ícones. O tamanho do ícone que é exibido depende do tamanho da fonte que é usado nessa célula. Formate apenas células que contêm valores de texto, número ou data ou hora. Para encontrar mais facilmente células específicas dentro de um intervalo de células, você pode formatar essas células específicas com base em um operador de comparação. Por exemplo, em uma planilha de inventário ordenada por categorias, você pode destacar os produtos com menos de 10 itens disponíveis em amarelo. Ou, em uma planilha de resumo da loja de varejo, você pode identificar todas as lojas com lucros superiores a 10, volumes de vendas inferiores a 100.000 e região igual ao Sudeste. Nota: Você não pode formatar campos condicionalmente na área Valores de um relatório de tabela dinâmica por texto ou data, apenas por número. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilo, clique na seta ao lado da formatação condicional. E, em seguida, clique em Realçar regras das células. Selecione o comando que deseja, como entre. Igual ao texto que contém. Ou uma data ocorrida. Digite os valores que deseja usar e, em seguida, selecione um formato. Você pode alterar o método de escopo para campos na área Valores de um relatório de tabela dinâmica usando a opção Aplicar regra de formatação ao botão de opção. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique em Gerenciar Regras. A caixa de diálogo Gerenciador de regras de formatação condicional é exibida. Siga um destes procedimentos: Para adicionar um formato condicional, clique em Nova regra. A caixa de diálogo Nova regra de formatação é exibida. Para alterar um formato condicional, faça o seguinte: Certifique-se de que a tabela apropriada, a tabela ou o relatório de tabela dinâmica são selecionados nas regras Exibir formatação para a caixa de listagem. Opcionalmente, altere o intervalo de células clicando em Collapse Dialog na caixa Applies to para ocultar temporariamente a caixa de diálogo, selecionando o novo intervalo de células na planilha ou em outras planilhas e, em seguida, selecionando Expand Dialog. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar regra de formatação é exibida. Sob aplicar regra para. Para alterar opcionalmente o escopo para campos na área Valores de um relatório de tabela dinâmica por: Seleção, clique apenas em estas células. Campo correspondente, clique em Todas as células do fieldgt do ltvalue com os mesmos campos. Campo de valor, clique em Todas as células do fieldgt do ltvalue. Em Selecionar um tipo de regra. Clique em Formatar apenas as células que contêm. Em Editar a Descrição da Regra. Nas células Formato apenas com a caixa de listagem, siga um destes procedimentos: Formato por número, data ou hora Selecione Valor da célula. Selecione um operador de comparação e, em seguida, insira um número, data ou hora. Por exemplo, selecione Entre e depois digite 100 e 200. Ou selecione Igual e depois entre 112009. Você também pode inserir uma fórmula que retorna um número, data ou valor de tempo. Se você inserir uma fórmula, comece com um sinal igual (). As fórmulas inválidas resultam em que nenhuma formatação seja aplicada. É uma boa idéia testar a fórmula para se certificar de que não retorna um valor de erro. Formatar por texto Selecione o texto específico. Escolha um operador de comparação e, em seguida, insira texto. Por exemplo, selecione Contém e, em seguida, entre Prata. Ou selecione Começar com e depois digitar Tri. As cotações estão incluídas na seqüência de pesquisa e você pode usar caracteres curinga. O comprimento máximo de uma string é de 255 caracteres. Você também pode inserir uma fórmula que retorna texto. Se você inserir uma fórmula, comece com um sinal igual (). As fórmulas inválidas resultam em que nenhuma formatação seja aplicada. É uma boa idéia testar a fórmula para se certificar de que não retorna um valor de erro. Formato por data Selecione Datas Ocorrendo. E depois selecione uma comparação de datas. Por exemplo, selecione Ontem ou Na próxima semana. Formatar células com espaços em branco ou sem espaços em branco. Selecione Blanks ou No Blanks. Um valor em branco é uma célula que não contém dados e é diferente de uma célula que contém um ou mais espaços (os espaços são considerados como texto). Formatar células com erro ou sem valores de erro Selecione Erros ou Não Erros. Os valores de erro incluem:, VALOR. DIV0. NOME. NA, REF. NUM. E NULL. Para especificar um formato, clique em Formatar. A caixa de diálogo Formatar células é exibida. Selecione o número, a fonte, a borda ou o formato de preenchimento que você deseja aplicar quando o valor da célula atende a condição e, em seguida, clique em OK. Você pode escolher mais de um formato. Os formatos que você seleciona são exibidos na caixa de visualização. Formate apenas os valores classificados superior ou inferior Você pode encontrar os valores mais altos e mais baixos em um intervalo de células baseadas em um valor de corte especificado. Por exemplo, você pode encontrar os 5 principais produtos de venda em um relatório regional, os 15 produtos mais baixos em uma pesquisa de clientes ou os 25 melhores salários em uma análise de pessoal do departamento. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilo, clique na seta ao lado da formatação condicional. E depois clique em TopBottom Rules. Selecione o comando que deseja, como itens Top 10 ou Bottom 10. Digite os valores que deseja usar e, em seguida, selecione um formato. Você pode alterar o método de escopo para campos na área Valores de um relatório de tabela dinâmica usando a opção Aplicar regra de formatação ao botão de opção. Selecione uma ou mais células em um intervalo, tabela ou relatório de tabela dinâmica. Na guia Início, no grupo Estilos, clique na seta ao lado da formatação condicional. E depois clique em Gerenciar Regras. A caixa de diálogo Gerenciador de regras de formatação condicional é exibida. Siga um destes procedimentos: Para adicionar um formato condicional, clique em Nova regra. A caixa de diálogo Nova regra de formatação é exibida. Para alterar um formato condicional, faça o seguinte: Certifique-se de que a tabela apropriada, a tabela ou o relatório de tabela dinâmica são selecionados nas regras Exibir formatação para a caixa de listagem. Opcionalmente, altere o intervalo de células clicando em Collapse Dialog na caixa Applies to para ocultar temporariamente a caixa de diálogo, selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expand Dialog. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar regra de formatação é exibida. Sob aplicar regra para. Para opcionalmente alterar os campos de escopo na área Valores de um relatório de tabela dinâmica por: Seleção, clique apenas em essas células. Campo correspondente, clique em Todas as células do fieldgt do ltvalue com os mesmos campos. Campo de valor, clique em Todas as células do fieldgt do ltvalue. Em Selecionar um tipo de regra. Clique em Formatar apenas valores de classificação superior ou inferior. Em Editar a Descrição da Regra. Nos valores de Formato que se classificam na caixa de listagem, selecione Top ou Bottom. Execute um dos seguintes procedimentos: Para especificar um número superior ou inferior, insira um número e, em seguida, desmarque a caixa de seleção do alcance selecionado. Os valores válidos são de 1 a 1000. Para especificar uma porcentagem superior ou inferior, insira um número e, em seguida, selecione a caixa de seleção do alcance selecionado. Os valores válidos são de 1 a 100. Opcionalmente, altere como o formato é aplicado para os campos na área Valores de um relatório de tabela dinâmica que são escopo pelo campo correspondente. Por padrão, o formato condicional é baseado em todos os valores visíveis. No entanto, quando você atinge o campo correspondente, em vez de usar todos os valores visíveis, você pode aplicar o formato condicional para cada combinação de: uma coluna e seu campo de linha principal, selecionando cada grupo de Colunas. Uma linha e seu campo de coluna pai, selecionando cada grupo de linhas. Para especificar um formato, clique em Formatar. A caixa de diálogo Formatar células é exibida. Selecione o número, a fonte, a borda ou o formato de preenchimento que você deseja aplicar quando o valor da célula atende a condição e, em seguida, clique em OK. Você pode escolher mais de um formato. Os formatos que você seleciona são exibidos na caixa de visualização. Format only values that are above or below average You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating. Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting . and then click TopBottom Rules . Select the command that you want, such as Above Average or Below Average . Enter the values that you want to use, and then select a format. You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button. Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting . and then click Manage Rules . The Conditional Formatting Rules Manager dialog box is displayed. Do one of the following: To add a conditional format, click New Rule . The New Formatting Rule dialog box is displayed. To change a conditional format, do the following: Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . Select the rule, and then click Edit rule . The Edit Formatting Rule dialog box is displayed. Under Apply Rule To . to optionally change the scope for fields in the Values area of a PivotTable report by: Selection, click Just these cells . Corresponding field, click All ltvalue fieldgt cells with the same fields . Value field, click All ltvalue fieldgt cells . Under Select a Rule Type . click Format only values that are above or below average . Under Edit the Rule Description . in the Format values that are list box, do one of the following: To format cells that are above or below the average for all of the cells in the range, select Above or Below . To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation. Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field. By default, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of: A column and its parent row field, by selecting each Column group . A row and its parent column field, by selecting each Row group . Click Format to display the Format Cells dialog box. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK . You can choose more than one format. The formats that you select are displayed in the Preview box. Format only unique or duplicate values Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values. Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting . and then click Highlight Cells Rules . Select Duplicate Values . Enter the values that you want to use, and then select a format. Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting . and then click Manage Rules . The Conditional Formatting Rules Manager dialog box is displayed. Do one of the following: To add a conditional format, click New Rule . The New Formatting Rule dialog box is displayed. To change a conditional format, do the following: Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . Select the rule, and then click Edit rule . The Edit Formatting Rule dialog box is displayed. Under Select a Rule Type . click Format only unique or duplicate values . Under Edit the Rule Description . in the Format all list box, select unique or duplicate . Click Format to display the Format Cells dialog box. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK . You can choose more than one format. The formats that you select are displayed in the Preview box. Use a formula to determine which cells to format If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting . and then click Manage Rules . The Conditional Formatting Rules Manager dialog box is displayed. Do one of the following: To add a conditional format, click New Rule . The New Formatting Rule dialog box is displayed. To change a conditional format, do the following: Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by selecting Expand Dialog . Select the rule, and then click Edit rule . The Edit Formatting Rule dialog box is displayed. Under Apply Rule To . to optionally change the scope for fields in the Values area of a PivotTable report by: Selection, click Selected cells . Corresponding field, click All cells showing ltValues fieldgt values . Value field, All cells showing ltValues fieldgt for ltRowgt . Under Select a Rule Type . click Use a formula to determine which cells to format . Under Edit the Rule Description . in the Format values where this formula is true list box, enter a formula. You must start the formula with an equal sign () and the formula must return a logical value of TRUE (1) or FALSE (0). Example 1: Use one conditional format with multiple criteria and cell references outside of the range of cells In this formula, one conditional format with multiple criteria applied to the range A1:A5 formats the cells green if the average value for all of the cells in the range is greater than the value in cell F1 and any cell in the range has a minimum value greater than or equal to the value in G1. Cells F1 and G1 are outside of the range of cells for which the conditional format is applied. The AND function combines multiple criteria, and the AVERAGE and MIN functions calculate the values. Green cell color Example 2: Shade every other row by using the MOD and ROW functions This formula shades every other row in the range of cells a blue cell color. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number. Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted. Blue cell color Click Format to display the Format Cells dialog box. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK . You can choose more than one format. The formats that you select are displayed in the Preview box. You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references. Find cells that have conditional formats If your worksheet has one or more cells with a conditional format, you can quickly locate them so that you can copy, change, or delete the conditional formats. You can use the Go To Special command to either find only cells with a specific conditional format or find all cells with conditional formats. Find all cells that have a conditional format Click any cell without a conditional format. On the Home tab, in the Editing group, click the arrow next to Find amp Select . and then click Conditional Formatting . Find only cells with the same conditional format Click the cell that has the conditional format that you want to find. On the Home tab, in the Editing group, click the arrow next to Find amp Select . and then click Go To Special . Click Conditional formats . Click Same under Data validation . Clear conditional formats Do one of the following: On the Home tab, in the Styles group, click the arrow next to Conditional Formatting . and then click Clear Rules Click Entire Sheet . In a range of cells, table, or PivotTable Select the range of cells, table, or PivotTable for which you want to clear conditional formats. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting . and then click Clear Rules . Depending on what you have selected, click Selected Cells . This Table . or This PivotTable. Got a version of Excel that uses the ribbon interface (Excel 2007 or later) This site is for you If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface . Author Bio With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates. a computer and publishing services company. Learn more about Allen. Helpful Links An Average that Excludes Zero Values by Allen Wyatt (last updated September 9, 2016) Please Note : This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you . For a version of this tip written specifically for earlier versions of Excel, click here: An Average that Excludes Zero Values. Veronica knows how to use the AVERAGE function to determine the average of a range of values. She would like to have the average determined based on the non-zero values in the range, however. The worksheet function most suited to this purpose is to use AVERAGEIF. You can use it in this manner: This function only includes in the average those cells that contain values greater than zero. If you want to also exclude blank cells, you should use the AVERAGEIFS function. This function differs from AVERAGEIF in that it allows you to specify multiple criteria that indicate which cells to average. Of course, if you want to approach the problem old school (without using AVERAGEIF or AVERAGEIFS), then there are several ways you can proceed. The first is to remember how an average is calculated. It is defined as the sum of a range of values divided by the number of items in the range. Thus, you could figure the exclusionary average by simply making sure that the denominator (the number you are dividing by) does not include any zero values. For instance: This approach uses the COUNTIF function to determine the number of cells in the range (A1:A50) that dont contain zero. If this range contains not only zeros but also blank cells, and you dont want the blank cells figured into the result, then you need to use a more complex formula: The COUNTIF function counts cells that do not explicitly evaluate to 0, but it will count blank and text cells. The COUNTBLANK term adjusts for the blank cells and the difference between COUNTA and COUNT adjusts the total count for cells that contain text. Of course you can also use an array formula to do your calculation: Remember that array formulas need to be entered by using the combination CtrlShiftEnter . This array formula also excludes blanks or cells containing text. All in all it is easier to use the AVERAGEIF or AVERAGEIFS functions. When would you not want to use them When you need to share your workbook with users of Excel versions prior to Excel 2007. (This is when the functions were added to Excel.) ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7729) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: An Average that Excludes Zero Values . Create Custom Apps with VBA Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today Leave your own comment: Comments for this tip: Larry K. Binning 28 Aug 2016, 11:39 I have a row of numbers that are added to each week. I need to calculate the average for the last 7 weeks excluding zeros or blanks when they occur and removing the max and min of the 7 numbers calculating the average on the 5 remaining numbers. Is there a convenient way to do this. Michael (Micky) Avidan 11 Jun 2016, 05:45 Wendi, It sounds as if your calculation have changed from Automatic to manual. (Check it according to your Excel version) -------------------------- Michael (Micky) Avidan Microsoft Answers - Wiki author Forums Moderator Microsoft MVP Excel (2009-2016) ISRAEL I am trying to write a formula where you calculate the average, and excluding any blank or zero value cells, then take the average sum and subtract the lower amount from it to the sum total. This is for calculating cost savings. Ive got the average part excluding the zero values (and as per your not above) hold the shiftcntrlenter. BUT whenever I change a value in one of the cells, it does not update the value, I have to go back in and hold shiftcntrlenter again. Help Willy Vanhaelen 24 May 2016, 11:04 Look in Excels Help and you will find: If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored however, cells with the value zero are included. So you have to do nothing special because what you want is simply the default. Sharyn Kingston 23 May 2016, 22:56 Can you please tell me how to do an average that includes zero values, but still excludes cells with no value Michael (Micky) Avidan 04 May 2016, 05:31 SAKILARANI, The AVERAGE Function discards empty cells - so there is no problme for the manager to leave them empty. Take a look at the picture. (see Figure 1 below) -------------------------- Michael (Micky) Avidan Microsoft Answers - Wiki author Forums Moderator Microsoft MVP Excel (2009-2016) ISRAEL SAKILARANI 04 May 2016, 01:16 if column I is an average of the week. If the manager didnt fill out a cell, dont allow that to average in. Just do an average of what was reported. how a pplied the formula veeranjaneyulu 25 Feb 2016, 01:28 Averaging a non-contiguous range: excelribbon. tips. netT007845AveragingaNon-ContiguousRange. html I have a similar situation as Todd seems all the examples show the cells to be averaged in range are in a continuous row. I need to do this function where I need the average of items in about every 3rd cell of a row, and not the whole row. the range includes all cells correct, am I looking at an array Michael (Micky) Avidan 23 Jul 2015, 07:13 Ben, In your example it should read: Michael (Micky) Avidan Microsoft Answers - Wiki author Forums Moderator Microsoft MVP Excel (2009-2015) ISRAEL Michael (Micky) Avidan 23 Jul 2015, 07:12 Ben, This tip was intended for 2003 and eralier versions users. However, starting with Excel 2007 - the following formula will achieve the same task without the need for an ampersand. By the way - the following formula takes into calculation also negative values, therefor I used: ltgt0. Michael (Micky) Avidan Microsoft Answers - Wiki author Forums Moderator Microsoft MVP Excel (2009-2015) ISRAEL I had to make a slight change to get it to work for me (Excel 2013). I wanted it documented in case others have a similar problem: Notice the ampersand in the fourth to last character. Really struggling with a Average Function.. I have a range of cells (M9:M18)that have one of three quantities: 100, 50, 0. These quantities are filled in based on the formula used in the cell to the left of them. I need to use a formula to come up with an average for these cells and have had no luck with all of the threads Ive tried. alec macleod 01 Mar 2015, 10:20 I want to average monetary values on different sheets with in the same work book that do not include zero I am wondering how to average a range of cells that include the SUM formula for a line of numbers, but eliminate the cells that do not have a calculation to the sum formula. There may be a 0 input in one of the cells, so I cant just eliminate 0. Is there a way to eliminate the cells that only have the formula without the actual calculation. The current AverageIf forumla works well for me. But the problem is its counting all the Zeros in my data. Are you able to provide guidance on how i add into this formula the exclusion of Zeros AVERAGEIF(YTD PPV - 410A:A, Aus - YTD StandardB54Aus - YTD StandardB55,YTD PPV - 410P:P) The first formula given: does not do what Veronica wants since there may be negative values in the range. She should instead use: Ricardo A. Reyes 11 Nov 2014, 02:06 this is my first visit in this site and i was looking for formula that meet my demands, with the help of google I found this site. it is very well explained and was very helpful. thanks you so much. keep up the good work. and hope this site last. Very well explained, thank you. Thank you, well explained. First tutorial I found that actually works, went through about 3-4 others. Andr Terra 06 Mar 2013, 16:55 Thanks First hit on google for average excluding ifs ) Our Company Our Products Our Authors
No comments:
Post a Comment