Comment recopier une formule sur des milliers de lignes dans Excel ?
Si vous travaillez sur un ordinateur, vous utilisez probablement un tableur au quotidien. Que ce soit Excel (en version Office 365 ou une version plus ancienne), Google Sheets (le tableur en ligne de Google), LibreOffice Calc (le tableur de la suite gratuite LibreOffice) ou un autre logiciel de feuilles de calcul, il y a de fortes chances que vous manipuliez régulièrement des tableaux de données dans un cadre professionnel ou étudiant.
📌 L’essentiel à retenir
Pour recopier une formule sur des milliers de lignes dans Excel, inutile de faire glisser la poignée de recopie sur toute la colonne. Un simple double-clic sur la croix noire en bas à droite de la cellule suffit : la formule s’applique instantanément à toutes les lignes du tableau. Cette astuce fonctionne aussi dans Google Sheets et LibreOffice Calc, et peut vous faire gagner un temps considérable sur les grands tableaux.
Pourquoi faire glisser la poignée de recopie ne suffit pas sur un grand tableau
Mon fils aîné est justement venu me demander conseil : il devait appliquer une formule sur un tableau contenant plus de 200 000 lignes.
Il connaissait la méthode classique pour appliquer une formule à un ensemble de lignes : se placer dans le coin inférieur droit de la cellule contenant la formule, saisir la poignée de recopie et la faire glisser vers le bas (méthode expliquée sur le site de Microsoft pour celles et ceux qui ne savent pas comment faire).
Sauf que son tableau comportait plusieurs dizaines de milliers de lignes. Faire glisser la formule manuellement sur une telle distance, c’est long, peu pratique et source d’erreurs. La bonne nouvelle : il existe une astuce pour recopier une formule sur des milliers de lignes sans effort, en une seconde !
L’astuce du double-clic pour recopier une formule instantanément
La manipulation est très simple. Voici comment recopier une formule sur des milliers de lignes sans effort :
- Placez-vous dans le coin inférieur droit de la cellule contenant la formule, jusqu’à ce qu’une croix noire (la poignée de recopie) apparaisse.
- Faites un double-clic sur cette croix noire. La formule est instantanément recopiée dans toutes les cellules de la colonne, jusqu’à la dernière ligne remplie de la colonne adjacente.
Une condition à respecter : la colonne située juste à gauche de votre formule doit être remplie sans cellule vide. C’est elle qui sert de repère à Excel pour savoir jusqu’où étendre la formule.
Si la poignée de recopie n’est pas activée dans votre version d’Excel, vous pouvez la réactiver en suivant les instructions de Microsoft.
Voici une vidéo qui vous montre comment étendre une formule sur toute la colonne :
Références relatives et absolues : ce qu’il faut savoir avant de recopier une formule
Avant de recopier une formule sur des milliers de lignes, vérifiez bien les références de cellule utilisées dans votre formule. C’est un point que beaucoup de débutants oublient, et qui peut produire des résultats faux sur tout le tableau.
Par défaut, Excel utilise des références relatives. Si votre formule en cellule C2 est =A2*B2, elle deviendra automatiquement =A3*B3 en C3, =A4*B4 en C4, etc. C’est le comportement souhaité dans la plupart des cas.
Si votre formule fait référence à une cellule fixe (un taux de TVA en G1, par exemple), vous devez la verrouiller avec le signe $. La formule =A2*$G$1 gardera la référence à G1 intacte lors de la recopie. Sans ce verrouillage, Excel décalera G1 vers G2, G3, etc., et vos résultats seront faux.
Astuce rapide : pour basculer entre référence relative, absolue et mixte, placez votre curseur sur la référence dans la barre de formule et appuyez sur la touche F4. Chaque appui fait défiler les combinaisons possibles : A1 → $A$1 → A$1 → $A1 → A1.
Autres méthodes pour recopier une formule sur toute une colonne
Le double-clic sur la poignée de recopie est la méthode la plus rapide, mais ce n’est pas la seule. Voici deux alternatives pratiques qui fonctionnent aussi bien sur des petits tableaux que sur des feuilles de calcul contenant des centaines de milliers de lignes.
Méthode par raccourci clavier (Ctrl + D)
Sélectionnez la cellule contenant la formule. Étendez ensuite votre sélection vers le bas jusqu’à la dernière ligne souhaitée. Pour aller vite, vous pouvez utiliser le raccourci clavier Ctrl + Maj + Fin (qui sélectionne jusqu’à la dernière cellule utilisée du tableau). Appuyez ensuite sur Ctrl + D : la formule est recopiée dans toutes les cellules sélectionnées.
Méthode par copier-coller (Ctrl + C, Ctrl + V)
Copiez la cellule contenant la formule (Ctrl + C). Sélectionnez ensuite la plage de destination (par exemple de C3 à C200000) et collez avec Ctrl + V. Cette méthode a l’avantage de fonctionner même quand la colonne adjacente contient des cellules vides, ce qui bloque parfois le double-clic.
Optimiser la recopie de formules sur un très grand tableau Excel
Quand votre tableau dépasse les 100 000 lignes, la recopie d’une formule peut prendre du temps et ralentir Excel. Le logiciel recalcule l’ensemble du classeur à chaque modification, ce qui consomme beaucoup de mémoire vive.
Pour éviter ce problème, désactivez temporairement le calcul automatique avant de lancer la recopie. Dans Excel, allez dans l’onglet Formules, cliquez sur Options de calcul, puis sélectionnez Manuel. Recopiez votre formule, puis réactivez le calcul automatique et appuyez sur F9 pour recalculer le classeur en une seule passe. La différence de vitesse est spectaculaire.
Pensez aussi à sauvegarder votre fichier avant toute manipulation sur un tableau volumineux. Si votre fichier Excel est endommagé à la suite d’un plantage, vous pouvez consulter notre guide pour réparer un fichier Excel corrompu.
Recopier une formule dans Google Sheets et LibreOffice Calc
L’astuce du double-clic sur la poignée de recopie n’est pas réservée à Excel. Elle fonctionne aussi dans les autres tableurs courants.
Dans Google Sheets, la poignée de recopie est un petit carré bleu situé en bas à droite de la cellule sélectionnée. Le principe est identique : double-cliquez dessus et la formule s’applique jusqu’à la dernière ligne remplie de la colonne voisine. Le raccourci Ctrl + D fonctionne aussi dans Google Sheets après avoir sélectionné la plage de destination.
Dans LibreOffice Calc, le comportement est le même qu’Excel. La poignée de recopie apparaît sous la forme d’un petit carré noir. Double-cliquez dessus pour recopier la formule. Vous pouvez aussi passer par le menu Feuille > Remplir les cellules > Remplir vers le bas pour obtenir le même résultat sur une sélection.
Questions fréquentes sur la recopie de formules dans un tableur
Pourquoi le double-clic sur la poignée de recopie ne fonctionne pas ?
Le double-clic sur la poignée de recopie a besoin d’un repère pour savoir où s’arrêter. Si la colonne située immédiatement à gauche de votre formule contient des cellules vides, la recopie s’arrêtera à la première ligne vide. Vérifiez que la colonne adjacente est remplie sans interruption.
Comment recopier une formule vers le bas avec un raccourci clavier ?
Sélectionnez la cellule contenant la formule, puis étendez la sélection jusqu’à la dernière ligne souhaitée (par exemple avec Ctrl + Maj + Fin). Appuyez ensuite sur Ctrl + D pour remplir toutes les cellules sélectionnées avec la formule. Cette méthode fonctionne dans Excel et LibreOffice Calc.
L’astuce du double-clic fonctionne-t-elle dans Google Sheets ?
Oui. Dans Google Sheets, le double-clic sur la poignée de recopie (le petit carré bleu en bas à droite de la cellule) fonctionne de la même manière que dans Excel. La formule est recopiée automatiquement jusqu’à la dernière ligne remplie de la colonne adjacente.
Quelle est la différence entre une référence relative et une référence absolue dans une formule ?
Une référence relative (ex. : A1) s’adapte automatiquement quand vous recopiez la formule vers le bas : A1 devient A2, A3, etc. Une référence absolue (ex. : $A$1) reste fixe, quelle que soit la cellule de destination. Utilisez le signe $ pour verrouiller une ligne, une colonne, ou les deux.
Comment recopier une formule sur 200 000 lignes sans ralentir Excel ?
Sur de très grands tableaux, désactivez temporairement le calcul automatique avant la recopie (Formules > Options de calcul > Manuel). Recopiez la formule, puis réactivez le calcul automatique et appuyez sur F9 pour recalculer. Cela évite qu’Excel recalcule le classeur à chaque ligne ajoutée.
Peut-on recopier une formule sur une ligne entière (horizontalement) ?
Oui, le principe est identique. Placez votre curseur sur la poignée de recopie (coin inférieur droit de la cellule), puis faites un double-clic ou glissez vers la droite. Le raccourci Ctrl + R remplit les cellules sélectionnées vers la droite, tout comme Ctrl + D le fait vers le bas.
Commentaires
Laisser un commentaire