Ajouter, modifier, rechercher ou effacer des formats conditionnels En savoir plus sur la mise en forme conditionnelle La mise en forme conditionnelle vous aide à répondre visuellement à des questions spécifiques sur vos données. Vous pouvez appliquer la mise en forme conditionnelle à une plage de cellules, une table Microsoft Excel ou un rapport de tableau croisé dynamique. Il existe des différences importantes à comprendre lorsque vous utilisez la mise en forme conditionnelle sur un rapport de tableau croisé dynamique. Les avantages de la mise en forme conditionnelle Chaque fois que vous analysez des données, vous vous posez souvent des questions, telles que: Où sont les exceptions dans un résumé des profits au cours des cinq dernières années Quelles sont les tendances dans un sondage marketing au cours des deux dernières années Qui a vendu Plus de 50 000 dollars ce mois-ci Quelle est la répartition par âge globale des employés Quels sont les produits qui ont plus de 10 hausses de revenus d'une année à l'autre Qui sont les étudiants les plus performants et les moins performants de la classe freshman Le formatage conditionnel contribue à répondre à ces questions en facilitant Pour mettre en évidence des cellules ou des plages de cellules intéressantes, mettre l'accent sur des valeurs inhabituelles et visualiser des données en utilisant des barres de données, des échelles de couleurs et des ensembles d'icônes. Un format conditionnel modifie l'apparence d'une plage de cellules en fonction des conditions (ou des critères). Si la condition est vraie, la plage de cellules est formatée en fonction de cette condition si la condition est fausse, la plage de cellules n'est pas formatée en fonction de cette condition. Vous pouvez trier et filtrer par format, y compris la couleur de la cellule et la couleur de la police, que vous ayez formaté manuellement ou conditionnellement les cellules. Remarque: Lorsque vous créez un format conditionnel, vous pouvez référencer uniquement d'autres cellules de la même feuille de calcul ou, dans certains cas, des cellules des feuilles de calcul dans le même classeur ouvert. Vous ne pouvez pas utiliser la mise en forme conditionnelle sur des références externes à un autre classeur. Formatage conditionnel pour un rapport de tableau croisé dynamique La mise en forme conditionnelle dans un rapport de tableau croisé dynamique diffère de mise en forme conditionnelle dans une plage de cellules ou une table Excel de plusieurs façons: Si vous modifiez la disposition du rapport de tableau croisé dynamique en filtrant, en masquant les niveaux, Le format conditionnel est maintenu tant que les champs des données sous-jacentes ne sont pas supprimés. La portée du format conditionnel pour les champs dans la zone Valeurs peut être basée sur la hiérarchie de données et est déterminée par tous les enfants visibles (le niveau inférieur suivant dans une hiérarchie) d'un parent (le niveau supérieur suivant dans une hiérarchie) sur les lignes Pour une ou plusieurs colonnes, ou colonnes pour une ou plusieurs lignes. Remarque: dans la hiérarchie des données, les enfants n'héritent pas de mise en forme conditionnelle à partir du parent et le parent n'hérite pas de mise en forme conditionnelle des enfants. Il existe trois méthodes pour définir le format conditionnel des champs dans la zone Valeurs: par sélection, par champ correspondant et par champ de valeur. La méthode par défaut de la portée des champs dans la zone Valeurs est par sélection. Vous pouvez modifier la méthode de scoping dans le champ correspondant ou le champ de valeur en utilisant le bouton d'option Appliquer la règle de mise en forme à, la boîte de dialogue Nouvelle règle de mise en forme ou la boîte de dialogue Modifier la règle de mise en forme. Les trois méthodes de scoping (onglet Accueil, groupe Styles, commande Conditionnal Formatting) vous offrent une plus grande flexibilité en fonction de vos besoins: Scoping by selection Utilisez cette méthode si vous souhaitez sélectionner: Un ensemble contigu de champs dans la zone Values, Des totaux du produit pour une région. Un ensemble de champs non contigus dans la zone Valeurs, tels que des totaux de produit pour différentes régions entre niveaux dans la hiérarchie de données. Champ Scoping by value Utilisez cette méthode si vous voulez: Eviter de faire de nombreuses sélections non contiguës. Formatez formellement un ensemble de champs dans la zone Valeurs pour tous les niveaux dans la hiérarchie des données. Inclure les sous-totaux et les totaux agrégés. Définition de la portée par champ correspondant Utilisez cette méthode si vous voulez: Éviter de faire de nombreuses sélections non contiguës. Formatez formellement un ensemble de champs dans la zone Valeurs pour un niveau dans la hiérarchie de données. Lorsque vous formatez des champs de format conditionnel dans la zone Valeurs pour les valeurs supérieures, inférieures, supérieures à la moyenne ou inférieures à la moyenne, la règle est basée sur toutes les valeurs visibles par défaut. Toutefois, lorsque vous étendez par champ correspondant, au lieu d'utiliser toutes les valeurs visibles, vous pouvez éventuellement appliquer le format conditionnel pour chaque combinaison de: Une colonne et son champ de ligne parent. Une ligne et son champ de colonne parent. Mise en forme conditionnelle dans les feuilles de calcul Vous pouvez utiliser la mise en forme conditionnelle sur les cellules qui sont référencées dans une autre feuille de calcul dans le même classeur. Cette fonctionnalité n'est pas disponible entre les classeurs. Formater toutes les cellules à l'aide d'une échelle de deux couleurs Les échelles de couleurs sont des guides visuels qui vous aident à comprendre la distribution et la variation des données. Une échelle bicolore vous permet de comparer une gamme de cellules en utilisant une gradation de deux couleurs. L'ombre de la couleur représente des valeurs supérieures ou inférieures. Par exemple, dans une échelle de couleur verte et rouge, vous pouvez spécifier que les cellules de plus grande valeur ont une couleur plus verte et les cellules de valeur inférieure ont une couleur plus rouge. Si une ou plusieurs cellules de la plage contiennent une formule renvoyant une erreur, la mise en forme conditionnelle n'est pas appliquée à l'ensemble de la plage. Pour s'assurer que la mise en forme conditionnelle est appliquée à la plage entière, utilisez une fonction IS ou IFERROR pour renvoyer une valeur autre qu'une valeur d'erreur. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Échelles de couleurs. Sélectionnez une échelle à deux couleurs. Survolez les icônes des couleurs pour voir quelle icône est une échelle à deux couleurs. La couleur supérieure représente des valeurs plus élevées, et la couleur inférieure représente des valeurs inférieures. Vous pouvez modifier la méthode de scoping pour les champs dans la zone Valeurs d'un rapport de tableau croisé dynamique en utilisant le bouton Options de formatage qui apparaît à côté d'un champ de tableau croisé dynamique qui a appliqué la mise en forme conditionnelle. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Gérer les règles. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Procédez de l'une des façons suivantes: Pour ajouter un format conditionnel, cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s'affiche. Pour modifier un format conditionnel, procédez comme suit: Assurez-vous que la feuille de calcul appropriée, la table ou le rapport de tableau croisé dynamique est sélectionné dans la zone de liste Afficher les règles de formatage pour. Si vous le souhaitez, modifiez la plage de cellules en cliquant sur Collapse Dialog dans la zone Applied to pour masquer temporairement la boîte de dialogue en sélectionnant la nouvelle plage de cellules de la feuille de calcul, puis en sélectionnant Expand Dialog. Sélectionnez la règle, puis cliquez sur Modifier la règle. La boîte de dialogue Modifier la règle de formatage s'affiche. Sous Appliquer la règle à. Pour modifier optionnellement la portée des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique en: Sélection, cliquez sur Cellules sélectionnées. Toutes les cellules d'un label Valeur, cliquez sur Toutes les cellules contenant les valeurs labelgt ltValue. Toutes les cellules d'un libellé de valeur, à l'exclusion des sous-totaux et du total général, cliquent sur Toutes les cellules affichant les valeurs de labelgt ltValue pour ltRow Labelgt. Sous Sélectionner un type de règle. Cliquez sur Formater toutes les cellules en fonction de leurs valeurs (par défaut). Sous Modifier la description de la règle. Dans la zone de liste Style de format, sélectionnez Échelle 2 couleurs. Pour sélectionner un type dans la zone Type pour Minimum et Maximum. Effectuez l'une des opérations suivantes: Format des valeurs les plus basses et les plus élevées Sélectionnez Valeur minimale et Valeur la plus élevée. Dans ce cas, vous n'entrez pas de valeur minimale et maximale. Formater un numéro, une date ou une valeur de temps Sélectionnez Numéro. Puis entrez une valeur minimale et maximale. Formater un pourcentage Sélectionnez Pourcentage. Puis entrez une valeur minimale et maximale. Les valeurs valides vont de 0 (zéro) à 100. N'entrez pas de signe de pourcentage. Utilisez un pourcentage lorsque vous voulez visualiser toutes les valeurs proportionnellement car la distribution des valeurs est proportionnelle. Formatez un percentile Select Percentile, puis entrez une valeur minimum et maximum. Les percentiles valides sont de 0 (zéro) à 100. Utilisez un percentile lorsque vous voulez visualiser un groupe de valeurs élevées (comme le 20e percentile supérieur) dans une proportion de grade de couleur et des valeurs basses (comme le 20e percentile inférieur) dans une autre couleur Parce qu'ils représentent des valeurs extrêmes qui pourraient fausser la visualisation de vos données. Formater un résultat de formule Sélectionnez Formule. Puis entrez les valeurs pour Minimum et Maximum. La formule doit renvoyer un nombre, une date ou une valeur temporelle. Démarrez la formule avec un signe égal (). Les formules non valides entraînent l'absence de formatage. C'est une bonne idée de tester la formule pour s'assurer qu'elle ne renvoie pas une valeur d'erreur. Assurez-vous que la valeur Minimum est inférieure à la valeur Maximum. Vous pouvez choisir un type différent pour le minimum et le maximum. Par exemple, vous pouvez choisir un nombre pour Minimum un pourcentage pour Maximum. Pour choisir une échelle de couleurs Minimum et Maximum, cliquez sur Couleur pour chacune d'entre elles, puis sélectionnez une couleur. Si vous souhaitez choisir d'autres couleurs ou créer une couleur personnalisée, cliquez sur Autres couleurs. L'échelle de couleurs sélectionnée s'affiche dans la zone Aperçu. Formater toutes les cellules à l'aide d'une échelle de trois couleurs Les échelles de couleurs sont des guides visuels qui vous aident à comprendre la distribution et la variation des données. Une échelle à trois couleurs vous permet de comparer une gamme de cellules en utilisant une gradation de trois couleurs. L'ombre de la couleur représente des valeurs supérieures, moyennes ou inférieures. Par exemple, dans une échelle de couleur verte, jaune et rouge, vous pouvez spécifier que les cellules de valeur supérieure ont une couleur verte, les cellules de valeur moyenne ont une couleur jaune et les cellules de valeur inférieure ont une couleur rouge. Si une ou plusieurs cellules de la plage contiennent une formule renvoyant une erreur, la mise en forme conditionnelle n'est pas appliquée à l'ensemble de la plage. Pour s'assurer que la mise en forme conditionnelle est appliquée à la plage entière, utilisez une fonction IS ou IFERROR pour renvoyer une valeur que vous spécifiez (par exemple 0 ou NA) au lieu d'une valeur d'erreur. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Échelles de couleurs. Sélectionnez une échelle à trois couleurs. La couleur supérieure représente des valeurs plus élevées, la couleur centrale représente des valeurs moyennes et la couleur inférieure représente des valeurs inférieures. Survolez les icônes des couleurs pour voir quelle icône est une échelle à trois couleurs. Vous pouvez modifier la méthode de la portée pour les champs dans la zone Valeurs d'un rapport de tableau croisé dynamique à l'aide du bouton Options de formatage qui apparaît à côté d'un champ de tableau croisé dynamique qui a mise en forme conditionnelle appliquée. Sélectionnez une ou plusieurs cellules dans une plage, tableau ou tableau croisé dynamique rapport. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Gérer les règles. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Effectuez l'une des opérations suivantes: Pour ajouter un format conditionnel, cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s'affiche. Pour modifier un format conditionnel, procédez comme suit: Assurez-vous que la feuille de calcul appropriée, la table ou le rapport de tableau croisé dynamique est sélectionné dans la zone de liste Afficher les règles de formatage pour. Si vous le souhaitez, modifiez la plage de cellules en cliquant sur Collapse Dialog dans la zone Applied to pour masquer temporairement la boîte de dialogue en sélectionnant la nouvelle plage de cellules de la feuille de calcul, puis en sélectionnant Expand Dialog. Sélectionnez la règle, puis cliquez sur Modifier la règle. La boîte de dialogue Modifier la règle de formatage s'affiche. Sous Appliquer la règle à. Pour modifier éventuellement la portée des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique en: Sélection, cliquez sur Ces cellules. Dans le champ Correspondant, cliquez sur Toutes les cellules ltvalue avec les mêmes champs. Dans le champ Valeur, cliquez sur Toutes les cellules de champ de ltvalue. Sous Sélectionner un type de règle. Cliquez sur Formater toutes les cellules en fonction de leurs valeurs. Sous Modifier la description de la règle. Dans la zone de liste Style de format, sélectionnez Échelle 3 couleurs. Sélectionnez un type pour Minimum. Point milieu. Et Maximum. Effectuez l'une des opérations suivantes: Format des valeurs les plus basses et les plus élevées Sélectionnez un point milieu. Dans ce cas, vous n'entrez pas la valeur la plus basse et la plus haute. Formater un numéro, une date ou une valeur de temps Sélectionnez Numéro. Puis saisissez une valeur pour Minimum. Point milieu. Et Maximum. Formater un pourcentage Sélectionnez Pourcentage. Puis saisissez une valeur pour Minimum. Point milieu. Et Maximum. Les valeurs valides sont de 0 (zéro) à 100. N'entrez pas de signe% (). Utilisez un pourcentage lorsque vous souhaitez visualiser toutes les valeurs proportionnellement, car l'utilisation d'un pourcentage garantit que la distribution des valeurs est proportionnelle. Formatez un percentile Select Percentile, puis entrez une valeur pour Minimum. Point milieu. Et Maximum. Les percentiles valides sont de 0 (zéro) à 100. Utilisez un percentile lorsque vous voulez visualiser un groupe de valeurs élevées (comme le 20e centile supérieur) dans une proportion de grade de couleur et des valeurs basses (comme le 20e percentile inférieur) dans un autre Couleur, car ils représentent des valeurs extrêmes qui pourraient fausser la visualisation de vos données. Formater un résultat de formule Sélectionnez Formule. Puis saisissez une valeur pour Minimum. Point milieu. Et Maximum. La formule doit renvoyer un nombre, une date ou une valeur temporelle. Démarrez la formule avec un signe égal (). Les formules non valides entraînent l'absence de formatage. C'est une bonne idée de tester la formule pour s'assurer qu'elle ne renvoie pas une valeur d'erreur. Vous pouvez définir des valeurs minimales, intermédiaires et maximales pour la plage de cellules. Assurez-vous que la valeur dans Minimum est inférieure à la valeur dans le point milieu. Qui à son tour est inférieur à la valeur en Maximum. Vous pouvez choisir un type différent pour Minimum. Point milieu. Et Maximum. Par exemple, vous pouvez choisir un nombre minimum, un centile intermédiaire et un pourcentage maximum. Dans de nombreux cas, la valeur par défaut de 50 pour cent du point milieu est la meilleure, mais vous pouvez ajuster cette valeur pour qu'elle corresponde à des exigences uniques. Pour choisir un minimum. Point milieu. Et l'échelle de couleurs maximale, cliquez sur Couleur pour chacun, puis sélectionnez une couleur. Si vous souhaitez choisir d'autres couleurs ou créer une couleur personnalisée, cliquez sur Autres couleurs. L'échelle de couleurs sélectionnée s'affiche dans la zone Aperçu. Formater toutes les cellules à l'aide de barres de données Une barre de données vous permet de voir la valeur d'une cellule par rapport à d'autres cellules. La longueur de la barre de données représente la valeur dans la cellule. Une barre plus longue représente une valeur plus élevée, et une barre plus courte représente une valeur inférieure. Barres de données sont utiles pour repérer les nombres plus élevés et plus bas, en particulier avec de grandes quantités de données, tels que la vente de haut et les jouets de vente au bas dans un rapport de ventes de vacances. Astuce: Si une ou plusieurs cellules de la plage contiennent une formule renvoyant une erreur, la mise en forme conditionnelle n'est pas appliquée à l'ensemble de la plage. Pour s'assurer que la mise en forme conditionnelle est appliquée à la plage entière, utilisez une fonction IS ou IFERROR pour renvoyer une valeur (par exemple 0 ou NA) au lieu d'une valeur d'erreur. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Style, cliquez sur la flèche à côté de Mise en forme conditionnelle. Cliquez sur Barres de données. Puis sélectionnez une icône de barre de données. Vous pouvez modifier la méthode d'exploration des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique à l'aide de la commande Appliquer la règle de formatage à l'option. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Gérer les règles. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Effectuez l'une des opérations suivantes: Pour ajouter un format conditionnel, cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s'affiche. Pour modifier un format conditionnel, procédez comme suit: Assurez-vous que la feuille de calcul appropriée, la table ou le rapport de tableau croisé dynamique est sélectionné dans la zone de liste Afficher les règles de formatage pour. Si vous le souhaitez, modifiez la plage de cellules en cliquant sur Collapse Dialog dans la zone Applied to pour masquer temporairement la boîte de dialogue en sélectionnant la nouvelle plage de cellules de la feuille de calcul, puis en sélectionnant Expand Dialog. Sélectionnez la règle, puis cliquez sur Modifier la règle. La boîte de dialogue Modifier la règle de formatage s'affiche. Sous Appliquer la règle à. Pour modifier éventuellement la portée des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique en: Sélection, cliquez sur Ces cellules. Dans le champ Correspondant, cliquez sur Toutes les cellules ltvalue avec les mêmes champs. Dans le champ Valeur, cliquez sur Toutes les cellules de champ de ltvalue. Sous Sélectionner un type de règle. Cliquez sur Formater toutes les cellules en fonction de leurs valeurs. Sous Modifier la description de la règle. Dans la zone de liste Style de format, sélectionnez Barre de données. Sélectionnez un type minimum et maximum. Effectuez l'une des opérations suivantes: Formatage des valeurs minimale et maximale Sélectionnez Valeur minimale et Valeur la plus élevée. Dans ce cas, vous n'entrez pas de valeur pour Minimum et Maximum. Formater un numéro, une date ou une valeur de temps Sélectionnez Numéro. Puis entrez une valeur minimale et maximale. Formater un pourcentage Sélectionnez Pourcentage. Puis saisissez une valeur pour Minimum et Maximum. Les valeurs valides sont de 0 (zéro) à 100. N'entrez pas de signe% (). Utilisez un pourcentage lorsque vous souhaitez visualiser toutes les valeurs proportionnellement, car l'utilisation d'un pourcentage garantit que la distribution des valeurs est proportionnelle. Formatez un percentile Select Percentile, puis saisissez une valeur pour Minimum et Maximum. Les percentiles valides sont de 0 (zéro) à 100. Utilisez un percentile lorsque vous voulez visualiser un groupe de valeurs élevées (comme le 20e percentile) dans une proportion de la barre de données et des valeurs basses (comme le 20e percentile inférieur) dans un autre Car elles représentent des valeurs extrêmes qui pourraient fausser la visualisation de vos données. Formater un résultat de formule Sélectionnez Formule. Puis saisissez une valeur pour Minimum et Maximum. La formule doit renvoyer un nombre, une date ou une valeur temporelle. Démarrez la formule avec un signe égal (). Les formules non valides entraînent l'absence de formatage. C'est une bonne idée de tester la formule pour s'assurer qu'elle ne renvoie pas une valeur d'erreur. Assurez-vous que la valeur Minimum est inférieure à la valeur Maximum. Vous pouvez choisir un type différent pour Minimum et Maximum. Par exemple, vous pouvez choisir un nombre minimum et un pourcentage maximum. Pour choisir une échelle de couleurs Minimum et Maximum, cliquez sur Couleur de la barre. Si vous souhaitez choisir d'autres couleurs ou créer une couleur personnalisée, cliquez sur Autres couleurs. La couleur de la barre que vous sélectionnez est affichée dans la boîte Aperçu. Pour afficher uniquement la barre de données et non la valeur dans la cellule, sélectionnez Afficher uniquement la barre. Pour appliquer une bordure solide aux barres de données, sélectionnez Bordure solide dans la zone de liste Bordure et choisissez une couleur pour la bordure. Pour choisir entre une barre pleine et une barre graduée, choisissez Remplissage solide ou Remplissage gradient dans la zone de liste Remplir. Pour formater les barres négatives, cliquez sur Valeur négative et Axe, puis dans la boîte de dialogue Valeur négative et Paramètres de l'axe, choisissez les options pour les couleurs de bordure et de remplissage de barres négatives. Vous pouvez choisir les paramètres de position et une couleur pour l'axe. Lorsque vous avez terminé de sélectionner les options, cliquez sur OK. Vous pouvez modifier la direction des barres en choisissant un réglage dans la zone de liste Bar direction. Il est défini par défaut sur Contexte, mais vous pouvez choisir entre une direction de gauche à droite et une direction de droite à gauche, selon la façon dont vous souhaitez présenter vos données. Formater toutes les cellules à l'aide d'un ensemble d'icônes Utilisez une icône pour annoter et classer les données en trois à cinq catégories séparées par une valeur de seuil. Chaque icône représente une plage de valeurs. Par exemple, dans l'ensemble d'icônes des 3 flèches, la flèche vers le haut verte représente les valeurs supérieures, la flèche latérale jaune représente les valeurs moyennes et la flèche vers le bas rouge représente les valeurs inférieures. Vous pouvez choisir d'afficher des icônes uniquement pour les cellules qui répondent à une condition, par exemple, afficher une icône d'avertissement pour les cellules qui tombent en dessous d'une valeur critique et pas d'icônes pour ceux qui le dépassent. Pour ce faire, vous masquez les icônes en sélectionnant Aucune icône de cellule dans la liste déroulante icône située à côté de l'icône lorsque vous définissez des conditions. Vous pouvez également créer votre propre combinaison d'ensembles d'icônes par exemple, une coche verte de symbole, un feu jaune et un drapeau rouge. Problème: la mise en forme conditionnelle n'apparaît pas pour une cellule de la plage Si une ou plusieurs cellules de la plage contiennent une formule renvoyant une erreur, la mise en forme conditionnelle n'est pas appliquée à l'ensemble de la plage. Pour s'assurer que la mise en forme conditionnelle est appliquée à la plage entière, utilisez une fonction IS ou IFERROR pour renvoyer une valeur (par exemple 0 ou NA) au lieu d'une valeur d'erreur. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Style, cliquez sur la flèche à côté de Mise en forme conditionnelle. Cliquez sur Jeu d'icônes. Puis sélectionnez un ensemble d'icônes. Vous pouvez modifier la méthode d'exploration des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique à l'aide de la commande Appliquer la règle de formatage à l'option. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Gérer les règles. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Effectuez l'une des opérations suivantes: Pour ajouter un format conditionnel, cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s'affiche. Pour modifier un format conditionnel, procédez comme suit: Assurez-vous que la feuille de calcul appropriée, la table ou le rapport de tableau croisé dynamique est sélectionné dans la zone de liste Afficher les règles de formatage pour. Si vous le souhaitez, modifiez la plage de cellules en cliquant sur Collapse Dialog dans la zone Applied to pour masquer temporairement la boîte de dialogue en sélectionnant la nouvelle plage de cellules de la feuille de calcul, puis en sélectionnant Expand Dialog. Sélectionnez la règle, puis cliquez sur Modifier la règle. La boîte de dialogue Modifier la règle de formatage s'affiche. Sous Appliquer la règle à. Pour modifier éventuellement la portée des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique en: Sélection, cliquez sur Ces cellules. Dans le champ Correspondant, cliquez sur Toutes les cellules ltvalue avec les mêmes champs. Dans le champ Valeur, cliquez sur Toutes les cellules de champ de ltvalue. Sous Sélectionner un type de règle. Cliquez sur Formater toutes les cellules en fonction de leurs valeurs. Sous Modifier la description de la règle. Dans la zone de liste Style de format, sélectionnez Ensemble d'icônes. Sélectionnez un jeu d'icônes. La valeur par défaut est 3 feux de signalisation (Unrimmed). Le nombre d'icônes et les opérateurs de comparaison par défaut et les valeurs de seuil pour chaque icône peuvent varier pour chaque ensemble d'icônes. Si vous le souhaitez, vous pouvez ajuster les opérateurs de comparaison et les valeurs de seuil. La plage de valeurs par défaut pour chaque icône est de taille égale, mais vous pouvez les ajuster à vos besoins uniques. Assurez-vous que les seuils sont dans une séquence logique de plus haut au plus bas de haut en bas. Effectuez l'une des opérations suivantes: Formater un numéro, une date ou une valeur de temps Sélectionnez Numéro. Formater un pourcentage Sélectionnez Pourcentage. Les valeurs valides sont de 0 (zéro) à 100. N'entrez pas de signe% (). Utilisez un pourcentage lorsque vous souhaitez visualiser toutes les valeurs proportionnellement, car l'utilisation d'un pourcentage garantit que la distribution des valeurs est proportionnelle. Formater un percentile Sélectionner le percentile. Les percentiles valides sont de 0 (zéro) à 100. Utilisez un percentile lorsque vous souhaitez visualiser un groupe de valeurs élevées (comme le 20e percentile) en utilisant une icône particulière et des valeurs basses (comme le 20e percentile inférieur) en utilisant une autre icône , Car ils représentent des valeurs extrêmes qui pourraient fausser la visualisation de vos données. Formater un résultat de formule Sélectionnez Formule. Puis entrez une formule dans chaque zone Valeur. La formule doit renvoyer un nombre, une date ou une valeur temporelle. Démarrez la formule avec un signe égal (). Les formules non valides entraînent l'absence de formatage. C'est une bonne idée de tester la formule pour s'assurer qu'elle ne renvoie pas une valeur d'erreur. Pour que la première icône représente des valeurs inférieures et que la dernière icône représente des valeurs supérieures, sélectionnez Inverser l'ordre des icônes. Pour afficher uniquement l'icône et non la valeur dans la cellule, sélectionnez Afficher uniquement l'icône. Vous devrez peut-être ajuster la largeur de colonne pour accueillir l'icône. Il existe trois formats d'icônes. La taille de l'icône affichée dépend de la taille de police utilisée dans cette cellule. Formater uniquement des cellules contenant des valeurs de texte, de nombre ou de date ou de temps Pour trouver plus facilement des cellules spécifiques dans une plage de cellules, vous pouvez formater ces cellules spécifiques en fonction d'un opérateur de comparaison. Par exemple, dans une feuille de calcul inventoriée par catégories, vous pouvez mettre en surbrillance les produits contenant moins de 10 éléments en jaune. Ou, dans une feuille de travail récapitulative d'un magasin de détail, vous pouvez identifier tous les magasins avec des bénéfices supérieurs à 10, des volumes de vente inférieurs à 100 000 et une région égale à SouthEast. Remarque: Vous ne pouvez pas mettre en forme conditionnellement des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique par texte ou date, uniquement par numéro. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Style, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Mettre en surbrillance les règles de cellules. Sélectionnez la commande souhaitée, par exemple Entre. Égal au texte qui contient. Ou une date qui se produit. Saisissez les valeurs que vous souhaitez utiliser, puis sélectionnez un format. Vous pouvez modifier la méthode d'exploration des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique à l'aide de la commande Appliquer la règle de formatage à l'option. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Gérer les règles. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Effectuez l'une des opérations suivantes: Pour ajouter un format conditionnel, cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s'affiche. Pour modifier un format conditionnel, procédez comme suit: Assurez-vous que la feuille de calcul appropriée, la table ou le rapport de tableau croisé dynamique est sélectionné dans la zone de liste Afficher les règles de formatage pour. Si vous le souhaitez, modifiez la plage de cellules en cliquant sur Collapse Dialog dans la zone Applied to pour masquer temporairement la boîte de dialogue, en sélectionnant la nouvelle plage de cellules sur la feuille de calcul ou sur d'autres feuilles de calcul, puis en sélectionnant Expand Dialog. Sélectionnez la règle, puis cliquez sur Modifier la règle. La boîte de dialogue Modifier la règle de formatage s'affiche. Sous Appliquer la règle à. Pour modifier éventuellement la portée des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique en: Sélection, cliquez sur Ces cellules. Dans le champ Correspondant, cliquez sur Toutes les cellules ltvalue avec les mêmes champs. Dans le champ Valeur, cliquez sur Toutes les cellules de champ de ltvalue. Sous Sélectionner un type de règle. Cliquez sur Format uniquement les cellules qui contiennent. Sous Modifier la description de la règle. Dans la zone de liste Formater uniquement des cellules avec, effectuez l'une des opérations suivantes: Format par numéro, date ou heure Sélectionnez Valeur de la cellule. Sélectionnez un opérateur de comparaison, puis entrez un numéro, une date ou une heure. Par exemple, sélectionnez Entre, puis entrez 100 et 200. Ou sélectionnez Equal to et entrez 112009. Vous pouvez également saisir une formule qui renvoie un nombre, une date ou une valeur temporelle. Si vous entrez une formule, commencez-la par un signe égal (). Les formules non valides entraînent l'absence de formatage. C'est une bonne idée de tester la formule pour s'assurer qu'elle ne renvoie pas une valeur d'erreur. Format par texte Sélectionnez le texte spécifique. Choisissez un opérateur de comparaison, puis entrez le texte. Par exemple, sélectionnez Contient, puis entrez Argent. Ou sélectionnez Démarrer, puis Entrée Tri. Les guillemets sont inclus dans la chaîne de recherche et vous pouvez utiliser des caractères génériques. La longueur maximale d'une chaîne est de 255 caractères. Vous pouvez également saisir une formule qui renvoie du texte. Si vous entrez une formule, commencez-la par un signe égal (). Les formules non valides entraînent l'absence de formatage. C'est une bonne idée de tester la formule pour s'assurer qu'elle ne renvoie pas une valeur d'erreur. Format par date Sélectionner les dates Occurring. Puis sélectionnez une comparaison de date. Par exemple, sélectionnez Yesterday ou Next week. Formater des cellules avec des blancs ou pas de blancs Sélectionnez Blanks ou No Blanks. Une valeur vide est une cellule qui ne contient pas de données et est différente d'une cellule qui contient un ou plusieurs espaces (les espaces sont considérés comme du texte). Formater des cellules avec erreur ou pas de valeurs d'erreur Sélectionnez Erreurs ou Aucune erreur. Les valeurs d'erreur comprennent:, VALUE. DIV0. PRÉNOM. NA, REF. NUM. Et NULL. Pour spécifier un format, cliquez sur Format. La boîte de dialogue Format des cellules s'affiche. Sélectionnez le numéro, la police, la bordure ou le format de remplissage que vous souhaitez appliquer lorsque la valeur de la cellule répond à la condition, puis cliquez sur OK. Vous pouvez choisir plusieurs formats. Les formats que vous sélectionnez sont affichés dans la zone Aperçu. Formater uniquement les valeurs classées en haut ou en bas Vous pouvez trouver les valeurs supérieures et inférieures dans une plage de cellules qui sont basées sur une valeur de coupure que vous spécifiez. Par exemple, vous pouvez trouver les 5 produits les plus vendus dans un rapport régional, les 15 premiers produits dans un sondage auprès des clients ou les 25 principaux salaires dans une analyse du personnel du ministère. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Style, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur TopBottom Rules. Sélectionnez la commande souhaitée, par exemple, les 10 premiers éléments ou le bas 10. Saisissez les valeurs que vous souhaitez utiliser, puis sélectionnez un format. Vous pouvez modifier la méthode d'exploration des champs dans la zone Valeurs d'un rapport de tableau croisé dynamique à l'aide de la commande Appliquer la règle de formatage à l'option. Sélectionnez une ou plusieurs cellules dans une plage, une table ou un rapport de tableau croisé dynamique. Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche à côté de Mise en forme conditionnelle. Puis cliquez sur Gérer les règles. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Effectuez l'une des opérations suivantes: Pour ajouter un format conditionnel, cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s'affiche. Pour modifier un format conditionnel, procédez comme suit: Assurez-vous que la feuille de calcul appropriée, la table ou le rapport de tableau croisé dynamique est sélectionné dans la zone de liste Afficher les règles de formatage pour. Si vous le souhaitez, modifiez la plage de cellules en cliquant sur Collapse Dialog dans la zone Applied to pour masquer temporairement la boîte de dialogue en sélectionnant la nouvelle plage de cellules de la feuille de calcul, puis en sélectionnant Expand Dialog. Sélectionnez la règle, puis cliquez sur Modifier la règle. La boîte de dialogue Modifier la règle de formatage s'affiche. Sous Appliquer la règle à. Pour modifier éventuellement les champs d'étendue dans la zone Valeurs d'un rapport de tableau croisé dynamique en: Sélection, cliquez sur Juste ces cellules. Dans le champ Correspondant, cliquez sur Toutes les cellules ltvalue avec les mêmes champs. Dans le champ Valeur, cliquez sur Toutes les cellules de champ de ltvalue. Sous Sélectionner un type de règle. Cliquez sur Formater uniquement les valeurs classées en haut ou en bas. Sous Modifier la description de la règle. Dans les valeurs Format qui se rangent dans la zone de liste, sélectionnez Haut ou Bas. Effectuez l'une des opérations suivantes: Pour spécifier un numéro supérieur ou inférieur, entrez un nombre et désactivez la case à cocher de la plage sélectionnée. Les valeurs valides sont de 1 à 1000. Pour spécifier un pourcentage supérieur ou inférieur, entrez un nombre et cochez la case de la plage sélectionnée. Les valeurs valides sont 1 à 100. En option, modifiez la façon dont le format est appliqué pour les champs de la zone Valeurs d'un rapport de tableau croisé dynamique qui sont définis par champ correspondant. Par défaut, le format conditionnel est basé sur toutes les valeurs visibles. Toutefois, lorsque vous définissez la portée par le champ correspondant, au lieu d'utiliser toutes les valeurs visibles, vous pouvez appliquer le format conditionnel pour chaque combinaison de: Une colonne et son champ de ligne parent, en sélectionnant chaque groupe de colonnes. Une ligne et son champ de colonne parent, en sélectionnant chaque groupe de lignes. Pour spécifier un format, cliquez sur Format. La boîte de dialogue Format des cellules s'affiche. Sélectionnez le numéro, la police, la bordure ou le format de remplissage que vous souhaitez appliquer lorsque la valeur de la cellule répond à la condition, puis cliquez sur OK. Vous pouvez choisir plusieurs formats. Les formats que vous sélectionnez sont affichés dans la zone Aperçu. 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