5 astuces Microsoft Excel sans lesquelles je ne pourrais pas vivre

Avec n'importe quel logiciel, plus vous l'utilisez, plus vous découvrez de hacks et de raccourcis, et jamais un mot plus vrai n'a été dit en ce qui concerne Microsoft Excel. De plus, au fur et à mesure que le programme évolue, vous ajoutez de nouvelles astuces à votre boîte à outils. Voici quelques-uns de mes favoris, anciens et nouveaux, sans lesquels je ne pourrais pas vivre.

Coller un tableau dynamique en tant que valeurs statiques

Certaines fonctions, comme FILTER, SEQUENCE et RANDARRAY, produisent des tableaux dynamiques, dans lesquels le résultat s'étend à partir de l'endroit où vous avez tapé la formule dans les cellules adjacentes (comportement appelé débordement). Les tableaux dynamiques remplacent les formules matricielles Ctrl+Shift+Enter (CSE) moins flexibles, et vous savez qu'un tableau est dynamique lorsqu'une ligne bleue entoure les cellules contenant le résultat renversé.

Vous devez utiliser Excel pour le Web ou l'application de bureau Excel Microsoft 365 pour bénéficier des dernières fonctionnalités d'Excel, y compris les dernières fonctions de tableau dynamique.

Cependant, vous souhaiterez parfois corriger les valeurs affichées dans le tableau dynamique, plutôt que de les recalculer et de les mettre à jour continuellement. Par exemple, disons que vous avez utilisé les fonctions DATE et SEQUENCE pour créer un tableau dynamique de dates, en fonction des paramètres des cellules C9, C10 et C11.

Maintenant, cependant, vous ne souhaitez plus apporter de modifications aux dates, vous souhaitez donc les corriger telles quelles. Pour ce faire, sélectionnez les cellules et appuyez sur Ctrl+C pour les copier.

Ensuite, avec les mêmes cellules toujours sélectionnées, appuyez sur Ctrl+Maj+V pour coller uniquement les valeurs. Vous savez que cela a fonctionné correctement car, même si les dates apparaissent toujours comme avant, lorsque vous sélectionnez l'une des cellules, vous ne voyez pas de ligne bleue et la barre de formule affiche une valeur unique plutôt qu'une formule.

Enfin, vous pouvez supprimer les cellules contenant les paramètres, car les dates ne les utilisent plus.

Sélectionnez les références de cellules à l'aide de la souris

Je suis toujours à la recherche de moyens d'accélérer mon flux de travail Excel, et même s'il s'agit d'une astuce de niveau débutant, c'est une astuce que j'utilise quotidiennement.

Imaginons que vous travaillez sur cette feuille de calcul et que vous souhaitiez remplir la colonne des totaux en additionnant les scores de chaque équipe au cours des six semaines.

À partir de la cellule E2, la formule que vous recherchez est :

=SUM(B2:G2)

Cependant, la saisie manuelle des références de cellule peut prendre du temps et entraîner des erreurs. Une méthode plus rapide et plus fiable consiste à utiliser la souris.

Après avoir tapé :

