Créer un calendrier dynamique sur Excel en 7 étapes (+ fichier à télécharger)

Planifier son année avec précision est un levier stratégique, quel que soit votre rôle. Responsable d’équipe, indépendant·e ou simplement soucieux·se d’optimiser votre organisation, un calendrier dynamique vous offre une vision claire des mois à venir. Il facilite l’anticipation, intègre automatiquement les jours fériés et réduit les tâches répétitives, pour vous permettre de vous concentrer sur l’essentiel.

Dans ce guide, vous apprendrez pas à pas à construire un calendrier annuel entièrement automatisé et personnalisable. Le fichier repose sur les fonctions modernes d’Excel (LET, SEQUENCE, FILTRE…) et sur des mises en forme conditionnelles pour s’adapter à l’année saisie. Et si vous préférez suivre un format vidéo, un tutoriel complet est disponible sur notre chaîne et peut être utilisé en complément ou à la place de l’article – vous êtes libre de choisir ce qui vous convient le mieux.

Étape 1 : Mettre en forme la base du calendrier

‍

1.1 — Créer une feuille CALENDRIER

Dans un classeur vierge, ajoutez un onglet nommé CALENDRIER. Placez en haut une cellule (par exemple K6) destinée à accueillir l’année de référence ; vous pouvez la nommer ANNÉE via le Gestionnaire de noms. L’utilisateur modifie cette cellule pour afficher l’année souhaitée.

‍

1.2 — Définir 12 blocs mensuels

Divisez la feuille en 12 zones (une par mois) réparties sur trois colonnes :

^{Colonne 1 : affiche la première lettre du jour de la semaine (L, M, M, J, V, S, D).
Colonne 2 : affiche le numéro du jour (1 à 31).
Colonne 3 : affiche le type d’événement (par exemple « Présentiel », « Distanciel », « CP », etc.).}

Adaptez la largeur des colonnes et la hauteur des lignes pour obtenir une grille lisible. Insérez un en‑tête au‑dessus de chaque bloc avec le nom du mois. Utilisez des bordures et des couleurs sobres pour délimiter les blocs.

‍

1.3 — Ajouter un titre et une zone « Légende »

Au‑dessus de la grille, inscrivez un titre : « Calendrier de l’année AAAA » (où AAAA fait référence à la cellule ANNÉE). Sous le calendrier, réservez quelques cellules pour une légende afin d’expliquer les couleurs : orange pour les jours fériés, gris pour les week‑ends, bleu pour les formations, vert pour les congés et rouge pour les doublons. Ces repères visuels faciliteront la lecture.

‍

‍

Étape 2 : Générer automatiquement les dates

L’un des atouts de ce calendrier est qu’il se met à jour tout seul lorsque l’année change. Pour y parvenir, nous allons générer les dates avec SÉQUENCE et FILTRE. Le but est :

• de remplir la colonne des numéros de jour uniquement avec les dates du mois courant ;
  

• d’extraire la première lettre du jour de la semaine.
  
  

2.1 — Générer les dates du mois

Identifiez la cellule contenant le nom du mois (par exemple B10 pour « Janvier »). Dans la première cellule vide de la colonne des numéros de jour, entrez la formule suivante :

_{=LET(d;SEQUENCE(31;;DATEVAL(B10&Annee));FILTRE(d;MOIS(d)=MOIS(DATEVAL(B10&Annee))))}

Cette formule :

• crée 31 dates consécutives à partir du premier jour du mois avec : _{SEQUENCE(31;;DATEVAL(...))}
• filtre ces dates pour ne conserver que celles qui appartiennent au mois voulu : _{FILTRE(d; MOIS(d)=MOIS(...))}
  
  

Elle s’adapte ainsi aux mois de 28, 29, 30 ou 31 jours et se recalculera automatiquement lors du changement d’année. Formatez ensuite cette colonne avec le format personnalisé j pour n’afficher que le numéro du jour.

‍

2.2 — Extraire la lettre du jour

Dans la cellule de la colonne de gauche, vous pouvez utiliser la formule classique suivante :

_{=MAJUSCULE(GAUCHE(TEXTE(C11;"jjjj");1))}

