Comment faire pour recopier automatiquement une formule lorsqu’on insère des lignes dans Excel ?
Lorsque vous travaillez avec des données dans Excel, il est courant d’insérer de nouvelles lignes au sein d’un jeu de données existant, par exemple pour ajouter de nouvelles entrées ou mettre à jour des enregistrements. Toutefois, insérer des lignes vides entre des données pose un problème fréquent : les formules présentes dans les colonnes adjacentes ne se copient généralement pas automatiquement vers ces nouvelles lignes. Vous devez alors habituellement utiliser manuellement la poignée de recopie ou faire glisser la formule vers le bas pour l’étendre aux nouvelles lignes — une tâche qui peut vite devenir fastidieuse, surtout si vous mettez fréquemment à jour vos données.
Cette situation peut entraîner des incohérences de calcul ou des omissions, notamment si vous oubliez d’appliquer une formule à une ligne nouvellement insérée. Pour illustrer ce point, prenons l’exemple suivant : certaines colonnes contiennent des formules, et vous insérez une ligne vide entre des données existantes — les formules des colonnes adjacentes ne sont alors pas transférées automatiquement.
Pour remédier à ce problème et garantir que vos formules soient automatiquement recopiées à chaque insertion de nouvelles lignes, plusieurs solutions pratiques s’offrent à vous. Cet article présente des instructions détaillées pour chacune de ces approches, adaptées à diverses habitudes et exigences de travail. Vous y découvrirez également des conseils pratiques ainsi que des suggestions de dépannage pour éviter les pièges courants.
➤ Recopie automatique de Formule de remplissage lors de l’insertion de Lignes vides en créant un tableau
➤ Recopie automatique de Formule de remplissage lors de l’insertion de Lignes vides à l’aide d’un code VBA
➤ Recopie automatique de Formule de remplissage à l’aide de la commande Remplissage vers le bas d’Excel
Recopie automatique de Formule de remplissage lors de l’insertion de Lignes vides en créant un tableau
L’une des méthodes les plus efficaces pour que Excel recopie automatiquement les Formule de remplissage dans les nouvelles lignes consiste à convertir votre Plage de données en tableau. Dans ce format, les formules saisies dans une colonne sont considérées comme faisant partie d’une colonne structurée et s’appliquent automatiquement à toute ligne ajoutée au tableau — que vous insériez une ligne à la fin ou au milieu. Cela vous évite la tâche répétitive de copier les formules et élimine presque entièrement les erreurs dues à des formules oubliées.
Pour utiliser cette méthode, suivez les étapes ci-dessous :
1. Sélectionnez la plage de données dans laquelle vous souhaitez que les formules soient recopiées automatiquement. Accédez ensuite à l’onglet Insertion et cliquez sur Tableau. Reportez-vous à cette capture d’écran :
2. Dans la boîte de dialogue Créer un tableau, assurez-vous de cocher l’option Mon tableau comporte des en-têtes si votre plage inclut des en-têtes de colonne. Cela préserve vos titres et permet d’organiser clairement vos données. Voir la capture d’écran :
3. Cliquez sur le bouton OK pour confirmer. Vos données sont désormais mises en forme sous forme de tableau. À partir de ce moment, chaque fois que vous insérerez une ligne vide au sein du tableau, les formules existantes dans les colonnes seront automatiquement étendues à la nouvelle ligne, comme illustré ci-dessous :
Cette méthode est simple et fiable, idéale pour gérer des listes structurées ou des enregistrements réguliers.
Conseils et remarques :
- Les tableaux appliquent automatiquement les formules à toutes leurs colonnes, garantissant ainsi une cohérence parfaite même dans les jeux de données les plus volumineux.
- Vous pouvez insérer des lignes en cliquant avec le bouton droit sur un numéro de ligne du tableau et en sélectionnant Insérer, ou simplement en appuyant sur Tabulation lorsque vous êtes positionné à la fin du tableau.
- Si une formule dépend de références de cellules situées en dehors du tableau, veillez à utiliser des références absolues lorsque nécessaire pour éviter toute erreur de référence.
- Si vous supprimez la mise en forme de tableau, cette recopie automatique ne sera plus appliquée.
- La mise en forme de vos données sous forme de tableau peut ne pas convenir aux utilisateurs qui privilégient une disposition libre ou une mise en forme avancée des cellules.
Recopie automatique de Formule de remplissage lors de l’insertion de Lignes vides à l’aide d’un code VBA
Si l’utilisation de tableaux ne correspond pas à votre flux de travail ou si vos données nécessitent une plage traditionnelle, vous pouvez utiliser VBA pour obtenir une recopie automatique des formules après l’insertion d’une ligne. Cette solution VBA est flexible et peut être personnalisée afin de copier automatiquement les formules de la ligne supérieure dès qu’une nouvelle ligne est insérée, vous aidant ainsi à préserver l’intégrité des formules dans votre jeu de données.
Pour appliquer cette approche, suivez attentivement les étapes suivantes :
1. Commencez par sélectionner ou ouvrir l’onglet de la feuille contenant les formules que vous souhaitez recopier automatiquement. Cliquez avec le bouton droit sur l’onglet de la feuille en bas d’Excel, puis choisissez Afficher le code dans le menu contextuel. L’éditeur Microsoft Visual Basic pour Applications s’ouvrira. Dans la fenêtre qui apparaît, insérez un nouveau module en cliquant sur Insertion > Module, puis copiez-collez le code suivant dans le module :
Code VBA : Recopie automatique de Formule de remplissage lors de l’insertion de Lignes vides
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub
2. Sauvegardez et fermez l’éditeur VBA, puis revenez à votre feuille de calcul. Désormais, dès que vous double-cliquerez sur une cellule de votre plage de données, une nouvelle ligne s’insérera automatiquement juste en dessous, et les formules des colonnes adjacentes seront recopiées instantanément dans cette nouvelle ligne.
Remarques supplémentaires concernant cette approche :
- Si une invite relative à la sécurité des macros s’affiche lors de la réouverture du classeur, activez les macros pour permettre au code de s’exécuter correctement.
- Les solutions VBA offrent une grande flexibilité pour gérer des mises en forme variées, mais elles exigent des classeurs compatibles avec les macros — une contrainte qui peut poser problème dans des environnements Excel strictement contrôlés ou restreints.
- Lorsque vous appliquez des modifications à des classeurs partagés ou à des documents stockés dans le cloud, l’exécution des macros peut être limitée — assurez-vous que les autres utilisateurs en sont informés et disposent des autorisations appropriées.
- Attention : un double-clic en dehors de la plage souhaitée peut tout de même déclencher l’insertion d’une ligne ; veillez donc à tester soigneusement.
Recopie automatique de Formule de remplissage à l’aide de la commande Remplissage vers le bas d’Excel
Si vous insérez occasionnellement de nouvelles lignes et que vous recherchez un moyen rapide d’appliquer des formules existantes — sans transformer vos données en tableau ni utiliser VBA — la commande Remplissage vers le bas d’Excel constitue une solution simple et efficace. En quelques clics ou à l’aide d’un raccourci clavier, elle copie automatiquement la formule de la cellule située juste au-dessus dans les cellules sélectionnées en dessous.
Étapes de la procédure :
- Méthode A : Faites glisser la poignée de recopie (le petit carré en bas à droite de la cellule sélectionnée) vers le bas jusqu’à la cellule vide.
- Méthode B :Utilisez la commande Remplissage vers le bas :
- Accédez à l’onglet Accueil> groupe Édition> cliquez sur Remplissage>Vers le bas
- Ou appuyez sur Ctrl + Dpour remplir la formule dans la cellule située en dessous
Avantages :
- Pas besoin de recourir à des tableaux ni au code VBA.
- Offre un contrôle manuel précis sur l’endroit et le moment d’application des formules.
- Rapide et efficace pour des modifications ponctuelles.
Inconvénients :
- Moins adapté aux insertions fréquentes de lignes ou aux grands ensembles de données.
- Les étapes manuelles, si elles ne sont pas appliquées avec rigueur, peuvent entraîner des lignes oubliées ou des formules incohérentes.
Conseil de dépannage : Après avoir inséré des lignes et appliqué la commande « Remplissage vers le bas », vérifiez toutes les formules pour vous assurer qu’elles font bien référence aux plages souhaitées — surtout si vous utilisez des références dynamiques ou des totaux cumulés. Si vous devez fréquemment appliquer des formules à de nouvelles lignes, envisagez d’utiliser un tableau structuré ou une macro VBA simple pour automatiser ce processus de manière plus efficace.
Grâce à ces méthodes, vous préservez la cohérence de vos données, réduisez les tâches manuelles et évitez les erreurs causées par des formules manquantes après l’ajout de nouvelles lignes. Optez pour l’approche qui correspond le mieux à votre environnement Excel et à vos besoins spécifiques.
Démo : Recopie automatique de Formule de remplissage lors de l’insertion de Lignes vides
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels