20 compétences Excel et de tableur qui vous feront gagner des heures chaque semaine

La plupart des gens qui utilisent Excel tous les jours exploitent moins d'un quart de ses capacités. Ils retapent des données que Flash Fill pourrait nettoyer en quelques secondes. Ils parcourent des milliers de lignes à la recherche d'une seule valeur qu'une formule de recherche pourrait trouver instantanément. Ils reconstruisent le même tableau récapitulatif chaque semaine au lieu de configurer une table pivot une fois et de la rafraîchir d'un clic. Rien de tout cela n'est un échec d'effort. L'interface d'Excel cache ses fonctionnalités les plus utiles derrière des menus, et la plupart des gens apprennent le programme par essais et erreurs plutôt que par une formation formelle.

Les compétences de cette liste ne sont pas exotiques. Ce sont les petites actions répétables qui différencient quelqu'un qui lutte avec son tableur de quelqu'un qui le maîtrise. Une poignée de raccourcis clavier, un ensemble de fonctions qui remplacent le comptage manuel lent, et quelques astuces de formatage transforment un mur de chiffres en quelque chose de lisible en un coup d'œil. Certaines de ces fonctionnalités existent depuis des décennies et n'ont jamais été incluses dans une formation. D'autres, comme XLOOKUP, sont des ajouts plus récents qui ont discrètement remplacé des méthodes plus anciennes et maladroites que la plupart des gens utilisent encore par habitude.

Aucune de ces compétences ne nécessite un bagage en codage ou l'aide d'un département informatique. La plupart prennent moins de cinq minutes à apprendre, et une fois apprises, elles économisent ce temps ou plus chaque jour. Un spécialiste du marketing construisant un rapport mensuel, un propriétaire de petite entreprise suivant les stocks, un étudiant organisant des données de recherche et un gestionnaire examinant un budget y trouveront tous quelque chose d'utile.

La liste va des compétences les plus utiles aux plus spécialisées. Les raccourcis et astuces de formatage qui s'appliquent à presque tout tableur viennent d'abord, suivis par des fonctions et outils adaptés à des ensembles de données plus grands ou plus désordonnés. La plupart de ce qui suit fonctionne presque de la même manière dans Google $GOOGL Sheets, avec seulement des différences mineures dans les noms de menu ou la syntaxe. La logique sous-jacente se transfère même pour les lecteurs qui ouvrent rarement Excel lui-même.

Il n'est pas nécessaire d'apprendre les 20 compétences d'un coup. En choisir deux ou trois et les utiliser de façon constante pendant une semaine suffit à changer la manière dont un tableur est construit et le temps qu'il faut pour en construire un.

Credit: Canva Images

Faire défiler un tableur avec une souris est l'un des moyens les plus lents de se déplacer dans Excel, et c'est aussi l'un des plus courants. Une poignée de raccourcis clavier éliminent presque tout cela.

Ctrl plus une touche fléchée saute au bord d'un bloc de données dans cette direction. Il passe devant les cellules vides et s'arrête à la dernière cellule remplie avant un espace. C'est le moyen le plus rapide de passer du haut d'une colonne au bas de milliers de lignes sans un seul défilement. Ctrl+Home saute à la cellule A1 peu importe où le curseur se trouve actuellement. Ctrl+End saute à la dernière cellule qui contient des données ou un formatage, ce qui est utile pour confirmer la taille réelle d'une feuille.

Passer d'un onglet à l'autre est plus rapide avec Ctrl+Page Up et Ctrl+Page Down que de cliquer sur chaque onglet individuellement, surtout dans des classeurs avec une douzaine de feuilles ou plus. Pour aller directement à une cellule spécifique, F5 ou Ctrl+G ouvre la boîte Aller à, où taper une référence de cellule comme Z500 y déplace le curseur immédiatement, pas besoin de faire défiler.

Sélectionner des lignes ou colonnes entières est plus rapide avec Shift+Espace, qui sélectionne la ligne, et Ctrl+Espace, qui sélectionne la colonne, que de glisser sur un en-tête. Combiner l'un ou l'autre avec Ctrl+Shift+Flèche pour étendre une sélection de la cellule actuelle au bord d'un bloc de données en un seul mouvement. C'est utile avant de copier, supprimer ou formater une grande plage.

Aucun de ces raccourcis ne nécessite de mémoriser un manuel. Ils correspondent à la logique utilisée par Windows ailleurs, ce qui fait qu'ils restent une fois appris. Une personne qui en utilise même trois ou quatre régulièrement remarquera en une semaine que naviguer dans un grand classeur prend une fraction du temps qu'il fallait auparavant.

Ces raccourcis se retrouvent presque exactement dans Google $GOOGL Sheets. Les deux programmes partagent des conventions de clavier héritées de décennies de conception de logiciels de tableur. Toute personne qui partage son travail entre les deux plateformes n'a pas besoin d'apprendre un deuxième jeu d'habitudes.

Credit: Canva Images

Flash Fill, introduit dans Excel 2013, regarde ce qu'une personne tape dans une colonne et essaie de détecter un modèle basé sur les données déjà présentes à côté. Une fois qu'il reconnaît ce modèle, il propose de remplir le reste de la colonne automatiquement, sans formule nécessaire.

L'exemple classique est de séparer un nom complet en prénoms et noms de famille. Taper "John" dans la première ligne d'une colonne à côté de celle qui lit "John Smith" déclenche une suggestion. Une fois qu'une deuxième entrée confirme le modèle, Excel remplit le reste des prénoms, correctement séparés des noms de famille. Le même outil peut extraire un domaine d'une adresse e-mail, reformater des numéros de téléphone dans un style cohérent, ou combiner un prénom et un nom de famille en un seul champ. Il peut également convertir un format de date désordonné en un format standard.

Flash Fill peut être déclenché en tapant le modèle manuellement jusqu'à ce qu'Excel propose une suggestion en gris, puis en appuyant sur Entrée pour l'accepter. Il peut également être déclenché directement via Ctrl+E une fois qu'au moins une ligne exemple est remplie, ou via le bouton Flash Fill dans l'onglet Données.

L'outil fonctionne en faisant correspondre des motifs dans les données visibles, pas en appliquant une règle fixe. Cela le rend mieux adapté aux données sources propres et cohérentes et aux travaux de nettoyage ponctuels, plutôt qu'à tout ce qui doit continuer à fonctionner automatiquement à mesure que de nouvelles lignes sont ajoutées. Si les données sources changent, Flash Fill ne se met pas à jour de lui-même comme le ferait une formule. Pour un ensemble de données qui n'a besoin d'être nettoyé qu'une seule fois, cela pose rarement un problème.

Il vaut la peine de vérifier soigneusement les résultats avant de les accepter, surtout avec de grands ensembles de données. Flash Fill peut occasionnellement mal lire un motif si les données sources ont un formatage incohérent en cours de route. Parcourir la colonne remplie pour repérer les entrées qui semblent déplacées prend quelques secondes et empêche une petite erreur de se glisser dans un rapport fini.

Credit: Lukas Blazek / Pexels

Un tableau croisé dynamique prend une longue liste de données brutes et la transforme en un résumé sans nécessiter une seule formule. Au lieu d'ajouter manuellement les ventes par région ou de compter les commandes par mois, un tableau croisé dynamique effectue l'agrégation en faisant glisser les noms de champ dans différentes cases.

Pour en créer un, sélectionnez n'importe quelle cellule à l'intérieur d'une plage de données, puis allez dans l'onglet Insertion et choisissez Tableau Croisé Dynamique. Excel ouvre une nouvelle feuille avec une liste de champs sur le côté. Faire glisser un champ dans la case Lignes décompose le résumé par cette catégorie. Faire glisser un champ dans la case Valeurs calcule un total pour celui-ci, somme par défaut, bien que cela puisse être changé en moyenne, en compte, ou en plusieurs autres options. Faire glisser un champ dans la case Filtres permet de restreindre un rapport à une région, une gamme de produits ou une période de temps sans toucher aux données sous-jacentes.

Les dates insérées dans un tableau croisé dynamique peuvent être regroupées automatiquement en mois, trimestres ou années. Cela transforme une colonne de dates de transaction individuelles en une tendance mensuelle claire sans écrire une seule fonction de date. Changer le résumé d'un total à une moyenne, ou d'un compte à un pourcentage du total général, nécessite quelques clics plutôt qu'une formule reconstruite.

Le plus grand avantage par rapport à un résumé manuel est ce qui se passe lorsque les données sources changent. Un résumé construit manuellement doit être reconstruit ou ses formules vérifiées à chaque fois que de nouvelles lignes sont ajoutées. Un tableau croisé dynamique doit simplement être actualisé, ce qui se fait via l'onglet Données ou en cliquant avec le bouton droit n'importe où dans le tableau, et il recalculera automatiquement avec les données actuelles.

Les tableaux croisés dynamiques ont la réputation d'être compliqués, mais la mécanique réelle se rapproche davantage du tri d'un ensemble de boîtes étiquetées que de la programmation. Toute personne à l'aise avec le glisser-déposer peut en construire un fonctionnel dès la première tentative. La compétence se transfère directement dans Google $GOOGL Sheets, qui a sa propre version du même outil sous le même nom.

Credit: RDNE Stock project / Pexels

RECHERCHEV fait partie d'Excel depuis les premières versions du logiciel et reste l'une de ses fonctions les plus largement utilisées. Elle présente également des limitations qui piègent même les utilisateurs expérimentés. Elle ne recherche que dans la colonne la plus à gauche d'une plage sélectionnée et renvoie une valeur d'une colonne à droite, comptée par position plutôt que par nom. Insérer une nouvelle colonne dans cette plage et le décompte change, cassant la formule sans aucun avertissement.

RECHERCHEX, introduit par Microsoft $MSFT en 2019 et déployé plus largement aux abonnés de Microsoft 365 en 2020, résout la plupart de ces problèmes. Il peut rechercher dans n'importe quelle direction, à gauche ou à droite, de sorte que la colonne de recherche n'a plus besoin de se trouver à gauche de la colonne de résultat. Il référence la colonne de résultat directement plutôt que de compter sa position, donc insérer ou supprimer des colonnes ailleurs dans la feuille ne casse pas la formule. Il opte également par défaut pour une correspondance exacte, là où RECHERCHEV opte pour une approximative — une différence qui a causé plus d'erreurs de feuille de calcul que presque tout autre paramètre dans Excel.

RECHERCHEX a également un moyen intégré de gérer une valeur non trouvée. Au lieu de renvoyer l'erreur #N/A que produit RECHERCHEV, RECHERCHEX peut être configuré pour renvoyer un message spécifique, un zéro ou une cellule vide à la place. Cela empêche cette erreur de se propager à d'autres formules qui dépendent du résultat.

Un cas d'utilisation typique est la recherche du prix d'un produit à partir d'une liste distincte en utilisant son code produit. Un autre est l'extraction du département d'un employé à partir d'un numéro d'identification stocké dans une feuille différente. Là où RECHERCHEV nécessite de compter exactement combien de colonnes à droite se trouve la réponse, RECHERCHEX pointe simplement vers la colonne elle-même.

RECHERCHEX nécessite Microsoft 365 ou Excel 2021 ou ultérieur. Il n'est pas disponible dans Excel 2019, 2016 ou toute version autonome antérieure. Quiconque possède une licence plus ancienne devra s'en tenir à RECHERCHEV ou INDEX et EQUIV jusqu'à ce qu'il effectue la mise à niveau. Google $GOOGL Sheets a depuis ajouté sa propre version de la fonction également.

Credit: Canva Images

