Comment figer des cellules pour ne plus faire d’erreurs dans vos formules

Imaginez

Vous avez créé un tableau pour calculer le pourcentage des produits vendus par votre entreprise.

Vous savez que pour calculer un pourcentage vous devez diviser la quantité vendue par le total des produits.

Pour cela, vous avez déjà calculé le pourcentage du premier produit.

Mais lorsque vous recopiez la formule dans les lignes suivantes ou lorsque vous copiez et collez la formule, vous obtenez des messages d’erreurs de types : #DIV/0!.

#DIV/0!, signifie que vous ne pouvez pas diviser un nombre par zéro.

Le problème, c’est que quand vous recopiez la formule vers le bas, Excel déplace les cellules d’autant de lignes que vous descendez.

Comment allez-vous procéder ?

C’est là qu’interviennent les « RÉFÉRENCES DE CELLULES ».

Qu’est-ce que les « références de cellules » ?

Par essence, toutes les cellules de votre feuille de calcul sont libres. Les formules que vous y appliquez peuvent être déplacées dans toutes les directions (horizontal et/ou vertical).

Or, dans votre calcul de pourcentages, toutes les formules se basent sur le total des quantités vendues. Votre cellule total des quantités vendues doit donc rester fixe, quel que soit le déplacement que vous opérez.

Selon le calcul à effectuer, vous utiliserez des références relatives, absolues ou mixtes, qui font référence à un emplacement mobile ou fixe.

Les références relatives

Les références relatives sont les références de base d’une cellule (A1, B2, C7, etc.). Lorsque vous copiez/coller ou déplacez des « références relatives », les références contenues dans la formule sont modifiées pour correspondre à celles des cellules occupant la même position relative par rapport à la formule.

Les colonnes et les lignes sont totalement mobiles.

Les références absolues

Si une référence doit rester identique dans la copie d’une formule, utilisez une « référence absolue » en insérant le symbole $ devant chaque partie de la référence : la lettre pour la/les colonne(s) et le chiffre pour la/les ligne(s) ($C$7).

Pour insérer le symbole $, sélectionnez la cellule visée et appuyez sur la touche F4 du clavier

Si vous copiez la formule dans un autre emplacement, celle-ci fera toujours référence à la cellule d’origine.

Les références absolues s’appliquent lorsque dans un calcul vous devez faire référence à une même cellule ou lorsque vous placez à l’extérieur de votre tableau, des valeurs constantes comme des : taux, tarifs, coefficients, durées, etc.

Les références mixtes

Les « références mixtes » sont formées d’une ligne absolue et d’une colonne relative ou d’une colonne absolue et d’une ligne relative. C’est-à-dire que le $ est placé soit devant l’indication de colonne ($C7), soit devant l’indication de ligne (C$7).

En fonction de l’emplacement du $, seule la colonne ou seule la ligne reste fixe.

Utiliser une « référence absolue » dans une formule

Sélectionnez la cellule ou la plage de données dans laquelle vous voulez obtenir le résultat

Tapez la formule, commencez par = (égal) ou sélectionnez une formule automatique

Cliquez F2 ou double-cliquez sur la cellule pour afficher les références de la formule

Placez-vous au niveau de la référence de cellule que vous souhaitez figer

Cliquez F4 pour mettre la référence de cellule en absolu ($ devant la lettre de colonne et $ devant le numéro de ligne)

Cliquez Entrée pour valider la formule

Les deux $ indiquent que la formule ne peut pas être déplacée. Elle est totalement figée.

Utiliser une « référence mixte » dans une formule

Sélectionnez la cellule ou la plage de données dans laquelle vous voulez obtenir le résultat

Tapez la formule, commencez par = (égal) ou sélectionnez une formule automatique

Cliquez F2 ou double-cliquez sur la cellule pour afficher les références de la formule

Placez-vous au niveau de la référence de cellule que vous souhaitez figer

Cliquez deux ou trois fois sur F4 pour mettre la référence de cellule en mixte ($ devant la lettre de colonne ou $ devant le numéro de ligne)

Cliquez Entrée pour valider la formule

Le $ placé devant l'indice de colonne interdit tout déplacement horizontal.

Le $ placé devant l'indice de ligne interdit tout déplacement vertical.

Comment utiliser la «croix de recopie» et ne plus avoir à saisir vous-même vos données et formules

Imaginez

Vous devez créer un tableau des ventes annuelles des commerciaux de votre entreprise. Ce tableau doit comporter différentes données comme les noms des commerciaux, les mois de l’année ainsi que les montants et totaux des ventes réalisées (en ligne et en colonne).

