Cliquez ici pour rejoindre le Master Club Excel,
le club privé pour apprendre à utiliser toutes les formules Excel
Cliquez ici si vous avez besoin d'une application
vous permettant de gérer vos devis et factures sur Excel
Tutoriel réalisé avec Excel 2013
Pour gérer vos factures, il vous sera utile de créer un échéancier Excel pour connaître toutes les échéances et les majorations si besoin.
Dans ce tutoriel, nous allons créer le même échéancier que j’utilise pour mes factures. Néanmoins, vous pourrez, bien sûr, le présenter comme vous le souhaitez.
Pour commencer, nous allons créer les titres, les bordures, modifier les formats, pour ensuite terminer par les formules.
Dans mon échéancier, j’ai 2 onglets : l’un qui s’appelle Échéancier et l’autre CA facturé.
Dans le premier, j’ai la liste de mes factures et, dans le second, j’ai le chiffre d’affaires généré par chaque client et le chiffre d’affaires total.
Création des titres :
Pour commencer, je saisis dans la cellule A1 du premier onglet Date du jour et de E1 à G1 (en les fusionnant avec le bouton 
de l’onglet Accueil) Taux d’intérêt légal pour l’année 2013.
Sur la ligne 4, je saisis tous les entêtes de mon tableau, à savoir :
- Raison sociale (et/ou Nom si vous avez des particuliers en tant que client) ;
- N° facture ;
- Date facture ;
- Date d’échéance : date limite de paiement avant la majoration ;
- Montant : montant de la facture ;
- Débours : étant auto-entrepreneur, certains frais peuvent être comptés comme des frais de débours (renvoi par La Poste de documents remis par le client par exemple). Dans ce cas, le montant déclaré sera le montant de la colonne Montant (sauf si une majoration est appliquée) et le montant de la facture sera celui de la colonne Total facture ;
- Total facture : égal à Montant + Débours ;
- Jours en dépassement : cette colonne comptera les jours écoulés entre Date d’échéance et Date du jour pour le calcul de la majoration ;
- Majoration : montant de la majoration en cas de dépassement de la date d’échéance ;
- Solde restant dû : égal à Total facture (Montant + Débours) + Majoration ;
- Réglée : j’affiche simplement En attente lorsque la facture n’est pas encore payée, puis Validée lorsque le paiement est reçu ;
- Première/Deuxième et Troisième relance : ces colonnes permettent d’écrire les dates des relances si nécessaires ;
- Observations : cette colonne permet d’inscrire des informations supplémentaires concernant la facture, le paiement ou la relance.
Étant auto-entrepreneur, je ne facture pas de TVA, mais si c’est votre cas, vous rajouterez une colonne pour cette dernière.
Pour tous les titres, j’applique du bleu avec comme couleur RVB 51-102-255, je les place en gras et je les centre. J’applique une bordure fine à chaque élément.
Format des cellules :
En H1, je clique, dans le groupe Nombre de l’onglet Accueil, sur le menu déroulant qui est par défaut sur Standard, je le change en Pourcentage.
Pour les colonnes Raison sociale, N° facture, Réglée, Première relance, Deuxième relance, Troisième relance et Observations, je laisse le format Standard.
Pour les colonnes Date facture et Date d’échéance, j’applique le format Date courte.
Pour les colonnes Montant, Débours, Total facture, Majoration et Solde restant dû, j’applique le format Monétaire.
Enfin, pour la colonne Jours en dépassement, j’applique le format Nombre que je modifie dans la boîte de dialogue Format de cellule pour ne pas avoir de décimale.
Insertion des formules :
- Date du jour : en B1, je saisis la formule
=AUJOURDHUIComme elle n’a pas d’argument, je la termine par () (parenthèse ouvrante et fermante). Cette formule n’est pas obligatoire, car vous pouvez insérer celle-ci à l’intérieur d’une formule, mais cela permettra de ne pas avoir trop d’arguments dans vos formules et d’avoir la date d’un seul coup d’œil ; - Taux d’intérêt légal : en H1, je saisis 0,04 qui correspond au taux d’intérêt légal pour cette année. Il sera bien sûr à modifier manuellement aux alentours des mois de février et mars (mois de publication du nouveau taux) ;
- Total facture : en G5, je saisis
=E5+F5qui additionne Montant et Débours ; - Jours en dépassement : en H5, nous allons combiner 2 formules, car le but est d’avoir le nombre de jours entre Date facture et Date d’échéance seulement si la Date d’échéance est dépassée. Les 2 formules à combiner sont la formule SI et la formule DATEDIF. La formule DATEDIF se compose des 2 dates dont vous souhaitez calculer l’écart et le format vers lequel renvoyer la valeur, c’est-à-dire d (day) pour jour, m (month) pour mois et y (year) pour année. Il existe également 3 autres formats, mais nous y reviendrons dans un prochain article. Pour recopier facilement cette formule, nous allons bloquer la cellule B1 avec le signe du dollar puisque la date sera toujours la même.
Notre formule est donc=DATEDIF(D5;$B$1;"d")
Pour la formule SI, il suffit d’ajouter la condition pour que la formule DATEDIF s’applique et la valeur à afficher si la condition n’est pas respectée. Nous souhaitons afficher le résultat de DATEDIF uniquement si la date d’échéance est indiquée donc différente de 0 sinon elle affiche 0.
La formule complète est=SI(D5>0;DATEDIF(D5;$B$1;"d");0)donc Si Date d’échéance est supérieur à 0 Alors Excel affiche le nombre de jours entre la Date du jour et la Date d’échéance Sinon il affiche 0 ; - Majoration : en I5, nous avons également besoin de la formule SI qui doit donner Si Date d’échéance est différente de 0 Alors Excel calcule Total facture x Jours en dépassement x Taux d’intérêt légal pour l’année 2013 x 3 + 40 € Sinon il affiche 0. Le taux d’intérêt légal doit être multiplié par 3 au minimum et je le place entre parenthèses à cause de la règle de priorité des calculs. J’ajoute également 40 €, qui depuis 2013, est une mention qui doit obligatoirement figurer sur les factures, c’est une indemnité forfaitaire pour frais de recouvrement due au créancier en cas de retard de paiement. La formule est
=SI(D5>0;G5*H5*($H$1*3)+40;0). Vous pouvez aussi multiplier le taux directement en H1, ce qui donnera dans cette cellule=0,04*3/100et en I5,=SI(D5>0;G5*H5*$H$1+40;0); - Solde restant dû : en J5, je saisis
=G5+I5qui additionne Total facture et Majoration.
Pour terminer, il ne vous reste plus qu’à recopier vos formules sur toutes vos lignes.
Pour le second onglet, dans la colonne A, vous allez saisir les uns en dessous des autres les noms de vos clients et vous terminerez par une ligne TOTAL. Écrivez bien les noms de la même manière, la casse n’est pas importante, mais si vous utilisez des initiales dans le premier onglet, faites de même dans le second.
À la colonne B, le chiffre d’affaires généré par chaque client sera affiché. Pour cela, nous allons utiliser la formule SOMME.SI. Elle se compose de la plage où se trouvent les données sur lesquelles portent le critère, le critère selon lequel additionner et la plage des sommes à additionner.
La formule en B1 sera =SOMME.SI(‘Échéancier’!A5:E50;A1;’Échéancier’!E5:E50) qui indique que la plage où se trouve le critère sont les cellules A5 à E50 de l’onglet Échéancier, que le critère est le nom du client qui se trouve en A1 et que les sommes à additionner se trouvent parmi les cellules E5 à E50 de l’onglet Échéancier.
À chaque ajout de nouvelle facture, la formule SOMME.SI ajoutera le total dans la case correspondante, tant que la facture est encore dans la plage de cellules indiquée, auquel cas, il faudra étendre la plage.
Enfin, à la ligne TOTAL, il suffira d’utiliser la Somme automatique de l’onglet Accueil 
ou Formules 
.
Note : pour faciliter la construction de votre tableau, vous pouvez utiliser la fonctionnalité Tableau d’Excel.
Cliquez ici pour découvrir les 7 étapes pour créer
des tableaux croisés dynamiques comme un pro sur Excel
Cliquez ici si vous avez besoin d'une application
vous permettant de gérer vos devis et factures sur Excel
Vous aimerez peut-être aussi :
Créer un devis et une facture avec Excel
Calculer plus rapidement avec la valeur cible d’Excel
Astuces de saisie pour Excel
Calculer un seuil de rentabilité et un point mort sur Excel
Créer un format personnalisé pour le singulier et le pluriel sur Excel
Extraire l’année, le mois et le jour d’une date sur Excel
Super merci ! Justement, je suis en train de mettre en place mes documents factures et devis sur Excel (je fonctionne avec des onglets). Tu as peut-être une manière de faire qui pourrait nous être utile ? Une idée pour ton prochain tuto 😉
Je crois que tu vas être servie Muriel, mais il faudra être un peu patiente. Je viens justement de finir d’écrire un article sur la création de devis et facture Excel (avec les mentions obligatoires) mais cet article sera intégré à un magazine qui devrait être diffusé en octobre. Cependant, dans ce tuto, j’explique comment automatiser certaines actions pour créer les devis et factures, ce qui fait gagner du temps 🙂
Super je suis impatiente ! Tu nous feras parvenir les références de l’article. @ bientôt !
Oui, oui, je partagerai le magazine ici 🙂
J’aurai peut-être fait un tableau croisé dynamique dans l’onglet CA facturé, ainsi pas besoin de rajouter « à la main » une ligne à chaque nouveau client.
Oui, effectivement, c’est également une possibilité, cependant, il faudra quand même penser à actualiser et une ligne Vide apparaîtra si on ne veut pas avoir à modifier la source à chaque nouvelle facture.
Bonjour Lydia,
Je souhaiterais savoir comment créer ce même tableau à partir d’un tableau de ventes déjà existant afin que mon échéancier ne récupère uniquement les lignes en attente de règlement… Penses-tu que cela soit possible avec Excel ?
Merci de ton retour…
Anne
Bonjour Anne,
Désolée, mais je n’ai pas très bien compris votre demande. Vous souhaitez mettre en lien vos 2 fichiers ? Si oui, il faudrait combiner les appels à une autre feuille de calculs et la formule SI afin de ne récupérer que les lignes en attente de règlement.
Salut Lydia.
Tout d’abord, j’aimerais bien te remercier pour toutes les vidéos que tu mets à notre disposition. Personnellement, j’ai beaucoup appris de tes vidéos. Merci encore.
J’aimerais bien faire pareil que toi, créer un blog, faire des vidéos et les partager mais mes connaissances en informatique sont assez limitées et je ne sais pas par où commencer. Donc ma question est de savoir comment as-tu commencé pour atteindre ce niveau ?
J’attends votre réponse avec impatience.
Bonjour et merci Moustapha.
Pour répondre à votre question : concernant Office, c’est un mélange de cours scolaires et d’apprentissage en autodidacte et, pour Adobe, c’est uniquement en autodidacte. Pour ce qui est de la mise en place du blog, ce sont de longues recherches sur internet qui m’ont permises de créer le blog tel qu’il est aujourd’hui.
Comment créer un tableau pour facilités de paiement (électroménager) ?
Désolée, je ne comprends pas votre demande.
Bonjour,
Je viens de découvrir votre tuto sur les échéanciers et franchement merci c’est clair, précis et efficace ! J’ai juste un souci sur l’avant-dernière étape. Lorsque je mets en date d’échéance la même date que celle du jour en B1, la majoration reste de 40€ alors que la condition D5>0 n’est pas remplie. Comment est-ce possible ? Si mon client paye au jour d’échéance, il y a donc une majoration qui n’a pas lieu d’être. Voici ma ligne de formule :
=IF(D5>0;G5*H5*($H$1*3)+40;0)Je précise que j’ai Excel 2007 anglais.
Merci d’avance.
Bonjour,
Il me semble avoir modifié mon échéancier depuis ce tutoriel.
Essayez cette formule pour la colonne des Jours en dépassement :
=SI(K6="Validée";0;SI(D6>AUJOURDHUI();0;DATEDIF(D6;AUJOURDHUI();"d")))Et cette formule pour la colonne Majoration :
=SI(H5>0;G5*E1*(H5/365)+40;0)D étant « Date d’échéance », K étant « Réglée », H étant « Jours en dépassement », G étant « Solde restant dû » et E le taux.
Bonjour Lydia,
Est-il possible d’intégrer une formule pour faire apparaître automatiquement :
– En attente : lorsque la facture n’est pas encore payée ;
– Validée : lorsque le paiement est reçu.
Merci.
Bonjour Ka,
C’est à ça que sert la colonne « Réglée ».
Cours super bien donné.
Bonjour Lydia,
Vos cours sont très bien.
Pouvez-vous m’aider ?
J’ai réussi à calculer automatiquement ma date d’échéance : je clique sur une des cellules « date d’échéance = » je clique sur « date de facture + 30 » (nombre de jours) sur la même ligne que ma cellule sélectionnée, puis Entrée.
Cela fonctionne très bien, mais comment puis-je faire pour le programmer sur toute la colonne et non ligne par ligne ?
J’espère que c’est clair… 🙂
Merci de votre réponse !
Bonjour,
Il vous suffit simplement de vous placer sur la cellule qui contient la formule, de survoler le point en bas à droite de la cellule qui se transformera en croix, puis de cliquer et glisser vers la fin de votre colonne.
Bonjour,
Cette formule ne marche pas :
=SI(D5>0;DATEDIF(D5;$B$1;"d");0)Bonjour,
Avez-vous bien renseigné une date d’échéance en D5 et une date du jour en B1 (ou remplacé $B$1 par AUJOURDHUI()) ? Sinon, quelle est l’erreur rencontrée ?
Bonjour Lydia,
Je viens de visionner votre vidéo et je suis un peu perplexe en voyant la méthode utilisée.
Pour ce qui est de la mise en forme, je conseillerai plutôt d’utiliser la fonctionnalité Mettre sous forme de tableau, ce qui permet d’éviter les bordures un peu ringardes et de nommer automatiquement les en-têtes et le tableau.
Pour ce qui est des recopies de formules, bien positionner les $ évite d’intervenir sur chaque ligne CLIENT A, CLIENT B….. c’est la base d’Excel.
On saisit directement 0,04% sans préalablement mettre en format Pourcentage, Excel comprend la saisie du signe %.
En ce qui concerne l’utilisation des $, j’expliquerais pourquoi mettre des $ devant les n° de ligne et/ou les lettres des colonnes.
Enfin, j’ai vraiment du mal à comprendre que vous mettiez en ligne une vidéo où vous vous trompez plusieurs fois au lieu de la recommencer et de mettre à disposition quelque chose d’impeccable.
C’est dommage.
Bonjour Sylvie,
Vous regardez une vidéo qui a 4 ans…et mes connaissances d’il y a 4 ans !
De plus, c’est grâce à mon site et mes vidéos que j’ai mes connaissances actuelles, même en VBA (je n’ai aucune formation en programmation et pourtant c’est désormais mon métier !).
C’est dommage alors de laisser en ligne cette vidéo qui ne reflète pas votre professionnalisme et qui pourrait vous faire perdre des clients.
Je vous souhaite néanmoins toute la réussite possible dans cette activité qui demande beaucoup de travail et d’implication.
Bonjour,
Lors de la première formule, lorsque j’inscris :
=DATEDIF(B1;F4;"F")cela m’indique :#NOMBREIdem pour la deuxième :
=SI(F4>0;DATEIF(F4;$B$1;"F");0)Cela m’indique : #Nom
Comment puis-je faire ?
Merci.
Bonjour,
L’argument F n’existe pas.