📍 Guide principal de la série : Excel pour PME : tableaux structurés, fonctions dynamiques, Copilot et Python.
Une boutique alimentaire à Dakar, un atelier de couture à Bamako, un dépôt de matériaux à Abidjan ont tous le même besoin : savoir à tout moment combien de chaque référence il leur reste, ne pas se laisser surprendre par une rupture sur un produit phare, et sortir une valorisation du stock pour la comptabilité de fin d’année. Excel répond à ces trois besoins sans logiciel dédié si on construit le classeur correctement. Ce tutoriel détaille la méthode, des fiches articles aux mouvements en passant par l’alerte de seuil minimum.
Prérequis
- Excel 2016 ou plus récent
- Avoir manipulé tableaux structurés et RECHERCHEV
- Une activité avec au moins 20 références à suivre
- Temps : 75 minutes pour la première construction
Étape 1 — La structure en 4 feuilles
Un suivi de stock robuste sépare quatre rôles. Une feuille Articles contient la fiche signalétique de chaque référence (code, désignation, prix, seuil mini). Une feuille Mouvements trace toutes les entrées et sorties. Une feuille Stock calcule le stock actuel par article. Une feuille Alertes liste les ruptures imminentes.
Cette séparation a un grand avantage : on saisit dans Mouvements, et tout le reste se met à jour automatiquement. Pas besoin de modifier des tableaux multiples à chaque réception ou vente.
Ouvrir un classeur vide. Renommer Feuil1 en Articles. Créer les 3 autres feuilles via le +. Sauvegarder en .xlsx.
Étape 2 — La feuille Articles
Sur Articles, créer un tableau avec en-têtes : Code, Désignation, Catégorie, Unité (kg, pièce, litre…), Prix achat, Prix vente, Seuil mini, Stock initial. Convertir en tableau structuré (Ctrl + L) et le nommer tblArticles.
Saisir 10 à 30 articles. Pour la cohérence, les codes suivent une convention simple : P001, P002… ou avec préfixe par catégorie : ALI-001 (alimentaire), HYG-001 (hygiène).
Le seuil mini est le niveau en dessous duquel une alerte de réapprovisionnement se déclenche. Pour un article qui se vend 10 unités par semaine et dont le réapprovisionnement prend 7 jours, mettre seuil mini = 15 (10 jours de stock pour absorber l’attente + un peu de marge).
Étape 3 — La feuille Mouvements
Sur Mouvements, en-têtes : Date, Type (Entrée/Sortie), Code, Quantité, Prix unitaire, Référence document (n° facture fournisseur, n° vente), Notes. Convertir en tableau structuré nommé tblMouvements.
Pour fiabiliser la saisie, ajouter des validations. Type doit être Entrée ou Sortie : Données → Validation → Liste → Entrée;Sortie. Code doit être présent dans Articles : validation par formule =NB.SI(tblArticles[Code];A2)=1. Un message d’erreur clair guide l’utilisateur.
Saisir quelques mouvements de test : 10 entrées (réceptions de marchandises) et 20 sorties (ventes ou consommations).
Étape 4 — Calculer le stock par article
Sur la feuille Stock, en A1 mettre Code, en B1 Désignation, en C1 Stock initial, en D1 Entrées, en E1 Sorties, en F1 Stock actuel. En A2, formule pour récupérer la liste des codes :
=tblArticles[Code]Si vous avez Microsoft 365 ou Excel 2021+, cette formule unique liste tous les codes en se déversant. Sinon, copier-coller manuellement les valeurs.
En B2, RECHERCHEV pour la désignation : =RECHERCHEV(A2;tblArticles;2;FAUX). En C2 : =RECHERCHEV(A2;tblArticles;8;FAUX) pour le stock initial.
En D2, total des entrées de cet article : =SOMME.SI.ENS(tblMouvements[Quantité];tblMouvements[Type];"Entrée";tblMouvements[Code];A2).
En E2, idem pour Sorties : =SOMME.SI.ENS(tblMouvements[Quantité];tblMouvements[Type];"Sortie";tblMouvements[Code];A2).
En F2, le stock actuel : =C2+D2-E2.
Recopier les formules pour tous les articles. Désormais, dès qu’un mouvement est saisi, le stock se met à jour.
Étape 5 — La feuille Alertes
Sur Alertes, on veut afficher uniquement les articles dont le stock actuel est sous le seuil mini. Avec Microsoft 365 / Excel 2021+ :
=FILTRE(Stock!A:F;Stock!F:F<RECHERCHEV(Stock!A:A;tblArticles;7;FAUX))Cette formule unique extrait les lignes de Stock dont le stock actuel est inférieur au seuil mini de l’article. Les articles bien fournis n’apparaissent pas.
Pour une version plus universelle (toutes versions Excel), créer une colonne Sous_seuil dans Stock : =SI(F2<RECHERCHEV(A2;tblArticles;7;FAUX);"OUI";"NON"). Puis filtrer manuellement Stock sur Sous_seuil = OUI.
Étape 6 — Mise en forme conditionnelle
Sur Stock, sélectionner la plage F2:F100 (stock actuel). Mise en forme conditionnelle → Nouvelle règle → Utiliser une formule. Formule :
=$F2<RECHERCHEV($A2;tblArticles;7;FAUX)Format fond rouge clair. Maintenant, dès qu’un stock passe sous le seuil, la cellule rougit. Lecture instantanée à l’écran.
Ajouter une seconde règle pour les ruptures totales (stock = 0) : formule =$F2=0, format fond rouge vif. La hiérarchie : rouge vif si zéro, rouge clair si sous seuil, blanc sinon.
Étape 7 — Valorisation du stock
La valorisation est l’estimation comptable du stock à un instant donné. Méthode CMP (coût moyen pondéré) : pour chaque article, prix moyen pondéré par les quantités entrées.
Sur Stock, ajouter une colonne G : Valorisation. Formule :
=F2*RECHERCHEV(A2;tblArticles;5;FAUX)Multiplie le stock actuel par le prix d’achat de référence. Total général en bas : =SOMME(G2:G100). C’est la valeur du stock à ajouter au bilan.
Pour une vraie méthode CMP qui reprend l’historique des prix d’achat, il faut calculer un prix moyen à partir des entrées de Mouvements. Formule plus complexe :
=SOMMEPROD((tblMouvements[Type]="Entrée")*(tblMouvements[Code]=A2)*tblMouvements[Quantité]*tblMouvements[Prix unitaire])/SOMME.SI.ENS(tblMouvements[Quantité];tblMouvements[Type];"Entrée";tblMouvements[Code];A2)Cette formule pondère chaque entrée par sa quantité pour donner le vrai prix moyen. Indispensable pour des articles dont le prix d’achat varie significativement (importation en devises fluctuantes).
Étape 8 — Inventaire physique
Périodiquement (mensuel, trimestriel), il faut comparer le stock théorique (calculé par Excel) au stock physique (compté manuellement en boutique). Ajouter sur Stock une colonne Stock physique à remplir lors de l’inventaire, et une colonne Écart = Stock physique – Stock actuel.
Les écarts doivent être analysés : produits cassés, vols, erreurs de saisie, péremption. Une fois validés, créer un mouvement de régularisation (type Entrée ou Sortie selon le sens) pour ramener le théorique au physique.
Étape 9 — Vérifier que ça marche
Saisir 5 entrées et 10 sorties dans Mouvements. Vérifier que la feuille Stock affiche les bons stocks calculés à la main. Si un article passe sous son seuil, vérifier qu’il apparaît rouge dans Stock et listé dans Alertes.
Sauvegarder une copie du classeur avant l’inventaire physique mensuel. Cela permet de revenir en arrière en cas de bug pendant la régularisation.
Erreurs fréquentes
| Symptôme | Cause | Solution |
|---|---|---|
| Stock négatif | Saisie d’une sortie supérieure au stock | Validation : refuser sortie > stock disponible |
| RECHERCHEV ne trouve pas un code | Code créé dans Mouvements absent d’Articles | Créer la fiche article avant de saisir mouvement |
| Stock actuel faux après ajout d’article | Plage figée au lieu de tableau | Convertir en tableau structuré |
| Mise en forme conditionnelle non actualisée | Plage règle non couvrante | Étendre la plage à 1000 lignes |
| Doublons dans les codes | Saisie manuelle sans contrôle | Validation unique : =NB.SI(...)=1 |
| Valorisation incohérente | Prix achat changé sans historique | Passer en méthode CMP basée sur Mouvements |
Pour explorer plus loin
- Comptabilité de base avec Excel — intégrer la valorisation stock au bilan.
- Tableau de bord de suivi — KPI stock dans un dashboard de gestion.
🔝 Retour au guide principal : Excel pour PME.
Foire aux questions
Combien de références Excel peut-il gérer en stock ?
Quelques milliers fonctionnent confortablement avec les formules de ce tutoriel. Au-delà de 10 000 références ou 100 000 mouvements, basculer sur Power Query pour les agrégations, ou sur un logiciel dédié (ERP simple).
Comment gérer plusieurs entrepôts ?
Ajouter une colonne Entrepôt dans Mouvements. Les formules SOMME.SI.ENS prennent un critère supplémentaire. La feuille Stock devient un croisement Article × Entrepôt.
Faut-il sauvegarder chaque jour ?
Pour un classeur opérationnel, oui. Une copie quotidienne datée (stock_2026-05-03.xlsx) sur un disque externe ou un cloud (Google Drive, OneDrive) protège contre les corruptions et permet de remonter à un état antérieur.
Excel ou logiciel de caisse ?
Pour < 100 ventes par jour, Excel suffit largement. Au-dessus, un logiciel de caisse avec écran tactile et code-barres améliore la productivité. Le suivi de stock peut rester sur Excel et se synchroniser avec la caisse via export-import quotidien.
Comment éditer une commande fournisseur depuis ce classeur ?
Créer une feuille Commande avec gabarit type bon de commande. Les articles à commander viennent automatiquement de la feuille Alertes (formule INDEX/EQUIV ou FILTRE). On imprime ou on envoie par email.
Le suivi en temps réel est-il possible avec Excel ?
Pas vraiment. Excel n’est pas multi-utilisateur en saisie simultanée. Si plusieurs personnes saisissent sur le même fichier (même via OneDrive partagé), des conflits surviennent. Pour du vrai temps réel, un système connecté (caisse + base) est nécessaire.