13 règles d'or pour parfaitement gérer vos données sur Excel

Bonjour à tous,

L’utilisation d’Excel comme gestionnaire de données est une situation extrêmement fréquente… Rappelons tout d’abord qu’à l’origine, Excel n’est pas fait pour stocker des données, mais pour faire des calculs ! Cependant, les usages l’ont très rapidement transformé en outil de gestion de bases de données (une ou plusieurs tables/listes/tableaux de données), et les récents développements d'Excel permettant le stockage de plusieurs millions de lignes vont encore accélérer ce mouvement. Je pense personnellement qu’Excel est dans de nombreuses situations un excellent outil de gestion de données, A CONDITION de respecter un certain nombre de règles!

Une fois ces règles acquises, vous pouvez aussi ajouter un formulaire au fichier grâce à mon dernier outil : FormXL Pro

Je vous présente donc ici ma sélection des 13 règles d’or pour utiliser Excel comme table de données !

Récapitulatif des règles
Règle 0 - Utiliser les tableaux d'Excel
Règle 1 – Une feuille = une table de données et surtout rien d’autre
Règle 2 – Dans la première ligne : uniquement et seulement les titres de colonnes
Règle 3 – Pas de cellules vides dans les titres de colonnes
Règle 4 – Pas de doublons dans les titres de colonnes
Règle 5 – Une clef primaire dans la première colonne
Règle 6 – Pas de lignes et colonnes vides
Règle 7 – Ne pas ajouter de totaux, sous-totaux et calculs intermédiaires
Règle 8 – Utiliser les filtres automatiques
Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimension
Règle 10 – Positionner les données numériques et les calculs dans la partie droite de la table
Règle 11 – Utiliser les tableaux d’Excel
Règle 12 – N’utiliser qu’une seule formule par colonne
Règle 13 – Utiliser l’outil de validation des données

Règle 0 – Utilisez les tableaux d'Excel retour
[Edit du 10/3/2016] - Je remonte cette règle, car réellement, depuis l'introduction des tableaux en 2007, cela rend obligatoire un certain nombre de règles ci-dessus.
Pour ajouter un tableau, se placer sur vos données, puis Accueil et Mettre sous forme de tableau :
mise-forme-tableau 1

