DECALER (OFFSET en anglais) est la fonction qui transforme une référence statique en référence vivante. Tu lui donnes un point de départ, tu lui indiques de combien de lignes et de colonnes se déplacer, et elle te renvoie la plage résultante, que tu peux même redimensionner à la volée. Contrairement à A1:A10 qui pointe toujours vers les mêmes cellules, DECALER calcule sa position et sa taille en temps réel.
Concrètement, c'est elle qui permet de créer un graphique des ventes qui se met à jour automatiquement chaque semaine sans que tu aies à retoucher sa source, d'afficher toujours les 3 derniers mois de chiffre d'affaires dans un tableau de bord, de calculer une moyenne mobile qui glisse avec les nouvelles données, ou de construire un rapport interactif où l'utilisateur choisit lui-même le nombre de périodes à analyser.
Syntaxe de la fonction DECALER
=DECALER(réf; lignes; colonnes; [hauteur]; [largeur])DECALER est une fonction volatile : elle recalcule à chaque modification de la feuille, même si ses cellules d'entrée n'ont pas changé. Sur de gros fichiers avec de nombreuses formules DECALER, cela peut ralentir les calculs. Si tu n'as pas besoin d'une plage qui change de taille, INDEX/EQUIV est plus performant.
Comprendre chaque paramètre de la fonction DECALER
réf
: la cellule ou plage de référence de départ, le point d'ancrage à partir duquel DECALER va calculer le décalageÇa peut être une cellule unique comme A1 ou une plage comme A1:B5. Si tu utilises une plage, DECALER prend la cellule en haut à gauche comme point de départ.
Utilise une référence absolue comme $A$1 si tu prévois de copier la formule : cela évite que ton point d'ancrage se déplace avec la copie.
Astuce : Pour les plages nommées dynamiques utilisées dans des graphiques, ancre toujours la référence de départ avec $ : =DECALER($A$1;1;0;NB.VAL($B:$B);2). Ton graphique référencera le nom de cette plage et s'adaptera automatiquement.
lignes
: le nombre de lignes à décaler par rapport à la référenceUn nombre positif déplace vers le bas, un nombre négatif vers le haut. 2 signifie « descends de 2 lignes », -1 signifie « monte d'une ligne ». Utilise 0 si tu ne veux pas de décalage vertical.
Ce paramètre peut être le résultat d'un calcul. Par exemple, NB.VAL(B:B)-3 positionne la plage 3 lignes avant la dernière valeur de la colonne B.
colonnes
: le nombre de colonnes à décalerUn nombre positif déplace vers la droite, un nombre négatif vers la gauche. 3 signifie « va 3 colonnes à droite », -2 signifie « recule de 2 colonnes ». Utilise 0 pour rester dans la même colonne.
[hauteur]
: le nombre de lignes que doit couvrir la plage résultante(facultatif)Si tu omets ce paramètre, DECALER conserve la hauteur de ta référence de départ.
C'est ici que la magie opère : en utilisant NB.VAL(A:A) ou NB.VAL(A:A)-1 (pour sauter l'en-tête), tu crées une plage qui s'étend automatiquement jusqu'à la dernière valeur saisie.
Astuce : Protège-toi contre le cas où la colonne est vide en enveloppant avec MAX : MAX(NB.VAL(A:A)-1;1). Cela évite que hauteur vaille 0 ou moins, ce qui provoquerait une erreur.
[largeur]
: le nombre de colonnes que doit couvrir la plage résultante(facultatif)Si tu l'omets, DECALER conserve la largeur de ta référence. Combine ce paramètre avec hauteur pour redimensionner complètement la plage de façon dynamique.
Très utile pour sélectionner automatiquement un bloc de données dont le nombre de colonnes varie selon un paramètre.
Pas envie d'écrire la formule DECALER à la main ?
Génère-la avec notre IA
Exemples pratiques pas à pas
Analyste financier : créer une plage dynamique pour graphique
Tu es analyste financier et tu dois créer un graphique des ventes qui se met à jour automatiquement quand de nouvelles données sont ajoutées chaque semaine. Au lieu de modifier manuellement la source du graphique, tu utilises DECALER pour créer une plage nommée dynamique.
| A | B | |
|---|---|---|
| 1 | Semaine | Ventes (k€) |
| 2 | S1 | 45 |
| 3 | S2 | 52 |
| 4 | S3 | 48 |
| 5 | S4 | 61 |
| 6 | S5 | 55 |
=DECALER($A$1;1;0;NB.VAL($B:$B);2)La formule part de A1, descend d'une ligne pour sauter l'en-tête, ne bouge pas en colonnes, puis prend autant de lignes qu'il y a de valeurs dans la colonne B sur 2 colonnes de large. La plage renvoyée s'étend donc toute seule (A2:B6 aujourd'hui) à chaque semaine ajoutée.
Astuce de pro : Une plage nommée basée sur DECALER est la technique classique pour des graphiques dynamiques. Elle est plus fiable qu'un tableau Excel formaté car elle fonctionne dans toutes les versions et avec n'importe quel type de graphique.
Contrôleur de gestion : afficher toujours les N dernières valeurs
Tu es contrôleur de gestion et tu veux créer un tableau de bord qui affiche toujours les 3 derniers mois de chiffre d'affaires, peu importe combien de mois sont déjà saisis. DECALER te permet d'extraire dynamiquement cette fenêtre glissante.
| A | B | C | |
|---|---|---|---|
| 1 | Mois | CA (k€) | Top 3 |
| 2 | Jan | 125 | |
| 3 | Fév | 138 | |
| 4 | Mar | 142 | |
| 5 | Avr | 151 | 151 |
| 6 | Mai | 148 | 148 |
| 7 | Jun | 159 | 159 |
=DECALER(B$1;NB.VAL(B:B)-3;0;3;1)La formule compte les valeurs de la colonne B, se positionne 3 lignes avant la fin, puis renvoie une fenêtre de 3 lignes sur 1 colonne (ici B4:B6). Cette fenêtre glissante suit toujours les 3 derniers mois saisis ; remplace le 3 pour changer le nombre de périodes.
Data analyst : calculer une moyenne mobile sur 5 périodes
Tu es data analyst et tu dois identifier les tendances dans les données de trafic web en calculant une moyenne mobile. DECALER crée automatiquement une fenêtre glissante de 5 périodes qui se déplace avec tes données.
| A | B | C | |
|---|---|---|---|
| 1 | Mois | Visiteurs | Moy. 5 mois |
| 2 | Jan | 12500 | |
| 3 | Fév | 13200 | |
| 4 | Mar | 11800 | |
| 5 | Avr | 14100 | |
| 6 | Mai | 13400 | 13000 |
| 7 | Jun | 15200 | 13540 |
| 8 | Jul | 14800 | 13860 |
=MOYENNE(DECALER(B2;LIGNE()-6;0;5;1))Ici, la formule fait glisser une fenêtre de 5 lignes grâce au numéro de la ligne courante : sur la ligne de juin, elle pointe la plage B2:B6 et MOYENNE en renvoie la moyenne (13 540). Recopiée vers le bas, la fenêtre suit chaque mois pour lisser la tendance du trafic.
Manager commercial : rapport interactif avec nombre de jours variable
Tu es manager commercial et tu veux créer un rapport qui montre toujours les performances des X derniers jours, où X est choisi par l'utilisateur via une cellule de contrôle. DECALER combiné avec une cellule input te donne cette flexibilité.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Période | 7 jours | ||
| 2 | Jour | Ventes (€) | ||
| 3 | Lun | 2400 | Dern. 7j | |
| 4 | Mar | 2650 | visible | |
| 5 | Mer | 2200 | selon | |
| 6 | Jeu | 2350 | cellule B1 | |
| 7 | Ven | 2450 | (7 jours) | |
| 8 | Sam | 2700 | ||
| 9 | Dim | 2800 |
=SOMME(DECALER(B2;NB.VAL(B:B)-$B$1;0;$B$1;1))La formule lit le nombre de jours en B1, se positionne d'autant de lignes avant la fin et délimite une fenêtre de cette hauteur, que SOMME additionne (17 550 € sur 7 jours). Change la valeur de B1 (7, 14, 30…) et le total se recalcule instantanément.
Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
Les erreurs fréquentes avec la fonction DECALER
#REF! : référence qui sort des limites de la feuille
Le décalage ou les dimensions calculées pointent en dehors des limites de la feuille. Cela arrive souvent quand NB.VAL renvoie une valeur trop grande ou quand le décalage est négatif depuis la ligne 1.
Solution : Utilise MIN et MAX pour borner tes décalages : =DECALER(A1;MIN(lignes;1000);0). Pour la hauteur, garantis une valeur minimale de 1 : =DECALER(A1;0;0;MAX(NB.VAL(A:A)-1;1);1).
#VALEUR! : arguments non numériques
Les paramètres lignes, colonnes, hauteur et largeur doivent être des nombres. Si une fonction de comptage comme NB.VAL retourne du texte, ou si une référence de cellule contient du texte, DECALER ne peut pas calculer.
Solution : Vérifie que tes fonctions de comptage retournent bien des entiers. Utilise ESTNUM() pour tester une valeur si tu as un doute, ou enveloppe avec CNUM() pour forcer la conversion.
Hauteur ou largeur nulle ou négative
Les paramètres hauteur et largeur doivent être des entiers positifs. Si NB.VAL renvoie 0 (colonne vide) ou 1 (seulement l'en-tête), le calcul -1 peut donner 0 ou un nombre négatif.
Solution : Enveloppe avec MAX : =DECALER(A1;0;0;MAX(NB.VAL(A:A)-1;1);1). La valeur minimale garantie est 1, ce qui évite l'erreur même quand la colonne ne contient que l'en-tête.
Fichier qui ralentit à chaque frappe
DECALER est volatile : elle recalcule à chaque modification de la feuille, même si ses dépendances directes n'ont pas changé. Beaucoup de formules DECALER dans un fichier volumineux peuvent causer des ralentissements perceptibles.
Solution : Limite DECALER aux cas où tu as vraiment besoin de plages dynamiques. Pour les recherches ponctuelles de valeurs, utilise INDEX/EQUIV qui ne sont pas volatiles. Tu peux aussi passer en calcul manuel (Formules > Options de calcul > Manuel) et déclencher les calculs avec F9 uniquement quand nécessaire.
Tu cherches surtout à corriger l'erreur #REF! affichée dans ta cellule, sans passer par la fonction DECALER ? Consulte la fiche dédiée à l'erreur #REF! pour comprendre toutes ses causes et comment la corriger.
DECALER vs INDEX vs INDIRECT
DECALER est la seule des trois à pouvoir retourner une plage redimensionnable. Mais cette puissance a un coût : la volatilité. INDEX reste le meilleur choix pour extraire des valeurs ponctuelles, INDIRECT pour les références textuelles.
| Critère | DECALER | INDEX | INDIRECT |
|---|---|---|---|
| Type de retour | Référence (valeur ou plage) | Valeur | Référence (valeur ou plage) |
| Plages redimensionnables | ✅ Oui (hauteur, largeur) | ❌ Non | ❌ Non |
| Graphiques dynamiques | ✅ Parfait | ⚠️ Possible avec NBVAL | ✅ Oui |
| Volatile | ⚠️ Oui | ✅ Non | ⚠️ Oui |
| Performance | ⚠️ Ralentit sur gros fichiers | ✅ Très rapide | ⚠️ Ralentit sur gros fichiers |
| Cas d'usage principal | Plages dynamiques, moyennes mobiles | Extraction de valeurs, EQUIV | Dashboards avec sélection de feuille |
Astuces avancées avec DECALER
Nomme tes plages DECALER pour simplifier tes formules
Au lieu d'écrire =SOMME(DECALER($A$1;1;0;NB.VAL($A:$A)-1;1)) partout, crée une plage nommée (Ctrl+F3) avec cette formule DECALER et appelle-la VentesDynamiques. Tes autres formules deviennent lisibles : =SOMME(VentesDynamiques), =MOYENNE(VentesDynamiques).
Si la structure de ton tableau change, tu modifies la définition de la plage nommée une seule fois, et toutes les formules qui l'utilisent se mettent à jour.
Combine DECALER avec NB.VAL pour des graphiques auto-extensibles
La combinaison =DECALER($A$1;1;0;NB.VAL($B:$B);2) dans une plage nommée est la recette classique pour un graphique qui s'étend automatiquement. NB.VAL($B:$B) compte les valeurs numériques dans la colonne B, et DECALER sélectionne exactement ce nombre de lignes.
Chaque fois que tu ajoutes une semaine de données, le graphique intègre la nouvelle ligne sans que tu aies à modifier quoi que ce soit.
Utilise des références absolues pour l'ancre de départ
Quand tu crées des formules DECALER que tu comptes copier vers le bas ou vers la droite, la référence de départ (réf) doit être absolue : $A$1 et non A1. Sinon, l'ancre se déplace avec la copie et DECALER calcule depuis un mauvais point de départ.
Les paramètres lignes et colonnes peuvent eux rester relatifs si leur valeur doit s'adapter à chaque ligne.
Questions fréquentes sur la fonction DECALER
Quelle est la différence entre DECALER et INDEX ?
DECALER retourne une référence de cellule ou de plage dynamique, tandis qu'INDEX retourne la valeur d'une cellule spécifique. DECALER est idéal pour créer des plages nommées dynamiques qui s'ajustent automatiquement, alors qu'INDEX est parfait pour extraire des valeurs précises d'un tableau.
Utilise DECALER quand tu as besoin d'une référence qui change de taille ou de position, notamment pour des graphiques. Préfère INDEX/EQUIV pour des recherches de valeurs car il ne recalcule pas inutilement.
Comment utiliser DECALER pour créer une moyenne mobile ?
Utilise =MOYENNE(DECALER(B2;LIGNE()-6;0;5;1)) pour calculer la moyenne des 5 dernières valeurs. LIGNE() renvoie le numéro de la ligne courante, et la soustraction ajuste le point de départ à mesure que tu copies la formule vers le bas.
Cette technique est parfaite pour analyser les tendances dans des données de ventes ou de performance en lissant les variations ponctuelles.
Pourquoi DECALER est-elle considérée comme une fonction volatile ?
DECALER recalcule à chaque modification de la feuille, même si les cellules qu'elle référence n'ont pas changé directement. Excel ne peut pas savoir à l'avance si le changement affecte le résultat, donc il recalcule par précaution.
Cela peut ralentir les fichiers volumineux avec beaucoup de formules DECALER. Dans ce cas, INDEX/EQUIV offre de meilleures performances pour extraire des valeurs ponctuelles.
Peut-on utiliser DECALER avec des plages non contiguës ?
Non, DECALER retourne toujours une plage rectangulaire continue. Les paramètres hauteur et largeur définissent les dimensions de cette plage depuis le point de départ décalé.
Si tu as besoin de sélectionner des plages non contiguës, tu devras utiliser plusieurs formules DECALER séparées et les combiner, par exemple avec SOMMEPROD.
Comment combiner DECALER avec NB.VAL pour une plage dynamique ?
Utilise =SOMME(DECALER(A1;0;0;NB.VAL(A:A);1)) pour créer une somme qui s'étend automatiquement jusqu'à la dernière valeur numérique de la colonne A, sans inclure les cellules vides.
C'est parfait pour des bases de données qui s'agrandissent régulièrement. Pense à soustraire 1 si la colonne a un en-tête : NB.VAL(A:A)-1.
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