Vous ne voulez surtout pas saisir ces informations les unes après les autres.

Vous aimeriez trouver un moyen de recopier les données sans avoir à compléter la saisie vous-même. Par exemple, vous ne voulez saisir les formules qu’une seule fois pour ensuite les recopier sur les différentes lignes et/ou colonnes qui composent votre tableau.

Comment allez-vous procéder ?

C’est là qu’intervient la «CROIX DE RECOPIE» ou « POIGNÉE DE RECOPIE ».

Qu’est-ce que la «croix de recopie» ?

La ««croix de recopie»» permet d'appliquer à une série de cellules consécutives (horizontalement ou verticalement) les modifications apportées à la première d'entre-elles. La ««croix de recopie»» est un outil qui saisit automatiquement des données en fonction des données déjà présentes dans des cellules.

Grâce à la ««croix de recopie»», vous pouvez créer des séries ou copier des valeurs ou des formules dans les cellules voisines.

Où trouver la «croix de recopie» ?

Pour obtenir la ««croix de recopie»», déplacez la souris en bas à droite d’une cellule. Vous obtenez alors une marque foncée en forme de croix noire. C’est la «croix de recopie».

Elle est représentée par un petit carré situé sur le bord inférieur droit d’une cellule sélectionnée.

Comment utiliser la «croix de recopie» ?

Pour utiliser la «croix de recopie», vous avez deux possibilités :

1/

• Sélectionnez la «croix de recopie».
• Avec le bouton gauche de la souris, cliquez sur la croix, maintenez et recopiez votre formule vers les cellules vers lesquelles effectuer la recopier.

2/

• Placez le curseur sur la «croix de recopie». Double-cliquez sur la croix. Excel applique automatiquement la recopie sur toutes les cellules qui suivent jusqu'à ce qu'il tombe sur une cellule vide.

Comment copier des données avec la «croix de recopie» ?

Dans votre tableau, vous voulez saisir l'ensemble des mois de l'année : de janvier à décembre.

• Saisissez le premier mois : janvier
• Faites glisser la «croix de recopie» dans la direction voulue.

Le logiciel incrémente automatiquement les mois suivants : février, mars, avril jusqu'à décembre.

Excel est configuré pour savoir qu’après le mois de janvier vient février ensuite mars, etc.

Cette liste est prédéfinie dans les paramètres d’Excel. Il ne lui reste plus qu’à compléter la série sur la base du modèle que vous avez saisi.

En quelques secondes vous avez rempli votre tableau des mois de l'année.

Avec la «croix de recopie», vous pouvez aussi recopier les jours de semaine (lundi, mardi, etc.), les dates (20/02/2021, 21/02/21, etc.), les valeurs alphanumériques (trimestre 1, trimestre 2, etc.), les nombres (1, 2, 3, etc.) et les formules (somme, moyenne, etc.).

Attention

Lorsque vous voulez recopier une série de nombres, de 1 à 20 par exemple, vous devez saisir les deux premiers chiffres sur les deux premières cellules pour indiquer un modèle à suivre pour Excel.

Saisissez 1 et 2, sélectionnez les deux cellules et tirez la «croix de recopie», Excel créé la suite. Sans quoi, il répète uniquement le chiffre que vous avez saisi.

Réaliser la copie d’une formule avec la «croix de recopie»

Pour continuer, vous voulez maintenant compléter votre tableau avec des formules afin de calculer le total des ventes effectuées par chaque commercial.

Pour cela, saisissez la formule de calcul dans la cellule visée.

Maintenant que la formule de calcul est saisie, utilisez la «croix de recopie» pour appliquer la même formule à toutes les cellules voulues, et ce, sans devoir saisir une nouvelle fois la formule.

• Sélectionnez la «croix de recopie» avec le curseur de la souris
• Maintenez le clic gauche de la souris
• Faites glisser la «croix de recopie» dans la direction souhaitée
• Relâchez le bouton de la souris pour appliquer la recopie

Attention

Copier une formule dans des cellules est simple. Mais, recopier une formule qui contient une valeur qui doit rester fixe est plus complexe.

Si vous créez la formule et vous la tirez vers le bas, Excel risque d’afficher une erreur de type #VALEUR !, #DIV/0 ! au fur et à mesure de votre recopie.

En effet, le logiciel recopie la formule vers le bas, sur chacune des lignes dans lesquelles vous reproduisez la formule. Il va donc changer de ligne même pour la donnée qui doit rester fixe.

Pour éviter les erreurs, vous devez figer la cellule, l’immobiliser en ajoutant le signe $ dans la référence de la cellule visée.