Règle 1 – Une feuille = une table de données et surtout rien d’autre retour
Il est indispensable que chaque table occupe une feuille, et rien qu’une feuille. En effet, avoir deux tables sur la même feuille de calcul est potentiellement très dangereux. Par exemple, vous courrez le risque de supprimer par inadvertance des lignes appartenant aux deux tables.
Règle 1
Règle 2 – Dans la première ligne : uniquement et seulement les titres de colonnes retour
La première ligne de la feuille contient toujours et uniquement les titres et doit se trouver en ligne 1 :
Règle 1
Si vous voulez ajouter des boutons, des grands titres ou d’autres informations, augmentez la hauteur de la première ligne et ajoutez un cadre blanc au dessus (outils de dessin) pour améliorer la lisibilité et la navigation dans le fichier. On se trouve alors sur un autre plan que les données et ceci fonctionne correctement :
Règle 1
Encore une fois, l’idée est de n’avoir que les données sur la feuille au niveau des cellules, et rien d’autre.
Règle 3 – Pas de cellules vides dans les titres de colonnes. retour
En effet, vous perdez l’information concernant le contenu de la colonne et en plus, vous ne pourrez pas utiliser correctement les tableaux croisés dynamiques.
Un corollaire de cette règle est de ne JAMAIS utiliser de cellules fusionnées, que ce soit pour les titres de colonnes ou à l’intérieur de la table.
Règle 1
Règle 4 – Pas de doublons dans les titres de colonnes retour
Afin de garantir que chaque colonne désigne un élément spécifique, il faut se garder d’utiliser le même nom de colonnes deux fois.
Règle 1
Règle 5 – Une clef primaire dans la première colonne retour
Lorsqu’on construit une table dans un gestionnaire de données de type Access, on ajoute toujours une clef primaire à cette table. Cette clef n’existe pas dans Excel, donc il est indispensable de l’ajouter manuellement (ou automatiquement avec VBA). On choisira une clef qui s’auto-incrémentera lors de l’ajout d’une nouvelle ligne.
Règle 1
Aucune cellule vide dans cette colonne ne doit exister, en d’autres termes, chaque ligne doit avoir une clef. Ceci permettra d’effectuer un comptage du nombre de lignes (fonction nombre dans les tableaux croisés dynamiques), ou encore de retrouver rapidement une fiche papier si la base a été saisie à partir de données papier.
Règle 1
Dans le cas où vous utilisez des noms dynamiques, cela permet aussi de définir la hauteur d’une table de données (plus d’information sur les noms dynamiques ici : https://www.polykromy.com/html/poly_main_cours_decaler.html). Depuis l'introduction des tableaux en 2007, je n'utilise plus du tout cette approche, mais je définis un nom sur chaque colonne du tableau.

Règle 6 – Pas de lignes et colonnes vides retour
En laissant une ligne ou une colonne vide, vous risquez de ne travailler que sur une partie de la table (reconnaissance automatique de la table par Excel).
Règle 1
Règle 7 – Ne pas ajouter de totaux, sous-totaux et calculs intermédiaires retour
En ajoutant ces calculs dans la feuille contenant les données, vous courrez le risque que ceux-ci soient comptabilisés dans des tableaux croisés dynamiques ou dans d’autres calculs effectués sur des colonnes entières.
Règle 1
Règle 8 – Utiliser les filtres automatiques retour
L’utilisation des filtres automatiques permet de travailler directement sur toute la table de données sans se poser des questions. Ceci est particulièrement utile pour filtrer les données (évidemment), mais aussi pour créer des tableaux croisés dynamiques ou encore pour trier les données par ordre alphabétique.
Rappel : aller dans l’onglet (ou le menu) données, puis choisir filtres automatiques

Règle 1

Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimension retour
Vous pouvez retrouver une explication détaillée et en vidéo ici : https://www.xlerateur.com/?p=496
Un exemple classique est la création de nouvelles colonnes pour chaque nouveau mois. Ceci indique en général un problème dans la structure des données et surtout, cela va complexifier les calculs et les comparaisons par mois. Dans de tels cas, il vaut mieux restructurer la table pour mettre la date dans une seule colonne (sauf cas particulier).
Règle 1

Règle 10 – Positionner les données numériques et les calculs dans la partie droite de la table retour
De manière générale, on met les données numériques et les calculs à droite de la table de données. Ceci permet de localiser rapidement les calculs, mais aussi de respecter une certaine logique dans la lecture des informations. Finalement, lors de la construction d’un tableau croisé dynamique, cela permet de plus facilement créer des tableaux complexes.
Règle 1
De manière assez logique, on essaiera aussi de faire en sorte que l'enchainement des formules se fasse de gauche à droite. Ceci permet d'améliorer la vitesse d'exécution ainsi que la lisibilité des formules (et donc d'éviter des erreurs).
Règle 11 – Utiliser les tableaux d’Excel retour
Introduit et enrichit progressivement au fur et à mesure des versions d’Excel, l’utilisation des tableaux (autrefois appelés Liste sous 2003) s’avère relativement efficace notamment pour saisir des données directement dans le tableau. Voici comment les mettre en place :
- 2003 : Menu données, puis Liste
- 2007 et suivant : Sélectionnez votre plage de données, puis dans l’onglet Accueil, choisir Mettre sous forme de tableau
Règle 1
Note : Il y a beaucoup de débats sur la sémantique autour des tables/listes/tableaux de données. Au final, on parle globalement de plus ou moins de la même chose : une structure avec des titres de colonnes et des lignes reprenant les données.
Règle 12 – N’utiliser qu’une seule formule par colonne retour
Si la base est bien structurée, il est recommandé de n’utiliser qu’une seule formule pour toute la colonne, formule qui sera recopiée sur toute la colonne de la table. Cela permet entre autres, d’éviter des corruptions de données.
Règle 1
Règle 13 – Utiliser l’outil de validation des données retour
La validation des données sous Excel est un outil très puissant qui permet de vérifier par exemple que des dates ont bien été saisies, que les valeurs rentrées appartiennent à une liste, ou encore qu’une valeur se trouve entre un maximum et un minimum. Faire un cours complet sur cette fonctionnalité prendrait une journée, tant elle est puissante.
Voici un exemple permettant d’ ajouter une liste à une colonne. Tout d’abord définissez un nom, ici liste_villes (sélectionnez la plage, puis dans la zone de nom située à gauche de la zone de formule, tapez liste_villes, validez par entrée) qui fera référence à votre liste de valeurs.
Règle 1
Puis sélectionnez la colonne de la ville dans votre table de données et choisissez Données – Validation, puis dans le menu déroulant, Liste. Finalement, ajouter = liste_villes dans la zone "Source".
Règle 1
Il ne reste plus qu’à utiliser la zone de liste déroulante disponible lorsqu’on se trouve sur la cellule :
Règle 1
En respectant ces 13 règles vous pourrez construire des tables/listes/tableaux de données faciles à utiliser, rigoureux et solides.

Si vous en voyez d'autres n'hésitez pas à laisser un commentaire ci-dessous...

Pour aller plus loin :
- Ajouter un formulaire de saisie via FormXL Pro :

- Mon CD de formation "Organisez et gérez vos données" qui reprend un cours complet avec exemple, 4h de vidéo sur ces sujets et plusieurs autres règles d'or !

A bientôt

Gaëtan

Pour marque-pages : Permaliens.

