Créer une base de données sur Excel est une excellente manière de centraliser des informations, de les filtrer rapidement et d’automatiser une partie de son travail. Suivi de clients, gestion de produits, inventaire, liste de dépenses ou encore suivi de projets : Excel peut parfaitement jouer le rôle d’une petite base de données, à condition de respecter quelques règles simples.

Le piège le plus fréquent consiste à transformer une feuille en « tableau fourre-tout », avec des titres fusionnés, des cellules vides et plusieurs informations dans une même colonne. Au début, tout semble fonctionner. Puis vient le moment de filtrer, de faire une recherche ou de créer un graphique… et Excel commence à faire grise mine.

Dans cet article, nous allons voir comment créer une base de données propre, fiable et facile à exploiter, sans utiliser de VBA. La méthode convient aussi bien aux débutants qu’aux utilisateurs souhaitant remettre de l’ordre dans un fichier existant.

Définir l’objectif de la base de données

Avant d’ouvrir Excel, prenez quelques minutes pour répondre à une question essentielle : quelles informations souhaitez-vous stocker et que voulez-vous en faire ?

Une base de données destinée à suivre des clients ne contiendra pas les mêmes colonnes qu’une base dédiée à la gestion d’un stock. L’objectif détermine donc la structure du fichier.

Par exemple, pour gérer des commandes, vous pourriez avoir besoin des informations suivantes :

  • La date de la commande
  • Le numéro de commande
  • Le nom du client
  • Le produit commandé
  • La quantité
  • Le prix unitaire
  • Le statut de la commande

Pour un suivi de dépenses, les champs seront plutôt la date, la catégorie, le montant, le moyen de paiement et une éventuelle remarque.

Cette étape permet d’éviter d’ajouter des colonnes « au hasard ». Une bonne base de données n’essaie pas de tout stocker. Elle conserve uniquement les données utiles à l’objectif fixé.

Organiser les données en colonnes

Dans une base de données Excel, chaque colonne doit représenter une information précise. La première ligne contient les en-têtes, puis chaque ligne correspond à un enregistrement.

Voici un exemple de structure pour une base de données de clients :

  • ID client
  • Nom
  • Prénom
  • Entreprise
  • Adresse e-mail
  • Téléphone
  • Ville
  • Date d’inscription
  • Statut

La règle est simple : une cellule doit contenir une seule information. Évitez donc de regrouper le prénom et le nom dans une même colonne si vous prévoyez de rechercher séparément ces deux éléments. De la même façon, ne placez pas plusieurs numéros de téléphone dans une seule cellule.

Il est également préférable d’éviter les colonnes trop générales comme « Informations complémentaires ». Elles peuvent sembler pratiques, mais elles rendent ensuite les recherches et les analyses beaucoup moins efficaces.

Respecter une structure propre

Pour qu’Excel puisse exploiter correctement votre base de données, gardez une structure régulière :

  • Une seule ligne d’en-têtes
  • Aucune ligne vide au milieu des données
  • Aucune colonne vide dans le tableau
  • Un type de donnée cohérent par colonne
  • Des en-têtes courts et explicites
  • Aucune cellule fusionnée dans la zone de données

Les cellules fusionnées sont jolies dans un rapport, mais elles sont rarement les bienvenues dans une base de données. Elles compliquent les tris, les filtres et les formules. Gardez les mises en forme sophistiquées pour une feuille de présentation ou un tableau de bord.

Autre point important : évitez de saisir manuellement des variantes d’une même valeur. Par exemple, une colonne « Statut » contenant à la fois « En cours », « en cours », « EN COURS » et « En-cours » donnera des résultats incohérents lors des analyses.

Transformer la plage en tableau Excel

Une fois vos données saisies, sélectionnez une cellule de la plage, puis utilisez le raccourci Ctrl + T. Vous pouvez également passer par le menu Insertion > Tableau.

Excel vous demandera si votre tableau comporte des en-têtes. Vérifiez que l’option est bien cochée, puis validez.

Cette transformation apporte plusieurs avantages :

  • Les filtres sont ajoutés automatiquement
  • La mise en forme s’étend aux nouvelles lignes
  • Les formules sont recopiées automatiquement
  • Les données s’intègrent plus facilement aux graphiques croisés dynamiques
  • Les références deviennent plus lisibles

Dans l’onglet de conception du tableau, pensez à lui donner un nom explicite. Par exemple, vous pouvez le nommer Clients, Commandes ou Produits. Évitez les noms génériques comme Tableau1 ou Tableau2 : après quelques mois et plusieurs fichiers, vous ne saurez plus vraiment à quoi ils correspondent.

Avec un tableau nommé Commandes, une formule peut devenir beaucoup plus claire. Pour calculer le chiffre d’affaires d’une ligne, vous pouvez écrire :

=Commandes[@Quantité]*Commandes[@[Prix unitaire]]

Cette écriture utilise les références structurées d’Excel. Elle est souvent plus facile à comprendre qu’une formule basée sur des coordonnées comme D2*E2.

Choisir les bons types de données

Excel peut interpréter une donnée comme du texte, un nombre, une date ou une valeur logique. Ce détail a une grande importance pour les tris et les calculs.

Dans une colonne de dates, saisissez de véritables dates Excel, et non du texte qui ressemble à une date. Une date correctement reconnue pourra être filtrée par mois ou par année et utilisée dans des calculs.

Pour vérifier rapidement la nature d’une donnée, modifiez temporairement son format ou utilisez la fonction :

=ESTNUM(A2)

Si le résultat est VRAI, Excel considère la cellule comme un nombre ou une date numérique. Si le résultat est FAUX, la cellule est probablement enregistrée comme du texte.

Pour les identifiants, les codes postaux ou les références produit, le texte peut être préférable. Un code postal commençant par zéro, comme 01230, risque en effet d’être transformé en 1230 si Excel le considère comme un nombre.

Utiliser la validation des données

La validation des données permet de limiter les erreurs de saisie. Elle est particulièrement utile pour les colonnes qui doivent contenir un choix prédéfini.

Imaginons une colonne « Statut » avec trois valeurs possibles : À traiter, En cours et Terminé. Sélectionnez la colonne concernée, puis rendez-vous dans Données > Validation des données. Choisissez le type Liste, puis indiquez les différentes valeurs autorisées.

Un menu déroulant apparaîtra dans chaque cellule. L’utilisateur n’aura plus besoin de retaper le statut à chaque fois, ce qui évite les fautes et les variantes inutiles.

Vous pouvez appliquer le même principe pour :

  • Les catégories de produits
  • Les régions commerciales
  • Les moyens de paiement
  • Les niveaux de priorité
  • Les responsables de dossier

Pour une liste plus facile à maintenir, placez les valeurs autorisées dans une feuille dédiée, par exemple une feuille nommée « Paramètres ». Vous pourrez ensuite modifier cette liste sans toucher à la base principale.

Créer des identifiants uniques

Chaque enregistrement devrait pouvoir être identifié sans ambiguïté. Le nom d’un client n’est pas toujours suffisant : deux personnes peuvent porter le même nom, et une entreprise peut avoir plusieurs contacts.

Ajoutez donc une colonne d’identifiant unique, comme un numéro client ou un numéro de commande. Cet identifiant peut être saisi manuellement ou généré avec une formule.

Pour créer un identifiant simple à partir du numéro de ligne, vous pouvez utiliser :

= »CLI-« &TEXTE(LIGNE()-1; »0000 »)

Cette formule pourra produire des valeurs comme CLI-0001, CLI-0002 ou CLI-0003. Elle convient à un fichier simple, mais gardez à l’esprit qu’un identifiant basé sur le numéro de ligne peut changer si les lignes sont déplacées ou supprimées.

Dans un contexte plus avancé, Power Query ou VBA peuvent générer des identifiants réellement persistants. Pour la plupart des besoins courants, une colonne dédiée et une vérification des doublons suffisent.

Repérer les doublons et les erreurs

Les doublons sont l’un des problèmes classiques des bases de données. Ils peuvent fausser un total, envoyer deux fois une relance ou donner une vision erronée du nombre de clients.

Pour mettre en évidence les doublons, sélectionnez la colonne concernée, puis utilisez Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Valeurs en double.

Vous pouvez aussi utiliser la fonction NB.SI. Pour vérifier si une valeur de la colonne A apparaît plusieurs fois :

=NB.SI($A:$A;A2)>1

Cette formule renvoie VRAI lorsque la valeur existe plusieurs fois dans la colonne.

Avant de supprimer un doublon, vérifiez toutefois qu’il s’agit bien d’une erreur. Deux commandes identiques en apparence peuvent correspondre à deux opérations réellement différentes. Excel ne connaît pas votre activité : il applique les règles que vous lui donnez, même lorsqu’elles n’ont aucun sens métier.

Ajouter des colonnes calculées

Une base de données ne doit pas uniquement stocker des informations saisies manuellement. Elle peut aussi calculer automatiquement certains indicateurs.

Dans une base de commandes, vous pouvez ajouter une colonne « Total » avec la formule :

=[@Quantité]*[@[Prix unitaire]]

Vous pouvez ensuite créer une colonne « Remise » et calculer le montant final :

=[@Total]*(1-[@Remise])

Dans un tableau Excel, la formule se recopie automatiquement aux nouvelles lignes. Vous réduisez ainsi les risques d’oublier une formule ou de faire référence à la mauvaise ligne.

Quelques colonnes calculées utiles :

  • Montant total
  • Durée de traitement
  • Âge d’un client ou d’un produit
  • Année et mois d’une date
  • Écart entre une date prévue et une date réelle
  • Statut automatique selon une condition

Pour obtenir automatiquement l’année d’une date, utilisez par exemple =ANNEE([@Date]). Pour extraire le mois, la fonction =MOIS([@Date]) peut être utile dans certains rapports.

Exploiter la base avec les filtres et les recherches

Une fois la base structurée, les filtres du tableau permettent d’afficher uniquement les données qui vous intéressent. Vous pouvez filtrer une période, un responsable, une catégorie ou un statut en quelques clics.

Pour récupérer une information à partir d’un identifiant, la fonction RECHERCHEX est particulièrement pratique dans les versions récentes d’Excel :

=RECHERCHEX(A2;Clients[ID client];Clients[Entreprise]; »Client introuvable »)

Cette formule recherche l’identifiant présent en A2 dans la colonne « ID client » et renvoie le nom de l’entreprise correspondante.

Si votre version d’Excel ne dispose pas de RECHERCHEX, vous pouvez utiliser RECHERCHEV, même si elle est moins flexible :

=RECHERCHEV(A2;Clients;4;FAUX)

Dans tous les cas, prévoyez un message lorsque la recherche ne trouve aucun résultat. Une formule qui affiche une erreur incompréhensible n’aide personne, surtout un lundi matin.

Analyser les données avec un tableau croisé dynamique

Les tableaux croisés dynamiques permettent de résumer rapidement une base de données volumineuse. À partir d’une liste de commandes, vous pouvez obtenir le chiffre d’affaires par mois, par commercial ou par catégorie de produit.

Pour en créer un, cliquez dans le tableau, puis sélectionnez Insertion > Tableau croisé dynamique. Choisissez ensuite les champs à placer dans les zones Lignes, Colonnes, Valeurs et Filtres.

Un exemple d’analyse simple :

  • Placez « Catégorie » dans la zone Lignes
  • Placez « Mois » dans la zone Colonnes
  • Placez « Total » dans la zone Valeurs
  • Placez « Statut » dans la zone Filtres

Vous obtenez ainsi une synthèse du chiffre d’affaires par catégorie et par mois, avec la possibilité d’exclure les commandes annulées.

Pensez à actualiser le tableau croisé dynamique après l’ajout de nouvelles données. Si votre source est un véritable tableau Excel, les nouvelles lignes seront généralement intégrées à la source, mais l’actualisation reste nécessaire pour mettre à jour les résultats.

Séparer les données, les paramètres et les analyses

Pour conserver un fichier lisible, évitez de tout placer sur une seule feuille. Une organisation simple peut être composée de trois onglets :

  • Base : les données brutes, sous forme de tableau
  • Paramètres : les listes utilisées dans les menus déroulants
  • Analyse : les indicateurs, tableaux croisés et graphiques

La feuille Base doit rester propre et stable. Évitez d’y ajouter des titres décoratifs, des totaux manuels ou des blocs de texte. La feuille Analyse, elle, peut être mise en forme pour être consultée par une équipe ou présentée à un responsable.

Cette séparation facilite également la maintenance. Si une formule ou un graphique doit être modifié, vous ne risquez pas de perturber les données originales.

Protéger et sauvegarder la base

Lorsque plusieurs personnes utilisent un fichier, protégez les zones qui ne doivent pas être modifiées. Vous pouvez verrouiller les cellules contenant des formules et laisser déverrouillées les cellules de saisie.

La protection de feuille se trouve dans l’onglet Révision. Elle ne remplace pas un véritable système de gestion des droits, mais elle évite déjà de supprimer une formule importante par inadvertance.

Pensez aussi à effectuer des sauvegardes régulières. Une base de données représente souvent plusieurs heures, voire plusieurs années de travail. Enregistrez des versions datées du fichier ou utilisez un espace de stockage synchronisé comme OneDrive.

Si la base devient très volumineuse, si plusieurs utilisateurs doivent saisir des informations en même temps ou si les relations entre les données deviennent complexes, Excel atteindra ses limites. Il faudra alors envisager Access, une base SQL ou un outil spécialisé. Mais pour de nombreux besoins professionnels, une base Excel bien conçue reste une solution rapide, flexible et économique.

La clé tient finalement en quelques habitudes : une ligne par enregistrement, une colonne par information, des données cohérentes et un tableau Excel correctement structuré. Avec cette méthode, vos filtres, vos formules et vos analyses fonctionneront beaucoup mieux… et votre fichier cessera progressivement de ressembler à un puzzle dont il manque trois pièces.