Parcourir une colonne de chiffres à la recherche de quelque chose d'inhabituel est lent et facile à mal faire, surtout au-delà de quelques dizaines de lignes. Le formatage conditionnel effectue cette analyse automatiquement en appliquant un formatage, comme une couleur de remplissage ou une icône, aux cellules qui répondent à une règle définie à l'avance.

La fonctionnalité se trouve dans l'onglet Accueil sous Mise en forme conditionnelle. Quelques règles intégrées couvrent la plupart des besoins courants. Celles-ci incluent la mise en surbrillance des valeurs en double dans une liste, la mise en surbrillance des cellules qui sont au-dessus ou en dessous de la moyenne, ou la mise en surbrillance des valeurs les plus élevées ou les plus basses par classement. Des règles personnalisées peuvent aller plus loin, en utilisant une formule pour définir exactement quelles cellules doivent être signalées, comme marquer toute dépense qui dépasse une ligne budgétaire spécifique.

Les échelles de couleurs appliquent un dégradé sur une plage, ombrant les valeurs les plus élevées d'une couleur et les plus basses d'une autre. Cela transforme un tableau de chiffres en quelque chose de plus proche d'une carte thermique qui peut être lue d'un coup d'œil. Les barres de données remplissent chaque cellule avec une petite barre horizontale dimensionnée à sa valeur, utile pour comparer les magnitudes dans une colonne sans graphique séparé. Les ensembles d'icônes ajoutent de petits symboles, comme des flèches ou des couleurs de feux de circulation, à côté de chaque valeur en fonction de la plage dans laquelle elle se trouve.

Un gestionnaire qui examine un budget mensuel peut configurer la mise en forme conditionnelle une fois pour que tout élément de ligne dépassant l'objectif devienne rouge automatiquement. Cela est préférable à vérifier chaque ligne à l'œil chaque fois que la feuille est mise à jour. Un recruteur parcourant une feuille de calcul de candidats peut mettre en surbrillance toute personne manquant un champ requis sans rechercher manuellement.

Les règles peuvent être modifiées ou supprimées via l'option Gérer les règles de la mise en forme conditionnelle, qui répertorie chaque règle actuellement appliquée à une feuille en un seul endroit. Cela devient utile une fois qu'une feuille a accumulé plusieurs règles, car il est sinon facile d'oublier laquelle fait quoi. Les règles de mise en forme conditionnelle voyagent avec les cellules auxquelles elles sont appliquées, de sorte que copier une plage formatée dans une nouvelle feuille apporte automatiquement la mise en forme.

Credit: Birgit Müller (WMDE)  / Wikimedia Commons (CC BY-SA 4.0)

Une cellule qui accepte toute entrée tapée est une cellule qui contiendra éventuellement une faute de frappe, une abréviation incohérente ou une valeur qui n'appartient pas. La validation des données empêche cela en restreignant ce qui peut être entré dans une cellule, le plus souvent par une liste déroulante.

La configuration commence dans l'onglet Données, sous Validation des données. Choisir "Liste" comme type de validation permet un court ensemble de valeurs tapées directement dans la boîte de paramètres, séparées par des virgules. Il accepte également une référence à une plage de cellules ailleurs dans le classeur contenant les options approuvées. Une fois appliqué, la cellule affiche une petite flèche qui ouvre la liste des choix. Taper quoi que ce soit en dehors de cette liste déclenche un avertissement ou bloque l'entrée complètement, selon la rigueur de la règle définie.

C'est particulièrement utile sur toute feuille que plus d'une personne remplit, comme un suivi des dépenses partagé ou un journal de statut de projet. Sans validation, une personne pourrait taper "En cours", une autre "en cours", et une troisième "EC", ce qui rendrait la colonne inutile pour filtrer ou résumer plus tard avec une formule comme NB.SI. Une liste déroulante force tout le monde à utiliser le même ensemble d'options dès le départ.

La validation des données ne se limite pas aux listes. Le même menu permet à une cellule d'accepter uniquement des nombres entiers dans une plage, uniquement des dates dans une certaine fenêtre, ou uniquement du texte sous une certaine longueur. Elle peut également exiger une valeur qui passe une formule personnalisée, comme une entrée dans une cellule avant qu'une autre cellule puisse être remplie. Un message d'entrée peut également être ajouté, de sorte que cliquer sur la cellule affiche une petite info-bulle expliquant ce qui est attendu. Cela est utile sur un modèle que d'autres personnes rempliront sans orientation.

Parce que la liste source pour un menu déroulant peut pointer vers une plage séparée, la mise à jour des valeurs approuvées à un endroit met à jour chaque menu déroulant construit à partir de celle-ci. Il n'est pas nécessaire de modifier les paramètres de chaque cellule individuellement.

Credit: Canva Images

Une formule comme =B2*$C$1 fonctionne parfaitement, mais elle ne dit rien à un lecteur sur ce que représentent réellement B2 ou C1 sans ouvrir la feuille pour vérifier. Une plage nommée résout ce problème en attachant une étiquette en langage courant à une cellule ou une plage, de sorte que la même formule puisse au lieu de cela se lire =Revenu*TauxDeTaxe.

En créer une prend quelques secondes. Sélectionnez la cellule ou la plage, cliquez dans la zone de nom juste à gauche de la barre de formule, tapez un nom sans espaces, et appuyez sur Entrée. Pour plus de contrôle, l'onglet Formules inclut une option Définir un nom avec un champ de description plus long. Elle offre également un choix entre deux portées : le niveau du classeur, où le nom fonctionne sur n'importe quelle feuille, ou le niveau de la feuille de calcul, où il ne s'applique qu'à cet onglet.

Une fois qu'une plage a un nom, ce nom peut être tapé directement dans n'importe quelle formule au lieu de la référence de cellule, et Excel le traite de la même manière. Cela importe le plus dans les classeurs avec de nombreuses formules référant aux mêmes quelques constantes, comme un taux de taxe, un taux de change ou un pourcentage de commission. Nommer cette cellule signifie que chaque formule qui y fait référence est lisible, et la mise à jour du taux à un endroit met à jour chaque formule qui en dépend.

