ARRAYFORMULA est la fonction qui change tout sur Google Sheets : elle applique une formule à une colonne entière d'un seul coup. Au lieu de recopier ta formule sur des centaines de lignes, tu l'écris une fois, en haut, et elle s'occupe du reste.
Mieux : quand de nouvelles lignes arrivent (une commande, une vente, un lead), le calcul se fait tout seul, sans que tu aies à étirer quoi que ce soit. C'est ce qui transforme un simple tableur en système qui tourne sans entretien.
Syntaxe de la fonction ARRAYFORMULA
=ARRAYFORMULA(formule_tableau)La formule passée à ARRAYFORMULA doit référencer des plages (par ex. A2:A) et non une cellule unique : c'est ce qui déclenche la propagation sur toute la colonne.
Comprendre chaque paramètre de la fonction ARRAYFORMULA
formule_tableau
: la formule que tu veux appliquer à une plage entièreAu lieu de référencer une cellule unique comme A2, tu références une plage comme A2:A1000, et ARRAYFORMULA exécute ta formule sur chaque ligne automatiquement.
Elle accepte la plupart des types de formules : les opérations mathématiques (A2:A * B2:B, A2:A + 100), la concaténation (A2:A & " " & B2:B), les fonctions conditionnelles (SI(A2:A > 100; "Élevé"; "Normal")), les fonctions texte (MAJUSCULE(A2:A), GAUCHE(A2:A; 5)) et les fonctions de date (MOIS(A2:A), ANNEE(B2:B)).
Les fonctions d'agrégation comme SOMME ou MOYENNE font exception : enveloppées dans ARRAYFORMULA, elles renvoient un total global et non un résultat par ligne.
Astuce : Utilise des plages ouvertes comme A2:A (sans préciser la dernière ligne) pour que ARRAYFORMULA s'adapte automatiquement quand tu ajoutes de nouvelles données. C'est idéal pour les bases de données qui évoluent.
Attention : ARRAYFORMULA remplit toutes les cellules sous ta formule. Si ces cellules contiennent déjà des données (titres, totaux, notes), elles sont écrasées sans avertissement. Place toujours la formule dans une colonne vide.
Pas envie d'écrire la formule ARRAYFORMULA à la main ?
Génère-la avec notre IA
Exemples pratiques pas à pas
Directeur commercial : calculer les commissions de toute l'équipe
Tu es directeur commercial et tu dois calculer les commissions mensuelles de toute ton équipe. Plutôt que de saisir chaque commission ligne par ligne, ARRAYFORMULA te permet de tout calculer en une seule formule placée tout en haut de la colonne.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Vendeur | Ventes du mois | Commission 8 % | Bonus performance |
| 2 | Marie Dubois | 45 000 € | 3 600 € | 500 € |
| 3 | Pierre Martin | 32 500 € | 2 600 € | 500 € |
| 4 | Julie Bernard | 28 000 € | 2 240 € | 0 € |
| 5 | Lucas Petit | 51 200 € | 4 096 € | 500 € |
| 6 | Sophie Durand | 19 800 € | 1 584 € | 0 € |
=ARRAYFORMULA(B2:B * 0,08)Placée une seule fois en haut de la colonne, la fonction applique le taux de 8 % à chaque vente de la plage d'un coup. Chaque nouveau vendeur que tu ajoutes reçoit automatiquement son calcul, sans aucune recopie.
Gestion catalogue : appliquer la bonne TVA à chaque produit
Tu gères un catalogue produits avec plusieurs taux de TVA selon les catégories. ARRAYFORMULA applique le bon taux à chaque ligne instantanément, même si les produits n'ont pas tous le même taux.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Produit | Prix HT | Taux TVA | TVA | Prix TTC |
| 2 | Livre numérique | 15,00 € | 5,5 % | 0,83 € | 15,83 € |
| 3 | Ordinateur portable | 850,00 € | 20 % | 170,00 € | 1 020,00 € |
| 4 | Produit alimentaire | 12,50 € | 5,5 % | 0,69 € | 13,19 € |
| 5 | Accessoire high-tech | 45,00 € | 20 % | 9,00 € | 54,00 € |
=ARRAYFORMULA(B2:B * C2:C)Ici, la fonction multiplie le prix HT de chaque produit par son propre taux de TVA, ligne à ligne, même si les taux diffèrent d'une catégorie à l'autre. Plus besoin de recopier la formule sur des centaines de lignes.
Ressources humaines : concaténer prénoms et noms en masse
Tu travailles aux ressources humaines et tu as importé une base avec les prénoms et noms dans des colonnes séparées. Tu veux reconstituer automatiquement le nom complet de chaque employé sans toucher à chaque ligne.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Prénom | Nom | Nom complet | Email professionnel |
| 2 | Marie | Dupont | Marie Dupont | marie.dupont@entreprise.com |
| 3 | Pierre | Martin | Pierre Martin | pierre.martin@entreprise.com |
| 4 | Julie | Bernard | Julie Bernard | julie.bernard@entreprise.com |
| 5 | Lucas | Petit | Lucas Petit | lucas.petit@entreprise.com |
=ARRAYFORMULA(A2:A & " " & B2:B)La fonction colle le prénom et le nom de chaque ligne avec un espace au milieu, pour toute la liste d'un coup. Chaque nouvel employé ajouté voit son nom complet se générer tout seul.
Contrôle budgétaire : calculer un total cumulé mensuel
Tu gères le budget de ton service et tu veux suivre le cumul des dépenses mois après mois, sans saisir une formule par ligne. ARRAYFORMULA peut générer toute la colonne de totaux cumulés (running totals) d'un seul coup.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Mois | Dépenses | Total cumulé | Budget annuel restant |
| 2 | Janvier | 8 500 € | 8 500 € | 91 500 € |
| 3 | Février | 7 200 € | 15 700 € | 84 300 € |
| 4 | Mars | 9 800 € | 25 500 € | 74 500 € |
| 5 | Avril | 6 900 € | 32 400 € | 67 600 € |
| 6 | Mai | 10 500 € | 42 900 € | 57 100 € |
=ARRAYFORMULA(SI(B2:B=""; ""; MMULT(TRANSPOSE(TRANSPOSE(B2:B)*(COLONNE(B2:B)>=TRANSPOSE(COLONNE(B2:B)))); LIGNE(B2:B)^0)))La formule combine MMULT, TRANSPOSE et COLONNE pour additionner progressivement chaque dépense aux précédentes, tout en laissant vides les lignes sans données grâce au test sur la cellule source. C'est l'une des techniques les plus avancées d'ARRAYFORMULA.
Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
Les erreurs fréquentes avec la fonction ARRAYFORMULA
Presque tous les ratés d'ARRAYFORMULA tournent autour d'une seule chose : la façon dont elle propage le calcul ligne par ligne. Dès que tes plages n'ont pas la même longueur, tu récoltes des #N/A sur les lignes orphelines ; et comme elle remplit tout ce qui est en dessous, elle peut écraser tes titres ou tes totaux sans rien te demander.
L'autre faux pas classique, c'est de glisser un SOMME ou une MOYENNE dedans en espérant un résultat par ligne : ces fonctions regroupent les données et te renvoient un total unique.
Tailles de plages incompatibles
Si tu combines plusieurs plages de tailles différentes (par exemple A2:A10 * B2:B20), ARRAYFORMULA renvoie #N/A pour les cellules sans correspondance. C'est l'erreur la plus courante avec cette fonction.
Solution : Solution : assure-toi que toutes les plages référencées ont exactement la même taille. Le plus simple est d'utiliser des plages ouvertes (A2:A) qui s'ajustent seules, ou de fixer la même fin partout (A2:A100 * B2:B100).
La formule écrase les cellules en dessous
ARRAYFORMULA remplit automatiquement toutes les cellules sous la formule. Si elles contiennent déjà des données (titres, totaux, notes, commentaires), elles sont écrasées sans avertissement.
Solution : Solution : place toujours ta formule dans une colonne vide, ou borne la propagation avec LIGNE() : =ARRAYFORMULA(SI(LIGNE(A2:A)<=100; A2:A*B2:B; "")) limite le calcul aux 100 premières lignes.
Résultats parasites sur les lignes vides
Si certaines lignes de la plage source sont vides, ARRAYFORMULA peut renvoyer 0, des cellules vides ou des erreurs pour ces lignes, ce qui rend le tableau incohérent et difficile à lire.
Solution : Solution : encadre la formule d'un test sur la cellule source : =ARRAYFORMULA(SI(A2:A=""; ""; ta_formule)). Les lignes vides restent ainsi vides, sans 0 ni erreur.
Une fonction d'agrégation ne se calcule pas ligne par ligne
Les fonctions comme SOMME, MOYENNE, MAX ou MIN ne fonctionnent pas par ligne dans ARRAYFORMULA. =ARRAYFORMULA(SOMME(A2:A)) renvoie un seul total global, pas une somme par ligne.
Solution : Solution : pour additionner plusieurs colonnes ligne par ligne, écris l'opération directement : =ARRAYFORMULA(A2:A + B2:B + C2:C). Pour des calculs plus complexes, oriente-toi vers SOMMEPROD ou QUERY.
ARRAYFORMULA vs formules manuelles vs Excel 365 dynamique
ARRAYFORMULA brille sur Google Sheets pour automatiser des calculs sans écrire de code. Sur Excel 365, les formules dynamiques offrent la même puissance sans syntaxe spéciale ; sur Excel classique (2016, 2019), seule la recopie manuelle est possible.
| Critère | ARRAYFORMULA | Formules manuelles | Excel 365 dynamique |
|---|---|---|---|
| Vitesse de configuration | Instantané | Lent (copier-coller) | Instantané |
| Adaptation aux nouvelles lignes | Oui (plages ouvertes) | Non (recopie manuelle) | Oui (natif) |
| Maintenance | 1 seule formule à modifier | Chaque cellule à modifier | 1 seule formule |
| Performance sur grandes données | Correct (< 50k lignes) | Très bon | Excellent |
| Disponibilité | Google Sheets | Partout | Excel 365 |
| Complexité d'apprentissage | Moyenne | Facile | Facile (automatique) |
ARRAYFORMULA vs MAP vs BYROW vs SCAN vs REDUCE
Sur Excel 365, plusieurs fonctions à base de LAMBDA recouvrent les usages d'ARRAYFORMULA en offrant plus de souplesse. Voici à quoi sert chacune et son avantage face à ARRAYFORMULA.
| Fonction | Utilisation principale | Avantage vs ARRAYFORMULA |
|---|---|---|
| MAP | Appliquer une LAMBDA personnalisée sur un tableau | Plus flexible, logiques complexes réutilisables |
| BYCOL | Appliquer une fonction sur chaque colonne | Spécialisée par colonne, plus lisible |
| BYROW | Appliquer une fonction sur chaque ligne | Spécialisée par ligne, idéale pour agréger |
| SCAN | Totaux cumulés et calculs séquentiels | Bien plus simple pour les running totals |
| REDUCE | Réduire un tableau à une valeur unique | Idéale pour des agrégations sur mesure |
Questions fréquentes sur la fonction ARRAYFORMULA
ARRAYFORMULA existe-t-elle dans Excel ?
Non, ARRAYFORMULA est exclusive à Google Sheets. Excel 365 propose des formules dynamiques équivalentes qui se propagent automatiquement, sans fonction spécifique à écrire.
Si tu utilises Excel classique (2016, 2019), cette mécanique n'existe pas : tu dois recopier ta formule sur chaque ligne ou passer par d'autres approches.
Puis-je combiner ARRAYFORMULA avec d'autres fonctions ?
Oui, c'est tout son intérêt. ARRAYFORMULA peut envelopper la quasi-totalité des formules de Google Sheets. C'est particulièrement utile avec SI, CONCATENER, TEXTE et les opérations mathématiques.
Fais juste attention aux fonctions d'agrégation comme SOMME ou MOYENNE : elles regroupent les données et demandent une approche différente si tu veux un résultat par ligne.
Comment savoir si ma formule ARRAYFORMULA fonctionne ?
Si tout va bien, les résultats se remplissent d'eux-mêmes sur toutes les lignes concernées : tu vois les valeurs calculées apparaître instantanément.
Si tu vois #N/A ou des cellules vides inattendues, vérifie d'abord que toutes tes plages ont la même taille : c'est la cause la plus fréquente.
ARRAYFORMULA ralentit-elle mes feuilles de calcul ?
Sur de très grandes plages (plus de 50 000 lignes), elle peut ralentir légèrement le recalcul. Le moteur doit en effet réévaluer tout le tableau à chaque changement.
Pour la plupart des usages professionnels (moins de 10 000 lignes), la différence est négligeable et largement compensée par le temps gagné en configuration et en maintenance.
Que se passe-t-il si j'ajoute de nouvelles lignes à ma source ?
Si tu utilises des plages ouvertes (comme A2:A au lieu de A2:A100), ARRAYFORMULA s'adapte automatiquement aux nouvelles lignes. C'est la meilleure pratique pour les bases de données évolutives.
Avec une plage fermée, tu devras au contraire élargir la plage à la main dans ta formule pour inclure les nouvelles données.
Comment éviter les résultats sur les lignes vides ?
Enveloppe ta formule dans un test SI sur la cellule source : =ARRAYFORMULA(SI(A2:A=""; ""; A2:A*B2:B)). Tant que la ligne source est vide, le résultat reste vide.
C'est la technique de référence pour garder un tableau propre, sans 0 ni erreur sur les lignes encore vides en bas de la plage.
Pour aller plus loin
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois