Vous avez sans doute déjà passé de longues minutes à recopier une formule sur des centaines de lignes en tirant la petite poignée bleue en bas d'une cellule. Puis, dès qu'une ligne est ajoutée, il faut recommencer. La fonction ARRAYFORMULA de Google Sheets met fin à ce travail répétitif : une seule formule, placée dans une seule cellule, calcule le résultat pour une colonne entière et s'étend toute seule aux nouvelles lignes. Ce guide vous explique sa syntaxe, son application à une colonne complète, sa combinaison avec SI, RECHERCHEV et QUERY, le raccourci clavier qui l'insère automatiquement, ainsi que les erreurs les plus fréquentes.
Qu'est-ce qu'ARRAYFORMULA et quelle est sa syntaxe
La fonction ARRAYFORMULA transforme une formule classique, qui s'applique à une seule cellule, en une formule de matrice qui s'applique à toute une plage en une seule fois. Sa syntaxe est volontairement minimale : vous enveloppez simplement une expression entre les parenthèses.
=ARRAYFORMULA(expression_de_plage)L'unique argument, expression_de_plage, est une formule qui manipule des plages (comme B2:B) au lieu de cellules isolées (comme B2). C'est cette différence qui change tout : Google Sheets comprend qu'il doit calculer le résultat pour chaque ligne de la plage, puis renvoyer l'ensemble des résultats d'un coup.
Comparez les deux approches sur une multiplication entre une quantité (colonne B) et un prix unitaire (colonne C) :
// Approche classique, à recopier dans chaque cellule
=B2*C2
// Avec ARRAYFORMULA, une seule cellule pour toute la colonne
=ARRAYFORMULA(B2:B*C2:C)Avec ARRAYFORMULA, vous n'écrivez la formule qu'une seule fois, dans la première cellule de résultat. Les cellules en dessous restent vides : elles affichent les valeurs calculées, mais ne contiennent aucune formule. Cela allège considérablement le classeur.
Appliquer une formule à une colonne entière
Le cas d'usage le plus courant consiste à appliquer une opération à toute une colonne sans connaître à l'avance le nombre de lignes. La clé est d'utiliser une plage ouverte, notée B2:B, qui désigne « la colonne B à partir de la ligne 2 jusqu'en bas ».
Reprenons un tableau de ventes avec, en colonne B, des quantités et, en colonne C, des prix unitaires. Pour calculer le total de chaque ligne en colonne D, placez cette formule dans la cellule D2 :
=ARRAYFORMULA(B2:B*C2:C)La colonne D entière se remplit instantanément. Vous pouvez de la même manière concaténer un prénom et un nom, calculer une TVA ou convertir une devise :
// Nom complet à partir des colonnes A (prénom) et B (nom)
=ARRAYFORMULA(A2:A&" "&B2:B)
// Montant TTC à 20 % à partir d'un montant HT en colonne C
=ARRAYFORMULA(C2:C*1.2)Une plage ouverte comme A2:A&" "&B2:B couvre toutes les lignes, y compris les lignes vides en bas du tableau, ce qui peut afficher des espaces ou des zéros indésirables. Pour l'éviter, encadrez l'expression d'un test, par exemple =ARRAYFORMULA(SI(A2:A="";"";A2:A&" "&B2:B)), afin de ne calculer que les lignes réellement remplies.
ARRAYFORMULA avec SI : appliquer une condition sur toute la colonne
La fonction SI est l'une des plus utilisées de Google Sheets, et elle se marie parfaitement avec ARRAYFORMULA. L'idée est d'écrire la condition sur une plage plutôt que sur une cellule unique. Le test est alors évalué ligne par ligne, et le résultat est renvoyé pour toute la colonne.
Imaginons une colonne B contenant des notes sur 20. Pour afficher « Reçu » ou « Ajourné » dans la colonne C, une seule formule en C2 suffit :
=ARRAYFORMULA(SI(B2:B>=10; "Reçu"; "Ajourné"))Vous pouvez enchaîner plusieurs conditions avec SI imbriqués, ou recourir à SI.CONDITIONS (IFS) pour gagner en lisibilité :
// Attribution d'une mention selon la note
=ARRAYFORMULA(SI.CONDITIONS(B2:B>=16; "Très bien"; B2:B>=14; "Bien"; B2:B>=10; "Passable"; VRAI; "Insuffisant"))Pour ne pas calculer les lignes vides, ajoutez un premier test sur la cellule source : =ARRAYFORMULA(SI(B2:B=""; ""; SI(B2:B>=10; "Reçu"; "Ajourné"))). La colonne reste ainsi propre tant que vous n'avez pas saisi de note.
Vous voulez gagner du temps sur vos tableurs ?
Notre formation vous apprend ARRAYFORMULA, les fonctions matricielles et les automatisations Google Sheets, à distance ou en présentiel. Démarrage immédiat, paiement en 3× sans frais.
Combiner ARRAYFORMULA avec RECHERCHEV ou QUERY
La vraie puissance d'ARRAYFORMULA apparaît quand on l'associe à d'autres fonctions. Deux combinaisons reviennent en permanence dans les tableaux professionnels : avec RECHERCHEV et avec QUERY.
ARRAYFORMULA et RECHERCHEV
Normalement, une RECHERCHEV doit être recopiée dans chaque cellule. En l'enveloppant dans ARRAYFORMULA et en lui passant une plage comme valeur de recherche, vous la propagez à toute la colonne. Ici, on cherche dans une table Tarifs!A:B le prix associé à chaque référence de la colonne A :
=ARRAYFORMULA(RECHERCHEV(A2:A; Tarifs!A:B; 2; FAUX))Une seule formule remplit toute la colonne de prix, et chaque nouvelle référence ajoutée en colonne A reçoit automatiquement son tarif. Pour approfondir cette fonction, consultez notre guide de la fonction RECHERCHEV sur Google Sheets.
ARRAYFORMULA et QUERY
La fonction QUERY traite déjà des plages entières, elle n'a donc pas toujours besoin d'ARRAYFORMULA. En revanche, ARRAYFORMULA est utile pour préparer une colonne calculée avant de la passer à QUERY, par exemple pour créer une colonne « marge » à la volée. Le tableau ci-dessous résume quand utiliser chaque approche.
| Besoin | Fonction recommandée |
|---|---|
| Appliquer un calcul simple à toute une colonne | ARRAYFORMULA |
| Recopier une RECHERCHEV sur toute une colonne | ARRAYFORMULA + RECHERCHEV |
| Filtrer, grouper, trier et agréger des données | QUERY |
| Créer une colonne calculée puis l'analyser | ARRAYFORMULA en amont, QUERY ensuite |
| Extraction conditionnelle simple | FILTER |
Pour les analyses complexes (regroupements, totaux, pivots), reportez-vous à notre tutoriel sur la fonction QUERY ; pour les extractions conditionnelles simples, le guide de la fonction FILTER sera plus direct.
Le raccourci Ctrl+Maj+Entrée pour insérer ARRAYFORMULA
Inutile de taper le mot ARRAYFORMULA à la main : Google Sheets propose un raccourci qui l'ajoute automatiquement. Saisissez votre formule comme si elle portait sur des plages, puis validez avec la combinaison adaptée à votre système.
| Système | Raccourci |
|---|---|
| Windows et Chrome OS | Ctrl + Maj + Entrée |
| Mac | Cmd + Maj + Entrée |
Concrètement, tapez =B2:B*C2:C puis appuyez sur Ctrl+Maj+Entrée : la formule devient automatiquement =ARRAYFORMULA(B2:B*C2:C). C'est plus rapide, et cela évite les fautes de frappe sur un nom de fonction aussi long.
Ce raccourci fonctionne aussi pour transformer une formule déjà saisie. Placez le curseur dans la cellule, repassez en mode édition avec F2, puis validez avec Ctrl+Maj+Entrée pour l'envelopper dans ARRAYFORMULA sans tout réécrire.
Étendre automatiquement le calcul aux nouvelles lignes
L'avantage décisif d'ARRAYFORMULA sur une formule recopiée est sa capacité à s'étendre seule. Quand vous utilisez une plage ouverte comme B2:B, la formule couvre l'ensemble de la colonne, y compris les lignes qui n'existent pas encore.
Concrètement, si vous saisissez une nouvelle commande en ligne 501 d'un tableau qui en comptait 500, le total correspondant apparaît immédiatement, sans aucune manipulation. C'est idéal pour les formulaires Google Forms, dont les réponses s'ajoutent en continu dans une feuille : une seule ARRAYFORMULA en haut de la colonne calcule chaque nouvelle réponse.
// Total automatique pour chaque nouvelle ligne, sans limite haute
=ARRAYFORMULA(SI(B2:B=""; ""; B2:B*C2:C))Le test SI(B2:B="";"";…) est ici essentiel : il laisse les cellules vides tant qu'aucune donnée n'est saisie, tout en restant prêt à calculer dès qu'une ligne est ajoutée. C'est le compromis idéal entre automatisation et propreté visuelle.
Erreurs et pièges courants avec ARRAYFORMULA
La fonction est simple, mais quelques pièges reviennent souvent. Les connaître vous fera gagner un temps précieux.
- Plage fermée au lieu d'ouverte : écrire
B2:B100au lieu deB2:Blimite le calcul aux 100 premières lignes. La 101ᵉ ligne ne sera pas calculée. Préférez les plages ouvertes pour profiter de l'extension automatique. - L'erreur #REF! « le résultat n'a pas été développé » : elle survient quand une cellule située plus bas dans la colonne de sortie contient déjà une valeur.
ARRAYFORMULArefuse d'écraser des données. Videz la zone de sortie pour résoudre le conflit. - Lignes vides qui affichent des zéros : sans test
SI(plage="";"";…), les lignes vides produisent des 0 ou des espaces. Encadrez toujours l'expression d'un test sur la cellule source. - Fonction non compatible avec les tableaux : certaines fonctions n'acceptent qu'une seule valeur. Si rien ne se développe, vérifiez que chaque composant de la formule sait travailler sur une plage.
- Mélanger texte et nombres : une colonne hétérogène peut fausser les calculs. Nettoyez vos données ou ajoutez un test de type avant le calcul.
Une fois ces réflexes acquis, ARRAYFORMULA remplace des centaines de formules recopiées par une seule, plus rapide à maintenir et plus fiable. C'est exactement ce type de compétences que vous travaillez en profondeur dans notre formation Google Sheets certifiante, qui vous rend autonome sur le tableur, des fonctions de base jusqu'aux formules matricielles avancées.
Cette formation débouche sur une certification ICDL (Tableur) reconnue. Elle se suit à distance ou en présentiel, avec un démarrage immédiat et un paiement en 3 fois sans frais. Notre organisme est certifié Qualiopi (NDA 93 13 18605 13), gage de qualité reconnu par les financeurs.