25 réponses à 13 règles d'or pour parfaitement gérer vos données sur Excel

  1. site admin dit :

    Petite mise à jour à 7:03 sute à un commentaire de Dominique sur l'enchainement des formules qui doit se faire autant que possible de gauche à droite. Merci 😉

  2. Charles dit :

    Excellent...

  3. J.G.D dit :

    superbement bien expliqué !!!

  4. Diaz jean-Luc dit :

    Bonjour,

    Il ya quelques temps vous annonciez la sortie prochaine d'un cous sur VBA avance voire très avancé. J'avais répondu à un sondage qui portait sur le chois du support (vidéo ou CD) et personnellement j'étais favorable au CD mais cela est un détail.
    Je suis impatient de connaître la date de sortie de ce nouveau cours qui j' en suis certain sera excellent comme pour les autres CD que je possède.
    Merci pour votre réponse.
    Cordialement
    Jean-Luc Diaz

  5. site admin dit :

    Bonjour,

    Le cours surVBA avancé n'est pas encore finalisé.

    J'ai du faire face à d'autres impératifs, mais ca reste dans mes priorités. Donc malheureusement, pas de date ferme de prévue.

    Je vous tiendrai au courant de la suite.

    Merci pour votre question.

    Gaetan Mourmant

  6. ARISTIDE YANDO dit :

    j' attend avec impatience ce merci de tout vos courage

    ARIS

  7. LJ dit :

    Merci Gaetan de rappeler très clairement ces "bonnes pratiques" qui au final permettent..
    ... de gagner du temps
    ... augmenter la fiabilité du traitements de données
    ... et d'améliorer l'exploitation de celles-ci.

    Cdlt
    LJ

  8. Ping :XLérateur » Blog Archive » Les outils pour analyser vos données - Xlérer vos connaissances sur Excel

  9. J-F L dit :

    franchement bravo et merci

  10. billy dit :

    Bonjour,

    J'ajouterai la règle "Format minimaliste et standardisé des données", qui rejoint et élargit la règle 13 Validations.

    Par exemple, exprimer les pourcentages en décimales donc en nombre standard, car il est plus facile de multiplier les décimales par 100 lors de la présentation finale que de se demander pour chaque nombre s'il faut lui appliquer une opération.
    Ou encore, tout en minuscules, pas d'accent (surtout sur les majuscules !), etc.
    La donnée doit être la plus brute possible !

    Un grand merci pour ces 13 règles 😀
    Cordialement.

  11. c bullet dit :

    the best site i have found!!! merci beaucoup de la part d'une personne très novice mais très motivée!!!

  12. Gaetan Mourmant dit :

    Merci à C Bullet 😉

  13. David dit :

    Simplement dit : données simplement stockées,et bien compactées (écologique quoi) si seulement les tableaux qu'on nous donnent à traité pouvez suivre ces règles

  14. Gaetan Mourmant dit :

    Oui, c'est bien pour ca que j'en parle ici 😉 J'en ai fait un CD complet si vous cherchez des idées de cadeaux de Noël 😉

  15. Clem dit :

    Très juste tous ces conseils ! Merci
    Depuis l'introduction des tableaux, il n'est toutefois plus aussi crucial de placer la première entête en ´A1'
    Mais cela reste une bonne idée si l'on ne comprend pas toutes les implications d'un tel choix

  16. Gaetan dit :

    Bonjour Clem,

    Oui, je suis d'accord avec vous. Ca reste cependant une bonne pratique, ca évite la tentation de vouloir avoir autre chose qu'un tableau dans une feuille (et donc de supprimer par inadvertance des données !).

    Merci pour votre commentaire.

    Gaetan

  17. veronique NICOLE dit :

    1ère visite sur votre site. je suis épatée. efficience, je crois qu'il n'y a pas d'autre mot pour résumer vos propos. merci beaucoup. vero

  18. Gaetan dit :

    Bienvenue Véronique et merci pour vos commentaires.
    Gaetan

  19. Daniel dit :

    Bonjour Gaëtan,
    Merci beaucoup pour ces informations *élémentaires* mais qui m'étaient inconnues. Je pensais avoir quelques bases mais me rends compte que j'en suis loin.
    Super infos bien utiles..
    Merci encore

  20. Gaetan dit :

    Bonjour Daniel,
    Bien content que tout ceci vous soit utile.
    A bientôt
    Gaetan

  21. Ping :Informatique - clauche3107 | Pearltrees

  22. meftah dit :

    qu'est ce qu'un multi colonnes et comment le créer !?

  23. Gaetan dit :

    Bojour Meftah,
    Je ne comprends pas bien ce que vous voulez dire par multi-colonnes?
    Cordialement
    Gaetan

  24. Ping :Excel | Pearltrees

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *


La période de vérification reCAPTCHA a expiré. Veuillez recharger la page.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.