5 astuces Excel pratiques pour booster votre productivité

by Elke De Wit
5 astuces Excel pratiques pour booster votre productivité

Excel est l’un des logiciels les plus populaires sur le marché, mais de nombreux professionnels se « débattent » encore et toujours avec le large éventail de possibilités et de fonctionnalités qu’offre cet outil. Bien que le programme semble à première vue simple et intuitif, il cache de nombreuses fonctions et fonctionnalités complexes. Il suffit de savoir où les trouver. Voici quelques astuces Excel pratiques :

1. Définir vos propres critères de tri dans Excel

Quoi ?

Trier des données est l’une des fonctions les plus utilisées dans Excel. Les exemples les plus courants sont le tri alphabétique, le tri par valeur (de la plus élevée à la plus basse ou de la plus basse à la plus élevée), le tri par jour de la semaine ou par mois.

Ces critères de tri sont prédéfinis, mais vous pouvez créer de nouveaux critères sur la base de vos propres données. Dans l’exemple ci-dessous, nous souhaitons trier les données de Small à Xlarge. Voici comment créer facilement des critères de tri.

Comment ?

Étape par étape

  • Dans le ruban, allez dans l’onglet « File » puis « Options ».
  • Dans la boîte de dialogue « Excel Options », sélectionnez « Advanced » dans la barre de gauche. Faites défiler vers le bas jusqu’à l’onglet « General », et choisissez « Edit Custom Lists ».
  • Une nouvelle boîte de dialogue permettant d’ajouter des critères s’ouvre. Ajoutez les critères dans la fenêtre « List Entries », et séparez-les par une virgule. Si votre liste de critères est longue, vous pouvez choisir de l’importer à partir d’une liste séparée provenant de votre tableur.
  • Cliquez sur « Add » et enfin sur « OK », puis, dans la boîte de dialogue « Options », cliquez à nouveau sur « OK ».
  • Retournez à votre liste ou sélectionnez-la.
  • Dans le ruban, allez dans l’onglet « Data », puis « Sort » (ou via « Home » puis « Sort & Filter », et choisissez « Custom Sort » dans le menu déroulant).
  • La boîte de dialogue suivante s’ouvre.
    – Indiquez dans « Sort by » la colonne que vous souhaitez trier. Dans notre exemple, il s’agit de « Taille »
    – Dans « Sort On », laissez « Cell Values ».
    – Dans « Order », choisissez « Custom List ».
  • Une nouvelle fenêtre s’ouvre avec les listes personnalisées. Choisissez la liste « Small, Medium, Large, Xlarge » que vous avez créée, et cliquez sur « OK », puis cliquez à nouveau sur « OK » dans la fenêtre suivante.
  • La liste est désormais triée selon la taille, de la plus petite à la plus grande.

Résultat

2. Créer une liste de choix dépendante (menu déroulant) dans Excel

Quoi ?

Vous savez déjà que vous pouvez facilement créer une liste de choix dans Excel. Mais il peut arriver que vous deviez utiliser plusieurs listes, de sorte que les éléments disponibles dans la deuxième liste (menu déroulant) dépendent de la sélection de la première liste (menu déroulant).

C’est ce que l’on appelle des listes de choix dépendantes.

Vous remarquez que les choix du menu déroulant numéro 2 dépendent de la sélection dans le menu déroulant numéro 1. Si je sélectionne « Fruit » dans le premier menu déroulant, ce sont les types de fruits qui apparaissent, mais si je choisis « Légume » dans le premier menu déroulant, ce sont les types de légumes qui apparaissent dans le menu déroulant numéro 2.

Comment ?

Étape par étape

  • Sélectionnez la cellule où vous désirez mettre en place votre premier (principal) menu déroulant.
  • Dans le ruban, allez dans l’onglet « Data » puis « Data Validation ».
  • Une boîte de dialogue « Data Validation » s’ouvre. Dans l’onglet « Settings », dans « Allow », choisissez « List » dans la liste. Spécifiez dans « Source » la plage où se trouvent les éléments du premier menu déroulant. Cliquez enfin sur « OK ».
  • Vous avez créé votre première liste de choix.
  • Sélectionnez ensuite toute la liste (dans notre exemple, les cellules de A1 à B6).
  • Dans le ruban, allez dans l’onglet « Formulas » et choisissez « Create from Selection » dans « Defined Names ».
  • Dans la boîte de dialogue suivante, cochez la case « Top row ». Toutes les autres options doivent être décochées. Vous créez ainsi deux listes de choix (« Fruit » et « Légume »).
  • Cliquez sur « OK ».
  • Sélectionnez la cellule où vous désirez mettre en place votre liste de choix dépendante (dans notre cas, il s’agit de la cellule E4, sous le titre « Drop Down 2 »).
  • Dans le ruban, allez dans l’onglet « Data » puis « Data Validation ».
  • Une boîte de dialogue « Data Validation » s’ouvre. Dans l’onglet « Settings », dans « Allow », choisissez « List » dans la liste. Dans « Source », indiquez une formule qui renvoie au menu déroulant (principal) dépendant. Dans notre exemple, il s’agit de la formule « =INDIRECT(D4) », car la cellule D4 contient la liste de choix principale.
  • Cliquez sur « OK ».
  • Si je sélectionne un élément dans la liste de choix Drop Down 1, les options de la liste de choix Drop Down 2 s’adaptent automatiquement.

Résultat

3. Désactiver les formules GETPIVOTDATA pour les tableaux croisés dynamiques

Quoi ?

Cela vous a probablement déjà ennuyé. Ce « GETPIVOTDATA » qui apparaît lorsque vous utilisez une formule dans une feuille Excel, qui fait référence à une cellule d’un tableau croisé dynamique. Au lieu d’un renvoi direct vers la cellule, par exemple « =B4 », une longue et horrible fonction « GETPIVOTDATA » est ajoutée à votre formule.

Saviez-vous que vous pouviez facilement la désactiver via les « Paramètres » ?

Comment ?

Étape par étape

  • Imaginons que vous ayez un tableau croisé dynamique comme dans l’exemple ci-dessous et que vous désiriez, dans la cellule C2, effectuer un calcul à partir du contenu du tableau croisé dynamique.
  • Placez-vous dans n’importe quelle cellule du tableau croisé dynamique
  • Dans le ruban, allez dans l’onglet « Analyze » et cliquez sur le menu déroulant « Options ». Vous verrez que la case à côté « Generate GetPivotData » est cochée. Cela signifie que l’option est activée. Si vous cliquez dessus, l’option sera désactivée.
  • Si vous retournez dans la cellule C2 et que vous utilisez une formule avec un renvoi vers une des cellules du tableau croisé dynamique, la formule utilise désormais une référence relative. Cela signifie que vous pouvez sans problème copier/coller votre formule dans plusieurs cellules.

Résultat

REMARQUE IMPORTANTE : L’option « Generate GetPivotData » est une option gérée au niveau de l’application. Cela signifie que l’option est identique pour tous les fichiers sur votre ordinateur. Celle-ci ne doit être configurée qu’une seule fois. Le réglage sera conservé même si vous fermez et rouvrez Excel.

Étant donné que cette option est gérée au niveau de l’application, elle n’est PAS transmise avec le classeur. Si vous envoyez votre fichier à des collègues, le réglage sera DIFFÉRENT. Ceux-ci doivent eux-mêmes modifier l’option « GetPivotData » sur leur ordinateur. Toutefois, les formules existantes ne sont PAS modifiées dans le classeur lorsque vous ou quelqu’un d’autre modifiez l’option. L’option « Generate GetPivotData » n’est qu’une aide à la création de NOUVELLES formules. Elle n’a aucune influence sur les formules existantes.

4. VLOOKUP avec plusieurs critères

Quoi ?

L’un des nombreux points faibles de la formule VLOOKUP est que l’on ne peut utiliser qu’un seul critère pour rechercher des données dans un autre tableau. Mais saviez-vous qu’en associant plusieurs chaînes de caractères, vous pouviez créer un index unique (grâce à la formule CONCATENATE), et appliquer des VLOOKUPS en utilisant plusieurs critères ?

Comment ?

Étape par étape

  • Imaginons que vous ayez une liste comme dans l’exemple ci-dessous, et que vous souhaitiez rechercher le prix dans le premier tableau pour l’afficher dans le second. Vous remarquez que le prix dépend à la fois du type et de la couleur, soit 2 critères.
  • Nous commençons par créer à côté de la source, plus précisément dans le premier tableau (colonne B), une chaîne concaténée (=C3&B3) à partir du type et de la couleur, afin que cette chaîne devienne notre critère de recherche unique.
  • Nous copions également cette formule à côté du deuxième tableau (inventaire) dans la colonne H.
  • Nous pouvons désormais sans problème rechercher le prix dans la colonne K en utilisant la formule VLOOKUP avec la chaîne concaténée.
  • Si nous copions les cellules dans la colonne K, le prix s’affiche partout.

Résultat

5. Transformer des nombres positifs en nombres négatifs avec le collage spécial

Quoi ?

Vous êtes-vous déjà demandé comment transformer facilement des nombres positifs en nombres négatifs ? Cela peut en effet être pratique dans une liste avec des remboursements ou des notes de crédit. Pour ce faire, utilisez le collage spécial.

Comment ?

Étape par étape

  • Dans notre exemple, nous aimerions que les montants des notes de crédit soient transformés en nombres négatifs.
  • Dans une cellule voisine (par exemple E1), tapez « -1 ».
  • Copiez la cellule (Ctrl + C)
  • Sélectionnez les montants des notes de crédit. Dans notre exemple, il s’agit des cellules C10 à C14 incluse.
  • Choisissez « Paste Special » dans le menu contextuel (clic droit).
  • Dans la boîte de dialogue, choisissez « Values » et « Multiply » et cliquez sur « OK ».
  • Vous remarquez que les montants des notes de crédit sont désormais négatifs.

Résultat

CONSEIL : La fonction « Format Cells » vous permet en outre d’afficher les nombres négatifs en rouge. Allez dans « Format Cells > Numbers > Negative numbers » et choisissez la dernière option, en rouge.

Sources :
https://trumpexcel.com/
http://www.iwillteachyouexcel.com/
https://www.excelcampus.com/

Envie d’en savoir plus

Envie de donner un boost à vos aptitudes en Excel en 1 seule journée ? Venez alors sûrement au Excel Expérience Day.

Également intéressant pour vous