Maîtriser Excel

Faire son budget sur Excel qui se remplit tout seul

Noter ses dépenses ne suffit pas pour savoir où part l'argent. Dans ce guide, vous construisez un budget sur Excel qui fait les totaux de chaque mois tout seul. Les dépenses se notent avec une liste de catégories, le mois se calcule seul et les dépassements se colorent dès qu'ils apparaissent.

Par Anthony, formateur ExcelPublié le · 4 min de lecture
Une femme vue de dos vérifie ses tickets de caisse à la table de sa cuisine, devant son ordinateur portable

Vidéo hébergée par YouTube : au lancement, YouTube peut déposer des cookies (les cookies de YouTube expliqués).

Chapitres de la vidéo (8:02)

Les catégories et le budget prévu

Sarah veut suivre ses dépenses de juillet à septembre. Elle commence par une petite feuille Catégories avec six lignes, le logement, les courses, le transport, les loisirs, la santé et les abonnements. À côté de chaque catégorie, elle écrit ce qu'elle prévoit de dépenser par mois, par exemple 400 euros pour les courses.

Cliquez dans cette liste et appuyez sur Ctrl + T pour la mettre sous forme de tableau. Dans l'onglet Création de tableau, tapez Categories dans la case Nom de la table. Un tableau s'agrandit tout seul quand vous ajoutez une ligne en dessous, ce qui évite de reprendre les formules plus tard.

Noter ses dépenses avec une liste de catégories

La feuille Dépenses compte trois colonnes, la date, la catégorie et le montant. Mettez-la aussi sous forme de tableau et appelez-la Depenses, sans accent. Pour la catégorie, rien ne se tape à la main. Une faute ou un espace en trop suffirait à faire disparaître la dépense des totaux.

Dans l'onglet Formules, cliquez sur Définir un nom. Appelez-le ListeCategories et indiquez =Categories[Catégorie] dans Fait référence à. Sélectionnez ensuite la colonne Catégorie des dépenses puis ouvrez Données, Validation des données. Choisissez Liste dans Autoriser et tapez =ListeCategories dans Source. Chaque case affiche alors les six catégories, toujours écrites de la même façon.

Boîte Validation des données d'Excel avec Autoriser sur Liste et la source =ListeCategories
La source de la liste est le nom ListeCategories, posé sur la colonne du tableau Categories.

Le mois calculé tout seul

Pour faire les totaux par mois, chaque dépense doit savoir à quel mois elle appartient. Ajoutez une quatrième colonne Mois et tapez =TEXTE([@Date];"mmmm"). Les quatre m demandent le nom du mois en toutes lettres. Excel affiche juillet. Comme vous êtes dans un tableau, la formule descend toute seule jusqu'à la dernière ligne.

Le budget qui se remplit avec SOMME.SI.ENS

Sur la feuille Budget, les six catégories occupent la colonne A et les trois mois la ligne 3. SOMME.SI.ENS additionne seulement les lignes qui remplissent plusieurs conditions. Le premier argument dit ce qu'on additionne, les suivants vont par paires, une colonne puis le critère à respecter.

En B4, la formule =SOMME.SI.ENS(Depenses[Montant];Depenses[Catégorie];$A4;Depenses[Mois];B$3) additionne les montants dont la catégorie vaut A4 et dont le mois vaut B3. Le dollar devant le A garde la colonne des catégories et le dollar devant le 3 garde la ligne des mois. La touche F4 fait défiler les possibilités. Recopiez la formule vers la droite puis vers le bas. Le tableau entier se remplit. Sarah voit 394,12 euros de courses en juillet et 127,11 euros de transport.

Feuille Budget d'Excel avec les six catégories, juillet, août et septembre remplis par SOMME.SI.ENS
SOMME.SI.ENS additionne les dépenses d'une catégorie sur un mois.

Le total, les revenus et le reste à vivre

La ligne 10 additionne chaque mois avec SOMME, soit 1 565,41 euros en juillet. La ligne 11 porte les revenus de Sarah, 2 100 euros par mois. Le reste à vivre se calcule en ligne 12 avec les revenus moins les dépenses. Il vaut 534,59 euros en juillet, 603,26 euros en août et 640,62 euros en septembre. Pour prévoir les mois à venir plutôt que suivre les dépenses passées, le budget prévisionnel sur Excel calcule la trésorerie cumulée mois par mois.

