Parfois, Excel peut paraître trop simple pour être vrai. Il suffit de saisir une formule et tout ce que vous faisiez manuellement devient automatique. Que vous ayez besoin de fusionner deux feuilles de calcul contenant des données similaires, de réaliser des calculs simples ou de combiner les informations de plusieurs cellules, Excel vous permet de le faire très facilement.

De même, si vous devez effectuer une mise à jour de vos données, il existe sans doute une formule qui vous évitera de faire ce travail manuellement. Quelle que soit la tâche qui vous attend, Excel compte de nombreuses astuces pour vous aider à travailler plus efficacement.

Comment utiliser Excel ?

Si vous faites vos premiers pas avec Excel, il peut être utile de connaître quelques commandes de base, notamment comment :

  • Créer une nouvelle feuille de calcul
  • Exécuter des opérations simples dans une feuille de calcul (addition, soustraction, multiplication, division)
  • Rédiger et mettre en forme le texte et les titres des colonnes
  • Utiliser les fonctions de remplissage automatique
  • Ajouter ou supprimer une colonne, une ligne ou une feuille de calcul. Vous apprendrez ci-dessous comment insérer plusieurs colonnes ou lignes
  • Figer des colonnes et des lignes, afin que vous sachiez quelles données vous remplissez lorsque vous faites défiler la feuille de calcul

Astuces simples pour ses fichiers Excel

Découvrez dans cet article quelques astuces sur Excel à connaître absolument pour augmenter sa productivité. 

1 - Tableaux croisés dynamiques

Les tableaux croisés dynamiques permettent de réorganiser les données d'une feuille de calcul. Ils ne les modifient pas, mais peuvent les synthétiser et les comparer pour répondre à certaines questions.

Pour savoir, par exemple, dans quel secteur d'activité travaillent vos prospects, cette fonctionnalité se révélera très pratique si vous disposez d'un vaste ensemble de données.

Pour créer un tableau croisé dynamique, sélectionnez Insérer > Tableau croisé dynamique. Excel remplit automatiquement le tableau mais vous pouvez toujours modifier l'ordre de vos données. Quatre options sont ensuite disponibles :

  1. Filtre du rapport : ce filtre vous permet d'afficher uniquement certaines lignes de votre ensemble de données. Par exemple, si vous souhaitez créer un filtre par secteur d'activité, vous pouvez choisir d'inclure uniquement les prospects exerçant dans le secteur du tourisme.
  2. Étiquettes de colonnes : elles peuvent servir d'en-têtes de colonnes dans votre ensemble de données.
  3. Étiquettes de lignes : elles peuvent servir d'en-têtes de lignes dans votre ensemble de données. Les étiquettes de lignes et de colonnes peuvent contenir les données de vos colonnes : par exemple, vous pouvez faire glisser le prénom vers l'étiquette de ligne ou de colonne, en fonction de la façon dont vous souhaitez afficher les données.
  4. Valeurs : cette section vous permet d'afficher vos données différemment. Au lieu de saisir des valeurs numériques, vous pouvez additionner, calculer un total, une moyenne ou une valeur maximale ou minimale, ou encore effectuer d'autres opérations avec vos données. Par défaut, lorsque vous faites glisser un champ vers Valeurs, un total est toujours calculé.

Si vous souhaitez savoir combien de prospects vous comptez dans chaque secteur d'activité, faites glisser la colonne Secteur dans Étiquettes de lignes et dans Valeurs dans votre tableau croisé dynamique. Cela vous permettra de calculer le nombre total de prospects associés à chaque secteur.

2 - Insérer plusieurs lignes ou colonnes à la fois

Lorsque vous travaillez sur des données, il est parfois nécessaire d'ajouter plus de lignes ou de colonnes à la fois. Les insérer une à une rendrait la tâche beaucoup trop fastidieuse. Une méthode plus simple existe.

Pour insérer plusieurs lignes ou plusieurs colonnes dans une feuille de calcul, sélectionnez un nombre de lignes ou de colonnes existantes identique au nombre que vous souhaitez insérer. Effectuez ensuite un clic droit et sélectionnez Insertion.

Insérer des espaces

3 - Filtres

Si votre feuille de calcul contient un très grand ensemble de données, il est rare que vous ayez besoin de consulter toutes les lignes à la fois. Vous consultez souvent les données qui correspondent à un critère spécifique.

Les filtres vous permettent de réduire la quantité de données visibles en n'affichant que certaines lignes à la fois. Dans Excel, vous pouvez ajouter un filtre à chaque colonne, puis sélectionner les cellules que vous souhaitez consulter.

Ajoutez un filtre en cliquant sur l'onglet Données puis en sélectionnant l'option Filtrer. Cliquez sur la flèche dans l'en-tête de colonne. Vous pouvez alors choisir si vous souhaitez trier vos données par ordre croissant ou décroissant, et indiquer si vous souhaitez afficher des lignes en particulier.

Imaginez que vous ne souhaitiez afficher que les prospects travaillant dans le secteur de l'audiovisuel. En sélectionnant le filtre Audiovisuel, vous masquerez les autres lignes.

Conseil : si vous souhaitez effectuer des analyses supplémentaires, vous pouvez copier les valeurs d'une feuille de calcul filtrée dans une autre.

4 - Supprimer les doublons

Les grands ensembles de données contiennent souvent des doublons. Si vous disposez d'une liste de plusieurs contacts dans une entreprise et que vous souhaitez afficher uniquement le nombre d'entreprises, la fonction de suppression des valeurs en double vous sera très utile.

Pour supprimer les doublons, sélectionnez la ligne ou la colonne que vous souhaitez traiter. Accédez ensuite à l'onglet Données, puis cliquez sur Supprimer les doublons dans le groupe Outils de données.

Une fenêtre contextuelle s'affiche et vous demande de confirmer les données que vous souhaitez traiter. Sélectionnez Supprimer les doublons pour supprimer les valeurs en double.

Supprimer les doublons

Vous pouvez également utiliser cette fonctionnalité pour supprimer une ligne entière en fonction d'une valeur de colonne en double. Si trois de vos lignes contiennent les informations de Laurent Pasquier et que vous ne souhaitez en afficher qu'une seule, vous pouvez sélectionner l'ensemble de vos données puis supprimer les doublons selon l'adresse e-mail. Votre liste ne contiendra plus que des noms uniques.

5 - Transposer des données

Dans une feuille de calcul, vous pouvez choisir de transformer une ligne de données en colonne, ou inversement. Pour éviter de copier et coller chaque en-tête, la fonctionnalité de transposition vous permet de déplacer les données afin de les réorganiser.

Commencez par sélectionner dans votre feuille de calcul la colonne que vous souhaitez transposer en ligne. Faites un clic droit et sélectionnez Copier. Sélectionnez ensuite les cellules dans lesquelles vous souhaitez commencer votre première ligne ou colonne. Faites à nouveau un clic droit sur la cellule et sélectionnez Collage spécial. Une fenêtre s'affiche. En bas de celle-ci, cochez la case Transposé, puis cliquez sur OK. Votre colonne sera alors transposée en ligne, ou inversement.

Transposer

6 - Convertir

Excel permet de fractionner une cellule pour en créer deux. Vous pouvez, par exemple, extraire le nom de l'entreprise de l'adresse e-mail d'une personne, ou diviser la colonne de noms complets afin d'obtenir deux colonnes distinctes contenant le nom et le prénom de vos contacts pour vos modèles d'e-mail marketing.

Commencez par sélectionner la colonne que vous souhaitez fractionner. Accédez ensuite à l'onglet Données et sélectionnez Convertir. Une fenêtre contenant des informations supplémentaires s'affiche.

Vous devez d'abord sélectionner l'option Délimité ou Largeur fixe.

  • Sélectionnez Délimité si vous souhaitez fractionner la colonne en suivant un séparateur, comme une virgule ou une tabulation.
  • Sélectionnez Largeur fixe si vous souhaitez sélectionner l'emplacement exact de fractionnement pour toutes les colonnes concernées.

L'exemple ci-dessous illustre l'utilisation de l'option Délimité pour séparer le nom complet et le diviser en deux cellules : prénom et nom.

Il vous faudra alors sélectionner les séparateurs. Il peut s'agir d'une tabulation, d'un point-virgule, d'une virgule, d'une espace ou autre (par exemple, le symbole @ utilisé dans une adresse e-mail). Dans cet exemple, le séparateur utilisé est l'espace. Excel affiche ensuite un aperçu des nouvelles colonnes.

Si la nouvelle configuration vous convient, cliquez sur Suivant. La fenêtre qui s'affiche alors vous permet de sélectionner le format des données, si nécessaire. Une fois cette étape effectuée, cliquez sur Terminer.

Convertir

Utilisation des formules Excel

Découvrez quelques formules Excel à utiliser pour réaliser des calculs sur vos données. 

7 - Calculs simples

Excel permet de réaliser des calculs complexes, mais aussi des opérations simples, comme additionner, soustraire, multiplier ou diviser vos données.

  • Pour les additionner, utilisez le signe +.
  • Pour les soustraire, utilisez le signe -.
  • Pour les multiplier, utilisez le signe *.
  • Pour les diviser, utilisez le signe /.

Vous pouvez également utiliser des parenthèses pour effectuer certains calculs en priorité. Dans l'exemple ci-dessous (10+10*10), les deuxième et troisième valeurs ont d'abord été multipliées, avant d'ajouter la dernière valeur au résultat. Cependant, si vous saisissez (10+10)*10, l'addition des valeurs entre parenthèses sera effectuée avant la multiplication.

Calculs simples

Conseil : si vous souhaitez calculer la moyenne d'un ensemble de nombres, vous pouvez utiliser la formule =MOYENNE(plage de cellules). Si vous souhaitez additionner une colonne de nombres, vous pouvez utiliser la formule =SOMME(plage de cellules).

8 - Formules de mise en forme conditionnelle

La mise en forme conditionnelle permet de modifier la couleur d'une cellule en fonction des informations qu'elle contient.

Par exemple, si vous souhaitez mettre en avant certains nombres au-dessus de la moyenne ou faisant partie des 10 % les plus élevés de votre feuille de calcul, vous pouvez utiliser cette fonctionnalité. Excel permet aussi de colorer les points communs entre différentes lignes. Vous pouvez ainsi afficher plus clairement les informations qui vous importent.

Sélectionnez le groupe de cellules auquel vous souhaitez appliquer une mise en forme conditionnelle. Cliquez ensuite sur Mise en forme conditionnelle dans l'onglet Accueil et sélectionnez une option dans la liste déroulante. Vous pouvez également créer votre propre règle. Une fenêtre s'affiche et vous demande de préciser votre règle de mise en forme. Cliquez sur OK pour la mettre en place. Elle sera appliquée automatiquement.

9 - Fonction SI

Dans des ensembles de données importants, compter le nombre d'occurrences d'une valeur peut s'avérer extrêmement laborieux. Il est plus simple d'ajouter des informations dans une cellule si une cellule correspondante contient cette valeur.

L'exemple ci-dessous illustre une situation dans laquelle 10 points doivent être ajoutés à tous les prospects travaillant dans le secteur du tourisme. Au lieu de saisir manuellement le nombre 10 pour chaque personne concernée, vous pouvez utiliser la formule SI ALORS : si un prospect travaille dans le tourisme, alors il reçoit 10 points.

La formule : SI(test_logique, valeur_si_vrai, valeur_si_faux)

Voici la syntaxe avec les variables correspondant à l'exemple ci-dessous : =SI(D2="Tourisme";"10";"0")

Cette formule contient plusieurs variables :

  • Test_logique : l'élément logique de la fonction SI. Dans ce cas, la condition est D2="Tourisme", car vous recherchez les prospects travaillant dans le secteur du tourisme. Assurez-vous de bien mettre le mot Tourisme entre guillemets.
  • Valeur_si_vrai : il s'agit de la valeur qui doit s'afficher si l'élément est vrai. Dans ce cas, la valeur 10 doit s'afficher dans la cellule pour indiquer que le prospect a reçu 10 points. Utilisez des guillemets uniquement si vous souhaitez insérer un texte plutôt qu'un nombre.
  • Valeur_si_faux : il s'agit de la valeur qui doit s'afficher si l'élément est faux. Dans ce cas, si un prospect ne travaille pas dans le tourisme, la cellule doit afficher 0 pour montrer qu'il n'a pas reçu de points. Utilisez des guillemets uniquement si vous souhaitez insérer un texte plutôt qu'un nombre.

Note : dans l'exemple ci-dessus, 10 points ont été attribués à chaque prospect du secteur du tourisme. Il sera impossible de calculer plus tard le total des points car les valeurs 10 sont entre guillemets et sont considérées par Excel comme du texte, et non comme des nombres qui peuvent être additionnés.

10 - Symbole $

Vous rencontrerez souvent le symbole du dollar dans Excel. Dans une formule, il ne représente pas la devise américaine, mais il vous permet de conserver les mêmes valeurs de colonne et de ligne lorsque vous copiez la formule dans les lignes suivantes.

En effet, la référence de cellule (par exemple, si vous désignez la cellule A5 dans la cellule C5) est relative par défaut. Dans ce cas, vous faites référence à une cellule se trouvant deux colonnes vers la gauche (de C à A) sur la même ligne (5). C'est ce que l'on appelle une formule relative.

Lorsque vous copiez une formule relative d'une cellule à une autre, les valeurs sont automatiquement modifiées selon la cellule dans laquelle cette formule est déplacée. Cependant, si ces valeurs doivent rester les mêmes lorsque vous les copiez dans une autre cellule, vous devez alors utiliser une formule absolue.

Pour transformer la formule relative (=A5+C5) en formule absolue, il suffit de faire précéder les valeurs de ligne et de colonne d'un symbole $, comme dans cet exemple : (=$A$5+$C$5). Vous trouverez davantage d'informations sur la page de support Microsoft Office ici.

Fonctions Excel

Enfin dans cette dernière partie, découvrez comment utiliser quelques-unes des fonctions Excel.

11 - Fonction RECHERCHEV

Il est possible de combiner les données de deux feuilles de calcul différentes sur une seule feuille.

Par exemple, lorsque vous disposez d'une liste de noms et d'adresses e-mail dans une feuille de calcul, et de la même liste d'adresses e-mail avec les noms d'entreprises dans une autre feuille, vous pouvez combiner ces deux feuilles pour afficher toutes les informations au même endroit.

La fonction RECHERCHEV est utile dans ce cas de figure. Avant de l'utiliser, assurez-vous de disposer d'au moins une colonne identique dans les deux feuilles de calcul. Vérifiez minutieusement que les deux colonnes de données que vous souhaitez combiner sont parfaitement similaires sans exception.

La formule : =RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; [valeur_proche])

Voici la syntaxe avec les variables correspondant à l'exemple ci-dessous : =RECHERCHEV(C2;Feuil2!A:B;2;FAUX)

Cette formule contient plusieurs variables. Lorsque vous voulez combiner les informations des feuilles de calcul 1 et 2 sur la feuille 1, la situation est la suivante :

  • Valeur cherchée : il s'agit de la valeur identique dans les deux feuilles de calcul. Sélectionnez la première valeur dans la première feuille de calcul. Dans l'exemple suivant, il s'agit de la première adresse e-mail de la liste, soit la cellule C2.
  • Table matrice : il s'agit de la plage de colonnes de la feuille 2, depuis laquelle vous allez extraire les données, qui comprend la colonne de données identique à votre valeur cherchée (dans cet exemple, adresses e-mail) de la feuille 1 ainsi que la colonne de données que vous souhaitez copier dans la feuille 1. Dans cet exemple, il s'agit de « Feuil2!A:B ». A désigne la colonne A de la feuille 2, qui correspond à la colonne de la feuille 2 dans laquelle se trouvent les données identiques à la valeur cherchée (adresses e-mail) dans la feuille 1. B désigne la colonne B, qui contient les informations disponibles uniquement dans la feuille 2 et que vous souhaitez transférer sur la feuille 1.
  • Numéro de colonne : s'il s'agit de la table matrice (la plage de colonnes que vous venez de définir), cela indique à Excel dans quelle colonne se trouvent les nouvelles données que vous voulez copier dans la feuille 1. Dans cet exemple, il s'agit de la colonne Secteur d'activité, la seconde colonne dans la plage de colonnes (table matrice), autrement dit la colonne 2.
  • Valeur proche : utilisez FAUX pour vous assurer d'extraire uniquement les correspondances de valeurs exactes.

Dans l'exemple ci-dessous, les feuilles 1 et 2 contiennent des informations différentes sur les mêmes personnes, le lien entre les deux étant leurs adresses e-mail. Vous souhaitez donc combiner ces deux feuilles de calcul afin que les informations concernant le secteur d'activité soient transférées de la feuille 2 à la feuille 1.

RECHERCHEV

Lorsque vous saisissez la formule =RECHERCHEV(C2;Feuil2!A:B;2;FAUX), vous importez les informations concernant le secteur d'activité dans la feuille de calcul 1.

Notez ici que RECHERCHEV récupère uniquement les valeurs de la deuxième feuille situées à droite de la colonne contenant les données identiques. Cette fonction peut présenter certaines limites, ce qui explique pourquoi certains utilisateurs préfèrent les fonctions INDEX et EQUIV.

12 - INDEX et EQUIV

À l'instar de RECHERCHEV, les fonctions INDEX et EQUIV extraient des données d'un ensemble pour les placer dans un emplacement central. Voici les principales différences :

  1. RECHERCHEV est une formule bien plus simple. Dans de grands ensembles de données qui risqueraient d'entraîner des milliers de recherches, vous pouvez utiliser les fonctions INDEX et EQUIV pour réduire considérablement la durée de chargement dans Excel.
  2. Les formules INDEX et EQUIV fonctionnent de droite à gauche, alors que la fonction RECHERCHEV ne permet d'effectuer qu'une recherche de gauche à droite. En d'autres termes, si vous devez effectuer une recherche dans une colonne à droite de la colonne des résultats, vous devrez les réorganiser pour pouvoir utiliser RECHERCHEV. Cette tâche peut s'avérer laborieuse dans un grand ensemble de données, ou même entraîner des erreurs.

Si vous souhaitez combiner les informations de deux feuilles sur la feuille 1, mais que les valeurs des colonnes des feuilles 1 et 2 ne sont pas identiques, vous devez intervertir les colonnes pour utiliser RECHERCHEV. Dans ce cas, vous pouvez utiliser plutôt les fonctions INDEX et EQUIV.

Voici un exemple. La feuille 1 contient une liste de noms de prospects et leurs adresses e-mail d'entreprise, tandis que la feuille 2 contient la liste des adresses e-mail d'entreprise et la gamme de services qui intéresse les prospects.

Les informations contenues dans les deux feuilles sont les adresses e-mail, mais ces colonnes n'ont pas le même numéro de colonne sur chaque feuille. Utilisez alors les formules INDEX et EQUIV plutôt que RECHERCHEV pour ne pas avoir à intervertir les colonnes.

La fonction INDEX EQUIV est en réalité la formule INDEX intégrée à la formule EQUIV. La formule EQUIV est distinguée dans cet exemple par une couleur différente.

La formule : =INDEX(table matrice, formule EQUIV)

Elle devient : =INDEX(table matrice, EQUIV (valeur_cherchée, matrice_recherche))

Voici maintenant la syntaxe avec les variables correspondant à l'exemple ci-dessous : =INDEX(Feuil2!A:A;(EQUIV(Feuil1!C:C;Feuil2!C:C;0)))

Les variables sont :

  • Table matrice : la plage de colonnes de la feuille 2 contient les nouvelles données que vous souhaitez importer dans la feuille 1. Dans cet exemple, A renvoie à la colonne A, qui contient les informations Services intéressants pour chaque prospect.
  • Valeur cherchée : il s'agit de la colonne de la feuille 1 contenant des valeurs identiques dans les deux feuilles. Dans l'exemple suivant, elle correspond à la colonne E-mail de la feuille 1, c'est-à-dire la colonne C, ce qui donne : Feuil1!C:C.
  • Matrice recherche : il s'agit de la colonne de la feuille 2 contenant des valeurs identiques dans les deux feuilles. Dans l'exemple suivant, elle correspond à la colonne E-mail de la feuille 2, qui se trouve également être la colonne C, ce qui donne : Feuil2!C:C.

Une fois les variables saisies, saisissez la formule INDEX EQUIV dans la première cellule vide de la colonne Services intéressants sur la feuille 1, où vous souhaitez insérer les informations.

INDEX EQUIV

13 - Fonction NB.SI

Une formule permet de compter automatiquement le nombre d'occurrences d'une valeur dans Excel. Grâce à la fonction NB.SI, Excel peut ainsi comptabiliser le nombre d'occurrences d'un mot ou d'un nombre dans n'importe quelle plage de cellules.

Par exemple, imaginez que vous souhaitez connaître le nombre de fois où le mot Tourisme apparaît dans votre ensemble de données.

La formule : =NB.SI(plage, critère)

Voici la syntaxe avec les variables correspondant à l'exemple ci-dessous : =NB.SI(D:D,"Tourisme")

Cette formule contient plusieurs variables :

  • Plage : il s'agit de la plage que vous souhaitez couvrir avec la formule. Dans ce cas, puisque vous vous concentrez sur une seule colonne, vous devez saisir D:D pour indiquer que la colonne D correspond à la fois à la première et à la dernière colonne à prendre en compte. S'il s'agissait des colonnes C et D, vous auriez écrit C:D.
  • Critère : il s'agit de l'élément alphabétique ou numérique que vous souhaitez qu'Excel recherche. Utilisez des guillemets uniquement si vous souhaitez insérer un texte plutôt qu'un nombre. Dans cet exemple, le critère est Tourisme.

Saisissez la formule NB.SI dans une cellule, puis appuyez sur Entrée : vous obtiendrez alors le nombre d'occurrences de Tourisme dans la colonne de recherche.

14 - Combiner des cellules grâce au symbole &

Les bases de données ont tendance à séparer les données pour les rendre aussi précises que possible.

Par exemple, au lieu d'une colonne contenant le nom complet d'une personne, une base de données disposera de deux colonnes : une pour le prénom et une pour le nom. Vous rencontrerez également cette situation avec les adresses, avec des colonnes distinctes pour la ville, le code postal et la région. Dans Excel, vous pouvez combiner des cellules contenant différentes données en une seule en utilisant le symbole & dans votre formule.

Voici la syntaxe avec les variables correspondant à l'exemple ci-dessous :  A2&" "&B2

Si vous souhaitez, par exemple, combiner les prénoms et les noms dans une seule colonne contenant donc les noms complets, vous devez placer le curseur dans la cellule vide, où vous souhaitez afficher le nom complet. Sélectionnez alors une cellule contenant un prénom, saisissez le symbole &, puis sélectionnez la cellule contenant le nom correspondant.

Si votre formule finale est =A2&B2, notez que le prénom et le nom ne seront pas séparés l'un de l'autre par une espace. Pour insérer l'espace nécessaire, utilisez la fonction =A2&" "&B2. Les guillemets autour de l'espace indiquent à Excel d'insérer ce caractère entre le prénom et le nom.

Pour appliquer cette formule à plusieurs lignes, il vous suffit de faire glisser le coin inférieur droit de cette première cellule vers le bas, comme le montre l'exemple.

Combiner avec &

Toutes ces astuces sont très utiles mais lorsque vous travaillez avec de nombreuses données, il peut être intéréssant d'adopter plutôt un CRM

Pour découvrir les avantages d'un CRM pour votre entreprise, téléchargez cet e-book sans attendre. 

Télécharger l'e-book sur le CRM

Publication originale le 5 août 2019, mise à jour le août 05 2019