En bref : sous-totaux Excel, ce qu’il faut savoir
Excel propose deux approches distinctes pour calculer des sous-totaux : une fonction de formule et un outil automatique accessible depuis l’onglet Données. Maîtriser les deux vous fait gagner un temps considérable sur l’analyse de grands volumes de données.
- ✅ La fonction SOUS.TOTAL accepte 11 codes de calcul différents (somme, moyenne, comptage…) et ignore automatiquement les lignes masquées par un filtre.
- L’outil Données > Sous-total génère des regroupements en quelques clics, sans écrire une seule formule : idéal pour segmenter un tableau par commercial, région ou catégorie.
- 🎯 Contrairement à SOMME, SOUS.TOTAL ne double pas les calculs lorsqu’il rencontre d’autres sous-totaux dans la plage : vos totaux généraux restent toujours justes.
- Combiner SOUS.TOTAL et SI permet d’appliquer des conditions avancées sur des données filtrées, là où une simple SOMME.SI échoue.
- ⚠️ La majorité des erreurs viennent d’un tableau non trié avant insertion des sous-totaux automatiques : un détail qui fausse toute l’analyse.
- L’article révèle aussi pourquoi les sous-totaux dans un tableau croisé dynamique obéissent à des règles différentes, souvent source de confusion.
La fonction SOUS.TOTAL Excel : syntaxe et codes de calcul
La fonction SOUS.TOTAL est l’outil central pour tout calcul conditionnel dans Excel. Elle combine flexibilité et intelligence : elle s’adapte aux filtres, ignore les doublons de calcul et couvre 11 opérations statistiques différentes.
Structure de la formule SOUS.TOTAL
La syntaxe est volontairement simple. Elle repose sur deux arguments obligatoires :
=SOUS.TOTAL(no_fonction ; réf1 ; [réf2] ; …)
- no_fonction : un code numérique entre 1 et 111 qui détermine le type de calcul.
- réf1 : la plage de cellules sur laquelle appliquer le calcul.
- Les arguments réf2 et suivants sont optionnels pour couvrir plusieurs plages.
Le point clé que beaucoup ratent : le choix entre les codes 1-11 et 101-111. Les codes 1-11 incluent les lignes masquées manuellement. Les codes 101-111 les excluent systématiquement, y compris celles cachées via un filtre. En pratique, selon cleex.fr, les codes 101-111 sont les plus utiles dans un contexte de tableau filtré, car ils calculent uniquement sur les données visibles.
Autre avantage décisif : lorsque votre plage contient déjà des formules SOUS.TOTAL imbriquées, Excel les ignore automatiquement. Vos totaux généraux ne gonflent jamais artificiellement.
Les 11 codes de fonction : somme, moyenne, comptage et plus
Voici le tableau complet des codes disponibles. Chaque opération a son équivalent « données visibles uniquement » :
| Code (tout) | Code (visible) | Opération | Équivalent Excel | Usage fréquent |
|---|---|---|---|---|
| 1 | 101 | Moyenne | MOYENNE | 🟡 Partiel |
| 2 | 102 | Nombre de valeurs numériques | NB | ✅ Très courant |
| 3 | 103 | Nombre de cellules non vides | NBVAL | ✅ Très courant |
| 4 | 104 | Maximum | MAX | 🟡 Partiel |
| 5 | 105 | Minimum | MIN | 🟡 Partiel |
| 6 | 106 | Produit | PRODUIT | ❌ Rare |
| 7 | 107 | Écart-type échantillon | ECARTYPE | ❌ Rare |
| 8 | 108 | Écart-type population | ECARTYPEP | ❌ Rare |
| 9 | 109 | Somme | SOMME | 🔥 Indispensable |
| 10 | 110 | Variance échantillon | VAR | ❌ Rare |
| 11 | 111 | Variance population | VARP | ❌ Rare |
Concrètement, dans 90% des cas terrain, trois codes suffisent : 9 pour additionner, 2 pour compter des montants et 3 pour compter des libellés. Pour approfondir la logique de cette fonction, consultez ce guide dédié sur la fonction SOUS.TOTAL qui détaille chaque cas d’usage avec des exemples concrets.
💡 Un exemple direct : =SOUS.TOTAL(109;C2:C500) calcule la somme uniquement sur les lignes visibles après filtrage de votre tableau commercial. Résultat : vous obtenez le chiffre d’affaires réel du segment sélectionné, sans recalculer manuellement.
Comment insérer des sous-totaux Excel avec l’outil Données
L’outil Données > Sous-totaux d’Excel est souvent sous-exploité. Pourtant, il automatise en quelques clics ce que beaucoup font encore à la main. Voici comment l’utiliser correctement, sans piège.
Préparer et trier vos données avant d’insérer les sous-totaux
C’est l’étape que la majorité des utilisateurs rate. Excel exige un tri préalable sur la colonne de regroupement, sinon il crée autant de groupes que de lignes.
Concrètement, si vous voulez des sous-totaux par région commerciale, triez d’abord votre tableau sur la colonne « Région ». Sans ce tri, vous obtiendrez dix lignes « Île-de-France » éparpillées dans dix groupes différents, chacun avec son propre sous-total. Un résultat inutilisable.
- ✅ Vérifiez que votre plage dispose d’une ligne d’en-tête bien définie.
- ✅ Supprimez les lignes vides intercalées dans vos données.
- ✅ Triez sur la colonne de regroupement (Données > Trier) avant toute manipulation.
Configurer la boîte de dialogue : à chaque changement de groupe
Accédez à Données > Plan > Sous-total. La boîte de dialogue qui s’ouvre pilote l’intégralité de la logique de calcul.
Trois paramètres clés à renseigner :
- À chaque changement de : choisissez la colonne de regroupement (ex. : « Région »).
- Utiliser la fonction : sélectionnez Somme, Moyenne, Nombre, etc.
- Ajouter un sous-total à : cochez la ou les colonnes à calculer (ex. : « CA HT »).
Deux options méritent attention. « Remplacer les sous-totaux existants » écrase les calculs précédents : décochez-la si vous empilez plusieurs niveaux de regroupement. « Synthèse sous les données » place chaque total en bas du groupe, ce qui est le comportement attendu dans 99 % des cas terrain.
Selon cleex.fr, Excel génère automatiquement un plan hiérarchique à gauche de la feuille, avec des boutons numérotés (1, 2, 3) pour afficher ou masquer les niveaux de détail. 🎯 Ce plan est l’un des vrais gains de productivité de cette fonctionnalité.
Supprimer les sous-totaux Excel en un clic
Retournez dans Données > Plan > Sous-total, puis cliquez sur « Supprimer tout ». Excel efface les lignes de sous-totaux et dégroupe le plan en une seule opération. Votre tableau revient à son état d’origine, sans manipulation supplémentaire.
En pratique, cette suppression est indispensable avant toute nouvelle utilisation avancée de la fonction SOUS.TOTAL ou avant d’importer les données dans un autre outil de reporting.
Sous-totaux Excel avec filtres et conditions avancées
Filtrer des données change tout. La fonction SOUS.TOTAL a été précisément conçue pour s’adapter dynamiquement aux lignes visibles, là où SOMME ou MOYENNE restent aveugles aux filtres appliqués.
Formule SOUS.TOTAL avec filtre : ignorer les lignes masquées
Quand vous appliquez un filtre automatique sur votre tableau, SOUS.TOTAL recalcule instantanément en tenant compte uniquement des lignes affichées. C’est son avantage décisif sur les fonctions standards.
Deux séries de codes pilotent ce comportement :
- Codes 1 à 11 : ignorent les lignes masquées manuellement, mais incluent les lignes masquées par un filtre automatique.
- ✅ Codes 101 à 111 : ignorent toutes les lignes masquées, qu’elles soient filtrées ou masquées manuellement.
En pratique, pour un tableau filtré dynamiquement, utilisez systématiquement les codes 100+. Exemple concret : pour sommer les ventes d’une région sélectionnée via un filtre, saisissez =SOUS.TOTAL(109;C2:C500) plutôt que 9. Selon cleex.fr, cette distinction entre les deux séries de codes est l’une des sources d’erreur les plus fréquentes chez les utilisateurs intermédiaires d’Excel.
| Situation | Code recommandé | Lignes filtrées ignorées | Lignes masquées ignorées |
|---|---|---|---|
| Filtre automatique actif | 109 (SOMME) | ✅ | ✅ |
| Masquage manuel uniquement | 9 (SOMME) | ❌ | ✅ |
| Aucun masquage | 9 ou 109 | 🟡 Identique | 🟡 Identique |
Ce tableau résume la logique à retenir avant de choisir votre code de calcul.
Sous-total Excel avec 2 conditions : combiner SOUS.TOTAL et SI
La fonction SOUS.TOTAL ne gère pas nativement les conditions. Elle ne possède pas d’argument de critère comme SOMME.SI. Pour filtrer sur deux critères simultanés tout en respectant les lignes masquées, la solution terrain est une formule matricielle.
Exemple : sommer les ventes de la région « Nord » avec un montant supérieur à 1 000 €, sur une plage filtrée :
=SOMME(SI((A2:A100= »Nord »)*(C2:C100>1000);SOUS.TOTAL(109;DECALER(C2;LIGNE(C2:C100)-LIGNE(C2);0))))
Concrètement, cette approche reste complexe à maintenir. Dans les faits, pour deux conditions ou plus, je recommande plutôt :
- 🔥 SOMME.SI.ENS si les données ne sont pas filtrées dynamiquement.
- Un tableau croisé dynamique pour des analyses multi-critères récurrentes : bien plus robuste à l’usage.
- Power Query pour des volumétries importantes ou des sources de données multiples.
Ce que ça change pour votre business : si vous gérez des reporting commerciaux par région et par catégorie de produit, la combinaison SOUS.TOTAL + filtre couvre 80 % des besoins. Pour aller plus loin, consultez ce guide complet sur la fonction SOUS.TOTAL Excel avec des exemples sectoriels détaillés.
Erreurs fréquentes et cas particuliers à connaître
Pourquoi les sous-totaux Excel ne fonctionnent pas : causes et solutions
C’est le piège classique. Vous insérez vos sous-totaux, et rien ne se passe comme prévu. Voici les causes les plus fréquentes, par ordre de fréquence terrain.
- Données dans un tableau Excel structuré (objet Table) : Microsoft le confirme explicitement : l’outil Données > Sous-totaux est désactivé dès que vos données sont dans un tableau mis en forme. Solution : convertir en plage classique via « Convertir en plage » avant d’insérer les sous-totaux.
- ❌ Données non triées avant insertion : si la colonne de regroupement n’est pas triée, Excel crée un sous-total par bloc contigu, pas par groupe logique. Résultat : des dizaines de sous-groupes au lieu de quelques-uns.
- Cellules fusionnées dans la plage : elles brisent la détection des groupes. Supprimez toutes les fusions avant de travailler avec SOUS.TOTAL.
- 🟡 Codes 1-11 vs 101-111 : si vos résultats ignorent les filtres actifs, vous utilisez probablement un code de la série 1-11. Passez à la série 100 (ex. : 109 au lieu de 9) pour que les lignes filtrées soient exclues du calcul.
- Imbrication de SOUS.TOTAL : la fonction ignore automatiquement les autres SOUS.TOTAL dans sa plage. En pratique, cela évite les doublons, mais peut surprendre si vous ne l’anticipez pas.
En pratique, 80 % des pannes viennent du tableau structuré ou du tri manquant. Vérifiez ces deux points en premier.
Sous-totaux dans un tableau croisé dynamique : différences clés
Le tableau croisé dynamique (TCD) calcule ses propres sous-totaux, indépendamment de la fonction SOUS.TOTAL. Les deux outils ne sont pas interchangeables.
- Dans un TCD, les sous-totaux sont configurés via un clic droit sur le champ de ligne : « Paramètres de champ » > onglet Sous-totaux. Vous pouvez choisir Somme, Moyenne, Nombre, etc.
- ⚠️ La fonction SOUS.TOTAL est sans effet à l’intérieur d’un TCD. Elle ne reconnaît pas la structure interne du tableau croisé.
- 💡 Le TCD est nettement plus adapté pour des analyses multi-niveaux (région > commercial > produit) que des formules imbriquées en colonne.
- La mise à jour du TCD nécessite un actualisation manuelle (F5 ou clic droit > Actualiser) : vos sous-totaux ne se recalculent pas en temps réel si la source change.
Concrètement, pour un reporting commercial récurrent avec des regroupements hiérarchiques, le TCD reste la solution la plus robuste. Pour des calculs ponctuels sur une plage filtrée, SOUS.TOTAL garde tout son intérêt. Consultez ce guide complet sur la fonction SOUS.TOTAL Excel pour approfondir les cas d’usage sectoriels.
Questions fréquentes
Pourquoi utiliser sous-Total Excel ?
La fonction sous-totaux Excel permet d’analyser des données groupées sans perturber les calculs globaux. Elle ignore automatiquement les lignes masquées par un filtre, ce qui garantit des résultats précis et cohérents lors de vos analyses.
Comment calculer le sous-total d’un groupe dans Excel ?
Sélectionnez votre tableau, puis allez dans l’onglet Données et cliquez sur Sous-total. Choisissez la colonne de regroupement, la fonction souhaitée (SOMME, MOYENNE, etc.) et validez. Excel insère automatiquement les lignes de sous-totaux pour chaque groupe détecté.
Comment faire une soustraction totale dans Excel ?
Pour soustraire des valeurs dans Excel, utilisez une formule simple comme =A1-B1 ou combinez la fonction SOMME avec des valeurs négatives. Pour des soustractions sur des plages entières, la formule =SOMME(A1:A10)-SOMME(B1:B10) reste la méthode la plus fiable.
Comment enlever les sous-totaux dans Excel ?
Pour supprimer les sous-totaux Excel, rendez-vous dans Données puis Sous-total. Dans la boîte de dialogue qui s’ouvre, cliquez simplement sur le bouton Supprimer tout. Excel retire immédiatement toutes les lignes de sous-totaux et restaure le tableau d’origine.
À 33 ans, je suis un pur produit de la culture « corporate » qui a décidé d’en briser les codes pour vous en livrer une lecture plus authentique. Diplômé d’une grande école de commerce, j’ai forgé mes premières armes pendant cinq ans au sein d’un cabinet de conseil du « Big Four », où j’ai appris la rigueur stratégique tout en observant de très près les dérives du jargon bureaucratique.