Les plages nommées rendent également les formules plus faciles à auditer. Une formule entièrement construite à partir de références de cellules nécessite de cliquer à travers la feuille pour confirmer à quoi chacune d'elles correspond. Une formule construite à partir de plages nommées s'explique principalement d'elle-même.

Une précaution vaut la peine d'être gardée à l'esprit. Excel met généralement à jour automatiquement une plage nommée lorsque des lignes sont insérées à l'intérieur, mais une plage définie de manière inhabituelle peut parfois rester fixe au lieu de s'étendre. Vérifier qu'une plage nommée couvre toujours les bonnes cellules après une modification majeure prend un moment et évite qu'une formule fonctionne discrètement à partir d'un ensemble de données incomplet.

Credit: Wolf Art / Pexels

Faire défiler une longue feuille de calcul finit par pousser la ligne d'en-tête hors du haut de l'écran, laissant un mur de chiffres sans étiquettes. Les volets figés résolvent ce problème en verrouillant des lignes ou des colonnes spécifiques en place pour qu'elles restent visibles, peu importe jusqu'où le reste de la feuille défile.

La version la plus simple se trouve dans l'onglet Affichage. Figer la première ligne garde la toute première ligne de la feuille épinglée en place tandis que tout ce qui est en dessous défile librement. Figer la première colonne fait la même chose pour la colonne la plus à gauche, ce qui est utile sur une feuille où les étiquettes de lignes se trouvent dans la colonne A plutôt qu'en haut. Les deux options s'appliquent d'un simple clic et ne nécessitent aucune configuration.

Pour plus de contrôle, sélectionner une cellule spécifique avant d'ouvrir les volets figés verrouille tout ce qui se trouve au-dessus et à gauche de cette cellule. Tout ce qui se trouve en dessous et à droite reste libre de défiler. C'est l'option à utiliser lorsqu'une feuille a besoin à la fois d'une ligne d'en-tête figée et d'une colonne d'étiquettes figée en même temps. Un grand tableau de données en a souvent besoin, avec des étiquettes de lignes sur le côté gauche et des en-têtes de colonnes en haut.

Un volet figé apparaît sous la forme d'une fine ligne grise traversant ou descendant la feuille, marquant la frontière entre les sections verrouillées et défilables. Il peut être supprimé à tout moment via le même menu de l'onglet Affichage, affichant maintenant Dégeler les volets à la place.

Cette fonctionnalité est particulièrement importante pour les ensembles de données trop volumineux pour tenir sur un seul écran, ce qui décrit la plupart des feuilles de calcul de travail en dehors des calculs rapides. Sans elle, vérifier à quelle colonne correspond un numéro 300 lignes plus bas signifie revenir en haut, lire l'en-tête, puis redescendre. Avec elle, l'en-tête est toujours juste là. Google $GOOGL Sheets inclut la même fonctionnalité sous Affichage, puis Figer.

Credit: Canva Images

Sélectionner un bloc de données et appuyer sur Ctrl+T le convertit en ce qu'Excel appelle un tableau, un objet structuré qui se comporte différemment d'une plage simple de plusieurs manières utiles.

Le bénéfice le plus immédiat est le remplissage automatique des formules. Ajouter une formule dans une cellule d'une colonne de tableau l'applique à chaque ligne de cette colonne automatiquement, y compris les nouvelles lignes ajoutées plus tard en bas. Une plage simple nécessite de copier manuellement la formule chaque fois qu'une nouvelle ligne apparaît. Cette étape est facile à oublier, et c'est une source fréquente de rapports qui cessent discrètement de se mettre à jour correctement en cours de route.

Les tableaux introduisent également des références structurées, une manière d'écrire des formules en utilisant les noms de colonnes au lieu des adresses de cellules. Une formule écrite comme =SOMME(Tableau1[Ventes]) fait référence à toute la colonne Ventes d'un tableau nommé Tableau1. Elle continue de totaliser la plage correcte à mesure que des lignes sont ajoutées ou supprimées, sans jamais avoir besoin d'être réécrite. Cela se lit plus clairement qu'une formule comme =SOMME(B2:B847), et elle n'a jamais besoin d'être mise à jour à mesure que le tableau grandit.

Chaque tableau est doté par défaut de boutons de filtre intégrés à sa ligne d'en-tête, donc le tri et le filtrage sont disponibles immédiatement sans appliquer AutoFilter séparément. Les tableaux appliquent également une alternance de couleurs de fond automatiquement, alternant une couleur de fond claire ligne par ligne. Cela facilite le suivi d'une ligne à travers une large feuille sans règle ou plissement des yeux.

Nommer un tableau avec quelque chose de plus spécifique que le défaut d'Excel, fait via l'onglet Conception du tableau, facilite sa référence plus tard. Les formules, tableaux croisés dynamiques et graphiques peuvent tous pointer vers un tableau nommé et se mettre à jour automatiquement à mesure que le tableau grandit ou rétrécit. Un graphique construit à partir d'une plage de tableau s'étend pour inclure de nouveaux points de données ajoutés en bas. Il n'est pas nécessaire de modifier manuellement la plage source du graphique.

Credit: Marina Agrelo / Pexels

Additionner une colonne de nombres est simple avec SOMME. Additionner uniquement les nombres qui répondent à une condition spécifique, comme chaque vente réalisée dans une certaine région pendant un certain mois, nécessite un outil différent : SOMME.SI.ENS.

La fonction prend une plage de sommes, suivie de paires de critères : une plage à vérifier et une condition que cette plage doit remplir. Une formule comme =SOMME.SI.ENS(MontantVente, Région, "Ouest", Mois, "Juin") additionne chaque valeur de la colonne MontantVente où la ligne a "Ouest" dans Région et "Juin" dans Mois. Elle fait cela en une seule étape, sans filtrage manuel préalable requis. Des paires de critères supplémentaires peuvent être ajoutées pour affiner davantage la condition, et il n'y a pas de limite fixe au nombre qui peut être combiné dans une seule formule.

NB.SI.ENS fonctionne de la même manière mais compte les lignes correspondantes au lieu de sommer une valeur. Cela le rend utile pour répondre à une question telle que combien de commandes un client spécifique a passées dans une certaine plage de dates. MOYENNE.SI.ENS applique la même logique pour calculer une moyenne conditionnelle, comme la valeur moyenne des commandes pour une catégorie de produits spécifique au cours d'un trimestre spécifique.

Avant que ces fonctions n'existent sous leur forme actuelle, la solution de contournement consistait à filtrer les données pour les lignes correspondantes, puis à appliquer une simple SOMME ou COMPTE à ce qui restait. Le filtre devait ensuite être supprimé et le processus répété pour la condition suivante. Cette approche est lente, facile à mal interpréter, et ne laisse aucune trace de ce qui a été réellement calculé une fois le filtre supprimé.

SOMME.SI.ENS, NB.SI.ENS et MOYENNE.SI.ENS résolvent le même problème qu'une formule qui réside en permanence dans une cellule. Elle se recalcul automatiquement chaque fois que les données sous-jacentes changent, sans qu'il soit jamais nécessaire de réappliquer un filtre. Cela les rend bien adaptés à tout rapport qui est reconstruit régulièrement avec de nouvelles données, comme un résumé des ventes hebdomadaire ou un récapitulatif mensuel des dépenses par département.

Credit: Canva Images

Le collage standard Ctrl+V copie tout à propos d'une cellule — sa formule, son formatage et sa valeur en une seule fois. Le Collage spécial, ouvert avec Ctrl+Alt+V, décompose cette action unique en plusieurs actions plus spécifiques, chacune résolvant un problème différent.

Ne coller que les valeurs prend le résultat d'une formule et le place dans le nouvel emplacement sous forme de nombre simple, sans formule attachée. Cela est particulièrement important lorsqu'un rapport plein de formules doit être envoyé en dehors du classeur dans lequel il a été construit. Les formules qui font référence à d'autres feuilles ou classeurs peuvent échouer ou renvoyer des erreurs une fois retirées de leur contexte d'origine. Convertir un rapport terminé en valeurs avant de l'envoyer élimine totalement ce risque.

Coller uniquement les formats copie le style visuel d'une cellule, tel que sa couleur, ses bordures et son format de nombre, sans toucher la valeur ou la formule déjà présente dans la cellule de destination. C'est un moyen plus rapide d'appliquer un formatage cohérent à travers une feuille que de le reconstruire manuellement à chaque nouvel emplacement.

Transposer inverse l'orientation des données copiées, transformant une ligne en colonne ou une colonne en ligne. Cela résout un problème d'agencement courant : des données saisies en colonne qui doivent en fait s'étendre en ligne pour correspondre au reste d'un rapport, ou inversement. Reconstituer cet agencement en retapant chaque valeur prend beaucoup plus de temps que de copier la plage, d'ouvrir le Collage spécial et de cocher la case Transposer.

Le Collage spécial prend également en charge le collage avec une opération mathématique : ajouter, soustraire, multiplier ou diviser la valeur copiée par rapport à ce qui est déjà dans la cellule de destination. Cela convient à une tâche comme appliquer une augmentation de pourcentage unique sur toute une colonne de prix d'un coup. Copiez le pourcentage, sélectionnez la colonne des prix et choisissez multiplier, plutôt que d'écrire et de faire glisser une formule distincte pour chaque ligne.

Credit: Avinash Kumar / Unsplash

Taper directement "$1,200.00" dans une cellule semble correct à l'écran, mais cela transforme ce qui devrait être un nombre en texte. Cela casse toute formule qui essaie d'additionner, de faire une moyenne, ou de calculer autrement dessus. Les formats de nombre personnalisés résolvent cela en changeant comment une valeur s'affiche sans changer la valeur elle-même.

Le paramètre se trouve sous Format de cellule, ouvert avec Ctrl+1, dans l'onglet Nombre. Sélectionner Personnalisé ouvre un champ où un code de format contrôle exactement comment le nombre apparaît. Un code comme $#,##0.00 affiche un nombre simple comme une devise, avec un séparateur de milliers et deux décimales. La cellule contient toujours le nombre original en dessous, disponible pour toute formule à utiliser directement.

Les formats personnalisés peuvent ajouter une unité après un nombre sans changer ce qui est stocké, affichant "1,200 unités" tandis que la cellule contient toujours le nombre 1200 à des fins de calcul. Un format de pourcentage multiplie l'affichage par 100 et ajoute un signe de pourcentage, tandis que la valeur sous-jacente reste un décimal. Cela le garde compatible avec d'autres formules qui s'attendent à un pourcentage brut plutôt qu'à un nombre entier.

Les nombres négatifs peuvent être configurés pour s'afficher en rouge, entre parenthèses, ou avec un signe moins, selon la convention qu'un rapport doit suivre. Tout cela fonctionne avec le même code de format, sans qu'une règle de formatage conditionnel soit nécessaire. Les dates peuvent s'afficher dans des dizaines d'ordres et de styles différents, d'une date écrite complète à une date numérique courte. Rien de cela ne change la valeur de date réelle stockée dans la cellule.

La distinction entre formatage et saisie est la plus importante dans toute feuille qui sera plus tard additionnée, moyennée ou référencée par une autre formule. Une colonne de nombres tapés en tant que texte avec un symbole monétaire attaché ne s'additionnera pas correctement avec SOMME. La même colonne utilisant un format de nombre personnalisé le fera, puisque les valeurs sous-jacentes restent de vrais nombres tout au long.

Credit: Canva Images

Copier une formule vers le bas d'une colonne ou à travers une ligne est l'une des actions les plus courantes dans Excel. C'est aussi là qu'un type d'erreur spécifique apparaît le plus souvent. Par défaut, Excel traite chaque référence de cellule dans une formule comme relative, ce qui signifie qu'elle se déplace pour correspondre à la nouvelle position chaque fois que la formule est copiée ailleurs.

Ce comportement de décalage est généralement exactement ce qui est voulu. Une formule qui multiplie la quantité de chaque ligne par son propre prix doit se déplacer lorsqu'elle est copiée vers le bas d'une colonne, de sorte que chaque ligne calcule contre ses propres valeurs. Les ennuis commencent lorsqu'une formule doit référencer une cellule fixe, comme un taux d'imposition ou un taux de change unique, peu importe où la formule est copiée.

Un signe dollar verrouille une référence pour qu'elle cesse de se déplacer. Écrire $C$1 au lieu de C1 verrouille à la fois la colonne et la ligne, de sorte que copier cette formule n'importe où dans la feuille pointe toujours vers exactement la même cellule. Un seul signe dollar devant uniquement la lettre de colonne, comme $C1, verrouille uniquement la colonne tout en permettant à la ligne de se déplacer. Un signe dollar devant uniquement le numéro de ligne, comme C$1, fait l'inverse.

Appuyer sur F4 pendant qu'une référence de cellule est sélectionnée dans une formule parcourt ces quatre options dans l'ordre, sans avoir besoin de taper les signes dollar manuellement. C'est plus rapide que de modifier la référence à la main, surtout dans une formule avec plusieurs références qui ont chacune besoin d'une combinaison différente de verrouillage.

Un cas d'utilisation courant est une feuille de calcul calculant la commission sur des dizaines de lignes de ventes. La commission de chaque ligne dépend de son propre chiffre de vente mais du même taux de commission stocké dans une cellule. Verrouiller cette seule cellule de taux avec des signes dollar avant de copier la formule vers le bas garantit que chaque ligne référence le bon taux. Sans cela, la référence peut se déplacer vers une cellule vide quelques lignes plus bas et renvoyer une erreur ou un zéro.

Credit: Maria Stewart / Unsplash

Le petit carré en bas à droite d'une cellule sélectionnée, connu sous le nom de poignée de remplissage, va bien au-delà de la copie d'une seule valeur vers le bas d'une colonne. Le fait de le faire glisser reconnaît et continue automatiquement une gamme de motifs courants.

Taper une date dans une cellule et faire glisser la poignée de remplissage vers le bas continue la séquence par jour, sans formule nécessaire. Taper un nom de jour de la semaine fait de même pour les jours de la semaine, revenant au lundi après le dimanche. Un nom de mois continue de la même manière tout au long de l'année civile. Taper deux chiffres dans des cellules adjacentes, comme 10 et 20, puis sélectionner les deux avant de faire glisser, indique à Excel de continuer le motif au même intervalle. Il remplit 30, 40, 50, et ainsi de suite.

Un double-clic sur la poignée de remplissage au lieu de la faire glisser complète une formule ou un motif automatiquement, correspondant à la longueur d'une colonne adjacente. C'est plus rapide que de la faire glisser à la main sur une feuille avec des centaines de lignes, car Excel détecte où les données adjacentes se terminent et s'arrête là.

Au-delà des modèles intégrés, une liste personnalisée peut être créée pour toute séquence spécifique à un lieu de travail ou à un jeu de données. Les noms de départements, les emplacements de magasins et les catégories de produits qui ne suivent pas un ordre alphabétique ou numérique en sont des exemples courants. Cela se configure via Fichier, puis Options, puis Avancé, puis Modifier les listes personnalisées, où une séquence peut être saisie une fois et enregistrée. Après cela, taper le premier élément de la séquence et faire glisser la poignée de remplissage continue la liste dans le bon ordre à chaque fois. Cela fonctionne de la même manière qu'Excel gère déjà les jours de la semaine.

Cela transforme une tâche de saisie répétitive, entrant la même séquence fixe d'étiquettes sur des dizaines de lignes, en un seul glissement. C'est une petite fonctionnalité, mais sur une feuille reconstruite chaque semaine avec les mêmes catégories récurrentes, cela représente un gain de temps réel sur une année d'utilisation.

Credit: cottonbro studio / Pexels

Les données arrivent rarement sous la forme dont une feuille de calcul a réellement besoin. Une colonne peut contenir une adresse complète qui doit être divisée en rue, ville et code postal. Un rapport exporté d'un autre système peut combiner plusieurs champs en un seul qui doit être séparé avant que toute formule puisse l'utiliser correctement.

Texte en colonnes, trouvé dans l'onglet Données, traite la version plus simple de ce problème. Sélectionner une colonne et choisir Texte en colonnes ouvre un assistant qui divise son contenu en plusieurs colonnes en fonction d'un délimiteur. Ce délimiteur peut être une virgule, un espace ou un caractère spécifique que les données utilisent pour séparer les champs. Cela fonctionne bien comme une solution ponctuelle : exécutez-le une fois, et la colonne est immédiatement divisée. La division ne se répète pas automatiquement si de nouvelles données sont collées dans la même colonne plus tard.

Power Query, accessible via la section Obtenir et Transformer de l'onglet Données, résout un éventail plus large de problèmes et se souvient comment il l'a fait. Au lieu d'appliquer une action unique, Power Query enregistre chaque étape de nettoyage — division de colonnes, suppression de lignes vides, fusion de tableaux de différentes sources, renommage des en-têtes — comme une séquence enregistrée. Cette séquence peut être réexécutée d'un clic chaque fois que les données sources sont mises à jour. Cela convient bien mieux à un rapport qui est régulièrement reconstruit avec des données fraîches provenant de la même source qu'une division ponctuelle.

