Depuis des années, Excel a une mauvaise manie, celle de transformer des données, automatiquement, sans nous demander la permission ☹️, c’est-à-dire :
- transformer des codes en dates
- JAN01 → devient le 1er janvier de l’année en cours alors que l’année n’est pas spécifiée à l’origine
- supprimer les zéros à gauche d’un nombre
- Un numéro de facture « 000154 » → devient 154
- convertir des numéros longs en notation scientifique
- 123456789012345678 → devient 1,23456E+17
- transformer des codes qui contiennent un E entouré de chiffres, en nombre avec notation scientifique
- Un code de produit « 123E45 » → devient 1,23E+45
Cela est maintenant chose du passé, nous avons maintenant le contrôle sur la conversion de données dans Excel à partir des réglages (options).
Vous préférez la version vidéo ? La voici!
Dans quels scénarios ces réglages s’appliquent-ils?
Ces nouveaux paramètres fonctionnent dans plusieurs situations:
- Saisie de données dans une cellule : Super pratique, entre autres, si vous entrez régulièrement des nombres pour lesquels vous souhaitez conserver des zéros au début du nombre au lieu d’avoir à insérer une apostrophe (exemple : ‘0000125666) devant les zéros pour pouvoir les conserver.
- Copier-coller depuis le Web ou un autre outil
- Ouverture de fichiers CSV ou TXT
Données Démo : Fichier CSV
Pour démontrer le tout, nous allons utiliser un fichier CSV qui contient des données de facturation.
Lorsque nous ouvrons ce fichier dans Excel, la conversion automatique de 4 colonnes (voir en jaune dans l’image ci-dessus) nous pose problème :
- NoCompte : on perd les 2 premiers zéros
- NoFacture : on perd les 3 premiers zéros
- CodeMois : JAN31 : devient le 31 janvier 2026 alors que les autres codes débutant par FEB restent tels quels. (La raison est que le mois de février est inscrit ici en anglais et que mes paramètres sont en français, car autrement, toute la colonne aurait été transformée en date avec l’année en cours.)
- Reference : Les codes de référence utilisant seulement des nombres sont transformés en valeur numérique avec une notation scientifique.
Au moment d’ouvrir le fichier CSV en question, Excel affiche un message qui spécifie, au centre, quels types de données il a choisi de convertir dans mon fichier.
Si l’on clique sur Convertir, il va effectuer la conversion de tous les types de conversions spécifiées. D’autre part, si l’on clique sur Ne pas convertir, les données resteront telles qu’elles étaient à l’origine dans le fichier CSV.
Nous avons le choix entre tout ou rien !
Cependant, sachez que vous pouvez changer ceci dans vos options Excel, et ainsi choisir quelles conversions seront effectuées, ou pas, par défaut.
Étapes pour modifier la conversion automatique des données
Pour modifier la conversion par défaut, il faut aller dans le menu Fichier, puis dans le menu Options. Ensuite, il faut sélectionner Données dans le panneau de gauche. Puis, dans le panneau de droite, il faut se diriger vers la section Conversion automatique des données.
Les conversions que l’on peut maintenant activer ou désactiver
- Supprimer les zéros à gauche et convertir en nombre
- Tronquer les nombres à 15 chiffres et utiliser la notation scientifique
- Convertir les nombres contenant la lettre « E » en notation scientifique
- Convertir une chaîne lettres + chiffres en date
Selon le type de données que vous utilisez régulièrement, vous pouvez cocher/décocher les cases qui s’appliquent. Par exemple, si vous ouvrez régulièrement des fichiers CSV avec des numéros de clients qui comportent des 0000 au début, et que vous désirez garder ces zéros, vous devez décocher l’option Supprimer les zéros non significatifs et convertir en nombre.
Par exemple, je pourrais choisir de conserver seulement l’option Supprimer les zéros non significatifs et convertir en nombre et de laisser également la case, sous Options supplémentaires cochée : Lors du chargement d’un fichier .csv ou d’un fichier similaire, m’avertir des conversions de données automatiques. Ceci me permettra de choisir si je désire supprimer les zéros ou non selon le fichier chargé.
En ouvrant le fichier CSV j’obtiens alors le message qui demande si je désire convertir avec la règle suivante « Supprimer les zéros non significatifs » :
Si l’on clique sur Convertir, les données seront chargées telles qu’on les voit en arrière-plan. D’un autre côté, en cliquant sur Ne pas convertir, les zéros devant nos nombres resteront présents.
Résultat avec Convertir
Résultat avec Ne pas convertir
Dans tous les cas, le JAN31 de le colonne CodeMois n’est pas converti car nous avons décoché l’option Convertir une chaîne lettres + chiffres en date . J’attire toutefois votre attention sur la colonne Reference. Bien que nous ayons décoché les options Conserver les 15 premiers chiffres des nombres longs et les afficher en notation scientifique et Convertir les chiffres qui entourent la lettre E en un nombre en notation scientifique, Excel a tout de même converti quelques données en nombre scientifique, il faut donc tout de même rester attentif au résultat une fois les données chargées.
Qu’est-ce qu’un fichier CSV et pourquoi Excel se comporte-t-il de la sorte?
Un fichier CSV est un simple fichier contenant du texte. Il n’indique aucune information sur les colonnes, seulement des caractères.
Excel doit donc deviner :
- Quel est le séparateur qui délimite chacune des colonnes
- Quel est le format régional
- Comment interpréter les types de données
C’est ce qu’il tente de faire avec la conversion automatique. Comme Excel ne peut pas deviner comment je veux utiliser ces données, l’option de pouvoir configurer son comportement par défaut devient vraiment intéressante.
Les lacunes de la conversion automatique lors de l’ouverture de fichiers CSV ou TEXTE
Il est important de spécifier que la conversion automatique :
- n’applique aucune transformation structurée
- demeure un processus manuel
- ne règle pas tous les problèmes de qualité de données
Quand Power Query devient indispensable
Power Query reste l’outil à privilégier dès que l’on parle de transformation de données qui doit être répétée à plusieurs reprises.
Typiquement, Power Query est plus approprié lorsque :
- les données provenant d’une source externe, d’un même format, doivent être analysées régulièrement
- il faut nettoyer, transformer, normaliser les données
- le processus doit être réutilisable
Contrairement aux options de conversion automatique, Power Query :
-
- ne “devine” pas : vous définissez explicitement les types de données
- applique les mêmes règles à chaque actualisation
- vous choisissez explicitement : le séparateur et l’encodage
Pour en apprendre davantage sur Power Query, je vous invite à consulter l’article suivant : L’éditeur de requêtes Power Query pour Excel et Power BI
La bonne approche : les deux sont complémentaires
Plutôt que de les opposer, il faut voir ces fonctionnalités comme complémentaires :
Pour aller plus loin sur les conversions de données dans Excel
Voici quelques ressources complémentaires publiées sur Le CFO masqué…
- Récupérez les accents d’origine d’un fichier importé dans Excel
- Remplacez des points par des virgules dans vos fichiers Excel
Formation complémentaire
Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la formation Excel – Introduction à Power Query et au langage M.
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
