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.

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 centaines 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 :

1. 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.
   
2. 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 : une colonne adjacente à votre formule (celle de gauche, ou à défaut celle de droite) doit être remplie sans cellule vide. C’est elle qui sert de repère à Excel pour savoir jusqu’où étendre la formule. Sachez aussi que le double-clic ne recopie que vers le bas, jamais vers la droite.

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. Si ces notions sont encore floues, notre guide sur les bases du tableur Excel revient sur les références relatives, absolues et mixtes.

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 quatre 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. Sur 200 000 lignes, pas la peine de faire défiler à la souris : tapez directement la plage (par exemple C2:C200000) dans la Zone Nom, à gauche de la barre de formule, puis validez avec Entrée. Appuyez ensuite sur le raccourci clavier Ctrl + D : la formule est recopiée dans toutes les cellules sélectionnées.

Petit conseil : méfiez-vous de Ctrl + Maj + Fin, souvent cité pour ce genre de manipulation. Ce raccourci étend la sélection jusqu’à la dernière cellule utilisée de la feuille, donc aussi sur toutes les colonnes situées à droite de votre formule. Si elle n’est pas dans la dernière colonne, Ctrl + D écrasera les données voisines. Ça pique. Variante pratique : sélectionnez d’abord la plage vide, tapez la formule, puis validez avec Ctrl + Entrée au lieu d’Entrée. La formule est saisie d’un coup 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.

Méthode par tableau Excel (Ctrl + L) : la recopie devient automatique

C’est la méthode que je recommande dès que votre fichier évolue chaque semaine. Convertissez votre plage en tableau Excel (onglet Insertion > Tableau, ou raccourci Ctrl + L). Saisissez ensuite la formule dans la première cellule d’une colonne du tableau : Excel crée une colonne calculée et l’étend tout seul à toutes les lignes. Au passage, chaque nouvelle ligne ajoutée au tableau récupère la formule automatiquement. Plus rien à recopier, jamais.

Méthode par formule dynamique (Excel 365 et Excel 2021)

Les versions récentes d’Excel gèrent les formules matricielles dynamiques. Concrètement, une seule formule peut calculer toute la colonne : saisissez =A2:A200000*B2:B200000 en C2 et le résultat « déborde » automatiquement sur les 199 999 cellules du dessous. Une seule formule à maintenir au lieu de 200 000 copies. Seule contrainte : les cellules de destination doivent être vides, sinon Excel affiche l’erreur #PROPAGATION!.

Optimiser la recopie de formules sur un très grand tableau Excel

Quand votre tableau dépasse les 100 000 lignes (une feuille Excel en accepte au maximum 1 048 576), 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. Ces tableaux très longs proviennent souvent d’un export de logiciel métier : dans ce cas, prenez le temps de convertir un fichier CSV en Excel correctement avant d’y appliquer la moindre formule.

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.