(Remplacez C11 par la référence de la cellule de date de la même ligne). Cette formule :

• transforme la date en nom complet du jour : _{(TEXTE(...; "jjjj"))}
  
  
• extrait la première lettre : _{(GAUCHE(...;1))}
  
  
• met cette lettre en majuscule : _(MAJUSCULE)
  
  

Recopiez cette formule sur toutes les lignes du mois.

‍

2.3 — Dupliquer les formules pour les 12 mois

Copiez‑collez les deux formules dans les colonnes équivalentes de chaque bloc mensuel (février, mars, …, décembre). Adaptez la référence du mois (par exemple E10, H10, etc.) et celle de la cellule de date (par exemple F11). À ce stade, votre calendrier affiche les jours et les numéros pour toute l’année et se met à jour en changeant la cellule ANNÉE.

‍

Étape 3 : Construire l’onglet Paramètres

Le calendrier tire sa puissance de paramètres centralisés qui alimentent les formules. Dans un nouvel onglet nommé PARAMÈTRES, créez plusieurs tableaux structurés :

‍

1. ‍Date de Pâques et jours fériés : un tableau calcule automatiquement la date de Pâques sur plusieurs années et en déduit le lundi de Pâques, l’Ascension et la Pentecôte. Ajoutez également les jours fériés fixes (1ᵉʳ janvier, 1ᵉʳ mai, 8 mai, 14 juillet, 15 août, 1ᵉʳ novembre, 11 novembre, 25 décembre).
   ‍

🔗 Pour en savoir plus : si vous souhaitez aller plus loin et intégrer automatiquement les jours fériés via Power Query et une API, notre guide sur la création d’un diagramme de Gantt 100 % Excel montre comment récupérer ces dates dynamiquement.

‍

2. Liste des formations ou événements professionnels : créez un tableau TABFORMATION avec une colonne Date et une colonne Type (par exemple « Présentiel » ou « Distanciel »). Les dates saisies se reflèteront automatiquement dans le calendrier
   ‍
3. Liste des congés/absences : créez un tableau TABCONGE avec une colonne Date et une colonne Type (par exemple « CP » pour congés payés, « RTT », « Maladie », etc.).
   ‍

Vous pouvez personnaliser ces tables en ajoutant d’autres types d’événements (télétravail, réunions, tâches administratives…). Les listes étant des tableaux structurés, elles s’agrandiront automatiquement lorsque vous ajouterez des lignes.

‍

3.1 — Validation des données dans Paramètres

Pour éviter les erreurs de saisie :

• Dans la colonne Type de TABFORMATION, autorisez uniquement les valeurs « Présentiel » ou « Distanciel ».
  
  
• Dans la colonne Type de TABCONGE, autorisez les valeurs que vous utilisez (CP, RTT, Maladie…).
  ‍
• Dans la colonne Date des formations, ajoutez une règle de validation personnalisée excluant les dimanches et les jours fériés :
  
  ^{=ET(JOURSEM(DateCellule;2)<>7;NB.SI(TabJoursFeries[Date];DateCellule)=0)}

‍

Cette formule vérifie que le jour n’est pas un dimanche (JOURSEM(...;2)<>7) et que la date n’apparaît pas dans le tableau des jours fériés. Si l’utilisateur tente de saisir un dimanche ou un jour férié, Excel affichera un message d’erreur personnalisé (par exemple « Impossible d’ajouter une formation un dimanche ou un jour férié »). Appliquez une règle similaire pour les congés si besoin.

‍

3.2 — Mise en forme du tableau Paramètres

Mettez en évidence les en‑têtes et appliquez un fond léger pour distinguer chaque section (jours fériés, formations, congés). Ajoutez des bordures claires pour améliorer la lisibilité. Cela n’a pas d’incidence sur les formules, mais facilite la saisie.

🔗 Pour en savoir plus : si vous devez gérer plusieurs agendas en parallèle (plusieurs collaborateurs et plusieurs types d’activités), découvrez notre calendrier multi‑agendas. Il permet d’afficher les plannings de plusieurs personnes en simultané et d’analyser les tâches par jour.

