Mise en forme automatique Excel au fil de la saisie

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Mise en forme automatique de la saisie

La mise en forme d'un tableau Excel ne doit jamais être négligée. C'est elle qui rend la lisibilité et facilite l'interprétation des données.



Mais lorsque celle-ci peut intervenir automatiquement par reconnaissance dynamique des informations, il s'agit d'un gain de temps fort appréciable. Dans l'exemple illustré sur la capture ci-dessus, les titres sont automatiquement formatés avec un remplissage, des attributs de police et de bordure. Les nombres sont traités comme tels. Enfin, les résultats de calculs sont mis en avant.

Source et présentation du concept
Une fois n'est pas coutume, pour aboutir la solution, nous proposons de débuter les travaux à partir d'un classeur très simple offrant certes quelques réglages mais dénué de données.

• Télécharger le classeur mise-en-forme-automatique-saisie.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons effectivement un classeur doté d'une feuille unique, vierge qui plus est. Néanmoins, elle offre quelques attributs de format prédéfinis, car communs à toutes les cellules.

• Sélectionner par exemple la cellule B2,
• Saisir un texte puis le valider par le raccourci clavier CTRL + Entrée,

Grâce à cette combinaison de touches, nous conservons la cellule active. De fait, nous pouvons consulter les attributs enclenchés depuis le ruban Accueil notamment.

Tout d'abord le texte est aligné à gauche comme le proposent les attributs par défaut. Sa couleur est un gris foncé et non le noir. Un retrait est appliqué dans la cellule pour espacer l'information du bord gauche de la case. De même, la donnée est centrée dans la hauteur de la cellule. En effet, les cases de cette feuille ont été agrandies dans les deux directions.

La police quant à elle n'a pas été changée. Il s'agit du Calibri en taille 11 pt.

Formater un texte à la saisie
Comme vous le savez, les règles de mise en forme conditionnelle permettent de faire réagir les cellules dynamiquement en fonction de leur contenu. Et ces règles peuvent s'empiler. La dernière posée est prioritaire. Nous allons donc devoir respecter une certaine chronologie. Nous devons en effet étudier le cas d'un texte standard, d'un titre, d'une donnée numérique ou encore d'un résultat de calcul.

Une saisie standard concerne n'importe quelle cellule. Nous proposons de lui attribuer automatiquement une bordure d'encadrement. Par empilement, lorsqu'il s'agira d'un autre type de données, les nouveaux réglages prendront le pas sur les anciens. La mise en forme conditionnelle doit simplement détecter que la cellule n'est pas vide.

• Cliquer à l'intersection des étiquettes 1 et A en haut à gauche de la feuille,

Cette action a pour effet de sélectionner toutes les cellules en considérant que la cellule A1 est la première d'entre elles. Cette remarque est importante. Dans l'analyse de la règle à construire, le critère doit être vérifié en priorité sur cette dernière et puis sur les autres en respectant la chronologie.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Saisir alors la syntaxe suivante : =A1<>'',

L'inégalité ainsi posée consiste à vérifier que la cellule n'est pas vide. La cellule A1 doit absolument être défigée. Aucun dollar ne doit préfixer ses coordonnées. Ainsi, la mise en forme conditionnelle pourra vérifier cet état sur toutes les cellules de la feuille. Elles sont en effet globalement sélectionnées au moment de la construction de la règle.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Bordure,
• Dans la zone Styles, choisir le dernier trait soit le trait continu,
• Avec la liste des couleurs, choisir un gris foncé,
• Dans la zone Présélections, cliquer sur le bouton Contour,

De cette manière, l'encadrement est appliqué sur chaque bord de la cellule.

• Cliquer sur le bouton Ok pour appliquer ces attributs conditionnels de format,

Nous sommes de retour sur la première boîte de dialogue. Elle illustre le fait qu'une bordure doit automatiquement être appliquée à toute cellule non vide.

• Valider la création de cette règle en cliquant sur le bouton Ok de la boîte de dialogue,

De retour sur la feuille, vous remarquez que la précédente saisie est automatiquement encadrée.



