Apprenez à copier une formule dans plusieurs cellules, dans une colonne, dans des cellules non adjacentes, à copier des formules avec des références de cellule absolues ou mixtes, etc.
La copie de formules est l’une des tâches les plus courantes et les plus simples que vous effectuez dans une feuille de calcul classique reposant principalement sur des formules. Il est facile et rapide de copier et coller une formule d’une cellule à une autre que de retaper la formule entière à nouveau.
Après avoir écrit une formule dans Excel, vous pouvez utiliser les commandes copier-coller dans plusieurs cellules, plusieurs cellules non adjacentes ou des colonnes entières. Si vous ne le faites pas correctement, vous vous retrouverez avec ces horribles erreurs # REF et/DIV0. Dans cet article, nous aborderons différentes manières de copier des formules dans Excel.
Comment copier et coller des formules dans Excel
Microsoft Excel propose différentes manières de copier des formules avec des références de cellule relatives, des références de cellule absolues ou des références mixtes.
- Copier la formule d’une cellule à une autre
- Copier la formule d’une cellule dans plusieurs cellules
- Copier la formule dans toute la colonne
- Copier la formule sans mise en forme
- Copier des formules dans des cellules non adjacentes
- Copier des formules sans changer les références de cellule
Comment copier une formule d’une cellule à une autre dans Excel
Parfois, nous préférons peut-être copier et coller une formule d’une cellule à une autre pour éviter de retaper la formule entière une fois de plus.
Disons que nous avons ce tableau:
Il existe en fait plusieurs méthodes à copier.
Tout d’abord, sélectionnez la cellule contenant la formule et cliquez avec le bouton droit de la souris, puis dans le menu contextuel, sélectionnez”Copier”pour copier la formule. Vous pouvez également utiliser la sélection”Copier”dans la section”Presse-papiers”de l’onglet”Accueil”du ruban.
Mais vous copiez également des formules en appuyant simplement sur le raccourci clavier Ctrl + C . C’est une méthode plus efficace et plus rapide.
Ensuite, nous allons dans la cellule que nous voulons coller appuyez sur le raccourci Ctrl + V pour coller la formule. Ou cliquez avec le bouton droit de la souris sur la cellule que vous souhaitez coller et cliquez sur les options sous”Options de collage”: soit une simple option”Coller (P)”, soit coller comme option”Formule (F)”.
Vous pouvez également cliquer sur”Collage spécial”sous ces six icônes pour ouvrir la boîte de dialogue”Collage spécial”. Ici, vous pouvez voir les mêmes options en plus de plusieurs autres. Sélectionnez”Tous”ou”Formules”dans la section Coller et cliquez sur”OK”.
Désormais, la cellule avec la formule collée doit avoir des formules identiques à celle de la cellule copiée, mais avec les références de cellule modifiées pour correspondre aux numéros de ligne.
Copier la formule d’une cellule vers plusieurs cellules
La même opération de collage fonctionne de la même manière si nous sélectionnons plusieurs cellules ou une plage de cellules.
Cliquez sur la cellule contenant la formule pour la sélectionner et appuyez sur Ctrl + C pour copier la formule. Ensuite, sélectionnez toutes les cellules dans lesquelles vous souhaitez coller la formule et appuyez sur Ctrl + V pour coller la formule ou utilisez l’une des méthodes ci-dessus pour coller la formule (comme nous l’avons fait pour la cellule unique).
Copier la formule dans une colonne ou une ligne entière
Excel offre un moyen très rapide de copier une formule dans une colonne ou une ligne.
Pour copier une formule dans une colonne, commencez par saisir une formule dans une cellule. Ensuite, sélectionnez la cellule contenant la formule et placez le curseur de la souris sur un petit carré dans le coin inférieur droit de la cellule (appelé poignée de remplissage). Au fur et à mesure que vous survolez, le curseur se transforme en signe plus noir (+). Maintenez ce signe plus et faites glisser la poignée de recopie vers le bas de la colonne sur les cellules dans lesquelles vous souhaitez copier la formule.
Lorsque vous copiez la formule dans une plage de cellules, la référence de cellule change automatiquement en fonction de la position relative des lignes et des colonnes et la formule effectue des calculs basés sur les valeurs de ces références de cellule (voir ci-dessous).
De la même manière, vous pouvez faire glisser la formule dans les cellules adjacentes vers la gauche, la droite ou le haut.
Une autre façon d’appliquer la formule à l’ensemble de la colonne consiste à double-cliquer sur le”signe plus”au lieu de le faire glisser. Lorsque vous double-cliquez sur le signe plus, il copie la formule dans la mesure où il y a des données à gauche.
Copier une formule dans une plage sans copier le formatage
La copie d’une formule dans une plage de cellules en faisant glisser la poignée de recopie copie non seulement la formule, mais également la mise en forme de la cellule source telle que la couleur de la police ou la couleur d’arrière-plan, les symboles monétaires, les décimales, etc. (comme indiqué ci-dessous).
Pour éviter de copier la mise en forme de la cellule, faites glisser la poignée de recopie comme indiqué ci-dessus. Ensuite, cliquez sur”Options de remplissage automatique”dans le coin inférieur droit de la dernière cellule, et dans le menu déroulant, sélectionnez”Remplir sans mise en forme”.
Le résultat:
Copier une formule Excel avec uniquement la mise en forme des nombres
Mais si vous souhaitez copier et coller la formule avec uniquement la formule et le format des nombres tels que le format de pourcentage, le format de devise, etc., utilisez la fonction Copier et coller d’Excel.
Copiez la formule et sélectionnez toutes les cellules dans lesquelles vous souhaitez copier la formule. Dans l’onglet”Accueil”, cliquez sur la flèche sous le bouton”Coller”sur le ruban. Ensuite, sélectionnez l’icône”Formules et formatage des nombres”dans la liste déroulante pour coller uniquement la formule et la mise en forme des nombres.
Cette option ne copie que le formatage des formules et des nombres mais ignore tous les autres formats de cellule comme la couleur d’arrière-plan, la couleur de la police, etc.
Copier une formule dans des cellules non adjacentes
La copie de formules dans plusieurs cellules ou une plage de cellules à l’aide de la poignée de recopie est la méthode la plus rapide d’Excel. Mais que se passe-t-il si vous souhaitez copier une formule dans des cellules non contiguës ou non adjacentes ou au-delà de la fin de la cellule source? Vous pouvez le faire en utilisant la touche Ctrl .
Sélectionnez la cellule contenant la formule pour la sélectionner et appuyez sur Ctrl + C pour copier la formule. Ensuite, sélectionnez des cellules/plages non adjacentes tout en maintenant la touche Ctrl enfoncée. Appuyez sur Ctrl + V pour coller la formule et appuyez sur Entrée pour terminer.
Copie de formules sans changer les références de cellule dans Excel
Lorsque vous copiez une formule dans une autre cellule dans Excel, il ajuste automatiquement les références de cellule. Ces références sont appelées référence de cellule relative (sans $) qui utilise la position relative d’une adresse de cellule. Par exemple, si vous avez la formule «=A1 * B1» dans la cellule C1 et que vous copiez cette formule dans la cellule C2, la formule deviendra «=A2 * B2». Toutes les méthodes dont nous avons parlé ci-dessus utilisent des références relatives.
Si votre formule contient des références de cellules relatives, Excel les ajuste automatiquement afin que la formule fasse référence aux lignes et colonnes ajustées. Si vous utilisez des références absolues dans une formule, la formule exacte est copiée.
Lorsque vous ajoutez un signe dollar ($) devant la lettre de la colonne et le numéro de ligne (par exemple $ A $ 1), cette cellule devient une cellule absolue. Peu importe où vous copiez cette formule, la formule ne le sera jamais. Mais si vous avez une référence de cellule relative ou mixte dans une formule, utilisez l’une des méthodes suivantes pour copier sans modifier les références de cellule.
Copier une formule avec une référence de cellule absolue à l’aide de la méthode copier-coller
Parfois, vous souhaiterez peut-être copier la formule exactement, sans changer les références de cellule en cours de route. Si vous devez copier ou déplacer une formule exacte sans changer de référence, procédez comme suit:
Tout d’abord, sélectionnez la cellule contenant la formule que vous souhaitez copier. Ensuite, cliquez sur la barre de formule, sélectionnez la formule à l’aide de la souris et appuyez sur Ctrl + C pour la copier. Si vous souhaitez déplacer la formule, appuyez sur Ctrl + X pour la couper.
Sinon, sélectionnez la cellule avec la formule, appuyez sur F2 (ou double-cliquez sur la cellule) pour entrer en mode d’édition. Ensuite, sélectionnez la formule dans la cellule à l’aide de la souris et appuyez sur Ctrl + C pour copier la formule dans la cellule sous forme de texte.
Ensuite, appuyez sur la touche Echap pour quitter la barre de formule. Ensuite, sélectionnez la cellule de destination et appuyez sur Ctrl + V pour coller la formule.
Cela collera la formule exactement, sans changer les références de cellule.
Copier des formules avec des références de cellules absolues ou mixtes
Si vous souhaitez déplacer ou copier des formules Excel afin qu’aucune référence de cellule ne soit modifiée, le meilleur moyen serait de changer les références de cellule en références absolues. Par exemple, l’ajout du signe ($) à la référence de cellule relative (B1) en fait une référence absolue ($ B $ 1), elle reste donc statique quel que soit l’endroit où la formule est copiée ou déplacée.
Dans certains cas, vous devrez peut-être utiliser des références de cellule mixtes ($ A1 ou A $ 1) pour verrouiller une ligne ou une colonne.
Expliquons avec un exemple. Supposons que vous ayez ce tableau qui calcule les économies mensuelles en soustrayant le loyer (B9) des revenus (dans la colonne B) chaque mois.
Comme vous pouvez le voir dans la capture d’écran ci-dessous, la formule comprend une référence de cellule absolue ($ B $ 9) pour fixer le montant du loyer à la cellule B9, et une référence de cellule relative à la cellule B2 car vous souhaitez que cette référence s’ajuste pour chaque ligne. B9 est une référence de cellule absolue (9 $ G $), car vous souhaitez soustraire le même montant de loyer des revenus de chaque mois.
Supposons que vous souhaitiez déplacer les soldes de la colonne C à la colonne E. Si vous copiez la formule (par la méthode habituelle de copier/coller) de la cellule C2 (=B2-$ B $ 9) deviendra=D2-$ B 9 $ lorsqu’il est collé dans la cellule E2, ce qui rend vos calculs tout faux!
Pour résoudre ce problème, changez simplement une référence relative (B2) en une référence mixte $ B2 (colonne absolue et ligne relative) en plaçant le signe dollar ($) devant la lettre de la colonne.
Et maintenant, si vous copiez ou déplacez les formules de la colonne C vers la colonne E, ou toute autre colonne, la référence de colonne ne s’ajustera pas car vous l’avez ancrée par le signe dollar ($ B2).
Copier Coller des formules Excel sans modifier les références à l’aide du Bloc-notes
Vous pouvez voir chaque formule dans votre feuille de calcul Excel en utilisant les options Afficher la formule. Pour ce faire, allez dans l’onglet”Formules”et sélectionnez”Afficher les formules”.
Vous pouvez également accéder au mode d’affichage des formules en appuyant sur le raccourci Ctrl + `, qui affiche chaque formule dans votre feuille de calcul. La touche d’accent grave (`) est la touche la plus éloignée du coin supérieur gauche de votre clavier sur la ligne avec les touches numériques (sous la touche ESC et à côté de la touche numéro 1).
Sélectionnez toutes les cellules contenant les formules que vous souhaitez copier et copiez-les en appuyant sur Ctrl + C , ou Ctrl + X pour les couper. Ensuite, ouvrez le Bloc-notes ou tout autre éditeur de texte et appuyez sur Ctrl + V pour coller les formules dans le bloc-notes.
Ensuite, sélectionnez les formules et copiez-les depuis le bloc-notes, puis collez ( Ctrl + V ) dans les cellules où vous voulez que les formules exactes soient copiées.
Après avoir collé les formules, désactivez le mode d’affichage des formules en appuyant sur Ctrl + ` ou à nouveau allez dans”Formules”->”Afficher les formules”.
Copiez les formules exactes à l’aide de la fonction Rechercher et remplacer d’Excel
Si vous souhaitez copier une plage de formules Excel sans modifier leurs références de cellule, vous pouvez utiliser la fonction Rechercher et remplacer d’Excel de la manière suivante.
Sélectionnez les cellules contenant les formules que vous souhaitez copier. Ensuite, allez dans l’onglet «Accueil», cliquez sur «Rechercher et sélectionner» dans le groupe Édition et sélectionnez l’option «Remplacer», ou appuyez simplement sur Ctrl + H pour ouvrir la boîte de dialogue Rechercher et remplacer.
Dans la boîte de dialogue Rechercher et remplacer, saisissez le signe égal (=) dans le champ”Rechercher”. Dans le champ”Remplacer par”, saisissez un symbole ou un caractère qui n’est utilisé dans aucune de vos formules, comme #, ou \, etc. Cliquez ensuite sur le bouton”Tout remplacer”.
Vous verrez une boîte de message indiquant”Nous avons effectué 6 remplacements”(car nous avons sélectionné 6 cellules avec des formules). Cliquez ensuite sur «OK» et «Fermer» pour fermer les deux boîtes de dialogue. Cela remplace tous les signes égaux à (=) par des signes dièse (#) et transforme les formules en chaînes de texte. Désormais, les références de cellule des formules ne seront plus modifiées lors de la copie.
Maintenant, vous pouvez sélectionner ces cellules, appuyez sur Ctrl + C pour les copier et collez-les dans les cellules de destination avec Ctrl + V .
Enfin, vous devez remplacer les signes (#) par (=). Pour ce faire, sélectionnez les deux plages (plage d’origine et plage copiée) et appuyez sur Ctrl + H pour ouvrir la boîte de dialogue Rechercher et remplacer. Cette fois, saisissez le signe dièse (#) dans le champ”Rechercher”, et égal au signe (=) dans le champ”Remplacer par”, puis cliquez sur le bouton”Tout remplacer”. Cliquez sur”Fermer”pour fermer la boîte de dialogue.
Maintenant, les chaînes de texte sont reconverties en formules et vous obtiendrez ce résultat:
C’est fait!