Un contrôle rapide évite les oublis. Les trois totaux mis bout à bout font 4 521,53 euros, soit la somme de la colonne Montant. Si les deux chiffres diffèrent, une dépense porte une catégorie ou un mois mal écrit. Une fois les dépenses classées, le rapprochement bancaire sur Excel vérifie qu'elles correspondent bien au relevé du compte.

Le prévu face au réel

En E, MOYENNE calcule la dépense moyenne des trois mois. En F, RECHERCHEX va chercher le budget prévu dans la feuille Catégories. En G, l'écart vaut le prévu moins la moyenne. Un écart positif est une marge, comme les 21,96 euros gardés chaque mois sur les courses. Un écart négatif signale un dépassement, comme les loisirs qui dépassent de 7,63 euros en moyenne.

Les dépassements en couleur

Sélectionnez les montants des trois mois, de B4 à D9. Dans Accueil, ouvrez Mise en forme conditionnelle puis Nouvelle règle et choisissez la dernière ligne, Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué. Tapez =B4>$F4 puis choisissez une couleur de fond. Le dollar devant le F garde toujours la colonne du prévu.

Le transport de juillet et d'août, les loisirs des mêmes mois, la santé de juillet et les abonnements de septembre ressortent aussitôt. Quand Sarah ajoute une dépense sous le tableau, tout suit. Ses courses de 42,50 euros du 29 septembre font passer le total des courses de septembre de 384,57 à 427,07 euros sans toucher à une seule formule.

Budget Excel où six montants qui dépassent le budget prévu sont colorés
La règle =B4>$F4 colore chaque mois où la dépense dépasse le prévu.

Deux erreurs à éviter

La première est une catégorie mal écrite. Avec un espace en trop, la formule cherche courses avec un espace et ne trouve rien, elle affiche zéro. La liste déroulante règle ce problème une fois pour toutes. La seconde est l'oubli des dollars. Recopiée sans eux, la formule se décale et lit la mauvaise case, elle affiche zéro elle aussi. Quand un total vaut zéro alors que des dépenses existent, vérifiez d'abord ces deux points.

Le même tableau sert d'une année sur l'autre. Dupliquez la feuille chaque janvier, videz les montants et gardez les formules. La formation IniTableur reprend les tableaux, les références et les fonctions dans l'ordre avec des exercices corrigés.

Questions fréquentes

Comment faire un budget sur Excel ?

Notez vos dépenses dans un tableau avec la date, la catégorie et le montant. Ajoutez une colonne Mois avec TEXTE, puis faites les totaux par catégorie et par mois avec SOMME.SI.ENS sur une feuille Budget.

Comment additionner les dépenses d'une catégorie sur un mois ?

Utilisez SOMME.SI.ENS, par exemple =SOMME.SI.ENS(Depenses[Montant];Depenses[Catégorie];$A4;Depenses[Mois];B$3). Elle additionne seulement les lignes dont la catégorie et le mois correspondent.

Comment afficher le nom du mois à partir d'une date ?

La formule =TEXTE(A2;"mmmm") renvoie le mois en toutes lettres, par exemple juillet. Dans un tableau, =TEXTE([@Date];"mmmm") se recopie toute seule.

Pourquoi mon total SOMME.SI.ENS vaut zéro ?

Le plus souvent, une catégorie est mal écrite, avec un espace en trop par exemple. Il arrive aussi que la formule ait été recopiée sans dollars et lit la mauvaise case. Une liste déroulante et les références $A4 et B$3 évitent ces deux erreurs.

Comment repérer les dépassements de budget ?

Sélectionnez les montants, puis Mise en forme conditionnelle, Nouvelle règle et Utiliser une formule. La règle =B4>$F4 colore chaque mois où la dépense dépasse le budget prévu en colonne F.

Cet article vous a-t-il aidé ?

Une note de 1 à 5 étoiles

À lire ensuite

Une graphiste travaille à son bureau entre ses maquettes, ses nuanciers et son ordinateur portable

Maîtriser Excel4 min de lecture

Faire un devis sur Excel de A à Z

Les mentions demandées, un catalogue de prestations, des prix automatiques avec RECHERCHEX, puis le PDF et un modèle réutilisable.