Si vous réalisez de nouvelles saisies dans n'importe quelle cellule, vous constatez que la bordure s'inscrit automatiquement à validation de la frappe.

Mise en forme automatique des titres
Concernant la mise en forme automatique des titres, plusieurs contextes s'annoncent.

S'il s'agit du premier titre du tableau, il doit posséder une bordure sauf à droite, avec un remplissage et une police adaptés. Il est décelé lorsque la cellule du dessus et la cellule sur sa gauche sont vides, tandis que la cellule active et celle sur sa droite sont remplies.

S'il s'agit d'un titre du milieu, il doit posséder une bordure supérieure et inférieure seulement. Les attributs de remplissage et de police doivent être identiques aux précédents. Il est démasqué lorsque la cellule du dessus est vide tandis que les cellules à gauche, à droite et active sont remplies.

S'il s'agit du dernier titre du tableau, il doit posséder une bordure sauf à gauche avec des attributs de fond et de texte identiques aux précédents. Il est décelé lorsque les cellules du dessus et à droite sont vides. Dans le même temps la cellule active et la cellule sur sa gauche doivent être remplies.

S'il s'agit d'un titre isolé, il doit posséder une bordure intégrale avec les mêmes attributs de format que les autres. Il est détecté lorsque sa cellule n'est pas vide tandis que toutes celles qui l'entourent le sont.

Vous l'avez compris, quatre règles de mise en forme conditionnelle sont nécessaires. Et pour recouper tous les critères, la fonction ET est indispensables. Par contre, une procédure particulière doit être respectée. Comme l'analyse doit être réalisée sur les cellules de la ligne du dessus et de la colonne en amont, la règle doit débuter son implication à partir de la cellule B2. La première ligne et la première colonne sont donc considérées comme neutres et inopérantes.

• Cliquer dans la zone Nom en haut à gauche de la feuille,
• Saisir B2:Z1000 et valider par la touche Entrée,

Il ne s'agit pas d'attribuer un nom à une plage mais bien de la sélectionner. Avec cette borne inférieure arbitraire, nous prévoyons suffisamment de marge pour impacter l'ensemble des travaux sur une feuille. Et grâce à cette technique, la première case considérée est bien la première de ces cellules, soit la cellule B2.



Le reste de la procédure est similaire à la précédente. Bien sûr, il convient d'adapter les critères de la règle.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie juste en dessous,
• Saisir alors la syntaxe suivante : =ET(B1=''; A2=''; B2<>''),

Nous cherchons à déceler la présence d'un premier titre. En débutant le raisonnement chronologique à partir de la cellule B2, nous cherchons à savoir si la cellule du dessus (B1) et la cellule sur sa gauche (A2) sont vides quand dans le même temps, son propre contenu existe (B2<>''). Dans ces conditions, la mise en forme doit être adaptée telle que nous l'avons décrit précédemment.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un bleu pâle dans la palette de couleurs,
• Activer ensuite l'onglet Bordure,
• Choisir un style de trait continu avec une couleur bleu foncé,
• Dans la zone Présélections, cliquer sur le bouton Contour,
• Puis, cliquer sur la bordure de droite dans l'aperçu pour la supprimer,

De cette manière, nous assurons la continuité avec le titre susceptible d'intervenir sur sa droite.

• Activer l'onglet Police de la boîte de dialogue,
• Choisir un style gras avec un texte bleu foncé,
• Valider ces attributs de format avec le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle indique sans équivoque la mise en forme dynamique à appliquer à un premier titre détecté.

• Valider la création de la règle de mise en forme conditionnelle par le bouton Ok,

De retour sur la feuille, vous remarquez que les textes isolés sont automatiquement considérés comme des premiers titres de tableau.



Mais si vous saisissez à droite et en dessous, vous remarquez que les données ne sont pas considérées de la même façon. Notre règle semble donc parfaitement fonctionner pour la détection du premier type de titre.

• Sur la plage de cellules B2:Z1000 présélectionnée, créer les trois nouvelles règles suivantes :

=ET(B1=''; A2=''; C2=''; B2<>'')
=ET(B1=''; A2<>''; C2<>''; B2<>'')
=ET(B1=''; A2<>''; C2=''; B2<>'')

Dans les trois cas, les attributs de remplissage et de texte sont identiques à la précédente règle. Les bordures doivent être adaptées quant à elles.

La deuxième règle concerne le titre isolé. Elle doit donc proposer une bordure d'encadrement complète. La troisième règle fait référence au titre du milieu. Elle doit seulement offrir une bordure supérieure et une bordure inférieure. Les bordures latérales doivent disparaître pour assurer la continuité de l'information. La quatrième et dernière règle fait référence au dernier titre, celui situé le plus à droite. Il doit donc offrir une bordure supérieure, une bordure inférieure ainsi qu'une bordure latérale droite. La bordure latérale gauche doit être éliminée.



Après quelques essais, vous remarquez que tous les titres, malgré leurs spécificités, sont parfaitement et automatiquement mis en forme.

Mise en forme automatique des nombres
Pour les valeurs numériques, nous souhaitons simplement et logiquement appliquer un alignement à droite tout en conservant le retrait dans la cellule. Et malgré cette simplicité apparente, la réalisation de cette règle n'est pas si simple que cela. Vous l'avez sans doute déjà constaté, parmi les attributs conditionnels qu'il est possible de régler, l'alignement est absent. Or, il est prédéfini sur la gauche pour l'ensemble des cellules de la feuille. Mais nous allons le voir, c'est une astuce de format personnalisé qui va nous permettre d'atteindre l'objectif.

• Toujours sur la plage de cellule B2:Z1000, créer la nouvelle règle suivante : =ESTNUM(B2),

Nous exploitons simplement la fonction Excel logique EstNum sur la cellule B2 totalement défigée. Elles sont ainsi toutes évaluées dans l'enchaînement. Si le test de cette fonction réussit, il indique que le contenu de la cellule en cours d'étude est bien numérique.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Nombre,
• Dans la liste de gauche, sélectionner la catégorie Personnalisée,
• Dans la zone Type, saisir le code suivant : * 0'   ',

Attention, l'espace après l'étoile est impératif. De même, il doit y avoir trois espaces consécutifs dans les guillemets. Le chiffre 0 est le format standard pour les nombres. Le symbole de l'étoile en préfixe, suivi d'un espace, repousse le contenu de la cellule sur sa droite. Les espaces entre les guillemets permettent de conserver un retrait dans la cellule, pour observer un décalage par rapport à la bordure droite. Pour autoriser les décimales tout en conservant l'alignement, il s'agit d'adapter le format personnalisé comme suit : * 0,00'   '.

• Valider cet attribut de format personnalisé par le bouton Ok,
• Puis, valider la création de la règle de mise en forme conditionnelle,
• De retour sur la feuille, saisir quelques nombres,

Comme vous pouvez le voir, nous avons parfaitement réussi à forcer l'alignement des nombres sur la droite. Et comme l'astuce a consisté à utiliser un format, les données sont toujours considérées comme des valeurs numériques. Nous le constaterons avec les calculs.

Mise en forme automatique des calculs
Les résultats de calculs revêtent forcément une importance supérieure aux données standard. Il convient donc de les faire ressortir en conséquence.

• Sur la plage de cellules B2:Z1000, créer la nouvelle règle suivante :=ESTFORMULE(B2),

Nous exploitons donc la fonction logique EstFormule sur la cellule B2 complètement défigée pour les analyser toutes. Si son test est vérifié, elle indique que le contenu de la cellule est le résultat d'une formule.

• Associer un texte gras et bleu foncé à cette règle de mise en forme conditionnelle,

En réalisant la simulation d'une facturation avec les calculs adaptés, vous constatez que la mise en forme du tableau complet, s'adapte et se réalise parfaitement et automatiquement.



Bien sûr, pour parfaire la solution, il convient d'enregistrer le fichier en tant que modèle. Ainsi, à chaque nouveau travail depuis le modèle rappelé, la mise en forme automatique se déclenche instantanément à la saisie.