Pour figer une cellule :

• Sélectionnez la cellule visée
• Appuyer sur la touche F4 du clavier

Excel ajoute automatiquement le signe $ aux références de votre cellule ($B$3).

Le $ est placé devant la lettre et/ou devant le nombre.

$ devant la lettre, signifie que la colonne est figée.

$ devant le nombre, signifie que la ligne est figée.

À chaque fois que vous appuyez sur F4, le $ change de place, pour figer la ligne et/ou la colonne.

Pour gagner du temps

Vous voulez reproduire une formule à la verticale du haut vers le bas ?

Au lieu de déplacer la «croix de recopie» vers le bas, double-cliquez sur la «croix de recopie» et toutes les cellules dans la colonne de votre tableau vont être complétées jusqu'à ce qu’Excel rencontre une cellule vide.

Comment fusionner des cellules en deux étapes simples et rapides

Imaginez

Vous devez mettre en forme un tableau.

Pour cela, vous souhaitez fusionner certaines cellules. Notamment vous aimeriez placer le titre du tableau au centre de plusieurs colonnes. Vous avez aussi quelques autres cellules que vous aimeriez regrouper en une seule.

Comment allez-vous procéder ?

C'est là qu'intervient l'outil « FUSIONNER »

Qu’est-ce que « fusionner » ?

Fusionner vous permet de transformer plusieurs cellules distinctes et côte à côte (horizontalement ou verticalement) en une seule et plus grande cellule.

La fusion des cellules peut être particulièrement utile lorsque vous avez un contenu plus long que la taille standard d'une cellule et que vous ne voulez pas agrandir toute la cellule.

Où trouver « fusionner » ?

« Fusionner » se trouve dans le menu « Accueil » dans le groupe « alignement »

• Accueil > Alignement > « Fusionner »

Il est représenté par une icône en forme de cellule avec une flèche bleue au centre

Quel est le raccourci clavier de « Fusionner » ?

Le raccourci de l'outil fusionner est :

• Alt + L + UU + F

Comment fusionner des cellules ?

Dans votre tableau, vous voulez d'abord fusionner le titre pour le placer horizontalement sur toute la longueur du tableau.

• Sélectionnez votre titre et toutes les cellules sur lesquelles vous voulez le fusionner.

• Cliquez sur la flèche à droite de l’icône fusionner, vous accédez aux options de fusion.
• Cliquez sur « fusionner ».

Vous voyez les cellules sélectionnées disparaître et laissent place à une unique et grande cellule avec le titre de votre tableau.

Attention

Lorsque vous fusionnez une sélection avec plusieurs données, la fusion ne conserve uniquement que les données de la cellule la plus à gauche.

Cette fois-ci, vous voulez fusionner et centrer le titre du tableau

Quel est le raccourci clavier de « Fusionner et centrer » ?

Le raccourci de l'outil « fusionner et centrer » est :

• Alt + L + UU + R

Comment fusionner et centrer des cellules

Fusionner vous permet de transformer plusieurs cellules distinctes et côte à côte (horizontalement ou verticalement) en une seule et plus grande cellule. Centrer permet de placer le contenu d’une cellule au centre de cette cellule fusionnée.

Dans votre tableau, vous voulez d'abord fusionner le titre pour le placer horizontalement sur la longueur du tableau.

• Sélectionnez votre titre et toutes les cellules sur lesquelles vous voulez le fusionner.
• Cliquez sur la flèche à droite de l’icône fusionner, vous accédez aux options de fusion.
• Cliquez sur « fusionner et centrer ».

Vous voyez les cellules sélectionnées disparaître et laisser place à une unique et grande cellule avec le titre de votre tableau au centre.

Vous avez fusionné vos cellules. Mais, quand vous agrandissez la cellule fusionnée, vous agrandissez toute la colonne et votre tableau est décalé. Il n’a plus la forme que vous voulez.

De plus, vous ne pouvez rien saisir sur les cellules suivantes.

C’est embêtant !

Comment faire pour fusionner les données et garder les possibilités de mobilité et d’écriture sur les cellules fusionnées.

Pour ce faire :

Sélectionnez les cellules que vous voulez fusionner.

Cliquez sur la boîte de dialogue du groupe « alignement »

Dans « alignement de texte horizontal », sélectionnez « centrer sur plusieurs colonnes »

Cliquez OK pour valider

Vous avez centré votre titre sur plusieurs colonnes. Cette fois-ci, lorsque vous agrandissez votre cellule, elle s’agrandit en ne modifiant pas les autres cellules.