‍

Étape 4 : Récupérer les événements

Nous allons maintenant alimenter la troisième colonne du calendrier avec les informations saisies dans PARAMÈTRES. L’idée est de chercher, pour chaque date du calendrier, s’il existe un événement (formation ou congé) et d’afficher son type. Nous devons aussi détecter les doublons (cas où un congé et une formation sont planifiés le même jour).

4.1 — Créer la formule pour afficher l’événement

Dans la cellule de la troisième colonne du premier jour du mois, insérez la formule suivante :

^{=SI(NB.SI(TabFormation[Date];DateCellule) + NB.SI(TabConge[Date];DateCellule)>1;"Doublon";SIERREUR(LET(p; ASSEMB.V(TabFormation[#Tout]; TabConge[#Tout]);INDEX(p; EQUIV(DateCellule;CHOISIRCOLS(p;1);0);2));""))}

DateCellule est la référence de la cellule contenant la date (colonne 2). Le test NB.SI additionne la recherche de la date dans les deux listes ; si la somme est supérieure à 1, on affiche « Doublon ». Sinon, la fonction LET fusionne les deux tableaux avec ASSEMB.V, puis INDEX et EQUIV cherchent la date correspondante et retournent le type d’événement. La fonction SIERREUR évite d’afficher du texte lorsqu’aucune date n’est trouvée. Recopiez cette formule sur toutes les lignes du mois et pour tous les blocs mensuels.

4.2 — Adapter la formule pour d’autres événements

Si vous avez ajouté d’autres types dans vos tables (par exemple télétravail, réunions, etc.), la formule les récupérera automatiquement puisque tout nouveau type figure dans la deuxième colonne de vos tableaux. Veillez simplement à utiliser la même liste de valeurs lors de la validation des données pour éviter les fautes de frappe.

4.3 — Surveiller les doublons dans Paramètres

Pour éviter les doublons directement dans vos listes de formations et de congés, mettez en place une règle de mise en forme conditionnelle sur les colonnes Date de vos tableaux TABFORMATION et TABCONGE. L’idée est d’identifier automatiquement les dates présentes plusieurs fois et de les signaler visuellement.

Commencez par sélectionner toutes les cellules contenant des dates dans la colonne Date de TABFORMATION (dans notre modèle d’exemple, il s’agit de la plage H3:H1080) ainsi que celles de TabConge (plage K3:K1007). Créez ensuite une nouvelle règle de mise en forme conditionnelle de type Formule et saisissez la formule suivante :

^{=OU(NB.SI($H$3:$H$1080;H3)>1;NB.SI($K$3:$K$1007;H3)>0)}

Cette formule renvoie VRAI si la date en cours (H3) apparaît plus d’une fois dans la liste des formations (colonne H) ou si elle existe déjà dans la liste des congés (colonne K). Adaptez les plages $H$3:$H$1080 et $K$3:$K$1007 aux dimensions exactes de vos tableaux. Appliquez ensuite un remplissage rouge et une police blanche en gras pour faire ressortir immédiatement les doublons dans votre feuille PARAMÈTRES.

Une fois cette règle activée, toute date saisie en double sera mise en évidence. Corrigez les doublons ou modifiez les dates directement dans vos tableaux pour résoudre les conflits. Vos listes serviront alors de base de données fiable pour alimenter le calendrier.

‍

Étape 5 : Mettre le calendrier en forme conditionnelle

Le calendrier est fonctionnel ; il reste à lui donner un aspect visuel attrayant afin de repérer d’un coup d’œil les week‑ends, jours fériés, dates passées et les différents événements. Nous allons créer plusieurs règles de mise en forme conditionnelle. L’ordre des règles est important : placez toujours en premier la règle qui colore les doublons, puis celles pour les formations et congés, et enfin celles pour les week‑ends, jours fériés et dates passées. Ainsi, si un doublon se produit un jour férié, le rouge prévaudra.

‍

5.1 — Doublons

Sélectionnez la colonne des types d’événements (troisième colonne de chaque mois). Créez une règle de mise en forme conditionnelle de type Formule avec :

_{=cellule="Doublon"}

(remplacez CELLULE par la première cellule de la plage sélectionnée, sans dollar pour garder la référence relative).

Appliquez un fond rouge et une police blanche en gras.

Activez « Arrêter si vrai » (si disponible) afin qu’aucune autre règle ne s’applique sur ces cellules.

‍

5.2 — Colorier les formations et les congés

Au lieu de tester le contenu de la cellule (présentiel, distanciel, CP, etc.), il est plus sûr de vérifier si la date correspondante figure dans les listes Paramètres.

1. ‍Formations (présentiel/distanciel) : sélectionnez à nouveau la colonne des types. Créez une règle avec la formule :
   
   ^{=EQUIV(DateCellule;PARAMETRES!$H$3:$H$1080; 0)>0}
   où DATECELLULE est la cellule contenant la date de la ligne concernée (colonne 2). Appliquez un fond bleu et une police blanche en gras.
   ‍
2. ‍Congés (CP, RTT, Maladie…) : même logique :
   
   ^{=EQUIV(DateCellule; PARAMETRES!$K$3:$K$1007; 0)>0}
   Appliquez un fond vert et une police blanche en gras.

5.3 — Week‑ends en gris

Les week‑ends sont repérés grâce à la première lettre du jour de la semaine. Sélectionnez les deux premières colonnes du calendrier et créez une règle de mise en forme avec :

^{=ET(B11<>"";OU(B11="S"; B11="D"; SIERREUR(JOURSEM(B11;2)>5;"X")))}

Cette formule teste trois conditions :

1. la cellule n’est pas vide (B11<>""),
   
   
2. la lettre correspond à S ou D (pour samedi/dimanche),
   
   
3. ou bien le numéro de jour est un week‑end (on utilise JOURSEM pour couvrir tous les cas).

Appliquez un remplissage gris clair.

5.4 — Jours fériés en orange

Toujours sur les deux premières colonnes, créez une règle qui colore en orange les jours fériés :

^{=OU(NB.SI(PARAMETRES!$F$3:$F$13; B11)=1;NB.SI(PARAMETRES!$F$3:$F$13; C11)=1)}

La formule vérifie si la date (colonne 1 ou 2) se trouve dans la liste des jours fériés ($F$3:$F$13) et renvoie VRAI le cas échéant.

Appliquez un fond orange et une police blanche en gras.

‍5.5 — Dates passées en gris hachuré

Pour aider l’utilisateur à distinguer les jours déjà écoulés, appliquez une règle qui hachure les cellules des dates antérieures à aujourd’hui (avec une marge de 175 jours pour prendre en compte les lignes vides dans l’année) :

^{=OU(ET(B11<>""; B11<(AUJOURDHUI()-175));ET(C11<>""; C11<(AUJOURDHUI()-175)); ET(A11<>""; A11<(AUJOURDHUI()-175)))}

Choisissez un style de motif gris. La triple vérification (A11, B11, C11) permet d’appliquer cette règle quel que soit l’emplacement de la date.

Étape 6 : Calculer les statistiques et afficher les objectifs

Votre calendrier est maintenant fonctionnel et esthétique. Pour aller plus loin, ajoutez un tableau de bord en haut ou à côté du calendrier afin de visualiser le nombre total de formations, de congés et de doublons, et leur pourcentage par rapport à des objectifs annuels.

6.1 — Préparer l’espace des statistiques

Créez un tableau en haut de la feuille, avec trois lignes :

• Objectif formations : saisissez manuellement le nombre total de formations prévues pour l’année.
  
  
• Objectif congés : saisissez le nombre total de jours de congés.
  
  
• Objectif doublons : indiquez 0 (puisque l’objectif est d’en avoir aucun).
  
  

À côté, réservez des cellules pour afficher le nombre réalisé et le pourcentage de réalisation.

6.2 — Compter les événements

Sous la colonne « Positionné », utilisez la fonction NB.SI.ENS pour compter combien de fois chaque catégorie apparaît dans la plage du calendrier (toutes colonnes confondues) 

Nombre total de formations :
^{=NB.SI.ENS($B$11:$AK$41; "Présentiel") + NB.SI.ENS($B$11:$AK$41; "Distanciel")}

Nombre de congés (ajoutez les autres types selon vos besoins) :

^{=NB.SI.ENS($B$11:$AK$41; "CP") + NB.SI.ENS($B$11:$AK$41; "RTT")}

Nombre de doublons :

^{=NB.SI.ENS($B$11:$AK$41; "Doublon")}

6.3 — Calculer les pourcentages et afficher les barres de progression

Dans la colonne « % de réalisation », divisez le nombre réalisé par l’objectif :

^{=SI(Objectif>0; NombreRéalisé/Objectif; 0)}

Formatez ces cellules en pourcentage (0 décimale). Pour une représentation visuelle élégante, utilisez la fonction REPT afin de générer une barre de progression :

^{=REPT("█"; ENT(%Réalisé*20)}

Cette formule répète un caractère (bloc plein) autant de fois que le pourcentage réalisé (multiplié par 20 pour obtenir une barre de 20 caractères). Utilisez une police décorative et agrandissez la taille des caractères pour créer une mini‑jauge.

🔗 Envie de maîtriser les tableaux de bord ? Notre tutoriel sur la création d’un tableau de bord RH explique comment utiliser les tableaux croisés dynamiques, les segments et les fonctions avancées pour analyser vos données et créer des graphiques interactifs.

‍

Étape 7 : Personnaliser votre calendrier et aller plus loin

Votre calendrier dynamique est prêt ! Voici quelques pistes pour le personnaliser et l’enrichir :

• Ajouter d’autres catégories d’événements : télétravail, réunions, deadlines projets, tâches administratives… Il suffit de les inclure dans les listes PARAMÈTRES et de mettre à jour la validation des données.
  
  
• Adapter les couleurs : modifiez les règles de mise en forme conditionnelle pour choisir des teintes différentes (par exemple violet pour le télétravail, jaune pour les formations virtuelles…). N’oubliez pas d’ajouter ces couleurs dans la légende.
  
  
• Étendre la période : en ajustant le paramètre 31 de la fonction SEQUENCE, vous pouvez créer des calendriers trimestriels ou pluriannuels. Pour un calendrier multi-agendas qui affiche plusieurs collaborateurs en parallèle, consultez notre guide dédié.
  
  
• Aller plus loin avec Excel : la création de calendriers n’est qu’un début. Pour découvrir comment générer un diagramme de Gantt dynamique ou récupérer automatiquement des jours fériés via une API, lisez notre article sur le diagramme de Gantt 100 % Excel. Et pour apprendre à structurer vos statistiques à l’aide de tableaux croisés dynamiques, jetez un œil à notre tutoriel sur le tableau de bord RH.
  
  

En suivant ces 7 étapes, vous obtenez un calendrier élégant, interactif et parfaitement adapté à votre organisation. Vous maîtrisez désormais l’art de générer des dates dynamiquement, de structurer des paramètres, de récupérer des événements, de détecter les doublons, de colorier intelligemment les cellules et de synthétiser les données. À vous de jouer !

Besoin d’aller plus loin ?

Vous avez découvert comment construire un calendrier annuel moderne et personnalisé. Si vous souhaitez aller plus loin et transformer ces compétences en un véritable avantage professionnel, nos formations vous guident pas à pas. Chez Solpedinn, nous croyons qu’apprendre Excel autrement, avec une pédagogie axée sur la pratique, est essentiel.

Nos programmes couvrent des thématiques variées : automatisation avec Power Query, création de calendriers multi‑agendas, conception de tableaux de bord percutants et développement d’outils sur mesure. Chaque formation est certifiée Qualiopi et éligible au CPF, pour répondre aux besoins des entreprises comme des particuliers.

Voici des liens vers nos formations individuelles et elearling !

‍

Passez à l’action ! Regardez notre vidéo dans son intégralité et inscrivez‑vous à l’une de nos formations pour exploiter pleinement tout le potentiel d’Excel. C’est le meilleur moyen de gagner du temps, d’accélérer vos projets et de monter en compétences rapidement.

‍

‍

‍

‍