Power Query peut également combiner des données de plusieurs feuilles, classeurs ou même fichiers externes en une seule table propre, ce que Texte en colonnes ne peut pas faire du tout. Un processus de rapport mensuel qui extrait des données de plusieurs feuilles de calcul régionales et qui doit les combiner en une table cohérente est un candidat naturel pour Power Query. Les étapes de combinaison n'ont besoin d'être construites qu'une seule fois, puis simplement actualisées chaque mois.

Les deux outils résolvent différentes échelles du même problème. Texte en colonnes gère une division rapide et unique, tandis que Power Query gère tout ce qui doit se produire de la même manière encore et encore.

Credit: Richard Heinen / Unsplash

Un rapport avec des détails mensuels sur 12 colonnes est utile pour quelqu'un vérifiant un mois spécifique, mais accablant pour quelqu'un qui ne veut voir que le total annuel. Groupement et Plan résout cela en réduisant les lignes ou colonnes de détails à un niveau de synthèse, sans supprimer aucune donnée sous-jacente.

Sélectionner un ensemble de lignes ou de colonnes et choisir Groupement, trouvé dans l'onglet Données, ajoute un petit crochet dans la marge. Un signe moins dans ce crochet réduit la plage sélectionnée à une seule ligne lorsqu'on clique, et un signe plus la déploie à nouveau. Plusieurs niveaux de groupement peuvent être imbriqués. Une feuille organisée par année, trimestre et mois peut se réduire aux totaux annuels en un clic, puis se développer un niveau à la fois selon les besoins.

Cela diffère de simplement masquer des lignes ou des colonnes, ce qui ne laisse aucune indication visible que quelque chose est masqué et peut confondre quiconque ouvre la feuille plus tard. Une section groupée et réduite montre une barre de plan claire avec un signe plus, signalant qu'il y a plus de détails qui peuvent être déployés à la demande.

Le raccourci clavier Maj+Alt+Flèche droite groupe une plage sélectionnée sans ouvrir l'onglet Données, et Maj+Alt+Flèche gauche la dégroupe. Excel peut également appliquer le groupement automatiquement via l'option Plan automatique. Il détecte les lignes de sous-total existantes dans une feuille et groupe les détails sous chacune. Cela est utile sur un rapport qui a déjà des sous-totaux intégrés mais qui n'a jamais été réduit pour une visualisation plus facile.

Un rapport financier mensuel est un cas d'utilisation courant : 12 mois de détails sur les colonnes, se réduisant à une seule colonne trimestrielle ou annuelle pour une vue synthétique. Le détail mensuel complet reste disponible en dessous pour toute personne qui doit vérifier une période spécifique. Personne n'a à choisir entre une feuille détaillée et une feuille claire, puisque Groupement et Plan permet à la même feuille de servir les deux.

Credit: Ron Lach / Pexels

Rechercher et Remplacer, ouvert avec Ctrl+H, gère une recherche simple pour un texte exact. Les jokers l'étendent pour correspondre à des motifs au lieu de texte exact, ce qui résout un éventail plus large de tâches de nettoyage en un seul passage.

Un astérisque remplace n'importe quel nombre de caractères, y compris aucun. Chercher "Facture*" trouve chaque cellule qui commence par le mot Facture, quel que soit ce qui suit, que ce soit un chiffre, une date, ou rien d'autre. Cela est utile pour trouver et mettre à jour chaque entrée qui suit un modèle de nommage sans avoir besoin de connaître chaque valeur individuelle à l'avance.

Un point d'interrogation remplace exactement un caractère, ce qui est plus précis qu'un astérisque lorsque la longueur du modèle est importante. Chercher "200?" trouve des entrées de quatre caractères commençant par 200, comme 2001 à 2009, sans correspondre à un nombre plus long comme 20015, ce qu'un astérisque ferait.

Les deux jokers fonctionnent par défaut dans la boîte de recherche Rechercher et Remplacer, sans paramètre spécial requis au préalable. Cela diffère de l'utilisation des jokers dans une formule, comme dans une condition COUNTIF ou SUMIF, où ils fonctionnent également mais uniquement dans des fonctions conçues spécifiquement pour les accepter.

Un cas d'utilisation courant est le nettoyage de l'étiquetage incohérent sur une grande feuille, tel que remplacer chaque entrée commençant par "Dépt-" par un préfixe différent. Un autre est de trouver chaque cellule qui contient un mot spécifique quelque part au milieu d'une chaîne plus longue variant de ligne en ligne. Combiner des jokers avec Remplacer Tout applique la correction sur une feuille entière ou un classeur d'un seul coup, plutôt que de modifier chaque cellule correspondante individuellement.

Il vaut la peine d'exécuter une recherche d'abord, sans rien remplacer, pour examiner combien de cellules correspondent au modèle avant de s'engager dans un remplacement en masse. Cela prend quelques secondes et empêche un modèle de joker trop large de modifier des cellules qui n'étaient jamais censées être incluses.

Credit: Canva Images

Un graphique complet prend de l'espace réel sur une feuille, ce qui devient un problème sur un rapport avec des dizaines de lignes, chacune ayant besoin de sa propre tendance montrée à côté. Les mini-graphiques résolvent ce problème en compressant un graphique pour tenir dans une seule cellule.

Trouvés dans l'onglet Insertion, les mini-graphiques se déclinent en trois types. Un mini-graphique en ligne trace une tendance simple sur une ligne de données, utile pour afficher 12 mois de ventes d'un seul produit sans un graphique séparé pour chacun. Un mini-graphique en colonne montre les mêmes données sous forme d'un petit ensemble de barres verticales au lieu d'une ligne connectée. Cela est utile lorsque les valeurs individuelles importent plus que la forme globale de la tendance. Un mini-graphique de type gain-perte réduit encore plus les données, montrant uniquement si chaque période était positive ou négative. Cela convient à quelque chose comme une vérification mensuelle de profit et perte sur plusieurs lignes de produits à la fois.