=SUM(

dans la cellule H2, sélectionnez la cellule B2, maintenez votre souris enfoncée et faites glisser le curseur vers la cellule G2.

Enfin, tapez une parenthèse fermante, appuyez sur Ctrl+Entrée et double-cliquez sur la poignée de remplissage dans le coin inférieur droit pour appliquer la formule aux cellules restantes de la colonne H.

Vous pouvez également utiliser les touches fléchées pour ajouter des références de cellule à votre formule. Après avoir tapé le signe égal, le nom de la fonction et une parenthèse ouvrante, appuyez sur la touche fléchée pour activer le mode Point, qui vous permet de sélectionner des cellules pendant que vous construisez votre formule.

Cette astuce devient particulièrement utile lorsque vous faites référence aux en-têtes de colonnes dans des tableaux Excel formatés. En effet, ils nécessitent une syntaxe très spécifique qui prendrait trop de temps à saisir manuellement.

Dupliquer les règles de validation des données en un instant

Dans cette feuille de calcul Excel, supposons que vous ayez créé une règle de validation de données personnalisée à l'aide d'une formule complexe pour garantir que les sociétés saisies dans la colonne C contiennent une lettre suivie de quatre chiffres.

Supposons maintenant que vous souhaitiez utiliser la même règle de validation des données dans une autre feuille de calcul.

Cependant, retaper la règle prendra trop de temps et vous ne souhaitez pas copier et coller les cellules car elles sont formatées différemment.

Heureusement, il existe un moyen de copier les règles de validation des données d'une cellule ou d'une plage à une autre sans remplacer les formules existantes, modifier les données ou changer le formatage. Tout d’abord, sélectionnez et copiez une cellule ou toutes les cellules contenant déjà la règle de validation des données.

Ensuite, sélectionnez la ou les cellules dans lesquelles vous souhaitez que la règle soit dupliquée et appuyez sur Ctrl+Alt+V pour lancer la boîte de dialogue Collage spécial. Vous pouvez également cliquer sur la flèche vers le bas « Coller » dans l'onglet Accueil du ruban et sélectionner « Collage spécial ».

Ensuite, cochez le bouton radio « Validation » et cliquez sur « OK ».

Et c'est tout ! Les règles de validation des données sont dupliquées, laissant tout le reste intact. Vous pouvez tester que cela a fonctionné en saisissant des entrées valides et invalides.

Supprimer les espaces de début ou de fin d'une plage

Supprimer les espaces de début ou de fin dans les formules Excel est un excellent moyen de réduire et d'agrandir automatiquement les données, ce qui rend les formules plus dynamiques et plus propres. Cette astuce devient particulièrement utile pour les formules matricielles dynamiques, car elles sont incompatibles avec les tableaux Excel.

Dans cette feuille de calcul Excel, supposons que vous ayez utilisé la fonction FILTRE pour extraire tous les joueurs ayant plus de 50 apparitions, ainsi que l'équipe dans laquelle ils appartiennent.

Lorsqu'un joueur dépasse les 50 apparitions, il reçoit un maillot aux couleurs de son équipe. Votre objectif est de savoir quel joueur possède quel maillot. Pour ce faire, dans la cellule H2, vous utilisez une formule XLOOKUP dynamique :

=XLOOKUP(G2:G100,$J$2:$J$7,$K$2:$K$7)

où G2:G100 sont les cellules contenant l'équipe du joueur, avec des cellules supplémentaires incluses en bas pour permettre la croissance, $K$2:$K$7 est le tableau de recherche et $L$2:$L$7 est le tableau de retour.

Cependant, lorsque vous appuyez sur Entrée, l'erreur #N/A apparaît dans les cellules H12 vers le bas car il n'y a pas d'équipe dans la cellule correspondante de la colonne G.

Une façon de résoudre ce problème consiste à saisir deux guillemets doubles dans le si_not_found Argument XLOOKUP, qui remplace les erreurs par une cellule vide. Cependant, la formule XLOOKUP renvoie toujours un tableau dynamique qui s'étend au-delà de la portée actuelle des données, ce qui non seulement semble désordonné mais gaspille également de la mémoire.

Au lieu de cela, la voie à suivre est d’ajouter un point soigneusement placé à la formule. Dans ce contexte, la période est connue sous le nom d'opérateur de référence de trim. En ajoutant cela avant que les deux points coupent les blancs de début, en l'ajoutant après que les deux points coupent les blancs de fin, vous pouvez faire les deux pour couper les blancs de début et de fin en même temps.

Les opérateurs Trim ref ne sont disponibles que pour ceux qui utilisent Excel pour Microsoft 365, Excel pour le web et les applications mobiles et tablettes Excel.

Dans ce cas, l'opérateur trim ref est inséré après les deux points dans la formule, ce qui signifie que le tableau dynamique s'arrête à la cellule H11 :

=XLOOKUP(G2:.G100,$J$2:$J$7,$K$2:$K$7)

Cependant, si un autre joueur dépasse 50 apparitions, le tableau dynamique dans les colonnes F et G produit par la fonction FILTER s'étend sur une ligne, et le tableau dynamique dans la colonne H produit par la fonction XLOOKUP découpée fait de même.

Répéter les en-têtes de colonnes lors de l'impression

Imaginez que vous avez créé cette feuille de calcul Excel, qui contient un tableau avec l'ID, le sexe, le pays et le service de 130 personnes.

Vous souhaitez maintenant imprimer le tableau pour faciliter la consultation, mais lorsque vous le faites, vous remarquez que les en-têtes de colonnes ne sont imprimés que sur la première page, pas sur les suivantes. Par conséquent, une fois que vous avez dépassé la première page, l'impression peut prêter à confusion, en particulier pour les personnes qui ne savent pas ce que représente chaque colonne. Pour résoudre ce problème, vous pouvez répéter l'en-tête de colonne dans l'impression.

Accédez à l'onglet « Mise en page » du ruban et cliquez sur « Imprimer les titres ».

Le bouton Imprimer les titres est grisé si vous êtes en mode Édition, si un graphique est sélectionné ou si une imprimante n'est pas installée sur l'appareil.

Ensuite, ouvrez l'onglet « Feuille » et activez le curseur dans le champ intitulé « Lignes à répéter en haut ».

Ensuite, cliquez sur l'un des en-têtes de colonne de votre tableau et Excel saisit automatiquement une référence à la ligne entière, ce qui signifie que tout ce qui se trouve sur la ligne sélectionnée est considéré comme l'en-tête à répéter sur chaque page imprimée.

Les champs Lignes à répéter en haut et Colonnes à répéter à gauche sont grisés si plusieurs feuilles de calcul sont sélectionnées.

Après avoir cliqué sur « OK » pour fermer la boîte de dialogue, appuyez sur Ctrl+P (ou cliquez sur Fichier > Imprimer) pour ouvrir l'aperçu avant impression. Désormais, lorsque vous faites défiler l'aperçu, vous verrez les en-têtes de colonnes répétés sur chaque page.

---

Une façon d'améliorer votre piquant et d'augmenter votre efficacité dans Microsoft Excel consiste à utiliser des raccourcis clavier autant que possible, car cela signifie que vous n'avez pas besoin de basculer aussi souvent entre votre clavier et votre souris. Cependant, comme Excel en a tellement, vous pouvez imprimer une aide-mémoire sur les raccourcis clavier et l'épingler sur votre tableau d'affichage pour un accès facile.