La mise en place signifie sélectionner la cellule où le graphique doit apparaître, choisir le type de sparkline depuis l'onglet Insertion, puis sélectionner la plage de données qu'il doit résumer. Le graphique se trouve alors dans cette seule cellule, assez petit pour tenir à côté d'une ligne d'autres données dans un tableau. Il se redimensionne automatiquement si la hauteur de la ligne ou la largeur de la colonne change plus tard.

Le principal avantage par rapport à un graphique complet est la densité. Un tableau récapitulatif avec 50 lignes, chacune montrant la tendance des ventes mensuelles d'un produit sous forme de graphique dans la dernière colonne, tient entièrement sur un écran. Construire 50 graphiques de grande taille séparés pour montrer la même information serait peu pratique et prendrait beaucoup plus de place que les données sous-jacentes elles-mêmes.

Les graphiques ne sont pas destinés à remplacer un graphique complet lorsque des valeurs précises importent, car ils n'ont ni étiquettes d'axe ni quadrillage par défaut. Ils sont conçus pour un autre objectif : montrer la forme d'une tendance en un coup d'œil, sur de nombreuses lignes à la fois, sans exiger beaucoup d'attention de la part du lecteur.

Credit: Tanja Tepavac / Unsplash

Une liste constituée au fil des mois, ou rassemblée à partir de plusieurs sources, se retrouve souvent avec la même entrée enregistrée plusieurs fois. Cela peut être un enregistrement client répété, un numéro de commande en double, ou la même adresse e-mail entrée deux fois sous des noms légèrement différents. Trouver ceux-ci en faisant défiler la liste manuellement est lent et peu fiable au-delà de quelques dizaines de lignes.

L'outil Supprimer les doublons d'Excel, trouvé dans l'onglet Données, gère cela en un seul passage. Sélectionner la plage de données et choisir Supprimer les doublons ouvre une boîte de dialogue listant chaque colonne de la sélection. Les cases à cocher contrôlent celles qui sont vérifiées pour décider si deux lignes comptent comme des doublons. Vérifier uniquement la colonne d'adresse e-mail, par exemple, considère deux lignes comme des doublons si elles partagent une adresse e-mail, même si le nom attaché à chacune est orthographié différemment.

Une fois confirmé, Excel supprime chaque ligne en double qu'il trouve en fonction des colonnes sélectionnées et indique exactement combien ont été supprimées et combien de valeurs uniques restent. Ce rapport mérite d'être lu avant de continuer. Un nombre étonnamment élevé de doublons supprimés peut indiquer un problème plus profond avec la façon dont les données ont été collectées, plutôt qu'une simple erreur d'entrée ponctuelle.

La suppression des doublons est une action destructive : les lignes supprimées sont effacées de la feuille, pas seulement masquées. Il n'y a aucun moyen intégré de revoir quelles lignes spécifiques ont été supprimées après coup. Travailler sur une copie des données vaut les quelques secondes supplémentaires que cela prend. Il en va de même pour s'assurer que Ctrl+Z est disponible pour annuler l'action si le résultat semble incorrect.

Parfois, une suppression de doublons doit être plus sélective, comme garder le plus récent de plusieurs doublons plutôt qu'un arbitraire. Trier les données par date avant d'exécuter Supprimer les doublons garantit que la ligne qui survit est celle souhaitée. Excel conserve la première occurrence qu'il rencontre et supprime celles qui suivent.

Credit: Kindel Media / Pexels

La plupart des formules de feuille de calcul fonctionnent dans un sens : entrez les données et la formule calcule un résultat. Valeur cible fonctionne dans le sens opposé, partant d'un résultat souhaité et calculant quelle entrée le produirait.

Trouvé sous l'onglet Données dans Analyse de scénarios, Valeur cible demande trois informations. Il s'agit de savoir quelle cellule contient la formule à résoudre, quelle valeur cette formule doit atteindre, et quelle cellule d'entrée unique doit être modifiée pour y parvenir. Excel teste ensuite différentes valeurs dans cette cellule d'entrée, ajustant automatiquement jusqu'à ce que le résultat de la formule corresponde le plus possible à l'objectif, et rapporte la valeur d'entrée sur laquelle il s'est arrêté.

Un cas d'utilisation courant est de déterminer combien d'unités doivent être vendues pour atteindre le seuil de rentabilité. L'ancienne méthode consiste à deviner un chiffre de vente, le saisir dans une formule de profit et ajuster manuellement si le résultat est incorrect. Valeur cible évite ces conjectures. Étant donné les coûts fixes et le prix par unité intégrés dans la feuille, il prend un profit cible de zéro et calcule le nombre exact d'unités nécessaires pour l'atteindre.

Une autre utilisation courante est de travailler à rebours à partir d'un paiement de prêt. Étant donné un montant de prêt et une durée, Valeur cible peut calculer le taux d'intérêt qui produirait un paiement mensuel spécifique. Cela évite de tester manuellement des taux différents un par un dans une formule de paiement jusqu'à ce que les chiffres correspondent.

Valeur cible ajuste seulement une cellule d'entrée à la fois, ce qui convient à une question simple à une variable mais pas à un scénario où plusieurs entrées pourraient devoir changer ensemble. Pour ce type de problème à plusieurs variables, le module complémentaire Solver d'Excel offre plus de flexibilité. Contrairement à Valeur cible, qui est disponible par défaut dans chaque installation standard, Solver doit d'abord être activé via le menu Modules complémentaires.

Valeur cible fonctionne en testant des valeurs jusqu'à ce qu'elle converge vers une réponse, plutôt que de résoudre la formule algébriquement. Pour cette raison, elle nécessite occasionnellement un moment sur une feuille très grande ou complexe. Pour la plupart des questions à formule unique, le résultat apparaît presque immédiatement.